How to process all named ranges in a Google Sheet?

Question

Is there a way to process all of the Named Ranges in a Google Sheet?

Google Documents has .getNamedRanges(), but I can't find anything similar for sheets. I want to read and print each range parameter.

My end goal is to fix a lot of named ranges en masse. There are more than 200 in the document in question.

There's a bug in sheets (Documented here) and since default ranges in Named Ranges have the entire span of merged cells (ie: A1:J1), but named ranges only work in formulas if you specify JUST the first cell, I thought I could save a lot of time by just accepting the default named range (A1:J1 as an example) and then script it so I can just strip everything off of the end, leaving just A1. In order to do this, I need to simply be able to get a list of all named ranges in the sheet.

Is this possible?

Answer 1

Short answer is no, unfortunately.

there is an enhancement request at:

https://code.google.com/p/google-apps-script-issues/issues/detail?id=917

you can star to vote for it and receive updates.

Answer 2

The answer is now, at least, Yes. There remain some frustrating limitations with programmatic handling of named ranges, but the situation is much improved.

The same .getNamedRanges() method cited in the question works when applied to either a Sheet (only the named ranges referencing a range in the sheet) or Spreadsheet (all named ranges in the spreadsheet) object. It returns an array of all Named Ranges.

You cannot access a Named Range directly by name in GAS. A rather maddening oversight requiring iterating through an entire array of named ranges to get to the one needed.

In the end, however, there really isn't much you can't do with them. As several other questions on SO highlight, it's rarely if ever a good idea to programmatically delete a named range. Even though it would be a lot easier to delete one and then re-add it sometimes, that will break every reference in your sheet to the named range (unless they use the awkward indirect("named_range_name") construct).