Error Code 4000 "Unexpected Error"
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.
Answers
-
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 ?
-
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.
-
@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.
-
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 ?
-
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!
-
@Etienne Mermillod meant to tag you in the previous answer ^
-
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"]})
-
@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!
-
@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!
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives