Blog

How to add timestamp when any cell in column changes value - google sheet

How to add timestamp when any cell in column changes value – google sheet

by

As DevOps Engineer, there are cases where logging and monitoring is crucial as well as an important part, either you have shared inventory sheet or sales and purchase sheet with team members, you need to make sure when any crucial column’s value changes, we note timestamp, to be up-to-date. This tutorial will help you to automatically add/edit timestamp when any cell’s value changes Share your google sheet

Add/Edit timestamp whenever any user changes the value to a particular cell, follow this tutorial to know

Step by step tutorial to add automatic timestamp integration in google sheet

Before getting started:

Prerequisite: You need to have 1 working Gmail or GSuite Account

1. Create a new google sheet / edit existing google sheet

2. Add required columns

3. Add timestamp column

You can add timestamp column after main column for which you are logging timestamp

4. Change timestamp format

  • Select timestamp column
  • Click on Format
  • Click on Number
  • Select Date and Time format that suits your requirement

Pro-tip: While trying this tutorial, make sure you logged in with only 1 Gmail/GSuiteaccount from the browser

5. Go to Script Editor

  • Click on Tools
  • Select – Script Editor

It will pop up google apps script window in new tab

  • Name your Apps Script

Copy below code 

function onEdit(e) {
  var row = e.range.getRow();
  var col = e.range.getColumn();

  if(col == 1){
    e.source.getActiveSheet().getRange(row,2).setValue(new Date());
  }
}
  • it will add current date in cell in column number 2 when cell in column number 1 is added/edited

6. Add Trigger

  • In left pane, Select Triggers (Allow pop-up)
  • Click on “Add Trigger”

Choose mentioned details

  1. Choose which function to run: onEdit
  2. Choose which deployment should run: Head
  3. Select event source: From SpreadSheet
  4. Select event type: On edit
  5. Failure notification settings: Notify me immediately

Click on “Save”

It will prompt to add permission on your google account, accept it

9. Finish

Add entry in specific column and see timestamp getting added automatically

You can add timestamp for whichever and as many column as you want

Let us know your views in the comment section below

Drafted On,
22nd January 2022
DevOps @identicalCloud.com

References

[1] https://www.google.com/sheets/about/
[2] https://developers.google.com/apps-script

Post Views: 41,863

4 thoughts on “How to add timestamp when any cell in column changes value – google sheet”

  1. this works really well, – simple and runs perfectly.

    How I can I change this so that i can Specify to which cell the timestamp is added? What I’d like to achieve is the time stamp is added one row below, and 8 columns of the left of cell being added to/updated.

    (No idea why every word is being Capitalized here!)

    Thanks,
    Jim

    Reply

  2. Hello Sir, Greetings of the Day.! Thanks a lot for your tutorials. I want to add a time stamp on only numerical values. Now the code allows all characters including special characters also. But I want to restrict it to only numerical values.
    Thanks – B4ALLB4U

    Reply

  4. Hello, How would I got about making multiple arguments? I currently have 3 Different column entries I would like the timestamp to work for. I created 3 Separate entries but it is using an order of precedence it looks like.

    Reply

Leave a Comment