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.
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:
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:
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.
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 root domain/base URL:
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:
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.
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:
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:
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:
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: