CODEWITHSUNDEEP
javascriptvalidationgoogle-apps-scriptgoogle-sheets
user4676310
user4676310

Reputation: 391

Checkbox violates data validation rules

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.

Upvotes: 4

Views: 3817

Answers (2)

Jeremy Gibson Bond
Jeremy Gibson Bond

Reputation: 1

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 becomes "TRUE", which is invalid. Just set formatting to Automatic, and it works fine.

Upvotes: 0

user4676310
user4676310

Reputation: 391

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

Upvotes: 5

Related Questions