Dating When a Cell is Edited in Google Sheets

Question

I want to autofill the date of an edit in one cell into the next cell. Specifically:

• When I enter something into any cell in column 2, I want the cell next to it in column 3 to autofill the date.
• When I enter something into any cell in column 5, I want the cell next to it in column 6 to autofill the date.
• ... same for columns 8 and 11

I've seen similar questions, but I don't know the coding well enough to alter the answers to my needs. I used this as a basis (found here: Auto-updating column in Google Spreadsheet showing last modify date):

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  var r = s.getActiveCell();
  if( r.getColumn() != 2 ) { //checks the column
    var row = r.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, "GMT-08:00", "MM/DD/yy, hh:mm:ss");
    SpreadsheetApp.getActiveSheet().getRange('B' + row.toString()).setValue(time);
  };
 };

It works great except that I want to do the same thing with columns 5, 8, and 11. When I copied the code and changed the number, it only works with column 11, not 2, 5, or 8.

Answer 1

This is a much shorter script that does it:

function onEdit(e) {
  if ([2, 5, 8, 11].indexOf(e.range.columnStart) != -1) {
    e.range.offset(0, 1).setValue(new Date());
  }
}

Where [2, 5, 8, 11] is the list of columns of interest. The offset(0, 1) means same row, 1 column to the right.

If you want a particular format of the timestamp, add setNumberFormat like so: (reference for formats)

e.range.offset(0, 1).setValue(new Date()).setNumberFormat("yyyy.MM.dd at HH:mm");

Aside

Various versions of the script you found are now entrenched in forums despite many things being wrong with it. It does not preserve date structure, replacing it with a string. It is tied to a timezone. It does not care about Daylight Saving Time. It uses too many API calls.