5

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.

6

Short answer

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

Explanation

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

  • Thanks, exactly what I needed and it works without issues. I figured it might be something realted to copying it, I hope there's gonna be a better way in the future as they improve the docs and the script service – Athamas May 27 '18 at 19:34
  • @Andrew : I think that you should post your edit suggestion as a new answer rather than a edit to this one. – Rubén Jul 20 '18 at 1:07
1

Short answer

Use the undocumented method requireCheckbox

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 requireCheckbox() which is shown as a newDataValidation() method.

References

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.