How can I check if a range is in a named range?

Question

I need to check if the edited cell in an onEdit function is part of a named range. Something like this in Excel VBA:

If Not Intersect(Edited Cell, Range("NamedRange")) Is Nothing Then ...

Any ideas? Thanks in advance!

Answer 1

Something fairly rudimentary, at least, will give you ideas:

function _check_rows(range, namedRange) {
  var _result = false,
      _range_row_begin,
      _range_row_end,
      _namedRange_row_begin,
      _namedRange_row_end;
  if (range && namedRange) {
    _range_row_begin = range.getRow(),
    _range_row_end = range.getLastRow(),
    _namedRange_row_begin = namedRange.getRow(),
    _namedRange_row_end = namedRange.getLastRow(),
    _result = _range_row_begin >= _namedRange_row_begin && _range_row_end <= _namedRange_row_end;
  }
  return _result;
}

function _check_columns(range, namedRange) {
  var _result = false,
      _range_column_begin,
      _range_column_end,
      _namedRange_column_begin,
      _namedRange_column_end;
  if (range && namedRange) {
    _range_column_begin = range.getColumn(),
    _range_column_end = range.getLastColumn(),
    _namedRange_column_begin = namedRange.getColumn(),
    _namedRange_column_end = namedRange.getLastColumn(),
    _result = _range_column_begin >= _namedRange_column_begin && _range_column_end <= _namedRange_column_end;
  }
  return _result;
}

function _setNamedRange(name) {
  var _ss = SpreadsheetApp.getActiveSpreadsheet();
  _ss.setNamedRange(name, _ss.getRange('Sheet 1!C3:D6'));
}

function Intersect(range, namedRange) {
  var _result = false,
      _ss,
      _sheet,
      _range,
      _namedRange;
  if (range && namedRange) {
    _ss = SpreadsheetApp.getActiveSpreadsheet(),
    _sheet = _ss.getActiveSheet(),
    _namedRange = _ss.getRangeByName([_sheet.getName(), '!', namedRange].join('')),
    _range = _sheet.getRange(range),
    _result = _check_rows(_range, _namedRange) && _check_columns(_range, _namedRange);
  }
  return _result;
}

function test_Intersect() {
  var _currentCell = 'C4',
      _namedRange = 'NamedRange'; // NamedRange = 'Sheet 1!C3:D6';
  _setNamedRange(_namedRange);
  if (!Intersect(_currentCell, _namedRange))
    Logger.log('TRUE');
  else
    Logger.log('FALSE');
}

Answer 2

This function will return the indices of a cell in a named range or otherwise return -1s as the indices. Notice that it returns an array with two values representing the row and column indices in the named range.

const indexOfCellInNamedRange = (cell, range_name) => {
    
    const namedRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(range_name);
    if(cell.getSheet().getName() !== namedRange.getSheet().getName()) return [-1, -1]
    
    const start_row = namedRange.getRow();
    const end_row = start_row + namedRange.getNumRows() - 1;
    const start_col = namedRange.getColumn();
    const end_col = start_col + namedRange.getNumColumns() - 1;
 
    const row_index = Math.max( cell.getRow() - start_row, -1);
    const col_index = Math.max(cell.getColumn() - start_col, -1);
    if(row_index > end_row)return [-1, -1]
    if(col_index > end_col)return [-1, -1]
    return [row_index, col_index]
}