Build a multi-currency expense consolidator

Northwind books expenses in five currencies — a contractor invoice in euros, a software subscription in dollars, travel in whatever the local currency was. The finance team cannot compare or total any of it until everything is in one currency, and converting each line by hand against the day’s rate is both slow and inconsistent.

This script consolidates the lot. It walks the expenses sheet, converts every amount into GBP using a live exchange-rate lookup, and writes the GBP figure into its own column. With one run the whole sheet becomes summable, and the live FX function keeps the rates current.

What you’ll need

• An Expenses sheet with a header row and, at minimum, columns named currency (a three-letter code such as EUR), amount (the figure in that currency), and gbp (the converted figure, filled in by the script).
• The spreadsheet’s ID, pasted into the EXPENSES_SHEET_ID constant.
• Nothing else — the exchangerate.host endpoint used here needs no API key, and rates are cached so it is not hit on every row.

The script

// The spreadsheet that holds the expense rows.
const EXPENSES_SHEET_ID = '1abcExpensesSheetId';

// The currency every expense is normalised to.
const BASE_CURRENCY = 'GBP';

// How long a fetched FX rate stays cached, in seconds (6 hours).
const RATE_CACHE_SECONDS = 6 * 60 * 60;

/**
 * Walks the expenses sheet and writes each amount, converted to the
 * base currency, into the "gbp" column.
 */
function consolidateExpenses() {
  const sheet = SpreadsheetApp.openById(EXPENSES_SHEET_ID).getSheets()[0];

  // 1. Read the sheet and map header names to column indexes.
  const [header, ...rows] = sheet.getDataRange().getValues();
  const col = Object.fromEntries(header.map((name, i) => [name, i]));

  if (!rows.length) {
    Logger.log('No expenses to consolidate — nothing to do.');
    return;
  }

  // 2. Rebuild each row with the converted amount slotted into the gbp column.
  const out = rows.map((row) => {
    const gbp = row[col.currency] === BASE_CURRENCY
      ? row[col.amount]
      : row[col.amount] * getRate(row[col.currency], BASE_CURRENCY);
    return [
      ...row.slice(0, col.gbp),
      gbp,
      ...row.slice(col.gbp + 1),
    ];
  });

  // 3. Write the rebuilt rows back in a single call.
  sheet.getRange(2, 1, out.length, header.length).setValues(out);
  Logger.log('Consolidated ' + out.length + ' expense rows.');
}

/**
 * Returns the exchange rate from one currency to another. Rates are
 * cached for RATE_CACHE_SECONDS so the API is not hit on every row.
 */
function getRate(from, to) {
  // Same currency means a rate of 1 — no lookup needed.
  if (from === to) return 1;

  // Check the cache before reaching for the network.
  const cache = CacheService.getScriptCache();
  const key = 'fx:' + from + ':' + to;
  const hit = cache.get(key);
  if (hit) return parseFloat(hit);

  // Cache miss: fetch a fresh rate and store it.
  const res = UrlFetchApp.fetch(
    'https://api.exchangerate.host/convert?from=' + from + '&to=' + to
  );
  const rate = JSON.parse(res.getContentText()).result;
  cache.put(key, String(rate), RATE_CACHE_SECONDS);
  return rate;
}

How it works

1. consolidateExpenses opens the expenses spreadsheet and reads it in one call, splitting the header from the data rows and building a col lookup so columns are referenced by name.
2. If there are no data rows it logs a message and stops.
3. For each row it checks the currency. If it is already GBP the amount is taken as-is; otherwise it calls getRate and multiplies the amount by the rate.
4. It rebuilds the row by slicing around the gbp column — everything before it, the new GBP value, then everything after — so the rest of the row is left untouched no matter where the gbp column sits.
5. It writes all the rebuilt rows back in a single setValues call, which is far faster than writing cell by cell.
6. getRate short-circuits when both currencies match, then checks the script cache. A cached rate is reused; a miss fetches a fresh rate from exchangerate.host and stores it for six hours, so a sheet with 200 EUR rows only triggers one network call for EUR.

Example run

An Expenses sheet before a run:

date	description	currency	amount	gbp
2025-11-01	Contractor invoice	EUR	1200
2025-11-03	Software subscription	USD	49
2025-11-04	Office supplies	GBP	85

After consolidateExpenses runs, the gbp column is filled in (rates illustrative):

date	description	currency	amount	gbp
2025-11-01	Contractor invoice	EUR	1200	1032.00
2025-11-03	Software subscription	USD	49	38.71
2025-11-04	Office supplies	GBP	85	85.00

Now a =SUM() over the gbp column gives a single, comparable total for the month’s spend.

Trigger it

Rates drift through the day, so a daily re-consolidation keeps the GBP column honest:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose consolidateExpenses, a Time-driven source, a Day timer, and an early-morning slot.
4. Save, and approve the spreadsheet and URL-fetch authorisation prompt the first time.

Watch out for

• The script overwrites the gbp column on every run, recalculating against the current rate. If you need the rate used on the day an expense was booked, store that rate in its own column instead of re-converting.
• Rates are cached for six hours, so a run shortly after another uses the same rates. That is usually what you want for consistency, but it means an intra-day rate move will not show until the cache expires.
• exchangerate.host is a free service with no uptime guarantee. If a fetch fails or returns no result, that row’s conversion will be wrong or blank — add a guard around getRate if a missing rate must not corrupt the sheet.
• Every currency code in the sheet must be a valid three-letter ISO code. A typo like EU instead of EUR will return a bad rate or none at all.
• The sheet must already have a gbp column. The script writes into it by position — it does not create the column — so add the header before the first run.