How to import data from Shopify to Google Sheets

Shopify is the most popular ecommerce platform. With Shopify’s API, you can get analytics, customers, billing, inventory, orders, and other types of data sent from your Shopify account to your Google Sheet.

In this tutorial, I’ll show you step-by-step how to connect the Shopify API to Google Sheets in 6 steps:

  1. Install the Apipheny add-on
  2. Create a Shopify Development Store
  3. Create a Private App and Obtain an API Key
  4. Choose your Shopify API Endpoint URL
  5. Enter your Shopify API URL into Apipheny
  6. Run the Shopify API Request in your Google Sheet

Step 1.) Install and open the Apipheny add-on in Google Sheets

To pull data from the Shopify API to Google Sheets, first install and open the Apipheny Google Sheets add-on.

Apipheny is an API integrator for Google Sheets that you can use to connect to unlimited API data sources and make unlimited API requests. Click here for more information about Apipheny.

1. Install Apipheny by opening the following link in on desktop and then clicking the Install button on the page: https://gsuite.google.com/marketplace/app/apipheny/966163326746

Install Apipheny
Install Apipheny

2. After you’ve installed Apipheny, open a Google Sheet and then click on “Add-Ons” in the top menu. When you click “Add-ons”, you should see Apipheny in the dropdown menu.

Click Apipheny > Import API to open the Apipheny sidebar in your Google Sheet:

Open Apipheny
Open Apipheny

Step 2.) Create a Development Store

1. Next, log in to your Shopify account and click on Stores in the menu on the left:

Shopify Account
Log in to your Shopify account then click Stores

2. Click the Add store button:

Click "Add store"
Click “Add store”

3. Choose the Development store type and complete the Store name field:

Choose Development store and enter a name for your store
Choose Development store and enter a name for your store

4. Scroll down and complete the store address, choose the store purpose and then click the Save button:

Add store address and purpose then click save
Add store address and purpose then click save

5. Your Shopify development store is now created:

New Shopify store has been created
New Shopify development store has been created

Step 3.) Create a Private App and get your API Key

1. From your Shopify admin dashboard, go to Apps and then click Manage private apps:

Shopify Apps > Manage private apps
Click Apps > Manage private apps

2. Click the Create a new private app button:

Click Create a new private app

3. Enter a name for your Private app, something descriptive so you can remember the purpose of the app:

Enter a Private app name
Enter a Private app name

4. Scroll down to the Storefront API section and check the Allow the app to access your storefront data using Storefront API checkbox. Choose any permissions you want, and then click the Save button:

Choose your storefront API permissions then click Save
Choose your Storefront API permissions then click Save

5. In the Create a new private app screen that pops up, click I understand, create the app button:

Create a new private app
Create a new private app

6. Your private app has now been created:

Private app has been created
Private app has been created

7. Next, scroll down until you see the Admin API section and there you’ll find your API key, the password, the shared secret and the Storefront access token:

Shopify API key, password, and shared secret
Shopify API key, password, and shared secret
Shopify Storefront access token
Shopify Storefront access token

Copy and paste all of these credentials somewhere safe, as you will need them in the next steps.

Step 4.) Create your Shopify API URL with an Endpoint

In this section, we’ll show you how to browse the Shopify API documentation to find the exact API URL that you can use to retrieve specific data from your Shopify account.

If you already know your API URL, or you want to use the same example URL as us, just skip to Step 5.

First, open the Shopify API documentation page: https://shopify.dev/docs/admin-api/rest/reference

The menu on the left contains a list of the available API classes, each with its own APIs:

Shopify API documentation
Shopify API documentation

Clicking on an API class scrolls to that section, revealing a list of associated APIs within that API class.

Each API within an API class contains a list of one or more endpoints.

For instance, the Orders API class links to a list of 6 APIs within that class. One of the APIs within that class is the Order API, which links to a list of it’s 9 available endpoints: Retrieve a list of orders, Retrieve a specific order, Delete an order, etc.

Shopify API base URL

Shopify API root domain/base URL:
https://<store_name>.myshopify.com/admin/api/

All API calls to Shopify should be made to the https://<store_name>.myshopify.com/admin/api/  base domain, where <store_name> is the name of the store you previously created.

For example, one of the available endpoints is Retrieve a list of orders, and you can see the complete URL for this endpoint here:

https://examplestore.myshopify.com/admin/api/2020-04/orders.json

The documentation for each endpoint contains a short description, the HTTP method used (eg. GET), a description of the available parameters, and the link you should append to the root API URL for querying this endpoint (eg. /admin/api/2020-04/orders.json).

Shopify API endpoints documentation
Shopify API endpoints documentation

For endpoints that accept parameters, this documentation is where you would find them listed and explained, along with example inputs and responses.

Step 5.) Enter your Shopify API URL Into Apipheny

We’re almost done. Now go back to your Google Sheet and make sure that the Apipheny add-on is open on the “Import” tab. With the Import tab open, enter these details into the add-on:

Method: At the top of the Apipheny sidebar, select the HTTP method (GET or POST) required by your API endpoint. For this example, we are using the GET method.

API URL: In Step 4, we explained how you can find the Shopify REST API endpoint that you need. Now copy your complete Shopify API URL into the API URL Path field in the Apipheny add-on, followed by any GET parameters required for your query (if applicable). For this example, we are using the Shop API endpoint, to get data on the shop’s configuration:

https://examplestore.myshopify.com/admin/api/2020-04/shop.json

If you want to use this same example API URL, just replace “examplestore” with your store name.

Headers: In the Headers section in Apipheny, add one row with the following key and value:

Key:
X-Shopify-Access-Token
Value:
<your_private_app_admin_API_password>

<your_private_app_admin_API_password> is your private app’s Admin API password that you copied previously, in Step 3. Here’s what it should look like if you’ve entered everything correctly in Apipheny:

Shopify API URL and headers entered into Apipheny
Shopify API URL and headers entered into Apipheny

Step 6.) Run your Shopify API Request

Lastly, click the Run button at the bottom of the Apipheny add-on and then your Shopify API data will automatically be added to your Google Sheet:

Shopify API data in Google Sheets
Shopify API data in Google Sheets

After making a successful request to the Shopify API, try querying a different endpoint, such as the “all orders” endpoint:

/admin/api/2020-04/orders.json?status=any

You can also filter your orders results further by adding parameters to the end of your URL. Here’s a list of available parameters. Example: /admin/api/2020-04/orders.json?status=any?status=closed&financial_status=refunded will return a list of all closed and refunded orders

After you get the data you want in your spreadsheet, you can connect your Google Sheet to Google Data Studio if you’d like to visualize your Shopify data in Google Data Studio.

You can also try using one of the more advanced features in the Apipheny add-on, such as:

  • Save and schedule your Shopify API request
  • Enter multiple Shopify API URLs in the same request
  • Make a Shopify API POST request (if available)
  • Use the custom =APIPHENY() function to call Shopify data in a specific cell
  • Create a Shopify API request by referencing the value of a cell in your API request

Related articles:

Related Posts

Leave a Comment