function getNamedRange( e ) {
const sheet = SpreadsheetApp.getActiveSheet();
const activeRange = e.range;
const namedRange = sheet.getNamedRange( activeRange.getA1Notation() );
//alert named range
SpreadsheetApp.getUi().alert( namedRange );
return namedRange;
}
I have tried multiple ways of doing this to no avail.
Main goal: get the name of the named range where the edited cell is found
So if A1:C5
was a named range of "firstRange"
and I edited cell A2
, the onEdit(e)
would run getNamedRange(e)
and alert "firstRange"
I have tried getName()
and all sorts of combos using the reference section
CodePudding user response:
First, when I saw this question and Programmatically test if a Google Sheet cell is in a named range, also I thought that this might be the same situation. But, I noticed that in my answer, the intersection ranges between the specific range and the specific named range are retrieved. I thought that the basic method is the same. So in order to use my answer for this question, it is required to modify a little. So in this answer, I would like to propose the sample script for achieving the goal by modifying it.
Sample script 1:
When this sample script is modified for your script, it becomes as follows.
function getNamedRange(e) {
var inputRange = e.range;
var columnToLetter = function (column) { // <--- https://stackoverflow.com/a/21231012/7108653
var temp, letter = '';
while (column > 0) {
temp = (column - 1) % 26;
letter = String.fromCharCode(temp 65) letter;
column = (column - temp - 1) / 26;
}
return letter;
};
var res = [];
var result = [];
var sheet = SpreadsheetApp.getActiveSheet();
var namedRanges = sheet.getNamedRanges();
for (var i = 0; i < namedRanges.length; i ) {
var nr = namedRanges[i];
// Retrieve a1Notations from "inputRange".
var iStartRow = inputRange.getRow();
var iEndRow = iStartRow inputRange.getNumRows() - 1;
var iStartColumn = inputRange.getColumn();
var iEndColumn = iStartColumn inputRange.getNumColumns() - 1;
var irA1Notations = [];
for (var j = iStartRow; j <= iEndRow; j ) {
var temp = [];
for (var k = iStartColumn; k <= iEndColumn; k ) {
temp.push(columnToLetter(k) j);
}
Array.prototype.push.apply(irA1Notations, temp);
}
// Retrieve a1Notations from "myNamedRange".
var namedRange = nr.getRange();
var nStartRow = namedRange.getRow();
var nEndRow = nStartRow namedRange.getNumRows() - 1;
var nStartColumn = namedRange.getColumn();
var nEndColumn = nStartColumn namedRange.getNumColumns() - 1;
var nrA1Notations = {};
for (var j = nStartRow; j <= nEndRow; j ) {
for (var k = nStartColumn; k <= nEndColumn; k ) {
nrA1Notations[columnToLetter(k) j] = null;
}
}
// Retrieve intersection ranges.
result = irA1Notations.filter(function (e) { return nrA1Notations.hasOwnProperty(e) });
if (result.length > 0) {
res.push(nr.getName())
}
}
if (res.length == 0) return;
SpreadsheetApp.getUi().alert(res.join(","));
}
Sample script 2:
In this case, I thought that the following simple script might be able to be used.
function getNamedRange(e) {
const range = e.range;
const sheet = SpreadsheetApp.getActiveSheet();
const r = sheet.getNamedRanges().filter(r => {
const temp = r.getRange();
const startRow = temp.getRow();
const endRow = startRow temp.getNumRows();
const startCol = temp.getColumn();
const endCol = startCol temp.getNumColumns();
return (range.rowStart >= startRow && range.rowStart <= endRow && range.columnStart >= startCol && range.columnStart <= endCol) ? true : false;
});
if (r.length == 0) return;
SpreadsheetApp.getUi().alert(r.map(f => f.getName()).join(","));
}
Note:
When you edit a cell, when the edited cell is included in the named range, a dialog is opened. And, you can see the name of the named range.
From this question, it seems that you are using
getNamedRange
as the installable OnEdit trigger. In the above scripts, you can also use the simple trigger. So you can also modify the function name fromgetNamedRange
toonEdit
.In your script,
return namedRange;
is used. But whengetNamedRange
is run using the installable OnEdit trigger, I thought thatreturn namedRange;
is not used.
Reference:
- Related thread.