API Connector Documentation

Print

Import WooCommerce Data to Google Sheets

Last Updated OnMarch 19, 2024
byAna
< Knowledge Base

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. Congrats, you can now access the WooCommerce API.
    woocommerce-apikey

Part 2: Pull Data from WooCommerce to Sheets

The easiest way to get started with the WooCommerce API is through API Connector’s built-in integration.

  1. Select WooCommerce from the drop-down list of applications
    woocommerce-application
  2. Choose an endpoint.
    woocommerce-endpoints
  3. Enter the required parameters: domain, consumer_key, and consumer_secret
  4. Optionally set any optional parameters such as dates, limits, or product filters
  5. Choose a report style. The default "single-row" style will print every order onto its own row, with distinct columns for each line item. The "grid" mode style will print each order ID once, with new rows for each line item, and "grid + unwind" will duplicate IDs for each value.
  6. Select a destination sheet, name your request, and click Run.
    woocommerce-response
  7. WooCommerce returns a lot of fields, so filter out any you don't need using API Connector's field editor. We particularly recommend filtering out all fields containing "meta_data" as these can produce excessive rows in grid mode.
    woocommerce-filterfields

Part 3: Create a Custom API Request

Alternatively, you can run your own custom requests instead of using API Connector’s pre-built integration, using any of the endpoints and parameters shown in the API documentation. Here's an example request setup:

  • Application: Custom
  • Method: GET
  • Request URL: https://your_domain.com/wp-json/wc/v3/orders?consumer_key=your_key&consumer_secret=your_secret

Part 4: Handle Pagination

  1. 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. WooCommerce provides next page URLs in a Link header (documented here), so set the per_page parameter to 100 and cycle through each page like this:
    • Pagination type: next page URL
    • Field name: Link
    • Run until: choose when to stop fetching data
      pagination-nextpageurl-Link

Part 5: API Documentation

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

Subscriptions API: https://woocommerce.github.io/subscriptions-rest-api-docs/#introduction

36 thoughts on “Import WooCommerce Data to Google Sheets”

    • Sure, 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-21T00:00:00Z. 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

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

  4. 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

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

    Reply

  6. 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

  8. 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

    • 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

  10. Hi,
    Can I add a time parameter anywhere so API requests hit automatically after a few minutes or hours or retrieve new data automatically
    thanks

    Reply

  11. the total orders is not a number but is imported in text format and it is also wrong, for example the value is 129.70 and is imported as 130.00.00, how can I do it?

    Reply

    • API Connector just prints out whatever comes back from the API, so it sounds like it may be a regional issue. Can you please click Edit fields > Show raw response and check the raw response from the API? If they send the value back with US-type formatting like x,xxx, and you're in a region with a format like x.xxx, that would explain why the data doesn't get processed correctly. In that case you would need to either use a Sheets function to convert the field to your own data format, or you can switch your entire sheet to US locale (click File > Settings > Locale)

      Reply

  12. Hi! I'm trying to get my product list. I can connect using the site page, key and secret, but my results are off.

    I have been able to get it to work before on an another site, but I'm setting up a new site.

    Do you have any idea why my results look like this?

    Sample from the top few rows:

    <meta name="viewport" content="width=device-width
    " "

    Reply

    • Not sure if the sample went through:

      <meta name="viewport" content="width=device-width
      " "
      " @media all{@charset ""UTF-8"";body"
      <meta name='robots' content='noindex

      Reply

    • That looks like you're receiving an HTML response from the website rather than a data response from the API. I'm not sure why that is happening but I found other people discussing this issue here, and it seems like it could be related to your post structure or a WordPress theme/plugin that conflicts with the WordPress API. Do any of the endpoints work? Also, just to check one other thing, could you try running a custom request and include a header where Key = Accept, Value = application/json?

      Reply

  13. Hi Ana! Thank you for the quick reply. You all are so awesome at supporting users.

    Thank you for the direction. I’m going to investigate and I’ll post back later today or tomorrow if I see anything!

    I could totally see it being a plug-in issue.

    Reply

    • So, did some additional looking and what ended up solving it was:

      - Changing to a different theme.
      - Changing back to my original theme
      - Starting from Scratch with a new REST API Key

      Appreciate the help!

      Reply

      • Nice, I'm glad it's working now. And thank you for sharing your steps, one day this will definitely help some other poor soul searching for answers online 😉

  14. In Woocommerce API, I want the "line_items.name" in Column BB FIXED, but currently the sometimes "line_items.name" keep displaying in different column. Please guide me on how to fix "line_items.name" in Column BB permanently.

    Reply

Leave a Comment

Jump To