CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsnamed-ranges
Sam
Sam

Reputation: 33

Problem with Named Ranges in Google Sheets using Apps Script, when those Named Ranges have broken references

This little bit of Google Apps Script will list any named ranges that you define in the spreadsheet to which the apps script is linked. It uses the getNamedRanges() of the Spreadsheet object.

function myFunction() {
  let thisSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let theNamedRanges = thisSpreadsheet.getNamedRanges();

  let ordinal = 0;
  for (aRange of theNamedRanges) {
    Logger.log("Named Range %s: %s", ++ordinal, aRange.getName());
  }

}

If you define a few Named Ranges (Menu > Data > Named Ranges) in a new (blank - no need for data) Google Sheets document, then go to [Menu] > Extensions > Apps Script, paste the little snippet from above into the Code.gs file that it creates for you, save, and run 'myFunction', then answer Google's safety questions about granting access to your new project, you should see the names you gave to your Named Ranges listed in the 'Execution Log' that appears at the bottom of the apps script tab.

Now, return to your spreadsheet document, and add another sheet, by pressing the [+] button down in the bottom left. Now that you have two sheets in your document, you can define a few more named ranges on the new sheet. Then, if you go back to the apps script project, and re-run your myFunction, you'll see all the original set of Named Ranges plus all the new ones listed together. No surprises.

Now go back to your spreadsheet document once more, and in the bottom left of the screen, right-click on the 'tab' of one of the two sheets in your document, and choose 'delete'. If you've still got the Named Ranges sidebar open, you'll see that all your Named Ranges still exist, but that those which were defined on the now-deleted sheet no longer have a valid range of cells associated with them. Where the valid named ranges still show a Sheet!Cell:Cell reference, the invalid ones (those whose sheet has been deleted) just say #REF.

picture of valid and invalid named ranges

If you re-run your myFunction now, you'll see that it only lists the Named Ranges that still have valid references to cells in the Sheets document. The Named Ranges whose cells were on the deleted sheet are no longer listed.

I have a Google sheets document that is 'operated' by some Apps Script that I've written. Every day I use the spreadsheet, and I use my script to add new sheets, and to these sheets it adds new named ranges. When I am done with today's sheets, I can push a button and delete them en masse with another piece of apps script, but I did not realise that this leaves the named ranges in the document. So now my document has hundreds and hundreds of named ranges with broken cell references. I tried to use more apps script to delete the defunct named ranges, but as we've seen, I can only discover the named ranges that have valid cell references; I want the busted ones!

Am I doing it wrong? Does anyone know a way that I can list all the named ranges, so that I can delete the old broken ones? The getNamedRanges() function seems unable to see the broken ranges, though clearly Google is able to list the broken ones, because I can see them (hundreds of them!) in the Named Ranges sidebar.

thanks for your time

Sam

Upvotes: 0

Views: 417

Answers (1)

Cooper
Cooper

Reputation: 64140

function nr() {
  const ss = SpreadsheetApp.getActive();
  const arr = ss.getNamedRanges().map(nr => [nr.getName(),nr.getRange().getA1Notation()]).filter(r => !~r[1].indexOf("#REF"));
  arr.unshift(["Name","Range"]);
  Logger.log(JSON.stringify(arr))
}

This versions attempts to calculate the length of the data array associated with the range.

function nr() {
  const ss = SpreadsheetApp.getActive();
  const arr = ss.getNamedRanges().filter(nr => nr.getRange().getValues().flat().length > 0).map(nr => [nr.getName(),nr.getRange().getA1Notation()]);
  arr.unshift(["Name","Range"]);
  Logger.log(JSON.stringify(arr))
}

This removes the invalid namedRanges

function removeInvalidNamedRanges() {
  const ss = SpreadsheetApp.getActive();
  ss.getNamedRanges().filter(nr => nr.getRange().getValues().flat().length == 0).forEach(nr => nr.remove());
  let arr = ss.getNamedRanges().map(nr => [nr.getName(),nr.getRange().getA1Notation()]);
  arr.unshift(["Name","Range"]);
  Logger.log(JSON.stringify(arr))
}

Upvotes: 0

Related Questions