Google Sheets – Add Current Date to Cell on Row Edit

google sheetsgoogle-apps-scriptgoogle-sheets-timestamp

I have a column A4:A (heading = Date), and other columns after it B4:O.

I want column A to fill in today's date automatically when I fill in any other columns (some of these B4:O maybe empty, but most will be filled) in that row. But I only want that to happen the first time I edit any other columns within that row.

What I've tried:

A7: =IF(B7 > 0,TODAY(),)

Best Answer

This question is similar to Is there any way to create a timestamp in a Google Spreadsheet? except here you want to date of the first edit, not the most recent one.

  • This cannot be achieved with spreadsheet formulas, because if a formula inserts today's date subject to some condition (nonempty cells), it will also insert tomorrow's date tomorrow, if the condition still holds.

  • This can be done manually, by pressing Ctrl; (Control/Command and semicolon) in Column A after making the first edit to a row.

  • Or this can be done automatically by a script.

Such as this one, which inserts the date of first edit to a row in the first column of that row. The date is inserted in ISO format yyyy-MM-dd. (It can be any other format supported by SimpleDateFormat.) 

function onEdit(e) {
  var row = e.range.getRow();
  var dateCell = e.range.getSheet().getRange(row, 1);
  if (!dateCell.getValue()) {
    dateCell.setValue(new Date()).setNumberFormat("yyyy-MM-dd");
  }
}

The script should be entered in Tools > Script Editor. It runs automatically on every edit, but it will not replace the date in column A if it's already there.

To have a script recording the date of last edit, change it 

function onEdit(e) {
  var row = e.range.getRow();
  var dateCell = e.range.getSheet().getRange(row, 1);
  dateCell.setValue(new Date()).setNumberFormat("yyyy-MM-dd");
}
Related Topic