Create a Timer with Apps Script

<div>00:00 Hello, Better sheets members. We're gonna create a timer today. So a timer's a cool thing to have with a Google sheet because essentially what we get to do is we can create Pomodoro timers, we can create sort of timed tests or we can just see how long we've taken for certain tasks. 00:24 There's some limitations. So we have to work around those limitations.

App script is really cool and offers us the ability to sort of insert a timestamp , right? 00:35 We have the now function , which gives us this timestamp , and we have we can sort of keep putting nows and we can also do com command C copy. 00:49 And then piece to value, we get this date, right? This 44 8 8 1 0.26 of where ? 26 and a half percent through the day, right? 01:01 Cas Wow. From midnight to midnight.

So this gives us a hard coded time if we, you know, copy and paste values , but we don't really wanna do that with this equals now. 01:17 And now we have these two things, right, These two times and a start time and an end time, and we can subtract them. 01:29 We just need to format this properly. So let's format this as a time. No, that's not it. Format number , duration. 01:40 So this has been five seconds between these two things. So this is cool, right?

We have the ability in Google Sheets to do this math. 01:52 We can make a little bit of a start and we can time different events. Let's put this all sort of magically together, Not magically, but together. 02:05 This subtraction, we could add like an if is blank little magic thing here. If c2 Think It's D two. Well, let's say just if, if D two is blank, right? 02:35 There we go. So now if this doesn't exist, this, the time doesn't exist, the duration we can do equal now in copy and paste values so that this duration doesn't end. 02:53 That's been two minutes.

Again, we can format this. Let's format it before, before you go crazy. Need time. There we go. 03:04 So we have these timestamps, we have the duration, essentially have a timer here already, but it's a pretty crappy timer because we have to sort of keep putting the start in, keep putting the end in. 03:18 Wish we could do that programmatically. So let's do that with a couple things. Let's move these down. Let's put check boxes here. 03:31 Insert check box. We're gonna do the same here.

So if we click this start, we want this timestamp to start. 03:41 If we click this checkbox , we want this end to have a timestamp . This timestamp has to not be the equal. 03:51 Now function need to actually be a timestamp . So let's go into our app script and start doing this, right? We have, We know we wanna click on C1 and D one. 04:01 We want C3 and D three to be where we put attempts down. Okay? We have a function, my function here. 04:08 Let's change this to oned. And then we have an event E.

And we're gonna do if basically E range get row is equal to one e range dot, it's gonna be e get sheet, be e get sheet dot get name. 04:47 So we should be able to get the name of the sheet that the edit is happening on with this e dot range . 04:53 Do get sheet do get name, and you can always log it to check if that is actually occurring. And we want to make sure that that is equal to sheet one, which is the name of the sheet. 05:07 So sheet one, C one and D one. We want the range E road get row to be just in the first row for right now. 05:22 We'll just do that.

Actually no, we want two ifs. We want and e range dot get column can always again log this to double check that we're getting the right thing. 05:44 We wanna log the row and the column Should be, we wanna make sure the column is equal to, what is it? 05:55 C1 is gonna be three C. So if that happens, then we have another. If, if it's column four, we want to put in the timestamp . 06:07 So with timestamp it's just gonna be variable time. We'll just do time, Which I'll Be, we can do it in here. 06:18 Variable time start is equal to new date and that's it.

Just new date with this capital D and parenthesis, this is a timestamp . 06:28 And then we wanna do time end here. First off, let's double check that this range is all working correctly. So if we just do a couple of edits here, go to our executions, we go to our last execution and see sheet one. 06:50 That's correct, those my face, click refresh. Hmm, <affirmative>. And there we go. We got one in four. So we are getting the correct row and the correct column with our app script here. 07:09 Great. So now let's put this date somewhere.

This timestamp , where we wanna put it is if it's C1 that's clicked, we want to put it in c3. 07:21 So spreadsheet , app dot get active spreadsheet dot get sheet by name, sheet one dot get range . You want C, Sorry, what was that again? 07:39 C3 set value. It's gonna be time start. Now we can take the exact line here, put it here. But in, let's move my face up here. 07:53 Instead of c3, it'll be d3. So now we can even delete these two, right? This duration doesn't show up if this is edited in any way. 08:04 C1, there we go. We got a time stamp.

And then if D one is edited in any way, we can move that to false or true anything. 08:16 D three is not working. Oh, because we have time to start here. I forgot to edit time. And there we go. 08:25 That should work now. So any edit here, and we have a duration. So now anytime we click here, this changes. 08:33 Anytime we click here, this changes. So we've churned these check boxes into buttons a couple ways. We can sort of make this instead of check boxes, right? 08:45 Well we can keep them check boxes, but we can change their format . Let's make this A hundred.

We need two. 08:56 Change this. So if we change the size of these to a hundred, and we can also change the text to almost, we can actually turn this into almost imperceptible change. 09:17 But now we have this and we have this. And they can be big buttons, right? They can be buttons, the entire cell. 09:24 But the cool thing is we don't even need check boxes. We can instead, let's just type in start and you'll see this timestamp . 09:34 Let's end that and then we can type in end and times stamp will occur.

But if you do want something like check boxes, which are pretty fun little action items , right? 09:50 What you could do is say C one when this time starts just to uncheck it. So we would do the same spreadsheet app, C one set value. 10:05 And all we have to do, I think is set it to false, see if that works. So if I click true, then this should, C1 should also set it. 10:25 Oh, I know, I think we just have to do it without the brackets. So now each time I click this, it will set it back to false. 10:38 So the D one, let's fix that.

So this allows it to really be a little bit of a button. So this just needs to be D one. 10:47 We can also set it that if it's, if it's already, if it's just setting it to true. So if I actually put an end here, I think this will be a problem. 10:59 No, no, no problem. So just un checks it basically. Oh, actually this one doesn't work. So D one oh haven't saved little orange buttons here. 11:13 We can clean up our code a little bit. Let's just change this to variable sheet one equals this. And then sheet one. 11:27 And we can change this to sheet one.

Shouldn't change anything but just makes our code a little bit easier to read sheet one. 11:48 So we'd need variable one equals just line this up a little bit. Change this all sheet one. So again, this doesn't change the code in any way just makes it a little easier to read. 12:12 This all can be sort of separated like this as well, but do believe, so we're giving an enter, but we're not really changing the script at all. 12:26 Maybe. Yeah, so that's all easier to read.

So now, now you should be able to create a timer with app script anywhere in your sheet. 12:43 Have the edit access , sort of be check boxes or it could be really anything. Edit, access, edit. We can write these if functions . 12:54 We have an oned, we can also log to see if we're actually correct. Copy paste this sheet if you want, but let's double check that it actually works. 13:03 Now we can have our duration here if is blank is not gonna show up until the end. So let's start our timer and let's like, wait 10 seconds, right? 13:14 10, 9, 8, 7, 6, 5, 4, 3, 2, 1. Done.

And okay, that was 13 seconds. But there we go. We have a duration, we have a timer. We can use this for Pomodoro. 13:30 We can use this to test solving questions and answers on tests. We've got a lot of options here for Timer. 13:39 Hopefully this was helpful for you.</div>