Working with Checkboxes in Google Sheets using Google Apps Script ☑️

In this tutorial, you will learn how to work with checkboxes in Google Sheets using Google Apps Script. Checkboxes are versatile and can be used for a variety of use cases. They can be used to mark tasks in a to-do list as done. They can also be used to select the rows that should be processed by a Google Apps Script script.

In the screencast below, you'll see a simple implementation of this idea. Users of this spreadsheet can select which employees to email by checking the checkbox in column A. Then they will run the Send Email script from the Admin custom menu to send emails to the employees that were selected.

In this tutorial, we will work with a Google Sheets spreadsheet that has a single sheet called Employee. This sheet contains the following tabular data:

After you copy paste the above table into your Google Sheets spreadsheet, please insert checkboxes into the first column (i.e., the "Take action?" column). When you're done, you should have the following sheet in your spreadsheet.

What you will learn in this tutorial

In this tutorial, you will learn how to:

• Check if a cell in Google Sheets contains a checkbox

• Use checkboxes to select rows for your Apps Script code to process

• Insert checkboxes into a range in your Google Sheets spreadsheet using Apps Script

• Remove checkboxes from a range in your Google Sheets spreadsheet using Apps Script

• Uncheck all checkboxes in a range in your Google Sheets spreadsheet using Apps Script

• Check all checkboxes in a range in your Google Sheets spreadsheet using Apps Script

• Toggle checkboxes in a range in your Google Sheets spreadsheet using Apps Script

• Check if all checkboxes in a range in your Google Sheets spreadsheet are checked using Apps Script

Prerequisites

This tutorial assumes that you're familiar with the following concepts:

• Basic coding concepts and the basics of coding using Google Apps Script

• Range in Google Sheets

• Reading from and writing to a Range in Google Sheets

• Checkboxes in Google Sheets

How to check if a cell in a Google Sheets spreadsheet contains a checkbox?

A checkbox is implemented in Google Sheets as a data validation. Therefore, to check if a cell contains a checkbox, you need to check if it has a data validation criteria of type CHECKBOX.

function containsCheckbox(a1Notation) {
  var range = SpreadsheetApp.getActive().getRange(a1Notation);
  var validations = range.getDataValidations();
  return validations[0][0] != null
        && validations[0][0].getCriteriaType() === SpreadsheetApp.DataValidationCriteria.CHECKBOX;
}

The function containsCheckbox() checks if the first cell in the range specified by a1Notation contains a checkbox. It returns true if the cell contains a checkbox and false otherwise. To check if cell A2 contains a checkbox, use the following statement:

containsCheckbox("Employee!A2");

How to use checkboxes to select rows for your Apps Script code to process?

In the spreadsheet below, let's say you only want your Apps Script code to take some action on three employees: April, James and Lara. You've selected them by checking the checkboxes in the "Take action?" column. Now when you run your script, it should only process these three rows.

First, we'll write code to take action on all of the rows and then we'll modify it to only act on rows that have been selected. The function processAllRows() below loads the data in your spreadsheet and then logs the contents of each row.

function processAllRows() {
  var rows = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
  var headers = rows.shift();
  rows.forEach(function(row) {
    Logger.log(JSON.stringify(row));
  });
}

var rows = SpreadsheetApp.getActiveSheet().getDataRange().getValues();

var headers = rows.shift();

rows.forEach(function(row) {
    Logger.log(JSON.stringify(row));
  });

Next, we need to modify the function to only process rows that are checked. Since the sheet has a dedicated column (column A) that indicates whether a row should be processed or not, we can simply check if the value in column A is true or false. If it is true, it means that the row should be processed. If it is false, then no action should be taken. The function processSelectedRows() implements this logic.

function processSelectedRows() {
  var rows = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
  var headers = rows.shift();
  rows.forEach(function(row) {
    if(row[0]) {
      Logger.log(JSON.stringify(row));
    }
  });
}

Suppose we want the script to only process selected rows where the department is "Product". There are three rows selected but only one employee works in the product department.

The function processSelectedRowsConditionally() implements this logic. The if statement checks both of the following conditions:

• The row is selected: row[0] (i.e., is the value row[0] true?)

• If the department is "Product": row[3] === "Product"

function processSelectedRowsConditionally() {
  var rows = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
  var headers = rows.shift();
  rows.forEach(function(row) {
    if(row[0] && row[3] === "Product") {
      Logger.log(JSON.stringify(row));
    }
  });
}

How to insert checkboxes into a range in your Google Sheets spreadsheet using Apps Script?

To insert checkboxes into a range in your spreadsheet, first access the range and then use the insertCheckboxes() method. The cells in the range will be set to the value false by default (i.e., the checkboxes that are inserted will be unselected).

function insertCheckboxes() {
  var range = SpreadsheetApp.getActive().getRange("Employee!A2:A9");
  range.insertCheckboxes();
}

You can also set custom values for the checked and unchecked states of checkboxes that are inserted by specifying these values in the insertCheckboxes() method. Specify the value corresponding to the checked state first and the unchecked state next.

function insertCustomCheckboxes() {
  var range = SpreadsheetApp.getActive().getRange("Employee!A2:A9");
  range.insertCheckboxes("Y", "N");
}

range.insertCheckboxes("Y");

How to remove checkboxes from a range in your Google Sheets spreadsheet using Apps Script?

To insert checkboxes into a range in your spreadsheet, first access the range and then use the removeCheckboxes() method.

function removeCheckboxes() {
  SpreadsheetApp.getActiveSheet().getRange("Employee!A:A").removeCheckboxes();
}

The removeCheckboxes() function will get rid of all checkboxes in the range that you specify.

Here is the sheet before running the function.

Here is the sheet after running the function. All the checkboxes in column A are gone.

If you only want to reset the checkboxes to the unchecked state, please see: How to uncheck all checkboxes in a range in your Google Sheets spreadsheet using Apps Script?

The removeCheckboxes() method will only get rid of checkboxes in a range. It will not delete other data in the range even if these values are the same as the ones associated with the checked and unchecked states of the checkboxes in that range.

For example, consider the following spreadsheet where rows 2 to 7 have checkboxes in column A. Rows 8 and 9 have the values "Yes" and "No" instead of checkboxes.

When you run the removeCheckboxes() function, only the checkboxes will be removed. The values in Employee!A8:A9 will not be deleted.

How to uncheck all checkboxes in a range in your Google Sheets spreadsheet using Apps Script?

To uncheck all checkboxes in a range, first access the range and then use the uncheck() method.

function uncheckAllCheckboxes() {
  SpreadsheetApp.getActiveSheet().getRange("Employee!A:A").uncheck();
}

When you run the uncheckAllCheckboxes() function, all of the checkboxes in the range Employee!A:A will be unchecked.

How to check all checkboxes in a range in your Google Sheets spreadsheet using Apps Script?

To check all checkboxes in a range, first access the range and then use the check() method.

function checkAllCheckboxes() {
  SpreadsheetApp.getActiveSheet().getRange("Employee!A:A").check();
}

When you run the checkAllCheckboxes() function, all of the checkboxes in the range Employee!A:A will be checked.

How to toggle checkboxes in a range in your Google Sheets spreadsheet using Apps Script?

A checkbox has two states: the checked state and unchecked state. Toggling a checkbox will reverse its state. If the checkbox was previously checked, it will become unchecked and vice versa.

The function toggleCheckboxes() toggles all checkboxes in the range Employee!A:A.

function toggleCheckboxes() {
  var range = SpreadsheetApp.getActive().getRange("Employee!A:A");
  var validations = range.getDataValidations();
  var values = range.getValues();
  var numRows = range.getNumRows();
  var numCols = range.getNumColumns();
  for(var i = 0; i < numRows; i++) {
    for(var j = 0; j < numCols; j++) {
      if(validations[i][j] != null
        && validations[i][j].getCriteriaType() === SpreadsheetApp.DataValidationCriteria.CHECKBOX) {
        var checkboxValues = validations[i][j].getCriteriaValues();
        if(values[i][j] == checkboxValues[0]) {
          values[i][j] = checkboxValues[1];
        } else {
          values[i][j] = checkboxValues[0];
        } 
      }
    }
  }
  range.setValues(values);
}

Be careful when setting the state of a checkbox by assigning values to the cell that contains it.

While checkboxes by default have TRUE and FALSE values associated with their states, you can have checkboxes that use custom values. Therefore, be careful when setting the state of a checkbox by assigning a TRUE or FALSE value to the cell containing the checkbox.

In the screenshot below, cells A2 and A9 are set to the value TRUE and are displaying a data validation error. This is because the checkboxes in the range A2:A9 have the values "Yes" and "No" associated with their checked and unchecked states

Setting the values of cells A2 and A9 to "No" fixes the problem and unchecked checkboxes will be displayed in these cells.

How to check if all checkboxes in a range in your Google Sheets spreadsheet are checked?

Use the isChecked() method to check if all checkboxes in a range are checked.

function areAllCheckboxesChecked() {
  SpreadsheetApp.getActiveSheet().getRange("Employee!A:A").isChecked();
}

Conclusion

In this tutorial you learned how to work with checkboxes in Google Sheets using Google Apps Script. Checkboxes are versatile, enable interactivity in your spreadsheet, and users already know how to use them. So, the next time you need to enable users to select specific rows for your script to process, consider using checkboxes to implement this functionality.

In this tutorial, you learned how to:

• Check if a cell in Google Sheets contains a checkbox

• Use checkboxes to select rows for your Apps Script code to process

• Insert checkboxes into a range in your Google Sheets spreadsheet

• Remove checkboxes from a range in your Google Sheets spreadsheet

• Uncheck all checkboxes in a range in your Google Sheets spreadsheet

• Check all checkboxes in a range in your Google Sheets spreadsheet

• Toggle checkboxes in a range in your Google Sheets spreadsheet

• Check if all checkboxes in a range in your Google Sheets spreadsheet are checked

Thanks for reading!