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

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,
4th December 2021
DevOps @identicalCloud.com

References

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

Summary
Add timestamp when any cell changes value - google sheet
Article Name
Add timestamp when any cell changes value - google sheet
Description
How to add timestamp when any cell in column changes value in google sheet. log timestamp when cell in excel changes value
Author
Publisher Name
IdenticalCloud
Publisher Logo

Leave a Comment