Is it possible to create a "last updated" field under Google Sheets?

I have a status column, where the status progresses something like:

New -> Pending -> Started -> Done

I would like to add a "status last changed on XXX" column, where XXX would be a timestamp for when the status was changed, so that then I can filter for example anything that hasn't changed status in over a week (or color-code it/etc.)

I know I can in theory look this up under the revision history, but that is quite a tedious work-around

I am looking for a way that if a single cell is modified, then another specific cell will be updated with the current timestamp

  • @Rubén not quite, that seems to grab any change and update a single field - I want if a single cell is updated, to change the time in a corresponding cell in the same row Apr 20, 2016 at 23:07
  • 1
    Regarding the possible duplicate. If you are looking for a "automatic way" for inserting the timestamp, the answer will be practically the same as the code from the accepted answer could be "easily adopted" by the other hand if you are looking for keyboard combination or other solution like that there are several question with that kind of answers. Apr 20, 2016 at 23:20

1 Answer 1


Here is an easily customizable function for this purpose: enter the numbers of the column to watch and the column for timestamp on appropriate lines

function onEdit(e) {
  var colWatch = 5;      // column to watch, 5 = "E"
  var colRecord = 6;     // column for timestamp,  6 = "F"
  if (e.range.getColumn() == colWatch) {
    e.range.offset(0, colRecord - colWatch).setValue(new Date());
  • I seem to get an "TypeError: Cannot read property "range" from undefined. (line 5, file "Code")" error when I tried that Apr 20, 2016 at 23:36
  • 1
    This is not a function you run manually, it will run when triggered.
    – user79865
    Apr 20, 2016 at 23:37
  • @user2813274 To learn how to test a trigger function on GAS see stackoverflow.com/a/16089067/1595451 Apr 20, 2016 at 23:41

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