3

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.

  • Sorry, it should have said "column." 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. When I enter something into any cell in column 8, I want the cell next to it in column 9 to autofill the date. When I enter something into any cell in column 11, I want the cell next to it in column 12 to autofill the date. – Josh Kelver Jul 19 '17 at 20:44
3

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.

  • This error was returned when I ran the script: TypeError: Cannot read property "range" from undefined. (line 2, file "Date") – Josh Kelver Jul 20 '17 at 23:49
  • You tried to run the script manually. It's not meant for that. It runs automatically on edit. – user135384 Jul 21 '17 at 0:49
  • Didn't seem to run automatically for me. Is there anything else that needs to be done? What happens if you have multiple sheets? – skube Aug 13 '18 at 13:56

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