Google-sheets – Dating When a Cell is Edited in Google Sheets

google sheetsgoogle-apps-scriptgoogle-sheets-timestamp

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.

Best Answer

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.

Related Solutions

Google-sheets – Auto date insert when opposite cell is populated

This answer is coming late to the party, but you can achieve what you want by installing this script:

function onEdit(event) {
  var eventRange = event.range;
  if (eventRange.getColumn() == 4) { // 4 == column D
    var columnARange = SpreadsheetApp.getActiveSheet().getRange(eventRange.getRow(), 1, eventRange.getNumRows(), 1);
    var values = columnARange.getValues();
    for (var i = 0; i < values.length; i++) {
       values[i][0] = new Date();
    }
    columnARange.setValues(values);
  }
}

EDIT: It seems you just want to trigger the script when column D is modified, so I have modified the code accordingly.

To install it, go to Tools -> Script editor, paste the code above, and save. From then on, every time you edit a cell in column D, the current timestamp will be inserted in column A.

Google-sheets – Add a ‘creation date’ value when a new line is edited

You could add a trigger that runs when new rows are added to the spreadsheet:

function onModification(event) {
  var sheet = event.source.getActiveSheet();
  if (event.changeType == "EDIT") { // A cell is edited. Other values include "INSERT_ROW" (when a new row is inserted)
    fillEmptyRows(sheet);
  }
}

function fillEmptyRows(sheet) {
    var range = sheet.getRange(1, 1, sheet.getMaxRows(), 1);
    var firstColumnValues = range.getValues();
    for (var i = 0; i < firstColumnValues.length; i++) {
      if (firstColumnValues[i][0] == "") { // Column 0 is A
         firstColumnValues[i][0] = new Date();
      }
    }
    range.setValues(firstColumnValues);
}

You need to register this trigger manually: Click Resources -> Triggers -> Add new trigger, select the onModification method and select on modification from the right-most dropdown menu.

When a cell is edited, the script will fill all blank cells in column A with the current timestamp.

I have set up an example spreadsheet, feel free to play with it.

Best Answer

Aside

Related Solutions

Google-sheets – Auto date insert when opposite cell is populated

Google-sheets – Add a ‘creation date’ value when a new line is edited

Related Topic