Back to Blog

How to Export Shopify to Google Sheets Without Coding [Updated 2021]

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!

Note: The article has been updated once the Coupler.io team released the Shopify importer. We’ll explain how it works and keep the piece about importing data from Shopify using the JSON Client importer.

Connect Shopify to Google Sheets using the Shopify importer

Shopify importer is a ready-to-use integration to automate export of the following information from your Shopify store to Google Sheets:

  • Customers
  • Products 
  • Orders  

You’ll need to take two high-level steps:

  1. Get Shopify API credentials
  2. Set up the Shopify importer

How to get Shopify API credentials

To access the Shopify API and export data from your store, you’ll need to create a Shopify custom app.

  1. Go to the Apps menu of your Shopify store.

2. Click Manage private apps.

3. Click Enable private app development and provide your consent to the specified terms. This is required to let you create your private apps or make changes to them.

4. Once private app development is enabled, you can create your new private app by clicking the respective button.

5. Specify the App details: Private app name and Emergency developer email. Neither field can be blank.

6. Scroll down to the section “Active Permissions For This App” with the Products permission set to Read access. You’ll need to activate permissions for:

  • Products
  • Customers
  • Inventory
  • Orders

To do this, click Show inactive Admin API permissions.

7. Change access status for Products, Customers, Inventory, and Orders to Read access.

8. Click Save and then Create app to obtain API credentials.

You’ll find your Shopify API credentials in the Admin API block.

To connect Shopify to Google Sheets, we’ll need the following values:

  • API key: 0a**************************28a 
  • Password: shp*******************************0

Set up the Shopify importer to automatically export Shopify into Google Sheets

Shopify importer is provided by Coupler.io, a solution for importing data from different sources, such as Pipedrive, Jira, Xero, and many more.

Install Coupler.io from the Google Workspace Marketplace.

Open your spreadsheet, go to Add-ons => Coupler.io => Open dashboard and click Add Importer.

Find and click Shopify.

Complete the following steps to set up and use the Shopify-Google Sheets integration:

1. Name your importer

2. Set up the Source

In this section, you’ll need to fill out the fields that will let you connect to your Shopify shop and fetch data from it.

Shop name

Enter the hostname of your Shopify store in the following format:

{shop_name}.myshopify.com
  • {shop_name} – replace with the name of your Shopify store
API key

Enter the API key of your Shopify store.

API password 

Enter the API password of your Shopify store.

Import information about

Select the data entity you want to import from Shopify: Customers, Products, Orders or Orders with line items.

If necessary, click Show Advanced to expand additional fields to filter out the data to be imported.

Read Coupler.io Knowledge Base to discover how you can use these filter parameters.

3. Set up the Destination

Destination account

Add a Google Account to connect to Shopify. 

Sheet name

Enter the name of the sheet to import Shopify data to.

4. Configure the Settings

Automatic data refresh

To automate data export from Shopify on a schedule, toggle on Automatic data refresh and specify the Frequency.

If you click Show Advanced, you’ll be able to select the data import mode and add a column specifying the date of the last data refresh.

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.

Here is what that Shopify data imported to Google Sheets will look like:

How to export other data from Shopify to Google Sheets

The Shopify importer lets you only get information about customers, orders and products. To import other data, you’ll need the JSON Client importer. With it, you can get data using REST APIs from different apps. For example, Etsy allows its users to download two types of data in CSV, but with the JSON Client importer, you can import much more. Check out how it works in our Etsy to CSV blog post.

Let’s explore how it works in the example of information about locations. It represents a geographical location where your stores, pop-up stores, headquarters, and warehouses exist. First, make sure to add Read access permission to this data entity in your Shopify app.

Set up the Shopify integration with Google Sheets using the JSON Client importer

Open your spreadsheet, go to Add-ons => Coupler.io => Open dashboard => click Add Importer and select JSON Client. Complete the following steps:

1. Name your importer

2. Set up the Source

JSON URL

Insert the Example URL that you obtained when you created a Shopify private app. This is how it may look:

https://b104dw48dg97bp9ukke89uvzp7a41d:shppa_b57ecxb6p84k0t3epwdqoq7b716a0@sf-sandwiches.myshopify.com/admin/api/2021-01/orders.json

Depending on the data entity you’re going to export, modify the resource (orders.json) in the example URL. For example, to export information about locations from your Shopify store, use the “locations.json” resource:

https://b104dw48dg97bp9ukke89uvzp7a41d:shppa_b57ecxb6p84k0t3epwdqoq7b716a0@sf-sandwiches.myshopify.com/admin/api/2021-01/locations.json

Check out the list of Shopify data entities available for export and their API resources.

HTTP

GET is the default HTTP method and the one we need here.

URL query string

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.

Path

Enter the string to select nested objects from the JSON response. For example, to export locations from the Shopify store, the path string will be “locations“. The default value: select all objects.

The steps 3 (Destination) and 4 (Settings) are the same as during the Shopify importer setup. Check out Coupler.io Knowledge Base to learn more about the JSON Client importer parameters.


Once ready, Click Save to save the parameters of your Shopify to Google Sheets integration or Save & Run to save it and run the initial data import right away.

Here is what the information about locations imported from Shopify to Google Sheets will look like:

What data you can export from Shopify to Google Sheets using the JSON Client importer

Make sure that your Shopify has the required app permissions set to Read access.

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

How to export Shopify inventory details of products to Google Sheets

Now, let’s explore how the Shopify-Google Sheets integration works through a real-life use case.

You can import products inventory from your Shopify store using both the Shopify importer and the JSON Client importer. Let’s explore how the latter works.

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. 

Configuration for your Shopify integration to export products

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 URL{example-URL}/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. The Shopify importer has the Fields to import feature, where you can specify the fields you want to import, but it works only for Customers and Orders data entities.

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 export Shopify orders and other data to Google Sheets on a schedule? 

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 customize the schedule of data exports from Shopify to Google Sheets. So, why would you need to do this by hand? Anyway, it’s up to you.

We hope that this integration built either with the Shopify importer or JSON Client importer did the job for you. If you want to have another  data source to import data from, 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

Comments are closed.

Access your data
in a simple format for free!

Start Free