Get Data into a Google Sheet using a Web App

This post is the first in a series about getting data into a Google Sheet using a custom built WooCommerce webhook. Along the way, we’ll learn some smaller, really useful topics:

1. Building a web app with a Google App Script, deploying it and testing it with a call from the command line application cURL (this post)
2. Using that web app to accept data from a built in WooCommerce webhook (Update Order) and putting the resulting data into our spreadsheet (the next post),
3. and finally, building a completely custom webhook that can put whatever custom data we have access to into our spreadsheet (super cool!! …and the third post)

The overall goal is to create a webhook that intercepts the ShopMagic abandoned cart action and puts it into a Google Sheet. ShopMagic actually has functionality to do something like this but at the time of writing, it doesn’t work on abandoned carts. But you could generalise this code and make it into a custom webhook of any sort – just attach the custom hook that we create to any action hook in WordPress, set up the payload data and it’ll give you the same result.

But first things first – let’s create a web app with Google App Scripts!

Creating a Google Sheet with a Web App Attached

First we’ll create a spreadsheet in Google Sheets to accept the data from a calling script.

Warning! Creating a Google App Script that receives data could be one of the more frustrating things that you’ll ever do, but I have troubleshooting tips below – read them because there are some tricks to this!

Go to Google Sheets and create a new spreadsheet. Then go to Main Menu > Extensions > App Scripts:

In the resulting editor, for now, we’re just going to put this code:

//doPost returning text
function doPost(e) {
  let sheet = SpreadsheetApp.getActiveSheet();
  let data = JSON.stringify(e);
  sheet.appendRow([data]);
  return ContentService.createTextOutput(data);
}

Google App Scripts is just Javascript, so if you know Javascript then this should be simple to you. If not then just copy/paste anyway.

The doPost function is built in to Google App Scripts and receives any post request. Inside it you can do what you want with the data. There is a companion function called doGet for get requests, but we don’t need that here. To return something to the calling function, you need to use ContentService to generate the proper return data.

Deploying the App

Now we have to deploy the app to make it publicly accessible. Click the blue deploy button on the top right of the editor page and choose New Deployment from the dropdown:

This will open a modal with a settings cog in the ‘Select type’ section which you can use to select ‘Web App’:

Then fill out the details on the resulting screen (you don’t have to fill in the description but you should set who has access to ‘Anyone’):

Then click deploy and copy the resulting App URL because we’ll use it in the cURL script below.

Run the script

In addition to deploying the app, also run the script by clicking on the Run button at the top of the editor:

Google will ask you to grant permissions to the app which you need to do in order for it to work. Just go through the process and accept the grants.

Calling it with cURL

You can use Postman or something else for this, but I’m just going to run this script with cURL from the command line to test if the doPost is getting the request and handling the values:

curl -L \
-H 'Content-Type:application/json' \
-d '{"name": "Clare","country": "Australia"}' \
"https://script.google.com/macros/s/AKfycbxGqIanuJOWydL9rO7pcAQN01p70tPRPvYXzUUNyvHCcW7JbcsSRhE0oR3x-3EYypPUlw/exec"

You need to swap out the URL at the end of the script for your own URL that is generated when you do the deployment process detailed above.

What I’m getting as an output to this cURL statement is this:

{"parameter":{},"queryString":"","contextPath":"","parameters":{},"contentLength":40,"postData":{"contents":"{\"name\": \"Clare\",\"country\": \"Australia\"}","length":40,"name":"postData","type":"application/json"}}

So I can see the data going in and out. Also, the sheet.appendRow([data]); line in the app script is putting that same data into a row in my spreadsheet (and here you can see all the goes I had at it before it finally worked!):

Troubleshooting doPost

An app will happily spit error messages out at you that are difficult to decode and if you don’t know a few basic things, you’ll go mad. The first one is, every time you make a change you have to do a new deployment of the script and swap in the resulting new URL to your cURL script or whatever you’re using to call the app. This is both stupid and annoying, but now you know, you won’t waste countless hours trying to figure it out!

For info on how to troubleshoot different kinds of errors, here is a comprehensive guide.

Hopefully however, the above script will just work and you won’t have to go through this.

Wrapping up

So we now have an app script that can accept data, put the data into a spreadsheet and return a result to the calling function. In the next post we’ll look at how to call this script from a WooCommerce webhook. And then in the post after that, we’ll make a custom webhook so that we can put any data we want into our Google sheet.