how to detect a leading single-quote / CHAR(39) in google sheets?

I believe your goal is as follows.

• You want to change the background color of cells that have the cell values with ' of top character to the blue color.
• Your goal includes using Google Apps Script.

In my experience, I thought that in this case, there might be no direct method for retrieving ' of top character. Unfortunately, even when Sheets API is used, ' cannot be retrieved. So, in this answer, I would like to propose a workaround. The flow of this workaround is as follows.

1. Convert the sheet you want to use to XLSX format.
2. By parsing the XLSX data, detect the cells that have the cell values with ' of top character.
3. Change the background color of the detected cells to blue color.

In Microsoft Docs, the detailed specification is published as Open XML. So, in this answer, by analyzing XLSX data, the cell coordinates of the cells with ' of top character can be retrieved by using only the native methods of Google Apps Script. When this flow is reflected in Google Apps Script, it becomes as follows.

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet you want to use, and save the script. And, please set the sheet name.

function myFunction() {
  const sheetName = "Sheet1"; // Please set the sheet name.

  // Convert Google Spreadsheet to XLSX format.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = ss.getSheetByName(sheetName);
  const url = `https://docs.google.com/spreadsheets/export?exportFormat=xlsx&id=${ss.getId()}&gid=${srcSheet.getSheetId()}`;
  const res = UrlFetchApp.fetch(url, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } });

  // Retrieve the data from XLSX data.
  const blobs = Utilities.unzip(res.getBlob().setContentType(MimeType.ZIP));
  const { sheet, style } = blobs.reduce((o, b) => {
    const name = b.getName();
    if (name == "xl/styles.xml") {
      o.style = b.getDataAsString();
    } else if (name == "xl/worksheets/sheet1.xml") {
      o.sheet = b.getDataAsString();
    }
    return o;
  }, {});

  // Detect the cells including the single quote at the top character.
  const styler = XmlService.parse(style).getRootElement();
  const quotePrefix = styler.getChild("cellXfs", styler.getNamespace()).getChildren().map(e => e.getAttribute("quotePrefix") ? true : false);
  const sr = XmlService.parse(sheet).getRootElement();
  const ranges = sr.getChild("sheetData", sr.getNamespace()).getChildren().reduce((ar, r, i) => {
    r.getChildren().forEach((c, j) => {
      const r = c.getAttribute("r").getValue();
      const v = Number(c.getAttribute("s").getValue());
      if (quotePrefix[v]) ar.push(r);
    });
    return ar;
  }, []);

  // Change the background color of detected cells.
  if (ranges.length == 0) return;
  srcSheet.getRangeList(ranges).setBackground("blue");
}

Testing:

When this script is run, as a sample situation, the following situation is obtained. In this sample, in the input situation, the cells "A1:A3" has the value of '500. And, the cells "B1:B3" has the value of 500. When this script is run, the background color of "A1:A3" is changed.

Note:

• About resets color, in this case, how about the following modification?

  • From

      if (ranges.length == 0) return;
      srcSheet.getRangeList(ranges).setBackground("blue");
    

  • To

      if (ranges.length == 0) return;
      srcSheet.getDataRange().setBackground(null);
      srcSheet.getRangeList(ranges).setBackground("blue");
    

• Or, if you want to check only the cells with the blue background color, please modify as follows.

  • From

      if (ranges.length == 0) return;
      srcSheet.getRangeList(ranges).setBackground("blue");
    

  • To

      if (ranges.length == 0) return;
      const color = "#0000ff"; // This is blue.
      const r = srcSheet.getDataRange();
      r.setBackgrounds(r.getBackgrounds().map(r => r.map(c => c == color ? null : c)));
      srcSheet.getRangeList(ranges).setBackground(color);
    

References:

• Understanding the Open XML file formats
• XML Service

It doesn't seem to be possible with formulas or custom functions. But, you can do with TextFinder or the "Find and replace" functionality. Using regex's starting anchor ^, we replace the cells to a formula and back to text. The differentiating factor is that cells with a single quote prefix cannot be converted to a formula using this method.

• '005 => '=005(not a formula)
• abc => =abc(is a formula)
• =1+5 => ==1+5(still is a formula)

/**
 * @OnlyCurrentDoc
 * @description Detects single quotes in given range  and colors them 
 * @author TheMaster
 * @link https://stackoverflow.com/a/73621052
 */
function detectSingleQuotePrefix(
  range = 'Sheet1!A1:E15',
  color = 'LightGoldenRodYellow'
) {
  const dataRange = SpreadsheetApp.getActive().getRange(range),
    txtFinder = (
      /** @type {string} */ find,
      /** @type {string} */ replace,
      /** @type {boolean} */ matchFormula
    ) =>
      dataRange
        .createTextFinder(find)
        .matchFormulaText(matchFormula)
        .useRegularExpression(true)
        .replaceAllWith(replace),
    isEmpty = (
      /** @type {string} */ e,
      /** @type {number} */ i,
      /** @type {number} */ j
    ) => (e === '' && values[i][j] !== '' ? color : null);
  txtFinder('^.+', '=$0', false);
  const values = dataRange.getValues();
  const dividedColors = dataRange
    .getFormulas()
    .map((row, i) => row.map((col, j) => isEmpty(col, i, j)));
  txtFinder('^=', '', false);
  txtFinder('^=', '', true);
  dataRange.setBackgrounds(dividedColors);
  console.log(dividedColors);
}

As per this Google Issue Tracker it was confirmed that

"Update from spreadsheet team: A leading quote says that the remaining text should be treated as plain text. This behaviour is consistent with other spreadsheet products. "

This would mean that it is an expected behaviour for the leading single quote/apostrophe to be hidden on any spreadsheet products (not just in Google Sheets).

From my research, the only workaround that I could find relevant is by using two single quotes/apostrophes (if ever this would be applicable in your setup) instead of using a single one before the cell value as seen below.

Used =FIND("'",B1) for testing