How to restrict a Google Script to a certain cell range?

Question

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);
    };
  };
 };

Answer 1

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.

Answer 2

This is how the final code looks like :)

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  var range = s.getRange("C3:W26"); 
  var r = s.getActiveCell();
  if (r.getRow() >= range.getRow() && r.getRow() <= range.getLastRow() && r.getColumn() >= range.getColumn() && r.getColumn() <= range.getLastColumn()) {
  // do something
  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);
    };
  };
 };
};

Answer 3

The solution shared by the OP but taking advantage of the on edit event object

function onEdit(e) {
  /** Settings */
  var range = s.getRange("C3:W26"); 
  var name = "Estado Paises";
  var exceptionColumn = 2;

  var s = e.range.getSheet();
  
  /** Conditional Statement */
  if(
     e.range.rowStart >= range.getRow() && e.range.rowStart <= range.getLastRow() &&
     e.range.columnStart >= range.getColumn() && e.range.columnStart <= range.getLastColumn()
  ) {
     // do something
     if( s.getName() == name ) { //checks that we're on the correct sheet
       if( e.range.columnStart != exceptionColumn ) { //checks the column
         var date = Utilities.formatDate(new Date(), "GMT-03:00", "dd/MM/yy");
         s.getRange('X' + e.range.rowStart).setValue(date);
       }
     }
   } else {
     // do nothing
   }
}

---

Technically an on edit triggered script will be executed whenever the spreadsheet be edited by using the Google Sheets user interface so we can only limit only some part of the script to be executed by using conditional expressions on JavaScript statements like if...else, switch, just to mention the most commonly used.

The on edit event object has several properties that might be helpful specially the range that returns a Class Range object for the edited range but also include the properties rowStart, rowEnd, columnStart and columnEnd that return the row and column numbers of the top-left and bottom-right cells respectively of the edited range.

There have being reports where the "getActive..." methods don't return the correct objects i.e. when there is a delay on the execution of the function called by the on edit trigger, so, in order to prevent this, use the event object properties instead of those methods.

Related

• Trying to run two scripts on same sheet and getting error