How to Add a Timestamp when Cells Change in Google Sheets

If you want to keep track of the exact date and time when cells change in your spreadsheet, you have several options. You can write Apps Script that will automatically record timestamps (and usernames) for you whenever certain cells change. If you want control over when the timestamp gets recorded, you could add a tick-box button to record the timestamp whenever you tick it.

Or, for those of us that like no code solutions, you can record timestamps manually. There are handy keyboard shortcuts that allow quick entry of dates and times into any cell.

Finally, you can do nothing! If you’re not technical and just want to find out when something changed in your sheet, you can simply look back through your file version history, or a specific cell edit history to see everything that changed and who changed it! Google sheets records all this for you automatically.

Track cell changes using the file version history and cell history

One of the most useful features of Google Sheets is their in-built edit tracking. Every time anyone changes a sheet, a new revision gets created and the changes get tracked and stored.

How long are these revisions kept? How many can you have? I hear you ask, but unfortunately, the exact answer isn’t clear and will probably be subject to changes in the future. At the moment, it looks like Sheets files can have as many revisions as you like, but over time revisions may get merged to save space. So you can’t count on this technology to record every change to every sheet forever.

“A change to a Docs, Sheets, or Slides file gets a new revision. Each time the content changes, Drive creates a new revision history entry for that file. However, these editor file revisions may be merged together.”

https://developers.google.com/drive/api/guides/change-overview

For a mission critical change log, I’d suggest that you use the script examples below to capture whenever important cells change, and write out the value of what changed and who changed it to a separate sheet. Effectively keep a change log of your own in a sheet.

But if you don’t need that level of tracking, here’s how to see a sheets version history and cell edit history.

File version history

To see all the changes to your spreadsheet file over time use File > Version history > See version history from the menu. Or alternatively, use the keyboard shortcut Ctrl+alt+shift+H (Mac users switch Ctrl for ⌘).

This will open the Version history toolbar on the right side of your screen. This shows you all the versions of a file that Google has stored. You can use the little arrow to drill into any day and see all the changes in that day, the user that made them and the time. If you click on a specific version you can keep it, name it and you will see a summary of the changes made displayed on the left.

Cell edit history

If you right-click on any cell in a Google Sheet the context menu will show you the option to ‘Show Edit History’.

This will then show you all the changes made to a cell, when they were made and who made them. You can scroll through the entire history of a cell using the little arrow buttons on the top right of the pop-up window that display the changes.

Manually enter timestamps using keyboard shortcuts

For quick manual timestamp entries, you can insert the current date, current time or current date and time into any cell using a keyboard shortcut. I listed these below.

Action	Keyboard Shortcut
Windows, ChromeOS
Insert current date	Ctrl+;
Insert current time	Ctrl+shift+;
Insert current date and time	Ctrl+alt+shift+;
Mac
Insert current time	⌘+shift+;
Insert current date	⌘+;
Insert current date and time	⌘+option+shift+;

Adding timestamps with a checkbox button on each row

If you don’t want to use keyboard shortcuts and would rather click a button, you can easily create a checkbox on every row that records a timestamp. To see this in action, make a copy of our example spreadsheet and tick a box in the first column. You should see a timestamp added in the second column and your email address in the third.

Here’s the code we used to watch the onEdit simple trigger which is fired every time a cell changes on a Sheet and responds appropriately, making our checkboxes act like buttons and output a timestamp and username.

Also, note that your timestamp column needs to be formatted as a Date and Time (‘Format > Number > Date time‘) otherwise you’ll only see the Date by default. To add tick boxes to any column, just use ‘Insert > Tickbox‘ from the menu.

//onEdit runs for every cell edit, it's a simple trigger you don't need to set it up, just write this function
function onEdit(e) {
  if(e.range.getSheet().getName() == 'Sheet1') {
    //if column 1 (A), and any row after 1 was ticked
    if (e.range.getColumn() == 1 && e.range.getRow() >= 1 && e.value) {
      //add a timestamp next to the tickbox
      SpreadsheetApp.getActiveSheet().getRange(e.range.getRow(),2).setValue(new Date());
      //(optionally) write out the user who's ticking the box
      SpreadsheetApp.getActiveSheet().getRange(e.range.getRow(),3).setValue(e.user);
      e.range.uncheck(); //uncheck the tickbox
    } 
  }
}Code language: JavaScript (javascript)

Adding timestamps automatically using apps script

We can change the previous script to make it automatically add a timestamp next to a cell whenever the value of that cells changes. Here’s how that looks:

function onEdit(e) {
  if(e.range.getSheet().getName() == 'Sheet1') {
    //if column 5 (E), and any row after 1 was changed
    if (e.range.getColumn() == 5 && e.range.getRow() >= 1) 
      //add a timestamp next to the tickbox
      SpreadsheetApp.getActiveSheet().getRange(e.range.getRow(),6).setValue(new Date());
      //(optionally) write out the user who's ticking the box
      SpreadsheetApp.getActiveSheet().getRange(e.range.getRow(),7).setValue(e.user);      
    }
  }
}Code language: JavaScript (javascript)

If you want to see this working, make a copy of our example spreadsheet and edit it to work with exactly the cells and rows you want.