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:
-
Click Sign In with Smartsheet in the connection editor in Skyvia.
-
Enter your Smartsheet credentials or select another available service to log in.
- Give Skyvia permission to perform actions in Smartsheet.
- 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:
OBJECT | FIELD |
---|---|
Workspaces | Favorite, Folders, Permalink, Reports, Sheets, Sights, Templates |
Sheets | Columns, Rows, CrossSheetReferences, DependenciesEnabled, Discussions, EffectiveAttachmentOptions, Favorite, GanttEnabled, HasSummaryFields, IsMultiPicklistEnabled, Source_Id, Source_Type, Summary_Fields, TotalRowCount, Version, Workspace_AccessLevel, WorkspaceId, Workspace_Favorite, Workspace_Folders, Workspace_Name, Workspace_Permalink, Workspace_Reports, Workspace_Sheets, Workspace_Sights, Workspace_Templates |
SentUpdateRequests | ColumnIds, RowIds, IncludeAttachments, IncludeDiscussions |
Groups | Data |
Report | Scope_Sheets, Scope_Workspaces, SourceSheets, ProofsId, Proofs_OriginalId, Proofs_Name, Proofs_Type, Proofs_DocumentType, Proofs_ProofRequestUrl, Proofs_Version, Proofs_LastUpdatedAt, Proofs_LastUpdatedBy_Name, Proofs_LastUpdatedBy_Email, Proofs_IsCompleted, AccessLevel, Attachments, Columns, CrossSheetReferences, DependenciesEnabled, Discussions, EffectiveAttachmentOptions, Favorite, GanttEnabled, HasSummaryFields, Permalink, ProjectSettings_LengthOfDay, ProjectSettings_NonWorkingDays, ProjectSettings_WorkingDays, ReadOnly, ResourceManagementEnabled, Rows, ShowParentRowsForFilters, Source_Id, Source_Type, Summary_Fields, TotalRowCount, UserPermissions_SummaryPermissions, UserSettings_CriticalPathEnabled, UserSettings_DisplaySummaryTasks, Version, Workspace_AccessLevel, WorkspaceId, Workspace_Favorite, Workspace_Folders, Workspace_Name, Workspace_Permalink, Workspace_Reports, Workspace_Sheets, Workspace_Sights, Workspace_Templates |
UpdateRequests | RowIds, ColumnIds, IncludeAttachments and IncludeDiscussions |
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
Operations | Objects |
---|---|
INSERT, UPDATE, DELETE | Columns, Groups, Sheets, UpdateRequests, Users, Workspaces |
INSERT, DELETE | AlternateEmails, Discussions, Rows |
UPDATE, DELETE | Proofs, SheetShares, WorkspaceShares |
INSERT | CrossSheetReferences, HomeFolders, ProofDiscussions, WorkspaceFolders |
DELETE | Attachments |
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.