Return name of range as string in Google Sheets

Question

I have a range named apple in Google Sheets. I thought a method like getRangeName("apple") would exist. I want to get the names of many ranges and set them as the header for the columns of a new sheet.

The hope is that I could add something like log to apple ("apple_log") and then write a function to add all named ranges with the string "log" as a header to a different sheet.

Is there a way to return the name of a range as string?

Answer 1

That's currently not possible in Google Apps Script. See this enhancement request in the Google Apps Script issue tracker, to make that possible:

• issue 4245

As mentioned in the issue itself:

To subsequent readers: If you are also interested in this requested feature, please click the star next to the issue number.

Answer 2

Building on Mick's answer:

function returnRangeStr(){

  return SpreadsheetApp.getActiveSpreadsheet().getNamedRanges()[0].getName();

}

It does work, but some looping and checking might be required.

Similar is

function returnRangeName(rangeName) {

  return SpreadsheetApp.getActiveSpreadsheet().getNamedRanges()[rangeName].getName();

}

So calling '=returnRangeName(0)' will return first named range on the list. The closest I can get is

 function returnRangeName(rangeName) {

      var allNamedRanges = SpreadsheetApp.getActiveSpreadsheet().getNamedRanges();

      for(var i = 0; i < allNamedRanges.length; i++) {

        if(allNamedRanges[i].getName() == rangeName) {

          return allNamedRanges[i].getName();

        }
      }
    }

Calling =returnRangeName("apple_log") should return string 'apple_log'. After collecting named ranges, it should be fairly simple to loop through them and check only for ones that end with '_log' and return .getName() of those vertically or horizontally.

There's another way to loop trough named ranges and spit out only those that end with a specific pattern (like '_log'):

function returnRangeName(pattern) {

  var allNamedRanges = SpreadsheetApp.getActiveSpreadsheet().getNamedRanges();
  var returnThis = new Array();

  for(var i = 0; i < allNamedRanges.length; i++) {

    if(allNamedRanges[i].getName().substr(allNamedRanges[i].getName().length - pattern.length) == pattern) {

      // define cell in which to write 'allNamedRanges[i].getName();
      returnThis.push(allNamedRanges[i].getName());

    }

  }

  return returnThis;

}

Call it like this: =returnRangeName("_log"). You don't need to return it all at once, you could return every match to a different cell, but then you need to adjust this function. Hope this will be useful to someone.

Answer 3

Short Answer

Use getNamedRanges() to get all the named ranges then compare the range A1 notation with the A1 notations of the named ranges, then get the name of the range by using namedRange.getName()

Extended answer

On February 2016 the nameRange type and several related methods were added, so now it's possible to programatically get the names of the named ranges.

---

Few hours ago I added a code sample to Display the name of a cell in a worksheet which is slighty similar to this question. The sample code include on my answer to that question include a function that returns the name or current cell. It make use of a for loop to compare the A1 notation of the current cell with the a1 notation of the named ranges and returns the name of the first match.

/**
 * If the active cell is a named range, returns its name,
 * otherwise returns No named cell
 *
 */
function getName(){
  var cell = SpreadsheetApp.getCurrentCell();
  var namedRanges = SpreadsheetApp.getActive().getNamedRanges();
  for(var i = 0;i< namedRanges.length; i++){
    var range = namedRanges[i].getRange();
    if(cell.getA1Notation() === range.getA1Notation()) return namedRanges[i].getName();
  }
  return 'No named cell';
}

Answer 4

function returnRangeStr(){
  return SpreadsheetApp.getActiveSpreadsheet().getRangeByName(arguments[0]).getA1Notation();
}