Hey there stranger!

Sign up to get access.

Bjarne Asks: Can I show the Last Time of the Last Edit in a sheet?

About this Tutorial

ow do I show the date and time for the last time something was changed in a sheet? So what's interesting is that this This shows you Google Sheets shows an version history up here and shows you the last edit, but sometimes we want to have that data available to us inside of the sheet.

Featured Formulas

Video Transcript

 Hello. So Bjorn asked, how do I show the date and time for the last time something was changed in a sheet? So what's interesting is that this This shows you Google Sheets shows an version history up here and shows you the last edit, but sometimes we want to have that data available to us inside of the sheet.

Maybe we want to email that to ourselves every day and say, you know, when was the last edit? Maybe we want to use this for some kind of time functions. I, you know, prepared time sheets in, in, in Google Sheets before, and you wanna know just in case somebody.  enter some data and doesn't, you know, notate the time.

Sort of a backup. So I, I totally understand there's some interesting use cases for having the last edit actually in in the sheet. Also, this last edit was made seven minutes ago. Doesn't actually tell me the date or the time. It just says how much time and, and obviously it's for fi history. I can do it here, but I wanna know the actual date.

And I, I want to know the actual time of the last edit. So here's what I did to get this. So I just made a little tab called data.  and I have this, let's let's make some edits. You know, you have the equals now. Thing that gives you a date of time of now, but that doesn't really solve our problem.

Right? What if we want to actually edit now and then see when that was? So right up here it says, last edit was seconds ago. We go back to our data and it shows 1 15 48, which is pretty much the time that we just edited. So how does this, how does this, . Okay. It's a script. And you'll have access if you're better sheets.

Remember you have access to the script. I'll share that in the description below. But let's see. It's a few lines and it's pretty interesting. We use the built-in function of on edit. So we use a built-in function of oned, and this means that this will run literally every time you edit. And if you wanna add more on edits put more functions down here within this function on edit.

If you want to do more things, there's lots of things you might wanna do on an edit. Sometimes you want to add timestamps at particular places next to sheets. We've done that in some other videos. Sometimes you want to do execute something on edit but you put them all within the same function.

Okay? But this function in particular is four lines. So we say we just wanna grab the spreadsheet app. We just wanna know, okay. , this is the spreadsheet we're working on, the active spreadsheet. I get this spreadsheet by name because this edit can be done on any sheet. If you are looking to say, have one of these on every sheet, you, you have to create an if around this and you say, you know, if I won't do it now, but it's a little bit more complicated where you say, basically get this sheet and you know, if sheet.

Is equal to data, you know, and then do it on the data tab. But but I'm not gonna do that. This is actually wrong here, but I'm not gonna do that right now. What we're gonna do now is then we get this, the data sheet. Now we get the cell, which is a two. So I literally am hard coding this in this to say a two on data.

And that's all that means is, okay, where am I gonna set the value? It's gonna be on data in a two. This get range and what am I gonna set? I'm gonna set a value, and that's new date. This new date is a timestamp. And that gives us that information of editing. And so if I change anything here, you know, maybe I change this to summary.

Oops. Summary. , I'm gonna save that. And now it's not gonna work because there's no summary. So just put some kind of edit here. It's not changing this, but if I suddenly change this to summary and then go edit. Now this should and it edits it right here now 18 1 18 1 18:00 PM So now every time I edit, it's gonna change this.

So if you want to use. Code. Also, I can make this more uniform here that doesn't actually change anything in the code, but you can change your range and say, you know, a 10 there. That's how you change. Now it's gonna go to actually add those. Let's just edit the sheet. And there it is in a 10. Okay, so you can now I have to format this actually.

So that's one more step. You need to make sure you do format number and you want date time there, so make sure you well, Edit your no edit, sorry, format your timestamp in the proper way. You wanna actually see it, you can format it in any number of ways if you want to use that data for something else.

And see, this is last edit was seconds ago, but we know it was actually this particular date and time. Hopefully that was helpful and you should be able to get access to this sheet. And you can copy and paste this function over here. Just go over to. This add last edited timestamp sheet in the description below.

If you're a better sheets member, go to tools, go to script editor, and that will bring you here and you can copy this and add it to your sheet. Thank you. Bye.


Sheet Stories / Video Notes + ADDED: Email Notifications



ChatGPT Clone in Google Sheets Part 2

Create an Internal Google Sheets Add-on

Why Different Cell References in AI Integration in Sheets?

Show Sheet Tabs Based on Edit

Add Title Case to Google Sheets

Getting Started Coding in Apps Script

How to Power Testimonials with Google Forms and Sheets

Seek Errors When Coding Apps Script

Think Like a Programmer: Develop The Mindset of an Apps Script Coder

ChatGPT Clone in Google Sheets Part 1

Embed a Number in a Website from a Google Sheet

Create Navigation Like A Book or Presentation

Add Click Tracking To Your Google Sheets | Bitly in a Google Sheet


Hold a Giveaway Raffle in a Google Sheet

Capture Emails from Website Form to a Google Sheet (Without Zapier)

Embed a Headline in a Website from Google Sheets

Quickstart Tutorial OpenAI API in Google Sheets

Create a new Spreadsheet from just a Name in a Sheet.


Bjarne Asks: Can I show the Last Time of the Last Edit in a sheet?


Email Yourself a Cell from a Google Sheet, Every Day

OpenSea Data Inside Sheets

Create an Email Campaign Stats Calculator


Twitter App Clone in a Google Sheet

Dylan Asks: How to Automatically Delete Rows If Cell Contains Value

Highlight Row as You Move Your Cell Selection

Create a Timer with Apps Script

LinkTree in a Google Sheet


Password Protecting Data In a Google Sheet

Automatic Weekly Backup of Google Sheets

Create a CPM Custom Function (Create Better Calculators!)

Move Entire Row when a Cell is changed to "Yes" - The $75,000 Google Script


What Can You Automate in Google Sheets? Every single trigger available to Google Sheet users

Sync Two Tabs Without ImportRange()

Google Sheets Stories? No! But we'll add timestamped video notes to your google sheets.


Password Protecting Data In a Google Sheet Part 2 The Basics

Benoit Asks: How to Convert Case


Learn to Code in Google Sheets, For Programmers

Add a Checkbox to Turn on Dark Mode


Write Your First Script


Find Keywords in Any Column. Create quick search dropdown to find keywords


Basic CRM - Add a Powerful Script To Move Row Based on Status

How To Improve: 1,000 Business ideas: Business Idea Generator


Let's Make a Bookmarklet!


Troubleshooting Bitly in a Google Sheet Script


Unique Features - Design a Better Dashboard Part 2


How To Set Up Stripe Webhook to Google Sheets with Google Script


How to Edit a Macro


Sheet Stories / Video Notes + Clear 24 Hour Old Videos


Add A Timestamp to Task Lists (without Now Formula)


Make your Custom Functions Like Native Functions | Custom Function Autocomplete


Create a Changelog Between 2 Cells Custom Function | To learn Double For Loop


New Syntax for WhatChanged Formula in Google Script


How to Record Macros


2 Ways to Delete Lines Quickly (CAREFUL, it's a script!)


Deep Inside Dark Habits Google Script


How to Trigger Macros Daily


5 Ways to Create Coupon Codes | Create UUIDs

Create a Radio Button From Checkboxes Using Google Apps Script