CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Myupe
Myupe

Reputation: 545

Google Sheets scripts - get named range from active cell

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!

Upvotes: 0

Views: 4605

Answers (3)

user19349933
user19349933

Reputation: 11

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

Upvotes: 1

Myupe
Myupe

Reputation: 545

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

Upvotes: 1

Liora Haydont
Liora Haydont

Reputation: 1283

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

Upvotes: 4

Related Questions