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

User’s Quick Guide

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


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 file we provided above and paste it over the top of the starter code in your 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 file above, making sure not to delete out any other code in the file.

In your Google Apps Script editor go to File > New > Script file. Then name the file Then, copy and paste in the 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

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 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.


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:


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 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 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:


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:


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:


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.


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 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.




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

  1. Hi

    please can you advise how you would update a non-linear range of cells? So if my main tickbox was “E2” for instance and I wanted to un/tick boxes in the following cells: “A4:A11” and “C4:C6”?

    Kind regards

    1. Hi Philip,

      You would update the file with each range:

      //## Sheet Start ##
      //### Select All Start ###
      //### Select All End ###
      //### Select All Start ###
      //### Select All End ###
      //## Sheet End ##



  2. Brilliant…Quick question though…Is there a way to set the sheet to whatever sheet is currently active via one of the .getactivesheet formulas? That way instead of replicating the same code for each sheet in a “workbook”, making the script quite lengthy… it would instead use the above script on whichever sheet is currently being accessed and work for the range of cells within that active sheet only?

    1. Hi Kevin,

      There sure is. You could update the checkAllRange variable to:
      var checkAllRange = sheet.getDataRange()

    2. RE: checkbox for active sheet…where active sheet has same location for check boxes

      1) Create a constant variable
      const sheetName = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSheetName();

      2) update the sheet:sheetName
      sheet:sheetName, //this pulls the current active sheet otherwise, “Sheet 1”

      Now, you have the pleasures of adding this feature to duplicate pages.

  3. Yagi, first of all big thanks for posting this! Awesome 🙂

    Unfortunately, when i trying to run the script it crashes, saying:
    TypeError: Cannot read property ‘range’ of undefined (line 38, file „Code”)

    case null:
    case true:
    var checkBoxState = true;
    case false:
    var checkBoxState = false;

    If you hava any sugesstions or know how to fix this it would be a blast! 🙂 Thank you for your time!

      1. Hi 🙂
        i set it like this:
        var tickAllLocations = [
        //## Sheet Start ##
        //### Select All Start ###
        //### Select All End ###
        //### Select All Start ###
        //### Select All End ###
        //## Sheet End ##

        Here’s also a screenshot 😀

        Thank you 🙂

        1. Hi Marek,

          One thing it might be is the quotation marks, they seem a little off. But his might just be a result of pasting into comments.

          If you type in Logger.log(cell.range.getA1Notation()); above the switch and then check the “Select All” check box on the Google sheet, go back to the code editor and then select View >> Logs. What does the log say?

          1. It does not even create a log, it seems like the is not even called on the checkbox selection.

            I am facing the same issue, please help.

  4. Got it fixed.

    Possible causes –
    1. If you have multiple accounts logged in, this project will possibly not relate to the spreadsheet owner but another account.
    2. If you have any other document open it might get linked to a different document (This was in my case).

    To verify both the above cases open your “” and check for the owner as well as when you hover to the project it will show you the linked document.

    Let me know if this help you, Marek.


  5. Yagi,

    Thank you !
    I added a constant to replace the sheet name under var tickAllLocations

    const sheetName = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSheetName();

    sheet:sheetName, //this pulls the current active sheet otherwise, “Sheet 1”

    Now, you have the pleasures of adding this feature to duplicate pages. Hopefully there are no other pages with tick boxes!

  6. Just added a condition to run the script : 16 is the column of the ticker checkbox
    function onEdit(e){
    if (e.range.columnStart == 16){ checkAll(e) }

  7. I would like to know if there is a way to have one checkbox on lets say Sheet1 C1 that once checked, would check all checkboxes on all other C1 cells on other Sheets within the Spreadsheet

    1. I posted a similar solution above, but yes, you can create global variables (constants) that can affect different sheets. You may have to play with the code, but it is doable. The below is an example for an active sheet, but you could designate another sheet, call that sheet, and change the values (T/F).

      RE: checkbox for active sheet…where active sheet has same location for check boxes

      1) Create a constant variable
      const sheetName = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSheetName();

      2) update the sheet:sheetName
      sheet:sheetName, //this pulls the current active sheet otherwise, “Sheet 1”

      Now, you have the pleasures of adding this feature to duplicate pages.

    2. Hi Ricardo,

      All you need to do is update the tickAllLocations variable.

      If you want to do this in a more automated manner, I recommend that you check out the getSheets method. Then loop through each one excluding Sheet1.

      Give it a crack!


  8. Hi Yagi. Thank you for the post, it worked perfectly well on my sheet

    I have a question, I want to add this code on another sheet but with a twist: I want the checkAll box to work on the selected range (for example, setCell:”T5″ and range:”T6:T1133), but ONLY check those where the cell from the same row but different column is equal to TRUE. Is there a way a could do that?

    1. Hi Rodrigo,

      I’m glad it worked well for you.

      Your twist is a little outside the scope of this tutorial. However, I’ve put a few suggestions below to point you in the right direction.

      To do this you would need to create a seperate checkAll function. Maybe name it checkAllSpecial.

      You will need to get the ranges where you are going to cross-check if the column is equal to TRUE (Alternatively, you could use offset). Then remove the if statement in lines 73 to 78 and:
      1. Iterate through the range (loop) of your cross-check column
      2. If true, set the checkbox to check.


      1. Thank you for the reply! I will see if it works and I´ll post another reply if I manage to come up with something.

  9. Great script, and thanks for the useful tutorial!
    I think I have a specific use case that must be quite easy to achieve but I’m unable to do it. I want a checkbox to automatically check and uncheck a range of cherry picked cells, something that I tried in many ways in the file with no success, such as:
    Do you know what’s the proper way to achieve this? Thanks!

  10. Yagi, I keep getting the SyntaxError: Unexpected end of input (line 90, file “Check”” message.

    Here is my location code
    var tickAllLocations = [
    //## Sheet Start ##
    //### Select All Start ###
    //### Select All End ###
    //### Select All Start ###
    //### Select All End ###
    } ,

    Any clue on how to fix this? Thanks!!

    1. Hi Juan,

      Is your error on line 90 occurring at the start of your var tickAllLocations line? If you are using the old Google Apps Script Rino environment, then the comma at the end of your last curly braces above, mate result in an error.

      Let me know if this helped or not.



Leave a Reply