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

protected by Community Jun 25 at 19:30

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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