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 free API Connector add-on. We'll first get an API password from Shopify, and then set up a request to pull in product and transaction details from your Shopify store to your spreadsheet.
- Part 1: Get your Shopify API Password
- Part 2: Create your API Request URL
- Part 3: Enter values into API Connector
- Part 4: Notes and Expansions
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 a new private app" button
- On the "Create private app" screen, enter a name for your app and an email address (these are required fields). For now you don't need to change anything else, just scroll to the bottom and click Save.
- You'll see a verification modal after clicking Save. Click "I understand, create the 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, and can start pulling Shopify data into Google Sheets.
PART 2: CREATE YOUR API REQUEST URL
We’re going to follow the Shopify API documentation to retrieve a list of products in your store.
Shopify base URL: https://{your_shop_name}.myshopify.com
Example: https://mixedshoptest.myshopify.com
Endpoint: /admin/api/{version}/{resource}.json
Example: /admin/api/2019-07/products.json
Putting it all together, we get the full API Request URL:
https://mixedshoptest.myshopify.com/admin/api/2019-07/products.json
(Of course, you'll need to use your own shop name instead of mixedshoptest)
PART 3: ENTER VALUES INTO API CONNECTOR
We’re now ready to enter all our values into API Connector to start importing Shopify data into Google Sheets.
- Open up Google Sheets and click Add-ons > API Connector > Create New API Request.
- In the Create Request interface, enter the Request URL we just created
- Under Headers, enter X-Shopify-Access-Token as your Key, and your password as the Value, like this (the curly brackets indicate that you need to substitute in your own password):
X-Shopify-Access-Token {your password} It should look like this:
- 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.
- Name your request. Again we'll call it 'Products'
- Click Run and a moment later you’ll see your Shopify data populate the Products tab in your Google Sheet:
PART 4: NOTES AND EXPANSIONS
- 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/2019-07/products.json
https://your_shop_name.myshopify.com/admin/api/2019-07/orders.json?status=any
https://your_shop_name.myshopify.com/admin/api/2019-07/customers.json
https://your_shop_name.myshopify.com/admin/api/2019-07/countries.json
https://your_shop_name.myshopify.com/admin/api/2019-07/policies.json - Shopify limits the number of responses, usually to 50 records at a time. To get more, use the 'limit' parameter, e.g. /admin/api/2019-07/orders.json?status=any&limit=250. You can combine this with date-based parameters, e.g. /admin/api/2019-07/orders.json?status=any&limit=250&created_at_min=2019-10-25T16:15:47-04:00.
- 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. Alternately, you can implement some type of pagination to view all the results. Note that, as of July 2019, certain endpoints use cursor-based pagination, which unfortunately for us returns page info in the header response rather than the response output that gets printed into the sheet.
One workaround is to use the since_id parameter. As this post by Sudip Bishwakarma points out, be sure to use since_id=0 for the first page in order to sort via id, and then iterate additional requests using the last id from the previous request to the next. You can base your API URL on the value in a cell to set this ID dynamically, so you don't need to update your requests as the ID value changes. Example:
Page 1: https://your_shop_name.myshopify.com/admin/api/2019-10/orders.json?status=any&since_id=0
Page 2: https://your_shop_name.com/admin/api/2019-10/orders.json?status=any&since_id=+++Shopify_orders!A51+++
Hi
I think I have followed your guide all the way, but I get a error: [API] Invalid API key or access token (unrecognized login or wrong password)
Hey Morten, that means you’re connected and just have the wrong API key. Are you sure you copied in the right value? If you share your sheet with me I’ll be happy to take a look.
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 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
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, 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”)
Getting these two errors:
Request failed: Server responded with an error
Request failed: Login information disallowed
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 info@mixedanalytics.com 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.
Hi Ana,
financial_status is not working for me 🙁 I can call orders with “orders.json?status=any” but not “orders.json?financial_status=paid”…. it shows “Request processed, no records found”…
This is working as expected — it simply means that there are no records matching your condition, i.e. no records have a financial status of ‘paid’.
You can see this for yourself by checking the list returned by “orders.json?status=any”. Look for the column titled orders » financial_status (for me it is column Q). Based on your results, I suspect this column won’t contain any values of “paid”.
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).
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, are you able to connect and access the API examples in the article above? If you’re able to connect without issue, then you should be able to access the URL as described. Just remember that API Connector is a no-code method of accessing APIs, so you’d need to populate code snippets with the actual values. The sample code you show above should look something like this:
https://your-site.myshopify.com/admin/api/2019-10/123456.json
Yes, I was able to connect and access the API using the steps listed above.
When I run the API URL using my report ID, this is what I got.
“Request failed: Server response is not valid JSON”
That’s strange, since it’s a JSON endpoint. What happens when you try it in a different API tool, like https://inspector.swagger.io/?
Hi Ana
Do you know if you can run this for the finances summary report, specifically for Total Sales and Total payments?
Sorry, I’m not familiar enough with the Shopify API to know if that’s possible. I checked online and just see other people with similar questions: https://community.shopify.com/c/Shopify-APIs-SDKs/Recreate-Shopify-s-Sales-Over-Time-Report-with-API-data/td-p/475263
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.