Search API Connector Documentation
Import Shopify Data to Google Sheets
In this guide, we’ll walk through how to pull data from the Shopify API directly into Google Sheets, using the API Connector add-on for Sheets.
We’ll first get an API admin token from Shopify, and then set up a request to pull in data from your Shopify store to your spreadsheet.
The nice thing about this method is that you can access the entire set of functionality available via Shopify’s API, from pulling in transactions to updating product specs.
- Before You Begin
- Part 1: Get your Shopify Admin API Access Token
- Part 2: Create your API Request URL
- Part 3: Pull Shopify API Data to Sheets
- Part 4: More Example API URLs
- Part 5: Handle Pagination
- Part 6: Handle Filtering
- Part 7: Troubleshooting
- Part 8: API Documentation
- Appendix [PRO]: Get the Shopify Report Pack
Before You Begin
Click here to install the API Connector add-on from the Google Marketplace.
Part 1: Get Your Shopify Admin Access Token
- Log into your Shopify store and click Apps
- From here, click Develop apps
- From here, click the Create an app button
- You’ll see a modal prompting you to name your app and assign an app developer. Give it a name and click Create app.
- You’ll now be on your app overview screen. Click Configure Admin API scopes.
- The next section is where you search for and select which data points you’d like to have access to via the API (check the documentation on scopes for more information).
Most likely, you will want access to one or more of the following: read_orders, read_products, read_inventory, and read_customers.
- Once you’ve selected all the scopes you want to access, click Save. (You can add more scopes later if you need to)
- Head over to the API credentials tab and click Install app.
- Confirm the installation and you should now see a screen containing your Admin API access token. Click Reveal token once, and store it safely as we’ll need it soon. Congrats, you’re done! You now have access to the Shopify API.
Part 2: Create Your Shopify API Request URL
We’ll first retrieve a list of orders from your store. This is the URL:
Part 3: Pull Shopify API Data into Sheets
Now let’s paste that URL into API Connector.
- Open up Google Sheets and click Extensions > API Connector > Open.
- In the Create screen, enter the Request URL we just created.
- Under Headers, enter X-Shopify-Access-Token as your Key, and your Admin API token as the Value, like this:
It should look like this:
- Create a new tab and click Set current to use that tab as your data destination.
- (Optional) Under Output options, select the grid report style. This will help reduce the number of columns in your sheet and speed up this first request.
- Name your request and click Run. A moment later you’ll see your Shopify data populate your Google Sheet:
- You’ll notice that Shopify returns a huge number of fields by default. Check below for information on filtering.
- Toggle between the “single row (default)” and “grid” report styles (located under Output Options) to see which output style you prefer. The single row style puts each order ID in its own row, while grid style repeats the order ID for each line item.
Part 4: More Example API URLs
You can check the documentation for the full list of available API requests, but if you just want to jump in, you can play around with the URLs you enter in the API URL path field. Try the following (one at a time).
These sample URLs can also be accessed via the API library (just search for “Shopify” within API Connector).
- get a list of products in your Shopify store
The above example uses ‘grid’ style to put all product variants into the same column. The default ‘single row’ style would produce one row per product ID, with each product variant in a separate column.
- get a count of products in your store
- get product info with prices, skus, inventory item IDs and other fields
- get customer info (emails, names, addresses)
Part 5: Handle Pagination
- By default, Shopify limits the number of responses returned at once, usually to 50 records at a time.
To get more, use the ‘limit’ parameter as shown above, and / or combine it with “since_id” or date-based parameters.
- Instead of editing these fields manually, you can loop through and grab all your data automatically with pagination handling (paid feature), like this:
- API URL: enter your request URL, including the parameter limit=250
- Pagination type:
next page URL
- Next page path:
- Run until: choose when to stop fetching data
Part 6: Handle Filtering
- Shopify returns a huge amount of data from their orders endpoint, often 100+ distinct columns. Besides being more data than you need, this slows down your report and can cause your requests to time out due to Google Sheets’ 6-minute response time limit. Therefore, it’s helpful to pare down the fields you request, which you can do with the fields parameter, like this:
If you need information about the line items in each order, include the “line_items” field (though be aware it produces many additional columns).
- To get new data only, use Shopify’s since_id or date-based parameters. For example, you could create a tab called MaxID containing the function
=max(Orders!A:A). Then use that cell in your request URL and pull in new data only with a request URL like
- In addition to Shopify’s field filtering, API Connector provides its own filtering through the visual field editor. Just click Edit Fields and choose the fields you’d like to see in your sheet.
- All filtering types can be used together. Shopify’s native filtering lets you request less data from the server, which speeds up response time from Shopify. API Connector’s field editor only filters the data once the response has been received, which gives you better control about exactly which data gets displayed and speeds up the process of printing data into the sheet.
Part 7: Troubleshooting
- If you’re only getting 50 records, please see the section on pagination.
- If fields are moving around, please see the troubleshooting article on columns shifting.
- If you receive a “Failed to run request” error message, that means your request is pulling in too much data and timing out. Please see the section on adding filters, and/or reduce the number of pages you’re fetching at once.
Part 8: API Documentation
Official documentation: https://help.shopify.com/en/api/reference
Appendix [PRO]: Get the Shopify Report Pack
The Shopify report pack is a pack of pre-built reports to get you started. All you need to do is enter your own Shopify password and store name to populate the reports with your own data. It makes use of pagination handling, so you’ll need a pro account with API Connector for it to work (or install API Connector for a free trial).
The report pack includes the following:
- Products: A list of products in your store, including ID, title, variant IDs, barcode, weight, and inventory quantity
- OrderDetails (Grid): A report showing all your orders, including the line items within them. In grid mode so multiple line items appear in a single column.
- OrderDetails (Row): Same as above, but each order is listed on a new rows, so multiple line items get their own columns
- Sales Summary: A list of orders, without detail about line items
- Refunds: A report of all refunds with date and refund amount
- Overview: A simple overview matching* the metrics provided by Shopify’s Sales Summary report
*In some cases, the “refunds” metric will show a slight discrepancy.
Here is the link (click File > Make a Copy to get your own copy).