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).
    woocommerce-apikey
  5. 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 secret into 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.
     

     

     

Part 2: Create Your WooCommerce API Request URL

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:

https://YOUR_DOMAIN.com/wp-json/wc/v3/orders?per_page=100

Part 3: Pull WooCommerce API Data into Sheets

Now let’s enter that URL into API Connector.

  1. Open up Google Sheets and click Extensions > API Connector > Open.
  2. In the Create screen, enter the Request URL we just created
    woocommerce-url
  3. 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.
  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-results
  6. 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.
    https://site.com/wp-json/wc/v3/orders?consumer_key=ck_111111111111&consumer_secret=cs_1111111111111&per_page=100

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 ‘page’ and ‘per_page’ parameters as shown below.
    woocommerce-pagination
  2. 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: Link
    • Run until: choose when to stop fetching data
      pagination-nextpageurl-Link

Part 6: API Documentation

Official API documentation: https://woocommerce.github.io/woocommerce-rest-api-docs/#introduction

Previous Import Wikipedia Data to Google Sheets
Next Import WordPress Data to Google Sheets

22 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
  1. Hey there.

    I am busy running the woocommerce api to fetch my products and the stock level but I am not able to bring in the Variable products.

    Here is my url https://website.co.za/wp-json/wc/v3/products?per_page=50&page=1&status=publish

    I am filtering using JSME
    [].{sku:sku,stock_quantity:stock_quantity,regular_price:regular_price,sale_price:sale_price}

    How would I incorporate that the variants SKU and Stock level also get’s pulled?

    Thanks in advanced if anyone can help.

    Reply
    • Hey Andrew, I can’t really say with this information. Something like [].{sku:sku,stock_quantity:stock_quantity,regular_price:regular_price,sale_price:sale_price,variations:variations} could partially work but I don’t know if stock quantity is available from the /products endpoint, or if ‘variations’ is the field you’re looking for. You basically need to a) confirm that variants SKU and stock level are available metrics returned by the /products endpoint, and then b) check the underlying JSON to contruct a JMESPath query that targets them. If you send me your sample JSON and let me know which fields you want to extract from it I can help you create that JMESPath.

      Reply
  2. Hello,
    First I would like to say that the solution is great and helps a lot. Now I would like to ask a question, how to pull 2 order status at the same time? I tried but only present error. I want to pull orders completed and in processing

    https://website.com/wp-json/wc/v3/orders?per_page=100&status=completed

    and
    &status=processing

    how to do that?

    Thanks!

    Reply
  3. Anyone getting an SSL error when trying to connect into a Woocommerce sandbox account? I can access live accounts without issue.

    Reply
  4. Hey the Link parameter for pagination is not working anymore
    It gives error

    Cannot find function domain in object false

    I think woo has retired this parameter? Is there an alternative?

    Reply
  5. Hello, I followed the instructions above but I’m getting the following error:

    {“code”:”woocommerce_rest_cannot_view”,”message”:”Sorry, you cannot list resources.”,”data”:{“status”:401}}

    Do you know what I’m doing wrong?

    Reply
    • I see a lot of people discussing this problem online, for example here. One of the proposed solutions there is to use Basic authentication instead of passing the key and secret in the URL, like this:

      1. Remove the consumer key and secret parameters from your URL
      2. Encode consumer_key:consumer_secret (you can use the form on this page)
      3. Add a Header of key = Authorization, value = Basic your_encoded_value

      Can you please see if this works for you?
      If not, a different proposed solution was to add a slash before the parameters, like https://example.com/wp-json/wc/v3/products/?
      instead of https://example.com/wp-json/wc/v3/products?

      Reply
      • Thank you Ana! I tried adding the slash before the parameters which didn’t work. Then I used your first suggestion and it worked perfectly.
        Thank you again!

      • Thank you for the update! That’s good to know. I’ll update the article today so other people don’t have this problem.

    • There’s no realtime sync option but you can update products using a PUT request, and get products with a GET request, and run those on a schedule.

      Reply

Leave a Comment

Table of Contents