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 password from Shopify, and then set up a request to pull in data from your Shopify store to your spreadsheet. The cool thing about this method is that you can access the complete set of the data available from the API without limit.
If you are more of a video person, feel free to drop by our YouTube Channel and watch the video tutorial as well.
The end of this article also contains a pack of preset Shopify reports (pro users only).
- Before You Begin
- Part 1: Get your Shopify API Password
- 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
- Appendix [PRO]: Get the Shopify Report Pack
- Hire a Freelancer
Before You Begin
Click here to install the API Connector add-on from the Google Marketplace.
Part 1: Get Your Shopify API Password
- Log into your Shopify store and click Apps
- On the Apps page, click “Manage private apps”
- From here, click the “Create private app” button
- On the next screen, in the App details section, enter a name for your app and an email address.
- The next section is where you select which data points you’d like to have access to via the API. You start with none, so click “Show inactive Admin API permissions” to view them.
- You’ll now see a list of available permissions. Select the permissions you’d like to have access to via the API (check the documentation on scopes for more information). Note that selecting Reports does NOT give access to the reports in your Shopify dashboard, it only allows you to create new reports that can then be accessed in the interface. Analytics also doesn’t help here as it requires an additional SDK for access.
Most likely, you will want read access to one or more of the following: Orders, Products, Customers, and Inventory.
- Click save. You’ll see a verification modal. Click “Create app” to continue.
- Your Shopify password is ready. (For our purposes you can ignore everything but the Password). Congrats, you’re done! You now have access to the Shopify API.
Part 2: Create Your Shopify API Request URL
We’ll first follow the Shopify API documentation to retrieve a list of orders from your store.
- Base URL: https://YOUR_SHOP_NAME.myshopify.com
- Endpoint: /admin/api/2021-07/orders.json
- Parameters: ?status=any
Putting it together, we get the full API Request URL. Just substitute in your own store name and the newest API version (currently 2021-07).
?status=anyparameter, Shopify will only return orders with a status of open.
Part 3: Pull Shopify API Data into Sheets
Now let’s paste that URL into API Connector to get that Shopify data.
- Open up Google Sheets and click Add-ons > API Connector > Open.
- In the Create screen, enter the Request URL we just created (by now the screenshots show an outdated API version number, just replace that with the current version).
- Under Headers, enter X-Shopify-Access-Token as your Key, and your API password 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.
- Name your request and click Run. A moment later you’ll see your Shopify data populate your Google Sheet:
- 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):
- 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, like this:
You can also combine this with date-based parameters:
When you use date parameters, it’s usually useful to reference date cells in your sheet rather than manually add dates to your request URL. You can force your date cell to Shopify’s required format with a function like
- As an alternative to changing these fields manually, you can loop through and grab all your data automatically with pagination handling (paid feature). Shopify’s documentation shows that next page URLs are provided in a field called “Link”, so you would paginate through responses like this:
- API URL: enter your request URL as usual, making sure to include limit=250
- Pagination type:
next page URL
- Field name:
- Number of pages: enter the number of pages you’d like to fetch
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:
The “line_items” field alone produces a lot of columns about individual items in each order, so if you don’t need that level of detail, just leave it out.
- In addition to Shopify’s field filtering, API Connector provides its own filtering functionality. JMESPath filtering (paid feature) lets you specify exactly which fields to pull. This is especially useful for Shopify, because Shopify only lets you drill down to one level. In other words, you can filter for
fields=line_itemsbut not query for specific elements like product name and price within the “line_items” array. With JMESPath filtering, you can create a neat, simplified orders report like this:
The above example uses ‘grid’ style to put all line items into the same column. The default ‘single row’ style would produce one row per order ID, with each line item in a separate column.
- Both 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 JMESPath filtering ony 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
- Tip: if you’re running into errors, simply type “Shopify” into API Connector’s URL field. This will produce a list of preset Shopify requests with all query parameters and pagination already applied, just click to load.
- 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.
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 JMESPath filters and 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).
Hire a Freelancer
- If you’d like assistance building custom or complex reports, please send a message and we can refer you to a freelancer experienced with Shopify, API Connector, and Google Sheets. Examples of possible reports (many others possible):
- What to Order: Uses previous order history to show how many months your stock will last and what needs to be ordered for every variant.
- What to Market: Shows how popular items are by SKU to properly advertise unpopular or popular items in campaigns.
- Shipping Status: Shows what orders have not yet been shipped. Pulls orders into an alphabetical check list to assist in reviewing and printing invoices.
- COGS: Cost-of-goods-sold report.