Add a CheckBox with a script

Question

How can I use a script to add a CheckBox to a Sheet?

This is probaly trivial but I could not find a single example online and the CheckBox class isn't even mentioned in the docs.

From the GUI, I would go Insert -> CheckBox.

Setting the value of the cell to either TRUE or FALSE doesn't work.

Answer 1

Short answer

Add the checkbox from the Google Sheets UI, then use one of the copyTo methods of Class Range.

Explanation

NOTE: On April 19, 209 Google included on the Google Apps Script release notes the requireCheckbox method. See more details on my other answer.

The Google Apps Script Spreadsheet service doesn't include a methods for everything that could be done through the Google Sheets user interface. This is the case of the Insert > Checkbox which is a pretty new feature.

Even the Record macro feature can't do this. The following was recorded one momento ago

/** @OnlyCurrentDoc */

function InsertCheckbox() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1').activate();
  /*
   * Added to show the missing Insert > Checkbox step
   */
  spreadsheet.getRange('B1').activate();
  spreadsheet.getRange('A1').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
};

NOTE: If you don't want to pass all the cell properties (borders, formulas, background, etc. instead of SpreadsheetApp.CopyPasteType.PASTE_NORMAL use SpreadsheetApp.CopyPasteType.PASTE_DATA_VALIDATION.

Related Q on Stack Overflow

• Placing checkboxes in Google Sheets using Apps Script

Answer 2

Short answer

Use the undocumented method requireCheckbox

NOTE: On April 19, 209 Google included on the Google Apps Script release notes the requireCheckbox method.

Example

function addCheckboxToA1() {
  var cell = SpreadsheetApp.getActive().getRange('A1');
  var criteria = SpreadsheetApp.DataValidationCriteria.CHECKBOX;
  var rule = SpreadsheetApp.newDataValidation()
    .requireCheckbox()
    .build();
  cell.setDataValidation(rule);
}

Explanation

The Apps Script IDE autocomplete feature shows several methods that aren't documented on https://developers.google.com/apps-script/. One of this methods is was requireCheckbox() which is shown as a newDataValidation() method.

References

• Class DataValidationBuilder
• Placing checkboxes in Google Sheets using Apps Script