smartsheet-engine 1.3.0

A Python library that simplifies Smartsheet API workflows

Smartsheet Engine

A Python library that simplifies Smartsheet API workflows

Smartsheet Engine lets you perform high-level actions on a Smartsheet — such as updating rows or locking a column — using only one function call. And it represents Smartsheets as dataframes, so it can be seamlessly integrated into existing workflows that use dataframes.

---

Badges will go here

[!WARNING] This library is in beta and under active development. Please use it with care.

Table of Contents

• Smartsheet Engine
  • Table of Contents
  • Features
    • Current
    • Coming Soon
  • Roadmap
  • Installation
    • From PyPI
    • From GitHub
    • From the Alteryx Python Tool
  • Usage
  • How-to Guides
    • Create, Read, Update, and Delete Smartsheet Data
      • Get a Smartsheet as a Dataframe
      • Append a Dataframe to a Smartsheet
      • Update a Smartsheet From a Dataframe
      • Delete Smartsheet Rows
      • Provision a Smartsheet
    • Analyze Smartsheet Data
      • Compare Two Dataframes and Identify Row Changes
      • Compare Two Dataframes and Identify Column Changes
      • Compare Two Dataframes and Identify Cell Value Changes
    • Modify Smartsheet Object Properties
      • Set Column Formula
      • Set Column Dropdown Options
      • Set Column Formatting
      • Lock or Unlock a Column
      • Hide or Unhide a Column
      • Share a Smartsheet
      • Update a Shared User's Sheet Permissions
  • System Design
    • Architecture Diagram
    • SmartsheetEngine Class
    • SmartsheetAPIClient Class
    • GridRepository Class
    • SmartsheetGrid Dataclass
  • Acknowledgements
    • Contributors
  • License
  • Contributing

Features

Current

• Create, Read, Update, and Delete Smartsheet Data
  • Get a Smartsheet as a Dataframe
  • Append a Dataframe to a Smartsheet
  • Update a Smartsheet From a Dataframe
  • Delete Smartsheet Rows
• Modify Smartsheet Object Properties
  • Set Column Formula
  • Set Column Dropdown Options
  • Lock or Unlock a Column
  • Hide or Unhide a Column
  • Share a Smartsheet

Coming Soon

• Create, Read, Update, and Delete Smartsheet Data
  • Provision a Smartsheet
• Analyze Smartsheet Data
  • Compare Two Dataframes and Identify Row Changes
  • Compare Two Dataframes and Identify Column Changes
  • Compare Two Dataframes and Identify Cell Value Changes
• Modify Smartsheet Object Properties
  • Set Column Formatting
  • Change a Shared User's Access Level
• Other
  • Perform Bulk Actions on Many Smartsheets
  • Command-Line Interface

Roadmap

See the roadmap for the master list of work to be done and features coming soon

Installation

1. Download and install Python if needed
2. Install smartsheet-engine

From PyPI

pip install smartsheet-engine

From GitHub

git clone https://github.com/1npo/smartsheet-engine.git
cd smartsheet-engine
pip install .

From the Alteryx Python Tool

Alteryx.installPackage(package="smartsheet-engine")

Usage

To use smartsheet-engine in your script, Python Tool, or Notebook:

1. Get your Smartsheet API key and save it to a variable, such as smartsheet_api_key
2. Import the SmartsheetEngine class
3. Initialize a SmartsheetEngine object with your API key
4. Use the engine as needed in your workflow (see How-To Guides for examples)

from smartsheet_engine import SmartsheetEngine

S = SmartsheetEngine(api_key=smartsheet_api_key)

[!TIP] SmartsheetEngine will look in the SMARTSHEET_ACCESS_TOKEN environment variable for your API key first. You don't need to specify an API key if your API key is stored in this environment variable.

[!CAUTION] Don't hardcode your API key into your script, Python Tool, or widget. Put it in a secret store or an environment variable instead.

How-to Guides

Create, Read, Update, and Delete Smartsheet Data

Get a Smartsheet as a Dataframe

# Gets the dataframe for the Smartsheet called `finished_test_grid`
# and prints the dataframe

df = S.get_sheet('finished_test_grid').sheet_df
print(df)

         _ss_row_id  number   rating
0   123734752464772     1.0   Lowest
1  7876435046272900     2.0      Low
2  2246935512059780     3.0   Medium
3  2463203892629380     4.0     High
4  6966803519999876     5.0  Highest

[!WARNING] When you call S.get_sheet(), SmartsheetEngine downloads the contents of that Smartsheet and creates a dataframe from it. Then it adds a _ss_row_id column to the dataframe, which contains the ID of the corresponding row in the Smartsheet. This is how SmartsheetEngine maps dataframe rows to Smartsheet rows.

This means:

• If you delete the _ss_row_id column, you won't be able to use the dataframe to update or delete rows on the Smartsheet.
• If you update a Smartsheet with the dataframe, the data in each row of the dataframe will be inserted into the Smartsheet row that matches the ID in the _ss_row_id column.

Append a Dataframe to a Smartsheet

# Appends 2 rows from a dataframe to the Smartsheet named `test_grid`

df = pd.DataFrame({
    'number':       [4, 5],
    'rating':       [None, None],
    'missing_col':  ['data', 'ignored'], })

S.append_sheet_rows('test_grid', df)

Before Appending	After Appending

[!NOTE] Column values from a dataframe will only be updated on or appended to a Smartsheet if those columns exist in the Smartsheet. The column names need to match exactly. Any dataframe column that doesn't exist in the Smartsheet will be ignored.

You can choose to only update/append certain columns, or NOT to update/append certain columns, by using the include_cols and exclude_cols arguments.

For example, in this how-to guide -- number is the only column that will be updated, because rating contains no data, and missing_col doesn't exist as a column in the Smartsheet.

So you can achieve the same effect as this:

S.append_sheet_rows('test_grid', df)

By only including the number column:

S.append_sheet_rows('test_grid', df, include_cols=['number'])

Or excluding the rating and missing_col columns:

S.append_sheet_rows('test_grid', df, exclude_cols=['rating', 'missing_col'])

Update a Smartsheet From a Dataframe

# Gets the dataframe for the Smartsheet named `test_grid`, changes the
# dropdown options for the `rating` column, and then updates the column

import numpy as np

df = S.get_sheet('test_grid').sheet_df

S.update_column_dropdown('test_grid', 'rating', ['Lowest', 'Low', 'Medium', 'High', 'Highest'])

conditions = [
    df['number'] == 1,
    df['number'] == 2,
    df['number'] == 3,
    df['number'] == 4,
    df['number'] == 5,
]
choices = [
    'Lowest',
    'Low',
    'Medium',
    'High',
    'Highest',
]
df['rating'] = np.select(conditions, choices)

S.update_sheet_rows('test_grid', df)

Before Updating	After Updating

Delete Smartsheet Rows

[!CAUTION] Before you run S.delete_sheet_rows(sheet_name, df), make sure that df only includes the rows you want to delete from the Smartsheet. Because when you run that function, every Smartsheet row that has an ID listed in df._ss_row_id will be deleted from the Smartsheet.

# Gets the dataframe for the Smartshet named `test_grid`, selects
# only the rows that have the number 2 or 3 in the number column,
# and then deletes them

df = S.get_sheet('test_grid').sheet_df

df = df[df['number'].isin([2,3])]

S.delete_sheet_rows('test_grid', df)

Before Deleting	After Deleting

Provision a Smartsheet

[!NOTE]

Coming soon!

Analyze Smartsheet Data

Compare Two Dataframes and Identify Row Changes

[!NOTE]

Coming soon!

Compare Two Dataframes and Identify Column Changes

[!NOTE]

Coming soon!

Compare Two Dataframes and Identify Cell Value Changes

[!NOTE]

Coming soon!

Modify Smartsheet Object Properties

Set Column Formula

# Changes the column formula for the `month_rated` column to "=MONTH([date_rated]@row)"
# on the Smartsheet named `test_grid`.

S.set_column_formula('test_grid', 'month_rated', '=MONTH([date_rated]@row)')

Set Column Dropdown Options

# Changes the dropdown options for the `rating` column
# to `Low, Medium, and High` on the Smartsheet named `test_grid`

S.set_column_dropdown('test_grid', 'rating', ['Low', 'Medium', 'High'])

Set Column Formatting

[!NOTE]

Coming soon!

Lock or Unlock a Column

# Locks and then unlock the `rating` column on the Smartsheet
# named `test_grid`

S.lock_column('test_grid', 'rating')
S.unlock_column('test_grid', 'rating')

Hide or Unhide a Column

# Hide and then unhide the `rating` column on the Smartsheet
# named `test_grid`

S.hide_column('test_grid', 'rating')
S.unhide_column('test_grid', 'rating')

Share a Smartsheet

# Share a Smartsheet named `test_grid` with a list of email addresses, giving
# those users the EDITOR_SHARE access level, and send them an email notification
# that the sheet has been shared with them

S.share_sheet('test_grid',
              ['[e-mail]', '[e-mail]'],
              'EDITOR_SHARE',
              send_email=True)

[!NOTE]

• One or more email addresses must be provided as either a string or a list. When providing multiple emails as a string, each email address must be separated by a semicolon.
• The cess level is VIEWER if no access level is specified. See the Smartsheet API documentation for a list of valid Access Levels.
• S.share_sheet() will NOT send email notifications by default. If you want Smartsheet to notify the user(s) that the Sheet has been shared with them, then you MUST set send_email to True.

Update a Shared User's Sheet Permissions

[!NOTE]

Coming soon!

System Design

[!NOTE] This documentation will be refined, expanded, and eventually migrated into Sphinx docs that will be hosted on GitHub Pages.

Architecture Diagram

SmartsheetEngine Class

Provides a set of high-level Smartsheet actions, such as appending dataframe rows to a Smartsheet or locking a column

• Uses the SmartsheetAPIClient class to interact with the Smartsheet API
• Uses the GridRepository class to manage the Smartsheet SDK Sheet objects that represent all the Smartsheets that are available to the user

SmartsheetAPIClient Class

Simplifies using Smartsheet's Python SDK

• Converts dataframes to lists of Smartsheet SDK Row, Column, and Cell objects, and vice-versa
• Sends the lists of SDK objects to the API
• Retrieves data from the API and returns it to the user

GridRepository Class

Stores, retrieves, and modifies SmartsheetGrid objects

• Simple in-memory repository
• Stores SmartsheetGrids in a list
• Can create, read, and update SmartsheetGrid objects

SmartsheetGrid Dataclass

Contains a Smartsheet Sheet object, relevant metadata, and a dataframe representation of the Sheet

• Sheet Name, ID, and user's access level
• Column map (between English column name and Smartsheet Column ID)
• The Smartsheet SDK Sheet object
• The pandas dataframe representation of the Sheet
• Created and modified timestamps
• Flags for whether or not the Sheet exists in a folder or a workspace
• Name and ID of the folder or workspace

Acknowledgements

• The architecture diagram was made with Lucidchart

Contributors

Thanks and kudos to any contributors will go here

License

smartsheet-engine is made available under the MIT License

Contributing

See CONTRIBUTING for instructions on how to contribute to smartsheet-engine