Using WebHooks & API to create your own custom Column Functions

Lee Joramo

I thought it might be useful to share a general method I developed to add Functions missing from Smartsheet. This technique makes use of WebHooks & the API.

Most important: users can add this to their sheets nearly as easily as they can add formulas. They do NOT need my help as a programmer

For example here are a couple common use cases:

• We often have the need to access the raw Email address of a contact column. Smartsheet formulas do not have direct access to the email address. (Email addresses can be used to lookup additional information about a person such as employee ID or employment start date via `vlookups()` to other sheets)
• Similarly, it is sometimes useful to access the raw URL from a text column link. We use this in some of the emails sent out via workflows.

Here is an animation of the user experience:

Whenever a row is saved with additions or changes to either the Contact or Link columns, a workflow is triggered to update the rawEmail or rawURL columns.

The above GIF is sped up little to demonstrate this, but the WebHooks and API calls are normally pretty fast. As with all sheet updates not done by the immediate logged in user, they will sometimes need to force refresh the page to see changes.

So how do I create such WebHooks and make it easy for my users to add them like formulas?

First the User needs to take some preliminary steps:

1. Be trained how to use this tool and have their user account added to the list of users allowed to access this feature.
2. Any sheet the user wants this to work on needs to name the sheet ending with the arbitrary characters ^# (space caret hash)
3. We have a Smartsheet user account dedicated to API access. The user needs to grant the API account "Admin" access to their sheet.

I could do all of this without the above restrictions, but this gives me assurance that these tools are used correctly, and we do not over load the API rate limits. The restrictions also make it so that my users are explicitly opting into using these tools.

Now all a user has to do to create above email example :

1. Create the need columns "Contact", "rawEmail". None of these column names are required, but the "rawEmail" name is a suggested standard
2. In the "Contact" columns "Description" add the string =webhook.rawLink(rawEmail) You would replace 'rawEmail' with the name of the actual column you want to populate. And note, they can still put any other text in the Columns description field.
3. Save the sheet.
4. The user now can either wait for up-to an hour for the API application to see the sheet and automatically add the webhooks, or they can login to an internal web application to trigger the the process. The once an hour limit is mostly set to minimize API usage, I am looking into decreasing this.
5. The API Application will look for any sheet shared to it with a name that ends in ^# and add the appropriate webhooks based on the column descriptions

Now the sheet has the webhooks setup without a programmers involvement.

Of course, when they need a new Sheet Function, I am ready to help. And we do have other functions more specific to needs of our organization.

For the above "Link" example they would use the Description =webhook.rawLink(rawURL)

Hope this is helpful and I plan to share other such ideas in the near future.

Andrew Heitner

This is very interesting, what does the training look like and where is this list of approved users? Also, what does your API implementation look like?

I am working with the API as well, I would be very interested to check it out if possible.

Lee Joramo

@Andrew Heitner,

The training is conducted by me, and mostly goes over the various custom functions we have created, and how to add them to the Column Descriptions:

I explain how this works, so that the users understand that the updates occur only after a "Save". (Additionally, some of the custom functions, only update when a user manually forces an update, I will probably do another Show and Tell thread to describe these). I also make sure that my users understand that they will need to come to me with any problems, because Smartsheet support will be completely unable to help and confused.

The "Approved User List" is currently hard coded in a configuration file as part of the application. I have considered managing the Approved User List in Smartsheet, but I only have a handful of users who need these features, so it is easier to just manage it in a Text File.

As far as the code. I am using the Node.js SDK and running as an ExpressJS application. It is currently deeply bound up in our internal tooling. I some day when I have the time, I hope to post some cleaned up examples to GitHub.

While I found the Smartsheet API documentation and a few StackOverflow questions to be enough to do most of the tasks I need, figuring out how to do webhook's was hard. Found this simplified example helpful:

https://github.com/JakeMarquez/smartsheetdemo

(Actually, this is a great example if you are starting from scratch for Smartsheet and Node.js/Express even without webhooks)