Smartsheet Automations and API/Webhook Calls

jjwb96

Hi there,

We have a sheet then when an item in the sheet is marked as complete it is moved to an Archive sheet using Automation. The problem is there is so much data in a short period of time that we hit the sheet cell limit and have to export the data out into a CSV.

I am wondering if there is a way using automation to instead call an API or send a Webhook so I can store the data in a Database.

Any insight would be great!

Ryan Kramer

Definitely! I have done this many times.

There is an API to extract the sheet data and you can do so on an interval (like every hour) and pull all the records in the sheet, process them, input into your database and then delete the records from Smartsheet.

Webhook would work as well though depending on the data flow it might make more sense to have it run every so often rather than say on each new record being added.

Ryan

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

jjwb96

Thanks Ryan,

I will certainly look into the API and see if that will fit my needs, would you happen to have the link to that particular API?

Also would you have the information for setting up a web hook? Just want to review my options before choosing a specific path.

Genevieve P.

Hey @jjwb96

Here's the Developer's Portal that has links to API resources and information:

https://developers.smartsheet.com/

In the API documentation there's information on Webhooks as well:

https://smartsheet.redoc.ly/tag/webhooksDescription?_ga=2.13172407.101738377.1695725617-1875111245.1684938620&_gl=1*1uudxm7*_ga*MTg3NTExMTI0NS4xNjg0OTM4NjIw*_ga_ZYH7XNXMZK*MTY5NTk3NTMzNS40LjEuMTY5NTk4NDMzNy42MC4wLjA.

Cheers,

Genevieve

DTK

Just to confirm, I thought I had a similar question and the responses weren't clear to me. When you create an automation, the trigger is that cell is marked complete, and the action is to invoke an API call.

I did not see anything in the documents that explained how Smartsheet automation can trigger an action such as call the API.

Can someone clarify if you can invoke an API from within smartsheet automation? and if so, please link to where that is explained.

Thanks in advance for your clarifications!

Ryan Kramer

@DTK , you would want to look at webhooks -

https://smartsheet.redoc.ly/tag/webhooksDescription

Ryan

BKing

You will need a couple of elements:

Some code to create a webhook on your sheet - essentially a create - this generates an id for your hook and then an update - use the hook id and set it to enabled. The webhook need to point to an api url - an endpoint

Then your API endpoint need to access a piece of code that extracts your data to the db. We use AWS - API Gateway, a lambda function to do the work and then dynamoDB to store the data. We've found this inexpensive and reliable.

What happens is:

Your automation sets a column to 'archive'

This causes the webhook to trigger a send a series a JSON packets to the api. Its structure is something like:

Sheet Update - with sheet id

Row Update with row id

Then a series of Column updates with column ids

The code behind your api then has to:

Check each column update, look for your 'archive' column and test that it is set true.

Then simply process the row data you want to archive to the db. Use batch updating and consider the batch size.

Regards,

Brian