Create a live currency-conversion function

Northwind invoices clients in GBP, but it pays suppliers in USD and a couple of contractors in EUR. Every time someone needs to compare a quote across currencies, they open a browser tab, look up the rate, and paste a number into a cell — where it quietly goes stale the moment the market moves.

This automation replaces that habit with a single formula. =FX() is a custom function that takes an amount and two currency codes, fetches a live exchange rate, and returns the converted figure inline. The rate is cached for six hours so a sheet full of conversions does not hammer the rate API or slow down on every recalculation.

What you’ll need

• A Google Sheet with the amounts you want to convert in a column — Northwind keeps invoice totals in column B.
• No setup tabs and no API key — exchangerate.host serves rates without one.
• The function pasted into the script editor (Extensions → Apps Script) so the spreadsheet can see =FX() as a formula.

The function

// How long a fetched rate stays cached, in seconds. Six hours is plenty
// for invoicing — see "Watch out for".
const FX_CACHE_SECONDS = 6 * 60 * 60;

// The free rate API. No key required; it is rate-limited, which the cache
// is there to soften.
const FX_API = 'https://api.exchangerate.host/convert';

/**
 * Converts an amount from one currency to another at the latest rate.
 * Type =FX(B2, "GBP", "USD") into a cell.
 *
 * @param {number} amount The figure to convert.
 * @param {string} from The source currency code, e.g. "GBP".
 * @param {string} to The target currency code, e.g. "USD".
 * @return {number} The converted amount, or "" if any input is missing.
 * @customfunction
 */
function FX(amount, from, to) {
  // Bail out quietly on empty cells so a half-filled row shows nothing
  // rather than an error.
  if (!amount || !from || !to) return '';

  // Currency codes are case-insensitive for the caller's convenience.
  const rate = getRate(from.toUpperCase(), to.toUpperCase());
  return amount * rate;
}

/**
 * Returns the exchange rate between two currency codes, reading from the
 * script cache first and only calling the API on a miss.
 *
 * @param {string} from Upper-case source currency code.
 * @param {string} to Upper-case target currency code.
 * @return {number} The rate to multiply a "from" amount by.
 */
function getRate(from, to) {
  // Same currency on both sides — no lookup needed.
  if (from === to) return 1;

  // 1. Check the cache. A hit avoids the network call entirely.
  const cache = CacheService.getScriptCache();
  const key = `fx:${from}:${to}`;
  const hit = cache.get(key);
  if (hit) return parseFloat(hit);

  // 2. Cache miss — fetch the live rate.
  const res = UrlFetchApp.fetch(`${FX_API}?from=${from}&to=${to}`);
  const rate = JSON.parse(res.getContentText()).result;

  // 3. Store it so the next conversion in the sheet reuses it.
  cache.put(key, String(rate), FX_CACHE_SECONDS);
  return rate;
}

How it works

1. FX is the function you call from the sheet. It checks that all three arguments are present and returns an empty string if not, so blank rows stay blank instead of showing #ERROR.
2. It upper-cases the currency codes, so =FX(B2, "gbp", "usd") works just as well as the canonical spelling.
3. getRate does the actual lookup. If the source and target currency match, it short-circuits and returns 1 — no network call.
4. Otherwise it builds a cache key like fx:GBP:USD and checks CacheService. On a hit it returns the cached number straight away.
5. On a miss it calls the exchangerate.host API, reads the result field, and caches it for six hours (FX_CACHE_SECONDS) before returning it.
6. Back in FX, the rate is multiplied by the amount and the converted figure lands in the cell.

Example run

Say column B holds Northwind invoice totals in GBP and you want them in USD:

Invoice	Amount (GBP)	Formula	Amount (USD)
NW-1042	1,200	=FX(B2, "GBP", "USD")	1,524.00
NW-1043	850	=FX(B3, "GBP", "USD")	1,079.50
NW-1044	2,400	=FX(B4, "GBP", "EUR")	2,808.00

The first formula triggers one API call and caches the GBP→USD rate. The second reuses the cached rate with no network call at all. The third fetches a fresh GBP→EUR rate because it is a different pair.

Use it

Type the formula straight into a cell, pointing the first argument at the cell holding the amount:

=FX(B2, "GBP", "USD")

Drag it down a column to convert a whole list. Because the rate is cached, filling a hundred rows costs at most one API call per currency pair.

Watch out for

• Rates are end-of-day, not tick-by-tick. They are fine for invoices and budgeting; do not use them for anything trading-sensitive.
• The exchangerate.host API is free but rate-limited. The six-hour cache keeps a normal sheet well under any limit — but if you delete rows and recalculate constantly, you may still see the occasional throttle.
• Custom functions cannot be triggered on a schedule. The cached rate refreshes only when the sheet recalculates after the cache expires — open the sheet or edit a cell to force it.
• An unknown or misspelled currency code makes the API return null for result, which surfaces as #NUM! in the cell. Double-check the codes if a conversion looks wrong.
• The CacheService value cap is generous for single rates, but caching is best-effort: Google may evict an entry early under memory pressure, in which case the next call simply re-fetches.