Uncheck Every Checkbox in Google Sheets

Intermediate

About this Tutorial

Automatically uncheck a whole range of checkboxes. Or just do it yourself with the spacebar.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Video Transcript

<div>0:00 It seems like I'm learning new stuff about checkboxes all the time. This is about unchecking every checkbox , so you probably know this. 0:08 Check a box, check a box. Ah, you can even select all the boxes and hit the space bar. Ah, and then hit it again. 0:16 Again, to uncheck everything. But here's the crazy thing that I just found out. You can select some checkboxes. You can select the entire page. 0:25 Click here, ah, between the A and the 1. Hit the space bar. It selects all of them.

Hit the space bar again. 0:31 And any checkbox anywhere on the sheet, it all unchecks. But here's the thing I want to show you in this video that I think is pretty cool. 0:40 Is that you can use Apps Script to actually uncheck everything. And I will explain. The reason is, ah. Say you don't actually fill out the check, like checks, like you're checking off these daily things. 0:54 Maybe it's a daily or an hourly checklist or a weekly checklist .

Whatever it is, you can schedule the Apps Script to uncheck everything that you want unchecked. 1:04 You can be very specific about what's unchecked and the Apps Script is insanely easy and also I've done it for you. 1:11 So you can get this spreadsheet down below as a BetterSheet member. You are more than welcome to get this Apps Script and I will show you right now. 1:18 So go up to Extensions, Apps Script . Let's see what this Apps Script looks like and let's see what it does. 1:23 So uhm I have some selected stuff. I have selected some of these checkboxes.

I have this checkbox menu that is brand new custom menu and I click uncheck and it will run the script and it will uncheck all. 1:36 All of these specific checkboxes. So it won't uhm go outside the range if you have like something here. Let's see this not uncheck these ones on the right. 1:48 Good. Cool. So it will only check. Uncheck the ones you want.

And again, the reason we're doing this in Apps Script is because we want to trigger this, schedule this via triggers, which I'll show you, uhm, daily, weekly, or maybe we are not the ones that are using this, we are on making a template for 2:09 someone else to fill out and you want a fresh unchecked list every single day or every single hour. Okay, you can do that and I will show you. 2:16 So, the first thing we do is we write a function uncheck. We just name this. This is literally any name you want. 2:23 You can call it uncheckout. You can You can call it checkbox thingy.

Anything you want but it'll be important that you know what you're naming it. 2:31 So, we're calling it, we're gonna call it uncheck. We're getting a variable sheet equals spreadsheetapp .getactivespreadsheet. This is just getting the entire file. 2:38 That's all, uhm, like the whole thing. File. Then we want the actual sheet we're working on. So, in this case, it's sheet one. 2:45 So we say variable sheet one equals sheet this spreadsheet file sheet.getsheetbyname, sheet one.

Then, we take the sheet one and we get the checkboxes, which we're only gonna get if very specific range . 2:57 We're getting checkboxes equals, or sorry, variable checkboxes equals sheet one. getrange b2 colon b11. This is the range that we want to uncheck. 3:07 And literally, all we have to do is this one line. Checkboxes dot uncheck. Just uncheck is a built-in function here in JavaScript and in Apps Script here. 3:18 And now you're wondering, okay, how did I get this checkbox menu?

And all it is, is called function on open variable ui equals spreadsheet app dot get ui. 3:27 This is copied from Google Doc Helps. If you, if you Google like custom menu on open Google Sheets, you'll get this. 3:36 And I just deleted a whole bunch of other stuff we didn't need, but added ui dot create menu. Here's the name of the menu, literally checkbox menu. 3:44 Checkbox menu up here, and then dot add item, uncheck, uncheck.

So the name uncheck with a capital U is what is going to appear here. 3:55 And what is the function that's going to happen when you click it is going to be the second part. So. 4:01 Uncheck here, and then dot add to UI. Make sure you have these all capitalized. If you've never used Apps Script before, make sure to take Spreadsheet Automation 101 here on BetterSheets. 4:10 And ah, this is a really fun, I think, uhm, way to uncheck things. Really interesting to, to create those templates. 4:19 But here's how you create the triggers. So we have our function uncheck already.

We don't need the on open anymore. 4:25 The on open will only create this menu up here. But this function uncheck, we want to uncheck this, let's say, every day. 4:32 At 2 a.m., right? Sometime when we're not working. Go over to the left side, click triggers. I'm gonna move my face up a little bit because we got a button to click down here. 4:42 Add trigger on the bottom right. We're gonna choose which function , if you have other functions , you'll have available.

All the functions here, we're just gonna use uncheck, the one that we named uncheck. 4:52 If you named it something else, like uncheck everything, select that one. Down here at select event source, we, we actually don't need to change the deployment at all. 5:01 Head, actually, I don't even think there's any other options. Select event source is what we're gonna have to change. We're gonna have to change this to time driven, and then once we do that, we get some other options. 5:11 And I'm gonna do day timer. That means every single day, this is gonna be triggered, but at what time?

I like to do it between 2 to 3 a.m. 5:19 When I'm not working. If you're working late at night, maybe you want to do it early in the morning, like 5 or 6 a.m. 5:24 If you work in the afternoon, maybe you do want to make it like 8 or 9 a.m. Or 7 a.m. 5:30 Or an hour before you do it. You're gonna have to select an hour range . What this means if you've never used a trigger . 5:36 Before is that the very first time this triggers, when we hit save, it will choose a random time between 2 a.m. 5:44 And 3 a.m. And then, henceforth, after that, it'll be every 24 hours.

So it'll do the same time, but the first time it does, it'll be a random time in that. 5:52 Hour. That's just how Google manages their computer load, I guess. And now, once we hit that save, we have our function and our trigger and it'll run constantly. 6:05 If we make any changes whatsoever to this uncheck, it will run whatever the changes we've made. So if we have a different range here, it'll do that different range . 6:16 It's not like saving it forever.

Something else you might want to know, just in case you like don't want this to uncheck every day, click over here on uhm the three dots and click delete trigger . 6:29 You can also edit this if you want. Click here and you have the ability to edit it if you need to change the time at all. 6:37 Okay. But I want, I'm going to delete it right now. And you'll see, delete forever.

Another thing about triggers, if you've never used triggers before, is that if you create a trigger for someone else, the sheet itself will work even if you don't access it, but the other person will not be able to see 6:53 the trigger . Actually, I think that changed, that they can see the trigger , but they can't edit it.

100% they can't edit it, but used to be that you can't see anyone else's triggers, but I do believe like maybe if you're an admin or something you can see other triggers, but should not see other triggers 7:08 , so that's going to be an issue if you, if you create a trigger and someone else is like, I want to delete this, how do I delete this? 7:13 You're going to have to go and do that, but it will affect the sheet no matter what.

But yeah, I hope this was helpful, again it's some, some stuff I learn new all the time after 10 years of this, some things absolutely are new like this selecting ah everything. 7:28 And you're able to use the space bar to check and uncheck boxes, it's pretty darn cool. I, I just discovered that recently. 7:37 But yeah, I hope this Apps Script is also super helpful.

I also, literally in doing this, I, I discovered you can select an entire range and do dot uncheck. 7:44 It used to be, I think there's an old video on Better Sheets here, where I do the same exact thing here, but we run through a for loop to know ah, for this whole range . 7:52 But now you can just uncheck and check the whole range . So that's pretty cool. Hopefully, hopefully this is super helpful for you and you're using this to make your, your sheets better.</div>