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:
- In Sheets, open API Connector and create a new request (Extensions > API Connector > Open > Create request)
- Select Shopify from the drop-down list of applications
- Under Authorization, enter your API token
- Select the
/products
endpoint - Under the shop parameter, enter your shop name. For example, if your admin URL is
https://oj-stand.myshopify.com/admin
, your shop name isoj-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. - Select the fields you'd like to see in your report. Make sure to include at least the variants field.
- Under Output options, set the Report style to grid mode.
- 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:
- Create a new tab, let's call it InventoryIds
- In that tab, join all the inventory IDs together with the following Sheets function:
=join(",",Products!V2:V110)
. ReplaceV2:V110
with the column containing thevariants.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/2023-10/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.