Search API Connector Documentation

Print

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.

The nice thing about this method is that you can access the entire set of functionality available via Shopify's API, from pulling in transactions to updating product specs.

Contents

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

Part 1: Get Your Shopify Admin Access Token

  1. Log into your Shopify store and click Apps
    shopify-apikey-apps
  2. From here, click Develop apps
    shopify-apikey-developapps
  3. From here, click the Create an app button
    shopify-apikey-create
  4. You'll see a modal prompting you to name your app and assign an app developer. Give it a name and click Create app.
    shopify-apikey-create-confirm
  5. You'll now be on your app overview screen. Click Configure Admin API scopes.shopify-apikey-configure
  6. The next section is where you search for and select which data points you'd like to have access to via the API. Most likely, you will want access to at least one or more of the following: read_orders, read_products, read_inventory, and read_customers.
    shopify-apikey-configure-scopes
  7. Once you've selected all the scopes you want to access, click Save. (You can add more scopes later if you need to)
    shopify-apikey-configure-scopes-save
  8. Head over to the API credentials tab and click Install app.
    shopify-apikey-configure-install
  9. Confirm the installation and you should now see a screen containing your Admin API access token. Click Reveal token once, and store it safely as we'll need it soon. Congrats, you’re done! You now have access to the Shopify API.
    shopify-apikey-token

Part 2: Pull Data from Shopify to Sheets

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

  1. Select Shopify from the drop-down list of applications
    shopify-application
  2. Under Authorization, enter your API token
    shopify-authorization
  3. Select an endpoint. We’ll start by pulling in a simple orders report
    shopify-endpoints
  4. Under the shop parameter, enter your shop name. For example, if your admin URL is https://oj-stand.myshopify.com/admin, your shop name is oj-stand. You can also find your shop name by navigating to your store page, right-clicking View Page Source and searching for "Shopify.shop" in the HTML source code.
  5. Set a destination sheet, name your request, and click Run to see response data in your sheet.
    shopify-response

Part 3: Create a Custom 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.

To create a custom request, enter the complete URL into the request URL field and include a header of Key = X-Shopify-Access-Token, Value = your token
shopify-customresponse

To easily convert from a preset request to a custom API URL, tick the Add request URL box before running your preset request (under Output options). This will print out your complete API URL, which you can then copy/paste into the request URL field.

Part 4: Handle Pagination

  1. By default, Shopify limits the number of responses returned at once, usually to 50 records at a time.
    shopify-api-img13
    To get more, set the limit parameter to 250. Fetch additional data by using the "since_id" or date-based parameters.

  2. Loop through and grab all your data automatically with pagination handling (paid feature), like this:
    • Pagination type: next page URL
    • Next page path: Link
    • Run until: choose when to stop fetching data

      pagination-nextpageurl-Link

Part 5: Get New Data Only

Rather than retrieving the entire data set each time you run your request, you can set your request to fetch new data only. There are a few approaches you could take; here’s one:

  1. For this example, we’ll create a custom request. As mentioned above, if you're starting from a preset request you can convert it into a custom request by ticking the Add Request URL box.
  2. Run an initial request for the dates and fields you want to start with, e.g. https://shop.myshopify.com/admin/api/2022-07/orders.json?status=any&fields=id, order_number,created_at,email,total_price,total_discounts,total_tax&created_at_min=2022-09-01&created_at_max=2022-09-30&limit=250
  3. Use the field editor to select just the fields you want and assign them to specific columns in your report.
  4. Create a new sheet called MaxId that contains the function =to_text(max(Orders!A:A)). Replace A:A with whichever column contains your order IDs.
    shopify-maxorderid
  5. Now reference that max ID cell in your request URL and pull in new data only with a custom request URL of https://shop.myshopify.com/admin/api/2022-07/orders.json?status=any&fields=id, order_number,created_at,email,total_price,total_discounts,total_tax&limit=250&since_id=+++MaxId!A1+++
    shopify-newdata
  6. This will ensure that each request starts from the end of the prior data pull. Select Append mode to add each new data pull to the end of your existing dataset.
  7. Set your request to run on a schedule. You won’t need to update your request again.

Part 6: API Documentation

Official documentation: https://shopify.dev/api/admin-rest

GraphQL guide: https://mixedanalytics.com/knowledge-base/import-shopify-graphql-data-to-google-sheets/

133 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 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, you can filter for the fields you need like this: &fields=id,email,created_at,total_price

      Reply

    • Please 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.

      If you're still having issues, feel free to send over some screenshots and I'll take a look.

      Reply

  4. Hi Ana,

    Thank you for this tool!

    I would like to get all of the unpaid order out of the system but I can't figure out how.

    I tried https://3d4makers-com.myshopify.com/admin/api/2019-04/orders.json?status=pending and https://3d4makers-com.myshopify.com/admin/api/2019-04/orders.json?status=unpaid but it doesn't work.

    Could you help me out?

    Reply

    • Hey Jasper, Shopify's documentation shows all the parameters you can use to retrieve different types of orders. 'status', 'financial_status', and 'fulfillment_status' may all be interesting to you, but it looks like https://3d4makers-com.myshopify.com/admin/api/2019-04/orders.json?financial_status=unpaid is the one you want.

      Reply

  5. I've entered :
    API URL path: https://myshopname.myshopify.com/admin/api/2019-07/products.json
    Key : myAPIkey
    Value : mypassword

    And I get :
    Request failed: Server responded with an error

    How can we fix this ?

    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

    • I checked online and see a lot of other people with similar questions (e.g. here). They don't make it easy, but in my own tests, I was able to recreate the Shopify Sales report by taking the sum of the total_price field from the /orders endpoint, and then subtracting out refunds (orders that were updated in that time range with a status of refunded, partially_refunded, or voided).

      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 (at least not using Shopify's native filters). Does that answer your question?
      Append mode just adds on to your sheet, so by default it won't always align if the API isn't sending values across consistently. In those cases I suggest filtering for just the fields you need and using the field editor to "lock" columns into place.

      Reply

  11. Hi,
    I found your plugin through your another post and in need of something like that as well. I have tried same as what you have explained.

    https://mystore.myshopify.com/admin/api/2020-01/customers.json

    Key: X-Shopify-Access-Token
    Value: password of that API Key

    Am getting 403 Error. Any idea why it's happening? Any extra access should I have?

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

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

      Reply

  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

      • Hi Ana,

        The API connector + your amazing support is the perfect combo. I found the answer to a lot of my questions by reading previous comments.

        I would have a question similar to Kamna's. But instead of getting data from yesterday, is it possible to only get data from past hour?

        I am currently using the append option so I can keep past data. By implementing past hour data then we can surely be under the 250 limit + it would be amazing to have it updated hourly.

        Thank you in advance,
        -Ionut

      • Thanks! Sure, if you want hourly data you'd just adjust your date parameters, e.g. created_at_min=2021-01-01T16:00:00&created_at_max=2021-01-01T17:00:00.
        As you saw, you can reference a date cell so that your created_at_max date uses a formula like =NOW(), and created_at_min is =NOW()-(1/24) (to subtract one hour).
        However there's not really a 250 record limit, you just need to apply pagination handling to get more, which is really easy. So you could also just do this once a day or at whatever frequency makes sense for your business.

  17. Thanks for the great product and tutorial! I am successfully importing data from Shopify into Google Sheets using the API Connector. However, each time I run the automation, there are a different number of columns imported into Sheets. Any idea why this is happening?

    Thanks!
    Jason

    Reply

    • Hey Jason, Shopify switches around columns based on which fields contain data. It's related to the fact that they send back such a huge number of fields by default. To address, you can use filters to reduce the columns to just the data that gets populated during each call, you can use JMESPath filtering, or you can use a separate Sheets function to pull out columns and line them up in a second sheet. You can see more information about this issue here.

      Reply

  18. Hi Ana,
    really interesting tool! I'm trying to pull our products from Shopify to implement them into a Google Sheet that feeds our Facebook Catalog. The categories "id/title" and so on differ between Facebook and Shopify so I'd like to give them different names. Would it help to use the QUERY function to select cells to copy under my preferred headlines or what would you advise me?

    Reply

    • Hey Tim! You could add in your own headers, and then query all the data to a new sheet starting in row 2, but there's a risk that columns could shift around between data pulls and your new header names wouldn't match up with the correct fields. Therefore, I suggest one of the following instead:
      1) query individual columns by name into a second sheet using this Sheets’ function, replacing ‘data’ with your sheet name, and ‘field_name’ with your field name:
      =QUERY(data!$1:$10000,"SELECT "&SUBSTITUTE(ADDRESS(1,MATCH("field_name",data!$1:$1,0),4),1,""),1)
      2) JMESPath filtering allows you to choose and rename your fields all in one go.
      Update: you can now use API Connector's visual field editor to easily select and rename your columns.

      Reply

  19. Hey Ana, This was super helpful & I got this to work for the products.json. However when i try for the Product_listings.json i am seeing an error.

    Here is the URL - https://mystore.myshopify.com/admin/api/2020-07/product_listings.json
    And this is what the error looks like - {"errors":"Not Found"}
    And inside the Google Sheet API Connector I am seeing an error for - Request failed: Server responded with an error (404) show response

    My end goal is to create a data feed in Google Sheets and the products.json doesnt have enough fields by itself to make that work.

    Reply

    • Hey Shaun, Shopify's productListing documentation says "The ProductListing resource is available to Sales Channel SDK applications only." Can you please check if you meet that qualification? You can also see what Shopify staff responded to someone with this question here.

      Reply

    • Hi Hardik,
      In theory, yes, you would just need to paginate through all your orders to import them. However, in practice I think you will have an issue avoiding Google Sheets' 6-minute timeout limit per request since Shopify limits you to 250 records per page. 80k orders / 250 = 320 pages, which is a lot of data for Sheets to handle at once, especially since Shopify sometimes returns hundreds of columns by default. So I'd suggest first doing a test run of just 10 records or so, to see which fields you really need. Then add filters to your request for just those fields, and run a paginated pull to loop through and pull your records into a single sheet.  You might need to split it into multiple runs and combine them at the end. 
      Another possibility is to just do a csv export of your old orders, and use the API for new orders only.
      Either way, the good news is you'd only have to do that process once, since going forward you could set your date parameters to automatically update and only fetch new data.

      Reply

  21. Hey Ana,

    Great product! I've been testing it and so far everything has been working out very well. I'm very new to API's and queries but managed to get the hang of the basic things.

    I just had one question (note also that I am using JMESPath to query specific columns in my data):

    Is it possible to reverse the order of the output? Currently the output data is in newest descending down to oldest date. Is it possible to reverse this output to oldest descending down to newest date?

    Reply

    • Hi Long, thank you for the message, I'm glad you like API Connector 🙂

      For your question, you can reverse the sort order by appending &order=updated_at asc to your query so your whole URL looks like this:
      https://YOUR_SHOP.myshopify.com/admin/api/2020-10/orders.json?status=any&order=updated_at asc

      Another option is to choose your min/max dates, and then sort it yourself in Sheets:
      https://YOUR_SHOP.myshopify.com/admin/api/2020-10/orders.json?status=any&updated_at_min=2020-09-01&updated_at_max=2020-09-21

      Reply

  22. Hello,

    Is it possible to have an inventory of products on google sheets which is linked to Shopify and that syncs the infomation so that if I wanted to add a product or delete a product it would change on the shopify website after I saved the sheet?

    Thanks,
    Karl

    Reply

    • Key Karl, I think you'd need some custom code or a custom Shopify tool for that. API Connector lets you connect and send requests to the API but you need to specify exactly what those requests are. Shopify's API has different methods and endpoints for adding and deleting products (documentation: https://shopify.dev/docs/admin-api/rest/reference/products/product), so you'd need some code to identify whether you wanted to send a DELETE request or a CREATE request. Let me know if that makes sense.

      Reply

    • Hi Jyoti, if you just want sales data from the API you can follow the instructions in this article. If you're looking to exactly match the Sales report, it's a bit harder. There is no specific endpoint to pull in your Sales report data via the API, you'd need to use the /orders endpoint described in this article and do your own filtering and aggregation.

      Shopify's documentation regarding the Sales report says that Total sales = gross sales - discounts - returns + taxes + shipping charges.

      So based on that I was able to match the Sales report by first summing the the total_price field from the /orders endpoint (total_price already accounts for discounts, tax, and shipping) for a specific time period defined with the created_at_min and created_at_max parameters. I then subtracted out returns by running a second query to the /orders endpoint, this time adding &financial_status=refunded,partially_refunded,voided and using updated_at instead of created_at. The date piece is key because Shopify calculates total sales by removing refunds that happened within your selected time period.

      Reply

  24. Hi

    In all our exports, we seem to be able to generate each order as an individual row. Or we have been able to get each individual line item as a separate row (however the Shopify order number wouldn't be available).

    When we try to use a query such as "dmin/api/2020-04/orders.json?fields=name,line_items", then the results in the first column are much fewer and dont really match with the data corresponding to the line_items columns (this happens as some orders could have multiple line items).

    Is there any way where we can have the order numbers repeat when the order has more than one line-item?

    Reply

  25. Hello,
    I've been able to succesfully integrate the steps you've desceribed above, thank you. However, i am looking for a way to display individual sales rather than entire orders. For a given order, I may have various sales of products. For a view of my sales i want to see each sale of product represent its own line item. I was unable to find a URL retriaval in shopify documentation. A basic example of what I'm looking for is provided below.

    Date Order # Product (all remaining column headings...)
    12/20/2020 #1001 Product A
    12/20/2020 #1001 Product B
    12/21/2020 #1002 Porduct A

    Reply

    • Hey Blake, please try switching your report style to grid mode, that will copy down the order ID for each product (info). If you combine that with filters, you should be able to get it exactly how you want.

      Reply

    • Ana - Based on your comments above, I was able to accomplish this using the "Grid" format. However, I am still trying to generate a Cost of Goods Sold (COGS) column. I notice in shopify that you can plug in the cost per item for each product. Where is this data captured in the API retrieval?

      Thanks

      Reply

      • Ana, I really appreciate your responsiveness. I copied in the "/admin/api/2020-10/inventory_items.json?ids=808950810,39072856,457924702" endpoint into my connector and it said that no information was available. Are the given numbers arbitrary examples of inventory ID's? If so, I couldn't find a retrieval URL that simply lists all inventory rather then this one that desginates each specific ID. My goal is to automate a growing list of inventory items (which show COGS), rather then search for specific ones. Let me know if this makes sense. Thank you again!

      • You're welcome! Yes, those are just example numbers representing inventory IDs. It doesn't look like Shopify provides an endpoint containing all inventory IDs, so you'll need to pass those IDs in yourself. It will take a series of requests, and a bit of finesse with Sheets functions. The following should work:
        1) make a request to the /orders endpoint to get your orders.
        2) make a request to the /products endpoint to get a list of product IDs and their associated inventory IDs.
        3) make a request to the /inventory endpoint to get cost for each of those inventory IDs.
        Then you'd combine it all back together using Sheets formulas like VLOOKUP or QUERY.

        It's not that simple, but that's how Shopify has designed their API. For more detailed advice, I suggest checking their docs or their community forums (there are relevant posts here, here, here, etc.)

      • Ana, this seriously has been so hopeful, your directions have assisted me at each request. There are always more questions! I hope someone has the same issues like myself, therefore this feed will be very helpful haha. But I was wondering if it was possible to pull the data vertically in chronological order with the orders end point. Currently, my orders are retrieved from Shopify and they populate from newest to oldest (top to bottom). So the most recent items are populating in the top rows beneath the headers. Rather, would it be possible to pull the data oldest to newest? That way new sales are placed at the newest blank row at the bottom of the data list.

      • Hey Blake, thank you for your nice comments, I'm glad this has been helpful! If you want to change the sort order you would add "&order=updated_at asc" to the end of your URL, e.g. https://domain.myshopify.com/admin/api/2020-10/orders.json?status=any&limit=250&fields=id,email,created_at,total_price,financial_status,order_number&updated_at_min=2020-01-01&updated_at_max=2020-12-24&order=updated_at asc

    • If you're using Shopify's native filtering you'd add a "fields" parameter like https://domain.myshopify.com/admin/api/2020-10/orders.json?status=any&fields=billing_address. That will get you all the data within the billing address object (including name, address, phone, city, zip, province, country, and latitude/longitude) since Shopify doesn't let you filter more granularly than that.

      If you want to use API Connector's JMESPath filtering to target "city" only, you'd add a JMESPath query like this:
      orders[*].{city:billing_address.city}

      You can use one or both types of filtering. Using them together provides the fastest response time.

      Reply

  27. Hey Ana,

    Thank you for your help so far. Everything worked properly with my addon up until now (I use it to get hourly orders & data exports from Shopify to google Excel).

    The issue I seem to be experiencing now is the fact that a few inputs are being extracted normally, then for whatever reason something breaks and it imports data on different header titles.

    I use Append as an option and for example. If usually the "Date created at comes in column D", after a few inputs it will break and it will be imported in column T for example. This will automatically break all my other formulas.

    I checked it with multiple stores api's and it seems to be the addon issue I assume? I experience the same error with all the stores.

    Could you perhaps guide me into how to fix that?

    Hope that my message is clear,
    Thanks
    -Ionut

    Reply

    • Hi Ionut, API Connector returns data as it comes back from the API. In this case, Shopify's API sends fields in a different order based on which fields contain data. For example, if you're pulling orders and order #1 has one line item, while order #2 has two line items, you will end up with more columns for order #2 than order #1. Shopify sends back a huge number of fields by default, so there are a lot of fields that may be different between pulls. To address this you can try any or all of the following:
      1) use Shopify field filters to reduce the columns to just the data that gets populated during each call
      2) use a JMESPath expression to identify which fields you want and their location in the sheet
      3) switch to grid mode so values populate down instead of across
      4) use a separate Sheets function to pull out columns and line them up in a second sheet

      Update: You can now use API Connector's visual field editor to easily select fields and fix columns in place.

      Reply

  28. Can the API Connector do a two way sync?

    If I modify the address once the data has been downloaded to the Googhe Sheet, I would like for the new modified address to be corrected in Shopify. How can I do that?

    Reply

    • Hey John, sorry, API Connector doesn't support live updates since it works by reaching out for data rather than listening for data pushes. At best it could reach out every hour for fresh data.

      Reply

  30. Hi There,

    I love this tool and use it a lot. One thing I am struggling with is returning a list of orders of specified product(s). Is this possible?

    Reply

    • Thanks Adam! Shopify's API doesn't let you filter the orders endpoint for a specific product. So I think you have the following options:

      --Pull in all the data and use Google Sheets' filter or QUERY functions to pull just the data you want into a second sheet
      --Use API Connector's JMESPath filter function to filter the data. In that case you'd set it up like this:
      URL: https://your-site.myshopify.com/admin/api/2021-01/orders.json?limit=250
      JMESPath: orders[].{id:id,email:email,created_at:created_at,total_price:total_price,subtotal_price:subtotal_price,name:name,line_items:line_items[?title=='Your Product Name'].{title:title,sku:sku,quantity:quantity,product_id:product_id,price:price,total_discount:total_discount}}

      The specific JMESPath expression would vary based on the fields you want to pull back but that's the basic syntax of how you'd set it up.

      Update: you can now also easily filter specific columns with API Connector's visual field editor.

      Reply

      • Thanks so much for the help. The 250 limit makes this very tough, as our volume of orders is quite high and the data I need would be incomplete.

        Another option I have (as the data I am trying to pull has these parameters) is to get BOTH fulfillment_status=unfulfilled AND order status=refunded? I have been able to do one or the other but I haven't been able to include both in a single query.

      • You can check this section for information on getting more than 250 records.

        For your request URL, does this work?
        https://your-site.myshopify.com/admin/api/2021-04/orders.json?status=any&limit=250&fulfillment_status=unfulfilled&financial_status=refunded

  31. Hey Ana, this is awesome thank you!

    I just had a quick question.

    I have been able to successfully pull data from shopify into Google sheets and the request is scheduled to run every hour. The only thing I want to do now is be able to pull only the past hour's worth of data into the sheet. That way the data comes into the sheet and appends to the existing data every hour.

    I tried to follow the instructions on your post here: https://mixedanalytics.com/knowledge-base/api-connector-create-api-request-based-on-cell/

    I created a new tab called Inputs and used the =NOW() function and formatted the dates into the correct format. I think my issue is within the parameters I'm adding to my URL:

    /admin/api/2021-07/orders.json?status=any/time_entries?from=+++Inputs!B2+++&to=+++Inputs!B3+++&limit=250&fields=id,created_at,total_price,financial_status&financial_status=paid

    When I run the request, the data comes into the sheet but doesn't follow the time limits that I want to set.

    Let me know if you need some more information from my side. I'd appreciate your help.

    Thanks,

    Jordan

    Reply

    • Hey Jordan, this part of your URL has invalid syntax (two question marks and a slash): /admin/api/2021-07/orders.json?status=any/time_entries?
      Also, Shopify's API doesn't use "from" and "to" as date parameters. You can see all the date parameters they allow here.

      You should be able to make this work with the following setup:
      1) create a request URL that references an Inputs tab like this:
      /admin/api/2021-07/orders.json?status=any&limit=250&created_at_min=+++Inputs!A1+++&created_at_max=+++Inputs!A2+++&fields=id,created_at,total_price,financial_status
      2) Then in your Inputs tab, you'd need to create your timestamps in the format required by Shopify (ISO-8601). So to get the current time it should be something like =text(today(),"yyyy-mm-dd")&"T"&text(now(),"hh:mm:ss") , and then you need to subtract an hour from that with something like =text(today(),"yyyy-mm-dd")&"T"&text(now()-TIME(1,0,0),"hh:mm:ss") to get your prior time

      Now your created_at_min and created_at_max should dynamically update, and you can grab those new orders every hour.
      Hope that helps, let me know how it goes.

      Reply

      • Hey Ana, thank you for your reply I appreciate it!

        I updated my Inputs tab with those formulas and updated my URL to reference the Inputs tab.
        I ran the request and received the error message: Request processed, no records found

        Here are screenshots of my Inputs tab: https://drive.google.com/file/d/1fRWcnSal8oytMpor1lwqS-zmg87D5Jo-/view?usp=sharing
        https://drive.google.com/file/d/1mOnINf9AY1aJlUiyid25O4MiQg2IYxi6/view?usp=sharing

        This is my URL: /admin/api/2021-07/orders.json?status=any&limit=250&created_at_min=+++Inputs!B2+++&created_at_max=+++Inputs!B3+++&fields=id,created_at,total_price,financial_status

        Let me know if you need any more information from my side. Thank you.

        Jordan

      • That all looks fine to me, and I tested and confirmed it works in my own account as written.
        Are you sure you had orders during that time? Maybe it's related to time zones?
        To troubleshoot, I'd try hard coding in some dates and testing that it works before using the Inputs reference, then you can identify whether the issue is with time zones, formulas, or something else.

  32. Hi Ana

    I am trying to build out a report from shopify to google sheets. The issue I am having is that only 50 orders from today are pulling through and I want all my orders to come through ever. Which would be about 8000 orders so far. I tried using this extension on the url ?status=any and it didn't help, Any advice.

    Reply

    • Hey Melissa, by default Shopify only returns 50 records at a time. To get more you can add &limit=250 to the end of your URL, and to get more than 250 you can change your date ranges or set up pagination handling. Please check this section for more info.

      Reply

      • Hi Ana, thanks for the reply what is the easiest way for me to view say at least 1000 orders at once?

      • The easiest is to turn on automatic pagination handling using the "next page URL" field. You can see instructions and a screenshot of how to set it up here (check the 3rd bullet). Then you just set it to fetch 4 or more pages, 250 records at a time.

  33. Hey Ana, I tried playing around with the formula and the dates and it seems like the request runs successfully when I reference a cell with a static date (and the static date included the '+01:00' at the end - referring to the time zone).

    Could the issue be that the dynamic dates don't include the '+01:00' at the end?

    If this could be the issue, do you know how I can get the '+01:00' into the formula so that it shows at the end of the date? I tried using &"+01:00" but this didn't work.

    For example: 2021-07-14T11:00:00+01:00

    Thank you,

    Jordan

    Reply

    • I see, so it does seem like a time zone issue. In that case you'll need to add "+01:00" or just add an hour to your original date value.

      I'm not sure why &"+01:00" didn't work as that seems fine to me, can you please share the entire function you're using? Or feel free to send a message if you'd like me to check out your sheet.

      Reply

    • This will depend on what endpoint you're calling, but generally yes, you can make calls for specific products.
      For example, you can retrieve a list of specific products with a URL like https://YOUR_SHOP_NAME.myshopify.com/admin/api/2021-07/products.json?ids=632910392,921728736.
      You could even semi-automate it by using a Sheets function like =JOIN(",", A2:A100) to create a comma-separated list of all the SKUs in a column, and then referencing that cell in your API URL.

      Reply

  35. Hello team,
    Needed one help i am going to add products from the API i want to add one custom fields into products variant like custom_id and also want into the response is it possible to add this into variants details

    Reply

    • I think this is what you're looking for: https://shopify.dev/api/admin-rest/2021-10/resources/product-variant#%5Bpost%5D/admin/api/2021-10/products%2F%7Bproduct_id%7D%2Fvariants.json
      That link has an example called "Create a new product variant with a metafield", which says you'd set up the request like this:
      Method: POST
      URL: https://your-site.myshopify.com/admin/api/2021-10/products/632910392/variants.json
      Request Body: {"variant":{"option1":"Blue","metafields":[{"key":"new","value":"newvalue","value_type":"string","namespace":"global"}]}}

      Reply

      • @Ana,

        Thanks for reply but this will not work for me let me share some code format

        {"product":{"variants":[{"option1":"Normal","price":"292.37","custom_field":"my_id"},{"option1":"Foil","price":"438.56","custom_field":"my_id"}],"images":[{"src":"https://img.scryfall.com/cards/en/lea/275.jpg?20170330011327"}],"title":"Winter Orb","vendor":"9359f60c-9a27-4e53-b35b-964a121a6fba","body_html":"As long as Winter Orb is untapped, players can't untap more than one land during their untap steps.","tags":"Type_Singles, Limited Edition Alpha, set_Limited Edition Alpha, rare, rarity_rare, Artifact, Card Type_Artifact"}}

        This is my add products API i want this to pass custom_field into the products variant

      • Sorry, this is really getting into the details of Shopify's API, for this endpoint I only know what I see in their documentation so I can't really help much. I see other people reporting the same error in Shopify's support forums (e.g. here and here), so they might have some insight there.

  36. Hello team,
    i want to update products variants wise can you please help me out from this

    URL : admin/api/2021-07/variants/40993033060502.json

    {
    "variant": {
    "id": "40993033060502",
    "product_id": "7210975166614",
    "inventory_quantity": "20"
    }
    }

    Error : "error": "Write requests to inventory_quantity and inventory_quantity_adjustment are no longer supported. Please use the Inventory Levels API."

    Reply

  37. Greetings,

    I've had commands (that worked as recently as earlier today) stop working this evening. Based on some exploration I have narrowed it down to the pagination.

    Do you know if this changed with Shopify's new 2021-10 API?

    Previously, I was running the "next page URL" and "Link" version as described in your help section above. Now I get this error from API Connector: "Request failed: Cannot find function domain in object false."

    Removing pagination will get me the first page of responses.

    When I pull with curl, the header line looks like this (** replaces shop name and **** replaces a string of random characters that I'm hiding as I don't know if it contains access credentials):
    < link: <https://; rel="next"

    I don't know if this has changed in any major way since before today.

    Any thoughts?

    Reply

    • Hi Joy, I'm so sorry, this was a bug on our side related to an update we made yesterday.

      I've just rolled back the change that caused it so your request should be working now (please refresh your sheet to make sure you're running the latest version of API Connector).

      Thank you for letting me know, and I'm sorry again for the inconvenience. Just let me know if you're still having issues.

      Reply

      • Hi Ana,

        It works again! Thank you!

        Honestly, a bug on your end (though I know stressful for you) is about the best root cause I could have hoped for. I live in fear of the day Shopify totally updates or depreciates an API that I rely on. 🙂

        Thanks again for the prompt response and fix!
        Joy-El

  38. Hi Ana,
    Quick question i want to get the field "orders.fulfillments.1.tracking_company"

    But the dot seems to be an issue on the "field=" parameters

    Do you have any idea to solve this ?

    Thanks
    Paul

    Reply

    • Hey Paul, I think we messaged about this via email, but I'll put my response here too for reference. Shopify only lets us specify one level deep, so you can run "fields=fulfillments", but not "fields=fulfillments.tracking_company"

      Therefore, to reduce the number of columns, can you please do the following?
      1) switch to grid style (under Output Options) so nested data flows down instead of out
      2) At the bottom of your request click Edit fields
      3) In the field editor, click on each field you'd like to filter or keep in your sheet and hit Save.

      Reply

  39. Hi Ana,

    I'm trying to pull down data from Shopify in the grid view but when I change and request I get a blank sheet. Single Row works fine but nothing coming back for grid view.

    Any help would be massively appreciated 🙂

    Reply

    • It sounds like you may have saved a mapping in the field editor. Can you please click Edit Fields to open the field editor, and then Reset All to clear any mapping? Let me know if that resolves the issue.

      Reply

      • Thanks Ana,

        That's worked 🙂

        Only thing I'm seeing now is that the Grid view isn't breaking down the order line value, it's just repeating the overall order value. Is there anyway I can get this to show the individual line amounts?

        e.g
        Order Line 1 = $17
        Order Line 2 = $25.50

        I'm just seeing the sum of $42.50 on both lines

        Thanks again,

        Mark

    • I don't believe Shopify provides this information through the API (there's some discussion on it here). So you would need to use Google Analytics or similar tracking on your site to get your visit data.

      Reply

  42. It managed to get an order with a request key "created_at_min" whereas competitive app fails completely!

    One word about data: it interprets json data badly either in grid or row output style and as an example I used one order with 4 products from 2 vendors:

    - field "customer.created_at" repeats redundantly 4 times
    - field "customer.first_name" repeats redundantly 4 times etc.
    - field "fulfillments.id" gives 2 values correctly as the products come from 2 vendors
    - field "fulfillments.line_items.fulfillment_status" gives 4 values correctly as it refers to 4 products but one should not relate 1st value with 1st value given by previous "fulfillments.id" (user should be warned that same row values do not correlate in general)
    - field "fulfillments.line_items.name" gives 4 values correctly which do correlate with previous "fulfillments.line_items.fulfillment_status" etc.
    - field "shipping_address.first_name" repeats redundantly 4 times etc.

    I downloaded the json and checked it - there is no such redundancy so I infer it's an annoyance caused by the app.

    Reply

    • Thanks for the feedback but I think this may be working as designed. Please check this article on report styles, it explains why grid style repeats parent nodes. The idea is to, for example, let every product ID match up with its order ID in a table. (The single-row style also allows you to match up these IDs, but then each product occupies a new column.)
      With that said, I understand that this report style might not fit what you're looking for. I'd love to get your feedback in more detail so we can create a style that fits your use case, so if you have a moment please share your request URL and/or send a message to support so I can investigate further.

      Reply

  43. Hi Ana,
    Is it possible to get the Cost of Goods for ALL inventory items using the inventory_items endpoint instead of just with one id?
    Thanks!

    Reply

    • You can use multiple IDs, but you need to list them out explicitly, like this: https://your_shop.myshopify.com/admin/api/2022-07/inventory_items.json?ids=38852726947996,38247995703452
      To better automate this, you could do something like this
      1. run a request to the /products.json endpoint
      2. when you get the response, create a comma separated list of all the values in the variants.inventory_item_id field with a Sheets function like this: =join(",",I2:I100).
      3. now plug that comma-separated list into the /inventory_items.json request shown above. (Request URLs can only have about 2k characters, so you'll only be able to include ~100 IDs into your URL at one time.) The inventory_items.cost field in the response will contain the cost for each item in your list.

      Reply

  44. Hi Ana,
    This site is very helpful. Thank you. However, my analytics from shopify is not transferring to google sheets. I selected the "read_analytics" option on the shopify app under configuration, but API is reporting {"errors":"Not Found"}. This does not happen with orders or customers or products or anything else. I used the same URL as the others but just substituted "analytics": https://nogreaterlove-co.myshopify.com/admin/api/2022-04/analytics.json. Do you know what the issue may be? Thank you.

    Reply

    • Hey Gavin, the issue is that there is no /analytics endpoint. If you check the "Analytics" section in Shopify's docs, the closest you'll find is a /reports endpoint, but that just returns some metadata about your reports, not actual report data. Shopify doesn't have an API endpoint to return your analytics directly, instead you need to make requests to endpoints like /orders or /products and build new reports yourself with the data you retrieve.

      Reply

  45. Hi Ana!

    Thank you for this very helpful extension. I am specifically interested in the sales breakdown - is there a way for me to extract just the sales breakdown using your API connector?

    Thanks
    Chris

    Reply

    • Hey Chris, I'm not totally sure what you mean by sales breakdown, but you can retrieve any data available via the Shopify API.

      Reply

  46. Hi! Im using API Connector for the first time to get Shopify order reports, and i get this error:

    "1) : Completed with errors
    - Error DNS: https://https/arms-of-andes.myshopify.com/admin.myshopify.com/admin/api/2022-07/orders.json?status=any&fields=order_number%2Ccreated_at%2Cemail%2Ctotal_price%2Ctotal_discounts%2Ctotal_tax&limit=250"

    Could you please help me in this issue? Thank you so much, your extension is helping us a lot with our ads data

    Reply

    • Looks like you’ve entered an extra https in the beginning of your url, please try removing https from https/arms-of-andes

      Reply

  47. Hi, I am trying to import my Shopify orders to Google Sheets and once everything is done and I click "Run", I get the following message:

    Report Status:
    1) Pedidos: Completed with errors
    - Error SSL: https://iulam-joyas.myshopify.

    "Pedidos" is the name of my request.

    P.D.: I apologise for my English.

    Reply

  48. The problem is now solved. I hope not disturbing you too much from now on. Your answers on previous comments will help me.
    Many, many thanks.
    Ana

    Reply

  49. Hi there, I've spent a day trying to do this but I keep getting an error message:
    Attribute names
    Attribute names should follow the product data specification so that customers see your product listings when they search. Learn more about how attributes are used

    I'm not a developer and both shopify and google don't know how to help me. Any recommendations?

    Reply

  50. Hi there - like others leaving comments, I'm trying to calculate my stores total refunds. When I run the API connector report, I get over 100 columns containing "refund". Can you tell me which column contains the data that I'll need to sum in order to get a daily refunds number that will match what I see in Shopify? Feels like I'm close, but just not quite there! Appreciate the help...

    Reply

    • I think you're looking for the refunds.transactions.receipt.amount field. You can filter for this field with the field editor, or enter this JMESPath query as a filter: orders[*].{id:id,created_at:created_at,updated_at:updated_at,total_price:total_price,currency:currency,financial_status:financial_status,refunds:refunds[].transactions[].receipt.{amount:amount}}
      I just created a template you can use that automatically sets the URL and filters I just suggested and summarizes it into a sales report, please take a look.

      Reply

Leave a Comment

Jump To...