1

I am entering data into a Google Sheet. I would like to have a column where each row will be automatically timestamped with the DateTime in which I entered (or last updated) the contents of the row.

I know that I can do this manually by adding =now() to a cell, which will record the timestamp. However, this will update on any change to the doc, so I then need to copy and ctrl-shift-v to paste back just the value.

Would be more ideal if there was a formula that I could paste down the column which would only set now() as the value if there is a change in any of the selected columns in the given row.

Thoughts on how to do this?

  • Hi Simona, you need a script to do that, its called Google Apps Script. Do you have programming skills? – Jacob Jan Tuinstra Jun 17 at 9:20
  • 1
    I have used some c# and javascript before, but am still learning. Eager to try out new things, not afraid of scripts. – Simona Jun 17 at 9:22
1

The following onEdit trigger will add the date to the adjoining column:

Code

ES6/v8/arrow/shorthand if statement

const onEdit = (e) => e.range.columnStart === 1 ? e.range.offset(0,1).setValue(new Date()) : null

classic

function onEdit(e) {
  if(e.range.columnStart === 1) {
    e.range.offset(0,1).setValue(new Date())
  }
}

Explained

It reads as follows:

  • the script is only fired when an edit was made in column 1 (A)
  • if in column A, then add the date into the adjoining cell
  • else do nothing

You could use the Utilities class to format the date to the date/time notation

const onEdit = (e) => e.range.columnStart === 1 ? e.range.offset(0,1).setValue(Utilities.formatDate(new Date(), SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "dd-MM-yyyy HH:mm")) : null

but you can format the column from within the Google Sheet as well (Format > Number > Date time). Paste this script into the script editor (Tools > Script editor) and save. Now it should work.

| improve this answer | |
  • Works perfectly, thanks! – Simona Jun 17 at 12:21

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