0

I have created a NamedRange and used it for a data validation on . a certain cell.

ON my script, when this cell is edited, I would like to know that the certain cell uses a certain NamedRange.

can fetch the data validation range from the cell:

SpreadsheetApp.getActiveSheet().getActiveCell()

However this is not the named range.

Is there any way to do it?

Thanks!

Myupe
  • 545
  • 1
  • 5
  • 9

3 Answers3

4

There is no build in function to do this. You could create you own function that will check it.
Something like:

function getRangeName(A1Notation) {
    var rangeName = "";
    //get all the name ranges of the sheet
    var namedRanges = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getNamedRanges();
    // loop on all the names range of the sheet
    for(i=0;i<namedRanges.length;i++){
        //if it's the same A1 Notation
        if(namedRanges[i].getRange().getA1Notation() == A1Notation)
            rangeName = namedRanges[i].getName();
    }
    //will return the name of the range or "" if there is no range
    return rangeName;
}

You'd then simply have to call getRangeName(SpreadsheetApp.getActiveSheet().getActiveCell().getA1Notation()) to have the range name.

You might have to update it a little if your range is more than one cell

Liora Haydont
  • 1,252
  • 1
  • 12
  • 25
  • 1
    AFAICT, this won't work since `namedRanges[i].getRange().getA1Notation()` might look like `P13:P23` whereas the active cell notation will look like `P15`. You have to parse the column and row out of the notations and check for containment. – Dustin Wyatt Oct 15 '18 at 19:15
  • Dustin, quite right, still looking for a way to loop(?) through the cells in a multi column/row range... – maxhugen Dec 08 '20 at 06:11
1

Thanks Liora - this helps a lot!

My requirement was to get the name of a named range that is used by data validation of a cell that is in another sheet - I changed your answer a bit and got to this, which is working perfectly:

function onEdit() {
  var cell = SpreadsheetApp.getActiveSheet().getActiveCell();
  var rangeName = getRangeName(cell);
  Logger.log('The named range is %s', rangeName);
}

function getRangeName(cell) {
  var rangeName = "";

  if (!cell) return;

  var dataValidation = cell.getDataValidation();

  if (!dataValidation) return;

  var A1Notation = dataValidation.getCriteriaValues()[0].getA1Notation();

  //get all the name ranges of the sheet where I put my lists
  var namedRanges = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('lists').getNamedRanges();
  // loop on all the names range of the sheet
  for(i=0;i<namedRanges.length;i++){
    //if it's the same A1 Notation
    if(namedRanges[i].getRange().getA1Notation() == A1Notation)
      rangeName = namedRanges[i].getName();
  }
  //will return the name of the range or "" if there is no range
  return rangeName;
}
Myupe
  • 545
  • 1
  • 5
  • 9
1
function onEdit(e){
  var myRng = SpreadsheetApp.getActiveSheet().getRange('REPLACE_WITH_YOUR_RANGE_NAME');
  var actRng = SpreadsheetApp.getActiveSheet().getActiveCell();
  SpreadsheetApp.getUi().alert('The active cell is in named range ' + rangeIntersect(myRng, actRng));
}

function rangeIntersect (R1, R2) {
      return (R1.getLastRow() >= R2.getRow()) && (R2.getLastRow() >= R1.getRow()) && (R1.getLastColumn() >= R2.getColumn()) && (R2.getLastColumn() >= R1.getColumn());
    }

Modified from Reno Blair, Google App Script. Check if Range1 intersects Range2

user19349933
  • 11
  • 2