How to import data from Shopify into 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 5 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
  5. Enter your Shopify API request into Apipheny

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

Apipheny is a free API connector for Google Sheets. You can use Apipheny to connect your Google Sheets to API data sources, easily.

1.) Install Apipheny by opening the following link on desktop and then clicking the Install button in the Google Marketplace: https://apipheny.io/install

2.) After you’ve installed Apipheny, open a Google Sheet and then click on the Extensions dropdown in the menu at the top.

In the dropdown list you should see Apipheny. Click Apipheny then click Import API to open the Apipheny sidebar in your Google Sheet.

open apipheny

Tip: you can open a new Google Sheet by entering this URL in your browser: sheet.new

Step 2.) Create a Development Store

1. Next, log in to your Shopify account, click the user icon, then choose All stores:

2. Click the +Create store button:

3. You can complete the data they ask about your business and click Next or you can click Skip All:

4. We clicked to Skip All so we are choosing the Country and click the Next button:

5. Choose an account:

6. Your store is now created:

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

1. From your Shopify admin Store dashboard, click the Settings button:

Then click the Apps and sales channels item from the left menu:

In this section click the Allow custom App development button:

On the next screen click the black Allow custom app development button:

Now the Create an app button is enabled and you can click it:

Complete all the fields from the Create an app window and then click the black Create app button:

Your app is now created:

Click the Configuration tab to choose all the access scopes you need for your request and then click the Save button:

Your scopes are now added to your app:

Install the app by clicking the black Install app button:

Confirm the installation by clicking the Install button on the next screen:

Click the API credentials tab to find the access token, the API key and secret:

Note: Your access token can be revealed only once. Make sure to sav it in a safe location!

Step 4.) Choose a Shopify API 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

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.

Orders documentation
Orders documentation

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.

An example endpoint is Retrieve a list of orders, which gets a list of all orders. The URL for this endpoint is:

https://store_name.myshopify.com/admin/api/admin/api/2022-01/orders.json?status=any 

If you use this url, don’t forget to replace store_name with the name of your store.

The documentation for each endpoint contains a short description, the HTTP method used (eg. GET), a description of the available parameters, the cURL command, an example response and the link you should append to the root API URL for querying this endpoint (eg. /admin/api/2022-01/orders.json?status=any).

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

Shopify API endpoints documentation

Shopify API base URL

https://store_name.myshopify.com/admin/api/

All API calls to Shopify should be made with the https://store_name.myshopify.com/admin/api/ base domain.

Shopify API pagination

Manual pagination

Pagination is a process that is used to divide a large dataset into smaller chunks (pages). Usually the endpoints that return a list of resources support pagination. Some of the Shopify API endpoints use the pagination option. This means you need to add some of these parameters at the end of the URL:

  • limit – which is ≤ 250 and default 50, returns up to this many results per page.
  • page_info – A unique ID used to access a certain page of results. The page_info parameter can’t be modified and must be used exactly as it appears in the link header URL.
  • fields – a comma-separated list of which fields to show in the results. This parameter only works for some endpoints.
Example: 
Request:
GET https://{shop}.myshopify.com/admin/api/2019-07/products.json?limit=3&collection_id=841564295

Response header:
#...
Link: "<https://{shop}.myshopify.com/admin/api/2019-07/products.json?page_info=hijgklmn&limit=3>; rel=next"
#...

The response header returns a link header that includes a URL for the next page of results. This page will have 3 records per page because we set the limit parameter value to 3. To get the next page of results, you can make a request to the URL stored in the link header of the last response:

Request:
GET https://{shop}.myshopify.com/admin/api/2019-07/products.json?page_info=hijgklmn&limit=3

Response header:
#...
Link: "<https://{shop}.myshopify.com/admin/api/2019-07/products.json?page_info=abcdefg&limit=3>; rel=previous, <https://{shop}.myshopify.com/admin/api/2019-07/products.json?page_info=opqrstu&limit=3>; rel=next"
#...

The response header includes a link to the previous page of results and a link to the next page. This page will also have 3 records per page. The next 3 records after the first three from the first page. You can use the URLs in the link headers to iterate through each page of results. If you make a call to a page and the response header only includes a link to the previous page, then you’ve reached the last page of results.

Automated pagination

In Apipheny, in the Pagination section, you can choose the Next Page pagination type for the Shopify API.

This is an example where the fields are completed with the corresponding values for the GET https://{shop}.myshopify.com/admin/api/2019-07/products.json?limit=3&collection_id=841564295 request:

Next Page Path:
the_next_page_link

Run Until:
Response Field Empty

Response Field:
products

the_next_page_link – represents the link for the next page of results returned in header.

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, POST, PUT, PATCH or DELETE) 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).

Example endpoint 1

For this example, we are using the Shop API endpoint, to get data on the shop’s configuration:

https://store_name.myshopify.com/admin/api/2022-01/shop.json

If you want to use this same example API URL, just replace store_name 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_Admin_API_Password

Your_Admin_API_Password is your private app’s Admin API password that you copied previously, in Step 3.7.

Here’s what it should look like if you’ve entered everything correctly in Apipheny:

Shopify API URL and headers entered into Apipheny - Shop endpoint
Shopify API URL and headers entered into Apipheny – Shop endpoint

After you’ve entered these details, just click the Run button in Apipheny to import the data.

Example endpoint 2

Another example is Retrieve a list of locations endpoint, which retrieves a list of locations:

https://store_name.myshopify.com/admin/api/2022-01/locations.json

The steps are the same as the previous endpoint example. Just replace store_name with your store name and in the Headers section of Apipheny, add one row with the following key and value:

Key:
X-Shopify-Access-Token
Value:
Your_Admin_API_Password

After you’ve entered these details, just click the Run button in Apipheny to import the data.

Shopify API URL and headers entered into Apipheny - Retrieve a list of locations endpoint
Shopify API URL and headers entered into Apipheny – Retrieve a list of locations endpoint

Example endpoint 3

This is the endpoint that allows you to “Retrieve a specific order”:

https://store_name.myshopify.com/admin/api/2022-01/order_id.json?status=any

If you try this endpoint, don’t forget to replace order_id and store_name with real values. Also don’t forget to use the same headers as the previous examples.

That’s it! You’ve learned how to use the Shopify API and connect it to your Google Sheets using Apipheny.

After making a successful request, try using the other endpoints from the Shopify API documentation or try using the other features of the Apipheny add-on.



Popular API Tutorials


API Knowledge

What is an API?

What is an API URL?

What are parameters?

What is an endpoint?

What is an API key/token?

What is basic authentication?

What are headers?

What is a GET request?

What is a POST request?

Import JSON to Google Sheets


SEO by SearchCyrus