DEV Community

Gio Rich
Gio Rich

Posted on

How to track competitor prices and stock on Shopify stores in Google Sheets

If you sell online, you probably keep an eye on a handful of competitor stores. Did they drop a price? Launch a new product? Sell out of something? Most founders check this by opening each store and scrolling, which takes an hour a week and still misses things.

Many competitor stores run on Shopify, and Shopify has a public product feed you can read without any scraping tools at all. This guide shows how to use it to build a simple competitor price and stock tracker in Google Sheets.

Step 1: Check if the store runs on Shopify

Open the competitor's store and add /products.json to the end of the home page address:

https://competitor-store.com/products.json
Enter fullscreen mode Exit fullscreen mode

If you see a wall of text that starts with {"products":[, the store is on Shopify and the feed is open. If you get a 404 page or a redirect, either the store isn't on Shopify or the owner has blocked the feed. In that case skip to Step 6.

Two other quick tells: right-click the page, choose View Page Source, and search for cdn.shopify.com. If it appears, it's a Shopify store.

Step 2: Understand what the feed gives you

Each product in the feed includes:

  • title, handle (the URL slug) and product_type
  • created_at and updated_at dates, so you can spot new launches
  • a list of variants, and each variant has its own price, compare_at_price (the "was" price, if it's on sale), sku and available (true or false)

That last field is the useful one for stock tracking. available: false means that size or color is sold out.

The feed returns 30 products per page by default. You can ask for up to 250 and page through the rest:

https://competitor-store.com/products.json?limit=250&page=1
https://competitor-store.com/products.json?limit=250&page=2
Enter fullscreen mode Exit fullscreen mode

Keep going until a page comes back with an empty list: {"products":[]}.

Step 3: Pull it into Google Sheets with Apps Script

Google Sheets can't read JSON with a plain formula, but a short Apps Script can. Open a new sheet, go to Extensions > Apps Script, delete what's there and paste this:

function pullStore() {
  const store = 'https://competitor-store.com'; // change this
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const rows = [['Checked', 'Product', 'Variant', 'SKU', 'Price', 'Was', 'In stock', 'Created', 'URL']];
  const now = new Date();
  for (let page = 1; page <= 20; page++) {
    const res = UrlFetchApp.fetch(store + '/products.json?limit=250&page=' + page, { muteHttpExceptions: true });
    if (res.getResponseCode() !== 200) break;
    const products = JSON.parse(res.getContentText()).products;
    if (!products.length) break;
    products.forEach(p => p.variants.forEach(v => rows.push([
      now, p.title, v.title, v.sku, v.price, v.compare_at_price || '',
      v.available ? 'yes' : 'no', p.created_at, store + '/products/' + p.handle
    ])));
    Utilities.sleep(1000); // be polite: one request per second
  }
  sheet.clearContents();
  sheet.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
}
Enter fullscreen mode Exit fullscreen mode

Change the store address on the second line, click Save, then Run. Google will ask you to approve access the first time. After a few seconds, switch back to the sheet and every product and variant is listed with its price and stock status.

Step 4: Run it on a schedule

In the Apps Script editor, click the clock icon (Triggers) on the left, then Add Trigger. Choose pullStore, event source Time-driven, and pick daily or weekly. Now the sheet refreshes itself.

If you want history instead of a fresh snapshot each time, change the script to write to a new tab each run (for example, a tab named with today's date) instead of clearing the sheet. Then you can compare two tabs side by side.

Step 5: Spot the changes that matter

A few simple checks cover most of what founders care about:

  • Price drops: in your history tab, add a column with a VLOOKUP on SKU against last week's tab, then subtract. Filter for anything negative.
  • New products: sort by the Created column. Anything from the last 7 days is a new launch.
  • Sold out: filter In stock to no. If a competitor's best seller is out, that's a good week to push your version of it.
  • Sales: any row where Was is filled in is currently discounted. Count them to see when a competitor starts a sitewide sale.

Add one tab per competitor and run the same script with a different store address. Five competitors still fit easily in one sheet.

Step 6: When this won't work

The products.json trick only works on Shopify stores with the feed left open. You'll need a real scraper when:

  • the store runs on WooCommerce, BigCommerce, Magento or a custom build
  • the owner has blocked /products.json
  • you need data that isn't in the feed, like review counts, shipping times or bundle prices shown only on the page
  • you're tracking Amazon, Walmart or marketplace listings rather than standalone stores

Either way, keep it to public pages, go slowly (one request a second is plenty), and don't hammer a small store's site.

If you'd rather skip the setup, or your competitors aren't on Shopify, I build custom scrapers that turn any public site into a clean spreadsheet, once or on a schedule: fiverr.com/rel8ble

Written with AI assistance.

Top comments (0)