Creating a Smartsheet data source

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 New Data Source 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.

  1. Expand the Authentication menu.
  2. Under Type, select the option OAuth Credentials.
  3. Click Create.

  1. Select Smartsheet from the list of Service Providers.
  2. You will be prompted to log in to Smartsheet.
  3. After logging in, you will be prompted to allow Klipfolio to access your Smartsheet account.
  4. 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:

  1. Under Name, type Authorization.
  2. 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.
  3. Under Type, select Header from the drop-down list.

Set up your data source

  1. 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.
  2. At Data Format, select Excel.
  3. At Method, select GET.
  4. Expand the Query Parameters menu.
  5. At Parameters, under Name, type Accept.
  6. Under Value, type application/vnd.ms-excel.
  7. Under Type, select Header.

Step 3: Enter your Sheet ID

Find your Sheet ID in Smartsheet

To find your Smartsheet Sheet ID, follow the instructions in the Smartsheet article Sheet Properties.

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:

  1. Data Format = JSON.
  2. 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 Links