Google Apps Script: How to check if there is a Tick Box (Check Box) in a cell or range

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:

Google Sheet isChecked test

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:

And the logged results:

isChecked test log Google Apps Script

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.

Google Sheet isChecked range test

The code:

The log:

isChecked range test log Google Apps Script

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:

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…

~Yagi

One thought on “Google Apps Script: How to check if there is a Tick Box (Check Box) in a cell or range”

Leave a Reply