Get Product Cost From Shopify API in Google Sheets

This article will walk through how to fetch product cost from the Shopify API and display it in your spreadsheet. Shopify's /products endpoints doesn't display this data automatically so we'll make a couple of API requests and a Sheets function to get the IDs we need in the right format.

Contents

• Before You Begin
• Part 1: Get Your Shopify Access Token
• Part 2: Fetch Inventory Item IDs
• Part 3: Join Inventory IDs
• Part 4: Fetch Product Costs

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

Part 1: Get Your Shopify Access Token

To access Shopify's API we'll need a token. Follow the instructions in this article to get your token: Get your Shopify Access Token. Make sure to select the read_products scope.

Part 2: Fetch Inventory Item Ids

Product costs are associated with each variant's inventory_item_id (info). To get this value, run a request to the /products endpoint as follows:

1. In Sheets, open API Connector and create a new request (Extensions > API Connector > Open > Create request)
2. Select Shopify from the drop-down list of applications
   
3. Under Authorization, enter your API token
   
4. Select the /products endpoint
   
5. Under the shop parameter, enter your shop name. For example, if your admin URL is https://oj-stand.myshopify.com/admin, your shop name is oj-stand. You can also find your shop name by navigating to your store page, right-clicking View Page Source and searching for "Shopify.shop" in the HTML source code.
6. Select the fields you'd like to see in your report. Make sure to include at least the variants field.
   
7. Under Output options, set the Report style to grid mode.
   
8. Set a destination sheet (let's call it Products), name your request, and click Run to see response data in your sheet.
   
   

Part 3: Join Inventory IDs

You now have a list of all the inventory item IDs in your sheet. To fetch their associated costs, we'll need to send them as a comma separated list to Shopify's /inventory_items endpoint. You can do that as follows:

1. Create a new tab, let's call it InventoryIds
2. In that tab, join all the inventory IDs together with the following Sheets function: =join(",",Products!V2:V110). Replace V2:V110 with the column containing the variants.inventory_item_id field.
   
   

Part 4: Fetch Product Costs

We're now ready to fetch product costs. Set up your request like this:

• Application: Custom
• Method: GET
• Request URL: https://your_site.myshopify.com/admin/api/2024-04/inventory_items.json?ids=+++InventoryIds!A1+++
• Headers: 
  • X-Shopify-Access-Token: your_token

Product Ids, product SKUs, and their associated costs will be returned in your sheet.