Spreadsheet.getNamedRanges() does not return NamedRanges defined in DELETED worksheets

Question

I had a complicated spreadsheet where each tab had a lot of named ranges defined in different workseets (say Tab_A, Tab_B, ... Tab_X). I am trying to break this spreadsheet into smaller independent spreadsheets (lets say independent spreadsheet 1 has only Tab_A, Tab_B) by first copying the original large spreadsheet and chopping out worksheets I don't need and then trying to clean up the Named Ranges. I am trying to get rid off all the now invalid Named ranges that are defined in the now deleted worksheets.

I tried running this script...

function ListInvalidNamedRanges() { 
  var spreadsheet = SpreadsheetApp.getActive();
    
  /* remove named range if match or partial match with Tab Name reference */
  var namedRangeList = spreadsheet.getNamedRanges();
  for (var j=0; j<namedRangeList.length; j++) {
    var namedRangeName = namedRangeList[j].getName();
    // Do not use includes - ECMA-6 not supported in AppScript
    try {  
      var namedRange = namedRangeList[j].getRange();
      var valueToTriggerErrorIfApplicable = namedRange.getValue(); // force error if necessary
      Logger.log("Try: Valid named range: %s; Range: %s; Value: %s; j: %s", namedRangeName,namedRange, valueToTriggerErrorIfApplicable, j  );
    }
    catch(e) {
      Logger.log("Catch: Invalid named range: %s; Range: %s; Value: %s", namedRangeName,namedRange, valueToTriggerErrorIfApplicable  );
      // namedRangeList[j].remove();
    }
  }                        
}

The problem is that the spreadsheet.getNamedRanges() does NOT return the list of named ranges defined in the now-deleted tabs, BUT they do show up in the Named Range UI as you can see in the image below.

View of named images using the UI

Is there an alternative call to spreadsheet.getNamedRanges() that returns the full list?

There are too many junk NamedRanges to go delete one at a time from the UI (and even recording a macro does not work since these junk NamedRanges don't seem accessible via appscript in the macro either). I have this use case more than just this one time, so I am trying to do this in a script-driven way.

This can be easily seen with this simpler test case with just 2 worksheets.

This is the "Before Delete" version of the test spreadsheet. create a local copy for yourself. https://docs.google.com/spreadsheets/d/1XVTmOMROWuCO640eBnNDF2WpVZFU4UA854pabvYd1ZE/edit?usp=sharing.

Output of running the AppScript utility BEFORE deleting the 1st-worksheet

Now, delete the 1st worksheet, W1-to-be-deleted.

This is what shows up in the Named Range UI

This is what shows up when you run the AppScript function

Output of running the Appscript function AFTER the worksheet delete

Answer 1

Issue and workaround:

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:

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:

• Files: insert
• Class XmlService
• DefinedName Class