Import Shopify Data to Google Sheets
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. The cool thing about this method is that you can access the complete set of the data available from the API without limit.
The end of this article also contains a pack of preset Shopify reports (pro users only).
CONTENTS
- Before You Begin
- Part 1: Get your Shopify API Password
- Part 2: Create your API Request URL
- Part 3: Pull Shopify API Data to Sheets
- Part 4: More Example API URLs
- Part 5: Handle Pagination
- Part 6: Handle Filtering
- Appendix 1 [PRO]: Get the Shopify Report Pack
- Appendix 2: Variation – Use GraphQL
BEFORE YOU BEGIN
Click here to install the API Connector add-on from the Google Marketplace.
PART 1: GET YOUR SHOPIFY API PASSWORD
- Log into your Shopify store and click Apps
- On the Apps page, click “Manage private apps”
- From here, click the “Create private app” button
- On the next screen, in the App details section, enter a name for your app and an email address.
- The next section is where you select which data points you’d like to have access to via the API. You start with none, so click “Show inactive Admin API permissions” to view them.
- You’ll now see a list of available permissions. Select the permissions you’d like to have access to via the API (check the documentation on scopes for more information). Note that selecting Reports 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. Analytics also doesn’t help here as it requires an additional SDK for access.
Most likely, you will want read access to Orders and Products. - Click save. You’ll see a verification modal. Click “Create app” to continue.
- 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.
PART 2: CREATE YOUR SHOPIFY API REQUEST URL
We’ll first follow the Shopify API documentation to retrieve a list of orders from your store.
- Base URL: https://YOUR_SHOP_NAME.myshopify.com
- Endpoint: /admin/api/2020-10/orders.json
- Parameters: ?status=any
Putting it together, we get the full API Request URL. Just substitute in your own store name. The API version number (2020-10) will likely have updated by the time you read this, but you can still use it, or substitute in the latest version number.
https://YOUR_SHOP_NAME.myshopify.com/admin/api/2020-10/orders.json?status=any
?status=any
parameter, Shopify will only return orders with a status of open.PART 3: PULL SHOPIFY API DATA TO SHEETS
Now let’s paste that URL into API Connector and import Shopify data into Google Sheets!
- Open up Google Sheets and click Add-ons > API Connector > Open.
- In the Create screen, enter the Request URL we just created
- Under Headers, enter X-Shopify-Access-Token as your Key, and your API password as the Value, like this:
It should look like this:X-Shopify-Access-Token YOUR_API_PASSWORD - Create a new tab and click ‘Set current’ to use that tab as your data destination.
- Name your request and click Run. A moment later you’ll see your Shopify data populate your Google Sheet:
- Toggle between the “single row (default)” and “grid” report styles (located under Output Options) to see which output style you prefer. The single row style puts each order ID in its own row, while grid style repeats the order ID for each line item.
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):
- get a list of products in your Shopify store
https://YOUR_SHOP_NAME.myshopify.com/admin/api/2021-01/products.json
The above example uses ‘grid’ style to put all product variants into the same column. The default ‘single row’ style would produce one row per product ID, with each product variant in a separate column. - get a count of products in your store
https://YOUR_SHOP_NAME.myshopify.com/admin/api/2021-01/products/count.json
- get product info with prices, skus, inventory item IDs and other fields
https://YOUR_SHOP_NAME.myshopify.com/admin/api/2021-01/variants.json
- get customer info (emails, names, addresses)
https://YOUR_SHOP_NAME.myshopify.com/admin/api/2021-01/customers.json
PART 5: HANDLE PAGINATION
- By default, Shopify limits the number of responses returned at once, usually to 50 records at a time.
- To get more, use the ‘limit’ parameter, like this:
You can also combine this with date-based parameters:https://YOUR_SHOP_NAME.myshopify.com/admin/api/2021-01/orders.json?status=any&limit=250
When you use date parameters, it’s usually useful to reference date cells in your sheet rather than manually adding dates to your request URL. You can force your date cell to Shopify’s required format with a function likehttps://YOUR_SHOP_NAME.myshopify.com/admin/api/2021-01/orders.json?status=any&limit=250&created_at_min=2019-10-25T16:15:47-04:00
text(A1,"yyyy-mm-dd")
. - As an alternative to changing these fields manually, you can loop through and grab all your data 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:
- API URL: enter your request URL as usual, making sure to include limit=250
- Pagination type:
next page URL
- Field name:
Link
- Number of pages: enter the number of pages you’d like to fetch
PART 6: HANDLE FILTERING
- Shopify returns a huge amount of data from their orders endpoint, often 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’s 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/2021-01/orders.json?status=any&limit=250&fields=id,email,created_at,total_price,financial_status,order_number,line_items
The “line_items” field alone produces a lot of columns about individual items in each order, so if you don’t need that level of detail, just leave it out. - 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 neat, simplified orders report like this:orders[*].{id:id,email:email,created_at:created_at,total_price:total_price,subtotal_price:subtotal_price,name:name,line_items:line_items[*].{title:title,sku:sku,quantity:quantity,product_id:product_id,price:price,total_discount:total_discount}}
The above example uses ‘grid’ style to put all line items into the same column. The default ‘single row’ style would produce one row per order ID, with each line item in a separate column.
- 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.
APPENDIX 1 [PRO]: GET THE SHOPIFY REPORT PACK
The Shopify report pack is a pack of pre-built reports to get you started. All you need to do is enter your own Shopify password and store name to populate the reports with your own data.
The report pack includes the following:
- Products: A list of products in your store, including ID, title, variant IDs, barcode, weight, and inventory quantity
- OrderDetails (Grid): A report showing all your orders, including the line items within them. In grid mode so multiple line items appear in a single column.
- OrderDetails (Row): Same as above, but each order is listed on a new rows, so multiple line items get their own columns
- Sales Summary: A list of orders, without detail about line items
- Refunds: A report of all refunds with date and refund amount
- Overview: A simple overview matching* the metrics provided by Shopify’s Sales Summary report
*In some cases, the “refunds” metric will show a slight discrepancy.
This report pack makes use of JMESPath filters and pagination handling, so you’ll need a pro account with API Connector for it to work. Here is the link (click File > Make a Copy to get your own copy).
APPENDIX 2: 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 a little more technical (or willing to learn), and interested in working with Shopify’s GraphQL API, this is the summary of how to use it:
- Create your graphQL query and enter it into a cell, let’s say cell A1 of a sheet called Input. You can experiment and create queries using Shopify’s GraphQL Explorer. 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 } } }
- Encode the whole block by creating a new cell with this formula:
=ENCODEURL(A1)
- Back in the API Connector sidebar, choose the POST method.
- 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+++
- In the POST body, enter
{}
(we don’t need a POST body here but it can’t be empty) - Under Headers, enter the following:
X-Shopify-Access-Token YOUR_API_PASSWORD Content-type application/json - 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).
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? 🙂
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
The Report Styles update is done! : ) Products should now all appear on the same line.
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?
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
Getting these two errors:
Request failed: Server responded with an error
Request failed: Login information disallowed
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.
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?
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.
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 ?
The key isn’t your API key, it is the exact text listed in the article (“X-Shopify-Access-Token”, without the quotation marks).
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?
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)
Hi Andrew, the /reports endpoint is only for accessing reports that you’ve made through this ‘app’ (your Google Sheets app), and even then it will only show you the report properties rather than the data within the report. This thread explains it in more detail: https://community.shopify.com/c/Shopify-APIs-SDKs/Unable-to-get-reports-with-API/td-p/547475
Hi Ana
Do you know if you can run this for the finances summary report, specifically for Total Sales and Total payments?
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).No problem, will take a look at the link. Thanks Ana!
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..
You can view information on error messages here: https://mixedanalytics.com/knowledge-base/api-connector-error-messages. This particular error message is related to Google getting confused with multiple active accounts, so you’ll need to log out of your other accounts or re-open API Connector in an incognito window. Please let me know if you continue to have any issues.
Great tutorial ! i love it. Is it possible to retrieve the stock qty for each products by warehouse?
Thanks for the feedback! I’m not sure about stock quantity, you’d need to check the documentation. It looks like https://help.shopify.com/en/api/reference/inventory/inventorylevel is the relevant section.
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
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 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.
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?
Hey there, please make sure your access token was set up with the right scope in your private app. You can see documentation on scopes here: https://shopify.dev/docs/admin-api/access-scopes
For customers, you’ll need
read_customers
scope.Thanks Man! I managed to find this via https://stackoverflow.com/questions/50364356/shopify-errors-api-this-action-requires-merchant-approval-for-write-themes
I spent almost like few hours to figure this out.
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..
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 so I can take a look.
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!
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
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.
I found compact returns the items on a new row, but when including the order name it is not ‘in alignment’
https://+++Inputs!C5+++/admin/api/2020-04/orders.json?status=open&limit=250&fields=name,line_items
returned 2 orders, 3 products total.
order 1 has 2 products., 2 lines
2nd row has third rows order name against it though
3rd row has no order name
Hope that makes sense !
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.
Update: I’ve added a new report style called “grid” to address this issue. Grid style will match up parent IDs with nested elements.
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,
A few suggestions for troubleshooting:
1) the X-Shopify-Access-Token password should work for all endpoints. Can you try it with other endpoints and make sure it works, just to verify that you’re using the correct value?
2) Have you enabled read_inventory scope?
3) This thread discusses how to set up a request to the inventory_levels endpoint: https://community.shopify.com/c/Shopify-APIs-SDKs/GET-admin-inventory-levels-json-returns-422-Unprocessable-Entity/td-p/461209. Based on that, you have to include inventory_item_ids, location_ids, or both as filter parameters.
Noting this here in case anyone else comes across it — this was resolved by using the following endpoint: /admin/api/2020-04/locations/{location ID}/inventory_levels.json
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.
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:
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.
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()
, andcreated_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.
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
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: https://mixedanalytics.com/knowledge-base/troubleshooting-api-requests/#num3
That solves it! Thanks very much for the help Ana.
Jason
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?
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 (paid feature) allows you to choose and rename your fields all in one go.
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.
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.
Hi,
Great tool!
Is there a way to import ALL previous orders from a store? We have about 80k orders on Shopify.
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.
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?
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
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
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.
Hi ana
How I can get sales data with the help of API into google sheet
here is the link
https://myshopify.myshopify.com/admin/reports/sales_over_time?since=-30d&until=today&over=day
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
andcreated_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 usingupdated_at
instead ofcreated_at
. The date piece is key because Shopify calculates total sales by removing refunds that happened within your selected time period.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?
You can use “grid” style for this (under Output Options). Grid style will list the line items in a column and repeat the order numbers. This article contains more info about report styles: https://mixedanalytics.com/knowledge-base/report-styles/
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
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.
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
Great, I’m glad that worked. As for COGS, I just checked Shopify’s API docs and it looks like this is the endpoint you’re looking for: https://shopify.dev/docs/admin-api/rest/reference/inventory/inventoryitem.
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
orders » billing_address » city, I want to access city with HANDLE FILTERING, how can I do that ?
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 paid 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.
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
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
You can see more information about this issue here.