Import Shopify Data to Google Sheets

Import Shopify Data to Google Sheets

Last Updated On October 25, 2019
< Knowledge Base

In this guide, we’ll walk through how to pull data from the Shopify API directly into Google Sheets, using the free API Connector add-on. We’ll first get an API password from Shopify, and then set up a request to pull in product and transaction details from your Shopify store to your spreadsheet.

PART 1: GET YOUR SHOPIFY API PASSWORD

  1. Log into your Shopify store and click Apps
    shopify-api-img1

  2. On the Apps page, click “Manage private apps”
    shopify-api-img2

  3. From here, click the “Create a new private app” button
    shopify-api-img3

  4. On the “Create private app” screen, enter a name for your app and an email address (these are required fields). For now you don’t need to change anything else, just scroll to the bottom and click Save.
    shopify-api-img4

  5. You’ll see a verification modal after clicking Save. Click “I understand, create the app” to continue.
    shopify-api-img5

  6. Your Shopify password is ready. (For our purposes you can ignore everything but the Password). Congrats, you’re done! You now have access to the Shopify API, and can start pulling Shopify data into Google Sheets.
    shopify-api-img6

PART 2: CREATE YOUR API REQUEST URL


We’re going to follow the Shopify API documentation to retrieve a list of products in your store.

Shopify base URL: https://{your_shop_name}.myshopify.com
Example: https://mixedshoptest.myshopify.com

Endpoint: /admin/api/{version}/{resource}.json
Example: /admin/api/2019-07/products.json

Putting it all together, we get the full API Request URL:
https://mixedshoptest.myshopify.com/admin/api/2019-07/products.json
(Of course, you’ll need to use your own shop name instead of mixedshoptest)

PART 3: ENTER VALUES INTO API CONNECTOR


We’re now ready to enter all our values into API Connector to start importing Shopify data into Google Sheets.

  1. Open up Google Sheets and click Add-ons > API Connector > Create New API Request.
  2. In the Create Request interface, enter the Request URL we just created
    shopify-api-img7

  3. Under Headers, enter X-Shopify-Access Token as your Key, and your password as the Value, like this (the curly brackets indicate that you need to substitute in your own password):
    X-Shopify-Access-Token {your password}

    It should look like this:
    shopify-api-img8

  4. Create a new tab. You can call it whatever you like, but here we’ll call it ‘Products’. While still in that tab, click ‘Set’ to use that tab as your data destination.
  5. Name your request. Again we’ll call it ‘Products’
  6. Click Run and a moment later you’ll see your Shopify data populate the Products tab in your Google Sheet:
    shopify-api-img9

PART 4: NOTES AND EXPANSIONS

  1. 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):
    https://your_shop_name.myshopify.com/admin/api/2019-07/products.json
    https://your_shop_name.myshopify.com/admin/api/2019-07/orders.json?status=any
    https://your_shop_name.myshopify.com/admin/api/2019-07/customers.json
    https://your_shop_name.myshopify.com/admin/api/2019-07/countries.json
    https://your_shop_name.myshopify.com/admin/api/2019-07/policies.json

    shopify-api-img10

  2. Shopify limits the number of responses, usually to 50 records at a time. To get more, use the ‘limit’ parameter, e.g. /admin/api/2019-07/orders.json?status=any&limit=250. You can combine this with date-based parameters, e.g. /admin/api/2019-07/orders.json?status=any&limit=250&created_at_min=2019-10-25T16:15:47-04:00.
  3. If, even after increasing the ‘limit’ parameter, your response returns more than the limit, consider shortening your time range to reduce the number of results. Alternately, you can implement some type of pagination to view all the results. Note that, as of July 2019, certain endpoints use cursor-based pagination, which unfortunately for us returns page info in the header response rather than the response output that gets printed into the sheet.

    One workaround is to use the since_id parameter. As this post by Sudip Bishwakarma points out, be sure to use since_id=0 for the first page in order to sort via id, and then iterate additional requests using the last id from the previous request to the next. You can base your API URL on the value in a cell to set this ID dynamically, so you don’t need to update your requests as the ID value changes. Example:
    Page 1: https://your_shop_name.myshopify.com/admin/api/2019-10/orders.json?status=any&since_id=0
    Page 2: https://your_shop_name.com/admin/api/2019-10/orders.json?status=any&since_id=+++Shopify_orders!A51+++

Comments:15

  1. Hi
    I think I have followed your guide all the way, but I get a error: [API] Invalid API key or access token (unrecognized login or wrong password)

    Reply

    1. Hey Morten, that means you’re connected and just have the wrong API key. Are you sure you copied in the right value? If you share your sheet with me I’ll be happy to take a look.

      Reply

  2. Hi Ana,
    Thanks for this amazing post and add-on. It’s been really useful for me since I’ve downloaded it 🙂

    I’ve a question about Shopify and the GET orders API that you’re talking at the end of your article. I’ve found out that if the order has multiple products, there would be still one line displayed that is showing just 1 product. But If I do the same API on a specific order (/admin/api/#{api_version}/orders.json?ids=XXXX), all product appears on different lines.

    Any thoughts about that issue? 🙂

    Reply

    1. Hey Dimitri, thanks for the comment, glad you’re enjoying the add-on 🙂

      The format of the returned data depends on the underlying JSON response produced by Shopify. JSON responses aren’t specifically designed for spreadsheets, so API Connector applies some logic to convert them into a tabular format, but depending on the underlying JSON, this might result in mismatched rows. I’m working on a new feature where you’ll be able to try out different conversion algorithms and pick the one that works best, so that should help address any issues you’re having. You can see more about that here: Report Styles

      Reply

  3. Hi Ana,

    Great tool and article! Am wondering how I can remove some of the columns? I actually only need to use approx. 3 of them. Thoughts?

    Reply

    1. Awesome, glad you like it!
      API Connector displays everything that gets returned by the API. If you only need some of the columns, I suggest creating a second sheet that queries for the columns you need. For example, the following function would return columns A, B, and D from a sheet named ‘ShopifyData’.
      =QUERY(ShopifyData!A:Z, “select A, B, D”)

      Reply

    1. Try re-entering the URL exactly as written, without any spaces or other characters. You can also try entering your request to another API tool like https://inspector.swagger.io/builder, to help troubleshoot where the error comes from.

      Also, you should enter your password in the header field as described in the documentation above, not by prepending username:password@ to the hostname in the URL.

      If you’re still having issues, feel free to send over some screenshots to info@mixedanalytics.com and I’ll take a look.

      Reply

      1. Hi Ana,
        financial_status is not working for me 🙁 I can call orders with “orders.json?status=any” but not “orders.json?financial_status=paid”…. it shows “Request processed, no records found”…

      2. This is working as expected — it simply means that there are no records matching your condition, i.e. no records have a financial status of ‘paid’.
        You can see this for yourself by checking the list returned by “orders.json?status=any”. Look for the column titled orders » financial_status (for me it is column Q). Based on your results, I suspect this column won’t contain any values of “paid”.

    1. The key isn’t your API key, it is the exact text listed in the article (“X-Shopify-Access-Token”, without the quotation marks).

      Reply

Leave a Reply

Your email address will not be published.