Handling Incremental Smartsheet Data Updates

Applications requiring synchronization of Smartsheet data benefit greatly from efficient incremental updates, which minimize API load and prevent redundant data retrieval. Instead of repeatedly fetching entire datasets, utilizing Smartsheet's change-detection features provides a significantly optimized approach.

This section showcases how webhooks and event reporting address two sample use cases requiring Smartsheet data synchronization.

The first use case is an application that needs to maintain a local copy of project task statuses from a Smartsheet sheet in order to provide real-time updates to users. This application is configured with the Smartsheet access token of a user who has access to the sheet. Whenever the data in the sheet is changed, the application is updated with the latest data from the sheet.

For the second use case, consider a system that stores a local copy of all the Smartsheet data for an organization, allowing users to get relevant insights and concise summaries from the Smartsheet data based on their access rights. To retrieve all organizational sheets, the system requires a System Administrator's token and employs user impersonation. The system must also monitor for changes, including sheet updates, access right modifications (grants or revocations), and the addition or removal of sheets.

Webhooks

Smartsheet webhooks deliver real-time notifications of changes to specific sheet resources, including sheet metadata, rows, columns, cells, attachments, and comments. Applications register for webhooks to monitor Smartsheet objects for events, receiving a callback when an event occurs within the specified object's scope. Upon receiving a webhook event, your application can selectively retrieve the updated resources via the Smartsheet API, ensuring data synchronization only when necessary. This event-driven approach significantly reduces unnecessary API calls and improves efficiency.

Webhooks are an ideal solution to get incremental data updates for the first sample use case. The steps that this application would follow to keep sheet data synchronized using webhooks are explained below:

1. Establish a Webhook: Create and enable a webhook for the sheet to receive update notifications.

2. Initial Data Retrieval: Fetch the sheet's initial data using the Get Sheet endpoint. If needed, you can specify the include=discussions,attachments URL query parameter to retrieve discussion and attachment metadata.

3. Retrieve additional sheet data (Optional): If required, obtain any additional sheet data. For example:

   • Attachment data: For each attachment in the sheet, retrieve attachment details via Get Attachment to obtain a download URL. Use the download URL to download the attachment data.
   • Discussion data: For each discussion in the sheet, retrieve all comments within a discussion using the Get Discussion endpoint.

4. Webhook Notification Handling: Upon sheet data modification, the application's webhook callback URL will receive a notification containing the identifier of the updated sheet, row, cell, attachment, or comment.

5. Retrieving Updated Data: Use the Get Sheet, Get Row, Get Attachment, or Get Discussion endpoints to retrieve the latest data based on the webhook notification's identifiers.

Event Reporting

While webhooks are highly effective at tracking changes within sheets, they do not capture modifications to user access permissions. For example, in the second use case mentioned above, when a user is granted access to a workspace, the system must be notified about that change so that the user can start getting information related to the sheets within that workspace. Conversely, when a user's access is revoked, the system must immediately prevent access to that data. This access control functionality is not achievable through webhooks alone.

The Event Reporting API addresses this gap by providing a comprehensive log of events occurring within your Smartsheet organization. This API enables monitoring of changes across sheets, reports, dashboards, users, groups, workspaces, and more. See the Event Reporting reference documentation for a complete list of all currently supported events.

Event Reporting utilizes a polling approach, where applications periodically request a list of events that occurred within a specified time range. These events include the identifier of the resource that changed, which can be used to retrieve the updated resource via the Smartsheet API.

The steps that the system in the second use case would need to follow to keep the sheet data and permissions synchronized using event reporting are the following:

• Pre-requisite: Ensure that Event Reporting is enabled for your account. Event Reporting is a premium add-on which can be purchased for Enterprise and Advance Platinum plans only. For details on the Event Reporting add-on, please contact our Sales Team.

1. Initial Data Retrieval: Using a System Administrator's token and employing user impersonation, perform the following steps:
   • Fetch the list of all users in the org by using the List Users endpoint.
   • Fetch the list of groups in the org by using the List Groups endpoint.
   • For each user, retrieve the list of sheets using the List Sheets endpoint.
   • For each sheet:
     • Use the Get Sheet endpoint to get the sheet data. If needed, you can specify the include=discussions,attachments URL query parameter to retrieve discussion and attachment metadata.
     • Use the List Sheet Shares to get the list of users shared to the sheet.
     • If the sheet belongs to a workspace, use the Get Workspace endpoint to retrieve the workspace data and use the List Workspace Shares endpoint to get the list of users shared to the workspace.
2. Retrieve additional sheet data (Optional): If required, obtain any additional sheet data. For example:
   • Attachment data: For each attachment in the sheet, retrieve attachment details via Get Attachment to obtain a download URL. Use the download URL to download the attachment data.
   • Discussion data: For each discussion in the sheet, retrieve all comments within a discussion using the Get Discussion endpoint.
3. Check for Sheet Updates via Event Reporting: Periodically, query the List Events or List Filtered Events endpoint to retrieve events that have occurred since the initial data retrieval or the last update. The relevant events for the second use case are the following:
   • SHEET - UPDATE
   • SHEET - ADD_SHARE
   • SHEET - REMOVE_SHARE
   • WORKSPACE - ADD_SHARE
   • WORKSPACE - REMOVE_SHARE
   • GROUP - ADD_MEMBER
   • GROUP - REMOVE_MEMBER
   • SHEET - MOVE
4. Retrieving Updated Data: Use the Get Sheet, List Sheet Shares, List Workspace Shares, Get Group, Get Row, Get Attachment, Get Discussion and List groups endpoints to retrieve the latest data based on the identifiers returned in the response of the List Events endpoint.