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

Typed Sheet Cells

This parameter defines what data types to assign for the fields in the *_Rows objects. If the Typed Sheet Cells parameter is enabled, Skyvia assigns the actual data types for such fields. If it is disabled, Skyvia assigns String data type to such fields, regardless of their actual data types.

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

Object Peculiarities

Sheets

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

This object has a complex structure. It’s Columns and Row fields store data in JSON format. For user convenience, Skyvia represents data from the Sheets records as separate objects with *_Rows suffix in their names.

*_Rows Objects

Each object with *_Rows suffix in its name corresponds to the record in the Sheets object. For example, you have a sheet with the name MySheet. This sheet has a corresponding record in the Sheets object and a corresponding object MySheet_Rows. The *_Rows objects support INSERT, UPDATE and DELETE operations. Skyvia supports the Incremental Replication and Synchronization for such objects.

Fields in the *_Rows objects may have a predefined datatype and may contain text simultaneously. It may cause error when selecting data from such fields. For user convenience, Skyvia allows you to define data types of such fields in the connector using the Typed Sheet Cells connection parameter. This option determines Skyvia’s behavior when data doesn’t match the predefined type. Skyvia’s behavior depends on the type of field.

For example, the Status column in the table below is checkbox(boolean) but contains text data.

If you enable the Typed Sheet Cells option, Skyvia assigns the boolean type for the Status field. If you try to select data from the table, you get an error “Cannot cast the value ‘John Smith’ in the ‘Status’ field and the row number 6 to the Boolean data type. Please correct the value or turn off the ‘Typed Sheet Cells’ option”.

To avoid this error, disable the Typed Sheet Cells option. Then, Skyvia assigns String data type to such fields, regardless of the actual data type, and allows you to select data.

Reports

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

UpdateRequests and SentUpdateRequests

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

When inserting into the UpdateRequests object, 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 fields,

When querying data from the Events object, 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 object. 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

To 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 field from the Columns object.

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)

CreateRowDiscussion

To create a new discussion on a row, use the command

call CreateRowDiscussion (:sheetId,:rowId,:text)

AddGroupMembers

The following command allows to add a new member to a group using email.

call AddGroupMembers(:groupId,:email)

DeleteGroupMembers

To delete a member from a group, use the command

call DeleteGroupMembers(:groupId,:userId)

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 detects only the new records for the Attachments, ProofAttachments and Sheets objects.

Skyvia supports Synchronization for the UpdateRequests, and Groups objects.

Supported Actions

Skyvia supports all the common actions for Smartsheet.