Search the site:

Home Blog Export WooCommerce Orders to Google Sheets in realtime

Export WooCommerce Orders to Google Sheets in realtime

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.

In the next few steps, we will look at how to add WooCommerce orders to a Google sheet in realtime using WooCommerce Webhooks & Apps Script provided by Google.

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.

WooCommerce Orders Blank Google Sheet

I have added 5 fields to my sheet:

  1. Date Added – Date when this record was added in the Google Sheet
  2. Order Number – This is the WooCommerce order number
  3. Order Date – Date when the order was placed
  4. Order Status – Order status after it was placed
  5. 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.

Google Sheets Script Editor

You will see the below screen once you open the Script Editor.

Google Script Editor Blank View

Google has this feature where it allows a script to be published as a web app if it meets certain conditions. The requirements are:

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:

Google apps script editor code

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.

Google script deploy as web app

Clicking “Deploy as web app” will show the below popup:

Google script Deploy as web app

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.

Google script deployed as web app

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:

Create WooCommerce Webhook

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:

WooCommerce Webhooks

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.

WooCommerce Order Placed

After the above order is placed, it took about 2 minutes for the corresponding information to appear in the Google sheet:

Google sheet with WooCommerce orders

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:

WooCommerce Orders page

And after every order was placed, the Google sheet was populated with the order information within 10 seconds to 1 minute time:

Google sheet with WooCommerce orders

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:

Order Number
Order Date
Order Status
Product Name
Product Quantity
Product Total
Order Total
Billing Email
Billing First name
Billing Last name
Payment Method
Shipping Method
Shipping charges

My Google sheet looks like this now:

Google sheet with detailed WooCommerce order information

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:

Google sheets realtime orders

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.

Browse more in: Code Snippets, WooCommerce How Tos, WooCommerce Integrations, WooCommerce Tutorials

Leave a Reply

avatar

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  Subscribe  
Notify of
from purchased

Subscribe Now