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:

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:

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

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:

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

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…

Looking to learn more about Google Apps Scripts in a more structured format? Udemy has some great courses that can get you 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? Make an enquiry on my 'Hire me!' page. I occasionally pick up projects. Alternatively, Fiverr’s your best bet to find a skilled Google Apps Script developer to solve your problem quickly and professionally. *

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

~Yagi