Checkbox violates data validation rules

Question

I've generated multiple checkboxes (all are unticked) using apps script, all with the same code but some of them when clicked result in a popup saying they violated data validation rules (and they remain unticked). I checked manually (in the data validation menu) and they all have the same rule and if I remove the rule nothing changes.

var enforceCheckbox = SpreadsheetApp.newDataValidation(); enforceCheckbox.requireCheckbox(); enforceCheckbox.setAllowInvalid(false); enforceCheckbox.build(); checkboxRange.setDataValidation(enforceCheckbox);

In my script all their values are checked using the onEdit event but since I can't even edit their value I doubt that's relevant.

Answer 1

Turns out the problem was that the cells affected were formatted as plain text. Turning the formatting to automatic solved the issue.

Thanks to @Cooper I've discovered the simple way to add checkboxes via code, sadly this doesn't seem to be well documented on the apps script reference page.

For other beginners stumbling on this you can add a checkbox using:

  range.insertCheckboxes();
  range.insertCheckboxes(checkedValue);
  range.insertCheckboxes(checkedValue, uncheckedValue);

You can remove the checkboxes with:

  range.removeCheckboxes();