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

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

/*
 * #### Check All Box ####
 *
 * When a selected checkbox cell is checked or unchecked
 * the associated range will either all be checked or unchecked
 * accordingly.
 * 
 * #onEdit()#
 * To run, add teh locations.gs file to your projects. Then copy
 * and paste the functions below.
 * If you already have an onEdit(e) function, simply paste the checkAll(e)
 * to the start of the onEdit(e) funciton and paste all the other functions
 * after the data.
 */



function onEdit(e){
  
  checkAll(e) // Add this to your onEdit() function.
};

/* ###################################################################
 * Main run fuction for the Check All code
 *
 * 1. validates if is check box or something else. If check box, is it TRUE or FALSE
 * 2. check if checkbox appears in teh tickAllLocations object array. If so,
 *    get the corresponding range of checboxes the check all will be applied to.
 * 3. Set all checkboxes depending on whether the checkAll box is set to TRUE or FALSE.
 *
 * @param{object} cell : this is the active cell drawn from the onEdit(e)
 * event parameter.
 */

function checkAll(cell){

  //Validate if cell is a checkbox and if so, it's state.
  switch(cell.range.isChecked()){
    case null:
      return;
    case true:
      var checkBoxState = true;
      break;
    case false:
      var checkBoxState = false;
      break;  
  };
  
  
  var sheet = cell.range.getSheet();
    
  //Check if is a Check all box from locations object array. If so return range.
  var checkAllRange = tickAllLocations
    .filter(function(sht){
      return sht.sheet === sheet.getName();
    })[0].locs
    .filter(function(loc){
      return loc.setCell === cell.range.getA1Notation();
    })
    .map(function(rng){
      return rng.range
    });
  

  //If the checkAllRange is zero then no value exists.
  if(checkAllRange.length === 0){
    return;
  }else{
    
    //Gets range from Object Array
    var range = sheet.getRange(checkAllRange[0]);
    
    //Checks the checkboxes in the determined range.
    if(checkBoxState){
      range.check();
    }else{
      range.uncheck();
    }; 
  };
  
  return;
};

* #### Check All Box ####

* When a selected checkbox cell is checked or unchecked

* the associated range will either all be checked or unchecked

* accordingly.

* #onEdit()#

* To run, add teh locations.gs file to your projects. Then copy

* and paste the functions below.

* If you already have an onEdit(e) function, simply paste the checkAll(e)

* to the start of the onEdit(e) funciton and paste all the other functions

* after the data.

function onEdit(e){

checkAll(e) // Add this to your onEdit() function.

};

/* ###################################################################

* Main run fuction for the Check All code

* 1. validates if is check box or something else. If check box, is it TRUE or FALSE

* 2. check if checkbox appears in teh tickAllLocations object array. If so,

* get the corresponding range of checboxes the check all will be applied to.

* 3. Set all checkboxes depending on whether the checkAll box is set to TRUE or FALSE.

* @param{object} cell : this is the active cell drawn from the onEdit(e)

* event parameter.

function checkAll(cell){

//Validate if cell is a checkbox and if so, it's state.

switch(cell.range.isChecked()){

case null:

return;

case true:

var checkBoxState = true;

break;

case false:

var checkBoxState = false;

break;

};

var sheet = cell.range.getSheet();

//Check if is a Check all box from locations object array. If so return range.

var checkAllRange = tickAllLocations

.filter(function(sht){

return sht.sheet === sheet.getName();

})[0].locs

.filter(function(loc){

return loc.setCell === cell.range.getA1Notation();

})

.map(function(rng){

return rng.range

});

//If the checkAllRange is zero then no value exists.

if(checkAllRange.length === 0){

return;

}else{

//Gets range from Object Array

var range = sheet.getRange(checkAllRange[0]);

//Checks the checkboxes in the determined range.

if(checkBoxState){

range.check();

}else{

range.uncheck();

};

return;

};

Location.gs

/*
 * Object and Array list of all the Sheets that have a Select All box.
 *
 * For each sheet there is a sub-list of all the cells that have a Select All 
 * box along with the range of checkboxes that will be checked or unchecked 
 * if the select all box is used.
 *
 * ### Adding a new sheet ###
 * Make a copy of the first sheet range, identified by the ## Sheet Start ## and
 * ## Sheet End ## comment fields, and paste it below the last field.
 *
 * ### Adding an new checkbox and range ###
 * Make a copy of the first setCell and range, identified by the ### Select All 
 * Start ### and ### Select All End ### comment fields, and paste it below the
 * previous one.
 */

var tickAllLocations = [
  //## Sheet Start ##
  {
    sheet:"Sheet1",
    locs:[
      //### Select All Start ###
      {
        setCell:"A1",
        range:"A2:A20"
      },
      //### Select All End ###
      //### Select All Start ###
      {
        setCell:"B2",
        range:"C2:G2"
      }
      //### Select All End ###
    ]
  },
  //## Sheet End ##
  //## Sheet Start ##
  {
    sheet:"Sheet2",
    locs:[
      //### Select All Start ###
      {
        setCell:"C1",
        range:"C2:E6"
      }
      //### Select All End ###
    ]
  }
  // ##Sheet End##
];

* Object and Array list of all the Sheets that have a Select All box.

* For each sheet there is a sub-list of all the cells that have a Select All

* box along with the range of checkboxes that will be checked or unchecked

* if the select all box is used.

* ### Adding a new sheet ###

* Make a copy of the first sheet range, identified by the ## Sheet Start ## and

* ## Sheet End ## comment fields, and paste it below the last field.

* ### Adding an new checkbox and range ###

* Make a copy of the first setCell and range, identified by the ### Select All

* Start ### and ### Select All End ### comment fields, and paste it below the

* previous one.

var tickAllLocations = [

//## Sheet Start ##

{

sheet:"Sheet1",

locs:[

//### Select All Start ###

{

setCell:"A1",

range:"A2:A20"

//### Select All End ###

//### Select All Start ###

{

setCell:"B2",

range:"C2:G2"

}

//### Select All End ###

]

//## Sheet End ##

//## Sheet Start ##

{

sheet:"Sheet2",

locs:[

//### Select All Start ###

{

setCell:"C1",

range:"C2:E6"

}

//### Select All End ###

]

}

// ##Sheet End##

];

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.

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 support me for free by using this Amazon affiliate link in your next tech purchase :Computers & Stuff! 🐐

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:

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:

Validates the edited cell and checks if it is a checkbox. If so what TRUE or FALSE state it is in.
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.
Sets either TRUE or FALSE to the check all range.

1. Cell validation

... 
 //Validate if cell is a checkbox and if so, it's state.
  switch(cell.range.isChecked()){
    case null:
      return;
    case true:
      var checkBoxState = true;
      break;
    case false:
      var checkBoxState = false;
      break;  
  };
...

...

//Validate if cell is a checkbox and if so, it's state.

switch(cell.range.isChecked()){

case null:

return;

case true:

var checkBoxState = true;

break;

case false:

var checkBoxState = false;

break;

};

...

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

...
  var sheet = cell.range.getSheet();
    
  //Check if is a Check all box from locations object array. If so return range.
  var checkAllRange = tickAllLocations
    .filter(function(sht){
      return sht.sheet === sheet.getName();
    })[0].locs
    .filter(function(loc){
      return loc.setCell === cell.range.getA1Notation();
    })
    .map(function(rng){
      return rng.range
    });
...

...

var sheet = cell.range.getSheet();

//Check if is a Check all box from locations object array. If so return range.

var checkAllRange = tickAllLocations

.filter(function(sht){

return sht.sheet === sheet.getName();

})[0].locs

.filter(function(loc){

return loc.setCell === cell.range.getA1Notation();

})

.map(function(rng){

return rng.range

});

...

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

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.

...  .filter(function(sht){ return sht.sheet === sheet.getName(); })[0].locs

1	... .filter(function(sht){ return sht.sheet === sheet.getName(); })[0].locs

…on line 9 equates to:

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:

...
  //If the checkAllRange is zero then no value exists.
  if(checkAllRange.length === 0){
    return;
  }else{
...

...

//If the checkAllRange is zero then no value exists.

if(checkAllRange.length === 0){

return;

}else{

...

However, if the length is one:

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

... 
   //Gets range from Object Array
    var range = sheet.getRange(checkAllRange[0]);
    
    //Checks the checkboxes in the determined range.
    if(checkBoxState){
      range.check();
    }else{
      range.uncheck();
    }; 
  };
  
  return;
};

...

//Gets range from Object Array

var range = sheet.getRange(checkAllRange[0]);

//Checks the checkboxes in the determined range.

if(checkBoxState){

range.check();

}else{

range.uncheck();

};

return;

};

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.

Create and Publish Google Workspace Add-ons with Apps Script Course 300px

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,

Need help with Google Workspace development?

Go something to solve bigger than Chat GPT?

I can help you with all of your Google Workspace development needs, from custom app development to integrations and security. I have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.

~Yagi

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

Thx dude says:

at 9:20 pm

Thx for help! It worked and i have never even opened Google Apps Script editor before!

Loading...

Reply
Phil says:

at 10:33 pm

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
Phil

Loading...

Reply
1. Yagi says:
  
  at 6:38 am
  
  Hi Philip,
  
  You would update the locations.gs file with each range:
  //## Sheet Start ## { sheet:"Sheet1", locs:[ //### Select All Start ### { setCell:"E1", range:"A4:A11" }, //### Select All End ### //### Select All Start ### { setCell:"E2", range:"C4:C6" } //### Select All End ### ] }, //## Sheet End ## ... ... ];
  
  Cheers,
  
  Yagi
  
  Loading...
  
  Reply
Mark Maxwell says:

at 7:01 pm

Works like a champ. Thanks, Yagi!

Loading...

Reply
1. Yagi says:
  
  at 9:05 pm
  
  Great to hear, Mark. Cheers, Yagi.
  
  Loading...
  
  Reply
Kevin says:

at 10:33 pm

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?

Loading...

Reply
1. Yagi says:
  
  at 6:34 pm
  
  Hi Kevin,
  
  There sure is. You could update the checkAllRange variable to:
  var checkAllRange = sheet.getDataRange()
  
  Loading...
  
  Reply
  1. Harvi says:
    
    at 2:13 am
    
    Hi Yagi, I follow the steps but it doesn’t work for me, can I share the google sheet? Thank you
    
    Loading...
    
    Reply
    1. Yagi says:
      
      at 7:40 am
      
      HI Harvu. I’m not 100% I’ll get to you quickly, but someone from the community might be able to help too.
      
      Loading...
      
      Reply
2. Anthony says:
  
  at 3:21 pm
  
  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.
  
  Loading...
  
  Reply
Marek says:

at 3:11 pm

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”)

switch(cell.range.isChecked()){
case null:
return;
case true:
var checkBoxState = true;
break;
case false:
var checkBoxState = false;
break;
};

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

Loading...

Reply
1. Yagi says:
  
  at 6:54 am
  
  Hi Marek,
  
  What does your range look like in the Locations.gs file?
  
  Loading...
  
  Reply
  1. Marek says:
    
    at 3:04 pm
    
    Hi 🙂
    i set it like this:
    var tickAllLocations = [
    //## Sheet Start ##
    {
    sheet:”check”,
    locs:[
    //### Select All Start ###
    {
    setCell:”F2″,
    range:”F3:F22″
    },
    //### Select All End ###
    //### Select All Start ###
    {
    setCell:”G2″,
    range:”G3:G22″
    }
    //### Select All End ###
    ]
    },
    //## Sheet End ##
    ];
    
    Here’s also a screenshot 😀
    https://ibb.co/h2TFsWz
    
    Thank you 🙂
    
    Loading...
    
    Reply
    1. Yagi says:
      
      at 3:45 pm
      
      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?
      
      Loading...
      
      Reply
      1. Shreyansh Hingar says:
        
        at 2:00 pm
        
        It does not even create a log, it seems like the code.gs is not even called on the checkbox selection.
        
        I am facing the same issue, please help.
        
        Loading...
Shreyansh Hingar says:

at 2:13 pm

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 “https://script.google.com/home/all” 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.

Thanks,
Yagi

Loading...

Reply
Anthony says:

at 4:51 am

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!

Loading...

Reply
Durant says:

at 5:42 pm

Thanks !!

Loading...

Reply
Durant says:

at 5:48 pm

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) }
};

Loading...

Reply
1. Yagi says:
  
  at 7:02 pm
  
  Good thinking.
  
  Loading...
  
  Reply
Andrew says:

at 8:39 am

Is there a way for one check box to be the main check box for multiple ranges across different tabs? I have a picture to better explain it, but it will only read the first range that I have. https://drive.google.com/file/d/1m4ao7UI5mjaFDr_MdXer4l9SYGJPWDcg/view?usp=sharing

Loading...

Reply
Ricardo says:

at 11:39 pm

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

Loading...

Reply
1. Anthony says:
  
  at 12:58 am
  
  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).
  
  ex:
  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.
  
  Loading...
  
  Reply
2. Yagi says:
  
  at 3:27 am
  
  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!
  
  Cheers,
  Yagi
  
  Loading...
  
  Reply
Rodrigo says:

at 3:29 am

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?

Loading...

Reply
1. Yagi says:
  
  at 5:34 am
  
  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.
  
  Cheers,
  
  Loading...
  
  Reply
  1. Rodrigo says:
    
    at 11:37 pm
    
    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.
    
    Loading...
    
    Reply
Gerard says:

at 6:36 pm

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 Locations.gs file with no success, such as:
range:["C12","C23","C25","C29","C31","C32","C33","C34","C36","C38","C40"]
Do you know what’s the proper way to achieve this? Thanks!

Loading...

Reply
Juan says:

at 7:07 am

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

Here is my location code
var tickAllLocations = [
//## Sheet Start ##
{
sheet:”Master”,
locs:[
//### Select All Start ###
{
setCell:”C12″,
range:”C7:C11″
},
//### Select All End ###
//### Select All Start ###
{
setCell:”C22″,
range:”C14:C21″
},
{
setCell:”C50″,
range:”C24:C49″
}
//### Select All End ###
]
} ,
];

Any clue on how to fix this? Thanks!!

Loading...

Reply
1. Yagi says:
  
  at 9:29 pm
  
  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.
  
  Cheers,
  
  Yagi
  
  Loading...
  
  Reply
Andrew says:

at 10:28 am

very nice!

But I have a small problem, when I share it with others looks like the script doesn’t work!

Loading...

Reply
1. Yagi says:
  
  at 11:05 am
  
  Hi Andrew,
  
  Thanks!
  
  I just gave the script another test on a shared separate account and it is working well. A couple of things may however be causing this:
  – If the other checkbox cells are protected, the script won’t update them.
  – If the user has view permission, they will not be able to select the checkboxes.
  
  Hope that helps.
  
  ~Yagi
  
  Loading...
  
  Reply
antonio farias says:

at 7:12 am

Hey yagi this is wonderful! Is it possible to use this script on named ranges? It’s working great with ranged coordinates(eg.: A1:A7), but if I give that range a name like (List1_boxes) it won’t read… It would be amazing if there was a way, so I don’t have to manually change the script every time a row is added(since named ranges update with added rows and columns.

Loading...

Reply
1. Yagi says:
  
  at 8:49 am
  
  Hi Antonio, you could try keeping your range open. E.g. A1:A.
  
  ~Yagi
  
  Loading...
  
  Reply
Michelle says:

at 5:48 am

This implemented beautifully(!), except…
It runs on any edit made in the entire tab. I have other cells active in the tab that are not related to the column of checkboxes (which are located below all other rows being used in the tab.
How can I have the function checkAll only run when the setCell(s) are checked?

Loading...

Reply
1. Yagi says:
  
  at 12:45 pm
  
  Hi Michelle,
  
  Unfortunately, the onEdit trigger will run on all sheets at all times. It can be a fairly quick script if you ensure that the codes stops as soon as it is not in the correct location. Your script should first check the sheet name, the desired range.
  
  ~Yagi
  
  Loading...
  
  Reply
Ryan says:

at 11:53 pm

I keep getting this: Syntax error: SyntaxError: Unexpected token ‘;’ line: 30 file: Locations.gs

locations.gs:
/*
* Object and Array list of all the Sheets that have a Select All box.
*
* For each sheet there is a sub-list of all the cells that have a Select All
* box along with the range of checkboxes that will be checked or unchecked
* if the select all box is used.
*
* ### Adding a new sheet ###
* Make a copy of the first sheet range, identified by the ## Sheet Start ## and
* ## Sheet End ## comment fields, and paste it below the last field.
*
* ### Adding an new checkbox and range ###
* Make a copy of the first setCell and range, identified by the ### Select All
* Start ### and ### Select All End ### comment fields, and paste it below the
* previous one.
*/

var tickAllLocations = [
//## Sheet Start ##
{
sheet:”PIR Flyer Posting”,
locs:[
//### Select All Start ###
{
setCell:”A3″,
range:”A4:A16″
},
//### Select All End ###
//## Sheet End ##
];

Loading...

Reply
1. Yagi says:
  
  at 12:05 am
  
  Hi Ryan. I looks like you haven’t closed your locs array.
  
  Loading...
  
  Reply