Search the site:

How to 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 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 real-time.

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.

And while Woocommerce provides many functionalities out of the box, Woocommerce orders can’t be exported to Google sheet by default in WooCommerce.

Therefore, in the next few steps, we will look at how to export WooCommerce orders to a Google sheet in real-time using WooCommerce Webhooks & Apps Script provided by Google.

Step-by-step Guide on How to Export Woocommerce Orders to Google Sheet

Step 1: 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 to export to the Google Sheet.

WooCommerce export Orders starting with a 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

Step 2: 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 for woocommerce order information exports

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 an HTTP GET request, Apps Script invokes the doGet(e) function, whereas when an external application sends an 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 for fetching woocommerce order information

Step 3: 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; meaning the script to export our Woocommerce orders in real-time is now ready.

Note: 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.

Setting up Woocoomerce export order: Google script deployed as web app

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

Setting up Woocoomerce export order: Google script deployed 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”, do 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 this is the URL that will be used as we proceed in the Woocommerce orders export settings with WooCommerce webhook in the next step.

Setting up Woocoomerce export order: Google script deployed as web app

Step 4: Creating the WooCommerce webhook

Now that the Google Apps script is set up & 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:

creating woocommerce webhook to enable woocommerce orders to be exported to google sheet

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 set up 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:

creating woocommerce webhook to enable woocommerce orders to be exported to google sheet

You may or may not have additional webhooks in your setup. I have another webhook set up that sends a notification on Slack when an order is updated.

Step 5: Time for some action

Once an order is placed on your WooCommerce store, in the 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 information exported from WordPress

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 Order information 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 exported 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 metadata

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 orders. This meta also includes the 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 Woocommerce 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:

Exporting Woocommerce orders to Google sheet with detailed 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 every time 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:

Woocommerce orders getting exported directly to Google sheet in real time (as they come in))

Based on the above order #1214, 1217 & 1220, you can see all those orders getting created below with additional information.Google sheet containing all order information from WooCommerce

Thus, with the help of some simple modifications, we have managed to get full order information in the Google sheet.

This is the easiest and most straightforward method you can use to export your Woocommerce orders in WordPress. The orders are exported in real-time and there is no additional effort on your side whenever a new order is placed.

Browse more in: WooCommerce How Tos, Code Snippets, WooCommerce Integrations, WooCommerce Tutorials
Subscribe
Notify of
guest

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

57 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Grey
2 years ago

Hi, may I know how to list out all the products name and ID purchased in the order? The code above just getting 1 product information?

Tran Hong Quan
2 years ago

I have error “TypeError: can not read properties “postData” from undefined. (line 8, file “Listener”) when I create google script as you wrote above. I don’t know why? Please help me to fix it. Thanks

Kaivalya
Kaivalya
2 years ago
Reply to  Tran Hong Quan

Same here.

Nerea
Nerea
1 year ago
Reply to  Tran Hong Quan

I am also having the same error. Does anyone know how to fix this error?

Thành Thái
2 years ago

If you buy 2 different products, this code will not display correctly on google sheet.
On the google sheet only the first product is recorded.

Mike
Mike
2 years ago
Reply to  Thành Thái

Just tweak script and add a new row foreach product:

//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 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 timestamp = new Date();
var sheet = SpreadsheetApp.getActiveSheet();

for (var i = 0; i < myData.line_items.length; i++) {

var product_name = myData.line_items[i].name;
var product_qty = myData.line_items[i].quantity;
var product_total = myData.line_items[i].total;
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]);
}
}

Immy
2 years ago
Reply to  Mike

Hey, I tried your version of the code and still doesn’t give me each product line. Do you have an example which works? Thanks.

Aditi Lohomi
Aditi Lohomi
11 months ago
Reply to  Mike

Hi,
Thanks for the code.

Aditi Lohomi
Aditi Lohomi
11 months ago
Reply to  Mike

Also, can you tell me how to update my products automatically to woocommerce products section using the same web app process.
Thanks.

Terresa Lee Lindsay
2 years ago

This all seems brilliant. However I have no skills in this area. I would like to get woo commerce booking information as well such as booking dates, resources. Is this possible.

Diego Monserrat Nogués

I have modified the code to order up to 70 products in the same order, but the Script has stopped working, even when I have removed almost all products. It is saved correctly, without errors, but it no longer updates the sheet. The modification has been like this … var product_name = myData.line_items[0].name; var product_qty = myData.line_items[0].quantity; var product_name1 = myData.line_items[1].name; var product_qty1 = myData.line_items[1].quantity; var product_name2 = myData.line_items[2].name; … sheet.appendRow([order_created,order_number,product_name,product_qty,product_name1,product_qty1,product_name2,product_qty2 …. Is there a limit on the number of variables or requests for information that can be made in Google Apps Script? It seems that I have been banned… Read more »

from purchased

Subscribe for more offers

57
0
Would love your thoughts, please comment.x
()
x