Checking whether Cells on Google Spreadsheet have Checkboxes using Google Apps Script

GasTips / spreadsheet
Google Apps Script / spreadsheet

Gists

This is a sample script for checking whether the cells on Google Spreadsheet have checkboxes using Google Apps Script.

When the checkboxes are used in Google Spreadsheet, there is the case that it is required to know whether the cells have the checkboxes. This sample script can be used for such the situation.

Sample script 1

This sample script can check whether all cells in “A1:B10” have the checkboxes. When all cells in “A1:B10” have the checkboxes, res is true.

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
const range = sheet.getRange("A1:B10");
const res = range.getDataValidations().every(r => r.every(c => c && c.getCriteriaType() == SpreadsheetApp.DataValidationCriteria.CHECKBOX));
console.log(res);

And

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
const range = sheet.getRange("A1:B10");
const set = new Set(range.getDataValidations().flatMap(r => r.map(c => c && c.getCriteriaType())));
const res = set.size == 1 && set.has(SpreadsheetApp.DataValidationCriteria.CHECKBOX);
console.log(res);

Sample script 2

This sample script can retrieve the cells which have the checkboxes from the data range. When this script is run, the cell coordinates as the index can be retrieved.

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
const range = sheet.getDataRange();
const checkboxes = range.getDataValidations().reduce((ar, r, row) => {
  r.forEach((c, column) => {
    if (
      c &&
      c.getCriteriaType() == SpreadsheetApp.DataValidationCriteria.CHECKBOX
    ) {
      ar.push({ row, column });
    }
  });
  return ar;
}, []);
console.log(checkboxes);

Sample script 3

This sample script can be checked whether a single cell of “A1” has a checkbox.

const dataValidation = sheet.getRange("A1").getDataValidation();
if (
  dataValidation &&
  dataValidation.getCriteriaType() ==
    SpreadsheetApp.DataValidationCriteria.CHECKBOX
) {
  console.log("This cell has the checkbox.");
}

And

if (sheet.getRange("A1").isChecked() !== null) {
  console.log("This cell has the checkbox.");
}

isChecked() of Class Range returns the boolean type. Ref When this method is used to the cell which has no checkbox, null is returned. By this, it can be known whether the cell has the checkbox. This method might be simpler.

 Share!