Programmatically test if a Google Sheet cell is in a named range

Question

I'm writing a Google Sheets onEdit() trigger and it only needs to do something if any of the edited cells are in a named range on the spreadsheet.

I have this:

function onEdit(e) {
  var editRange = e.range;

  var numRows = editRange.getNumRows();
  var numCols = editRange.getNumColumns();

  for (var i = 1; i <= numRows; i++) {
    for (var j = 1; j <= numCols; j++) {
      var cell = editRange.getCell(i,j);

      if (isInRange(cell, myNamedRange)) {
         /* Do something clever */
      }
    }
  }
}

But I'm having trouble writing the isInRange() function. It feels like it should be a built-in spreadsheet function, but I can't find anything that looks likely.

Update: It struck me overnight that there might be an easier approach. What I actually need is the intersection between two ranges.

Answer 1

• You want to check whether a cell is included in the named range.
• You want to create the function of isInRange.
• You want to achieve this using Google Apps Script.

Flow:

The flow of isInRange is as follows.

1. Retrieve named ranges in the active sheet.
2. Retrieve the named range using myNamedRange.
3. Return true when cell is in the named range of myNamedRange.
4. Return false when cell is NOT in the named range of myNamedRange.
5. An error occurs when myNamedRange was not found.

Sample script:

function isInRange(cell, myNamedRange) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var namedRanges = sheet.getNamedRanges();
  for (var i = 0; i < namedRanges.length; i++) {
    var nr = namedRanges[i];
    if (nr.getName() == myNamedRange) {
      var range = nr.getRange();
      var startRow = range.getRow();
      var endRow = startRow + range.getNumRows() - 1;
      var startColumn = range.getColumn();
      var endColumn = startColumn + range.getNumColumns() - 1;
      return cell.getRow() >= startRow && cell.getRow() <= endRow && cell.getColumn() >= startColumn && cell.getColumn() <= endColumn;
    }
  }
  throw new Error(myNamedRange + " was not found.");
}

• When this sample script of isInRange is used for your onEdit, when cell is in the named range of myNamedRange, the if statement of if (isInRange(cell, myNamedRange)) {} is true.

Note:

• In this sample script, the named ranges are retrieved from the active sheet. If you want to change this, please modify the script.

References:

• getNamedRanges()
• Class NamedRange

---

Added:

• You want to retrieve the intersection ranges of two ranges.
  • For example, when the named range and the inputted range are "A1:B3" and "B3:C4", respectively, you want to retrieve "B3" as the intersection range.

The flow of this sample script is as follows.

Flow:

1. Retrieve a1Notations from "inputRange".
2. Retrieve a1Notations from "myNamedRange".
3. Retrieve intersection ranges.

In this sample script, the result value is returned an array including the a1Notations. Because the input range might be not continue, and when the array of a1Notations can be also used for RangeList.

Sample script:

function myFunction() {
  var myNamedRange = "sampleNamedRange";  // Please set the name of the named range.
  var inputRange = SpreadsheetApp.getActiveSheet().getRange("B3:C4");  // Please set the 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 result = [];
  var sheet = SpreadsheetApp.getActiveSheet();
  var namedRanges = sheet.getNamedRanges();
  for (var i = 0; i < namedRanges.length; i++) {
    var nr = namedRanges[i];
    if (nr.getName() == myNamedRange) {
      // 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)});
    }
  }
  
  Logger.log(result)
}

Answer 2

I've accepted Tanaike's answer as it gave me a lot of clues that I've found useful while working on this. But, actually, I ended up writing a rangeIntersection() function as I mentioned in this morning's update to my question.

The code is here in case anyone else might find it useful.

function rangeIntersection(range1, range2) {
  var sheet1 = range1.getSheet();
  var sheet2 = range2.getSheet();

  if (sheet1.getSheetId() !== sheet2.getSheetId()) {
    return false;
  }

  if (range1.getLastColumn() < range2.getColumn() ||
      range1.getColumn() > range2.getLastColumn() ||
      range1.getLastRow() < range2.getRow() ||
      range1.getRow() > range2.getLastRow()) {
    return false;
  }

  var intersectRange = sheet1.getRange(
    Math.max(range1.getRow(), range2.getRow()),
    Math.max(range1.getColumn(), range2.getColumn()),
    Math.min(range1.getLastRow(), range2.getLastRow()) - Math.max(range1.getRow(), range2.getRow()) + 1,
    Math.min(range1.getLastColumn(), range2.getLastColumn()) - Math.max(range1.getColumn(), range2.getColumn()) + 1,
  );

  return intersectRange;
}