Automatically substitute range names in google sheets

Question

I love the idea of named ranges for making formulas less obscure. However I have yet to find an easy way to actually use them.

E.g. when entering a sum, I normally do

=sum( then mouse to the blob of stuff I want then type )

If I want to use a named range, I have to type in the full name manually.

I've tried having the named range sidebar open and clicking on that. Still fills it in column row syntax.

EDIT: New Sheets allows you to click on a range name and it is stored in the formula as a range name.

I've also tried starting to type the name of the range, in hopes that it would try to guess ahead. No joy there either.

Is there a way to automate the use of named ranges? E.g. a script that looks for ranges in your sheet, and compares them to the list of named ranges, and substitutes the named range for the column row syntax?

Answer 1

I don't believe it would be possible, AFAIK, to have the Autocomplete work with named-ranges in that way. To do so, according to GAS documentation one would have to declare a function for each named-range that merely returns its own range, and then call that when wanting to use that range in a function. I believe that GAS prevents the automation of JavaScript code generating other code, so these 'identity' functions would have to be written by hand, somewhat defeating the purpose of adding convenience. I cannot find a link that backs this assumption up, so I apologize for the anecdotal evidence merely based on my experience.

Using the name of namedRanges to conjure up its range is, as of this writing, already baked into Spreadsheets functionality (which I found out at this very moment), so you won't have to have it fill in the A1 range syntax if you'd prefer to use the name itself.

Hope this helps. Cheers!