Apps Script getNamedRanges() returns "[Named Range]" instead of name of range

Question

I'm pretty new to App Script, so this is a real head-scratcher for me.

With four named ranges on Sheet1 ("Range1","Range2","Range3","Range4") Logger.log(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getNamedRanges());

Returns [NamedRange, NamedRange, NamedRange, NamedRange]

The larger goal has been to get this script working, to return the named range an active cell resides in.

Trying to get that working and debugging lead me to the above, making me wonder if the script isn't working due to how getNamedRanges is being returned?

Test Sheet file here.

Is the [NamedRange] result expected behavior or am I missing something to get the names of the ranges to return?

Thanks!

[Your email address can be accessed by the public](https://meta.stackoverflow.com/questions/394304/), when you share Google files. — TheMaster, Aug 28 '22 at 07:45

TheMaster · Accepted Answer · 2022-08-27T21:00:05.833

It is expected behavior. When logging a object of a class, the name of the class is usually logged in the console. .getNamedRanges() returns a array[] of namedRange objects.

console.log(
    SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sheet1")
    .getNamedRanges()[0]/*First namedRange*/
)//Expected: NamedRange Type: Object

Each namedRange has many inbuilt functions, i.e., methods. A list of such methods is here. One such method is getName(). You can use it to get the name of the NamedRange object:

console.log(
    SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sheet1")
    .getNamedRanges()[0]/*First namedRange*/
    .getName()/*First named range's name*/
)//Expected: "Range1" Type:string

You may use Array.map to get all the names:

console.log(
    SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sheet1")
    .getNamedRanges()
    .map(namedRange => namedRange.getName())
)//Expected: ["Range1","Range2","Range3","Range4"] Type: Object(specifically a array of strings)

Excellent, thank you for the clarification and pointing me in the right direction. Much appreciated. — Indie, Aug 27 '22 at 23:28

score 0 · Answer 2 · answered Aug 27 '22 at 20:16

getNamedRanges() returns an array of NamedRanges.

let namedRanges = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getNamedRanges());
namedRanges.forEach( namedRange => Logger.log(namedRange.getRange().getA1Notation()) );

will give a list of ranges in A1 notation such as

A1:B10
C2:Z100

score 0 · Answer 3 · answered Aug 27 '22 at 20:59

Display range name, sheet name and A1 Notation

function getSpreadsheetsNamedRanges() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const nrA = ss.getNamedRanges().map(rg => [rg.getName(),rg.getRange().getSheet().getName(),rg.getRange().getA1Notation()])
  sh.clearContents();
  nrA.unshift(["Range Name","Sheet Name","A1 Notation"]);
  sh.getRange(1,1,nrA.length,nrA[0].length).setValues(nrA);
}

Apps Script getNamedRanges() returns "[Named Range]" instead of name of range

3 Answers3

Display range name, sheet name and A1 Notation