Timestamp When Done (With Code)

Beginner

About this Tutorial

Automatically create a timestamp when a certain cell is marked as Done. I show you how to customize this scrip and where to get the script. I hope this is simple enough for beginners to start automating you sheets. If you want to see more automations and dive deeper into apps script enroll in Spreadsheet Automation 101
and see more examples of Apps Script at BetterSheets.co/snippets

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Video Transcript

<div>0:01 Hey, so we're creating a timestamp when we're changing the status to done and I'm going to show you the code But I'm also going to show you how to customize it in your case because in this case we have a status column And we're changing from pending to done, and we're going to get a timestamp in c3 here 0:16 column C. This is already happening over here in Apps Script .

We are using the function onEdit, we're getting the row, we're getting the column, we're getting the value, and we're selecting, okay, making sure that the column is the second column, the row is two or more, and the new value is done. 0:36 And then we're editing the timestamp here, uh, or at least formatting the timestamp here, uh, and then we're executing this line of code here. 0:44 Now, I'm going to show you how to customize this. So I'm actually going to customize it for you.

And then I'll show it to you in snippets, uh, where you can get this code. 0:52 You can also get this code if you're watching this video on bettersheets.co. Of course, down below, uh, you'll get this exact sheet, and you'll get the final product. 0:59 But I'll also show you a new place you can find this kind of code. So let's customize it. Uh, so this is going to be just a customized version, time stamp win x. 1:13 So you'll be able to x, uh, edit this and use it when you want. So our column will be.

We'll call it variable column we want. 1:24 We're going to assign it number two here. And we'll put the variable here, column we want. And then we want to also make sure that we're doing done. 1:37 So we want ah text we want equals done. And we can replace this text here with this variable text we want. 1:49 Now we want to have the uh timestamp be GMT5. So we'll replace this with a variable called timestamp timezone . And we'll write here a variable timestamp timezone . 2:08 Timezone equals GMT5. So now we have this here.

Can see it sometimes, you can see it a little more clearly if this line is all on the same line. 2:19 And then here's the format we want. So we'll do time. Stamp format . And again we'll add another variable here, time stamp format . 2:30 And we'll change the text here. So if you want to customize this, you can change the column that you want to look for here. 2:37 You can change the text that you're looking for here. You can also change the time zone if you want and the time stamp format if you want. 2:46 So again we'll put this all in one line to make it a little clearer.

And now this is totally customizable, much easier than searching through this code. 2:56 and looking for what you want, we have labeled it here. So again we can move this all into one line and you can sort of see much clearer what's happening. 3:04 So we're using this if here, we're saying hey, if all of these are true, if we get the column that we want, we have the row is more than two meaning, two or more meaning it's not the header row that we're editing and the value that we're adding here is what we want, then execute what's in these curly 3:23 brackets, okay?

And what's in the curly brackets is two things. We're creating a timestamp with this variable. We're formatting it as we wish. 3:31 We can change the format here. I'll show you that in a hot second. And then we're executing the active sheet that we're on. 3:38 And again, you can change this if you want to something else, which is you can add SpreadsheetApp .getActiveSpreadsheet. We can actually do this here. 3:48 Spreadsheet .getActiveSpreadsheet.getSheetByName. We can name of Spreadsheet . We can put a spreadsheet here.

Make sure we're doing it only on a spreadsheet . 4:05 I'll show you how to do that in a hot second. getSheetByName. And then this exact code here is exactly the same. 4:12 So getRangeRow. Now if we want to do this instead of just the active sheet , we have to make sure that the name of the spreadsheet is actually the name of the spreadsheet we're on. 4:22 So we're going to do, name of the spreadsheet is this, uh is, uh we'll do Sheet1, right.

Right now we're on Sheet1. 4:29 Making sure that we're on Sheet1 if we added a sheet to two and we execute the same thing, we don't want it to happen. 4:35 Okay, we do want to make it sure it's only on Sheet1. How do we get that here? We add and, and SheetName. 4:49 It is equal to, we need two equal signs, name of Spreadsheet . Okay, but how do we get this first variable, SheetName? 4:58 I'll show you how. Up here, we'll add it here. Variable SheetName is equal to. SpreadsheetApp .getActiveSheet.getSheetName.

And so now, anytime we're on a sheet, and using this on edit, it's going to know what sheet we're on. 5:20 And that's going to be this sheet name . And we're going to make sure that we're, it's exactly the same as this sheet one. 5:26 And so this is another form of this timestamp when done. If you want to make sure it's only on one tab, if you have other tabs, we can even duplicate this tab, let's just make sure that it works on the tab we want it to, which is sheet one. 5:40 So we change this to done. And we get a timestamp here. Perfect.

So now we want to make sure that it's not happening on any other one, so we'll hit done. 5:50 And now we'll hope nothing happens, right? Nothing happens because the sheet name is copy of sheet one, not sheet one. 5:59 So we've executed now two different timestamps when done. So we're one, when it's just done on the spreadsheet we're working on, but also we're adding a little bit of customization. 6:08 Here to make sure that we're on the exact spreadsheet we want to know, at least the tab we want.

So that's pretty cool that we can customize these things and now you can grab this code. 6:18 So again you can get this Apps Script if you go down below, access the sheet, go to extensions, Apps Script . 6:24 But I also want to share with you, you can go to bettasheets.co slash snippets. Snippets is where I'm saving more and more of these Apps Scripts for you to be able to copy immediately. 6:34 So if you scroll down or search for on this page, timestamp , you'll find two, uh timestamps when done.

You'll find timestamp when cell changed to done and the one we just did, timestamp when done on certain sheet. 6:47 And this one, you can just click copy to clipboard. You do not have to access the sheet. You do not have to go to Apps Script . 6:52 You can just go to this page, bettasheets.co slash snippets. Uh and grab the timestamp code if you want. This is a pretty new part of bettasheets. 7:04 If you are watching this later on, maybe there's some different version of this or different look of it.

But right now, this is where you can go to get any kind of Apps Script . 7:12 Scripts that I'm sharing with you. Right now there's a few custom function , custom menu , sorry, and open AI prompt is here. 7:19 And this little text here to add a custom function ability to any of your Google Sheets functions you're writing in Apps Script . 7:26 Some really cool stuff I'm adding here.

As Apps Script that you can easily copy instead of having to go to the page. 7:32 But again, if you want to check it out, go to the page down below, access sheet or copy the sheet directly to your Google Drive and then ah grab this code. 7:40 Enjoy.</div>