Shopify API to Google Sheets: Import Shopify Data [Tutorial]

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

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:

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:

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

5. Your Shopify development store is now 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:

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:

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 API key, the password, the shared secret and the 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:

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

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:

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:

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:

• Coinmarketcap data in Google Sheets
• Youtube data in Google Sheets
• Quickbooks data in Google Sheets
• SurveyMonkey data in Google Sheets
• Paypal data in Google Sheets
• Github data in Google Sheets
• Binance data in Google Sheets
• Facebook Ads data in Google Sheets
• Facebook Pages data in Google Sheets
• Hubspot data in Google Sheets
• Trello data in Google Sheets
• Asana data in Google Sheets
• Harvest data in Google Sheets
• WorldTradingData data in Google Sheets
• CoinAPI data in Google Sheets
• Mailchimp data in Google Sheets
• Typeform data in Google Sheets
• Foreign Exchange Rates data in Google Sheets
• SERP data in Google Sheets
• Riot Games data in Google Sheets
• Pipedrive data in Google Sheets
• Email Finder data in Google Sheets
• Wikipedia data in Google Sheets
• OpenWeather data in Google Sheets
• Remote Jobs data in Google Sheets
• JSON data in Google Sheets
• Discord data in Google Sheets
• Flight data in Google Sheets