checkbox - Finding all checkboxes in a Google Sheet

Question

Ever since checkboxes were added to the native Google Sheets UI in 2018, developers have wanted to programmatically read them or treat them in certain manners, such as treating them as "radio buttons", resetting them to "unchecked", or setting them to "checked".

How can we best find checkboxes in a given Google Sheet, so that we avoid accidentally modifying other cells when manipulating their state?

One method is to inspect the values on a worksheet and treat any true/false values as checkboxes:

function getAllCheckboxes() {
  const wb = SpreadsheetApp.getActive();
  const checkboxes = [];

  wb.getSheets().forEach(function (sheet) {
    var rg = sheet.getDataRange();
    var values = rg.getValues();
    var sheetCheckBoxes = [];

    values.forEach(function (row, r) {
      row.forEach(function (val, c) {
        // Checkbox values are stored as `false` (unchecked) and `true` (checked)
        if (val === false || val === true) {
          sheetCheckBoxes.push({
            rowIndex: r,
            colIndex: c,
            value: val,
            r1c1: "R" + (r+1) + "C" + (c+1)
          });
        }
      });
    });
    if (sheetCheckBoxes.length) {
      checkboxes.push({
        name: sheet.getName(),
        sheetId: sheet.getSheetId(),
        boxes: sheetCheckBoxes
      });
    }
  });

  return checkboxes; // An array of objects describing a sheet and its checkboxes.
}

However, this won't work in all use cases: the cell might be displayed as the literal TRUE or FALSE, and not as a checkbox. The above code will treat it as though it is one, because it shares the same value.

Answer 1

Checkboxes are implemented in the Google Sheets application as a specific type of Data Validation, and can have user-specified values for "checked" and "unchecked"--not just true and false. Thus, to properly find only cells which are checkboxes, we must check the data validation type that is applied to each cell. This can be done in Google Apps Script in two manners: with the Spreadsheet Service, or with the Google Sheets API (v4).

Spreadsheet Service

The spreadsheet service method does not require you to enable any additional symbol identifiers or enable any APIs in the Google Cloud Platform. However, it may not be as fast in some cases as the Sheets API.

The script is very similar to that in the question, with the difference that we must iterate the 2D data validation rule array, and not the value array. (If we don't need the current value of the checkbox, we can skip acquiring the values array.)

function getAllCheckboxesViaService() {
  const wb = SpreadsheetApp.getActive();
  const checkboxes = [];
  // The specific type of Data Validation that demarcates a UI checkbox.
  const CB = SpreadsheetApp.DataValidationCriteria.CHECKBOX;

  wb.getSheets().forEach(function (sheet) {
    var rg = sheet.getDataRange();
    var values = rg.getValues();
    var sheetCheckBoxes = [];

    var dvRules = rg.getDataValidations();
    dvRules.forEach(function (row, r) { // iterate data validations instead of values
      row.forEach(function (rule, c) {
        if (rule && rule.getCriteriaType() === CB) {
          sheetCheckBoxes.push({
            rowIndex: r,
            colIndex: c,
            r1c1: "R" + (r+1) + "C" + (c+1),
            choices: (rule.getCriteriaValues().length ? rule.getCriteriaValues() : [true, false]),
            value: values[r][c],
          });
        }
      });
    });
    if (sheetCheckBoxes.length) {
      checkboxes.push({
        name: sheet.getName(),
        sheetId: sheet.getSheetId(),
        boxes: sheetCheckBoxes
      });
    }
  });

  return checkboxes;
}

Sheets API

To use the Sheets API, it must first be enabled in the application's Google Cloud Platform project. For Apps Script projects, one of these is automatically created and accessible from the "Resources" menu in the Apps Script Editor. Review the Advanced Services guide if you are unsure of how to activate the Sheets symbol & the Sheets REST API.

Data Validation is retrieved via the endpoint spreadsheets.get, when the fields partial response mask includes sheets/data/rowData/values/dataValidation. Generally just this specific field is not useful--it is also useful to know the associated sheet's title, ID, and perhaps the value of the checkbox, so a more useful fields specification is sheets(data(rowData(values(dataValidation,effectiveValue/boolValue))),properties(sheetId,title)). (You can experiment with valid field masks in the Google APIs Explorer)

The relevant Data Validation type in the Sheets API is BOOLEAN. We can query our desired spreadsheet once via the API, and then locally inspect the resulting response data to determine which cells have checkboxes and which do not:

function getAllCheckboxesViaAPI() {
  const wbId = SpreadsheetApp.getActive().getId();
  const fields = "sheets(data/rowData/values("
        + "dataValidation(condition(type,values/userEnteredValue)),"
        + "effectiveValue(boolValue,numberValue,stringValue)),"
      + "properties(sheetId,title))";
  const resp = Sheets.Spreadsheets.get(wbId, {fields: fields}); // Enable before use...
  if (!resp.sheets || !resp.sheets.length)
    return [];

  const checkboxes = [];
  resp.sheets.forEach(function (sheetObj) {
    if (!sheetObj.data || !sheetObj.data.length)
      return;
    var sheetCheckBoxes = [];
    sheetObj.data.forEach(function (gridRange) {
      gridRange.rowData.forEach(function (row, r) {
        row.values.forEach(function (cell, c) {
          if (cell.dataValidation && cell.dataValidation.condition
              // Require the cell to be displayed as a Checkbox.
              && cell.dataValidation.condition.type === "BOOLEAN")
          {
            sheetCheckBoxes.push({
              rowIndex: r,
              colIndex: c,
              r1c1: "R" + (r+1) + "C" + (c+1),
              choices: (cell.dataValidation.condition.values ?
                  cell.dataValidation.condition.values : [true, false]),
              value: cell.effectiveValue // object, e.g. {booleanValue: false} or {stringValue: "Yes"}
            });
          }
        });
      });
    });
    checkboxes.push({
      name: sheetObj.properties.title,
      sheetId: sheetObj.properties.sheetId,
      boxes: sheetCheckBoxes
    });
  });

  return checkboxes;
}

Using the checkbox locations efficiently

Once one knows which cells in a Spreadsheet correspond to checkboxes - and which values display as "checked" vs "unchecked - it is natural to want to read or modify them. Blindly writing true or false to the checkbox cell is only valid for default (boolean) checkboxes. To handle all possible user-created checkboxes, you must write the appropriate value that means "checked" or "unchecked." (The above scripts store these values in the choices property.)

Resetting values is most easily done in Apps Script with the RangeList class, though the Sheets API endpoint spreadsheets.values.batchUpdate can achieve similar results (yes, R1C1 notation is acceptable for the Sheets API ValueRange specification), albeit with some boilerplate to construct the request. The API approach is able to issue a single request, while the Spreadsheet Service can only instantiate a single RangeList per sheet (and you'll need to create 1 RangeList per type of checkbox, to avoid writing incorrect values (e.g. false when the "unchecked" value should be "No")).

function getSpecificCBType(checkboxData, checkedVal, uncheckedVal) {
  const desiredCBs = checkboxData.filter(function (sheetObj) {
    return sheetObj.boxes.some(function (checkbox) {
      return checkbox.choices[0] === checkedVal && checkbox.choices[1] === uncheckedVal;
    });
  }).reduce(function (acc, sheetObj) {
    var desiredSheetCBs = sheetObj.boxes.filter(function (checkbox) {
      return checkbox.choices[0] === checkedVal && checkbox.choices[1] === uncheckedVal;
    });
    if (desiredSheetCBs.length) {
      acc.push({
        name: sheetObj.name,
        sheetId: sheetObj.sheetId,
        boxes: desiredSheetCBs
      });
    }
    return acc;
  }, []);
  return desiredCBs;
}


function resetSomeCBsViaService() {
  const allCBs = /* method from above */;
  const checkedValue = true;
  const uncheckedValue = false;
  const someCBs = getSpecificCBType(allCBs, checkedValue, uncheckedValue);    
  const wb = SpreadsheetApp.getActive();

  // Set to checked, using a RangeList (could use Sheets API values#batchUpdate).
  someCBs.forEach(function (sheetObj) {
    wb.getSheetByName(sheetObj.name)
      .getRangeList(sheetObj.boxes.map(function (checkbox) { return checkbox.r1c1; }))
      .setValue(checkedValue);
  });
}

To build the API request from someCBs, something like this would suffice:

function resetSomeCBsViaAPI() {
  const allCBs = /* method from above */;
  const checkedValue = true;
  const uncheckedValue = false;
  const someCBs = getSpecificCBType(allCBs, checkedValue, uncheckedValue);    
  const wbId = SpreadsheetApp.getActive().getId();

  const rq = someCBs.reduce(function (rqb, sheetObj) {
    var valueRanges = sheetObj.boxes.map(function (checkbox) {
      return {
        range: "'" + sheetObj.name + "'!" + checkbox.r1c1,
        values: [ [checkedValue] ]
      };
    });
    Array.prototype.push.apply(rqb.data, valueRanges);
    return rqb;
  }, {valueInputOption: "USER_ENTERED", data: []});

  Sheets.Spreadsheets.Values.batchUpdate(rq, wbId);
}