Google Sheets – How to Execute Script Only in Certain Range on Specific Sheet

google sheetsgoogle-apps-scriptgoogle-sheets-timestamp

Currently I use this script to put a timestamp in a column when a value is entered into a cell in the same row but different column. 

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
    var r = e.source.getActiveRange();
    if (r.getColumn() == 4) {
      sheet.getRange(r.getRow(),r.getColumn()+4).setValue(new Date());
  }
}

It works fine except I only want the script to work within a certain range on certain sheets, ie only when a value is entered in a cell in the range D6:D100, will the corresponding cell in H6:H100 be updated with the timestamp, and only have this occur on Sheet1 and Sheet2 but not anywhere on Sheet3.

Best Answer

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var sheetName = sheet.getName();
  if ( sheetName == "Sheet1" || sheetName == "Sheet2" ) {
    var r = e.source.getActiveRange();
    if (r.getColumn() == 4 ) {
      var col = r.getColumn();
      if ( col >=  6 && col <= 100 ) {
        sheet.getRange(r.getRow(),8).setValue(new Date());
      }
    }
  }
Related Topic