smartsheet-engine 1.5.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.

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
  • Developer's Guide
    • API Reference
    • System Design
      • Architecture Diagram
      • SmartsheetEngine Class
      • SmartsheetAPIClient Class
      • GridRepository Class
      • SmartsheetGrid Dataclass
    • Testing
      • Current Coverage
      • How to Run the Tests and Generate the Report
    • Linting
      • Current Results
      • How to Run the Linter and Generate the Report
  • Acknowledgements
  • 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
    • Create a Smartsheet From a Schema
    • Create a Column
• Analyze Smartsheet Dataframes
  • 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
  • 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, and start a SmartsheetEngine with your API key:

from smartsheet_engine import SmartsheetEngine

S = SmartsheetEngine(api_key=smartsheet_api_key)

3. Use the engine as needed in your workflow (see How-To Guides for examples)

[!TIP] You don't need to provide an API key to SmartsheetEngine if your key is already stored in the SMARTSHEET_ACCESS_TOKEN environment variable.

[!CAUTION] Do not 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 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.

[!WARNING] Whenever you call get_sheet(), it only downloads the most current Smartsheet once, and then saves the results in a "repository". Every time you call get_sheet() after that, it will always give you the version of the Sheet that was initially saved to the "repository". This is to avoid making excessive API calls.

So if your workflow needs to make updates to a Smartsheet, and then use get_sheet() to get an updated copy of the Smartsheet, you must provide the refresh=True option. This will force SmartsheetEngine to download the most current Sheet from the API, instead of getting the initial version from the repository. For example:

df = S.get_sheet('finished_test_grid', refresh=True).sheet_df
print(df)

All SmartsheetEngine actions call get_sheet() before performing their action, so this limitation applies to all actions. But all actions accept the refresh=True option as a workaround.

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`, and restricts the column to only allow these values

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

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 default access 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!

Developer's Guide

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

API Reference

[!NOTE]

Coming soon! Will be available as soon as Sphinx docs are configured and generated.

System Design

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

Testing

Current Coverage

[!NOTE] Current test coverage is ⭐ 94% ⭐ (as of commit 170b34a).

Name	Stmts	Miss	Cover	Missing
smartsheet_engine/__init__.py	206	22	89%	125-126, 133-138, 162-168, 214-218, 228, 650, 722-726, 738, 751
smartsheet_engine/client.py	188	23	88%	97-100, 126, 147-155, 342, 344, 346, 399, 401, 403, 405, 412, 420, 460-463
smartsheet_engine/grids.py	98	23	77%	207-226, 241-252
smartsheet_engine/utils.py	18	1	94%	49
tests/test_client.py	218	0	100%
tests/test_data.py	86	0	100%
tests/test_grids.py	90	0	100%
tests/test_smartsheet_engine.py	210	0	100%
tests/test_utils.py	17	0	100%
TOTAL	1131	69	94%

How to Run the Tests and Generate the Report

To run the tests and generate the coverage reports:

1. Install pytest and coverage: pip install pytest coverage
2. Change directory to the smartsheet-engine project root
3. Run the tests: coverage run -m pytest
4. Generate the Markdown report: coverage report -m --format=markdown
5. Generate the HTML report: coverage html -d docs/test_coverage_html

Linting

Current Results

[!NOTE] Current Pylint score is ⭐ 9.81/10 ⭐ (as of commit 170b34a).

Pylint doesn't check for warning W0311 in these results because it causes excessive warnings. There are many places where spaces are added after tabs to align code for readability and consistent style, but it triggers warning W0311.

How to Run the Linter and Generate the Report

See pylint_results_170b34a.log for the current Pylint results.

To generate the report:

1. Install pylint: pip install pylint
2. Change directory to the smartsheet-engine project root
3. Run Pylint: pylint -d W0311 smartsheet_engine > docs/linting/pylint_results_{commit}.log

Acknowledgements

• The architecture diagram was made with Lucidchart

License

This library was created by Nick O'Malley and is currently unlicensed.

Contributing

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