Track currency and crypto exchange rates

Northwind invoices clients in three currencies and holds a small amount of crypto as a curiosity. When the accountant closes the books each quarter, the first question is always the same: what was the exchange rate on the day? Live rate widgets only show today’s number, and chasing historical rates after the fact is fiddly and never quite authoritative.

This script logs the rates Northwind cares about every day, appending one row per pair to a sheet. Over time that sheet becomes a tidy historical record — no gaps, no guesswork — that the accountant can look up by date whenever a conversion needs justifying.

What you’ll need

• A Google Sheet to hold the rate log. The script appends to its first tab, so add a header row (date, from, to, rate) once before the first run.
• No API key — exchangerate.host serves the conversion endpoint used here without authentication.
• The sheet ID and the list of pairs you want to track, set in the config block below.

The script

// The currency pairs to log, each as [from, to]. Crypto codes work
// here too — BTC/USD is included as an example.
const PAIRS = [
  ['GBP', 'USD'],
  ['GBP', 'EUR'],
  ['BTC', 'USD'],
];

// The spreadsheet that holds the rate log.
const RATES_SHEET_ID = '1abcRatesId';

/**
 * Fetches the current rate for each tracked pair and appends one
 * timestamped row per pair to the rates sheet.
 */
function logRates() {
  const sheet = SpreadsheetApp.openById(RATES_SHEET_ID).getSheets()[0];

  // Walk each pair and log its current rate.
  for (const [from, to] of PAIRS) {
    // 1. Ask the API to convert one unit of `from` into `to`.
    const url = 'https://api.exchangerate.host/convert?from=' + from +
      '&to=' + to;
    const response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });

    // 2. Skip this pair if the request failed — one bad pair should
    //    not stop the rest of the run.
    if (response.getResponseCode() !== 200) {
      Logger.log('Failed to fetch ' + from + '/' + to + ' — skipping.');
      continue;
    }

    // 3. Pull the converted rate out of the response.
    const result = JSON.parse(response.getContentText()).result;
    if (result == null) {
      Logger.log('No rate returned for ' + from + '/' + to + ' — skipping.');
      continue;
    }

    // 4. Append a timestamped row: when, the pair, and the rate.
    sheet.appendRow([new Date(), from, to, result]);
  }
  Logger.log('Rate logging complete.');
}

How it works

1. logRates opens the rates sheet and loops over PAIRS, the list of currency and crypto pairs you want a daily record of.
2. For each pair it calls the exchangerate.host convert endpoint, which returns the rate for one unit of the from currency in the to currency.
3. muteHttpExceptions lets the script handle failures itself. If one pair’s request fails — a typo in a code, a brief outage — it logs a note and continues, so the other pairs still get logged.
4. It also guards against a missing result (the API returns null for an unknown pair) rather than writing an empty cell.
5. A good row gets appended with appendRow: a timestamp, the two currency codes, and the rate. Because rows are appended, the sheet grows into a complete history instead of being overwritten.

Example run

Run the script on 25 May 2026 and three rows are appended to the log:

date	from	to	rate
2026-05-25 00:00:01	GBP	USD	1.2684
2026-05-25 00:00:02	GBP	EUR	1.1731
2026-05-25 00:00:03	BTC	USD	67432.50

Run it again the next day and three more rows appear below. After a quarter the sheet holds roughly 270 rows — a date-stamped record the accountant can filter to any single day.

Trigger it

This is a daily logging job, so drive it with a time trigger:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Add a trigger: choose logRates, event source Time-driven, Day timer, set to run around midnight.
3. Save and approve the authorisation prompt the first time.

Once running, the script appends a fresh set of rows every night without anyone touching it. Pick a consistent time so the daily snapshots line up.

Watch out for

• The rate is whatever the API reports at the moment of the call. For accounting you usually want a daily close or reference rate — a midnight run is a reasonable proxy, but confirm it matches the basis your accountant uses.
• exchangerate.host is a free service with no uptime guarantee. The per-pair guard means a bad day skips a row rather than crashing, but check for gaps if the log feeds anything official.
• Crypto rates move fast and vary between sources. A once-a-day BTC figure is fine for a rough record; it is not a trading price.
• The sheet only ever grows. That is the point, but on a multi-year log consider archiving older rows to a separate tab to keep the active sheet quick to open.
• If the free endpoint ever requires a key, switch to a provider that issues one and store it in Script Properties rather than pasting it into the code.