When it comes to integrating with 3rd party services, there are a variety of plugins available in the WooCommerce ecosystem that allow you to transfer information to & from all sorts of systems right from Zapier to ConstantContact to helpdesk systems like Help Scout.
At Tyche, we have integrated some of our plugins with Google Calendar in the past, since a couple of our plugins involve bookings or handling order delivery dates. It was along these lines that I thought to explore on the idea of integrating WooCommerce orders with Google sheets. It would mean that any WooCommerce order that comes in, also gets added to a Google sheet in realtime.
Before starting to write on this, I was looking at what’s available for directly adding WooCommerce order information to a Google sheet. There were a couple of articles & plugins.
The first article talks about saving new WooCommerce orders into Google sheets via the WooCommerce Zapier extension. As you would know, Zapier provides integration with over 1500+ cloud services. This isn’t what I was looking for as I am trying to achieve this integration without using a plugin.
Apart from the WooCommerce Zapier extension, I found another extension on CodeCanyon too. But that’s not what I was looking for.
Creating the Google Sheet
You first need to create a new Google Sheet where all your WooCommerce orders shall be exported. Once it’s created, you can add the fields that you want exported to the Google Sheet.
I have added 5 fields to my sheet:
- Date Added – Date when this record was added in the Google Sheet
- Order Number – This is the WooCommerce order number
- Order Date – Date when the order was placed
- Order Status – Order status after it was placed
- Order Link – Link to the WooCommerce Edit Order page
Writing the Google Apps Script
Once we’ve created the sheet, we need to create the Google Apps Script that will be responsible for adding data to the sheet. The script will get data from the WooCommerce Webhooks (which we will see in a later step) and that data will be parsed & added to the sheet.
You can go to the Tools menu & click on Script Editor.
You will see the below screen once you open the Script Editor.
Google has this feature where it allows a script to be published as a web app if it meets certain conditions. The requirements are:
- It contains a
- The function returns an HTML service
HtmlOutputobject or a Content service
When an external application sends a HTTP GET request, Apps Script invokes the doGet(e) function, whereas when an external application sends a HTTP POST request, Apps Script would invoke the doPost(e) function. A detailed explanation of the Google Scripts web apps & the above functions can be found here.
Below are the 2 functions that I created in my script:
After adding the above code, my Google script now looks as shown below:
Deploying the script as a web app
Once the above code is written, we have fulfilled the requirements for it to be deployed as a web app. Deploying this script as a web app is necessary for it to be able to listen to external GET & POST requests.
To deploy the script as a web app, you need to select the Publish -> Deploy as web app option.
Clicking “Deploy as web app” will show the below popup:
When deploying as a web app, it’s important that each time you deploy, you select “New” in the Project version option. Only when you select “New”, any changes done in that iteration take effect.
You need to select “Me” in the “Execute the app as” option. And “Who has access to the app” should be set to “Anyone, even anonymous”.
Once you click on “Update”, the below message will appear indicating that the project is now successfully deployed as a web app. Along with it, it will also show a URL in the “Current web app URL” field. You need to copy this URL as that’s the one to be used when setting up the WooCommerce webhook in the next step.
Creating the WooCommerce webhook
Now that the Google Apps script is setup & deployed as a web app, we need to add a WooCommerce webhook that will be fired whenever an order is created.
You can add a webhook from WooCommerce -> Settings -> Advanced -> Webhooks menu. When you click on the “Add webhook” button, you need to fill in the fields as shown below:
The URL copied in the previous step from the “Current web app URL” field needs to be put in the “Delivery URL” field of the webhook. What this will do is whenever an order is created, it will send the order information to the delivery URL, where our Google Apps script is setup to listen to any incoming GET or POST requests. In the current case, all the order information is passed via HTTP POST.
Once the webhook is created, it will appear in the list of webhooks with Active status:
You may or may not have additional webhooks in your setup. I have another webhook setup that sends a notification on Slack when an order is updated.
Time for some action
Once an order is placed on your WooCommerce store, in next 1 or 2 minutes, that order’s information would appear in the Google sheet in the last row.
After the above order is placed, it took about 2 minutes for the corresponding information to appear in the Google sheet:
As you keep on receiving orders on your WooCommerce store, the Google sheet will automatically append the order information below the last populated row. I placed 4 orders after the above Order number 1166:
And after every order was placed, the Google sheet was populated with the order information within 10 seconds to 1 minute time:
The Order Link column is currently blank as I haven’t put any data in it. However, you can parse the order information as you like & populate any information from the orders like order total, payment method, products, etc.
Order meta data
When the order.created action is fired by WooCommerce, it sends a bunch of meta information about the order. You can find all the information below, which is taken from one of my sample order. This meta also includes Delivery Date & Delivery charges field, which are added from our Order Delivery Date plugin to automate the store deliveries. The order has 2 products in it, one simple product & a variable product:
Fetching full Order information
As you can see, the above information that I have fetched is very limited. The below example demonstrates how to fetch detailed order information that includes the following fields:
Billing First name
Billing Last name
My Google sheet looks like this now:
My Google Apps script to fetch all the above information is now modified as shown below (please remember to Deploy as a new web app everytime you make changes to the Google Apps script):
Once you place an order, the sheet will start populating as shown below:
Here are the orders that I placed:
Based on the above order #1214, 1217 & 1220, you can see all those orders getting created below with additional information.
Thus, with the help of some simple modifications, we have managed to get full order information in the Google sheet.