Discover 3 ways to import Shopify data into Google Sheets and harness the power of both platforms in your data analysis.
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.
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:
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
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:
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.