Removing Invalid Named Ranges from Google Spreadsheet using Google Apps Script

GasTips / spreadsheet
Google Apps Script / spreadsheet

Gists

This is a sample script for removing the invalid named range of #REF from Google Spreadsheet using Google Apps Script.

Issue and workaround

For example, there are 2 sheets of “Sheet1” and “Sheet2” in a Google Spreadsheet. A new named range of sample is created for the range of Sheet1!A1, and remove the sheet of “Sheet1”. By this flow, when the named range list is checked by UI on Spreadsheet, sample has #REF. This is the invalid named range.

Unfortunately, in the current stage, it seems that the named ranges of #REF cannot be retrieved by Google Spreadsheet service (SpreadsheetApp) and Sheets API. By this, the named ranges of #REF cannot be directly removed by the current specification. And, it seems that this has already been reported to the Google issue tracker. Ref

From the above situation, in this case, I would like to propose a workaround for removing the named ranges of #REF. The flow of this workaround is as follows.

  1. Convert Google Spreadsheet to XLSX data.
  2. Remove the named ranges of #REF in XLSX data.
    • Fortunately, the detailed specification of Microsoft Excel is published as Open XML. This workaround uses it. When Google Spreadsheet is converted to XLSX data, the XLSX data can be edited as the XML data.
  3. Convert XLSX data to Google Spreadsheet.

By this flow, the named ranges of #REF can be removed.

IMPORTANT

When Google Spreadsheet is converted to XLSX data, by functions in the Google Spreadsheet, the complete conversion might not be able to be achieved. For example, the checkboxes cannot be converted. So, please be careful about this. So, first, please test this workaround and confirm whether your Spreadsheet can be used normally for your actual situation.

Sample script:

Please copy and paste the following script to the script editor of the Spreadsheet including the invalid named ranges.

In this sample script, in order to convert XLSX to Google Spreadsheet, Drive API is used. So, please enable Drive API at Advanced Google services.

function removeInvalidNamedRanges() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const url =
    "https://docs.google.com/spreadsheets/export?exportFormat=xlsx&id=" +
    ss.getId();
  const name = "xl/workbook.xml";
  const blob = UrlFetchApp.fetch(url, {
    headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() },
  })
    .getBlob()
    .setContentType(MimeType.ZIP);
  const blobs = Utilities.unzip(blob);
  const xml = blobs.find((b) => b.getName() == name).getDataAsString();
  const root = XmlService.parse(xml).getRootElement();
  root
    .getChild("definedNames", root.getNamespace())
    .getChildren()
    .forEach((e) => {
      if (e.getValue() == "#REF!") e.detach();
    });
  const newBlobs = [
    ...blobs.filter((b) => b.getName() != name),
    Utilities.newBlob(
      XmlService.getRawFormat().format(root),
      MimeType.XML,
      name
    ),
  ];
  Drive.Files.insert(
    { title: `Modified_${ss.getName()}`, mimeType: MimeType.GOOGLE_SHEETS },
    Utilities.zip(newBlobs).setContentType(MimeType.MICROSOFT_EXCEL)
  );
}
  • When this script is run, the above flow is done. By this, you can see the created a new Spreadsheet to the root folder. When you open the new Spreadsheet, you can see the invalid named ranges of #REF are removed.

Note

  • Of course, the active Spreadsheet can be overwritten by the converted XLSX data. But, in this case, I proposed to create it as a new Google Spreadsheet. Because when the original Spreadsheet is overwritten, it might not be your expected result.

References

 Share!