Smartsheet MULTI_PICKLIST turning into TEXT_NUMER despite being level 2

Question

I'm trying to populate a multi picklist with object_values, but on the older sheets it wont work as its returning "Required object attribute(s) are missing from your request: cell.value." So i wrote a function to update the column to a MULTI_PICKLIST, but it won't actually change

this is the code I use to update the column

client = smartsheet.Smartsheet('KEY')
client.errors_as_exceptions(False)

target_sheet = init_sheet(workspace, target_sheet_name, client)
target_column = target_sheet.get_colunm(1234567890)

update_column = smartsheet.models.Column({
    'title': 'New Column',
    'index': target_column.index,
    'level': 3,
    'type': 'MULTI_PICKLIST',
    'options': options
})


client.Sheets.update_column(target_sheet.id, target_column.id, update_column)

But when I load in the sheet again the column is still a TEXT_NUMER type

Answer 1

First -- the error "Required object attribute(s) are missing from your request: cell.value." indicates an issue with your Python code that's attempting to set values for the multi-select list. For code that shows how to create a multi-select dropdown column in a new row, see my answer on this post: How to add a new row to Smartsheet sheet with Multi-Select Dropdown values?. Specifically, see the code snippet in the UPDATE - complete proof of concept section of the answer.

Regarding the "Update Column" code you've posted in your question:

• The index property doesn't need to be set, since you aren't changing its value with the update column request.
• level is not a valid column property in an Update Column request, so setting its value is doing no good (it's just going to be ignored).

Regarding the scenario you're trying to solve for -- using an Update Column API request to change a column from type TEXT_NUMBER to type MULTI_PICKLIST -- seems like your code should work as written (assuming that the value of options in your code is a list (array) of values). (Do go ahead and remove the index property and level property though, since setting those properties in your request will have no effect.)

I proved this out by creating a TEXT_NUMBER column in my sheet, and then running the following code -- to change the column type to MULTI_PICKLIST and populate the list with 3 values.

column_spec = smartsheet.models.Column({
    'title': 'multi-select col now',
    'options': ['One', 'Two', 'Three'],
    'type': 'MULTI_PICKLIST'
})

# Update column
sheetId = 2702602705784708
columnId = 7331855331184516
result = smartsheet_client.Sheets.update_column(sheetId, columnId, column_spec)

After running this code, I verified that it worked by refreshing the sheet in Smartsheet and then looking at the column properties:

So far, so good -- right? But then I issued a Get Column API request, to see that column definition, now that it's clearly been changed to a MULTI_PICKLIST column:

Request: GET https://api.smartsheet.com/2.0/sheets/2702602705784708/columns/7331855331184516

Here's the API response I received back:

{
    "id": 7331855331184516,
    "version": 2,
    "index": 4,
    "title": "multi-select col now",
    "type": "TEXT_NUMBER",
    "options": [
        "One",
        "Two",
        "Three"
    ],
    "validation": false,
    "width": 150
}

What the heck?! The type property is set to TEXT_NUMBER -- despite the presence of an options property that implies a picklist, and despite the fact that the column is definitely a MULTI_PICKLIST column when I look at its properties in the Smartsheet app.

I suspect that this is what you're seeing -- i.e., if you look at the column properties in the app after running your code, you'd see that the column type has indeed been changed to MULTI_PICKLIST -- but if you retrieve that column definition via API (either via a Get Sheet request or a Get Column request) -- the value of column.type in the API response is still TEXT_NUMBER.

Believe it or not, this behavior is actually "by design" -- due to the way that Smartsheet added/implemented the new column type MULTI_PICKLIST a while ago (for backwards-compatibility reasons).

• By default, API responses for MULTI* column types (multi-select picklists, multi-select contacts, etc.) will specify column.type of TEXT_NUMBER for any MULTI* column.

• To make an API response specify the actual column type for any MULTI* column (like MULTI_PICKLIST, for example) -- the corresponding API request must specify the level query string parameter, with value set as described here (taken from the docs for the Get Column operation).

So, if I issue another Get Column request for my column -- this time specifying level=2 in the query string -- I see that the value returned for type is MULTI_PICKLIST, as I expect it to be.

Request: GET https://api.smartsheet.com/2.0/sheets/2702602705784708/columns/7331855331184516?level=2

Response:

{
    "id": 7331855331184516,
    "version": 2,
    "index": 4,
    "title": "multi-select col now",
    "type": "MULTI_PICKLIST",
    "options": [
        "One",
        "Two",
        "Three"
    ],
    "validation": false,
    "width": 150
}