Connectors → Smartsheet
About Smartsheet
Smartsheet is a software as a service offering for collaboration and work management. It is used to assign tasks, track project progress, manage calendars, share documents, and manage other work, using a tabular user interface.
About the Smartsheet Connector
The Smartsheet connector uses the CData JDBC Driver which offers the most natural way to connect to Smartsheet data from Java-based applications and developer technologies. The driver wraps the complexity of accessing Smartsheet data in an easy-to-integrate driver that applications can use to access Smartsheet as a traditional database.
The Smartsheet connector supports the following Incorta specific functionality:
| Feature | Supported |
|---|---|
| Chunking | ✔ |
| Data Agent | |
| Encryption at Ingest | |
| Incremental Load | ✔ |
| Multi-Source | ✔ |
| OAuth | ✔ |
| Performance Optimized | ✔ |
| Remote | |
| Single-Source | ✔ |
| Spark Extraction | |
| Webhook Callbacks | ✔ |
Security configurations for the Smartsheet connector
There are two authentication method options for the Smartsheet connector:
- OAuth
- API Access Token
Use the OAuth Authentication Method
If your application requires users to be able to login with their own account, implement the full OAuth flow. A Security or System Administrator who manages your organization’s Smartsheet account as well as the Incorta Cluster creates the OAuth client credentials. Refer to the Smartsheet Adding Security by Using OAuth documentation for more information.
Use the API Access Token Authentication Method
Use the API Access Token Authentication method if you want to get started quickly, or are developing a standalone application that can run with your credentials. Refer to the Smartsheet Raw Token Requests documentation for more information.
Allow the Smartsheet connector
In order to use the Smartsheet connector, a Cluster Management Console (CMC) administrator must first allow it. Here are the steps:
- Sign in to the CMC.
- In the Navigation bar, select Clusters.
- In the cluster list, select a Cluster name.
- In the canvas tabs, select Connectors.
- Select Manage Connectors.
- Select Allow for smartsheet.
- Select OK.
There is an associated cost to allow a CData connector. Contact your Incorta Sales Executive for details.
Steps to connect Smartsheet and Incorta
To connect Smartsheet and Incorta, here are the high level steps, tools, and procedures:
- Create an external data source
- Create a schema with the Schema Wizard
- or, Create a schema with the Schema Designer
- Load the schema
- Explore the schema
Create an external data source
Here are the steps to create a external data source with the Smartsheet connector:
- Sign in to the Incorta Direct Data Platform™.
- In the Navigation bar, select Data.
- In the Action bar, select + New → Add Data Source.
- In the Choose a Data Source dialog, in Application, select Smartsheet.
- In the New Data Source dialog, specify the applicable connector properties.
- To test, select Test Connection.
- Select Ok to save your changes.
Smartsheet connector properties
Here are the properties for the Smartsheet connector:
| Property | Control | Description |
|---|---|---|
| Data Source Name | text box | Enter the name of the data source |
| Authentication Method | drop down list | Select the authentication method to connect to Smartsheet. The options are:
Refer to Security configurations for the Smartsheet connector for more information. |
| OAuth Client ID | text box | Select OAuth for Authentication Method to configure this property. Enter the ID used to identify the application. |
| OAuth Client Secret | text box | Select OAuth for Authentication Method to configure this property. Enter the secret string the application uses to prove its identity when requesting a token. |
| Authorize | button | Select OAuth for Authentication Method to configure this property. Select this button to authenticate your Smartsheet account and grant Incorta read access to your sheets. |
| API Access Token | text box | Select API Access Token for Authentication Method to configure this property. Enter the user access token. |
| Use Connection Pooling | toggle | Enable this property to use connection pooling. For a description of the connection pooling properties, refer to the CData JDBC Driver for Smartsheet Connection String Options. |
| Show Advanced Options | toggle | Enable this property to use the advanced connection options. For a description of the advanced options, refer to the CData JDBC Driver for Smartsheet Connection String Options. |
Create a schema with the Schema Wizard
Here are the steps to create a Smartsheet schema with the Schema Wizard:
- Sign in to the Incorta Direct Data Platform™.
- In the Navigation bar, select Schema.
- In the Action bar, select + New → Schema Wizard.
- In (1) Choose a Source, specify the following:
- For Enter a name, enter the schema name.
- For Select a Datasource, select the Smartsheet external data source.
- Optionally create a description.
- In the Schema Wizard footer, select Next.
- In (2) Manage Tables, in the Data Panel, first select the name of the Data Source, and then check the Select All checkbox.
- In the Schema Wizard footer, select Next.
- In (3) Finalize, in the Schema Wizard footer, select Create Schema.
Create a schema with the Schema Designer
Here are the steps to create a Smartsheet schema using the Schema Designer:
- Sign in to the Incorta Direct Data Platform™.
- In the Navigation bar, select Schema.
- In the Action bar, select + New → Create Schema.
- In Name, specify the schema name, and select Save.
- In Start adding tables to your schema, select Smartsheet.
- In the Data Source dialog, specify the Smartsheet table data source properties.
- Select Add.
- In the Table Editor, in the Table Summary section, enter the table name.
- To save your changes, select Done in the Action bar.
Smartsheet table data source properties
| Property | Control | Description |
|---|---|---|
| Type | drop down list | The default is Smartsheet |
| Data Source | drop down list | Select the Smartsheet external data source |
| Incremental | toggle | Enable the incremental load configuration for the schema table |
| Fetch Size | text box | Used for performance improvement, fetch size defines the number of records that will be retrieved from the database in each batch until all records are retrieved. The default is 5000. |
| Incremental Extract Using | drop down list | Enable Incremental to configure this property. Select one of the following options:
|
| Incremental Column | drop down list | Enable Incremental and select Maximum Value of a Column for Incremental Extract Using to configure this property. Select the column to be used for Maximum Value of a Column. The Loader will track and use the greatest value or most recent timestamp for each load operation. |
| Query | text box | Enter the query to retrieve data from the Smartsheet database |
| Update Query | text box | Enable Incremental to configure this property. Enter the query to retrieve data updates from the Smartsheet database. |
| Incremental Field Type | drop down list | Enable Incremental to configure this property. Select the format of the incremental field:
|
| Chunking Method | drop down list | Chunking methods allow for parallel extraction of large tables. The default is No Chunking. There are two chunking methods:
|
| Chunk Size | text box | Select By Size of Chunking for Chunking Method to configure this property. Enter the number of records to extract in each chunk in relation to the Fetch Size. The default is three times the Fetch Size. |
| Order Column | drop down list | Select a Chunking Method to configure this property. Select a column in the source table you want to order by before chunking. For a Chunking Method of By Size of Chunking, it is typically an ID column and must be numeric. For a Chunking Method of >By Timestamp, it is a timestamp column. |
| Chunk Period | drop down list | Select the chunk period to use in dividing the chunks:
|
| Number of Days | text box | Select Custom for Chunk Period to configure this property. Enter the chunking period in days. |
| Upper Bound for Order Column | text box | Optional. Enter the maximum value for the order column. |
| Lower Bound for Order Column | text box | Optional. Enter the minimum value for the order column. |
| Callback | toggle | Enable this option to call back on the source data set |
| Callback URL | text box | Enable Callback to configure this property. Specify the URL. |
View the schema diagram with the Schema Diagram Viewer
Here are the steps to view the schema diagram using the Schema Diagram Viewer :
- Sign in to the Incorta Direct Data Platform™.
- In the Navigation bar, select Schema.
- In the list of schemas, select the Smartsheet schema.
- In the Schema Designer, in the Action bar, select Diagram.
Load the schema
Here are the steps to perform a Full Load of the Smartsheet schema using the Schema Designer:
- Sign in to the Incorta Direct Data Platform™.
- In the Navigation bar, select Schema.
- In the list of schemas, select the Smartsheet schema.
- In the Schema Designer, in the Action bar, select Load → Full Load.
- To review the load status, in Last Load Status, select the date.
Explore the schema
With the full load of the Smartsheet schema complete, you can use the Analyzer to explore the schema, create your first insight, and save the insight to a new dashboard.
To open the Analyzer from the schema, follow these steps:
- In the Navigation bar, select Schema.
- In the Schema Manager, in the List view, select the Smartsheet schema.
- In the Schema Designer, in the Action bar, select Explore Data.
Additional Considerations
Types of Incremental Loads
You can enable Incremental Load for a Smartsheet data source. There are two types of incremental extracts:
Last Successful Extract Time
Fetch updates since the last time the tables were loaded. It is determined by the difference between the current time and the database timestamp.
Maximum Value of a Column
The column-based strategy depends on an extra column called Incremental Column in each table. The Smartsheet connector supports both timestamp and numeric columns. A timestamp column is of the type date or timestamp. A numeric column is of the type int or long.
Changing the incremental load strategy requires a full load to ensure data integrity.
Incremental Load Example
In this example, the invoices table must contain a column of the type Date or Timestamp in order to load the table incrementally with a last successful extract time strategy. In this case, the name of the date column is ModifiedDate and the format of the column is Timestamp.
Here are the data source property values for this example:
- Incremental is enabled
- Query contains
SELECT * FROM invoices - Update Query contains
SELECT * FROM invoices WHERE ModifiedDate > ? - Incremental Field Type = Timestamp
?is a variable in the update query that contains the last schema refresh date. When running an update query for an incremental load, the ? character will be replaced with the last incremental reference to construct a valid query to the database. The ? reference character is not valid in a standard query.