Intermediate

Smartsheet is a cloud-based management tool used by over 7000 companies which include Fortune 500 companies like Netflix. This system helps the user share documents, assign tasks, and much more. Smartsheet has its API available in Python which allows users to update and read smartsheets. This API also manages folders and administrator accounts. Python also has small-scale modules in which API isn’t needed.

In this article we shall look at how to setup smartsheet SDK, accessing smartsheet files, and updating them using different methods.

Preparation

1. Installing Prerequisites Modules

Before installing the smartsheet SDK please note having the modules listed below is compulsory. The installation method is also given.

1- requests
2- requests-toolbelt
3- six
4- certifi
5- python-dateutil

pip install requests
pip install requests-toolbelt
pip install six
pip install certifi
pip install python-dateutil

2. Installaing Smartsheet SDK

To install the Smartsheet SDK module in python can be done using the package manager (pip). The installation method is:

pip install smartsheet-python-sdk

If you already have Smartsheet SDK installed please remember to upgrade it using the following method:

pip install smartsheet-python-sdk --upgrade

Getting API Access Token from Smartsheet

The first step in getting started with Smartsheet Python SDK is generating the access token. To generate the access token follow the following steps:

Step 1:

Login to your smartsheet account and if you do not have an account on Smartsheet you can create one for free.

Step 2:

Go to your account and then “Apps and Integration”.

Step 3:

Click “Generate new access Token” and give your access token a name as you can see below.

Step 4:

A token will be generated against the name. Don’t forget to copy it as it won’t be available later on.

Finally, when you will see the screen given below it means your access token has been created successfully.

Adding Access Token to your Program

They are two ways of adding the access token in your code.

1. First Method

If you are using the Pycharm IDE the token needs to be configured in the Environment Variables through Edit Configuration. The same goes for Anaconda Spyder IDE.

2. Second Method

The access token can be added directly in the code and a connection can be made. This method isn’t secure in the long run since anyone can see your code.

Creating a Sample Smartsheet

In this article, we shall be working with a sample smartsheet that I have created in excel.

After creating the sample excel file to the Smartsheet homepage and then from the Create dropdown select import excel file.

Your smartsheet is successfully imported if you see the following screen. Click import and you will have a smartsheet ready to use for this article.

Creating a Successful Connection

After creating a smartsheet access token and smartsheet let’s see if our connection is successful. To check if a connection is successful we need to first initialize a client that uses the access token in its API call.

import smartsheet

SMARTSHEET_ACCESS_TOKEN = "08nyhgggzczqlwbdlr62ru8jrh"
# Initialize client
smartsheet_client = smartsheet.Smartsheet(SMARTSHEET_ACCESS_TOKEN)
print(smartsheet_client)
# Check for any errors
smartsheet_client.errors_as_exceptions(True)

Output:
If the code executes successfully that means connection is successful.

Using GetSheet Method to connect to a single worksheet

First, we need to note the sheet id of the sample sheet just imported. Open the smartsheet and open the File dropdown and select Properties. Copy the sheet id and save it in a variable.

You can create an API Call using the get sheet method with the sheet id parameter. The API call returns a JSON response. We will be printing the sheet name for now.

#Sheet_id gotten from Smartsheet
sheet_id = 2503335061284740

sheet = smartsheet_client.Sheets.get_sheet(sheet_id)
print("Loaded Sheet: " + sheet.name)

Output:

Reading Cell Values from the Smartsheet

First, we need to understand the structure of JSON which is returned when a connection is made with a sheet. Within rows we have cells and in the cell array, we have keys by the name, value given. So our code will be structured within two loops as given.

import smartsheet

SMARTSHEET_ACCESS_TOKEN = "08nyhgggzczqlwbdlr62ru8jrh"
# Initialize client
smartsheet_client = smartsheet.Smartsheet(SMARTSHEET_ACCESS_TOKEN)
#Sheet_id gotten from Smartsheet
sheet_id = 2503335061284740
sheet = smartsheet_client.Sheets.get_sheet(sheet_id)

#Reading Row in Loop
for row in sheet.rows:
   #For each row read cell value
   for cell in row.cells:
       if cell.value!= None:
           print(cell.value)

Output:

Understanding the Smartsheet Structure

Before learning how to update a cell in a smartsheet, we need to understand the structure of our smartsheet. A smartsheet has three major components:

1. Rows
2. Columns
3. Cells

Now, each row and column has a unique id that we use to distinguish them. We will learn how to map rows and columns to their IDs further on.

1. Map Columns to Column ID

In smartsheets, each column has a column id which is then used for updating cells or rows which we will see further on in this article. We can create a dictionary that maps the column value to the column id.

#Empty Dictionary
column_map ={}
for column in sheet.columns:
 #Map Values
 column_map[column.title] = column.id
print(column_map)

2. Map Rows to Row IDs

As columns have column ids that can be updated rows in the smartsheet also can be mapped to row id which is further used to update the specific row. We can create an empty dictionary and map the row id to the row number using a counter variable.

row_map = {}
i=0 #counter
for rows in sheet.rows:
 #Map Row ID
 row_map[i]=rows.id
 i=i+1
print(row_map)

Output:

Creating a new Cell to Update

Updating a cell requires three things. First is to build a new cell with values needed and then build an existing row in which the cell exists. Finally calling the update row method to update the smartsheet.

1. Build a Cell

To update a particular cell we need to first build a new cell that will access the column id using the dictionary built above. Then we need to assign an updated value. To create a new cell the method used is:

smartsheet_object_variable.models.Cell()

After assigning this method to a variable the properties can be assigned meaningful values to it.

#Build the cell you are updating with
new_cell = smartsheet_client.models.Cell()
#Update column Remaining
new_cell.column_id = column_map["Remaining"]
new_cell.value = 0
new_cell.strict = False

2. Build a Row

The second step in updating a cell is building the row according to its column id. For this example, we shall be updating the first row. To build a row the method used is:

smartsheet_object_variable.models.Row()

After assigning this method to a variable the properties can be assigned meaningful values to it. The cells append method is used to update the row value.

# Build the row to update
get_row = smartsheet.models.Row()
get_row.id = row_map[0]
get_row.cells.append(new_cell)

3. Using Update Row Method

Finally after building a new cell and row to update the update row method is called with sheet id as parameter and the row variable that we built as shown below.

updated_row = smartsheet_client.Sheets.update_rows(sheet_id,[get_row])

The code will have run successfully if there are no errors and you can reload your smartsheet to see the updated value in the cell.

Update an Image in a Cell

Smartsheet also allows you to update a given cell with an image. To do this the method “add_image_to_cell” is used. The add image method takes the parameters sheet id, row id, column id, picture, and the file type as parameters.

You need to add the specified image in your current directory and use the “add_image_to_cell” function which will upload the file to smartsheet automatically.

import smartsheet

SMARTSHEET_ACCESS_TOKEN = "08nyhgggzczqlwbdlr62ru8jrh"
# Initialize client
smartsheet_client = smartsheet.Smartsheet(SMARTSHEET_ACCESS_TOKEN)

#Empty Dictionary
column_map ={}
for column in sheet.columns:
 #Map Values
 column_map[column.title] = column.id
print(column_map)

row_map = {}
i=0 #counter
for rows in sheet.rows:
 #Map Row ID
 row_map[i]=rows.id
 i=i+1
print(row_map)

column_id = column_map["Remaining"]
row_id = row_map[4]
picture = "flower.jpg"
file_type = "jpg"
smartsheet_client.Cells.add_image_to_cell(sheet_id, row_id, column_id, picture, file_type)

The code above successfully adds an image to the cell specified.

Output:

Conclusion

Smartsheet is a very versatile tool which can be used to act as a powerful end user database. With the power of automating data reads or data writes with python 3, you have the option to provide some powerful solutions to users.

Subscribe to our newsletter