Create a daily price-history logger

Northwind pays for a stack of subscription tools, and their prices drift. A plan creeps up at renewal, a tier gets repackaged, a discount quietly expires. By the time finance notices, there is no record of what the old price actually was — so there is no way to prove the increase, let alone push back on it.

This script fixes that by snapshotting prices every day. It calls a small price endpoint for each tool, appends one dated row per service to a history sheet, and never overwrites anything. Leave it running and within a few weeks you have a clean time series — the baseline you need to spot a rise the moment it lands.

What you’ll need

• A history spreadsheet. Its first tab should have three header columns — date, service, price — in row 1.
• A price endpoint per tool that returns JSON containing a price field. The script reads .price from the response body.
• If those endpoints need an API key, store it in Script Properties rather than pasting it into the code — see Store API keys and secrets securely.

The script

// The spreadsheet that holds the price-history time series.
const HISTORY_SHEET_ID = '1abcPriceHistoryId';

// The tools to track. Each entry needs a display name and a price
// endpoint that returns JSON with a numeric "price" field.
const TOOLS = [
  { name: 'Figma', url: 'https://api.example.com/figma/price' },
  { name: 'Notion', url: 'https://api.example.com/notion/price' },
];

/**
 * Fetches today's price for every tracked tool and appends one row per
 * tool to the history sheet.
 */
function logDailyPrices() {
  const sheet = SpreadsheetApp.openById(HISTORY_SHEET_ID).getSheets()[0];
  const today = new Date();

  // 1. Fetch a price for each tool and build one row per service.
  const rows = TOOLS.map((tool) => [today, tool.name, fetchPrice(tool.url)]);

  // 2. Append the batch below whatever is already there — never overwrite.
  sheet
    .getRange(sheet.getLastRow() + 1, 1, rows.length, 3)
    .setValues(rows);

  Logger.log('Logged prices for ' + rows.length + ' tools.');
}

/**
 * Calls a price endpoint and returns its numeric price, or null if the
 * request fails.
 *
 * @param {string} url The price endpoint to call.
 * @return {number|null} The price, or null on any non-200 response.
 */
function fetchPrice(url) {
  const response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });

  // A failed request returns null so the row still logs, just blank.
  if (response.getResponseCode() !== 200) {
    Logger.log('Price fetch failed for ' + url);
    return null;
  }
  return JSON.parse(response.getContentText()).price;
}

How it works

1. logDailyPrices opens the history spreadsheet and grabs today’s date once, so every row in the batch shares the same timestamp.
2. It maps over the TOOLS config, calling fetchPrice for each one and building a [date, service, price] row.
3. It appends the whole batch at getLastRow() + 1 — the first empty row — so the history grows downward and nothing already logged is ever touched.
4. fetchPrice calls the endpoint with muteHttpExceptions so a server error does not abort the run. On any non-200 response it logs the failure and returns null, which lands as a blank cell rather than a missing row.

Example run

Say the history sheet already has a few days of data. After today’s run, two fresh rows are appended:

date	service	price
2025-09-22	Figma	12
2025-09-22	Notion	8
2025-09-23	Figma	12
2025-09-23	Notion	8
2025-09-24	Figma	15
2025-09-24	Notion	8

The Figma row for 24 September jumps from 12 to 15. Because every previous day is still on the sheet, that increase is now visible and dated — exactly the evidence finance needs at renewal time.

Trigger it

Run the logger once a day so the history fills itself:

1. In the Apps Script editor open Triggers (the clock icon).
2. Add Trigger → choose logDailyPrices, event source Time-driven, type Day timer, and pick the midnight to 1am slot.
3. Approve the authorisation prompt the first time.

A daily cadence is plenty — prices rarely change more than once a day, and a single snapshot keeps the sheet compact.

Watch out for

• A failed fetch logs a blank price, not a skipped row. That is deliberate — it keeps the time series unbroken — but a run of blanks means the endpoint is down, not that the price is genuinely empty. Check the execution log if you see gaps.
• The script appends unconditionally. If the trigger fires twice in one day, or you run it by hand after the scheduled run, you get duplicate rows for that date. Add a check against the last logged date if that matters to you.
• The script reads exactly .price from the JSON. If an endpoint nests its price differently — say under data.amount — adjust the line in fetchPrice to match that shape.
• Endpoints that need authentication will return 401 and log a blank. Add the key to the UrlFetchApp.fetch headers, pulled from Script Properties — see the guide linked above.
• The sheet grows by one row per tool per day forever. That is fine for years, but if you track many tools, archive older rows to a second tab once the sheet gets long.