如何用 Google Sheets 追蹤 Shopify 競爭商店的價格與庫存
How to track competitor prices and stock on Shopify stores in Google Sheets
Gio Rich 示範如何透過 Shopify 公開產品 feed(https://competitor-store.com/products.json)和 Apps Script,將商品、變體、價格及庫存狀態匯入 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
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) andproduct_type -
created_atandupdated_atdates, so you can spot new launches - a list of
variants, and each variant has its ownprice,compare_at_price(the "was" price, if it's on sale),skuandavailable(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
VLOOKUPon SKU against last week's tab, then subtract. Filter for anything negative. -
New products: sort by the
Createdcolumn. Anything from the last 7 days is a new launch. -
Sold out: filter
In stocktono. If a competitor's best seller is out, that's a good week to push your version of it. -
Sales: any row where
Wasis 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.
來源:DEV Community · dev.to