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.

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.

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.

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:

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:

//this is a function that fires when the webapp receives a GET request
function doGet(e) {
  return HtmlService.createHtmlOutput("request received");
}

//this is a function that fires when the webapp receives a POST request
function doPost(e) {
  var myData = JSON.parse([e.postData.contents]);
  var order_number = myData.number;
  var order_created = myData.date_created;
  var order_status = myData.status;
  
  var timestamp = new Date();
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.appendRow([timestamp,order_number,order_created,order_status]);
}

view rawcode.gs hosted with ❤ by GitHub

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:

{
"id":1194,
"parent_id":0,
"number":"1194",
"order_key":"wc_order_YzXqv7u79WjZS",
"created_via":"checkout",
"version":"3.6.4",
"status":"on-hold",
"currency":"USD",
"date_created":"2019-07-20T02:08:30",
"date_created_gmt":"2019-07-19T20:38:30",
"date_modified":"2019-07-20T02:08:31",
"date_modified_gmt":"2019-07-19T20:38:31",
"discount_total":"0.00",
"discount_tax":"0.00",
"shipping_total":"200.00",
"shipping_tax":"0.00",
"cart_tax":"0.00",
"total":"4252.00",
"total_tax":"0.00",
"prices_include_tax":true,
"customer_id":1,
"customer_ip_address":"::1",
"customer_user_agent":"Mozilla\/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/75.0.3770.142 Safari\/537.36",
"customer_note":"",
"billing":{
  "first_name":"Vishal",
  "last_name":"Kothari",
  "company":"Tyche Softwares",
  "address_1":"B 201, Apsara Building, Rd. 7,",
  "address_2":"Daulat Nagar, Borivali East",
  "city":"Mumbai",
  "state":"MH",
  "postcode":"400066",
  "country":"IN",
  "email":"vishal@tychesoftwares.com",
  "phone":"+919819888007"
},
"shipping":{
  "first_name":"Vishal",
  "last_name":"Kothari",
  "company":"Tyche Softwares",
  "address_1":"B 201, Apsara Building, Rd. 7,",
  "address_2":"Daulat Nagar, Borivali East",
  "city":"Mumbai",
  "state":"MH",
  "postcode":"400066",
  "country":"IN"
},
"payment_method":"bacs",
"payment_method_title":"Direct bank transfer",
"transaction_id":"",
"date_paid":null,
"date_paid_gmt":null,
"date_completed":null,
"date_completed_gmt":null,
"cart_hash":"2774074f472114fd92b2aebe8cb33022",
"meta_data":[
  {
    "id":16021,
    "key":"is_vat_exempt",
    "value":"no"
  },
  {
    "id":16023,
    "key":"_total_delivery_charges",
    "value":"40"
  },
  {
    "id":16024,
    "key":"Delivery Date",
    "value":"Wednesday, 24 July, 2019"
  },
  {
    "id":16025,
    "key":"_orddd_timestamp",
    "value":"1563926400"
  },
  {
    "id":16026,
    "key":"Time Slot",
    "value":"10:30 PM - 10:45 PM"
  },
  {
    "id":16027,
    "key":"_orddd_time_slot",
    "value":"22:30 - 22:45"
  },
  {
    "id":16028,
    "key":"_orddd_timeslot_timestamp",
    "value":"1564007400"
  }
],
"line_items":[
{
  "id":463,
  "name":"500 GB Hard disk drive",
  "product_id":375,
  "variation_id":0,
  "quantity":1,
  "tax_class":"",
  "subtotal":"4000.00",
  "subtotal_tax":"0.00",
  "total":"4000.00",
  "total_tax":"0.00",
  "taxes":[
  ],
  "meta_data":[
  ],
  "sku":"",
  "price":4000
},
{
  "id":464,
  "name":"Panera Sandwich - Mozzarella",
  "product_id":445,
  "variation_id":447,
  "quantity":1,
  "tax_class":"",
  "subtotal":"12.00",
  "subtotal_tax":"0.00",
  "total":"12.00",
  "total_tax":"0.00",
  "taxes":[
  ],
  "meta_data":[
    {
      "id":3678,
      "key":"select-your-cheese",
      "value":"Mozzarella"
    }
  ],
  "sku":"",
  "price":12
}
],
"tax_lines":[
],
"shipping_lines":[
{
  "id":466,
  "method_title":"Flat rate",
  "method_id":"flat_rate",
  "instance_id":"3",
  "total":"200.00",
  "total_tax":"0.00",
  "taxes":[
  ],
  "meta_data":[
  {
    "id":3690,
    "key":"Items",
    "value":"500 GB Hard disk drive × 1, Panera Sandwich - Mozzarella × 1"
  }
  ]
}
],
"fee_lines":[
{
  "id":465,
  "name":"Delivery Charges",
  "tax_class":"0",
  "tax_status":"taxable",
  "amount":"40",
  "total":"40.00",
  "total_tax":"0.00",
  "taxes":[
  ],
  "meta_data":[
  ]
}
],
"coupon_lines":[
],
"refunds":[
]
}

view rawwoocommerce-order-data-via-webhook.json hosted with ❤ by GitHub

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:

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):

//this is a function that fires when the webapp receives a GET request
function doGet(e) {
  return HtmlService.createHtmlOutput("request received");
}

//this is a function that fires when the webapp receives a POST request
function doPost(e) {
  var myData             = JSON.parse([e.postData.contents]);
  var order_number       = myData.number;
  var order_created      = myData.date_created;
  var order_status       = myData.status;
  var product_name       = myData.line_items[0].name;
  var product_qty        = myData.line_items[0].quantity;
  var product_total      = myData.line_items[0].total;
  var order_total        = myData.total;
  var billing_email      = myData.billing.email;
  var billing_first_name = myData.billing.first_name;
  var billing_last_name  = myData.billing.last_name;
  var payment_method     = myData.payment_method_title;
  var shipping_method    = myData.shipping_lines[0].method_title;
  var shipping_total     = myData.shipping_lines[0].total;
  
  var timestamp = new Date();
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.appendRow([timestamp,order_number,order_created,order_status,product_name,product_qty,product_total,order_total,billing_email,billing_first_name,billing_last_name,payment_method,shipping_method,shipping_total]);
}

view rawcode2.gs hosted with ❤ by GitHub

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.

Tin cùng chuyên mục