Live exchange rates in Google Sheets

Paste the function below into Extensions → Apps Script and enter =FXRATE("USD","EUR") in a cell. It calls the anonymous latest-rate endpoint and caches each pair for ten minutes. Anonymous access is capped at 12 requests a minute and 100 an hour per IP. That is enough for a private workbook, but Google egress can be shared. If other people can edit the sheet, do not put an API key in its bound script; use a server-side proxy that holds the key instead.

Start without putting a key in the sheet

The latest-rate endpoint accepts an anonymous request, capped at 12 requests a minute and 100 an hour per IP. Use that for this private-sheet example. A bound Apps Script project is visible to every spreadsheet editor, so it is not a safe place for a bearer token. If the workbook is shared or feeds a product, send the request through a server-side proxy that stores the key outside Google Sheets.

Open Apps Script from your sheet

Open the sheet that will hold the rates. Choose Extensions → Apps Script. Delete the starter myFunction and replace it with the code below. The script is bound to this spreadsheet, which makes the function available without an add-on and also makes the code visible to other editors.

Paste the FXRATE function

This version calls exchangerate.dev without an Authorization header, caches each pair for ten minutes, and returns a readable marker when access, quota, or pair validation fails. The cache is script-wide, so cells requesting the same pair can usually share one response. CacheService is best-effort: Google may discard an entry before its requested expiry.

// google-sheets: Apps Script Code.gs
const CACHE_TTL = 600; // Cache successful responses for 10 minutes.

/**
 * Returns an indicative exchange rate for one currency pair.
 * @param {string} from Three-letter base currency code.
 * @param {string} to Three-letter quote currency code.
 * @return {number|string} The rate or a readable error marker.
 * @customfunction
 */
function FXRATE(from, to) {
  from = (from || 'USD').toString().trim().toUpperCase();
  to = (to || 'EUR').toString().trim().toUpperCase();

  if (!/^[A-Z]{3}$/.test(from) || !/^[A-Z]{3}$/.test(to)) return '#NO_PAIR';

  const cacheKey = `fx_${from}_${to}`;
  const cache = CacheService.getScriptCache();
  const cached = cache.get(cacheKey);
  if (cached) return Number(cached);

  const url = `https://api.exchangerate.dev/v1/latest/${from}?symbols=${to}`;
  let resp;
  try {
    resp = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
  } catch {
    return '#FETCH_ERROR';
  }

  const code = resp.getResponseCode();
  if (code === 401 || code === 403) return '#ACCESS_ERROR';
  if (code === 429) return '#RATE_LIMIT';
  if (code !== 200) return '#ERROR';

  let data;
  try { data = JSON.parse(resp.getContentText()); }
  catch { return '#INVALID_RESPONSE'; }
  const rate = data && data.rates && data.rates[to];
  if (rate == null) return '#NO_PAIR';
  if (typeof rate !== 'number' || !Number.isFinite(rate) || rate <= 0) return '#INVALID_RESPONSE';
  cache.put(cacheKey, String(rate), CACHE_TTL);
  return Number(rate); // coerce so cells sum and sort
}

Use =FXRATE() in any cell

Back in the sheet, type =FXRATE("USD","EUR") in an empty cell. The first uncached call may take a few seconds because Sheets has to run Apps Script and make the external request. The same function works for any supported pair; swap the two currency codes:

// Euro per US dollar
=FXRATE("USD", "EUR")        // 0.9245

// Japanese yen per US dollar
=FXRATE("USD", "JPY")        // 157.32

// British pound per euro
=FXRATE("EUR", "GBP")        // 0.8531

// Indonesian rupiah per US dollar
=FXRATE("USD", "IDR")        // 16285.0

The API response includes the base, requested pair, timestamp, and freshness fields such as source and market_session. The values below illustrate the response shape; a live call returns current values and timestamps. Keep the metadata when you need to explain when a number was observed:

{
  "result": "success",
  "base": "USD",
  "source": "live",
  "market_session": "open",
  "timestamp": "2026-06-29T09:14:02Z",
  "data_updated_at": "2026-06-29T09:14:00Z",
  "rates": { "JPY": 157.32 },
  "sources": { "JPY": "live" },
  "notice": "Indicative rates, not for settlement."
}

Refresh and free-tier fit

A custom function recalculates when Sheets decides its inputs changed; it is not a live streaming feed, and a time-driven trigger does not automatically refresh every formula cell. For a dashboard that must refresh on a schedule, use a separate trigger function that fetches the required pairs and writes values into a range. For ordinary models, recalculate the sheet or edit an input when you need a fresh uncached value. The ten-minute cache and free quota are usually enough for a small workbook:

• Anonymous access: 12 requests a minute and 100 an hour per IP
• CacheService usually reuses the rate for ten minutes, though Google may evict it early
• Google may route multiple users through shared egress, so the effective anonymous capacity can vary
• Use a server-side proxy with a free key when a shared workbook needs a quota that belongs to your account