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

user_12492886 0
Viewed 113

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

google-apps-script named-ranges google-sheets
2 Answers

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:

user_71086530

This appears to be a bug.

There's a report of this same issue in Google's issue tracker from a few years back. A Google rep replied to this thread in a comment stating the following:

Currently the method Sheet.getNamedRanges() returns every named range on the Sheet (both valid and invalid) (...) To exemplarise this behaviour I created a new Sheet with three ranges, and I deleted the columns of two of those ranges to make them invalid. Then I wrote this code based on the described solution:

//(Some code and some logs)

The invalid named ranges are easily differentiated from the valid ones. Please, try to replicate this approach and come back if it doesn't help sharing how you created the invalid ranges and the code used, so I could continue investigating this.

You can go to the thread for the code sample, but essentially he showed that getNamedRanges() does return "invalid" ranges with a #REF error. This is not entirely wrong, except that he tested it by deleting the column rather than the worksheet. Given his explanation it seems that it is expected that the method should list all invalid ranges so they can be handled, so the fact that it doesn't list the ranges in deleted sheets does appear to be an oversight or bug. Unfortunately, the thread died since there was little activity afterwards.

I also tried a few other possible workarounds with no success:

  • The getSheets() method doesn't seem to keep track of deleted sheets to see if we can directly reference them to search for the ranges.
  • getRangeByName() also returns null even if you want to directly search by name.
  • As suggested by TheMaster's comment I tried the Advanced service by running Sheets.Spreadsheets.get(), and it also only listed the namedRanges that were not in deleted sheets. Given that this is pretty much the same as the "pure" API calls it seems that it's not only an Apps Script issue. You can also test it in Google's APIs Explorer.

My suggestion would be to go to the issue tracker to try to reopen the bug or maybe just file a new report which may get it quicker attention. Either way it may take a while to fix so in the meantime you'll probably have to account for this issue in your code and delete the named ranges before deleting a Sheet.

user_123066870
Related