Python API

Smartsheet

class RPA.Smartsheet.Smartsheet(access_token: Optional[str] = None, max_retry_time: Union[str, int] = 30)

Smartsheet is a library for accessing Smartsheet using the Smartsheet API 2.0. It extends smartsheet-python-sdk.

Getting started

To use this library, you need to have a Smartsheet account and an API token. You can get your API token from the Smartsheet Developer Portal. This library currently only supports raw token authentication. Once obtained, you can configure the access token using the Set Access Token keyword or via the access_token argument in the library import.

Working on a sheet

The library supports working on a single sheet at a time. To select a sheet to work on, use the Select Sheet keyword. This will set the sheet as the active sheet for all subsequent operations. Some operations update the sheet, but this will not necessarily be reflected in the active sheet. To refresh the active sheet, use the Refresh Sheet keyword.

Native Smartsheet objects

You can retrieve the native Smartsheet object from many keywords by specifying the native argument. The default will return a more common Python object, such as a dictionary or list. The native object is a class from the smartsheet-python-sdk library and will have additional methods and attributes. The most important attributes available for most native objects are (some may be unavailable for some objects):

id: the unique identifier of the object
name: the name of the object
title: the title of a column
permalink: the URL to the object

ROBOT_AUTO_KEYWORDS = False

ROBOT_LIBRARY_DOC_FORMAT = 'REST'

ROBOT_LIBRARY_SCOPE = 'GLOBAL'

ROW_EXCLUDES = ['filteredOutRows', 'linkInFromCellDetails', 'linksOutToCellsDetails', 'nonexistentCells']: List of supported options to remove cells from rows.

ROW_INCLUDES = {'attachmentFiles': None, 'attachments': 'attachments', 'discussions': 'discussions', 'filters': 'filteredOut', 'rowId': None, 'rowPermalink': 'permalink'}: Dictionary of supported additional objects to ask for via include parameters for rows. Key is the name of the option, value is the API attribute or function to call.

SEARCH_INCLUDES = {'favoriteFlag': None}: Dictionary of supported additional objects to ask for via include parameters for searches. Key is the name of the option, value is the API attribute or function to call.

SEARCH_SCOPES = ['attachments', 'cellData', 'comments', 'folderNames', 'reportNames', 'sheetNames', 'sightNames', 'summaryFields', 'templateNames', 'workspaceNames']

SHEET_INCLUDES = {'attachmentFiles': None, 'attachments': 'attachments', 'discussions': 'discussions', 'rowId': None, 'rowPermalink': 'permalink'}: Dictionary of supported additional objects to ask for via include parameters for sheets. Key is the name of the option, value is the API attribute or function to call.

add_column(title: str, column_type: str = 'TEXT_NUMBER', formula: Optional[str] = None, hidden: bool = False, index: Optional[int] = None, description: Optional[str] = None, primary: bool = False, format_string: Optional[str] = None, locked: bool = False, options: Optional[List[str]] = None, symbol: Optional[str] = None, validation: bool = False, width: Optional[int] = None)

Adds a column to the current sheet.

Parameters

title – Column title.
column_type – Column type, must be a supported type. Defaults to TEXT_NUMBER.
formula – Formula for the column (e.g., =data@row). Defaults to None.
hidden – Whether the column is hidden. Defaults to False.
index – Index of the column. You can insert a column into and existing sheet by setting this index. Index is zero-based. Defaults to None which will add the column to the end of the sheet.
description – Column description. Defaults to None.
primary – Whether the column is considered the primary key for indexing and searching. Defaults to False.
format_string – Column format using a format descriptor string. Defaults to None.
locked – Whether the column is locked. Defaults to False.
options – List of options for a PICKLIST or MULTI_PICKLIST column. Defaults to None.
symbol – When a CHECKBOX or PICKLIST column has a display symbol, you can set the type of symbols by selected an appropriate string from the symbol columns definitions. Defaults to None.
validation – Whether validation has been enabled. Defaults to False.
width – Column width in pixels. Defaults to None.

Example:

Add Column  Title  TEXT_NUMBER
Add Column  Description  TEXT_NUMBER  description=This is a description
Add Column  Formula  TEXT_NUMBER  formula==data@row

ss = Smartsheet(access_token=access_token)
ss.add_column(title="Title", column_type="TEXT_NUMBER")
ss.add_column(title="Description", column_type="TEXT_NUMBER", description="This is a description")
ss.add_column(title="Formula", column_type="TEXT_NUMBER", formula="=data@row")

add_columns(columns: Optional[List[Union[Dict, Column]]] = None) → List[Column]

Adds columns to the current sheet. Columns must be defined as a list of dictionaries or Column objects. Dictionaries can have additional keys set, see Add Column keyword for more information.

Column types must be supported by the Smartsheet API

Parameters: columns – Columns as a list of dictionaries or Column objects.

add_rows(data: Union[List, Table], native: bool = False) → List[Union[OrderedDict, Row]]

Adds rows to the current sheet with the provided data.

You can provide the data in several ways:

As a list of dictionaries: each list item represents a row as a single dictionary. The keys of the dictionary are the column IDs or Titles and the values are the values for the cells.
As a list of lists of dictionaries: each sub list item is a row and each dictionary is a cell. The keys of the dictionary should match cell attributes, e.g., column_id, title, value, etc. See the `smartsheet API docs`_ for more information. The dictionary keys must be provided in snake case. You must use this method to set formulas on the row.
As a list of native Row objects: each Row object is a native object from the API with new values for the cells.
As a Table object: the columns of the Table must either be the column IDs or Titles.

Examples:

Robot Framework:

${row1}=  Create Dictionary  column1=value1  column2=value2
${row2}=  Create Dictionary  column1=value3  column2=value4
${row3}=  Create Dictionary  column1=value5  column2=value6
${data} =  Create List  ${row1}  ${row2}  ${row3}
Add Rows  ${data}

Python:

ss = Smartsheet(access_token=access_token)
row1 = {"column1": "value1", "column2": "value2"}
row2 = {"column1": "value3", "column2": "value4"}
row3 = {"column1": "value5", "column2": "value6"}
data = [row1, row2, row3]
ss.set_rows(data)

convert_row_to_dict(row: Row) → OrderedDict: Converts a row object to a dictionary.

convert_sheet_to_table(sheet: Optional[Sheet] = None) → Table

Converts the current sheet to table. You can provide a differnt native sheet object to be converted via the sheet parameter.

This keyword attempts to return the sheet as a table via RPA.Tables, but if that library is not available in this context, the sheet is returned as its native data model (e.g., no operation is performed).

If the sheet contains additional data from the include argument, they will be appended to the end of the table as additional columns in the data model. These additional objects will be attached as a list of objects depending on the items requested.

create_sheet(name: str, columns: Optional[List[Union[Dict, Column]]] = None, from_sheet_id: Optional[Union[str, int]] = None) → Sheet

Creates a new sheet with the given name and columns, then sets the current sheet to the new sheet and returns it as a native Smartsheet object.

Parameters

name – Name of the new sheet.
columns – List of columns to create in the new sheet.
from_sheet_id – Sheet ID to use as a template for the new sheet.

Example:

${columns}=  Create List  Name  Email
${sheet}=  Create Sheet  My new sheet  ${columns}

ss = Smartsheet(access_token=access_token)
columns = [
    {"title": "Name", "type": "TEXT_NUMBER"},
    {"title": "Email", "type": "TEXT_NUMBER"},
]
sheet = ss.create_sheet("My new sheet", columns)

download_attachment(attachment: Union[int, str, Dict, Attachment], download_path: Optional[Union[str, Path]] = None) → Path

Downloads the provided attachment from the currently selected sheet to the provided download_path, which defaults to the ${OUTPUT_DIR}.

The attachment can be provided as an integer representing the attachments ID, a dictionary with at least the key id or as the native Attachment data model type.

Parameters

attachment – An integar representing the attachment ID, a dictionary with at least the key id, or a native Attachment data model object.
download_path – The path to save the attachment to.

Examples:

Robot Framework:

${attachment}=  Get Attachment  123456789
${path}=  Download Attachment  ${attachment}
Log  ${path}

Python:

ss = Smartsheet(access_token=access_token)
attachment = ss.get_attachment(123456789)
path = ss.download_attachment(attachment)
print(path)

get_application_constants() → ServerInfo: Gets application constants from the server. This is not necessary for most automation scenarios, but may be useful for debugging or for other advanced scenarios.

get_cell_history(row: Union[int, Row], column: Union[int, str, Column]) → Optional[List[Cell]]

Retrieves the history of a cell in a row of the current sheet.

Parameters

row – The row ID, row number, or a Row object.
column – The column ID or title.

Examples:

Robot Framework:

${cell_history}=  Get Cell History  1  Approval
FOR  ${revision} IN  @{cell_history}
    Log  Modified by ${revision.modified_by.email}
END

Python:

ss = Smartsheet(access_token=access_token)
cell_history = ss.get_cell_history(1, "Approval")
for revision in cell_history:
    print(f"Modified by {revision.modified_by.email}")

get_current_user() → User: Gets the current authenticated user, which is also set in the library’s memory as the current user. Call this again if you switch user or begin to impersonate a user.

get_row(row: Union[int, Row], include: Optional[Union[str, List[Any]]] = None, exclude: Optional[Union[str, List[Any]]] = None, native: bool = False) → Union[Row, OrderedDict]

Returns a single row from the current sheet.

You can provide the row as a native Row object or as an integer representing the row ID.

get_sheet(sheet_id: Optional[int] = None, sheet_name: Optional[str] = None, include: Optional[Union[str, List[Any]]] = None, row_ids: Optional[Union[str, List[Any]]] = None, row_numbers: Optional[Union[str, List[Any]]] = None, column_ids: Optional[Union[str, List[Any]]] = None, filter_id: Optional[int] = None, native: bool = False, download_path: Optional[Union[str, Path]] = None) → Union[Table, Sheet]

Retrieves a sheet from Smartsheet. This keyword also sets the currently selected sheet to the returned sheet.

You cannot provide both a sheet_id and sheet_name.

Parameters

sheet_id – The ID of the sheet to get. You cannot supply both a sheet_id and sheet_name.
sheet_name – The name of the sheet to get, it will return the first sheet name matching the provided name. You cannot supply both a sheet_id and sheet_name.
include – Additional metadata which can be retrieved with the table. The list can only contain the following items: attachments, attachmentFiles, discussions, rowPermalink, or ALL. Note that attachmentFiles will only download files if you do not set native to True.
row_ids – A list of row IDs to include. All other rows will be ignored. The list can be a list object or a comma-separated list as a string.
row_numbers – A list of row numbers to include. All other rows will be ignored. The list can be a list object or a comma-separated list as a string.
column_ids – A list of column IDs to only include, others will not be returned. The list can be a list object or a comma-separated list as a string.
filter_id – The ID of a filter to apply. Filtered out rows will not be included in the resulting table.
native – Defaults to False. Set to True to change the return type to the native Smartsheet data model. The native type is useful for passing to other keywords as arguments.
download_path – Defaults to None. Can be set when attachmentFiles is included in the include parameter. All attachments will be downloaded to the provided directory.

Example:

${sheet}=  Get Sheet  sheet_name=My Sheet
FOR  ${row}  IN  &{sheet}
    FOR  ${column}  ${value}  IN  &{row}
        Log  The column ${column} has the value ${value}
    END
END

ss = Smartsheet(access_token=access_token)
sheet = ss.get_sheet(sheet_name="My Sheet", native=True)
for row in sheet:
    for cell in row:
        print(f"The column {cell.column_id} has the value {cell.value}")

get_sheet_owner(sheet_id: Optional[int] = None, sheet_name: Optional[str] = None) → Tuple[str, int]: Returns the owner’s username and ID for the current sheet.

list_attachments() → Optional[List[Attachment]]

Gets a list of all attachments from the currently selected sheet.

This will include attachments to the sheet, rows, and discussions.

Examples:

Robot Framework:

${attachments}=  List Attachments
FOR  ${attachment} IN  @{attachments}
    Log  ${attachment.name}
END

Python:

ss = Smartsheet(access_token=access_token)
attachments = ss.list_attachments()
for attachment in attachments:
    print(attachment.name)

list_columns(sheet_id: Optional[int] = None, sheet_name: Optional[str] = None) → Optional[List[Column]]

Returns a list of columns for the current sheet.

Parameters

sheet_id – The ID of the sheet to get columns from.
sheet_name – The name of the sheet to get columns from.

list_sheet_filters(sheet_id: Optional[int] = None, sheet_name: Optional[str] = None) → Optional[List[SheetFilter]]

Returns a list of available filters for the current sheet. You can specify a different sheet via the sheet_id or sheet_name parameters.

The returned list of filters can be used with the filter_id argument of the get_sheet keyword.

Example:

${filters}=  List Sheet Filters
FOR  ${filter}  IN  @{filters}
    ${filtered_sheet}=  Get Sheet
    ...  sheet_name=My sheet
    ...  filter_id=${filter.id}
    Log  There are ${len(filtered_sheet)} rows in the filtered sheet
END

ss = Smartsheet(access_token=access_token)
my_sheet_id = 123456789
filters = ss.list_sheet_filters()
for filter in filters:
    filtered_sheet = ss.get_sheet(
        sheet_id=my_sheet_id,
        filter_id=filter.id,
        native=True,
    )
    print(
        f"There are {len(filtered_sheet.rows)} rows in the "
        f"filtered sheet"
    )

list_sheets(use_cache: bool = True) → List[Sheet]

Lists all sheets available for the authenticated account. Uses cached lists if available unless use_cache is set to False.

The cached lists is used for other keywords, so if you need to refresh the cache for other keywords to use, you must do so via this keyword.

Parameters: use_cache – Defaults to True. You can set to False to force a reload of the cached list of sheets.

Example:

${sheets}=  List Sheets
FOR  ${sheet}  IN  @{sheets}
    Log  ${sheet.name}

ss = SmartsheetLibrary(account_token=account_token)
sheets = ss.list_sheets()
for sheet in sheets:
    print(sheet.name)

refresh_sheet(native: bool = False) → Union[Table, Sheet]: Refreshes the current sheet from the API and returns it either as a Table or native data model depending on the native argument.

search(query: str, location: Optional[str] = None, modified_since: Optional[Union[str, int]] = None, include: Optional[Union[str, List[Any]]] = None, scopes: Optional[Union[str, List[Any]]] = None) → List[Sheet]

Searches for all sheets for text matching the query. Returns a list of native Smartsheet Sheet objects. You can use the additional parameters to filter the search and increase speed.

Parameters

query – The text to search for.
location – The location to search. When specified with a value of personalWorkspace, the search will be limited to the current user’s personal workspace.
modified_since – The date to search from. This can be either a string or an integer. If an integer is provided, it will be interpreted as a Unix timestamp. If a string is provided, it will be parsed via the Robot Framework time utilities, so you can provided it using keywords like NOW - 1 day.
include – When specified with the value of favoriteFlag, results will either include a favorite attribute or parentObjectFavorite attribute depending on the type of object found by the search engine.
scopes –
If search fails, try using an array for each type of this comma-separated list of search filters. The following strings can be used to filter the search results:
- attachments: Search in attachments.
- cellData: Search in cell data.
- comments: Search in comments.
- folderNames: Search in folder names.
- reportNames: Search in report names.
- sheetNames: Search in sheet names.
- sightNames: Search in sight names.
- summaryFields: Search in summary fields.
- templateNames: Search in template names.
- workspaceNames: Search in workspace names.

Example:

${sheets}=  Search  my search query
FOR  ${sheet}  IN  @{sheets}
    Log  ${sheet.name}

ss = Smartsheet(access_token=access_token)
sheets = ss.search("my search query")
for sheet in sheets:
    print(sheet.name)

set_access_token(access_token: str) → None

Sets the access token to be used when accessing the Smartsheet API.

Learn more about authenticating to Smartsheets here.

Parameters: access_token – The access token created for your Smartsheet user.

Example:

Set Access Token  ${access_token}

smartsheet = Smartsheet(access_token=access_token)
# or
smartsheet.set_access_token(access_token)

set_max_retry_time(max_retry_time: Union[str, int]) → Optional[int]

Sets the max retry time to use when sending requests to the Smartsheet API. Returns the current max retry time.

Parameters: max_retry_time – Maximum time to allow retries of API calls. Can be provided as a time string or int.

set_row(row: Union[int, Row], data: Optional[Union[Dict, List[Dict], Row]] = None, native: bool = False) → Union[Dict, Row]

Updates a single row of the current sheet with the provided data.

You can provide the row as a native Row object or as an integer representing the row ID. You may omit the data argument if you are providing a native Row object.

You can provide the data in several ways:

As a dictionary: the keys of the dictionary are the column IDs or Titles and the values are the values for the cells.
As a list of dictionaries: each dictionary is a cell. The keys of the dictionary should match cell attributes, e.g., column_id, title, value, etc. See the `smartsheet API docs`_ for more information. The dictionary keys must be provided in snake case. You must use this method to set formulas on the row.
As a native Row object: a native object from the API with new values for the cells.

For examples, see Set Rows.

set_rows(data: Union[List, Table], native: bool = False) → List[Union[OrderedDict, Row]]

Updates rows of the current sheet with the provided data.

Note

In order to update rows, you must identify the rows to the API. You can do this by providing the rowId or rowNumber as a column in the data. The ID must be the API ID, while the number is the row number per the UI. This can only be excluded if the length of the data matches the length of the sheet.

You can provide the data in several ways:

As a list of dictionaries: each list item represents a row as a single dictionary. The keys of the dictionary are the column IDs or Titles and the values are the values for the cells.
As a list of lists of dictionaries: each sub list item is a row and each dictionary is a cell. The keys of the dictionary should match cell attributes, e.g., column_id, title, value, etc. See the `smartsheet API docs`_ for more information. The dictionary keys must be provided in snake case. You must use this method to set formulas on the row.
As a list of native Row objects: each Row object is a native object from the API with new values for the cells.
As a Table object: the columns of the Table must either be the column IDs or Titles.

Examples:

Robot Framework:

${row1}=  Create Dictionary  rowId=123  column1=value1  column2=value2
${row2}=  Create Dictionary  rowId=456  column1=value3  column2=value4
${row3}=  Create Dictionary  rowId=789  column1=value5  column2=value6
${data} =  Create List  ${row1}  ${row2}  ${row3}
Set Rows  ${data}

# Or work with native row objects to update them.
${row1}=  Get Row  123
FOR  ${cell}  IN  @{row1.cells}
    IF  ${cell.column_id} == 123
        ${cell.value}=  Set Variable  New Value
    END
END
${data}=  Create List  ${row1}
Set Rows  ${data}

Python:

ss = Smartsheet(access_token=access_token)
row1 = {"rowId": 123, "column1": "value1", "column2": "value2"}
row2 = {"rowId": 456, "column1": "value3", "column2": "value4"}
row3 = {"rowId": 789, "column1": "value5", "column2": "value6"}
data = [row1, row2, row3]
ss.set_rows(data)

# or work with native row objects to update them.
row1 = ss.get_row(123)
for cell in row1.cells:
    if cell.column_id == 123:
        cell.value = "New Value"
data = [row1]
ss.set_rows(data)

property sheets: List[Sheet]: Full list of cached sheets.

unselect_current_sheet() → None: Resets the current sheet to None.

update_column(column: Union[int, str, Column], **kwargs) → Column

Updates a column in the current sheet. See the Add Column keyword for a list of supported attributes.

Parameters

column – Column ID or title.
kwargs – Column attributes to update. See Add Column keyword for a list of supported attributes.