The Smartsheet datasource provides connectivity between Smartsheet and third-party tools to the Knowi. This allows you to deliver powerful data visibility and insights with your Smartsheet data via the tools you already use.

By establishing a data source connection, users can leverage real-time or scheduled data updates to keep their Smartsheet projects and workflows up to date. This integration allows for seamless data management and collaboration across different platforms and ensures that teams are working with the most current information.

Overview

Connect, extract, and transform data from your smartsheet database, using the following option:

Through our UI, you can connect directly and Visualize and Automate your Reporting instantly.

UI Based Approach

Connecting

Step 1: Log in to Knowi and select Queries from the left sidebar.

Step 2: Click on the New Datasource+ button and a new page with a list of datasources will open.

Step 3: Select smartsheet in Data Warehouses.

A new datasource page will open.

Step 4: Configure the following details to set up connectivity to your smartsheet database:

Datasource Name: Enter a name for this connection

API Access Token: Enter your API access token key.

To get the API access token key you can follow the steps:

Login to your SmartSheet account > Generate an API key from the Left corner of the SmartSheet screen > Account > Personal Settings > API Access tab > Get the access key.

For more information visit the smartsheet docs to generate the API Access Token.

Step 5: Click on Save and start Querying.

Query

Set up Query using a visual builder or query editor

Query Editor

A versatile text editor designed for editing code that comes with the smartsheet datasource which creates and executes the Cloud9QL Query.

Step 1: After connecting to the smartsheet datasource.

a. Make your selections through the dropdown and select the Sheet from the SmartSheet Options.
b. Enter the sheet ID.

To get Sheet ID, open your SmartSheet with the required document, the Sheet ID will be the series of numbers at the end of the URL after '/sheets/'.

c. You can create your own query to the Cloud9QL Query to pull out the results from the smartsheet data source.

<gif>

Step 2: Define data execution strategy by using any of the following two options:

• Direct Execution: Directly execute the Query on the original Datasource, without any storage in between. In this case, when a widget is displayed, it will fetch the data in real-time from the underlying Datasource.
• Non-Direct Execution: For non-direct queries, results will be stored in Knowi's data store. Benefits include- long-running queries, reduced load on your database, and more. Non-direct execution can be put into action if you choose to run the Query once or at scheduled intervals.

For more information, check out the documentation on Defining Data Execution Strategy.

Step 3: Click on the Preview button to analyze the results of your Query and fine-tune the desired output, if required.

Step 4: After reviewing the results, name your dataset and hit the Save & Run button.