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 [email protected] 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"]})
    


  • Emileah Hiatt
    Emileah Hiatt ✭✭
    edited 04/21/20

    @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!