Resources
Data Source Type: | Web Accessible Resource |
Sample Query | https://api.smartsheet.com/2.0/sheets/your-sheet-id |
API Documentation | Smartsheet API Documentation |
Step-by-Step
Creating a data source using Smartsheet
To create a data source using Smartsheet, you need to accomplish the following tasks:
- Start the data source creation process.
- Set up your data source.
- Enter your Sheet ID.
- Authenticate with Smartsheet (using Option 1 or Option 2).
- Complete the data source creation process.
Start the data source creation process
To create a data source using Smartsheet, go to the Service Connectors page in Klipfolio and select Smartsheet from the list of connectors.
Authenticate with Smartsheet
There are two ways you can authenticate with Smartsheet. The most common way is to generate a Smartsheet OAuth token (Option 1). Or, use an API Key to authenticate (Option 2).
Option 1: Generate an OAuth Token
Note: We are experiencing intermittent issues with Smartsheet OAuth authentication. An alternate method of authentication is with an Access Token (Option 2).
Create an OAuth Token for Smartsheet.
- Expand the Authentication menu.
- Under Type, select the option OAuth Credentials.
- Click Create.
- Select Smartsheet from the list of Service Providers.
- You will be prompted to log in to Smartsheet.
- After logging in, you will be prompted to allow Klipfolio to access your Smartsheet account.
- Save and name your Smartsheet OAuth token.
Option 2: Authenticate with a Smartsheet Access Token
An alternate method is to generate an Access Token in your Smartsheet account and use it to authenticate in Klipfolio.
Find your Access Token in Smartsheet
To generate a Smartsheet Access Token, follow the steps in the following Smartsheet API document: Generating an Access Token
Enter your Access Token in Klipfolio
Once you have your Smartsheet Access Token, use it in Klipfolio's New Data Source page as a header in the following format:
- Under Name, type Authorization.
- Under Value, enter your API Token in the following format: Bearer your-API-Key. Replace your API Key with the Access Token in your Smartsheet account.
- Under Type, select Header from the drop-down list.
Set up your data source
- Enter a Smartsheet URL endpoint. For example:
https://api.smartsheet.com/2.0/sheets/
Note: There are many Smartsheet query options for you to retrieve data. Consult Smartsheet API Documentation for more details. - At Data Format, select Excel.
- At Method, select GET.
- Expand the Query Parameters menu.
- At Parameters, under Name, type Accept.
- Under Value, type application/vnd.ms-excel.
- Under Type, select Header.
Step 3: Enter your Sheet ID
Find your Sheet ID in Smartsheet
To find your Smartsheet Sheet ID,
- Go to the specific sheet that you want to get data from in your Smartsheet account.
- On the left side menu, select the Sheet Actions icon.
- Select Properties.
- Copy the Sheet ID.
Add your Sheet ID to the query in Klipfolio.
In Klipfolio, add the Sheet ID to the query in the following format:
https://api.smartsheet.com/2.0/sheets/your-sheet-id
Optional: Set up your data source (JSON format)
Although it is easier to work with Smartsheet data in Excel format, you can adjust your query to return data in JSON format.
To do this, you must specify the following settings:
- Data Format = JSON.
- Parameter settings: Name = Accept, Value = application/json, and Type = Header.
Additional information and tips
API Limits |
By default, most endpoints return a maximum of 100 records per query. To retrieve all records, append the includeAll=true parameter to your query. |
Pagination Method |
Index endpoints (typically those that return lists of objects such as sheets and folders) return a maximum of 100 records per query and support pagination to return more records. To page through the data, use the pageSize and page parameters in your Smartsheet query. Responses provide totalPages and totalCount values to indicate the total numbers of pages and records, as well as pageNumber and pageSize values. This data can be used in pagination formulas. Refer to Data Sources with Multiple Pages for details on how to handle pagination. |
Date Parameters |
Default format: yyyy-MM-dd Append the numericDates parameter your query to return dates in Unix time format (milliseconds). |
Smartsheet Reports | To query Smartsheet reports, you'll need your Report ID. You can find it by running the endpoint /reports/ in a query in Klipfolio, or you can find your report ID in your Smartsheet account properties. |
Sample queries
Sheets
https://api.smartsheet.com/2.0/sheets?includeAll=true
https://api.smartsheet.com/2.0/sheets/0123456
Folders
https://api.smartsheet.com/2.0/home/folders
Contacts
https://api.smartsheet.com/2.0/contacts?includeAll=true
Related articles
- Introduction to the Connector Gallery
- Guide to REST API data sources
- Working with XML/JSON data sources
- Data Sources with Multiple Pages