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

2026-11-20来源 dev.toEnglish

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 

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 

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); } 

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.

原文:https://www.a8q9.cn/en/article/how-to-track-competitor-prices-and-stock-on-shopify-stores-in-google-sheets-26c9f4.html

相关文章