CODEWITHSUNDEEP
google-apps-scriptnamed-ranges
maxhugen
maxhugen

Reputation: 1944

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

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

Upvotes: 1

Views: 444

Answers (2)

ziganotschka
ziganotschka

Reputation: 26836

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

Upvotes: 0

mshcruz
mshcruz

Reputation: 1987

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

Upvotes: 1

Related Questions