Smartsheets
This page guides you through the process of setting up the Smartsheets source connector.
Prerequisites
To configure the Smartsheet Source for syncs, you'll need the following:
- A Smartsheets API access token - generated by a Smartsheets user with at least read access
- The ID of the spreadsheet you'd like to sync
Step 1: Set up Smartsheets
Obtain a Smartsheets API access token
You can generate an API key for your account from a session of your Smartsheet webapp by clicking:
- Account (top-right icon)
- Apps & Integrations
- API Access
- Generate new access token
For questions on advanced authorization flows, refer to this.
Prepare the spreadsheet ID of your Smartsheet
You'll also need the ID of the Spreadsheet you'd like to sync. Unlike Google Sheets, this ID is not found in the URL. You can find the required spreadsheet ID from your Smartsheet app session by going to:
- File
- Properties
Step 2: Set up the Smartsheets connector in Airbyte
For Airbyte Cloud:
- Log into your Airbyte Cloud account.
- In the left navigation bar, click Sources. In the top-right corner, click +new source.
- On the Set up the source page, enter the name for the Smartsheets connector and select Smartsheets from the Source type dropdown.
- Authenticate via OAuth2.0 using the API access token from Prerequisites
- Enter the start date and the ID of the spreadsheet you want to sync
- Submit the form
For Airbyte Open Source:
- Navigate to the Airbyte Open Source dashboard
- Set the name for your source
- Enter the API access token from Prerequisites
- Enter the ID of the spreadsheet you want to sync
- Enter a start sync date
- Click Set up source
Supported sync modes
The Smartsheets source connector supports the following sync modes:
- Full Refresh | Overwrite
- Full Refresh | Append
- Incremental | Append
- Incremental | Deduped
Performance considerations
At the time of writing, the Smartsheets API rate limit is 300 requests per minute per API access token.
Supported streams
This source provides a single stream per spreadsheet with a dynamic schema, depending on your spreadsheet structure.
For example, having a spreadsheet Customers, the connector would introduce a stream with the same name and properties typed according to Data type map (see below).
Additionallly specific metadata fields related to the sheet or row can be include in the stream, these must be specified in the configuration in order to be included in the data stream
| Supported Metadata Fields |
|---|
| sheetcreatedAt |
| sheetid |
| sheetmodifiedAt |
| sheetname |
| sheetpermalink |
| sheetversion |
| sheetaccess_level |
| row_id |
| row_access_level |
| row_created_at |
| row_created_by |
| row_expanded |
| row_modified_by |
| row_parent_id |
| row_permalink |
| row_number |
| row_version |
Important highlights
The Smartsheet Source is written to pull data from a single Smartsheet spreadsheet. Unlike Google Sheets, Smartsheets only allows one sheet per Smartsheet - so a given Airbyte connector instance can sync only one sheet at a time. To replicate multiple spreadsheets, you can create multiple instances of the Smartsheet Source in Airbyte, reusing the API token for all your sheets that you need to sync.
Note: Column headers must contain only alphanumeric characters or _ , as specified in the Airbyte Protocol.
Data type map
The data type mapping adopted by this connector is based on the Smartsheet documentation.
NOTE: For any column datatypes interpreted by Smartsheets beside DATE and DATETIME, this connector's source schema generation assumes a string type, in which case the format field is not required by Airbyte.
| Integration Type | Airbyte Type | Airbyte Format |
|---|---|---|
TEXT_NUMBER | string | |
DATE | string | format: date |
DATETIME | string | format: date-time |
anything else | string |
The remaining column datatypes supported by Smartsheets are more complex types (e.g. Predecessor, Dropdown List) and are not supported by this connector beyond its string representation.
Changelog
| Version | Date | Pull Request | Subject |
|---|---|---|---|
| 1.1.1 | 2023-06-06 | 27096 | Fix error when optional metadata fields are not set |
| 1.1.0 | 2023-06-02 | 22382 | Add support for ingesting metadata fields |
| 1.0.2 | 2023-05-12 | 26024 | Fix dependencies conflict |
| 1.0.1 | 2023-04-27 | 25562 | Update testing dependencies |
| 1.0.0 | 2023-02-19 | 23237 | Fix OAuth2.0 token refresh |
| 0.1.14 | 2023-02-07 | 22419 | OAuth2.0 support - enabled; add allowed hosts |
| 0.1.13 | 2022-12-02 | 20017 | OAuth2.0 support - disabled |
| 0.1.12 | 2022-04-30 | 12500 | Improve input configuration copy |
| 0.1.11 | 2022-04-27 | 12203 | Doc improvements |
| 0.1.10 | 2022-04-15 | 12077 | Implement incremental read and improve code test coverage |
| 0.1.9 | 2022-04-12 | 11911 | Bugfix: scrambled columns |
| 0.1.8 | 2022-02-04 | 9792 | Added oauth support |