smartsheet-api

Python SDK API to add rows with multiple columns to update to Smartsheet

I have a script that reads columns from my source Smartsheet (SS) and gets the values from the columns in the SS and stores them in variables. This code works. Where I am getting stuck is assigning the variables to a new row and then adding the row to the destination SS. For example:

Source SS, reads row 1 and assigns the following variables:

This code works. Note: The col names on the source SS are the same names of the columns on the destination SS.

Where I'm getting stuck is using the Smartsheet SDK API, assign hub_site_clli and hub_site_name to a new row and then add that row to the destination SS. The destination column IDs for the columns on the destination SS are as follows:

I've looked at and used the Smartsheet's SDK examples but it doesn't show me the entire picture. For example, I cant find any examples on how to update 2 or more columns on a row. They all seem to show just one column in the examples.

I also looked at examples from other scripts and put them all together in the code below.

THIS IS THE CODE I WROTE TO ASSIGN TO A ROW AND APPEND TO THE DESTINATION SS. THIS CODE DOESN'T WORK & IS GETTING AN ERROR.

import smartsheet

smartsheet_client = smartsheet.Smartsheet(access_token)

smartsheet_client = smartsheet.Smartsheet()

hub_site_clli_col = 1088486425710468
   
hub_site_name_col = 5592086053080964

# Make sure we don't miss any error

smartsheet_client.errors_as_exceptions(True)   

sheet = smartsheet_client.Sheets.get_sheet(4812284098465668)
    
# Specify cell values for one row where two columns are updated on the row.
# This is why there are two 'column id' values assigned.

row_a = smartsheet.models.Row()

row_a.to_top = True

row_a.cells.append({

   'column_id': hub_site_name_col,

   'value': "YYUUNN"

   'column_id': hub_site_clli_col,

   'value': "CCIILL123"

})

# Add rows to sheet
response = smartsheet_client.Sheets.add_rows(
  4812284098465668,       # sheet_id
  [row_a])

WHAT I WAS EXPECTING:

I was expecting a new row on the destination sheet with the hub_site_name and hub_site_clli columns filled in with hub_site_name = YYUUNN and hub_site_clli = CCIILL123.

But instead I got an error:

File "./mike.py", line 64, in <module>

add_row()

File "./mike.py", line 30, in add_row

smartsheet_client = smartsheet.Smartsheet(access_token)

File "/home/mtuccillo/.local/lib/python3.8/site-packages/smartsheet/smartsheet.py", line 154, in __init__

self._session = pinned_session(pool_maxsize=max_connections)

File "/home/mtuccillo/.local/lib/python3.8/site-packages/smartsheet/session.py", line 53, in pinned_session

method_whitelist=Retry.DEFAULT_METHOD_WHITELIST.union(['POST'])))

AttributeError: type object 'Retry' has no attribute 'DEFAULT_METHOD_WHITELIST'
Solution

  • A couple of comments. First, it seems like there's a problem with the way you're setting smartsheet_client (your code sets it twice, to different values each time). And second, to specify cell values for a new row that you're adding, you need to define each cell object separately, and then use those cell objects to create the new row.

    The following code sample should fix the issues I've described. (Be sure to replace the text string YOUR_TOKEN_VALUE_HERE with your actual token value.)

    # Specify access token
os.environ['SMARTSHEET_ACCESS_TOKEN'] = 'YOUR_TOKEN_VALUE_HERE'

# Initialize client
# Uses the API token in the environment variable SMARTSHEET_ACCESS_TOKEN
smartsheet_client = smartsheet.Smartsheet()

smartsheet_client.errors_as_exceptions(True)

hub_site_clli_col = 1088486425710468

hub_site_name_col = 5592086053080964

# Define cell object (for the value in the first column) 
cell1 = smartsheet_client.models.Cell({
    'column_id': hub_site_name_col,
    'object_value': 'YYUUNN'
})

# Define cell object (for the value in the second column) 
cell2 = smartsheet_client.models.Cell({
    'column_id': hub_site_clli_col,
    'object_value': 'CCIILL123'
})

# Create row object that contains the 2 cells defined previously
row_a = smartsheet_client.models.Row({
    'cells': [cell1, cell2]
})

row_a.to_top = True

# Add row to sheet
sheetId = 4812284098465668
result = smartsheet_client.Sheets.add_rows(sheetId, [row_a])