Import WooCommerce Data To Google Sheets

“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 Use WooCommerce At All?

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.

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 WordPress dashboard, Click Settings on the left side.

woocommerce google sheets1

2 – Click Advanced. Then click REST API.

woocommerce google sheets3

3 – Click 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 Import WooCommerce Data To Google Sheets

To import WooCommerce data like orders to Google Sheets automatically, you need a Google Sheets add-on. These add-ons are most likely paid add-ons. As mentioned, you can do it manually (for free) to save money. But in perspective, you might want to spend a little bit to save time. You choose.

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

Installing The Google Sheets Add-on

1 – At the top of your Google Sheet, click ‘Add-ons’. Then click ‘Get add-ons’.

tmr 2

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

tmr 1

3 – You now have the add-on installed on your Google Sheets file.

tmr 3

Import WooCommerce Data Source

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

tmr 15

2 – Click ‘Add +’ under Data Sources.

tmr 4

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

4 – For ‘Base URL’, add your complete domain plus /wp-json/wc/v3
For example: https://www.yourdomain.com/wp-json/wc/v3

5 – For ‘Authorization’, select ‘Basic’. The username is the Consumer key while the password is the Consumer secret from before.

6 – For ‘Headers’, keep them empty.

7 – Click Save

woocommerce google sheets6
woocommerce google sheets5b

You may choose ‘Test before saving’ to verify that the API base URL is valid. Select ‘GET’ for Method.

Add /orders under Test URL Suffix. If not valid, the tool gives instructions on how to fix it.

If valid, the tool prompts ‘Successfully authenticated’.

woocommerce google sheets7

Import WooCommerce Data Query

This point in the process is the importing orders list to the spreadsheet.

1 – Click the burger icon. Then Click ‘Data Queries’. And click ‘Add +’.

tmr 11

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

3 – ‘Save on Sheet’ is the location of the sheet where the data is imported to. The location might be ‘Sheet 1’ but it depends if you named your sheet.

4 – Add A1 under ‘Cell’.

5 – Unselect ‘Append new data’. Unselected means the data overwrites the current spreadsheet.

woocommerce google sheets8

6 – Then follow the screenshot for the next set of inputs.

Method: GET

URL Path/Suffix: /orders. We’ll list all orders in this example.

JMES Path: Leave it empty

Pagination: None

Output Format: Grid (default)

woocommerce google sheets9

6 – Click ‘Run query’

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.

Other WooCommerce Data Points

At this point, the data you want to access depends on the API endpoints or API paths. All you need to do is change the URL Path/Suffix. For any attribute <>, this means you need to change it dynamically to the appropriate one based on your labeling.

For example, if you need to list all customers, you add /wp-json/wc/v3/customers in URL Path/Suffix. If you need information on product ID 04, you add /wp-json/wc/v3/products/04 in URL Path/Suffix. Learn more WooCommerce API endpoints here.

InformationAPI Endpoint
Retrieve an order/wp-json/wc/v3/orders/<id>
List all orders/wp-json/wc/v3/orders
Retrieve a coupon/wp-json/wc/v3/coupons/<id>
List all coupons/wp-json/wc/v3/coupons
Retrieve a customer/wp-json/wc/v3/customers/<id>
List all customers/wp-json/wc/v3/customers
Retrieve an order note/wp-json/wc/v3/orders/<id>/notes/<note_id>
List all order notes/wp-json/wc/v3/orders/<id>/notes
Retrieve a refund/wp-json/wc/v3/orders/<id>/refunds/<refund_id>
List all refunds/wp-json/wc/v3/orders/<id>/refunds
Retrieve a product/wp-json/wc/v3/products/<id>
List all products/wp-json/wc/v3/products
Retrieve a product variation/wp-json/wc/v3/products/<product_id>/variations/<id>
List all product variations/wp-json/wc/v3/products/<product_id>/variations
Retrieve a tax rate/wp-json/wc/v3/taxes/<id>
List all tax rates/wp-json/wc/v3/taxes
Retrieve a payment gateway/wp-json/wc/v3/payment_gateways/<id>
List all payment gateways/wp-json/wc/v3/payment_gateways
Retrieve a shipping method/wp-json/wc/v3/shipping_methods/<id>
List all shipping methods/wp-json/wc/v3/shipping_methods
source

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 the burger icon. Then click Schedule Refresh. And click ‘New +’.

tmr fb5

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

-Name

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

-Timezone

tmr li3

3 – 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

4 – Click ‘Save’.

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

Francis Angelo Reyes

Francis is the owner and runs Lupage Digital. With 5 years of experience, Francis writes everything he knows about digital marketing. There’s a large knowledge gap between developers and digital marketers. Francis is the middleman between where the non-technical and technical meet.