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:
- Be trained how to use this tool and have their user account added to the list of users allowed to access this feature.
- Any sheet the user wants this to work on needs to name the sheet ending with the arbitrary characters
^# (space caret hash) - 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 :
- Create the need columns "Contact", "rawEmail". None of these column names are required, but the "rawEmail" name is a suggested standard
- 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. - Save the sheet.
- 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.
- 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.
