Back to Blog

Shopify Data Export: How to Connect Shopify to Google Sheets Without Coding

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:

  1. Create a custom Shopify app to access your store’s data via Shopify’s APIs.
  2. Configure the Shopify to Google Sheets integration using Coupler.io, a GSheets add-on.

Create a Shopify custom app to export store’s data

  1. 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 (Analytics, Reports, 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:

https://b104dw48dg97bp9ukke89uvzp7a41d:shppa_b57ecxb6p84k0t3epwdqoq7b716a0@sf-sandwiches.myshopify.com/admin/api/2020-07/orders.json
  • b104dw48dg97bp9ukke89uvzp7a41d – API key
  • shppa_b57ecxb6p84k0t3epwdqoq7b716a0 – Password
  • sf-sandwiches.myshopify.com – hostname of your Shopify store
  • 2020-07 – Webhook API version
  • orders – resource

Set up the Shopify-Google Sheets integration with Coupler.io

Coupler.io is a Google Sheets add-on to import data from different sources, such as Pipedrive, Jira, Xero, and many more. Check out the ready-to-use integrations available.

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 “products” resource:

https://b104dw48dg97bp9ukke89uvzp7a41d:shppa_b57ecxb6p84k0t3epwdqoq7b716a0@sf-sandwiches.myshopify.com/admin/api/2020-07/products.json

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:

limit: 250

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 entityJSON URLPath
List of products.../admin/api/2020-07/products.jsonproducts
List of customers.../admin/api/2020-07/customers.jsoncustomers
List of orders.../admin/api/2020-07/orders.json?status=anyorders
Orders by customer.../admin/api/2020-07/customers/{customer-ID}/orders.jsonorders
Reports
(available to Shopify Advanced and Shopify Plus merchants only)
.../admin/api/2020-07/reports.jsonreports
Application charges.../admin/api/2020-07/application_charges.json
Application credits.../admin/api/2020-07/application_credits.json
List of locations.../admin/api/2020-07/locations.jsonlocations
List of blogs.../admin/api/2020-07/blogs.jsonblogs
Current balance*.../admin/api/2020-07/shopify_payments/balance.json
List of disputes* (initiated at the specified date).../admin/api/2020-07/shopify_payments/disputes.json?initiated_at={yyyy-mm-dd}
List of all payouts*.../admin/api/2020-07/shopify_payments/payouts.json
List of all balance transactions paid out in the specified payout*.../admin/api/2020-07/shopify_payments/balance/transactions.json?payout_id={payout-id}

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

Task:

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:

Source
JSON URLhttps://{api-key}:{password}@{shop-name}.myshopify.com/admin/api/2020-07/products.json
HTTP MethodGET
URL query stringlimit:250
Pathproducts
Settings
Automatic data refresh1 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 (column A)
  • title (column B)
  • body_html (column C)
  • product_type (column E)
  • variants.price (column Q)
  • variants.sku (column R)
  • variants.position (column S)
  • variants.inventory_policy (column T)
  • variants.barcode (column AD)
  • variants.grams (column AE)
  • variants.inventory_quantity (column AJ)

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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Access your data
in a simple format for free!

Start Free