Metadata-Version: 2.1
Name: smartsheet_engine
Version: 1.3.0
Summary: A Python library that simplifies Smartsheet API workflows
Author-email: Nick OMalley <nickpeteromalley@gmail.com>
License: MIT License
        
        Copyright (c) 2024 Nick O'Malley, 1npo <nickpeteromalley@gmail.com>
        
        Permission is hereby granted, free of charge, to any person obtaining a copy
        of this software and associated documentation files (the "Software"), to deal
        in the Software without restriction, including without limitation the rights
        to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
        copies of the Software, and to permit persons to whom the Software is
        furnished to do so, subject to the following conditions:
        
        The above copyright notice and this permission notice shall be included in all
        copies or substantial portions of the Software.
        
        THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
        IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
        FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
        AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
        LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
        OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
        SOFTWARE.
Project-URL: Homepage, https://github.com/1npo/smartsheet-engine
Keywords: smartsheet,smartsheet library
Classifier: License :: OSI Approved :: MIT License
Classifier: Programming Language :: Python
Classifier: Programming Language :: Python :: 3
Requires-Python: >=3.9
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: pandas
Requires-Dist: smartsheet-python-sdk

# 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](#smartsheet-engine)
  - [Table of Contents](#table-of-contents)
  - [Features](#features)
    - [Current](#current)
    - [Coming Soon](#coming-soon)
  - [Roadmap](#roadmap)
  - [Installation](#installation)
    - [From PyPI](#from-pypi)
    - [From GitHub](#from-github)
    - [From the Alteryx Python Tool](#from-the-alteryx-python-tool)
  - [Usage](#usage)
  - [How-to Guides](#how-to-guides)
    - [Create, Read, Update, and Delete Smartsheet Data](#create-read-update-and-delete-smartsheet-data)
      - [Get a Smartsheet as a Dataframe](#get-a-smartsheet-as-a-dataframe)
      - [Append a Dataframe to a Smartsheet](#append-a-dataframe-to-a-smartsheet)
      - [Update a Smartsheet From a Dataframe](#update-a-smartsheet-from-a-dataframe)
      - [Delete Smartsheet Rows](#delete-smartsheet-rows)
      - [Provision a Smartsheet](#provision-a-smartsheet)
    - [Analyze Smartsheet Data](#analyze-smartsheet-data)
      - [Compare Two Dataframes and Identify Row Changes](#compare-two-dataframes-and-identify-row-changes)
      - [Compare Two Dataframes and Identify Column Changes](#compare-two-dataframes-and-identify-column-changes)
      - [Compare Two Dataframes and Identify Cell Value Changes](#compare-two-dataframes-and-identify-cell-value-changes)
    - [Modify Smartsheet Object Properties](#modify-smartsheet-object-properties)
      - [Set Column Formula](#set-column-formula)
      - [Set Column Dropdown Options](#set-column-dropdown-options)
      - [Set Column Formatting](#set-column-formatting)
      - [Lock or Unlock a Column](#lock-or-unlock-a-column)
      - [Hide or Unhide a Column](#hide-or-unhide-a-column)
      - [Share a Smartsheet](#share-a-smartsheet)
      - [Update a Shared User's Sheet Permissions](#update-a-shared-users-sheet-permissions)
  - [System Design](#system-design)
    - [Architecture Diagram](#architecture-diagram)
    - [`SmartsheetEngine` Class](#smartsheetengine-class)
    - [`SmartsheetAPIClient` Class](#smartsheetapiclient-class)
    - [`GridRepository` Class](#gridrepository-class)
    - [`SmartsheetGrid` Dataclass](#smartsheetgrid-dataclass)
  - [Acknowledgements](#acknowledgements)
    - [Contributors](#contributors)
  - [License](#license)
  - [Contributing](#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](ROADMAP.md) for the master list of work to be done and features coming soon

## Installation
1. Download and install [Python](https://www.python.org/downloads/) 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](https://knowledge.alteryx.com/index/s/article/How-To-Use-Alteryx-installPackages-in-Python-tool-1583461465434)
```
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](#how-to-guides) for examples)

```python
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
```python
# 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)
```
```text
         _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
```python
# 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)
```

<table>
  <tr>
    <th>Before Appending</th>
    <th>After Appending</th>
  <tr>
    <td><img src='https://github.com/1npo/smartsheet-engine/blob/main/img/append_rows_before.png', alt='Before appending rows'></td>
    <td><img src='https://github.com/1npo/smartsheet-engine/blob/main/img/append_rows_after.png', alt='After appending rows'></td>
  </tr>
</table>

> [!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: 
>
> ```python
> S.append_sheet_rows('test_grid', df)
> ```
> 
> By only including the `number` column:
> 
> ```python
> S.append_sheet_rows('test_grid', df, include_cols=['number'])
> ```
>
> Or excluding the `rating` and `missing_col` columns:
>
> ```python
> S.append_sheet_rows('test_grid', df, exclude_cols=['rating', 'missing_col'])
> ```

#### Update a Smartsheet From a Dataframe


```python
# 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)
```

<table>
  <tr>
    <th>Before Updating</th>
    <th>After Updating</th>
  <tr>
    <td><img src='https://github.com/1npo/smartsheet-engine/blob/main/img/update_rows_before.png', alt='Before updating rows'></td>
    <td><img src='https://github.com/1npo/smartsheet-engine/blob/main/img/update_rows_after.png', alt='After updating rows'></td>
  </tr>
</table>

#### 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**.
```python
# 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)
```

<table>
  <tr>
    <th>Before Deleting</th>
    <th>After Deleting</th>
  <tr>
    <td><img src='https://github.com/1npo/smartsheet-engine/blob/main/img/delete_rows_before.png', alt='Before deleting rows'></td>
    <td><img src='https://github.com/1npo/smartsheet-engine/blob/main/img/delete_rows_after.png', alt='After deleting rows'></td>
  </tr>
</table>

#### 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
```python
# 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
```python
# 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
```python
# 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
```python
# 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
```python
# 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',
              ['alice@acme.com', 'bob@acme.com'],
              '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](https://smartsheet.redoc.ly/#section/Security/Access-Levels) 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](https://pages.github.com/).

### Architecture Diagram
<div align='center'>
<img src='https://github.com/1npo/smartsheet-engine/blob/main/img/smartsheet_engine_architecture.png' alt='smartsheet-engine system architecture diagram'>
</div>

### `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 `SmartsheetGrid`s 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](http://lucidchart.com/)
### Contributors
Thanks and kudos to any contributors will go here

## License
`smartsheet-engine` is made available under the [MIT License](LICENSE)

## Contributing
See [CONTRIBUTING](CONTRIBUTING.md) for instructions on how to contribute to `smartsheet-engine`
