Push a python dataframe to Smartsheet using Smartsheet API

Question

I have a python script where I'm trying to fetch data from meraki dashboard through its API. Now the data is stored in a dataframe which needs to be pushed to a Smartsheet using the Smartsheet API integration. I've tried searching the Smartsheet API documentation but couldn't find any solution to the problem. Has anyone worked on this kind of use case before or know a script to push a simple data frame to the smartsheet?

The code is something like this:

for device in list_of_devices:
        
        try:
            dict1 = {'Name': [device['name']],
                "Serial_No": [device['serial']],
                'MAC': [device['mac']],
                'Network_Id': [device['networkId']],
                'Product_Type': [device['productType']],
                'Model': [device['model']],
                'Tags': [device['tags']],
                'Lan_Ip': [device['lanIp']],
                'Configuration_Updated_At': [device['configurationUpdatedAt']],
                'Firmware': [device['firmware']],
                'URL': [device['url']]
                }
        except KeyError:
            dict1['Lan_Ip'] = "NA"

        temp = pd.DataFrame.from_dict(dict1)
        alldata = alldata.append(temp)

alldata.reset_index(drop=True, inplace=True)

The dataframe("alldata") looks something like this:

             Name       Serial_No                MAC  \

0 xxxxxxxxxxxxxxxx xxxxxxxxxxxxxx xxxxxxxxxxxxxxxxx
1 xxxxxxxxxxxxxxxx xxxxxxxxxxxxxx xxxxxxxxxxxxxxxxx
2 xxxxxxxxxxxxxxxx xxxxxxxxxxxxxx xxxxxxxxxxxxxxxxx

the dataframe has somewhere around 1000 rows and 11 columns

I've tried pushing this dataframe similar to the code mentioned in the comments but I'm getting a "Bad Request" error.

smart = smartsheet.Smartsheet(access_token='xxxxxxxx')
sheet_id = xxxxxxxxxxxxx
sheet = smart.Sheets.get_sheet(sheet_id)

column_map = {}
for column in sheet.columns:
    column_map[column.title] = column.id

data_dict = alldata.to_dict('index')


rowsToAdd = []
for i,i in data_dict.items():
    new_row = smart.models.Row()
    new_row.to_top = True


    for k,v in i.items():

        new_cell = smart.models.Cell()
        new_cell.column_id = column_map[k]
        new_cell.value = v
        
        new_row.cells.append(new_cell)

    rowsToAdd.append(new_row)

result = smart.Sheets.add_rows(sheet_id, rowsToAdd)

{"response": {"statusCode": 400, "reason": "Bad Request", "content": {"detail": {"index": 0}, "errorCode": 1012, "message": "Required object attribute(s) are missing from your request: cell.value.", "refId": "1ob56acvz5nzv"}}}

Smartsheet photo where the data must be pushed

Answer 1

The following code adds data from a dataframe to a sheet in Smartsheet -- this should be enough to at least get you started. If you still can't get the desired result using this code, please update your original post to include the code you're using, the outcome you're wanting, and a detailed description of the issue you encountered. (Add a comment to this answer if you update your original post, so I'll be notified and will know to look.)

# target sheet
sheet_id = 3932034054809476
sheet = smartsheet_client.Sheets.get_sheet(sheet_id)

# translate column names to column id
column_map = {}
for column in sheet.columns:
    column_map[column.title] = column.id

df = pd.DataFrame({'item_id': [111111, 222222],
                'item_color': ['red', 'yellow'],
                'item_location': ['office', 'kitchen']})

data_dict = df.to_dict('index')

rowsToAdd = []

# each object in data_dict represents 1 row of data
for i, i in data_dict.items():

    # create a new row object
    new_row = smartsheet_client.models.Row()
    new_row.to_top = True

    # for each key value pair, create & add a cell to the row object
    for k, v in i.items():

        # create the cell object and populate with value
        new_cell = smartsheet_client.models.Cell()
        new_cell.column_id = column_map[k]
        new_cell.value = v

        # add the cell object to the row object
        new_row.cells.append(new_cell)

    # add the row object to the collection of rows
    rowsToAdd.append(new_row)

# add the collection of rows to the sheet in Smartsheet         
result = smartsheet_client.Sheets.add_rows(sheet_id, rowsToAdd)

UPDATE #1 - re Bad Request error

Seems like the error you've described in your first comment below is perhaps being caused by the fact that some of the cells in your dataframe don't have a value. When you add a new row using the Smartsheet API, each cell that's specified for the row must specify a value for the cell -- otherwise you'll get the Bad Request error you've described. Maybe try adding an if statement inside the for loop to skip adding the cell if the value of v is None?

for k,v in i.items():

    # skip adding this cell if there's no value
    if v is None:
        continue

   ...

UPDATE #2 - re further troubleshooting

In response to your second comment below: you'll need to debug further using the data in your dataframe, as I'm unable to repro the issue you describe using other data.

To simplify things -- I'd suggest that you start by trying to debug with just one item in the dataframe. You can do so by adding the line (statement) break at the end of the for loop that's building the dict -- that way, only the first device will be added.

for device in list_of_devices:
        
    try:
        ...
    except KeyError:
        dict1['Lan_Ip'] = "NA"

    temp = pd.DataFrame.from_dict(dict1)
    alldata = alldata.append(temp)

    # break out of loop after one item is added
    break

alldata.reset_index(drop=True, inplace=True)

# print dataframe contents
print (alldata)

If you get the same error when testing with just one item, and can't recognize what it is about that data (or the way it's stored in your dataframe) that's causing the Smartsheet error, then feel free to add a print (alldata) statement after the for loop (as I show in the code snippet above) to your code and update your original post again to include the output of that statement (changing any sensitive data values, of course) -- and then I can try to repro/troubleshoot using that data.

UPDATE #3 - repro'd issue

Okay, so I've reproduced the error you've described -- by specifying None as the value of a field in the dict.

The following code successfully inserts two new rows into Smartsheet -- because every field in each dict it builds contains a (non-None) value. (For simplicity, I'm manually constructing two dicts in the same manner as you do in your for loop.)

# target sheet
sheet_id = 37558492129156
sheet = smartsheet_client.Sheets.get_sheet(sheet_id)

# translate column names to column id
column_map = {}
for column in sheet.columns:
    column_map[column.title] = column.id

#----
# start: repro SO question's building of dataframe
#----

alldata = pd.DataFrame()

dict1 = {'Name': ['name1'],
    "Serial_No": ['serial_no1'],
    'MAC': ['mac1'],
    'Network_Id': ['networkId1'],
    'Product_Type': ['productType1'],
    'Model': ['model1'],
    'Tags': ['tags1'],
    'Lan_Ip': ['lanIp1'],
    'Configuration_Updated_At': ['configurationUpdatedAt1'],
    'Firmware': ['firmware1'],
    'URL': ['url1']
    }
    
temp = pd.DataFrame.from_dict(dict1)
alldata = alldata.append(temp)

dict2 = {'Name': ['name2'],
    "Serial_No": ['serial_no2'],
    'MAC': ['mac2'],
    'Network_Id': ['networkId2'],
    'Product_Type': ['productType2'],
    'Model': ['model2'],
    'Tags': ['tags2'],
    'Lan_Ip': ['lanIp2'],
    'Configuration_Updated_At': ['configurationUpdatedAt2'],
    'Firmware': ['firmware2'],
    'URL': ['URL2']
    }
    
temp = pd.DataFrame.from_dict(dict2)
alldata = alldata.append(temp)

alldata.reset_index(drop=True, inplace=True)

#----
# end: repro SO question's building of dataframe
#----

data_dict = alldata.to_dict('index')

rowsToAdd = []

# each object in data_dict represents 1 row of data
for i, i in data_dict.items():

    # create a new row object
    new_row = smartsheet_client.models.Row()
    new_row.to_top = True

    # for each key value pair, create & add a cell to the row object
    for k, v in i.items():

        # create the cell object and populate with value
        new_cell = smartsheet_client.models.Cell()
        new_cell.column_id = column_map[k]
        new_cell.value = v

        # add the cell object to the row object
        new_row.cells.append(new_cell)

    # add the row object to the collection of rows
    rowsToAdd.append(new_row)

result = smartsheet_client.Sheets.add_rows(sheet_id, rowsToAdd)

However, running the following code (where the value of the URL field in the second dict is set to None) results in the same error you've described:

{"response": {"statusCode": 400, "reason": "Bad Request", "content": {"detail": {"index": 1}, "errorCode": 1012, "message": "Required object attribute(s) are missing from your request: cell.value.", "refId": "dw1id3oj1bv0"}}}

Code that causes this error (identical to the successful code above except that the value of the URL field in the second dict is None):

# target sheet
sheet_id = 37558492129156
sheet = smartsheet_client.Sheets.get_sheet(sheet_id)

# translate column names to column id
column_map = {}
for column in sheet.columns:
    column_map[column.title] = column.id

#----
# start: repro SO question's building of dataframe
#----
alldata = pd.DataFrame()

dict1 = {'Name': ['name1'],
    "Serial_No": ['serial_no1'],
    'MAC': ['mac1'],
    'Network_Id': ['networkId1'],
    'Product_Type': ['productType1'],
    'Model': ['model1'],
    'Tags': ['tags1'],
    'Lan_Ip': ['lanIp1'],
    'Configuration_Updated_At': ['configurationUpdatedAt1'],
    'Firmware': ['firmware1'],
    'URL': ['url1']
    }
    
temp = pd.DataFrame.from_dict(dict1)
alldata = alldata.append(temp)

dict2 = {'Name': ['name2'],
    "Serial_No": ['serial_no2'],
    'MAC': ['mac2'],
    'Network_Id': ['networkId2'],
    'Product_Type': ['productType2'],
    'Model': ['model2'],
    'Tags': ['tags2'],
    'Lan_Ip': ['lanIp2'],
    'Configuration_Updated_At': ['configurationUpdatedAt2'],
    'Firmware': ['firmware2'],
    'URL': [None]
    }
    
temp = pd.DataFrame.from_dict(dict2)
alldata = alldata.append(temp)

alldata.reset_index(drop=True, inplace=True)

#----
# end: repro SO question's building of dataframe
#----

data_dict = alldata.to_dict('index')

rowsToAdd = []

# each object in data_dict represents 1 row of data
for i, i in data_dict.items():

    # create a new row object
    new_row = smartsheet_client.models.Row()
    new_row.to_top = True

    # for each key value pair, create & add a cell to the row object
    for k, v in i.items():

        # create the cell object and populate with value
        new_cell = smartsheet_client.models.Cell()
        new_cell.column_id = column_map[k]
        new_cell.value = v

        # add the cell object to the row object
        new_row.cells.append(new_cell)

    # add the row object to the collection of rows
    rowsToAdd.append(new_row)

result = smartsheet_client.Sheets.add_rows(sheet_id, rowsToAdd)

Finally, note that the error message I received contains {"index": 1} -- this implies that the value of index in this error message indicates the (zero-based) index of the problematic row. The fact that your error message contains {"index": 0} implies that there's a problem with the data in the first row you're trying to add to Smartsheet (i.e., the first item in the dataframe). Therefore, following the troubleshooting guidance I posted in my previous update (Update #2 above) should allow you to closely examine the data for the first item/row and hopefully spot the problematic data (i.e., where the value is missing).