Skip to main content

How to insert timestamp automatically when data is updated in another column in Google sheet?

Author: Xiaoyang Last Modified: 2017-10-30

If you have a range of cells and you want to insert a timestamp automatically into the adjacent cell when the data is modified or updated in another column. How could you solve this task in Google sheet?

Insert timestamp automatically when data is updated in another column with script code


Insert timestamp automatically when data is updated in another column with script code

The following script code can help you to finish this job quickly and easily, please do as this:

1. Click Tools > Script editor, see screenshot:

2. In the opened project window, please copy and paste the below script code to replace the original code, see screenshot:

function onEdit(e)
{ 
  var sheet = e.source.getActiveSheet();
  if (sheet.getName() == "order data") //"order data" is the name of the sheet where you want to run this script.
  {
    var actRng = sheet.getActiveRange();
    var editColumn = actRng.getColumn();
    var rowIndex = actRng.getRowIndex();
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
    var dateCol = headers[0].indexOf("Date") + 1;
    var orderCol = headers[0].indexOf("Order") + 1;
    if (dateCol > 0 && rowIndex > 1 && editColumn == orderCol) 
    { 
      sheet.getRange(rowIndex, dateCol).setValue(Utilities.formatDate(new Date(), "UTC+8", "MM-dd-yyyy")); 
    }
  }
}

Note: In the above code, order data is the sheet name that you want to use, Date is the column header which you want to insert timestamp, and Order is the column header which cell values you want to be updated. Please change them to your need.

3. Then save the project window, and enter a name for this new project, see screenshot:

4. And then go back to the sheet, now, when the data in Order column is modified, the current timestamp is inserted into the cell of Date column automatically which is adjacent to the modified cell, see screenshot:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (62)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi.. Thank you very much,
I was struggling to get the below excel macro script into google sheet script. Can you please help me out.
"Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 10 Then
Application.EnableEvents = False
If Target.Value <> """" Then
Target.Offset(, 6).Value = Environ(""username"")
Else
Target.Offset(, 6).ClearContents
End If
Application.EnableEvents = True
End If

If Target.Column = 11 And Target.Column Mod 1 = 0 And Target.Row >= -8 Then
For Each c In Target
If c.Value = c.Offset(0, -4).Value Then
c.Offset(0, -8).Value = Format(Date, ""DD/MMM/YYYY"")
Else
c.Offset(0, -8).Value = """"

End If
Next c

End If


If Target.Column = 10 And Target.Column Mod 3 = 1 And Target.Row >= 6 Then
For Each c In Target
If c.Value = """" Then
c.Offset(0, 7).Value = """"
Else
c.Offset(0, 7).Value = Format(Time, ""h:mm AM/PM"")
End If
Next c
End If
End Sub



"

This comment was minimized by the moderator on the site
This worked for me perfectly. I had three separate columns that I had to update with static dates. Just made three diff arguments for each column and it worked. Thank you!
This comment was minimized by the moderator on the site
Can u show how do you do that?
This comment was minimized by the moderator on the site
How do i apply this to work in multiple tabs in google sheet?
This comment was minimized by the moderator on the site
I need this to apply in multiple sheets. How do I do that
This comment was minimized by the moderator on the site
Replace e.source by SpreadsheetApp.getActiveSpreadsheet()
This comment was minimized by the moderator on the site
TypeError: Cannot read property 'source' of undefined
onEdit @ Code.gs:3pl. give solution ,,, on google sheet
This comment was minimized by the moderator on the site
TypeError: Cannot read property 'source' of undefined
onEdit @ Code.gs:3
This comment was minimized by the moderator on the site
THANK YOU IT WORKED
This comment was minimized by the moderator on the site
Hi all, will this script work for the column populated by a formula or auto populated. depending on a auto populated column how can i generate the the updated date.
This comment was minimized by the moderator on the site
Hello I found this very helpful yet have a additional step in my spreadsheet. My Column A data remains untouched which is what I want. I need Column B when updated by staff date&time stamp Column C. I have this so far BUT I also need Column D when updated to date&time stamp Column E. So B&C interact and D&E interact. Advice?
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations