Import Shopify Data to Google Sheets – Step by Step Guide

Import Shopify Data to Google Sheets – Step by Step Guide

Last Updated On May 27, 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 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 as we’ll need it shortly. (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


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

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 ‘Shopify’. While still in that tab, click ‘Set’ to use that tab as your data destination.
  5. Name your request. Again we’ll call it ‘Shopify’
  6. Click Run and a moment later you’ll see your Shopify data populate the Shopify tab in your Google Sheet:
    shopify-api-img10
  7. 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 as described in the documentation.

Following the above steps, you will now be able to import data from the Shopify API directly into Google Sheets. By setting Google Sheets as a data source, you can also use this method to create Shopify data visualizations and dashboards in Google Data Studio.

Comments:0

Leave a Reply

Your email address will not be published.