Search API Connector Documentation
Import WooCommerce Data to Google Sheets
In this guide, we’ll walk through how to pull data from the WooCommerce API directly into Google Sheets, using the API Connector add-on for Sheets.
We’ll first get an API key from WooCommerce, and then set up a request to pull in WooCommerce order data to your spreadsheet.
- Before You Begin
- Part 1: Get your WooCommerce API Key
- Part 2: Create your API Request URL
- Part 3: Pull WooCommerce API Data into Sheets
- Part 4: More Example API URLs
- Part 5: Handle Pagination
- Part 6: API Documentation
Before You Begin
Click here to install the API Connector add-on from the Google Marketplace.
Part 1: Get Your WooCommerce API Key
- While logged into your WordPress account, click WooCommerce > Settings from the sidebar menu.
- Click the Advanced tab, and then the REST API sub tab. Finally, click either the Add key or the Create an API key button.
- You’ll be presented with a screen to create your API key. Enter in a brief description and click Generate API key
- You will now see your consumer key and consumer secret. Copy these down and keep them safe as we’ll need them in a moment (and they won’t be displayed again).
- One last step: we’ll encode these keys to base 64 (aka Basic Authentication). You can do this by entering your your credentials in the format
consumer key:consumer secretinto this form (i.e. your consumer key, then a colon, and then your consumer secret).
The encoding script runs in your browser, and none of your credentials are seen or stored by this site.
We’ll first get a list of orders.
- API root: https://YOUR_DOMAIN/wp-json/wc/v3/
- Endpoint: /orders
- URL parameters: ?per_page=100
The per_page parameter isn’t required, but we’ll include it to show how it works. Putting it together, we get the full API Request URL:
Now let’s enter that URL into API Connector.
- Open up Google Sheets and click Extensions > API Connector > Open.
- In the Create screen, enter the Request URL we just created
- We don’t need any OAuth so just leave that set to None. We don’t need any Headers either, so just leave that section blank.
- Create a new tab and click Set current to use that tab as your data destination.
- Name your request and click Run. A moment later you’ll see a list of your 100 most recent WooCommerce orders in your Google Sheet:
- Some servers may not parse the Authorization header correctly, returning a “Consumer key is missing” other other error. In this case, provide the consumer key/secret as query string parameters instead, e.g.
You can check the documentation for the full list of available API requests, but if you just want to jump in, you can play around with the URLs you enter in the API URL path field. Try the following (one at a time):
Part 5: Handle Pagination
- Note that WooCommerce limits the number of records returned in each request. By default, only 10 records will be returned unless you use the ‘page’ and ‘per_page’ parameters as shown below.
- With API Connector you can loop through these pages automatically with pagination handling (paid feature). WooCommerce provides next page URLs in a Link header (documented here), so you can cycle through like this:
- API URL: enter your request URL, including per_page=100
- Pagination type:
next page URL
- Field name:
- Run until: choose when to stop fetching data
Part 6: API Documentation
Official API documentation: https://woocommerce.github.io/woocommerce-rest-api-docs/#introduction