Google Apps Script: checkbox, Google Sheets
This is something I came across today when working on a project. I needed my Google Apps Script to look at a cell or range and determine whether there is a checkbox in that Google Sheet cell. If there is, then my script would do something. Otherwise, I would throw an error alert to the user.
Off I went searching through the Google Apps Script docs hoping to find something like an isCheckbox() method.
No such luck on such an obvious solution, but after playing around with some of the other checkbox methods, I did find a solid alternative.
isChecked()
I didn’t feel that the docs were up to their usual amazing standard here. Thus the explanation below (Yes, I am also procrastinating from my project, but I’m also making an online note for myself).
isChecked() takes a range or a cell and returns:
- TRUE: if the cell has a checked tick box.
- FALSE: if the cell has an unchecked tick box.
- null: if the cell is not a tick box.
isChecked() in Individual Cells
Using isChecked() on an individual cell was exactly what I needed.
I tested it out with the following data:
Row 3 indicates the results of the test. The empty test (cell A2), number value test (cell B2) and text value test (Cell C2) all showed null which is ideal.
I was a little worried that booleans might be misread as checkboxes, but I should have trusted in Google. As you can see in D2 and E3, they also returned null. Finally, for an unchecked box, it returned FALSE, for not being checked (Cell F2) and for G2, the checked box, TRUE.
Here is the test code:
|
1 2 3 4 5 6 7 |
function checkCellTest(){ for(var col = 1; col <=7; col++){ var range = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(2,col); Logger.log("Cell " + range.getA1Notation() + "Is checked? " + range.isChecked()); }; }; |
And the logged results:
isChecked() for Ranges
When applying the isChecked() method to ranges in Google Apps Script, I discovered that it will evaluate an entire selected range and if all are checked, it will return TRUE, if all are unchecked, it will return FALSE. If there is a combination of checked and unchecked checkboxes, or if there are non-checkbox cells in the range, it will return null.
The code:
|
1 2 3 4 5 6 7 |
function checkRangeTest(){ for(var row = 9; row <=15; row+=3){ var range = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(row,1,1,7); Logger.log("Row "+ row + " is checked? " + range.isChecked()); }; }; |
The log:
Not particularly helpful for my needs. It might be good for something like a task completion lists that sends an email when all items are checked off or something.
Update 18 July 2019
GSuite’s Developer Relations Engineer, the very amazing Eric Koleda, was kind enough to steer me to a better approach when searching for checkboxes in ranges. You can check out his Twitter reply here:
You can determine this in bulk by call getDataValidations() on the range and checking to see if the cell has a data validation and if the type is CHECKBOX. pic.twitter.com/Xod1q8GnAL
— Eric Koleda (@erickoleda) July 17, 2019
Thanks, Eric! getDataValidations() didn’t cross my mind.
I hadn’t considered a checkbox as a data validated cell before, but that totally makes sense now.
Conclusion
If you need to check in Google Apps Script if a tick box appears in a Google Sheet cell, then you really need to use the isChecked() method cell-by-cell for each test.
This means iterating over each cell in the range you are searching for tick boxes in. Not the most efficient approach, I know. Though I can’t see many situations that you would need to search a huge range for checkboxes.
I’m glad I found this method, it’s going to come in handy for my current project. Which, I should probably get back to…
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