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?
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.
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:
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:
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:
Several of the important data that you can access using Shopify’s Admin API are the following:
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.
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:
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.
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.
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:
=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.
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:
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.
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
