Exchange rates in Google Sheets with an API

You can pull exchange rates into Google Sheets with a short Apps Script that calls the fxapi foreign exchange rates API. This guide builds a FXRATE(base, target, [date]) custom function, keeps your API key out of the sheet, adds a rates tab that refreshes on a schedule, and keeps the number of API requests low enough for the free plan.

What you’ll build

Step 1: store the API key in Script Properties

1. Sign up for a free key.
2. In your spreadsheet, open Extensions → Apps Script.
3. Click Project Settings (gear icon) → Script properties → Add script property.
4. Name it FXAPI_KEY and paste your key.

The key now lives in the script project instead of a cell, so it isn’t exported with CSV downloads or visible to viewers. Editors can still open the script project, so share edit rights accordingly.

Step 2: an FXRATE function for exchange rates in Google Sheets

Paste this into Code.gs and save:

const FXAPI_BASE = 'https://api.fxapi.com/v1/';
const CACHE_SECONDS = 6 * 60 * 60; // CacheService maximum: 6 hours

function fetchUsdRates_(date) {
  const cacheKey = 'fx_usd_' + (date || 'latest');
  const cache = CacheService.getScriptCache();
  const cached = cache.get(cacheKey);
  if (cached) return JSON.parse(cached);

  const key = PropertiesService.getScriptProperties().getProperty('FXAPI_KEY');
  if (!key) throw new Error('Add FXAPI_KEY under Project Settings → Script properties');

  const url = FXAPI_BASE + (date ? 'historical?date=' + date + '&' : 'latest?') + 'base_currency=USD';
  const res = UrlFetchApp.fetch(url, { headers: { apikey: key }, muteHttpExceptions: true });
  const status = res.getResponseCode();
  if (status !== 200) {
    const reason = {
      401: 'invalid API key',
      403: 'not included in your plan',
      422: 'invalid parameters – dates must be between 1999-01-01 and yesterday',
      429: 'rate limit or monthly quota reached',
    }[status] || res.getContentText().slice(0, 120);
    throw new Error('fxapi ' + status + ': ' + reason);
  }

  const body = JSON.parse(res.getContentText());
  const rates = { USD: 1 };
  Object.keys(body.data).forEach(function (code) { rates[code] = body.data[code].value; });
  cache.put(cacheKey, JSON.stringify(rates), CACHE_SECONDS);
  return rates;
}

/**
 * Exchange rate between two currencies: units of target per 1 unit of base.
 *
 * @param {string|Array} base   Source currency code(s), e.g. "EUR" or A2:A100
 * @param {string} target       Target currency code, e.g. "USD"
 * @param {Date} [date]         Optional date for a historical end-of-day rate
 * @return {number|Array}       The exchange rate(s)
 * @customfunction
 */
function FXRATE(base, target, date) {
  let day = null;
  if (date instanceof Date) {
    day = Utilities.formatDate(date, Session.getScriptTimeZone(), 'yyyy-MM-dd');
  } else if (date) {
    day = String(date).trim(); // text such as "2024-12-31"
  }
  const rates = fetchUsdRates_(day);

  function one(b, t) {
    if (b === '' || t === '') return '';
    b = String(b).trim().toUpperCase();
    t = String(t).trim().toUpperCase();
    if (!(b in rates)) throw new Error('Unknown currency: ' + b);
    if (!(t in rates)) throw new Error('Unknown currency: ' + t);
    return rates[t] / rates[b]; // cross rate via USD
  }

  if (Array.isArray(base)) {
    return base.map(function (row) { return row.map(function (b) { return one(b, target); }); });
  }
  return one(base, target);
}

Now use it like any built-in function:

=FXRATE("EUR", "USD")              latest EUR→USD rate
=B2 * FXRATE("USD", "EUR")         convert the USD amount in B2 to EUR
=FXRATE(A2:A200, "EUR")            one call for a whole column of currency codes
=FXRATE("USD", "JPY", DATE(2024,12,31))   historical end-of-day rate

The first call asks you to authorize the script to connect to an external service. Cross rates such as EUR→GBP are calculated from a single USD-based response, so one request serves every currency pair.

Step 3: a rates tab refreshed on a schedule

Google Sheets recalculates custom functions only when their inputs change or the file is reopened. If you need rates that update on a timetable – for dashboards or shared reports – write them to a tab with a time-driven trigger:

function refreshRatesSheet() {
  const key = PropertiesService.getScriptProperties().getProperty('FXAPI_KEY');
  const res = UrlFetchApp.fetch(FXAPI_BASE + 'latest?base_currency=EUR', {
    headers: { apikey: key },
    muteHttpExceptions: true,
  });
  if (res.getResponseCode() !== 200) {
    console.error('fxapi ' + res.getResponseCode() + ': keeping previous rates');
    return; // leave the last good rates in place
  }

  const body = JSON.parse(res.getContentText());
  const rows = Object.keys(body.data).map(function (c) { return [c, body.data[c].value]; });

  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName('Rates') || ss.insertSheet('Rates');
  sheet.clearContents();
  sheet.getRange(1, 1, 1, 4).setValues([['code', 'per_1_EUR', 'last_updated_at', body.meta.last_updated_at]]);
  sheet.getRange(2, 1, rows.length, 2).setValues(rows);
}

function installTrigger() {
  ScriptApp.newTrigger('refreshRatesSheet').timeBased().everyDays(1).atHour(6).create();
}

Run installTrigger once from the editor. Then reference the tab with normal formulas:

=XLOOKUP("USD", Rates!A:A, Rates!B:B)          EUR→USD
=C2 / XLOOKUP(D2, Rates!A:A, Rates!B:B)        amount in C2 (currency in D2) → EUR

Match the trigger to your plan’s update frequency: daily on Free, hourly on Basic (everyHours(1)), and up to every minute on Professional and Enterprise. A daily trigger uses about 30 requests a month – well within the Free plan’s 300.

Quota-friendly design

• Fetch all currencies at once. One /v1/latest call returns 190+ currencies; never call the API per cell or per row.
• Cache inside Apps Script. CacheService keeps results for up to six hours and is shared by all cells using the script.
• Historical dates cost one request each. A column of 500 different dates means 500 requests. For monthly reporting, the average exchange rates API returns a whole year of monthly averages in one request.
• Check usage with /v1/status, which doesn’t count against your quota. Validation errors (422) don’t count either.

GOOGLEFINANCE vs. an exchange rate API

Sheets’ built-in GOOGLEFINANCE("CURRENCY:EURUSD") needs no setup and is fine for quick personal lookups. Google’s GOOGLEFINANCE documentation notes that quotes may be delayed by up to 20 minutes, are provided for informational purposes, and that historical data can’t be accessed through the Sheets API or Apps Script.

An API makes sense when you need a documented timestamp for each rate (meta.last_updated_at), historical rates back to 1999 that scripts can process, precious metals and crypto alongside fiat, or the same rates your application uses. See the historical exchange rates API and features overview.

Troubleshooting

Next steps

• Prefer Excel? See exchange rates in Excel with Power Query.
• Month-end and average rates for reporting: accounting and financial reporting.
• All endpoints and parameters: API docs.

Frequently asked questions