Error Code 4000 "Unexpected Error"

Emileah Hiatt

I am getting error 4000 when trying to write to a sheet using python. All it says is "An unexpected error has occurred. Please contact Smartsheet support at api@smartsheet.com for assistance." I have successfully written to other smartsheets using the same code so I do not think it's something I am doing wrong on my end. How do I get get past this? Thank you.

Etienne Mermillod

Hi,

Did you get the error only once, or can you reproduce the issue ?

If you can reproduce the issue, can you look for any relevent difference between the two sheets and provide more information ?

Emileah Hiatt

I get the same error every time I try to write to this one sheet, yes. It's never happened when I've written to other sheets. I have already looked through the properties and settings to see if there is something different with this one, but I don't see anything that stands out to me. I was hoping someone else had experienced the same thing and could give me some insight.

Emileah Hiatt

@Etienne Mermillod I've narrowed down the problem to a particular column that is a multiple-select string column. If I try to give it a value from one of the options, it throws the 4000 error. If I give it an empty string then it's fine.

Etienne Mermillod

Writing to a multi drop down list is very different form a single dropdown list, the same code won't work, So first you can try to by not setting a value to the cell.

What language are you using to query the api, the c# sdk, can you provide a sample code ?

Emileah Hiatt

I'm using python. I have a pandas dataframe that is a subset of the columns in the smartsheet that I need to write to the smart sheet.

First I create a list of the columns from the dataframe to generate a dictionary of the column titles with their respective columns ids.

sheet = ss_client.Sheets.get_sheet(sheet_id)

col_lst = df.columns.tolist()

col_dct = {}

for col in sheet.columns:

if col.title in col_lst:

     col_dct[col.title] = col.id

Then I create list of row objects that are made from the dataframe that I then write to the smartsheet.

rows_lst = []

for index, row in df.iterrows():

ss_row = ss_client.models.Row()

ss_row.to_bottom = True

   for col in col_dct.keys():

       ss_row.cells.append({'column_id': col_dct[col], 'objectValue': row[col]})

rows_lst.append(ss_row)

response = ss_client.Sheets.add_rows(sheet_id, rows_lst)

Again, an empty string in the mult-select column works fine, but trying to give the value from the dataframe, even when it's one of the allowed options, doesn't work. Please let me know how I need to change it. Thank you!

Emileah Hiatt

@Etienne Mermillod meant to tag you in the previous answer ^

Etienne Mermillod

When you want to set a multiple picklisy cell value you need to set the object value property:

On the API documentation it looks like this :

{
    "cells": [{
        "columnId": 285878125913988,
        "objectValue": {
            "objectType": "MULTI_PICKLIST",
            "values": ["hello", "world"]
        }

    }]
}

In other words, when looping over the keys, you first have to detect that the current column is an multipick list and then build the cell value with something like :

ss_row.cells.append({'column_id': col_dct[col], 
   'objectValue': {'objectType': 'MULTI_PICKLIST','values': [row[col], "world"]})

Emileah Hiatt

@Etienne Mermillod Ok, so I tried adding the level=2 and include=objectValue parameters to my get_sheet query so I can see which columns are multi_picklist, and it doesn't seem to accept that as an option.

sheet = ss_client.Sheets.get_sheet(28176781993860, level=2, include='objectValue')

This is the error:

ValueError: `MULTI_PICKLIST` is an invalid value for ObjectValue`object_type`, must be one of ['DATE', 'DATETIME', 'ABSTRACT_DATETIME', 'CONTACT', 'DURATION', 'PREDECESSOR_LIST', 'MULTI_CONTACT']

Without using those parameters, it looks like the multi_picklist columns are the ones with version=2, so I tried that as well and implemented what you said. First I identify those columns and add them to a separate list, (which works well, it caught all the multi-select columns) then build the cells for those columns differently in my loop.

sheet_id = 28176781993860

sheet = ss_client.Sheets.get_sheet(sheet_id)
col_lst = df.columns.tolist()
multi_col_lst = []
col_dct = {}
for col in sheet.columns:
    if col.title in col_lst:
        col_dct[col.title] = col.id
    if col.version == 2:
        multi_col_lst.append(col.title)


rows_lst = []
for index, row in df.iterrows():
    ss_row = ss_client.models.Row()
    ss_row.to_bottom = True
    for col in col_dct.keys():
            if col in multi_col_lst:
                ss_row.cells.append({'column_id': col_dct[col],
                                'objectValue': {'objectType':'MULTI_PICKLIST',
                                                'values':[row[col]]}})
            else:
                ss_row.cells.append({'column_id': col_dct[col],
                                'objectValue': row[col]})
    rows_lst.append(ss_row)
   
response = ss_client.Sheets.add_rows(sheet_id, rows_lst)

This time I get two errors.

ValueError: `MULTI_PICKLIST` is an invalid value for ObjectValue`object_type`, must be one of ['DATE', 'DATETIME', 'ABSTRACT_DATETIME', 'CONTACT', 'DURATION', 'PREDECESSOR_LIST', 'MULTI_CONTACT']
ValueError: ("Can't convert %s to %s in TypedList", {'column_id': 8487685309392772, 'objectValue': {'objectType': 'MULTI_PICKLIST', 'values': ['NA']}}, <class 'smartsheet.models.cell.Cell'>)

It seems to me like it doesn't even recognize that MULTI_PICKLIST columns are a thing. I'm confused. Thank you so much for helping me!

Emileah Hiatt

@Etienne Mermillod So it turns out I was working with an outdated version of the Python Smartsheet SDK. As soon as I upgraded everything worked fine. So sorry for not realizing that sooner. Thanks for your help!