Google Apps Script: Create a Select All Checkbox to Tick Checkboxes in Any Desired Range in Google Sheets

Google Select All Check Box cover pic

Google Apps Script: isChecked(), switch, filter, map

One Checkbox to Rule them All

Now that’s a fantasy novel I could be my gums into.

Have you ever created a Google Sheet projects where you could really use a select all checkbox (they call them ‘Tick-boxes’ in Google Sheets)? Sure you can copy a range of tickboxes and paste the same range etc. But can you really trust your users not to mess that up?

People are used to select-all checkboxes in their computerised lives. It’s always better to work with familiarity to provide a better user experience rather than try and teach the user on the fly how to do something your way.

With this in mind, I went about creating a select-all checkbox for Google Sheets. Here is a little demo of how it all works. All the black background Tick boxes are select All boxes. These boxes have been assigned a range of other checkboxes that will be either checked or unchecked depending on the main select-all boxes state.

Google Select All Demo with Google Apps Script
Select All Demo

As you can see, the select all checkbox only changes the tick boxes in the assigned range. It does not affect any other non-tick box data in the same range.

To get this up and running on your own project, all you need to do is copy and paste in the two code files in your Google Apps Script editor. The first code file is the function that runs the check-all code. The other file stores all the select-all checkboxes and the ranges that they will affect.

Let’s take a look at the code and then run through a quick use guide before finishing off with an example.

Those of you who want to dive into the nuts and bolts of the code, there will be more explanation of each element of the code at the end.

The Code

Code.gs

Location.gs

User’s Quick Guide

In your Google Sheet, select Tools >Script Editor to get to the Google Apps Script editor page.

onEdit(e)

No preexisting code

If there is only a little bit of code like myFunction(){} in your sheet, go ahead and select all the code in the Code.gs file we provided above and paste it over the top of the starter code in your Code.gs file.

Preexisting Code

If there is already some code in your project, see if you can find an onEdit(e) function. If one exists, make sure that the onEdit function has an event object parameter inside of it. The most commonly used conventions is “e”, but your code might be something else.

match event parameter onEdit google apps script

Alternatively, if there is no onEdit(e), simply copy and paste in all the code in the Code.gs file above, making sure not to delete out any other code in the file.

Locations.gs

In your Google Apps Script editor go to File > New > Script file. Then name the file Locations.gs. Then, copy and paste in the Locations.gs data displayed above.

There are instructions in the sheet for you to help you edit data in the tickAllLocations variable. Hopefully, I have made it pretty straight forward for you to add and remove check all box locations.

Google Apps Script Object Array data tickAllLocations
Locations.gs

To modify, change the name of your Google Sheet tab to reflect the sheet name when it says sheet:. Then assign a cell location for your select all box in setCell:.

NOTE! Make sure you actually have a checkbox in this cell or it won’t work!

Finally, in the range part, add the range of cells that you want to apply your check-all to.

You can add more sheet tabs and select all cell locations simply by copying and pasting in the top example and modifying the sheet name and select all locations.

Alternatively, you can delete out any sheet , setCell or range in the example that you don’t want to use.

An Example

Imagine that we want to apply select-all to our Select All Example Google Sheet.

We only want to apply the select-all checkboxes to two locations on one Google Sheet Tab. This is what our sheet currently looks like:

Applying Select All Checkbox to new Google Sheet Example

Here we can see that we want to apply two select-alls to our Gollum sheet tab. Here is the data we need:

  • Sheet: Gollum
    • 1st Location:
      • Select all cell location: B1
      • Associated range: B2:B19
    • 2nd Location:
      • Select all cell location: F3
      • Associated range: E5:G14 

Let’s go to our Google Apps Script Editor and update the tickAllLocations variable in our Locations.gs file.

Google Sheets Select All Check Box
Gif file – May take a little while to load…

We’ll use the first sheet example and replace the values. Then we will delete out the second sheet and its set of values because we won’t be using it.

Simple as that.

Looking to learn more about Google Apps Scripts in a more structured format? Udemy has some great courses that can get your from the basics to a real Google Apps Script pro!

Got a more specific problem you need help with, but don’t have the time to develop the skills? Fiverr’s your best bet to find a skilled Google Apps Script professional to solve your problem quickly and cheaply. *

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.

Code Walkthrough

When preparing the checkAll(cell) function, I wanted to make sure that my calls to the Google server were only done at the last minute.

Normally you would see all the variables for the script at the top of the function or in a global, but this means they are also called unnecessarily each time. They are even called when the script might not be run all the way through.

This slows down the code and makes unneeded calls to the server.

Keep this in mind as we cycle through the code steps.

checkAll(cell)

The checkAll(cell) function takes one parameter, the cell. This is retrieved from the event objects in the onEdit(e) trigger. The “e” parameter in onEdit(e) equals the cell parameter in our checkAll(cell) function.

Our cell event object stores a bunch of data about the cell or range that is edited by the user. In our case, we are going to use the cell.range value which will store the object data of the range that has been edited. This is similar to the range data in an active cell:

SpreadsheetApp.getActiveRange()

From here it works like any Google Apps Script range value and you can apply the associated methods to them.

The checkAll(cell) funciton has three tasks:

  1. Validates the edited cell and checks if it is a checkbox. If so what TRUE or FALSE state it is in.
  2. Compares the cell against the tickAllLocations data in the Locations.gs file. If there is a match to the sheet tab and the setCell, then we return the associate range to checkAll.
  3. Sets either TRUE or FALSE to the check all range.

1. Cell validation

Here we use a switch to determine three possible cases. Switch looks a bit tidier than multiple if statements.

On line 3 the switch statement takes the argument. We can use the isChecked() method on a range to determine if the checkbox is ticked (TRUE), not ticked (FALSE) or does not exists (null) in the edited cell. It can only return one of these 3 results.

Our switch statement asks that if the cell range data returns one of these three options, do something.

In our first case (lines 4-5), if there is no checkbox in the edited cell, return or finish with the checkAll function. We don’t want to waste any more processing time on this.

Alternatively, if the case is TRUE or FALSE store their respective boolean values, break out of the switch condition and move onto the next stage of the code.

2. Compares the cell against the tickAllLocations

If we have a valid checkbox, we next want to see if it matches one of the checkboxes in our tickAllLocations data list in our Locations.gs file.

First, on line 2 we get the sheet object. We do this by calling our cell event object’s range and then get the sheet. There is no point doing this stage any later. We will be using the sheet information straight away.

Let’s say we clicked C1 on Sheet2.

Initially, we want to see if the sheet of the cell we edited is on our tickAllLocations data list.

To  do this we are going to create a variable called checkAllRange(Line 6). This variable will store the range of checkboxes that will be changed should the user click its associated select-all checkbox.

First, we want to filter down to any sheet that contains our Sheet2 name. If you look at our tickAllLocations array data you can see that the first level of data is an array. In the example, it is two data sets contain information for both sheets.

We will use the filter method to filter down to our Sheet2 data (Line 8).

Javascript first level filter method

In our example, our sheet.getName() will equal Sheet2. The first filter results would then look like this:

[{sheet:"Sheet2",locs:[{setCell:"C1",range:"C2:E6"}]}]

Notice that the first filter has stored the Sheet2 data in a new array indicated by the “[]” brackets in red.

Next, we want to search through the locs object array for a setCell that matches C1.

As you can see on line 9, we can simply tag on another filter to the end of the previous one. This is one of the beauties of the filter map and reduce methods. However, before we run our next filter, we want to ensure that it is first accessing the zeroeth cell and then starts searching through the locs reference.

…on line 9 equates to:

using filter results immediately after for anothe filter Javascript

Now we can use another filter right after the other one (Lines 10-12).

The second filter condition iterates through all the array items with an object name of setCell containing a value of C1. In our case the cell.range.getA1Notation(); is equal to C1.

There is only one item in this array and it matches, so the second filter results will look like this:

[{setCell:"C1",range"C2:E6"}]

Finally, we will combine this filter result with a map method (line 13) to take out just the range value. Our map will then return just the range in our selected array:

["C2:E6"]

Alternatively, at any point when either the sheet value or the cell value do not match, the resulting array will be empty:

[]

This is handy because we can use this to stop the checkAll() function if the length of the array is equal to zero:

However, if the length is one:

3. Set either true or false to the check all range of checkboxes

If there is a value in our checkAllRange array then we can select that range (Line 3).

Finally, we go all the way back to our first task that confirmed if the cell was a checkbox or not and if it is, ask if it is checked. If the edited checkbox is true, it set the checkBoxState to true so on line 7 we check the entire range of checkboxes associated with the just-edited select-all checkbox to true. This checks all the boxes in the desired range.

Alternatively, if checkBoxState is false all boxes in the range will be unchecked.

Conclusion

You can add as many select-all checkboxes to your Google Sheets tabs as you want. You just need to update the tickAllLocations array data in the Locations.gs file.

When the select-all checkbox is changed, it will change all the values in the associated range but it will not change any other type of value that is not a checkbox. This allows you to put text within the range without worrying about having to make changes.

I use the select-all checkboxes on project management, tasking sheets and attendance Google Sheets. I would love to hear how you made use of this little piece of Google Apps Script code.

Enjoy,

 

~Yagi

One thought on “Google Apps Script: Create a Select All Checkbox to Tick Checkboxes in Any Desired Range in Google Sheets”

Leave a Reply