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
Choose which function to run: onEdit
Choose which deployment should run: Head
Select event source: From SpreadSheet
Select event type: On edit
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
4 thoughts on “How to add timestamp when any cell in column changes value – google sheet”
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!)
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
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.
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
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
This is amazing! But how would I limit it to for example just the first two sheets of a spreadsheet?
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.