Question

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

Original Poster - Jaméz Cartwright

8/19/19

Automatic Timestamp when a Cell on the same row gets Updated. 2 Recommended Answers 49 Replies 93 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)

All Replies (49)

Gold Product Expert Abbas Abdulla

8/19/19

Recommended 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

Last edited 8/19/19

Helpful?

Original Poster - Jaméz Cartwright

8/19/19

Recommended 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

Helpful?

Gold Product Expert Abbas Abdulla

8/19/19

Recommended Answer

you're welcomed!

Google user

recommended this

Original Poster

Jaméz Cartwright

marked this as an answer

Helpful?

Original Poster - Jaméz Cartwright

8/19/19

Recommended 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

Helpful?

Wolfspack Marketing

4 days

Recommended Answer

I have 2 tabs in my worksheet, where I want to add this autostamp, how to I apply it to both the tabs in the same sheet.

I tried adding another function, but then they both stop working.

Google user

recommended this

Original Poster

Jaméz Cartwright

marked this as an answer

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 — Googler guides and community managers

Community Specialist — Google partners who share their expertise

Platinum Product Expert — Seasoned members who contribute beyond providing help through mentoring, creating content, and more

Gold Product Expert — Trusted members who are knowledgeable and active contributors

Silver Product Expert — New members who are developing their product knowledge

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

Gold Product Expert Abbas Abdulla

8/19/19

Recommended 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

Helpful?