Print

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

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
    shopify-application
  3. Under Authorization, enter your API token
    shopify-authorization
  4. Select the /products endpoint
    shopifycost-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.
    shopifycost-variants
  7. Under Output options, set the Report style to grid mode.
    shopifycost-grid
  8. Set a destination sheet (let's call it Products), name your request, and click Run to see response data in your sheet.
    shopifycost-response-products

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.
    shopifycost-joinproducts
This JOIN function is set to include just 109 products because Google Sheets has a limit of 2048 characters per URL, such that joining more products will produce a "Limit Exceeded: URLFetch URL Length" error in the next section. For more products, create additional requests and run in batches.

Part 4: Fetch Product Costs

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

  • ApplicationCustom
  • MethodGET
  • Request URLhttps://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.

Leave a Comment

Jump To