Skip to main content

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:

  1. Log into your Airbyte Cloud account.
  2. In the left navigation bar, click Sources. In the top-right corner, click +new source.
  3. On the Set up the source page, enter the name for the Smartsheets connector and select Smartsheets from the Source type dropdown.
  4. Authenticate via OAuth2.0 using the API access token from Prerequisites
  5. Enter the start date and the ID of the spreadsheet you want to sync
  6. Submit the form

For Airbyte Open Source:

  1. Navigate to the Airbyte Open Source dashboard
  2. Set the name for your source
  3. Enter the API access token from Prerequisites
  4. Enter the ID of the spreadsheet you want to sync
  5. Enter a start sync date
  6. 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 TypeAirbyte TypeAirbyte Format
TEXT_NUMBERstring
DATEstringformat: date
DATETIMEstringformat: date-time
anything elsestring

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

VersionDatePull RequestSubject
1.1.12023-06-0627096Fix error when optional metadata fields are not set
1.1.02023-06-0222382Add support for ingesting metadata fields
1.0.22023-05-1226024Fix dependencies conflict
1.0.12023-04-2725562Update testing dependencies
1.0.02023-02-1923237Fix OAuth2.0 token refresh
0.1.142023-02-0722419OAuth2.0 support - enabled; add allowed hosts
0.1.132022-12-0220017OAuth2.0 support - disabled
0.1.122022-04-3012500Improve input configuration copy
0.1.112022-04-2712203Doc improvements
0.1.102022-04-1512077Implement incremental read and improve code test coverage
0.1.92022-04-1211911Bugfix: scrambled columns
0.1.82022-02-049792Added oauth support