Google Script return date when range is edited

Question

I'm currently working on a script for a spreadsheet.

Desired Behavior: When cells F-I are edited, the date of the most recent edit should be returned in cell L of the corresponding row.

I've found two samples of code, the first returns the date in cell L when ANY field in the row is edited. This behavior is acceptable, but not ideal as it applies to any col

function onEdit() {
 var s = SpreadsheetApp.getActiveSheet();
 var r = s.getActiveCell();
 if( r.getColumn() != 2 ) {
   var row = r.getRow();
   var time = new Date();
   time = Utilities.formatDate(time, "GMT-05:00", "yyyy-MM-dd");
   SpreadsheetApp.getActiveSheet().getRange('L'+row.toString()).setValue(time);
  };
 };

The second should return the date one cell to the right of the edited column. This behavior is not desired as it may overwrite other important data.

function onEdit(e) {
var s = SpreadsheetApp.getActiveSheet();
var cols = [1, 7, 13, 19, 25, 31]
if (s.getName() !== "Sheet1" || cols.indexOf(e.range.columnStart) == -1) return;
s.getRange(e.range.rowStart, e.range.columnStart + 1)
    .setValue(new Date());
};

Any help or advice on editing these to behave more closely to the desired results would be much appreciated. Thanks!

JPV JPV · Accepted Answer · 2014-12-07T18:42:19

Something like this should work:

function onEdit(e) {
var ss = e.source.getActiveSheet();
var watchedCols = [6, 7, 8, 9]
if (watchedCols.indexOf(e.range.columnStart) === -1) return;
ss.getRange(e.range.rowStart, 12)
    .setValue(e.value ? Utilities.formatDate(new Date(), "GMT-05:00", "yyyy-MM-dd") : null)
}

Note that the script will work on every sheet of your google spreadsheet. If you want to limit it to only one sheet (and also exclude edits in the first row (headers ?)), you could try something like this:

function onEdit(e) {
var ss = e.source.getActiveSheet();
var watchedCols = [6, 7, 8, 9];
var watchedSheet = 'Sheet1'; //change name if needed
if (watchedCols.indexOf(e.range.columnStart) === -1 || ss.getName() !== watchedSheet || e.range.rowStart < 2) return;
ss.getRange(e.range.rowStart, 12)
    .setValue(e.value ? Utilities.formatDate(new Date(), "GMT-05:00", "yyyy-MM-dd") : null)
}

Hope that helps ?

Google Script return date when range is edited

1 Answers