Retrieving Cell Coordinates of Cells with Quote Prefix using Google Apps Script (Single Quote)

Google Apps Script / spreadsheet
GasTips / spreadsheet

Gists

This sample script retrieves the cell coordinates of cells with the quote prefix. In Google Spreadsheet, when a single quote is added to the top letter of the cell value, the cell is used as the text value. When we want to search the cells with the quote prefix in Spreadsheet, unfortunately, in the current stage, this cannot be achieved using Spreadsheet service (SpreadsheetApp) and Sheets API. In this method, such cells can be retrieved. The output values are the cell coordinates of the cells with the quote prefix.

Sample script

Please copy and paste the following script to the script editor of Spreadsheet. And, please set the sheet name, and run the script.

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");
}
  • When this script is run, the background colors of searched cells are changed to blue color.

References

  • This has already been added to a new method of DocsServiceApp.

  • I answered this method to this thread at Stackoverflow.

 Share!