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