Unable to update fields on PICKLIST columns with other values
Closed this issue · 5 comments
Hi.
This one is most likely an user error (using python 2.7).
I can't seem to add_rows() if a cell in PICKLIST column contains any value other than the pre-defined ones, though in the smartsheet UI the column is set to not "restrict to dropdown values only".
smartsheet.exceptions.ApiError: {"result": {"shouldRetry": false, "code": 1042, "name": "ApiError", "errorCode": 1042, "recommendation": "Do not retry without fixing the problem. ", "message": "The value for cell in column 2529062913632132, Final, did not conform to the strict requirements for type PICKLIST.", "refId": "vnoknjvk4g05", "statusCode": 400}}
When printing the column, I get:
{"index": 1, "title": "Status", "options": ["Not Started", "In Progress", "On Track", "At Risk", "Complete"], "width": 181, "validation": false, "type": "PICKLIST", "id": 2529062913632132}
I have found the solution, I had to compare the values I were getting with the options of the PICKLIST, and if they were not a match, set my cell.strict to False.
While it works, it's not the most intuitive way to go about it.
If you want to allow values outside your PICKLIST, you can always set cell.strict = False, you don't need to search the list first - in other words, it is not an error if value is in the PICKLIST but strict is False.
right.
I was assuming it would follow the UI value at first, but it's not actually a property of the column. Maybe it should?
Well, at the risk of making it more confusing, that UI value is in the column properties as "validation". If validation is True (meaning you have selected "Restrict to dropdown values only"), then even cell.strict = False won't allow you to update the cell. You'd have to also set cell.override_validation = True in order to force a value outside the PICKLIST into the cell. Since overrideValidation is only available to sheet Admin, it, in essence, removes that option (of setting a value outside the PICKLIST) from an Editor. See "cell value parsing" in the docs for a better and more complete explanation. (https://smartsheet-platform.github.io/api-docs/#cell-reference)
I see. It's slightly odd that the UI has more permissions than the SDK, but if it's how it's been designed, I'll close this ticket. Thanks