Import Shopify Data to Google Sheets

Last Updated On
byAna
< Knowledge Base

In this guide, we’ll walk through how to pull data from the Shopify API directly into Google Sheets, using the API Connector add-on for Sheets. We’ll first get an API password from Shopify, and then set up a request to pull in data 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).
    shopify-api-img4
  5. Select the data points you’d like to have access to via the API (check the documentation on scopes for more information). Confusingly / unfortunately, selecting Reports access does NOT give access to the reports in your Shopify dashboard, it only allows you to create new reports that can then be accessed in the interface. Read_analytics also doesn’t help here as it requires an additional SDK for access.

    Most likely, you will want read_orders and read_products access for data about your orders and products.
    shopify-api-img4b

  6. Click save. You’ll see a verification modal. Click “Create app” to continue.
    shopify-api-img5
  7. 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 SHOPIFY API REQUEST URL

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

  • Base URL: https://YOUR_SHOP_NAME.myshopify.com
    Base URL Example: https://mixedshoptest.myshopify.com
  • Endpoint: /admin/api/VERSION/products.json
    Endpoint Example: /admin/api/2020-07/products.json

Putting it all together, we get the full API Request URL:

https://mixedshoptest.myshopify.com/admin/api/2020-07/products.json

PART 3: PULL SHOPIFY API DATA TO SHEETS

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 API password as the Value, like this:
    X-Shopify-Access-TokenYOUR_API_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: 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):
https://YOUR_SHOP_NAME.myshopify.com/admin/api/2020-07/products.json
https://YOUR_SHOP_NAME.myshopify.com/admin/api/2020-07/customers.json
https://YOUR_SHOP_NAME.myshopify.com/admin/api/2020-07/countries.json
https://YOUR_SHOP_NAME.myshopify.com/admin/api/2020-07/policies.json https://YOUR_SHOP_NAME.myshopify.com/admin/api/2020-07/orders.json?status=any (include ?status=any to retrieve all records)
shopify-api-img10

PART 5: HANDLE PAGINATION

  1. By default, Shopify limits the number of responses returned at once, usually to 50 records at a time.
    shopify-api-img13To get more, use the ‘limit’ parameter, like this: 

    https://YOUR_SHOP_NAME.myshopify.com/admin/api/2020-07/orders.json?status=any&limit=250

    You can also combine this with date-based parameters:

    https://YOUR_SHOP_NAME.myshopify.com/admin/api/2020-07/orders.json?status=any&limit=250&created_at_min=2019-10-25T16:15:47-04:00
  2. 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, or changing your date range to retrieve additional sets of data. Alternately, you can loop through pages automatically with pagination handling (paid feature). Shopify’s documentation shows that next page URLs are provided in a field called “Link”, so you would paginate through responses like this:
    shopify-api-img12

PART 6: HANDLE FILTERING

  1. Shopify returns a huge amount of data from their orders endpoint, often as many as 100 distinct columns. Besides being more data than you need, this slows down your report and can cause your requests to time out due to Google Sheets’ 6-minute response time limit. Therefore, it can often be helpful to pare down the fields you request, which you can do with the fields parameter, like this:
    https://YOUR_SHOP_NAME.myshopify.com/admin/api/2020-07/orders.json?status=any&limit=250&fields=id,email,created_at,total_price,financial_status,order_number,line_items

    “line_items” alone produces a lot of columns, so if you don’t need that level of detail, just leave it out.

  2. In addition to Shopify’s field filtering, API Connector provides its own filtering functionality. JMESPath filtering (paid feature) lets you specify exactly which fields to pull. This is especially useful for Shopify, because Shopify only lets you drill down to one level. In other words, you can filter for fields=line_items but not query for specific elements like product name and price within the “line_items” array. With JMESPath filtering, you can create a filter for just product name and price, like this:
    orders[*].{name:line_items[*].name, price:line_items[*].price}

    If you’re using the free version of API Connector, you can achieve something similar by creating a second sheet and using a function like this to grab just the columns you need, though only after you’ve already retrieved the original data set:

    =QUERY(ShopifyData!A:Z, "select A, B, C")
  3. Both filtering types can be used together. Shopify’s native filtering lets you request less data from the server, which speeds up response time from Shopify. API Connector’s JMESPath filtering ony filters the data once the response has been received, which gives you better control about exactly which data gets displayed and speeds up the process of printing data into the sheet.  

PART 7: GET THE SHOPIFY REPORT TEMPLATE

The Shopify report template is a simple report containing a list of key transaction metrics. It accelerates the process of getting started, since you can simply enter your own Shopify password and store name to populate the report with your own data. It also allows for some easy customization of fields.

shopify-api-img14

Click this link to download the template for yourself: Shopify Report Template

APPENDIX: VARIATION – USE GRAPHQL

The above tutorial uses the REST API because it’s easier to get started with. Shopify also provides a GraphQL API, which allows more specificity in terms of the fields that you retrieve. If you are more technical (or willing to learn), and interested in working with Shopify’s GraphQL API, this is the summary of how to use it:

  1. Create your graphQL query and enter it into a cell, let’s say cell A1 of a sheet called Input. Here is an example of a valid graphQL query:
    query myQuery {
 orders(first: 50) {
  edges {
   cursor
   node {
    createdAt
    id
    name
    email
    displayFulfillmentStatus
    lineItems(first: 10) {
     edges {
      node {
       quantity
       title
      }
     }
    }
   }
  }
  pageInfo {
   hasNextPage
  }
 }
}
  2. Encode the whole block by creating a new cell with this formula: =ENCODEURL(A1)
  3. Back in the API Connector sidebar, choose the POST method.
  4. Enter a request URL that references your encoded cell, like this: https://YOUR_SHOP_NAME.myshopify.com/admin/api/2020-04/graphql.json?query=+++Input!A3+++
  5. In the POST body, enter {} (we don’t need a POST body here but it can’t be empty)
  6. Under Headers, enter the following:
    X-Shopify-Access-TokenYOUR_API_PASSWORD
    Content-typeapplication/json
  7. Click Run to see the results in your sheet. (Note that API Connector’s pagination handling will not work here since GraphQL uses a different method of pagination, described here).

43 thoughts on “Import Shopify Data to Google Sheets”

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

    • 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

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

    • 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

    • 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 support@mixedanalytics.com and I’ll take a look.

      Reply

      • I have a similar issue. I’m getting “Request failed: Attribute provided with invalid value: Header:X-Shopify-Access-Token”

      • Can you please double-check that there are no extra spaces before or after the “X-Shopify-Access-Token” text?

  6. Is there a way for us to pull a specific report using Shopify API like below?
    GET /admin/api/2019-10/reports/#{report_id}.json
    I keep getting error message (Bad Request)

    Reply

  8. Hi, im trying to follow steps above, as a result, I got error showing —-
    “Failed to run request
    ScriptError: Authorization is required to perform that action.”

    how to get authorized? thanks..

    Reply

  10. Hello! I want to get title of line items per order (orders » line_items » 1 » title)
    1- Can i customize the properties? (orders.json?fields=line_items)
    2- I have alignment problem with line items when the rows appended with every query. (Because every order has different piece of items) Cell values is not at the exact column, they are floating.
    How can i handle this? Regards

    Reply

    • Hey Burak, you can choose which fields you want to filter on like this: https://your_shop.myshopify.com/admin/api/2020-01/orders.json?limit=5&fields=id,email,amount, line_items. You can’t further filter for sub-fields, though. Does that answer your question?
      Append mode just adds on to your sheet, so unfortunately it won’t always align if the API isn’t sending values across consistently. In those cases I suggest filtering for the fields like above, to retrieve just the fields that appear every time. Automatic pagination handling (paid feature) would also resolve this as it matches headers across requests.

      Reply

  12. Hi! I can’t get pagination to work (and it’s the feature we mainly need ). The link to Shopify tutorials provided says that “REST endpoints support cursor-based pagination” while here “next page URL” is used. Any ideas? Much obliged..

    Reply

    • Hey there, sorry you’re having trouble. What happens when you enter it in as shown in the screenshot?
      As for the difference in names, the trouble is that “cursor-based pagination” is a rather broad term. Most “cursor-based” methods provide a token from which to start the subsequent request, but Shopify provides the full next page URL. Therefore I’ve included Shopify as a “next page URL” type (documentation). You should be able to achieve pagination by using the word “link” as shown in the screenshot. If you still can’t get it to work, please feel free to shoot over an email to support@mixedanalytics.com so I can take a look.

      Reply

      • Thank you so much for your fast reply! Went through it again, apparently I used a version that did not support the endpoint I was querying. I’m getting rows for next pages now, at least on most of the times I run the same request, even though there’s a “Failed to run request” message each time. Going forward with this for now, thank you!

  13. Hi, Thank you so much for this, how can I do a cron job where every new order I get on Shopify can be automatically fetched in Google Sheets

    Reply

    • Hi Sahil, the paid version of API Connector has scheduling functionality. Otherwise you can just create a request URL with time-based parameters, and manually refresh every day or every few hours.

      Reply

  14. Can the order line items be returned in separate lines? Current request returns all items in an order in the one row

    Reply

      • This does make sense, but it’s due to the nature of the underlying JSON. JSON isn’t flat like Sheets data, it’s more like a tree with nested values. So in this case Shopify returns a parent order ID, and then nests all the products associated with that order ID into an array. Since API Connector just returns the data from the API, our options are to either flatten out all the product IDs into separate columns (that’s the default approach), or just list out those product IDs and break the association with the parent order ID. This article explains the issue in more detail: Report Styles.

  15. Hi Ana,

    Trying to pull in inventory level info:

    Using this URL path: /admin/api/2020-04/inventory_levels.json and running into an error with the key. Using this key X-Shopify-Access-Token – but not sure if it should be different. Any advice would be super helpful!

    Thanks,

    Reply

  16. Hi,

    This is super helpful. I am a total noob when it comes to API queries but I managed to create a Google sheet using this addon to fetch all the abandoned checkouts. I would like to see only yesterday’s data though without hard-coding the date.

    My query looks like this:
    https://headphone-zone.myshopify.com/admin/api/2020-04/checkouts.json?status=any&limit=250&created_at_min=2020-06-30T00:00:00+05:30

    How can I change this to only get yesterday’s data?

    Alternatively, can you help me with the pagination parameters so I get more than 250 rows and can further filter the date on Google Data Studio.

    Reply

    • Hey, glad it’s helpful and you got some data. To get yesterday’s data without hard-coding the date, you can make a formula like this: =text(today()-1,"yyyy-mm-dd")  
        Then, if that formula is in cell A1 of a Sheet called Inputs, you can reference that cell like this:   

      https://headphone-zone.myshopify.com/admin/api/2020-04/checkouts.json?status=any&limit=250&created_at_min=+++Inputs!A1+++

      This way it will automatically update each day and pull in just data from the prior day.
      For pagination, there's a screenshot in the Pagination section. Basically, you need to select 'next page URL' and enter a Field name of Link.

      Reply

  17. I keep getting

    Request failed: Server responded with an error (404) show response
    {“errors”:”Not Found”}

    I tried to follow the other common comments but that did not work.

    Reply

Leave a Comment