based on the edited cell how do I return the namedRange the cell belongs in?

Question

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

Google Reference Link

Answer 1

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 from getNamedRange to onEdit.

• In your script, return namedRange; is used. But when getNamedRange is run using the installable OnEdit trigger, I thought that return namedRange; is not used.

Reference:

• Related thread.
  • Programmatically test if a Google Sheet cell is in a named range