3

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

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.

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.