Checkboxes created with fixRowDataValidation() do not work (ridenoco-trip-request)

[NomeQ]

In api_provider.js, the receiveTripRequest() method now uses createRow() to append new trips to the Outside Trips sheet. The "Claim" and "Decline" columns are defined now in constants.js and when creating a new row, the validation rules there are used to format the columns as checkboxes.

It initially appears to work; however, clicking on a checkbox changes the cell content to "TRUE" and breaks the validation rule.

[keviniano]

I think this might be a clash between formatting and data validation. Check out this on Google's forum for Sheets: https://support.google.com/docs/thread/104768094?hl=en&msgid=104776121

[keviniano]

This could possibly be fixed in the "repair sheets" process by defining both formatting and data validation for the appropriate columns in defaultDocumentProperties in constants.

[NomeQ]

@keviniano tagging you to look at this. In my end, the offending code is running during createRow() in the fixRowDataValidation() function. This occurs in outside trips, when creating a new trip request.

[keviniano]

OK, after a bunch of testing of difference scenarios, here's what I've found:

• One way, the way I think Google intended things to work, is to not use custom cell values (that is, not specify checked or unchecked values) and have formatting set to Automatic (range.setNumberFormat('General')). This will set the cell format to boolean TRUE on the first click and then boolean FALSE on the next click. There is no way to set the cell to blank after that, except by going up to the formula bar and clearing out the value. Reading cells formatted this way in Apps Script will give you boolean-type values.
• Another way is to set custom checked and unchecked values, e.g., Yes and No, or just Yes, with blank for unchecked. This will also work with formatting set to Automatic. Reading cells formatted this way in Apps Script will give you string-type values. This includes blank for unchecked, which will return a zero-length string. You can coerce values to booleans via the standard JS !! idiom, though having an unchecked value of No would get coerced to true.
• The problems comes when you want use the words true or false as custom cell values. This can be done, but under some circumstances (I don't know exactly which ones) it will only work if the cell is formatted as plain text (range.setNumberFormat('@')). Attempting to use the words true or false with automatic formatting breaks Google's Intended Way of Doing Checkboxes by introducing values that sheets wants to convert to boolean when the checkbox data validation code is expecting string types only when using custom cell values. The fix of specifying plain text formatting results in Apps Script giving you string-type values.

In another RideSheet instance, I have cells formatted the "wrong way" (custom checked value = TRUE with formatting set to Automatic) without any issue. I don't know why your test environment is different, but at least we have three ways of getting things to work.

Reference: See this helpful Stack Overload conversation for more information about how to set number formatting using GAS.