Question

This content is likely not relevant anymore. Try searching or browse recent questions.

8/19/19

Automatic Timestamp when a Cell on the same row gets Updated. 2 Recommended Answers 60 Replies 130 Upvotes

Hi,

So I'm trying to make a spreadsheet with multiple rows and columns (obviously).

Each row belongs to a customer, and each column contains a piece of data for them.

I want to add a column on the very end of the spreadsheet so that whenever a customer's row is updated in any cell along the row, the cell in the very end column will bring up a date and time stamp of when the row was last updated.

I've been googling this issue for days, and I know that I'll have to use a script, but what script do I use?

Could somebody help me out please?

I've attached a link to a sample spreadsheet that you can play around with and figure out the script.

Thanks in advance for your help!

https://docs.google.com/spreadsheets/d/1vo26TSMXo_f2XKe3l0qn1omA7JyYxSkWsL-DOkfDVAg/edit?usp=sharing

Details

Sheets, Mac OS, Personal use

Recommended Answer Recommended Answers (2)

Relevant Answer Relevant Answers (0)

All Replies (60)

Abbas Abdulla Gold Product Expert

8/19/19

Recommended Answer

Relevant Answer

Hi,

Try the below script:

function onEdit(e) {

var ss = SpreadsheetApp.getActiveSheet();

var r = ss.getActiveCell();

//1.Change 'Sheet1' to be matching your sheet name

  if (r.getColumn() < 9 && ss.getName()=='Sheet1') { // 2. If Edit is done in any column before Column (I)  And sheet name is Sheet1 then:

var celladdress ='I'+ r.getRowIndex()

ss.getRange(celladdress).setValue(new Date()).setNumberFormat("MM/dd/yyyy hh:mm");

}

};

Google user

recommended this

Original Poster

Jaméz Cartwright

marked this as an answer

Recommended based on info available

Last edited 8/19/19

Helpful?

Jaméz Cartwright Original Poster

8/19/19

Recommended Answer

Relevant Answer

Hi Abbas,

Thank you so much!

Not all heroes wear capes.

You saved the day!

Google user

recommended this

Original Poster

Jaméz Cartwright

marked this as an answer

Recommended based on info available

Helpful?

Abbas Abdulla Gold Product Expert

8/19/19

Recommended Answer

Relevant Answer

you're welcomed!

Google user

recommended this

Original Poster

Jaméz Cartwright

marked this as an answer

Recommended based on info available

Helpful?

Jaméz Cartwright Original Poster

8/19/19

Recommended Answer

Relevant Answer

Hi again Abbas,

While that did work perfectly, I just realised it's not in my timezone.

I live in the "GMT" timezone, but it seems to be giving me GMT-4.

Is there anyway I can rectify this issue?

Thanks again!

Google user

recommended this

Original Poster

Jaméz Cartwright

marked this as an answer

Recommended based on info available

Helpful?

Healthy Workplace

10/20/20

Recommended Answer

Relevant Answer

How to I do the same Timestamp script, but it would only occur if a cell value changes, rather than a manual update?

Google user

recommended this

Original Poster

Jaméz Cartwright

marked this as an answer

Recommended based on info available

Last edited 10/20/20

Helpful?

This question is locked and replying has been disabled.

Subscribe to updates

Write a reply

10 characters required

Failed to attach file, click here to try again.

Edit link

Text to display:Link to:

Personal information found

We found the following personal information in your message:

This information will be visible to anyone who visits or subscribes to notifications for this post. Are you sure you want to continue?

Link to post

Delete post?

This will remove the reply from the Answers section.

Report post

What type of post are you reporting?

Off-topic

Post is disrespectful, about third party products or contains unrelated content or personal information.

Abuse

Post contains harassment, hate speech, impersonation, nudity; malicious, illegal, sexually explicit or commercial content.

Google takes abuse of its services very seriously. We're committed to dealing with such abuse according to the laws in your country of residence. When you submit a report, we'll investigate it and take the appropriate action. We'll get back to you only if we require additional details or have more information to share.

Go to the Legal Help page to request content changes for legal reasons.

Google user

This reply is no longer available.

Badges

Some community members might have badges that indicate their identity or level of participation in a community.

Google Employee — Google product team members and community managers

Community Specialist — Google partners who help ensure the quality of community content

Diamond Product Expert — Community members with product mastery who help other Google users and Product Experts

Platinum Product Expert — Community members with advanced product knowledge who help other Google users and Product Experts

Gold Product Expert — Community members with in-depth product knowledge who help other Google users by answering questions

Silver Product Expert — Community members with intermediate product knowledge who help other Google users by answering questions

Product Expert Alumni — Former Product Experts who are no longer members of the program

Community content may not be verified or up-to-date.

Abbas Abdulla · Accepted Answer · 2019-08-19T11:35:19.647995+00:00

Hi,

Try the below script:

function onEdit(e) {

var ss = SpreadsheetApp.getActiveSheet();

var r = ss.getActiveCell();

//1.Change 'Sheet1' to be matching your sheet name

  if (r.getColumn() < 9 && ss.getName()=='Sheet1') { // 2. If Edit is done in any column before Column (I)  And sheet name is Sheet1 then:

var celladdress ='I'+ r.getRowIndex()

ss.getRange(celladdress).setValue(new Date()).setNumberFormat("MM/dd/yyyy hh:mm");

}

};

Abbas Abdulla · Accepted Answer · 2019-08-19T12:07:39.681397+00:00

Abbas Abdulla Gold Product Expert

8/19/19

Recommended Answer

Relevant Answer

Hi,

Just make sure from the time zone selected for the sheet - Goto: File > Spreadsheet setting > Timezone

Google user

recommended this

Original Poster

Jaméz Cartwright

marked this as an answer

Recommended based on info available

Helpful?