Google Sheets Script: Get Multi-Col/Row Named Range from Current Cell

Question

From other examples, I have a function that returns the name of a Named Range for the current cell - but only if the range consists of a single cell.

I started trying to parse the starting and ending Col and Row of the range, but ran into problems as soon as cols went to more than one character, eg "AA".

Then I thought that if I could get the cell's "R1C1" notation, I could use getCell() to test if it exists in the range. However, I can't find an equivalent function of getA1Notation() to get the R1C1 of the current cell. How is it obtained pls?

function c_GetCellNamedRange() {
  const ui = SpreadsheetApp.getUi();
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const cell = sheet.getCurrentCell().getA1Notation();
  ui.alert("Current Cell: " + cell);

  //get all the name ranges of the spreadsheetsheet
  var namedRanges = SpreadsheetApp.getActiveSpreadsheet().getNamedRanges();
  // loop through all the names range of the sheet
  for(i=0;i<namedRanges.length;i++){
  
/*  
    // this only works if range is a single cell! How to loop through?
    if(namedRanges[i].getRange().getA1Notation() == A1Notation) {
      rangeName = namedRanges[i].getName();
      break;
    }
*/
    // get the current cell R1C1 notation, then use getCell() to test if
    // it exists in the named range?
    //var rv = namedRanges[i].getCell(cell.getRow(), cell.getColumn())
    var rv = namedRanges[i].getCell(cell.getRowIndex(), cell.getColumn())
    if (!rv ) {
      rangeName = namedRanges[i].getName();
      break;
    }

  }

  if ( !rangeName ) rangeName = "none"
  ui.alert("Current Cell:   " + cell + " \r\n\r\n Named Range:   " + rangeName);
}

What exactly is your purpose? Do you want to find out either the named range consists of more than one cell? Or do you want to know to which namedRange a cell belongs? — ziganotschka, Dec 08 '20 at 08:51

mshcruz · Accepted Answer · 2020-12-08T08:24:53.560

1

You can get the row index and the column index of a cell using getRow() and getColumn(). Then you can use that to write its R1C1 notation:

const r1c1Notation = `R${sheet.getCurrentCell().getRowIndex()}C${sheet.getCurrentCell().getColumn()}`;

edited Dec 08 '20 at 08:24

answered Dec 08 '20 at 07:35

mshcruz

1,967
2
12
12

Thanks, but when I tried both getRow() and getRowIndex(), I got "TypeError ... is not a function". I have updated the function above to show exactly what I'm doing. – maxhugen Dec 08 '20 at 08:21
1

I haven't realized you were using a `cell` variable already to store its A1 notation. The idea is that you use getRow() and getColumn() on the range (cell) object. I've updated the answer to make that more clear. – mshcruz Dec 08 '20 at 08:26
Thank you! After 2 months of JS, I still get a bit mixed up as to whether I'm referencing the object, or not! – maxhugen Dec 08 '20 at 09:41
1

I'm far from being an expert myself, but this book really helped me understand some JS quirks: https://github.com/getify/You-Dont-Know-JS/blob/1st-ed/README.md – mshcruz Dec 08 '20 at 09:58
1

Can't even remember when I last read a book, with almost everything online. Google is usually my friend, but sometimes it leads me down a rabbit hole! Thank you the Github link, – maxhugen Dec 08 '20 at 11:46

ziganotschka · Answer 2 · 2020-12-09T10:19:55.370

If I understood you correctly you want to loop through each cell of a named range to compare the A1 notation of the cells with a variable

To do so, you can use the method range.getCell() withinh a nested loop - since the range is a 2-D object.
One of the ways to establish the height and width of the range is to use the methods getRow(), getLastRow(), getColumn() and getLastColumn().

Sample:

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var namedRanges = SpreadsheetApp.getActiveSpreadsheet().getNamedRanges();
  var A1Notation = "B5";
  for(var i = 0;i < namedRanges.length; i++){
    Logger.log(namedRanges[i].getRange().getA1Notation());
    var range = namedRanges[i].getRange();    
    var rangeWidth = range.getWidth();
    var rangeHeight = range.getHeight();
    for (var j = 1; j <= rangeWidth; j++){ 
      for (var k = 1; k <= rangeHeight; k++){ 
        var A1 = range.getCell(j, k).getA1Notation();
        if (A1 == A1Notation){
          rangeName = namedRanges[i].getName();
          break;
        }      
      }
    }
  }
}

Google Sheets Script: Get Multi-Col/Row Named Range from Current Cell

2 Answers2

If I understood you correctly you want to loop through each cell of a named range to compare the A1 notation of the cells with a variable