Using Google Sheets to Capture Webhook Data

Publishing a Google Sheet as a web app provides a convenient endpoint for capturing webhook data - no need to spin up an entire app for that! You can then manipulate and store that data right in Sheets.

 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:

  1. Deploy a Google Sheet as a Web App: this provides the endpoint for the webhook
  2. Write a doGet(e) function: this is checked by the webhook sender
  3. 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

App Script new deployment

 

For type, choose Web App:

Apps Script deployment type

 

Name it whatever you want, but for Who has access set it to "Anyone" so that your webhook service can find it:

Apps Script deployment config

 


The next screen shows the Web App URL. Copy this.

Apps Script web app url
 

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

{
  "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