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. 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.
- At the bottom, make sure to select the data points you'd like to have access to via the API (e.g. 'read_orders' if you're interested in transaction data). Check the documentation on scopes for more information.
PART 2: CREATE YOUR SHOPIFY API REQUEST URL
We’re going to follow the Shopify API documentation to retrieve a list of products in your store.
- Base URL: https://{your_shop_name}.myshopify.com
Base URL Example: https://mixedshoptest.myshopify.com - Endpoint: /admin/api/{version}/{resource}.json
Endpoint 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.jsonPART 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: SHOPIFY API + GOOGLE SHEETS 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 - 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:
https://your_shop_name.myshopify.com/admin/api/2019-07/orders.json?status=any&limit=250You can also combine this with date-based parameters:
https://your_shop_name.myshopify.com/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, or changing your date range to retrieve additional sets of data. Alternately, you can loop through pages automatically with pagination handling (paid feature). Shopify's documentation shows that next page URLs are provided in a field called "Link", so you would paginate through responses like this:
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 support@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.
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.jsonHi 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.
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. 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_customersscope.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 to support@mixedanalytics.com so I can take a look.