Skip to main content

How do I create Smartsheet data sources?

Updated

Visualize, discover, and act on your data with Klips and Klip dashboards.

Ready to get the most from your Smartsheet project management data? Get started by setting up a connection between Klipfolio and your Smartsheet data and adding some data sources.

When you're finished adding Smartsheet data sources, you'll use them to power your custom Klips.

This article includes:

Authentication requirements - Your Smartsheet Access Token

You authenticate with Smartsheet using an API Key. To do this, you need to enter your Smartsheet Access Token into Klipfolio. If you don't have an Access Token, learn how to generate one here: Authentication and Access Tokens.

When adding your pre-built or custom data source, you'll enter your Smartsheet Access Token, in the Configure your connection page as a header using the following format:

  1. Under Name, type Authorization.
  2. Under Value, enter your API Token in the following format: Bearer <yourAccessToken>, replacing the placeholder text with the Access Token from your Smartsheet account.
  3. Under Type, select Header from the drop-down list.

Finding your Sheet ID

When adding your Smartsheet data source, you'll need to enter your Sheet ID.

To find your Smartsheet Sheet ID:

  1. Go to the specific sheet that you want to get data from in your Smartsheet account.
  2. On the left side menu, select the Sheet Actions icon.
  3. Select Properties.
  4. Copy the Sheet ID.

Adding a pre-built or custom Smartsheet data source

To create a pre-built or custom data source using Smartsheet:

  1. Go to the Service Connectors page in Klipfolio and select Smartsheet from the list of connectors.
  2. On the Choose a pre-built or custom connection page, select either a pre-built or custom data source option.
  3. On the Configure your connection page, at Query URL, enter a Smartsheet URL endpoint. For example: https://api.smartsheet.com/2.0/sheets/
    Note: The Query URL field is auto-filled for you (remember to replace the placeholder text <yourSheetNumber> with your own sheet number).
    There are many Smartsheet query options you can use to retrieve data. Refer to the Smartsheet API Documentation for more details.
  4. At Data Format, select Excel.
  5. At Method, select GET.
  6. Expand the Query Parameters menu.
  7. At Parameters, under Name, type Accept.
  8. Under Value, type application/vnd.ms-excel.
  9. Under Type, select Header.
  10. When you're finished, click Get data.
  11. Ensure this is the data you’re looking for and, optionally, select the checkbox to Model your data.  Learn more about modelling your data source.
  12. Click Continue.
  13. Name, choose refresh settings, and, optionally, share your data source.
  14. Click Save.
    See below for an example of a Smartsheet data source setup.

The data source is added to your account and is ready to use for custom Klips. You can see all your data sources in your Data Source Library, accessed by clicking Data Sources in the left navigation sidebar.

Adjusting your query to return data in JSON format (optional)

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

Data Source Type Web Accessible Resource
API Documentation Smartsheet API Documentation
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

Next steps

Great! You've connected to your Smartsheet data and created one (or more) data sources. Now you're ready to use those data sources in custom Klips.

Want to learn more?

Check out these related articles:

Related articles