To keep a sheet of competitor prices current without opening it, use an Apps Script on a daily trigger rather than a formula. Every morning the script fetches each product URL in column A, reads the price the store publishes for Google, and writes it back with a status. When a price moves, it adds the old and new price to a Changes tab.
The script is free to copy and takes four steps to set up. Each check runs through LurkAPI: ten competitor pages, one of them a JavaScript-heavy page, cost 10 credits a day, which the free plan’s daily top-up covers.
IMPORTXML stops when you close the tab
IMPORTXML is the obvious first try: give it a URL and an XPath and a price lands in the cell. But Google’s help page says import functions check for updates every hour, and only while the document is open. Close the tab on Friday and nothing runs until someone opens it again, and since each refresh overwrites the cell, you never see what a competitor charged on Saturday.
It also never runs a page’s JavaScript, and some stores draw the price with it: Uniqlo’s product page arrives as a megabyte of app code with no price in it. And it can’t send a header, so an API key would sit in the formula for every editor to read. A custom function such as =PRICE(A2) doesn’t help: Google stops it after 30 seconds and recalculates it only when you edit it or the cell it reads.
A time-driven trigger runs a function on a schedule under your Google account, with the sheet closed. You pick the hour, Google picks a minute inside it and keeps it from day to day, and you get an email if a run fails. The quotas leave plenty of room: a run can last 6 minutes, and a free Google account gets 20,000 URL fetches a day.
The first dollar sign on a page is rarely the price
The quick way to pull a price out of HTML is a pattern for the first dollar amount. On 6 of the 10 competitor pages in the sheet above, that amount was something else: a shipping fee, a free-shipping minimum, a menu link to “Home essentials under $10”, a number inside the page’s JavaScript.
Read the number the store writes for Google instead. Product pages carry a block of structured data, JSON-LD, so they can show up in Google’s shopping results, and Google defines its offers.price as “the current, active offer price”. It’s the price Google shows next to the store’s listing, so stores keep it current. Every page in the sheet had one. Here is one page through the fetch endpoint, which returns a page’s HTML the way a browser receives it, for 1 credit:
curl -G \
https://api.lurkapi.com/v1/web/fetch \
-H "x-api-key: $LURK_KEY" \
--data-urlencode \
"url=https://www.adafruit.com/product/4295"{
"@type": "Product",
"name": "Raspberry Pi 4 Model B - 1 GB RAM",
"offers": {
"@type": "Offer",
"availability": "http://schema.org/OutOfStock",
"price": 38.5,
"priceCurrency": "USD"
}
}Stores vary the details: Logitech publishes an AggregateOffer with a lowPrice, others tuck the price into priceSpecification. The script checks each of those, and on a page without JSON-LD it tries og:price:amount meta tags, then an amount inside an element whose class mentions price. The same block also says whether the item is in stock (Adafruit’s Pi 4 said OutOfStock), handy if you also want to know when a competitor sells out.
A few stores only add the block once their JavaScript runs. Uniqlo’s T-shirt page is one: a plain fetch finds no price at all. Tick Render on that row and the script calls the render endpoint instead, where a browser loads the page first, for 1 credit. Keep the format at HTML, the default: markdown and text drop the page head and scripts, and the JSON-LD goes with them.
Set it up once, then leave it
- Open a new sheet (
sheets.new) and choose Extensions > Apps Script. Delete the starter code, paste the script below and save. - Make a key for this sheet in your dashboard; new accounts start with 100 credits. In the Apps Script editor, open Project Settings, add a script property named
LURK_KEYand paste the key as its value. - Pick
setupin the function menu next to Run, click Run and allow access when Google asks. It adds a Prices tab, a Changes tab and the daily trigger, set for 6 to 7 am. - Paste product URLs into column A of Prices, from A2 down, and run
checkPricesonce to fill the sheet now. Where Status saysno price found, tick Render and run it again. Stick to pages anyone can see logged out, and check a store’s terms before you add it (is web scraping legal?).
The script: Code.gs, 137 lines
// Competitor price tracker: a Google Sheet that checks product pages through LurkAPI on a schedule.
// Setup: Project Settings > Script properties > add LURK_KEY = your API key. Then run setup() once.
const API = 'https://api.lurkapi.com/v1/web/';
const SHEET = 'Prices';
const LOG = 'Changes';
const HEADERS = ['URL', 'Render', 'Price', 'Currency', 'Source', 'Status', 'Checked at', 'Credits'];
const MAX_RUN_MS = 4 * 60 * 1000; // Apps Script stops a run at 6 minutes; a render can take 90 s
const SYMBOLS = { '$': 'USD', '€': 'EUR', '£': 'GBP' };
function setup() {
const ss = SpreadsheetApp.getActive();
const sheet = ss.getSheetByName(SHEET) || ss.insertSheet(SHEET);
sheet.getRange(1, 1, 1, HEADERS.length).setValues([HEADERS]).setFontWeight('bold');
// A checkbox rule on all of column B: existing ticks stay as they are.
sheet.getRange('B2:B').setDataValidation(SpreadsheetApp.newDataValidation().requireCheckbox().build());
const log = ss.getSheetByName(LOG) || ss.insertSheet(LOG);
log.getRange(1, 1, 1, 5).setValues([['Time', 'URL', 'Old price', 'New price', 'Currency']]).setFontWeight('bold');
ScriptApp.getProjectTriggers()
.filter((t) => t.getHandlerFunction() === 'checkPrices')
.forEach((t) => ScriptApp.deleteTrigger(t));
ScriptApp.newTrigger('checkPrices').timeBased().everyDays(1).atHour(6).create();
}
function checkPrices() {
const key = PropertiesService.getScriptProperties().getProperty('LURK_KEY');
if (!key) throw new Error('Add LURK_KEY in Project Settings > Script properties');
const ss = SpreadsheetApp.getActive();
const sheet = ss.getSheetByName(SHEET);
const count = sheet.getLastRow() - 1;
if (count < 1) return;
const rows = sheet.getRange(2, 1, count, HEADERS.length).getValues();
// Rows checked longest ago go first, so a long list catches up over several runs.
const order = rows.map((r, i) => i).filter((i) => rows[i][0]).sort((a, b) => (rows[a][6] || 0) - (rows[b][6] || 0));
const started = Date.now();
for (const i of order) {
if (Date.now() - started > MAX_RUN_MS) break;
const [url, render, oldPrice] = rows[i];
const r = check(String(url).trim(), render === true, key);
if (r.price != null && oldPrice !== '' && Number(oldPrice) !== r.price) {
(ss.getSheetByName(LOG) || ss.insertSheet(LOG)).appendRow([new Date(), url, oldPrice, r.price, r.currency]);
}
// A failed check keeps the last good Price, Currency and Source together; Status says what went wrong.
const found = r.price != null ? [r.price, r.currency, r.source] : rows[i].slice(2, 5);
sheet.getRange(i + 2, 3, 1, 6).setValues([[...found, r.status, new Date(), r.credits]]);
}
}
// One page through the API: /v1/web/fetch, or /v1/web/render (a real browser) when Render is ticked.
function check(url, render, key) {
let res;
try {
res = UrlFetchApp.fetch(API + (render ? 'render' : 'fetch') + '?url=' + encodeURIComponent(url), {
headers: { 'x-api-key': key },
muteHttpExceptions: true,
});
} catch (e) {
return { status: 'error: ' + e.message, credits: 0 };
}
let body;
try {
body = JSON.parse(res.getContentText());
} catch (e) {
return { status: 'HTTP ' + res.getResponseCode(), credits: 0 };
}
const credits = body.credits_charged || 0;
if (!body.success) return { status: body.code, credits }; // an error code such as upstream_error: free
if (body.status >= 400) return { status: 'page ' + body.status, credits };
let found;
try {
found = findPrice(body.content || '');
} catch (e) {
return { status: 'error reading page: ' + e.message, credits }; // this row only; the run goes on
}
if (!found || found.price == null) return { status: 'no price found', credits };
return { price: found.price, currency: found.currency, source: found.source, status: 'ok', credits };
}
// Structured data first (what the store publishes for search engines), then a pattern.
function findPrice(html) {
const blocks = html.match(/<script[^>]*application\/ld\+json[^>]*>[\s\S]*?<\/script>/gi) || [];
for (const block of blocks) {
let data;
try {
data = JSON.parse(block.replace(/^<script[^>]*>|<\/script>$/gi, ''));
} catch (e) {
continue;
}
const offer = findOffer(data);
if (offer) return { price: toNumber(offer.price), currency: offer.currency, source: 'json-ld' };
}
const amount = meta(html, 'og:price:amount') || meta(html, 'product:price:amount');
if (amount) {
const currency = meta(html, 'og:price:currency') || meta(html, 'product:price:currency');
return { price: toNumber(amount), currency: currency, source: 'meta' };
}
// The first amount inside an element whose class mentions "price".
const m = html.match(/class="[^"]*price[^"]*"[^>]*>\s*(?:<[^>]+>\s*)*([$€£])\s*(?:<[^>]+>\s*)*(\d[\d.,]*)/i);
if (m) return { price: toNumber(m[2]), currency: SYMBOLS[m[1]], source: 'pattern' };
return null;
}
// The first offer with a price anywhere in a JSON-LD tree (Product, ProductGroup, @graph...).
function findOffer(node) {
if (Array.isArray(node)) {
for (const n of node) {
const o = findOffer(n);
if (o) return o;
}
return null;
}
if (!node || typeof node !== 'object') return null;
for (const o of [].concat(node.offers || [])) {
const spec = [].concat(o.priceSpecification || [])[0] || {};
const price = o.price != null ? o.price : o.lowPrice != null ? o.lowPrice : spec.price;
if (price != null && price !== '') return { price: price, currency: o.priceCurrency || spec.priceCurrency || '' };
}
for (const k in node) {
const o = typeof node[k] === 'object' ? findOffer(node[k]) : null;
if (o) return o;
}
return null;
}
function meta(html, name) {
const tag = html.match(new RegExp('<meta[^>]+(?:property|name)=["\']' + name + '["\'][^>]*>', 'i'));
const content = tag && tag[0].match(/content=["']([^"']*)["']/i);
return content ? content[1].trim() : '';
}
// "1,299.00", "1.299,00" and "25" become numbers; "Call for price" becomes null, not 0.
function toNumber(s) {
s = String(s).replace(/[^\d.,]/g, '');
s = /,\d{1,2}$/.test(s) ? s.replace(/\./g, '').replace(',', '.') : s.replace(/,/g, '');
const n = Number(s);
return /\d/.test(s) && isFinite(n) ? n : null;
}
Each morning it starts with the rows checked longest ago and writes each one as soon as it’s done. It starts no new row after 4 minutes, since Apps Script ends a run at 6, so a list too long for one morning catches up over the next few. A failed check keeps the last good price and writes the reason in Status. A price that differs from the one in the cell adds a row to Changes: time, URL, old price, new price and currency.
The key stays in Script Properties, out of every cell and URL. Anyone who can edit the sheet can open its script, so give the sheet a key of its own: revoking it in the dashboard stops the sheet and nothing else.
| Status | What to do |
|---|---|
ok | Nothing. Price, Currency and Source are from this check. |
no price found | The page has no price the script can read. If the price appears only after the page’s scripts run, tick Render. |
page 404 | The store’s own not-found page, usually a product taken down. Swap the URL. |
upstream_error | The store didn’t answer. The check is free, and tomorrow’s run tries again. |
invalid_api_key | The key in Script Properties is wrong or was revoked. |
insufficient_credits | The balance ran out. The next daily top-up or a credit pack refills it. |
Ten pages cost 10 credits a day
A check costs 1 credit on fetch and 1 credit on Render. Tick Render only where a plain fetch finds no price: it takes several seconds where a fetch takes about one. A check the store never answers costs nothing.
So the ten pages above come to 10 credits a day, 300 a month. Once a UTC day the free plan raises a balance below 25 back to 25, so a sheet this size runs on the top-up alone. On paid credits, at the Starter pack’s $9 for 10,000, the same ten pages cost $0.27 a month.
