Track Competitor Prices in Google Sheets with Apps Script

Can you track competitor prices in a Google Sheet without paying for an app? Yes. Every Shopify store publishes its catalog at /products.json, Google Apps Script can fetch that file on a schedule, and a sheet can hold the results. With two short functions and one trigger you get a daily snapshot of each competitor's prices and stock, plus a tab that lists what changed since yesterday. Below are the sheet layout, the full script, how to read what it produces and the limits you will run into.

The approach suits a store that watches a handful of competitors with catalogs in the hundreds of products, not the tens of thousands. If you have not looked at a competitor's /products.json yet, read the /products.json guide first: it explains every field the script below relies on.

Setting up the spreadsheet

Create a new Google Sheet and add two tabs. Name them exactly Snapshots and Changes, because the script looks them up by name.

In row 1 of Snapshots, add these nine headers:

1. date: the day the snapshot was taken, written as yyyy-mm-dd.
2. store: the competitor's base URL.
3. handle: the product's URL slug, stable even when the title changes.
4. product: the product title as the store shows it.
5. variant_id: Shopify's numeric ID for the size, color or pack.
6. variant: the variant title, such as "Large / Black".
7. price: the current selling price.
8. compare_at: the crossed-out "was" price, empty if there is none.
9. available: yes or no, depending on whether the variant can be bought.

In row 1 of Changes, add seven headers: date, store, product, variant, change, old price, new price. That tab stays empty until the second daily run, because a change needs two snapshots to compare.

The sheet stores one row per variant because Shopify keeps prices and stock on the variant. A jacket with four sizes in two colors has eight variants, and a competitor can sell out of medium black while every other size stays in stock. If you store only one row per product, you lose exactly the partial stockouts that tell you the most about what is selling.

The variant ID goes into the sheet as text. Shopify IDs are long integers, and Sheets would otherwise round them or display them in scientific notation, which breaks the matching between days.

The script that takes a daily snapshot

Open Extensions → Apps Script from the sheet. Delete the empty function in the editor and paste the code below. Replace the two example URLs in STORES with your competitors' storefront addresses, without a trailing slash.

const STORES = [
  'https://rival-one.com',
  'https://rival-two.com',
];

function snapshotCompetitors() {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName('Snapshots');
  const today = Utilities.formatDate(new Date(), ss.getSpreadsheetTimeZone(), 'yyyy-MM-dd');
  const rows = [];
  STORES.forEach(function (store) {
    for (let page = 1; page <= 20; page++) {
      const url = store + '/products.json?limit=250&page=' + page;
      const res = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
      if (res.getResponseCode() !== 200) break;
      const products = JSON.parse(res.getContentText()).products;
      if (!products || products.length === 0) break;
      products.forEach(function (p) {
        p.variants.forEach(function (v) {
          rows.push([today, store, p.handle, p.title, String(v.id), v.title,
            Number(v.price),
            v.compare_at_price ? Number(v.compare_at_price) : '',
            v.available ? 'yes' : 'no']);
        });
      });
      Utilities.sleep(1500);
    }
  });
  if (rows.length) {
    sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, rows[0].length).setValues(rows);
  }
}

• Pagination. /products.json returns at most 250 products per request. The loop asks for page 1, page 2 and so on until a page comes back empty, with a ceiling of 20 pages (5,000 products) as a safety stop.
• Error handling. muteHttpExceptions stops the script from crashing when a store returns an error. A store that answers with anything other than 200 is simply skipped for that day.
• Pause between requests. Utilities.sleep(1500) waits a second and a half between pages. That keeps the load on the competitor's store close to one ordinary visitor and lowers the chance of being rate-limited.
• One write per run. All rows are collected in memory and written to the sheet in a single setValues call. Writing row by row is far slower and can push a long run past the execution time limit.

Run snapshotCompetitors once by hand from the editor. Google will ask you to authorize the script, because it fetches external URLs and edits your sheet. After that, the Snapshots tab should fill with one row per variant for each store. Check the row count against the store: a competitor with 120 products and an average of three variants should give you around 360 rows.

Before scheduling anything, look at whether every store actually returned data. Some Shopify stores block requests from cloud servers, and Apps Script runs on Google's servers, not your laptop. If one store is missing, open its /products.json in your own browser. If it works there but not from the script, that store is outside what this method can reach. You can also paste the address into the free store checker to see whether its public catalog is readable at all.

Turning snapshots into a list of changes

A pile of daily snapshots is hard to read. What you want each morning is a short list: what got cheaper, what got more expensive, what sold out, what came back, what is new and what disappeared. The second function compares the two most recent dates and writes those differences to the Changes tab. Paste it under the first one.

function findChanges() {
  const ss = SpreadsheetApp.getActive();
  const tz = ss.getSpreadsheetTimeZone();
  const day = v => v instanceof Date ? Utilities.formatDate(v, tz, 'yyyy-MM-dd') : String(v);
  const data = ss.getSheetByName('Snapshots').getDataRange().getValues().slice(1);
  const dates = [...new Set(data.map(r => day(r[0])))].sort();
  if (dates.length < 2) return;
  const prevDate = dates[dates.length - 2];
  const lastDate = dates[dates.length - 1];

  const prev = new Map();
  data.filter(r => day(r[0]) === prevDate)
      .forEach(r => prev.set(r[1] + '|' + r[4], r));
  const todayRows = data.filter(r => day(r[0]) === lastDate);
  const storesSeenToday = new Set(todayRows.map(r => r[1]));

  const out = [];
  todayRows.forEach(r => {
    const key = r[1] + '|' + r[4];
    const old = prev.get(key);
    if (!old) { out.push([lastDate, r[1], r[3], r[5], 'new', '', r[6]]); return; }
    if (old[6] !== r[6]) {
      out.push([lastDate, r[1], r[3], r[5], r[6] > old[6] ? 'price up' : 'price down', old[6], r[6]]);
    }
    if (old[8] !== r[8]) {
      out.push([lastDate, r[1], r[3], r[5], r[8] === 'yes' ? 'restocked' : 'sold out', '', '']);
    }
    prev.delete(key);
  });
  prev.forEach(old => {
    if (storesSeenToday.has(old[1])) {
      out.push([lastDate, old[1], old[3], old[5], 'removed', old[6], '']);
    }
  });

  const changes = ss.getSheetByName('Changes');
  if (out.length) {
    changes.getRange(changes.getLastRow() + 1, 1, out.length, 7).setValues(out);
  }
}

function dailyRun() {
  snapshotCompetitors();
  findChanges();
}

Each variant gets a key made of the store URL and the variant ID. The function loads yesterday's rows into a map, then walks through today's rows:

• A key that is in today's data but not in yesterday's is a new variant. Usually that means a new product, sometimes a new size or color added to an existing one.
• A key in both days with a different price is a price up or price down, with old and new values side by side.
• A key in both days whose availability flipped is a sold out or restocked event.
• A key left over in yesterday's map at the end is a removed variant.

Keep the storesSeenToday check. Without it, a single failed request would make the script report every product of that store as removed, and the next day as new again. With it, a store that did not answer today is left alone until it answers.

The day helper handles a quirk of Sheets. When the script writes the text 2026-11-10, Sheets turns it into a date value, and reading it back returns a JavaScript Date. Formatting both sides to the same yyyy-mm-dd string makes the comparison reliable.

Now schedule it. In the Apps Script editor, open Triggers (the clock icon), click Add Trigger, choose the dailyRun function, set the event source to Time-driven, pick Day timer and an hour window such as 6 to 7 am. From then on the sheet updates itself every morning.

A few habits make the Changes tab more useful once it starts filling:

• Add a filter to the header row and filter by change. On most days you will only care about price moves and sell-outs.
• Watch the compare_at column for the products that dropped in price. A new compare-at price next to a lower price is a visible sale. A lower price with no compare-at price is a quiet repricing, which usually means the competitor intends it to last. The difference is covered in more detail in the compare_at_price article.
• Count the rows per store per week. A competitor that suddenly produces three times its usual number of changes is restructuring something, and that week deserves a closer look at their store.

Don't react to a single row. One price drop on a single variant can be a typo fixed the next day. Five drops across one collection on the same date are a pattern.

Where a spreadsheet tracker stops working

Most of the limits come from Google's quotas and from what the public catalog contains, so a better script won't remove them.

The sheet fills up. A Google Sheet holds at most 10 million cells. Each snapshot row uses nine. Take five competitors with 400 products each and an average of four variants: that is 8,000 rows and 72,000 cells per day. At that rate the sheet hits the ceiling in about 139 days, and it gets noticeably slow long before. The practical fix is to keep only the last 30 days in Snapshots and rely on Changes as your long-term history, since it only stores what moved.

Runs have a time limit. An Apps Script execution on a regular Google account stops after six minutes. With 1.5 seconds of pause per page plus fetch time, a run covering 30 or 40 pages in total is comfortable. Add several stores with thousands of products each and the run can be cut off midway, leaving a partial snapshot that the change detection then misreads.

Once a day misses short events. A daily snapshot shows the state at one moment. A flash sale that starts at noon and ends at midnight never appears. A bestseller that sells out at 10 am and is restocked by evening looks unchanged. Apps Script can run hourly, but then the cell count grows 24 times faster and the sheet becomes unworkable within days. How often you actually need to check depends on your niche, which is covered in the frequency guide.

The public data has gaps. /products.json shows the store's base currency prices, so market-specific prices and discount codes applied at checkout do not appear. Availability is a yes or no flag, not a stock count. Unpublished products are not in the feed, so a product the competitor hides looks the same as one they deleted.

Nobody maintains it but you. When a store changes its domain, blocks cloud requests or moves off Shopify, the script quietly stops collecting data for it. Nothing tells you unless you check. The trigger also runs under your Google account, so its quotas and failures are yours to watch. Apps Script can email you when a trigger fails, which is worth enabling in the trigger settings.

The sheet works well while you watch two or three stores, check the Changes tab a few times a week and enjoy adjusting formulas. Move on from it when you catch yourself fixing the script more often than reading its output, or when the day-old view keeps costing you sales windows. If you prefer a self-hosted workflow with instant messages instead of a sheet, the n8n build uses the same data source.

StoreSentry covers the same ground as a Shopify app: it reads competitors' public catalogs every hour on every plan and reports price increases and drops, new and removed products, sell-outs and restocks by email or Telegram, with a price history per product. The free plan watches two competitor stores, which is roughly where the spreadsheet starts to need regular care.