CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetslogging
Indie
Indie

Reputation: 47

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

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!

Upvotes: 1

Views: 677

Answers (3)

TheMaster
TheMaster

Reputation: 50426

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)

Upvotes: 2

Cooper
Cooper

Reputation: 64042

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

Upvotes: 0

TheWizEd
TheWizEd

Reputation: 8598

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

Upvotes: 0

Related Questions