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:

Take action? First name Last name Department
No Ben Ram Finance
No Jane Ryan Finance
No April Madison Marketing
No Jake Wood Sales
No James Garland Marketing
No Mary Liu Engineering
No Lara Jacobs Product
No Kent Ferguson Engineering

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.

Screenshot of a Google Sheets spreadsheet with the range A1:F9 displayed. Column A contains checkboxes and all of them are unchecked.

What you will learn in this tutorial

In this tutorial, you will learn how to:

Prerequisites

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

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.

Screenshot of a Google Sheets spreadsheet with the range A1:F9 displayed. Column A contains checkboxes and cells A4, A6 and A8 are checked.

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

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.

Screenshot of a Google Sheets spreadsheet with the range A1:F9 displayed. Column A contains checkboxes and cells A4, A6 and A8 are checked.

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

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() 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.

Screenshot of a Google Sheets spreadsheet with the range A1:F9 displayed. The range A2:A7 contains checkboxes and all of them are checked, Cell A8 contains the value "Yes" and cell A9 contains the value "No".

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

Screenshot of a Google Sheets spreadsheet with the range A1:F9 displayed. The range A2:A7 is empty (the checkboxes that were previously in this range have been deleted). Cells A8 and A9 continue to have the values "Yes" and "No" respectively.

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.

Screenshot of a Google Sheets spreadsheet with the range A1:F9 displayed. Column A contains checkboxes and all of them are 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.

Screenshot of a Google Sheets spreadsheet with the range A1:F9 displayed. Column A contains checkboxes and all of them are 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);
}

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:

Thanks for reading!


Have feedback for me?

I'd appreciate any feedback you can give me regarding this post.

Was it useful? Are there any errors or was something confusing? Would you like me to write a post about a related topic? Any other feedback is also welcome. Thank you so much!