How To Create a Stop Watch in Google Sheets

Create a Stop Watch in a Google Sheet. Start and stop time stamps, add up duration once you click. This is a sweet Google Sheet to make and you can see it step-by-step.
https://www.youtube.com/watch?v=uSloWQqkekc

TRANSCRIPT

How To Create a Stop Watch in Google Sheets - YouTube
https://www.youtube.com/watch?v=uSloWQqkekc

(00:00) Start time. . We're logging that start time. Perfect. We log the stop, stop time, and we got the duration. 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.

(00:17) So you might be wondering, what is a stopwatch in a Google sheet? It is a way to keep time and track duration of time. So we're gonna have a button. We're gonna create a button out of a checkbox. We're going to click that button, log the time with a timestamp, then click the button again, log the time, and then calculate that duration immediately, and then keep track of all the times we start and stop that stopwatch.

(00:40) I'm also going to show you step by step how to do this. We're gonna start from a blank sheet. , we're gonna have no app script written. We're gonna use the onEdit, simple trigger, which is built into Google Sheets. It's really interesting and I'm gonna show you every single step. Hang on.

(00:55) 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.

(01:13) 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.

(01:33) 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.

(01:53) 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.

(02:18) 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.

(02:46) 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.

(03:12) 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.

(03:32) 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.

(04:06) 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.

(04:33) 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.

(04:56) 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.

(05:23) 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.

(05:45) 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.

(06:15) 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.

(06:46) 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.

(07:11) 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.

(07:32) 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.

(07:54) 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.

(08:16) 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.

(08:37) 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.

(08:59) 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.

(09:17) 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.

(09:41) 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.

(10:13) 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.

(10:37) 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.

(11:07) 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.

(11:36) 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.

(12:00) 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.

(12:22) 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 that. Step-by-step tutorial on how to create that stopwatch inside of Google sheets. If you're looking for the sheet and you just want a copy of the sheet, become a better sheets member today, better sheets.co.

(12:45) You can become a free member, a monthly member or by lifetime deal and get every single sheet I ever create. Or go watch this video where I create a chat GPT, clone inside of a Google sheet, or check out any one of these videos where I improve sheets. Maybe you're not looking to do something from scratch, but you're just looking to improve your own sheets.

(13:05) Watch how I improve other people's sheets here in this playlist.

Watch on YouTube:

Google Sheet Formula Frustrations Solved

Find every formula you'll ever need in Google Sheets here at Better Sheets. Whether you're a beginner or an advanced user, I’ve got you covered with a comprehensive guide of 504 formulas.

Are you struggling to find a specific value in a column of data? Look no further than the powerful VLOOKUP formula. Or maybe you need to calculate the sum of values that meet specific criteria - try out SUMIF. And when it comes to frequency of values, COUNTIF has you covered.

Have you heard of the mysterious and powerful IF formula? It can turn your spreadsheets into gateways of productivity. And don't forget about the oft-partner ISBLANK().

Find step-by-step tutorials for any formula here on Better Sheets. Every formula page comes with links to written blog posts and Better Sheets tutorials featuring the exact formula.

Looking for a way to segment data based on specific criteria? The FILTER formula is perfect for you.

If you need to replace VLOOKUP, give INDEX/MATCH a try.

At bettersheets.co/formulas, I have everything you need to take your Google Sheets skills to the next level.

From ABS() to YIELD(), Better Sheets has it all.

\