Live exchange rates in Google Sheets

To get live exchange rates in Google Sheets, paste a small Apps Script custom function that calls /v1/latest/{base} with your key in the Authorization header, then write =FXRATE("USD","EUR") in any cell. A free key covers 10,000 calls a month, no card. The function below caches for ten minutes and handles the two errors you will actually hit. It is the live FX input for a model or dashboard you keep in Sheets.

Get a free API key

Sign up at exchangerate.dev/signup. After email confirmation your dashboard shows a key starting with exr_live_. Copy it — you will paste it into Apps Script next. The free tier is 10,000 calls a month, no card required.

Open Apps Script from your sheet

Open any Google Sheet. From the menu, click Extensions → Apps Script. A new tab opens with a default function myFunction() {}. Delete its contents — you will replace them in the next step.

Paste the FXRATE function

Replace the editor contents with this function. It calls exchangerate.dev, caches each pair for ten minutes via CacheService, and returns a readable marker for the two errors you can hit (401 and 429).

// google-sheets: Apps Script Code.gs
// Replace API_KEY with your exchangerate.dev key from /signup

const API_KEY = 'exr_live_replace_with_your_key_here';
const CACHE_TTL = 600; // seconds — FX moves slower than equities; 10 min is plenty

function FXRATE(from, to) {
  from = (from || 'USD').toString().toUpperCase();
  to = (to || 'EUR').toString().toUpperCase();

  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}`;
  const resp = UrlFetchApp.fetch(url, {
    headers: { Authorization: `Bearer ${API_KEY}` },
    muteHttpExceptions: true,
  });

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

  const data = JSON.parse(resp.getContentText());
  const rate = data.rates && data.rates[to];
  if (rate == null) return '#NO_PAIR'; // unknown currency code
  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. After about a second the cell shows the current rate. 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

Under the hood each call returns the base, the pair you filtered for, and two freshness fields — source and market_session — so you always know how current the number is:

{
  "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

Apps Script does not re-run custom functions on a timer by itself. For periodic updates, add a time-driven trigger: in the Apps Script left rail, click Triggers → Add Trigger, choose FXRATE, source Time-driven, Minutes timer. The ten-minute cache plus the generous quota keeps a busy workbook safe:

• 10,000 calls a month, 12 requests a minute, no card
• CacheService returns the cached rate for 10 minutes, so repeated edits cost nothing
• Raise CACHE_TTL if a sheet with many pairs ever brushes the per-minute cap
• Covers /v1/latest, /v1/convert, /v1/range, and /v1/{date}/{base}