Search API Connector Documentation

Print

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.

CONTENTS

BEFORE YOU BEGIN

Click here to install the API Connector add-on from the Google Marketplace.

PART 1: GET YOUR WOOCOMMERCE API KEY

  1. While logged into your WordPress account, click WooCommerce > Settings from the sidebar menu.
    woocommerce-img1
  2. Click the Advanced tab, and then the REST API sub tab. Finally, click either the “Add key” or the “Create an API key” button.
    woocommerce-img2
  3. You’ll be presented with a screen to create your API key. Enter in a brief description and click “Generate API key”
    woocommerce-img3
  4. 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). You now have all you need to access the WooCommerce API!
    woocommerce-img4

PART 2: CREATE YOUR WOOCOMMERCE API REQUEST URL

We’ll follow the documentation to access a list of orders.

  • API root: https://YOUR_DOMAIN/wp-json/wc/v3/
  • Endpoint: /orders
  • URL parameters: consumer_key=YOUR_KEY&consumer_secret=YOUR_SECRET&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:

https://YOUR_DOMAIN.com/wp-json/wc/v3/orders?consumer_key=YOUR_KEY&consumer_secret=YOUR_SECRET&per_page=100

PART 3: PULL WOOCOMMERCE API DATA INTO SHEETS

Now let’s enter our URL into API Connector and import WooCommerce data into Google Sheets.

  1. Open up Google Sheets and click Add-ons > API Connector > Open.
  2. In the Create screen, enter the Request URL we just created
    woocommerce-img5
  3. We don’t need any Headers, so just leave that section blank. We don’t need any extra authentication either so leave that set to None.
  4. Create a new tab and click ‘Set current’ to use that tab as your data destination.
  5. 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:woocommerce-img6

PART 4: MORE EXAMPLE API URLs

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

  1. https://YOUR_DOMAIN.com/wp-json/wc/v3/coupons
  2. https://YOUR_DOMAIN.com/wp-json/wc/v3/orders
  3. https://YOUR_DOMAIN.com/wp-json/wc/v3/products
  4. https://YOUR_DOMAIN.com/wp-json/wc/v3/shipping_methods
  5. https://YOUR_DOMAIN.com/wp-json/wc/v3/subscriptions

PART 5: HANDLE PAGINATION

  1. Note that WooCommerce limits the number of records returned in each request. By default, only 10 records will be returned unless you use the ‘per_page’ parameter as shown above.
  2. To access more than 25 records, add the ‘page’ and ‘per_page’ parameters like this:
    page 1: https://YOUR_DOMAIN.com/wp-json/wc/v3/orders&per_page=100&page=1
    page 2: https://YOUR_DOMAIN.com/wp-json/wc/v3/orders&per_page=100&page=2
  3. With API Connector you can either run these request URLs manually or loop through them 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 as usual, making sure to include per_page=100
    • Pagination type: next page URL
    • Field name: Link
    • Number of pages: enter the number of pages you’d like to fetch
      woocommerce-img7

4 thoughts on “Import WooCommerce Data to Google Sheets”

    • Sure, based on WooCommerce’s API documentation you can limit orders by date using the ‘before’ and ‘after’ parameters. So, for example, your API request would be something like https://YOUR_DOMAIN.com/wp-json/wc/v3/orders?after=2021-02-21. You can reference cells in your sheet and tie them to a function like =today()-1 to make that date value automatically update each day. Please try that and let me know how it goes!

      Reply

Leave a Comment