Connect WooCommerce Data To Google Sheets

Maintain your SEO traffic with seamless website migration. Schedule a consultation and start your brand relaunch!

“How do I connect WooCommerce orders to Google Sheets?”
“Is there a way to display WooCommerce products in Google Sheets?”

– Ecommerce specialist

Ecommerce maintenance takes hours to do. You want more time to strategize, not crunch numbers. If you maintain a WooCommerce website, you don’t experience any difference. You’re juggling different things at once so you want to do reports fast.

Why WooCommerce?

I did a small and quick survey in the Online Genius slack. WooCommerce is one of the most used e-commerce platforms with Shopify. Though not as high, the value comes from automating reports connecting ecommerce data with other data. Integration of information across platforms into one report is a goldmine to interpret data.

og woocommerce shopify 1

In this article, learn how to use the WooCommerce API in Google Sheets to import products data or orders information.

Why Pay for Automation at All?

Automations solve huge problems in organizations. So, most automation tools are paid products. Yes, you can do everything manually for free to save money. But in perspective, you might want to spend a little to save time. The number of hours that you take back to avoid hellish and repetitive work is worth the price.

busy desktop

I hate a super cluttered desktop. You ever experienced a desktop full of spreadsheet files? I hate them. It increases stress and productivity comes to an all-time low. The amount of time I clicked the Export CSV button is unbearable. Automation avoids all of this and is useful when you’re tracking marketing campaigns.

Requirement: WooCommerce API

You need to go through the WooCommerce API to import the data into Google Sheets. Unfortunately, there’s no other way around as you need to go through some code syntax. Luckily, accessing the WooCommerce data is within reach and easy to do. But note that you need a little bit of coding knowledge to understand this.

First thing is to create the API key for WooCommerce. The guide assumes WooCommerce is already installed on your WordPress.

1 – On the WordPress dashboard, under Settings, click on Settings on the left side.

woocommerce google sheets1

2 – Click on Advanced. Then click on REST API.

woocommerce google sheets3

3 – Click on the big Create an API key purple button or Add key button.

4 – Once inside, add a description. Then select the user. Then select Read/Write for Permissions.

woocommerce google sheets4

5 – The Consumer key and Consumer secret are created.

IMPORTANT: Remember and note these API credentials now as you won’t see them again.

Remember that the Consumer key is your username and the Consumer secret is your password. You use these two components later on.

woocommerce google sheets5

How To Connect WooCommerce Data To Google Sheets

This article shows two options on how to import WooCommerce orders to Google Sheets. Option 1 is using a Google Sheets add-on. Option 2 is using a third-party integration tool.

Option 1: Google Sheets Add-On

You’ll use Two Minute Reports for this example. TMR imports data from the WooCommerce API directly to Google Sheets in minutes.

tmr sheets 01

Installing The Google Sheets Add-on

1 – Go to your Google Drive workspace.

2 – Click + New then create a new Google Sheets file.

tmr 0
tmr 00

3 – At the top of your file, click on Extensions. Then click on Add-ons. Then click on Get add-ons.

sheets 01

4 – Search for Two Minute Reports. Then install it.

tmr 1

5 – You now have the extension installed on your Google Sheets file.

sheets tmr 01

Import WooCommerce Data Source

1 – Launch the extension. A navigation pane on the right side appears.

sheets tmr 02

2 – Click on Add + under Data Sources.

tmr 16

3 – Name your data source. Then select ‘WooCommerce’ under Type.

woocommerce sheets 1

4 – Under API Key, enter the Consumer key from earlier.

5 – Under API Secret, enter the Consumer secret from earlier.

6 – Under Store URL, enter the store domain.

7 – Click on Save.

woocommerce sheets 4
woocommerce sheets 6

8 – If valid, the tool prompts ‘Successfully authenticated’.

woocommerce sheets 5

Import WooCommerce Data Query

1 – Click on the burger icon. Then click on Data Queries. Then click on Add +.

tmr 11

2 – Name your data query. Then find the API Bridge data source you imported earlier.

3 – Under (Data Source), find the WooCommerce data source you imported earlier.

4 – Add A1 under ‘Cell’.

5 – Under Save on Sheet, select the location of the sheet where the data will appear.

6 – Under Cell, select A1.

7 – Under Query, select the following Metrics:

Line item subtotal
Line item subtotal tax
Total sales
Total tax

woocommerce sheets 2

8 – Under Dimensions (Group by), select the following data points:

Order ID
Product name
Product ID
Product variation id

9 – Under Date, select the date range you want to get data from.

Ignore all other filters and options.

10 – Click on Run query.

woocommerce sheets 3

6 – Click on Run query.

woocommerce google sheets10

Viola! You imported the orders data on a Google Sheet. These are the first steps in pulling in any important data from the WooCommerce API. At this point, you may take the steps on accessing your required data.

Schedule WooCommerce Reports

Two Minute Reports has a scheduling function. This means the spreadsheet refreshes the acquired data from the API. Reports are automated even more.

1 – Click on the burger icon. Then click on Schedule Refresh.

2 – Click on New +.

tmr fb5

3 – Once inside, you can set the terms of the report schedule.

-Repeats: This is where you set how often the data refresh happens. Two Minute Reports can do hourly refreshes.

tmr li3

4 – You can also set the notification terms. Two Minute Reports can email you a PDF or Excel of the data. You can also decide to have the email contain the sheet content in the email body.

tmr fb7
tmr fb8

5 – Click on Save.

Scheduling helps when you need to report on overtime. No more copy and paste manual reports every day, or every week, or every month.

Option 2: Third-Party Integration Tool

You’ll use Make for this option. Make is a powerful integration platform that allows you to visualize, design, and automate your work in minutes. In other words, it lets apps talk to each other so that you can remove repetitive tasks.

make 01c

Create a Make account and get started.

Getting Started

1 – Click on Templates. The tool already has a template since this scenario is a standard case.

make 10

2 – Search and click on the WooCommerce Google Sheets template.

woo sheets integromat 01

3 – Click on the Create new scenario from template button from the lower left of the page.

make 11

Setting Up WooCommerce Connection

The first section is setting up the WooCommerce connection.

1 – Under Connection click on Add.

woo sheets integromat 02

2 – Under Connection name, name the connection. The name is arbitrary so it’s up to you. But I suggest writing a descriptive name for later use.

3 – Under Store’s URL, enter the domain of your store. Include https://.

4 – Under Consumer key, enter the generated consumer key from earlier.

5 – Under Consumer secret, enter the generated consumer secret from earlier.

woo sheets integromat 03

6 – Click on Continue.

7 – Under Watch, select either New only, Completed only, or All changes.

8 – Under Maximum number of returned orders, if it appears, enter 10 for now.

There may be advanced settings. Ignore those for now.

woo sheets integromat 04b

9 – Click on OK.

10 – Under Choose where to start, if it appears, select All orders.

woo sheets integromat 05

Done! At this point, you’re finished with the WooCommerce connection side.

Setting Up Google Sheets Connection

Next is setting up the Google Sheets connection. But first, you need to go to Google Drive and create a new Google Sheets file.

1 – Go to your Google Drive workspace.

2 – Click on + New then create a new Google Sheets file.

tmr 0
tmr 00

3 – Name the Google Sheets file. The name is arbitrary so it’s up to you. But I suggest writing a descriptive name for later use.

integromat 07 sheets

4 – Name the tab or sheet on the bottom. The name is arbitrary so it’s up to you. But I suggest writing a descriptive name for later use.

integromat 07 sheets2

5 – Name the headers. It’s helpful to map out the correct data point in the integration process later.

Column A
Column B
Column C
Column D

integromat 07 sheets3

The first steps are done! Go back to Make.

1 – Press right-click. Click on + Add a module. Or you can double-click then click on the HUGE circle with a PLUS SYMBOL.

2 – Search for Google Sheets. Then select Add a Row.

mailchimp sheets integromat 06

3 – Under Connection, click on Add or Create a connection.

4 – Name the connection. The name is arbitrary so it’s up to you. But I suggest writing a descriptive name for later use.

make 06 sheets

5 – Click on Continue or Sign-In. A pop-up window appears.

6 – Inside the pop-up window, select the Gmail account that stores the Google Sheets file.

make 04

7 – Click on Allow. Make asks permission to access your Google account. This is standard procedure. The window closes after clicking it.

make 05

Back in Make you add the additional parameters.

8 – Under Choose a Method, choose Select by path.

9 – Under Choose a Drive, select My Drive. The spreadsheet you created earlier is likely located here.

10 – Under Spreadsheet ID, select the Google Sheets file you created earlier. Find the name you gave it.

11 – Under Sheet Name, select the Google Sheets tab from earlier. Find the name you gave it.

12 – Under Table contains headers, select Yes.

After which, the Values are created.

make 07 sheets

13 – Under Values is where it gets tricky. The headings from the Google Sheets file are the parameters where you decide the values. Once you click on the field, you’re presented with different parameters. Don’t be overwhelmed. Read the context of the parameters to know which one is mapped to which.

integromat 07 sheets7

Under (A), select Created at.
Under (B), select Order number.
Under (C), select Status.
Under (D), select Total.

The parameters that appear here are from the connection you added earlier. Other parameters are also available. Make sure to add an extra column and name it on the Google Sheets spreadsheet so on and so forth.

14 – Click on Ok. Now it’s time to run and process the integration.

15 – Click on Run once with a play button icon.

make 08

A spinning wheel and a Log on the bottom right appear. Wait for the integration to finish. How long the integration takes depends on the Maximum number of results you select. It’s finished when a) green checkmarks appear, and when b) The scenario run was completed appears in the Log.

woo sheets integromat 06

Go to your Google Sheets file and you now see the ecommerce data successfully exported from WooCommerce. Viola! Congratulations you imported WooCommerce orders to Google Sheets with complete information! You may also add other parameters or fields if you need them.

Schedule Automation and Reports

Make can schedule the automation based on cycles. The schedule is up to you.

1 – On the screen, click on the Scheduling button and turn it on.

make 08

2 – Under Run scenario, select the schedule you need. What you select on this parameter dictates the next ones.

If you select At regular intervals, you need to select the minutes that Make processes the automation.

If you select Once, you need to select the date.

If you select Every day, you need to select the time.

If you select Days of the week or Days of the week, you need to select the days.

make 09

3 – Click on Ok. Ignore any advanced settings for now.

Viola! Congratulations! You now know how to import WooCommerce orders to Google Sheets with the API. The methods above automate accessing data without manual import and export. It avoids spreadsheet headaches.

Conclusion: Create WooCommerce Reports Faster

Know how to use an API in Google Sheets for your ecommerce needs. Google Sheets is a web-based spreadsheet app that provides an easy way to analyze data. With the help of Google Sheets add-ons, you can use external APIs and automate reporting.

You’re wasting time manually importing and exporting data. If you’re a marketer, automated reporting through APIs makes lives easier.

About The Author