أسعار صرف حية في Google Sheets

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

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