Clear all Checkboxes Google Sheets and Button Making

" 'Bout time you broke in them pretty shoes." - Jayne, from Firefly

Nothing is more vexing to me than knowing there is a solution out there and that it is eluding me. This is exacerbated when I conduct multiple advanced Google searches and still find no solution. I recently had such an experience and eventually found the solution (though there are limitations of which I had not been aware). It's been a LONG while since I wrote a tutorial post but this recent adventure lends itself to one. So sit back, relax, and journey with me as I talk about how I learned to create a button in Google Sheets that performs an action (Spoiler: this totally works, but only on the desktop versions. This will not work on iOS devices, which is what I was hoping to use- That will be another adventure).

We recently had a fire drill at school and I am not the type of person who has a clipboard and writing utensils around as a regular part of my day. What I do often have is my iPad or my iPhone. I decided that I wanted to create a google sheet (that could work offline) that was a list of all my students and to have it with checkboxes. I go through the student numbers, tap a box, I know the student is there. Missing check? Missing student!. This part was easy enough. But at the end of the drill, I was left with a sheet that had nearly 30 checkboxes that needed to be unchecked individually. I could do this, but I wanted a button that would just clear all the boxes. Enter my obsessive 3-hour search and 5 hours of work...

Here is my final checklist (Note the awesome "Clear" button.

In order to accomplish this task, I learned about Google Script editor. I had no idea this even existed until this foray into the world of GSuite scripting, but I know I've only scratched the surface.

Step 1. After creating your list with checkboxes (if you want to learn how to do this watch this video. Click on the "Tools" menu and then click on "<>sript editor". This will open a new tab for the script editor.

Step 2. Name your script and save it by typing a new name for the sheet and then clicking File->Save. I named mine "Checkbox Clear".

 

Step 3. The area that begins with "function" is where you can begin writing your script. This takes some basic coding knowledge, or some basic searching knowledge to find or create scripts that do what you want.  In this case, I entered the following script:

Here is the text version of the script if you want to just copy and paste it:


function resetCheckBoxesAllSheets() {
var ss = SpreadsheetApp.getActive();
var allsheets = ss.getSheets();
for (var s in allsheets){
var sheet=allsheets[s]

var dataRange = sheet.getRange('A2:A27');
var values = dataRange.getValues();
for (var i = 0; i < values.length; i++) {
for (var j = 0; j < values[i].length; j++) {
if (values[i][j] == true) {
values[i][j] = false; // Modified
}
}
}
dataRange.setValues(values);

}//end of sheets loop.
}// end of function...


 

Note: you just have to change the range to match the range of the checkboxes you automatically want to clear in your sheet. This is also for columns of checkboxes play around with it if you need another type of layout.

Step 4: Click: "File"--> "Save"

I learned the hard way that this is not enough to have the script work... you have to approve some advanced google services:

Step 5: in the script editor click on "Resources" --> "Advanced Google Services..."

A list of google services with toggles to turn on and off will appear:

Step 6: Scroll down until you find the Advanced Google Service you want to change (in this case Google Sheets API) and toggle it to "ON"

Step 7: Google will prompt you that Authorization is required. Click on the blue "Continue" button.

Step 8: You're making a button. Google likes it when you click buttons. A new window will open asking if the script your writing will be allowed to view and manage your spreadsheet in Google Drive.  Click the blue "Allow" button.

Now we need to create a button that we will associate with the script we just created.

Step 9: Go back to your google sheet and click on "Insert" --> "Drawing".

Step 10: You can create your own drawing, or box, or anything. However, I wanted something that looked better than a simple box. I did a quick search and found an image of a button that said "clear".  I downloaded the image and wanted to use it as my button. To do this click on the Image icon in the menu.

Step 11: A popup window will open asking you to drag and drop your image (or any various other options) to get your image into the google drawing program. Choose your method, locate your file, then click the blue "Select" button.

Step 12: your image will appear in the Google drawing editor. Edit it as you see fit. Mine was a bit large so I resized it to a more manageable size. Once you have it the way you want it to look click on the Big, Green, "Save and Close" button.

This will return you to your google sheet and your drawing (image) will be dropped onto the sheet.

Step 13: Click on your image. It is very easy to miss, but in the top right of the image that has been placed on your sheet, there are 3 vertical dots. Click on these dots.

Step 14: This will open up a menu. Click on "Assign Script".

Step 15: This will open up a menu that asks you what script you want to assign to the image (button).

Now this can be a bit confusing... but the filename you created is NOT the name of the script you are assigning. Instead you are going to copy and paste the name of the FUNCTION you created as the name of the script you are assigning. In this case it is "resetCheckBoxesAllSheets"

To do this, go back to the script editor tab and highlight the name of the function.

copy (Ctrl+C) the function name. Go back to your google sheet tab. and paste it into the Assign Script window and click the big, green "OK" button.

You can now try out your new button.  Make some checks in your checkboxes:

Then, click on your button.  Your script should run and, if successful, your checkboxes in the column range you specified in your script will be cleared:

Try it out. If you accidentally click your button and clears all your boxes when you don't want it to a Command+Z or Ctrl+Z will undo your action.

NO iOS

I originally wanted to create this script/ button so that I could click it and have it work on my iPhone or iPad. Unfortunately, I learned that this only works on a desktop.  It is not functional on an iPhone or iPad. Hours more of searching online and it seems that there is no solution for this as Google and Apple are not making this a possibility.  However, if you happen to find out a workaround or any solution that makes this possible please share it in the comments! I would really love to have this functionality available on my mobile devices.

 

Try it Out

If you decided to try this tutorial, please let me know how it went (I'm hoping I didn't miss any steps). Let us know your successes, questions, etc. I'd love to hear how this works for you!

Crosswalk image from https://www.flickr.com/photos/walkingsf/6508082835 under (CC).

Leave a Comment