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

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!

CodePudding user response：

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)

CodePudding user response：

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

CodePudding user response：

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);
}