Can't set checkbox DataValidation

Question

I've tried to automatically add checkboxes as dataValidation. Checked is 1, unchecked is 0. If I'm trying to set it up manually - everything working fine. When I'm trying to use some string like 'Approved' as default - it is working too. I've tried to use 'true' string and got the same issue. If I'm trying to modify validation manually - everything is correct in Data Validation window, and then I'm pressing 'OK' (without any changes) - issue is gone.

Sample code:

function make_checkboxes(){
    var cell = SpreadsheetApp.getActive().getActiveRange();
    var rule = SpreadsheetApp.newDataValidation().requireCheckbox(1, 0).build();
    cell.setDataValidation(rule);
}

Steps to reproduce:

Run function on any range
Check the checkbox - checkbox is dissapeared, cell value is 1 and "Invalid:This cell's contents violate its validation rule"

When I've tried to find a workaround and just copy the validation (from script) I've got an error:

"Message details Service Spreadsheets failed while accessing document with id 1UWvTC3mswQ0lh23eZDq49lr2VNeQ8tEMrzlpF0Kv07c."

Sample code:

var range = spreadsheet.getSheetByName('hacks_and_workarounds').getRange('A1');
valid_rule = range.getDataValidation();
SpreadsheetApp.getActiveSpreadsheet().getActiveRange().setDataValidation(valid_rule);

user_11407695 · Answer

Problem

Checkbox DataValidation with numeric constraints results in "Invalid: This cell's contents violate its validation rule" error.

Explanation & testing

I decided to reproduce the issue and replicated the same DataValidation builder on a sample spreadsheet (no formats set, validations cleared, etc). The issue was successfully reproduced, but what caught my eye was the output of a getCriteriaValues() method. To test, I used the following function:

function getDataValidation() {
    const ss = SpreadsheetApp.getActiveSpreadsheet();

    const sh = ss.getActiveSheet();

    const cell = sh.getActiveCell();

    const dataValidation = cell.getDataValidation();

    const allowed = dataValidation.getCriteriaValues();

    const ui = SpreadsheetApp.getUi();

    ui.alert(`Allowed values: ${allowed.join(", ")}, type: ${typeof allowed[0]}`);
}

The output stated:

Allowed values: 1.0, 0.0, type: string

Note the type of value (one would expect it to be a number). I added a small utility for checking what are the current value, display value and number format for a given cell.

Here is the utility used (for reference):

function getCellValueDataType() {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sh = ss.getActiveSheet();
    const cell = sh.getActiveCell();

    const value = cell.getValue();

    const display = cell.getDisplayValue();

    const format = cell.getNumberFormat();

    const ui = SpreadsheetApp.getUi();

    const valuePrompt = `The value is "${value} of type ${typeof value}"`;
    const displayPrompt = `Display value is "${display}" of type ${typeof display}`;

    ui.alert(`${valuePrompt}
${displayPrompt}
Format is ${format}`);
}

Here is the output if the format is set to automatic (which is the default):

The value is "1 of type number"
Display value is "1" of type string
Format is 0.###############

And here is how it looks like for the plaintext format:

The value is "1.0 of type string"
Display value is "1.0" of type string
Format is

Compare it to the output of getCriteriaValues() - we obviously need to change the number format to plaintext to match. Upon doing so, the error, as expected, goes away, and the checkbox starts to work properly:

Same goes for true and false - only when the cell format is set to plaintext does the validation recognize the value as correct:

The value is "true of type string"
Display value is "true" of type string
Format is

Solution

If you want to set correct format programmatically, you can use the setNumberFormat (or its batch equivalent) with "@" parameter (forces content to be set as plaintext):

rng.setNumberFormat("@"); //assuming rng is defined elsewhere

Notes

I don't know why it works the way it does. You are correct in assuming that any value should go as the documentation states that parameter type is any (check the TS declaration file, for example).
Cooper linked to a related issue reported to issue tracker - at the time of writing, unfortunately, the report didn't get any traction and got closed (likely due to inactivity), and then updated to "won't fix".

References

requireCheckbox() method reference
getCriteriaType() method reference
getNumberFormat() method reference
getDisplayValue() method reference

Can't set checkbox DataValidation

1 Answers