喺 Google Sheets 攞即時匯率

// 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
}

// 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

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