Pages

Tuesday, 17 March 2020

Create a Tickbox to select all other Tickboxes

The following Google Apps Script is designed to enable a select-all Tickbox within Google Sheets so that multiple Tickboxes can be un/ticked by clicking just the one. In this example there are 3 master Tickboxes which will un/tick a selection of others based on the specified ranges.
Screenshot of Google Sheet with Tickboxes
Screenshot of Google Sheet with Tickboxes
locations.gs
This is not a function but instead a variable containing an object array all of the Sheets that have Tickboxes on, the cell that contains the master Tickbox and the range of cells containing other Tickboxes to be updated accordingly. In this example I have made it possible to update more than one range of cells that are not necessarily next to each.

onEdit(e).gs
Once we have the status of the master Tickbox we can use a 'switch' to set whether it is true, false or null (this is where a cell other than a Tickbox has been edited and hence the code would not need to continue).

The tricky bit comes next as we need to search through our array of location data (above) to see if the selected Tickbox matches one of our masters. First we need to 'filter' through and look for a Sheet name match (as we may have multiple Tickboxes on various Sheets):
.filter(function(sht) {
      return sht.sheet == sheet.getName();
})[0].locs
The returned data will look like this:
[{sheet:"Sheet1",locs:[{setCell:"A2",range:"A4:A11"}]}]
Next we search through the returned array to see if the setCell value is the same as the cell value that has been ticked in the Google Sheet:
.filter(function(loc) {
      return loc.setCell == cell.getA1Notation();
})
The returned data will look like this:
[{setCell:"A2",range"A4:A11"}]
Finally we need to filter the array and get the relevant range of cells containing the other Tickboxes we specified that should be updated alongside the master. We will use 'map' to extract the range value(s):
.map(function(rng) {
      return rng.range
});
So the final returned data will be:
["A4:A11"]
The script has been designed to handle updating multiple ranges of Tickboxes across the Sheet, so we need to 'split' the array of ranges and then 'forEach' item in the array update its un/ticked status based on the value of the master Tickbox.

Create a Tickbox to select all other Tickboxes

No comments:

Post a comment