If you've pulled data from an API, you know how useful they are for retrieving information. But if you need that data pushed to your app as it happens, webhooks are even better. Webhooks are an event-driven message sent to a URL of your choice, and they are offered by many services that have an API.
How are webhooks used?
Some webhook examples are:
- Zoom can alert you when a participant enters a meeting
- Mailchimp can send you a message when someone signs up for your newsletter
- WooCommerce can let you know when a customer makes a purchase on your WordPress site
Webhooks are just JSON-formatted data, and examples of how it can be used are limitess, but here are a few:
- Add Zoom participant details to a spreadsheet
- Run a MailChimp signup email through a known spam list
- Send information about a WooCommerce order to a fulfillment house
Webhooks are a great way to send live data from one app to another, or to process event-driven data for further use.
In order to use webhooks, you typically have to register your app with the service, then tell it what URL to send the webhook data to.
Using Google Sheets with webhooks
Webhook data can be captured right in Google Sheets, and then be processed further as needed. For instance, in Google Sheets, you could capture MailChimp signups via webhooks, then use Google Apps Script to check each email address against a spam list, and put the results of the check in another spreadsheet. Or use Google Apps Script to post the results back to Mailchimp, and clean those emails from your signup list.
The 3 basic steps for capturing webhook data in Google Sheets are:
- Deploy a Google Sheet as a Web App: this provides the endpoint for the webhook
- Write a doGet(e) function: this is checked by the webhook sender
- Write a doPost(e) function: this picks up the actual webhook data,
Start with a Google Sheet and deploy it as a Web App
At any point along the way, you can deploy your app. This step turns the Sheet into a web app, and provides the URL that is used by the webhook.
Open up your Google Sheet and click Tools > Script Editor
This opens up a new Apps Script project. Give it a name at the top.
Even before you write any code, you can deploy it ... which will provide your endpoint URL.
To do so, choose Deploy > New Deployment
For type, choose Web App:
Name it whatever you want, but for Who has access set it to "Anyone" so that your webhook service can find it:
The next screen shows the Web App URL. Copy this.
NOTE: You will need to authorize your Google account to use this app.
The Google App Script functions
The first thing a webook does with your URL is to send a GET request. It's looking for a "202 Accepted" response from your app before it sends any data.
A simple doGet(e) function - which uses Apps Script's HTML Service - can provide that response.
Then, a doPost(e) function reads the webhook data (often called a payload). In this case, data from the webhook is written to a new line in a specific spreadsheet. This is just one of many, many things you can do with webhook data.
Get Sample Data and Test It!
Most webhook services will show you an example their webhook structure, and that can be used with an app like Postman to test the process.
In Postman, set up a sample with the following
- a POST request to your Google Sheet's Web App URL
- a Body, using sample data like the webhooks here: https://mailchimp.com/developer/marketing/guides/sync-audience-data-webhooks/
{
"type": "subscribe",
"fired_at": "2009-03-26 21:35:57",
"data": {
"id": "8a25ff1d98",
"list_id": "a6b5da1054",
"email": "api@mailchimp.com",
"email_type": "html",
"ip_opt": "10.20.10.30",
"ip_signup": "10.20.10.30"
"merges": {
"EMAIL": "api@mailchimp.com",
"FNAME": "Mailchimp",
"LNAME": "API",
"INTERESTS": "Group1,Group2"
}
}
}
Run the POST, and check your Google Sheet to see if the data was recorded. If it's not working, use the logs to troubleshoot. If you need more info on Apps Script logs, here's a good article: Debugging and troubleshooting your Apps Script