How to Import Shopify Data to Google Sheets

Discover 3 ways to import Shopify data into Google Sheets and harness the power of both platforms in your data analysis.

Table of Contents
  1. A quick introduction to Shopify API
  2. A summary of Shopify data
  3. How to generate Shopify API key
  4. How to import Shopify data using Google Sheets add-on: API connector
  5. How to import Shopify data using Google Apps Script
  6. How to filter Shopify data to reduce the number of columns
  7. How to import Shopify data using Lido
Table of contents
Chapter 1
Chapter 2
Chapter 3
  1. A quick introduction to Shopify API
  2. A summary of Shopify data
  3. How to generate Shopify API key
  4. How to import Shopify data using Google Sheets add-on: API connector
  5. How to import Shopify data using Google Apps Script
  6. How to filter Shopify data to reduce the number of columns
  7. How to import Shopify data using Lido
7 Minutes

Shopify continues to be one of the most popular eCommerce platforms worldwide. We have published articles in the past about different ways of importing Shopify data, but here is our definitive guide of the best methods for extracting your Shopify data to a spreadsheet. Simply keep this page open while trying the methods one-by-one. Are you ready?

A quick introduction to Shopify API

Different services over the internet include so-called application programming interfaces, or APIs, which allow communication between the platform and other software. The developers use APIs so that their own systems can communicate quickly and easily with outside platforms. APIs require authentication to prevent access to full data by nonauthenticated end users, keeping your data secure. The data that can be accessed through APIs are structured but not formatted for end user use. Developers must develop their own code to process and format it. For example, several developers of apps that use Facebook take advantage of the Facebook API in order to use that platform. You can learn more from this HowtoGeek article.

APIs also allow access to data that the platform holds. Shopify has their own API that can be accessed via web browser, and the documentation is available here.

A summary of Shopify data

Shopify data is stored in the form of JSON files and can be accessed via the API. For example, you can access the customers data via its customers.json file with the following URL:

https://mystore.myshopify.com/admin/customers.json

Where “mystore” is replaced by the name of your store. You can directly open the json file in your browser if you are logged in as an admin of the Shopify store. 

For other information, you can check the following JSON files:

  • orders.json - list of orders including all the relevant data such as the one who made the order, the value of the order, and even where the order is to be delivered
  • products.json - list of products you offer including their price and the variations that you offer.
  • reports.json - list of analytics reports generated by Shopify with the ShopifyQL query that generates the values.

How to generate a Shopify API key

Step 1: Click the Apps option in the admin sidebar on the left. The page Apps will load. Scroll down. At the end there is a sentence that says “Working with a developer on your shop? Manage private apps.” Click on the link.

Step 2: The page Private apps will be loaded. By default, you are not allowed to create your own private apps, as gaining access to your API is a security risk. Enable access to your API by clicking Enable private apps. You cannot disable private app development once you have enabled it. Be mindful of Shopify’s API License and Terms of Use.

Step 3: Click the Create private app button on the upper-right corner of the page. Provide the required information as shown in the figure below:

Upper half of Create private app page.
Upper half of Create private app page. Here you enter the private app name and the emergency developer email. 

Step 4: Specify the Admin API options by setting the permissions for the private app. You can select which datasets to access by changing their access settings. The three access settings are: 

  • No access - you cannot access the data using the API key
  • Read access - you can access the data but cannot make changes to it
  • Read and write - you can access the data and also make changes to it from Google Sheets (we will not use this option in this tutorial)

Several of the important data that you can access using Shopify’s Admin API are the following:

  • Analytics: View store metrics
  • Browsing behavior: View or manage online-store browsing behavior including page views, cart updates, product views and searches
  • Customers: View or manage customers, customer addresses, order history, and customer groups
  • Inventory: View or manage inventory across multiple locations
  • Marketing events: View or manage marketing events and engagement data
  • Orders: View or manage orders, transactions, fulfillments, and abandoned checkouts
  • Price rules: View or manage conditional discounts
  • Products: View or manage products, variants, and collections
  • Reports: View or manage reports on the Reports page in the Shopify admin
  • Store content: View or manage articles, blogs, comments, pages, and redirects

The lower half of the create private app page.
The lower half of the create private app page. You set the permissions in this part setting the access rights for each dataset. Click Show inactive Admin API permissions to see more datasets that you can access.

Once you save it, the API keys will be loaded. You will need the following information:

  1. Example URL
  2. API Key
  3. Password

Admin API page
The Admin API will contain the information needed to access Shopify data via API. It is best not to store them in a file offline but to simply access them by logging in to your Shopify admin account.

While using one of the methods shown here, it is best practice to keep your Shopify account open to simply copy the API key, passwords, and example URL instead of storing them elsewhere. 

How to import Shopify data using Google Sheets add-on: API Connector

One of the most popular data import add-ons in Google Sheets is the API Connector. It is flexible and can be used to import data from different platforms as long as you have the API key and other relevant access codes needed. Click the link above to learn how to install the API Connector add-on to your Google Sheets.

API Connector
API Connector

To use API Connector, here are the steps:

Step 1: Click Add new request to add a new API.

API Connector sidebar, Add new request
API Connector sidebar, Add new request

A set of required information will appear in the sidebar that you should supply in order to access the service or the database you want to connect to Google Sheets. 

API Connector, Configure API Request options
API Connector, Configure API Request options

Step 2: Copy the Example URL from your Shopify private app. It already contains the API Key and password as well.

Shopify private app, example URL with a syntax guide below.
Shopify private app, example URL with a syntax guide below.

Take note of the syntax of the URL:

https://{apikey}:{password}@{hostname}/admin/api/{version}/{resource}.json

The example URL already takes care of everything you need to connect to a specific dataset. The resource accessed may be different from what you need; simply change it to whatever you need.

Step 3: Go back to the API Connector sidebar in Google Sheets, and paste the URL to the Request URL box.

API Connector, Configure API Request with Request URL added
API Connector, Configure API Request with Request URL added

Scroll down to specify the output sheet and the name of the request. Click Set current if you want to import the data to the current active sheet.

API Connector, Configure API Request, Output settings and request name 
API Connector, Configure API Request, Output settings and request name 

Step 4: Click Run. The importation can take some time to load the data, especially if the amount of data surpasses hundreds of rows.

Shopify data imported to Google Sheets.
Shopify data imported to Google Sheets.

How to import Shopify data using Google Apps Script 

Another method for importing Shopify data is Google Apps Script. One script is called ImportJSON, hosted on Github.  Here are the steps for using it:

Step 1: Copy the code from Github.

Step 2: Go to Google Apps Script by clicking Extensions:

Extensions, Apps Script
Extensions, Apps Script

And then paste the script:

Apps Script, ImportJSON code added
Apps Script, ImportJSON code added

Then click save.

Step 3: Go back to Google Sheets, and use the following formula:

=ImportJSONBasicAuth("database_URL","api_key","password")

Where database_URL has the following format:

https://{hostname}/admin/api/{version}/{resource}.json

As clear in the format, when you use this method, make sure that the link does not have the API key and password parsed with it.

Take note, however, that some JSON databases might have too many columns that Google Sheets may not be able to process, resulting in an error. ImportJSON does not have a built-in function to automatically add columns when needed.

How to filter Shopify data to reduce the number of columns

Even though API Connector can handle well the hundreds of columns that Shopify datasets can have, it may still be best if you filter the data before it is imported. For example, in accessing your list of orders, you may only want to get the timestamp of the order and the total value. However, you easily get hundreds of columns worth of extraneous details:

Original imported Shopify data spanning hundreds of columns

The majority of these columns are often irrelevant to your analysis. To only select the columns you need, you need to add options to the URL. The options are available via the Documentation of Shopify’s Admin API. For example, you can check the list of options for orders.json here.

If we want to only include the date of order and the total price, we need to only include created_at and current_total_price. You need to modify the URL by adding the fields attribute:

https://<your_shopify_store>.myshopify.com/admin/api/2022-04/orders.json?fields=created_at,current_total_price

The result only includes the selected columns.

Imported Shopify data only including two selected columns.
Imported Shopify data only including two selected columns.

How to import Shopify data using Lido

We can’t end this tutorial without showcasing our own product, Lido, and how it makes importing data from Shopify a breeze compared to the complicated API keys and Google Apps Script showcased above.

Step 1: In a Lido spreadsheet, click Data, then scroll down to find Shopify.

Lido app, Data sidebar, raw data sources list.
Lido app, Data sidebar, raw data sources list.

Choose from three options: Customers, Orders, and Products. We will select Products for our example. 

Step 2: You will be asked to connect to your Shopify store. Enter the store name included in your store URL:

Lido app, Data sidebar, Shopify store URL prompt
Lido app, Data sidebar, Shopify store URL prompt

If unsure, simply check the address bar in the tab where you are logged in to your Shopify admin account:

Shopify admin page. The location of the store URL can be seen on the address bar.
Shopify admin page. The location of the store URL can be seen on the address bar.

Step 3: A pop-up window will appear saying the authentication was successful.

Pop-up window saying the connection was successfully authenticated.
Pop-up window saying the connection was successfully authenticated.

Step 4: The Dataset Editor will load. Select the columns that you want to load into your spreadsheet. The preview will update with every change in the columns selected. When you are satisfied, click Save dataset & add view

Lido app, Dataset editor. Includes list of columns that can be selected and a preview of the output.
Lido app, Dataset editor. Includes list of columns that can be selected and a preview of the output.

Voila! The data is now loaded.

Shopify data imported to Lido app
Shopify data imported to Lido app

Interested? Click here to get started.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

July 31, 2022

Chart with arrows pointing up and down

Shopify continues to be one of the most popular eCommerce platforms worldwide. We have published articles in the past about different ways of importing Shopify data, but here is our definitive guide of the best methods for extracting your Shopify data to a spreadsheet. Simply keep this page open while trying the methods one-by-one. Are you ready?

A quick introduction to Shopify API

Different services over the internet include so-called application programming interfaces, or APIs, which allow communication between the platform and other software. The developers use APIs so that their own systems can communicate quickly and easily with outside platforms. APIs require authentication to prevent access to full data by nonauthenticated end users, keeping your data secure. The data that can be accessed through APIs are structured but not formatted for end user use. Developers must develop their own code to process and format it. For example, several developers of apps that use Facebook take advantage of the Facebook API in order to use that platform. You can learn more from this HowtoGeek article.

APIs also allow access to data that the platform holds. Shopify has their own API that can be accessed via web browser, and the documentation is available here.

A summary of Shopify data

Shopify data is stored in the form of JSON files and can be accessed via the API. For example, you can access the customers data via its customers.json file with the following URL:

https://mystore.myshopify.com/admin/customers.json

Where “mystore” is replaced by the name of your store. You can directly open the json file in your browser if you are logged in as an admin of the Shopify store. 

For other information, you can check the following JSON files:

  • orders.json - list of orders including all the relevant data such as the one who made the order, the value of the order, and even where the order is to be delivered
  • products.json - list of products you offer including their price and the variations that you offer.
  • reports.json - list of analytics reports generated by Shopify with the ShopifyQL query that generates the values.

How to generate a Shopify API key

Step 1: Click the Apps option in the admin sidebar on the left. The page Apps will load. Scroll down. At the end there is a sentence that says “Working with a developer on your shop? Manage private apps.” Click on the link.

Step 2: The page Private apps will be loaded. By default, you are not allowed to create your own private apps, as gaining access to your API is a security risk. Enable access to your API by clicking Enable private apps. You cannot disable private app development once you have enabled it. Be mindful of Shopify’s API License and Terms of Use.

Step 3: Click the Create private app button on the upper-right corner of the page. Provide the required information as shown in the figure below:

Upper half of Create private app page.
Upper half of Create private app page. Here you enter the private app name and the emergency developer email. 

Step 4: Specify the Admin API options by setting the permissions for the private app. You can select which datasets to access by changing their access settings. The three access settings are: 

  • No access - you cannot access the data using the API key
  • Read access - you can access the data but cannot make changes to it
  • Read and write - you can access the data and also make changes to it from Google Sheets (we will not use this option in this tutorial)

Several of the important data that you can access using Shopify’s Admin API are the following:

  • Analytics: View store metrics
  • Browsing behavior: View or manage online-store browsing behavior including page views, cart updates, product views and searches
  • Customers: View or manage customers, customer addresses, order history, and customer groups
  • Inventory: View or manage inventory across multiple locations
  • Marketing events: View or manage marketing events and engagement data
  • Orders: View or manage orders, transactions, fulfillments, and abandoned checkouts
  • Price rules: View or manage conditional discounts
  • Products: View or manage products, variants, and collections
  • Reports: View or manage reports on the Reports page in the Shopify admin
  • Store content: View or manage articles, blogs, comments, pages, and redirects

The lower half of the create private app page.
The lower half of the create private app page. You set the permissions in this part setting the access rights for each dataset. Click Show inactive Admin API permissions to see more datasets that you can access.

Once you save it, the API keys will be loaded. You will need the following information:

  1. Example URL
  2. API Key
  3. Password

Admin API page
The Admin API will contain the information needed to access Shopify data via API. It is best not to store them in a file offline but to simply access them by logging in to your Shopify admin account.

While using one of the methods shown here, it is best practice to keep your Shopify account open to simply copy the API key, passwords, and example URL instead of storing them elsewhere. 

How to import Shopify data using Google Sheets add-on: API Connector

One of the most popular data import add-ons in Google Sheets is the API Connector. It is flexible and can be used to import data from different platforms as long as you have the API key and other relevant access codes needed. Click the link above to learn how to install the API Connector add-on to your Google Sheets.

API Connector
API Connector

To use API Connector, here are the steps:

Step 1: Click Add new request to add a new API.

API Connector sidebar, Add new request
API Connector sidebar, Add new request

A set of required information will appear in the sidebar that you should supply in order to access the service or the database you want to connect to Google Sheets. 

API Connector, Configure API Request options
API Connector, Configure API Request options

Step 2: Copy the Example URL from your Shopify private app. It already contains the API Key and password as well.

Shopify private app, example URL with a syntax guide below.
Shopify private app, example URL with a syntax guide below.

Take note of the syntax of the URL:

https://{apikey}:{password}@{hostname}/admin/api/{version}/{resource}.json

The example URL already takes care of everything you need to connect to a specific dataset. The resource accessed may be different from what you need; simply change it to whatever you need.

Step 3: Go back to the API Connector sidebar in Google Sheets, and paste the URL to the Request URL box.

API Connector, Configure API Request with Request URL added
API Connector, Configure API Request with Request URL added

Scroll down to specify the output sheet and the name of the request. Click Set current if you want to import the data to the current active sheet.

API Connector, Configure API Request, Output settings and request name 
API Connector, Configure API Request, Output settings and request name 

Step 4: Click Run. The importation can take some time to load the data, especially if the amount of data surpasses hundreds of rows.

Shopify data imported to Google Sheets.
Shopify data imported to Google Sheets.

How to import Shopify data using Google Apps Script 

Another method for importing Shopify data is Google Apps Script. One script is called ImportJSON, hosted on Github.  Here are the steps for using it:

Step 1: Copy the code from Github.

Step 2: Go to Google Apps Script by clicking Extensions:

Extensions, Apps Script
Extensions, Apps Script

And then paste the script:

Apps Script, ImportJSON code added
Apps Script, ImportJSON code added

Then click save.

Step 3: Go back to Google Sheets, and use the following formula:

=ImportJSONBasicAuth("database_URL","api_key","password")

Where database_URL has the following format:

https://{hostname}/admin/api/{version}/{resource}.json

As clear in the format, when you use this method, make sure that the link does not have the API key and password parsed with it.

Take note, however, that some JSON databases might have too many columns that Google Sheets may not be able to process, resulting in an error. ImportJSON does not have a built-in function to automatically add columns when needed.

How to filter Shopify data to reduce the number of columns

Even though API Connector can handle well the hundreds of columns that Shopify datasets can have, it may still be best if you filter the data before it is imported. For example, in accessing your list of orders, you may only want to get the timestamp of the order and the total value. However, you easily get hundreds of columns worth of extraneous details:

Original imported Shopify data spanning hundreds of columns

The majority of these columns are often irrelevant to your analysis. To only select the columns you need, you need to add options to the URL. The options are available via the Documentation of Shopify’s Admin API. For example, you can check the list of options for orders.json here.

If we want to only include the date of order and the total price, we need to only include created_at and current_total_price. You need to modify the URL by adding the fields attribute:

https://<your_shopify_store>.myshopify.com/admin/api/2022-04/orders.json?fields=created_at,current_total_price

The result only includes the selected columns.

Imported Shopify data only including two selected columns.
Imported Shopify data only including two selected columns.

How to import Shopify data using Lido

We can’t end this tutorial without showcasing our own product, Lido, and how it makes importing data from Shopify a breeze compared to the complicated API keys and Google Apps Script showcased above.

Step 1: In a Lido spreadsheet, click Data, then scroll down to find Shopify.

Lido app, Data sidebar, raw data sources list.
Lido app, Data sidebar, raw data sources list.

Choose from three options: Customers, Orders, and Products. We will select Products for our example. 

Step 2: You will be asked to connect to your Shopify store. Enter the store name included in your store URL:

Lido app, Data sidebar, Shopify store URL prompt
Lido app, Data sidebar, Shopify store URL prompt

If unsure, simply check the address bar in the tab where you are logged in to your Shopify admin account:

Shopify admin page. The location of the store URL can be seen on the address bar.
Shopify admin page. The location of the store URL can be seen on the address bar.

Step 3: A pop-up window will appear saying the authentication was successful.

Pop-up window saying the connection was successfully authenticated.
Pop-up window saying the connection was successfully authenticated.

Step 4: The Dataset Editor will load. Select the columns that you want to load into your spreadsheet. The preview will update with every change in the columns selected. When you are satisfied, click Save dataset & add view

Lido app, Dataset editor. Includes list of columns that can be selected and a preview of the output.
Lido app, Dataset editor. Includes list of columns that can be selected and a preview of the output.

Voila! The data is now loaded.

Shopify data imported to Lido app
Shopify data imported to Lido app

Interested? Click here to get started.

Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.