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

CodePudding user response：

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 v = Number(c.getAttribute("s").getValue());
      const columnIndexToLetter_ = index => (a = Math.floor(index / 26)) >= 0 ? columnIndexToLetter_(a - 1)   String.fromCharCode(65   (index % 26)) : ""; // Ref: https://stackoverflow.com/a/53678158
      if (quotePrefix[v]) ar.push(`${columnIndexToLetter_(j)}${i   1}`);
    });
    return ar;
  }, []);

  // Change the background color of detected cells.
  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.

References:

• Understanding the Open XML file formats
• XML Service