Google Sheets – How to Restrict a Script to a Certain Cell Range

google sheetsgoogle-apps-scriptgoogle-apps-script-triggers

I'm working in an automatic script that shows the last modified dates of some cells within a sheet. So far, I've succeed with the sheet restriction adding an if condition, but I want to restrict this to a certain cell range (e.g., C3:W26). Do you know how I can do it?

This is my script:

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  var r = s.getActiveCell();
  if( s.getName() == "Estado Paises" ) { //checks that we're on the correct sheet
    if( r.getColumn() != 2 ) { //checks the column
      var row = r.getRow();
      var time = new Date();
      time = Utilities.formatDate(time, "GMT-03:00", "dd/MM/yy");
      SpreadsheetApp.getActiveSheet().getRange('X' + row.toString()).setValue(time);
    };
  };
 };

Best Answer

A fairly readable way of stating the condition of cell r being in a range is 

var range = s.getRange("C3:W26"); 
if (r.getRow() >= range.getRow() && r.getRow() <= range.getLastRow() && r.getColumn() >= range.getColumn() && r.getColumn() <= range.getLastColumn()) {
  // do something
}

One could also hardcode the row/column boundaries, but this is not as easy to modify later, especially if your range is something like U2:BB8.

Related Topic