javascript - Checkbox violates data validation rules - Stack Overflow

admin管理员组

文章数量:1431434

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.

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.

• javascript
• validation
• google-apps-script
• google-sheets

Share Improve this question Follow asked Mar 2, 2019 at 22:09 user4676310user4676310 39111 gold badge44 silver badges1212 bronze badges 1

• Why not just use range.insertCheckboxes(); – Cooper Commented Mar 3, 2019 at 5:37

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 5

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();

Wow, it's 2023, and this is still an issue in Google Sheets. :/

Same issue, same fix. If the cell is plain text, the TRUE value bees "TRUE", which is invalid. Just set formatting to Automatic, and it works fine.

本文标签： javascriptCheckbox violates data validation rulesStack Overflow