smartsheet-engine 0.1.0.post3

A Python library that simplifies Smartsheet API workflows

Smartsheet Engine

A Python library that simplifies Smartsheet API workflows

Table of Contents

• Smartsheet Engine
  • Table of Contents
  • Features
    • Coming Soon
  • Installation
  • Usage
  • How-tos
    • Get a Smartsheet as a dataframe
    • Append a dataframe to a Smartsheet
    • Update a Smartsheet from a dataframe
    • Delete Smartsheet rows
    • Update column dropdown options
    • Lock or unlock a column
    • Hide or unhide a column
  • License
  • Contributing

Features

• Get a Smartsheet as a dataframe
• Append rows from a dataframe to a Smartsheet
• Update rows on a Smartsheet with a dataframe
• Delete rows from a Smartsheet with a dataframe
• Update the properties of a Smartsheet column
  • Update column dropdown options
  • Lock/unlock a column
  • Hide/unhide a column

Coming Soon

• Update all Smartsheet column properties
• Compare two versions of a dataframe to see how it has changed (diffing)
  • Merge the two versions on a unique identifier
  • Identify what column values changed between the left or right versions
  • Identify any rows that exist only in the left or right versions
• Perform actions on many Smartsheets, instead of just one at a time
• Provision new Smartsheets from a given schema (column names and column properties) and list of email contacts for sharing
• Command-line interface for updating Smartsheets on the fly

Installation

1. Download and install Python
2. Install from PyPI with pip:

pip install smartsheet-engine

Usage

To use Smartsheet Engine in your Notebook or script:

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

from smartsheet_engine.engine import SmartsheetEngine

S = SmartsheetEngine(api_key=smartsheet_api_key)

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

[!TIP] You don't need to provide an api_key when you initialize a SmartsheetEngine object if your API key is already saved in the SMARTSHEET_ACCESS_TOKEN environment variable.

How-tos

[!IMPORTANT] You need to import SmartsheetEngine and initialize it before any of these example snippets will work.

[!NOTE] Column values from a dataframe will only be updated or appended to a Smartsheet if those columns exist in the Smartsheet. If any dataframe column name doesn't exist in the Smartsheet, that column will be ignored.

Get a Smartsheet as a dataframe

Get the contents of the Smartsheet named finished_test_grid and print 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

[!NOTE] SmartsheetEngine converts a Sheet object to a dataframe when you call S.get_sheet(). And it adds the Smartsheet Row ID to a special _ss_row_id column in that dataframe. This is how it maps dataframe rows to Smartsheet rows.

Append a dataframe to a Smartsheet

Append 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)                          # Append all columns
S.append_sheet_rows('test_grid', df, exclude_cols=['rating']) # Append all columns except the `rating` column
S.append_sheet_rows('test_grid', df, include_cols=['number']) # Append only the `number` column

Before Appending	After Appending

Update a Smartsheet from a dataframe

Get a dataframe of the Smartsheet named test_grid, change the dropdown options for the rating column, and then update the column:

import numpy as np

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

S.update_column_picklist('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)                          # Update all columns
S.update_sheet_rows('test_grid', df, exclude_cols=['rating']) # Update all columns except the `rating` column
S.update_sheet_rows('test_grid', df, include_cols=['number']) # Update only the `number` column

Before Updating	After Updating

Delete Smartsheet rows

[!CAUTION] Be careful to select only the rows from df that you want to delete before you call S.delete_sheet_rows(), because every corresponding row in the Smartsheet will be deleted.

Get a dataframe of the Smartshet named test_grid, select only the rows that have the number 2 or 3 in the number column, and then delete 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

Update column dropdown options

Change the dropdown options for the rating column to 'Low', 'Medium', and 'High' on the Smartsheet named test_grid:

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

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')

License

Smartsheet Engine is made available under the MIT License

Contributing

• See CONTRIBUTING for instructions on how to contribute to nwsc