IssueTracker

When I call getNamedRanges() on a Spreadsheet I only get those named ranges with valid ranges. I would like to also get those with #REF (i.e. a name ranged from a tab which has been deleted). My use case is that I want to script automatically cleaning up those named ranges. I also tested with the advanced Google Sheets API and it doesn't return them either.

1. Create a spreadsheet with two tabs
2. Add a named range to an arbitrary cell on one of the tabs
3. Run the following script and confirm that your name range appears
3. Delete the tab and check that Data > Named ranges... now shows your named range as #REF
4. Run the same script and confirm that your name range no longer appears

function fetchNamedRanges() {
  var namedRanges = SpreadsheetApp.getActive().getNamedRanges();
  Logger.log(namedRanges.length);
  for (var i=0; i<namedRanges.length; i++) Logger.log(namedRanges[i].getName());
//  var namedRanges = Sheets.Spreadsheets.get(SpreadsheetApp.getActive().getId()).namedRanges;
//  Logger.log(namedRanges.length);
//  for (var i=0; i<namedRanges.length; i++) Logger.log(namedRanges[i].name);
}

To use the commented out lines, enable the advanced Google Sheets API in Resources > Advanced Google services... and in the Cloud Platform API Dashboard.