Import Shopify Data to Google Sheets

Import Shopify Data to Google Sheets

Last Updated On August 16, 2019
You are here:
< Back

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 key 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 KEY

  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 API key and password are now ready. (For our purposes you can ignore the Shared Secret value).
    shopify-api-img6
  7. One last step: Basic Access Authentication requires us to encode the API Key and password to base 64. You can do this by clicking Ctrl-J or F12 in your browser to open up Developer Tools. In the console, type in the following and click enter:
    Substitute the API Key and Password values from step 6 where it says {API key} and {Password}.  It should look like this (don’t forget the plus sign in the middle):
    shopify-api-img8

    Copy the output that appears in Developer Tools to your clipboard, excluding the quotation marks at the beginning and end of the string. (If you have any problems with the above, you can also try a tool like Basic Authentication Header Generator to generate your header.)

  8. Congrats, you’re done! You now have access to the Shopify API, and can start pulling Shopify data into Google Sheets.

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://mixedshop123.myshopify.com

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

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

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 a key-value pair like this:
    Authorization {your encoded value from Step 7 above}

    It should look like this:
    shopify-api-img9

  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-img10

PART 4: NOTES AND EXPANSIONS

  1. If you want to see other Shopify data, like a list of orders, you’d just change the endpoint from /admin/api/2019-04/products.json to /admin/api/2019-04/orders.json?status=any as described in the documentation.
    shopify-api-img11
  2. By default, Shopify only returns orders from the last 60 days. Therefore it may be useful to append new data into your Google Sheet rather than overwrite your old data.
  3. 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-04/orders.json?status=any&limit=250. You can also use date-based parameters, e.g. /admin/api/2019-04/orders.json?status=any&limit=250&processed_at_min=2019-04-25T16:15:47-04:00. Note that, as of July 2019, certain endpoints use cursor-based pagination.

Comments:6

  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)

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

  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? 🙂

    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

  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?

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

Leave a Reply

Your email address will not be published.