Detecting Cells with Quote Prefix in Google Spreadsheet using Google Apps Script

GasTips / spreadsheet
Google Apps Script / spreadsheet

Gists

This is a sample script for detecting cells with the quote prefix in Google Spreadsheet using Google Apps Script.

For example, when a value is put to a cell by adding a single quote ' as the top character, the cell value is used as the string value. This is the current specification. Under this condition, when the cells with the value of the quote prefix are tried to be detected, unfortunately, in the current stage, it seems that there is no method for directly achieving this in the methods of Spreadsheet service (SpreadsheetApp).

But, in the case of XLSX data, it can be detected whether the cell has the value of the quote prefix. And, fortunately, in Microsoft Docs, the detailed specification of XLSX data is published as Open XML. And also, Google Spreadsheet can be converted to the XLSX data. By using them, this goal can be achieved using the native Google Apps Script. The sample script is as follows.

Sample script

Please copy and paste the following script to the script editor of Spreadsheet. When this script is run, the cells in the active sheet are used.

function myFunction() {
  // Convert Google Spreadsheet to XLSX format.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = ss.getActiveSheet();
  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;
  }, []);

  console.log(ranges); // Here, you can see the detected cell coordinates as a list of A1Notation.

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

Testing

When this script is run, the demonstration of the top of this script can be obtained.

Note

From this post, it has already been found that the process cost of XmlService is still high. But, I would like to believe that this will be resolved in the future update. So, in this sample script, I used XmlService.

Reference

 Share!