Create a Stop Watch in a Google Sheet

About this Tutorial

We'll create a stop watch from scratch. Including Apps Script. What it will do is when you click a button (a checkbox) in a sheet, the script on every edit will add a start or a stop time, then add another time and calculate the duration.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Video Transcript

<div> Hey. So in this video we're gonna create a stopwatch inside of a Google sheet. We have a completely blank sheet here. We are gonna use app script a little bit. So hang on. I'll show you all the coding involved here. So before we get to coding though, let's go back to our sheet and create a button, not just any button. We're gonna create a button with a checkbox . We're gonna insert a checkbox here. We're not gonna just do that. We are going to make it 200 size. We're gonna bring. Box back down to resize row . Let's do 30 and then let's make this an actual box.

We're gonna resize this column 30. So we have this clicker here. So what this is doing, this is changing from true to false. And what we're trying to do is we need something to change when we click something. And the checkbox does that. When you click it, it changes from true to false or false to true. We want to, let's actually make this a little bit. Wider here. We're gonna right start right here. We are going to change this eventually. So we got this centered. We got a checkbox here, but we can see that this is changing, right?

One way we can do this is let's create, let's just make it well black. And if we just make both the background and the text the same color. It doesn't work. It gives you this heads up. So what we need to do is select it and then change one of these just a little tiny bit. So we're gonna change it to. Oh two. Now when we click it, it does not give us a message and you can't see the change, right? So this is going true, false, true false. Every time we click it, great. All right. Now what do we do in our function , in our app script , we are creating a stopwatch here.

And what we need to do, we need to use the on edit. Okay? But what do we need the on edit to be? We need an event. We need if. Actually we need e variable sheet equals E source, I think. So we have E source get active sheet . We actually want the sheet name that it's on. So sheet dot get, I think we need get name but we can log this logger dot log sheet name now. If we go to our box here, click this true false, then go to executions.

See we have some completed and we should in a moment be able to see if this is the sheet name . Now I'm showing you this because this is a really easy way to figure out if you're doing anything right. It's just to look at this log stuff. Let's keep looking. Let's refresh it. Sheet one that is the name of. . Perfect. So now that we got the name of the sheet, we can say, Hey, is the edit that we're editing on, is this edit actually on sheet one? And if it is, then let's log it. If we also actually need variable sheet no edit range , is it gonna be equal e dot range ?

Now we want whoop e range . . Now we don't want edit range . , this is, this auto complete is, ah, this auto complete is killing me. Escape. Okay, now logger dot log edit range . But we want to log something else here cuz what we need to do is we need sheet name to equal sheet one and two ampersands. We need edit row.  equals one. We also need no, we, it's not one, it's two because it's a second row and edit column, which will create another variable there. It needs to equal one that means a two, right? So row two column one.

We need to make sure that is set and if that is, what we're gonna do here is we're gonna say, Hey, create this get a timestamp . We're gonna do variable time equals. New date. That's gonna be our timestamp . We're gonna timestamped somewhere. We're probably gonna just create a row here before probably at, yeah, before something we'll insert a row. So we need to do variable sheet, which i s equals spreadsheet app. We'll always do spreadsheet , app dot, get active spreadsheet . We need to do variable sheet equals.  SS dot get sheet by name. We need sheet one.

We want to do variable range ad range , I think. No, we don't need that. We just need to do sheet dot insert. I think we do something like this. Let's, okay, we need sheet, which is sheet one. Insert rows before the fourth row. So we're gonna go over to the fourth row. We're gonna insert a row right before that. So we're gonna say start and then stop. We also, if this says start, we want to change it to stop. And if it says stop, we want it to change. To start. We want to do that by going to to do a one equals a. No sheet dot sheet dot range . We want the range .

Whoops, to be a one and then a one. Value. Yep. And then we want start, if it's says stop. So here, we gotta to put right here. If go ss dot, get sheet by name. Oh, should we have sheet? Probably want to change this to like something like sheet one. I think. Not just sheet. That might work better. Let's move this. Actually, we need this here. Value equals a one dot get value. That's how we get the exact value of that. So we can even log this as well. You can see this logger dot log.

A one value and we can log it, but if if a one value is equal to start, we wanna do something here. And if same thing, we'll get to this in a second. If it's a stop, we'll do something else. So if it's start, then we wanna do a one set value. To stop. And if it's stop, we're gonna do exactly the same thing, except change it to start. So every time we click that button that the cell above will edit.  so we can see if this edit Rowe, we can add this edit range is e range .

Then Rowe should be edited e range dot row. We can also log this logger dot log just to double check this is going okay, whatever that is. Then we can also log this edit column, which is the event, e range dot column. And we can log this just to Kate in case it's not correct. Sometimes this syntax is a bit hard to remember, so just make, remember to do logger dot log. We can save. , try clicking a couple times and then go to ac Ex our executions and let's see if this is correct. We'll click refresh this way here for a moment. There we go. We got null. Great.

So we have it wrong, but it is getting something. Okay, we're logging something but we're getting the wrong thing here. I think it's skit. Might be GETT Row. . There we go. So now we have it correct. We are clicking on this checkbox . We are getting completed correctly completed event here and we are correctly grabbing the range and then also edit row, whatever Rowe is being edited. Now what's really cool is you can go edit something else. We can edit here, add some difference. Or let's do time and we can go back to our executions. This is a little sideways thing. We can see here.

This was correct. This was the last one. There we go. Oh, there, actually it was this one. And see our first one was four, so that's the row. Nope. Not there, but, , sorry, column. I think we have to refresh this to get both of them. There we go. The row was the first one, three and then the column was the second one. Four. So now we're logging all, not logging, but we can log that whenever it happens.

But cool thing is making sure that only if the. sheet that we're editing is called sheet one, making sure that the edit row that we're editing is the second row and the column is one. So that a a two. And then once that happens, we're going to be changing this from start to stop. I think we can do that. There you go. That a one is starting. So now it labels it. Hey, click here to stop, or click here to start. But now what we need to do is put in a. . And if we notice, actually we can probably go down the bum. It, we have been adding the rows, we just haven't really noticed.

Cause we didn't have data in there. All right. How do we add the row? Wait, sorry. We add the row. Now we want to put in some data . We want the time, right? If it's okay, so if start, that means we're stopping and we want to put You wanna put it in the second place? But first let's do the first one. Wait, if it's start  we're starting. So we go. Where was that time? Okay, we need to add time somewhere. We need spreadsheet app. Let's just do this spreadsheet app dot get active spreadsheet . We'll do all get sheet by name. We have it actually here we can do is do sheet one.

Sheet one dot get range is going to be row four, column two, two set value. And the value is going to be time. So let's look at that. Let's see. . There we go. We got the time. We can't even format this, actually. That's the entire timestamp . We can do date time. Perfect. Now when we wanna do stop, we actually want to in the other place, but we don't wanna insert the row yet. We only want to insert the row when it start. So let's do that there and then we don't insert a row when it. But we do set the value time, so we just move the cursor the range to three. Great.

So now if I click it should only put it in c4. Perfect. Let's format this number to date time and we can also format , I'll actually, we don't even need all of that. We can just delete all of that. So now we have a time. This is going to always be. C1 minus B four, not c1, c4. So we can do equals C4 minus B four. We can format this number to a duration. We can click start. And you see we don't have the time yet, so maybe we wanna put this fun function and formula in time when we have a stop time. So we'll also add. Let's go do exactly this except the column four.

And instead of set value, we're gonna set formula . And this formula we're gonna set up is gonna be C four minus B four. And let's see how this goes. Okay, boo. We got it here. We got it here. Now we're gonna start a new one. Start time. . We're logging that start time. Perfect. We log the stop, stop time, and we got the duration. And all of these also change as they go down. These formulas will change, right? So we don't need to change this formula . This is awesome, right? We could also hard code this. We could just do the math, right?

We can take the this and minus this and put it here. That's another way to do it. But now we have a stopwatch inside of a Google sheet. We can have a start. Time stop time.  and the duration of those in between. Isn't that pretty cool? I hope you enjoyed this. You can also, if you're a member@bettersheets.co, you can grab this spreadsheet down below. If you are watching this somewhere else go to Better sheets.co. Become a member and get this stopwatch for free.</div>