Smartsheet

Smartsheet is a cloud service for work management and collaboration.

Data integration: Skyvia supports importing data to and from Smartsheet, exporting Smartsheet data to CSV files, replicating Smartsheet data to relational databases, and synchronizing Smartsheet data with other cloud apps and relational databases.

Backup: Skyvia Backup does not support Smartsheet.

Query: Skyvia Query supports Smartsheet.

Establishing Connection

When creating a connection, log in with Smartsheet. Skyvia stores only the OAuth authentication token. Skyvia does not store Smartsheet credentials.

Creating Connection

To connect to Smartsheet, perform the following steps:

1. Click Sign In with Smartsheet in the connection editor in Skyvia.

   

2. Enter your Smartsheet credentials or select another available service to log in.

   

3. Give Skyvia permission to perform actions in Smartsheet.
4. Click Create Connection when the token is generated.

Additional Connection Parameters

Suppress Extended Requests

Smartsheet API returns only part of the fields for some objects when querying multiple records. To query the values of additional fields, Skyvia performs additional extended requests for each record of such an object. However, this can decrease performance and significantly increase the number of API calls.

The list of such fields is the following:

To reduce the number of API calls, select the Suppress Extended Requests checkbox. However, please note that some of the fields in such objects will not be available in Skyvia (will return empty values) even if they have values in Smartsheet because its API does not return them without extended requests.

Connector Specifics

Sheets

To insert data into the Sheets table, you must map either the FromId or the Columns field in addition to the required field Name.

Reports

The Start column is used for filtering and does not return any data when selecting data from it. Thus, it is not necessary to add this column to the selection. If you set the filter by the Start column, the result will contain data modified on the specified date or later.

UpdateRequests and SentUpdateRequests

The planned update requests are stored in the UpdateRequests table. After the update requests are sent, they are displayed in the SentUpdateRequests table.

When inserting into the UpdateRequests table, specify the Schedule_StartAt field value in the DateTime format with the time value 00:00:00. For example 11/23/2022 00:00:00.

Events

When selecting data from the Since and StreamPosition, the returned result is empty for these columns,

When querying data from the Events table, set the filter by one of these fields, not by both. Only the =(equal to) operator is supported.

Columns

There is a limitation for the INSERT/UPDATE operations against the Columns table. when mapping the Validation field by the Constant, use the True value only. Do not map this field if the record must have the False value.

Rows

When selecting data from the Rows object, you must use filter by Id. If you don’t use filter, you get the error “The ‘Id’ field of object ‘Rows’ is required for select operation. You must use it in the WHERE clause.”

To successfully import data to this object, you must map the SheetId and Cells fields. Cells value must be mapped in the following format: {"ColumnId":111111,"Value":"Example for Value"}, where the ColumnId is the ID of the specific column from the Columns table.

Obtaining SheetId and ColumnId

You can execute the following command to obtain the SheetId and ColumnId for further use in integrations:

1
2
3
4
5
SELECT Sheets_Columns_SheetId.Name, Columns.SheetId, Columns.Id, Columns.Title
  FROM Columns
LEFT OUTER JOIN Sheets AS Sheets_Columns_SheetId ON
  Columns.SheetId = Sheets_Columns_SheetId.Id
WHERE (Sheets_Columns_SheetId.Name = 'New Sheet UI')

Stored Procedures

Skyvia represents part of the supported Smartsheet features as stored procedures. You can call a stored procedure, for example, as a text of the command in the ExecuteCommand action in a Target component of a Data Flow or in Query.

DeactivateUser

To deactivate the user, use command

call DeactivateUser(:userId)

The specified user cannot access Smartsheet after the procedure execution.

ReactivateUser

To reactivate the specified user, use command

call ReactivateUser(:userId)

The user becomes able to access Smartsheet after the procedure execution.

MakeAlternateEmailPrimary

To make the existing alternative email the primary email for the specified user, use command

call MakeAlternateEmailPrimary(:userId,:alternatEmailId)

DML Operations

Incremental Replication and Synchronization

Skyvia supports the Replication with Incremental Updates for the following Smartsheet objects:

WorkspaceShares, Sheets, SheetShares, SheetSummaryFields, Attachments, UpdateRequests, Groups, ProofAtttachments, Reports.

Incremental Replication considers only the new records for the Attachments and ProofAttachments tables. These tables contain only the CreatedDate field, and there is no UpdatedDate field which would have allowed considering the updated records.

Skyvia supports Synchronization for the Sheets, UpdateRequests, Groups tables.

Supported Actions

Skyvia supports all the common actions for Smartsheet.