Connect Shopify to Google Sheets - API Integration [Tutorial]

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:

Install the Apipheny add-on
Create a Shopify Development Store
Create a Private App and Obtain an API Key
Choose your Shopify API endpoint
Enter your Shopify API request into Apipheny

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

Apipheny is an API connector for Google Sheets. You can use Apipheny to connect your Google Sheets to unlimited API data sources, make unlimited API requests, and more. There is a 30 day free trial included.

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.

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 and click on Stores in the menu on the left:

2. Click the Add store button:

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

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

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:

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

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:

6. Your private app has now been created:

7. Next, scroll down until you see the Admin API section and there you’ll find your Admin API Password, along with your API key, Shared Secret, and the Storefront access token:

Shopify API key, password, and shared secret

Make sure to copy your Password and keep it somewhere safe, because you’ll need it to make your request in step 5.

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:

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.

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 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 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).

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.