One Million Checkboxes

Beginner

About this Tutorial

Check out this fun onEdit() counting you can do with checkboxes in a fun sheet with 1 Million Checkboxes.
Anyone can log into google sheets and go here: https://bettersheets.co/checkboxes

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Video Transcript

<div>00:01 I saw this tweet and it's a one million checkboxes, it's a website, and when you check off a checkbox it checks it off for everyone and you can check on or check off, and there's this counter going on here, however many boxes are checked, so when it's checked on it adds one, if it's checked off it doesn't 00:18 add it, and I said this could be a Google Sheet.

Well, I want to put my words to the test and actually create this as a Google Sheet. 00:27 We're going to make this totally publicly available ah Google Sheet and we're going to count the boxes as they're checked. 00:34 So let's do that, let's go sheet.new, create a new sheet, we'll call it one, we'll call it one, one, one million checkboxes and the point is we're going to try to get to ah a million checkboxes checked. 00:49 We don't need tables for this. We do want a bunch of columns but we want those columns to be smaller, maybe 40, maybe less. 01:00 Resize column to like 20.

That looks like a square. We'll insert checkbox . We can actually take the entire column . Actually, let's take the entire sheet. 01:12 Insert checkbox . I just did command Y and we have right now, it's a thousand rows but I don't think we need a thousand rows. 01:21 I think we need like something like 25. So let's delete all these rows down here. Delete rows 26 to 100 and we need more uh to the right. 01:33 We do want to make this a little bit bigger.

I think, um, that looks fine but we need do want a row with no checkboxes and we say however many are counted. 01:47 So we'll say here we'll format . We'll have these three be merged as well as these. We'll call it checkboxes. Checkboxes checked. 02:04 And then we will increase that a little bit more. There we go. And we'll put in a one here however many are checked. 02:12 So once we click, anyone clicks here, we want it to be checked off. So let's go to extensions app script . 02:19 We're going to use an on-premise on-edit function for this.

We're going to share this and make sure that anyone with the link is an editor because they need to edit this checkboxes. 02:34 So they can come in here, check off a checkbox , and then this on-edit will work. So we'll use on-edit, we use this event, we're going to get the row of the event, so variable row. 02:46 Actually we don't even need the row. All we need to know is if the value, new value is true or false. 02:53 Uhm, because checkboxes are just a true or false here. So we just need to know if it's marked true.

So new value equals E dot, I think it's new value, we can always look at on-edit, we can always look at on-edit, uh events, uh simple triggers, that's what we want. 03:18 Here's the event objects, that's what we're looking for. And we have all of our options here, so this is open, this is change, here's on-edit, uh old value, all we need is new value, it's gonna be value alone. 03:34 So it's just gonna be E dot value. So we can look at this actually, logger dot log.

And see what is the new value. 03:42 Let's save this, make sure it's saved here, call it one, million checkboxes, save that, and go and click a few of them, and in our executions we should have the on-edit. 04:00 Here, and we'll log what the new value is. Might take a moment to log that. Let's a few more. There it is running. 04:18 It says completed. Let's refresh. We should have some type of log here. Logger.log new value. Oh, this is supposed to be just value. 04:37 I don't know how that got messed up. Okay. Let's do that again. Do some of those checkboxes.

Go back to our executions. 04:46 Let's look at the top ones once it loads. So once it loads, it says true. There we go. So if it says false, we don't want to do anything, so if it says true, we do want to do something. 05:00 We do also need variable count is equal to spreadsheet app dot get active spreadsheet dot get sheet by name. We just want sheet one. 05:14 Actually, we might want to rename this checkboxes. There we go. We'll name this checkboxes. We want to get range is A1, get value. 05:29 So every time we get uhm we need the count, that's the count here.

We don't actually need it until we need it here. 05:39 And now we want if. New value is equal to true. This might need to change to something like true. We'll see if that works. 05:51 Variable count is going to be there. We're going to say variable new count equals count plus one. And then we are going to take this same thing here. 06:06 Instead of set, get value, we will set value and we'll set the value to new count. Okay. So this should update that count every time we get a true here. 06:16 Ah, this true is going to be a little wonky so let's look at it.

Let's see if it's going to update it here. 06:24 Does not look like it's happening. So we may need all caps true or we may need the text true. Let's test it a little bit more. 06:37 We may need text true. Here, save it and there we go. So there it is. Now the count is coming up. 06:50 Let's make it a little bit bigger and let's change it to something like uh like Consolas in this case. So now every time we have a checkbox checked. 07:07 So one checkbox , even if we uncheck it, it's not adding. There we go.

So now we'll add to the checkboxes every time this sheet, one million checkboxes is checked. I did want to test it with a new account so I just signed into a different account and I can uh edit these and it also counts. 07:33 But if I go to an incognito window, This is interesting is because I'm not signed in I still can do the check boxes, but it doesn't seem to be doing the counting. 07:43 It's not loading the app script .

So that's something very interesting to know about Google Sheets is that when you allow anyone to edit a sheet, the app script will not be loaded. 07:54 I think this is a safety feature for Google Sheets so that you don't uh have people who are not logged in. 08:00 Logged in, loading possibly a bad kind of app script . So, this is very interesting. So you need to sign in as a logged in user and then it will load the app script and then it will load this on edit. 08:16 So you can actually add to the count.

Before I make this public, I did realize that the million checkboxes website has a million checkboxes. 08:26 It's not trying to count to a million. So yeah, we will need ah our, ah what is this, seventy two more rows, okay, two more rows and now nine hundred and ninety rows. 08:42 Oop, we added too many. So we're gonna have a thousand rows, a thousand and one rows, but that's a thousand rows of checkboxes and we're going to try to create a thousand in columns, so insert 52. 09:00 Columns, so this is 52, we want 48 more. Insert 48. So now this is a hundred, so insert.

So what we want to do is select the columns, insert 100 columns to the right, we're gonna do that now, that's once, twice, three times, four times, five times, six times, seven times, eight times. 09:31 So now, let's see how many columns we have. If I select all the columns, I need to do it two more times. 09:47 So now there's a thousand columns and there's a thousand rows, so that's one million checkboxes.

And you can see these checkboxes are really struggling. 10:06 It's gonna have to load all the way to the end here, to the right, in the bottom, or not, in the top right corner. 10:12 And then it's probably going to, they're, it's so slow, and it might add number here. There, it, it did it very slowly, but it ultimately did add the, uh, number there. 10:29 Oh, now it's doing it a little bit faster. It's gotta get up to speed. And now you are able to get to this document if you go to bettersheets.co slash checkboxes. 10:40 Checkboxes.

You will be redirected directly to this Google Sheet where if you are logged in to your Google Workspace, you can check a box. 10:49 It'll check it for everyone. It'll add to the checkboxes checked, and you can uncheck it as well, which it will not de-de-delete from that number . 10:59 It'll just add all the times we're trying checking checkboxes. But yeah, here's a million checkboxes in a single sheet, and it is going pretty slow. 11:08 Enjoy.</div>