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:
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 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:
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
Once you save it, the API keys will be loaded. You will need the following information:
- Example URL
- API Key
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.
To use API Connector, here are the steps:
Step 1: Click Add new request to add a new API.
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.
Step 2: Copy the Example URL from your Shopify private app. It already contains the API Key and password as well.
Take note of the syntax of the URL:
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.
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.
Step 4: Click Run. The importation can take some time to load the data, especially if the amount of data surpasses hundreds of rows.
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:
And then paste the script:
Then click save.
Step 3: Go back to Google Sheets, and use the following formula:
Where database_URL has the following format:
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:
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:
The result only includes the 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.
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:
If unsure, simply check the address bar in the tab where you are logged in to your Shopify admin account:
Step 3: A pop-up window will appear saying the authentication was successful.
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.
Voila! The data is now loaded.
Interested? Click here to get started.
-Google Sheets Add-Ons for Importing Data
-Import Mailchimp to Google Sheets
-Import Klaviyo to Google Sheets
-Import Salesforce to Google Sheets
-Google Sheets Import Functions