Most of you know a native way to export Shopify data (products, orders, reports, etc.). It is the Export button, which lets you download a CSV file with your data. After that, you can open this CSV in a spreadsheet tool, such as Google Sheets or Excel, to manipulate your data. Manual data export is fine if you don’t need to do this recurrently. Otherwise, you’d better automate the process. We’ll explain how you can do this easily without a single line of code!
Connect Shopify to Google Sheets for Automatic Data Export
You’ll need to take two high-level steps:
- Create a custom Shopify app to access your store’s data via Shopify’s APIs.
- Configure the Shopify to Google Sheets integration using Coupler.io, a GSheets add-on.
Create a Shopify custom app to export store’s data
- Go to the Apps menu of your Shopify store.
2. Click Manage private apps.
3. Click Create new private app.
4. Specify the App details: Private app name and Emergency developer email. Neither field can be blank.
5. Select the wanted permissions for the app and set them to Read access. For example, if we set
Products permission to Read access, the app will view products, variants, and collections.
To activate other permissions (
Orders, etc.), click Show inactive Admin API permissions and configure the required ones.
6. Click Save and then Create app to obtain API credentials.
You’ll find the credentials in the Admin API block.
Example URL is a core parameter to set up Shopify Google Sheets integration. Here is an example of URL to export Shopify orders:
b104dw48dg97bp9ukke89uvzp7a41d– API key
sf-sandwiches.myshopify.com– hostname of your Shopify store
2020-07– Webhook API version
Set up the Shopify-Google Sheets integration with Coupler.io
Install Coupler.io from the G Suite Marketplace.
Open your spreadsheet, go to Add-ons => Coupler.io => Open dashboard and click Add Importer.
An importer is an integration that connects Google Sheets to a specific app or data source.
For now, Coupler.io does not provide a dedicated importer for Shopify. But, with the JSON Client importer, you can set up the Shopify to Google Sheets integration using the Shopify API. So, select JSON Client and complete the following steps:
1. Fill out the “Title” field
Enter the name of your integration.
2. Set up the Source
2.1. Fill out the “JSON URL” field
Insert the Example URL that you obtained when you created a Shopify private app. Depending on the data entity you’re going to export, modify the resource in the URL. For example, to export products from your Shopify store, use the “
Check out the list of Shopify data entities available for export and their API resources.
2.2. Select the HTTP Method
GET is the default HTTP method and the one we need here.
2.3. Fill out the “URL query string” field
Insert the following string:
This defines the maximum number of results to show on the page. A single importer can return up to 250 rows.
2.4. Fill out the “Path” field
Enter the string to select nested objects from the JSON response. For example, to export products from the Shopify store, the path string will be “
products“. The default value: select all objects.
3. Set up the Destination
3.1. Fill out the “Sheet Name” field
Add the name of the sheet that will be receiving data.
3.2. Fill out the “Cell address” field (optional)
Type in the address of the first cell where the data range will be imported. The default value is A1.
4. Configure the Settings (optional)
4.1. Automatic data refresh
To automate data export from Shopify on a schedule, toggle on Automatic data refresh and specify the Frequency.
4.2. Import Mode
Select the data import mode: replace or append. Check out our Knowledge Base to learn more about data import modes.
4.3. Add the Last Update column
If you want to add a column specifying the date of the last data refresh, toggle this function on.
Your Shopify to Google Sheets integration is almost ready. Click Save to save the parameters or Save & Run to save the parameters and run the initial data import right away.
What data you can export from your Shopify store
Make sure that your Shopify has the required app permissions set to Read access.
|Data entity||JSON URL||Path|
|List of products||products|
|List of customers||customers|
|List of orders||orders|
|Orders by customer||orders|
(available to Shopify Advanced and Shopify Plus merchants only)
|List of locations||locations|
|List of blogs||blogs|
|List of disputes* (initiated at the specified date)|
|List of all payouts*|
|List of all balance transactions paid out in the specified payout*|
*If you get an error with the Shopify Payments endpoints, it’s most likely that the Shopify Payments module is not available in your region.
For more information on other data entities available for export from Shopify, read the Shopify API documentation.
Use case: How to import inventory details of the products from a Shopify store into a spreadsheet
Now, let’s explore how the Shopify-Google Sheets integration works through a real-life use case.
We need to pull the products inventory from our Shopify store to Google Sheets. The data should be transferred automatically every hour, so we can track the inventory and share the report with the stakeholders.
Parameters for the integration:
Create a Shopify private app, and set the
Products admin API permission to Read access. Then use the following parameters to set up the JSON Client importer:
|URL query string|
|Automatic data refresh||1 hour|
Once you’ve set up the parameters of our Shopify to Google Sheets integration, click Save & Run to initiate the first data import.
In our case, we’ve got 66 columns of data, which is huge! To track the inventory efficiently, the following columns would be enough:
- id (
- title (
- body_html (
- product_type (
- variants.price (
- variants.sku (
- variants.position (
- variants.inventory_policy (
- variants.barcode (
- variants.grams (
- variants.inventory_quantity (
Unfortunately, the integration does not let us filter the data during the import. However, we can do this in Google Sheets with the help of the QUERY function. Create a separate sheet and apply the following QUERY formula:
=query( 'Shopify Products'!A:BN, "select A,B,C,E,Q,AJ,S,R,AD,AE,T" )
The formula extracted the necessary data, which will update automatically every hour thanks to Coupler.io. Read our blog post to discover more about the Google Sheets Query Functions.
Should I set up an automatic data export from Shopify?
If you have to export some data from your Shopify store once per quarter (or rarely), then you may prefer a manual approach. However, it’s very convenient to have your recurring tasks automated, isn’t it? The integration we’ve built with Coupler.io allows you to schedule frequent (1, 3, 6, 12 and 24 hours), as well as less frequent (daily, weekly, monthly, and yearly) data exports. So, why would you need to do this by hand? Anyway, it’s up to you.
We hope that this integration did the job for you. If you want to have a ready-to-use Shopify importer, tell us about it by filling out this form. The Coupler.io team is working on new integrations and your opinion is valuable to us. Good luck with your data!Back to Blog