Start with GOOGLEFINANCE if you only need a rate inside Google Sheets. Apps Script gives you another option when your sheet needs the same JSON API as your application. The FXRATE example uses anonymous access and caches each pair for ten minutes. For a shared workbook, keep the API key in a server-side proxy instead of the bound script.
- GOOGLEFINANCE supports a current currency rate or a daily date range directly in the sheet.
- A custom function gives you
=FXRATE("USD","JPY")in any cell without an add-on. - The copy-paste example uses anonymous access, so it does not expose a key to spreadsheet editors.
CacheServiceusually reuses one response for ten minutes, but Google may evict cached values early.- For a shared or production workbook, call your own server-side proxy and keep the API key there.
- Rates are indicative and intended for models, analytics, and display, not for settling a trade.
Start with GOOGLEFINANCE
For a current USD-to-EUR rate, enter the first formula below. The second returns a daily series for January 2026. GOOGLEFINANCE uses Google’s data; it does not call exchangerate.dev. Google notes that quotes may be delayed and historical results cannot be read through Sheets API or Apps Script. See the GOOGLEFINANCE documentation.
=GOOGLEFINANCE("CURRENCY:USDEUR")=GOOGLEFINANCE("CURRENCY:USDEUR", "price", DATE(2026, 1, 1), DATE(2026, 1, 31), "DAILY")If you need a rate you can also retrieve and download outside Sheets, use the historical exchange rate tool or the API example below.
Start without putting a key in the sheet
The latest-rate endpoint accepts an anonymous request. Use that for a small private sheet. 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.gsconst 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.0The 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."}Google’s custom-function guide lists URL Fetch as a supported service. Apps Script → Executions shows the response and error detail when a request fails.
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 is capped per IP; check the response headers when you need the current limit
CacheServiceusually 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
| Code | Symptom | Fix |
|---|---|---|
| 401 or 403 | Cell shows #ACCESS_ERROR | Inspect Apps Script → Executions. If anonymous access no longer fits the workbook, call a server-side proxy; do not paste a key into a shared bound script. |
| 429 | Cell shows #RATE_LIMIT | The per-IP cap was reached. Increase CACHE_TTL, reduce the number of distinct pairs, or move the request behind a keyed proxy. |
| 200 without the quote | Cell shows #NO_PAIR | The quote was not in the response. Check the ISO code (for example, JPY, not JYP). |
| Other | Cell stuck on Loading… | The request is still running or Sheets is retrying it. Check Apps Script → Executions, then refresh the cell after the execution finishes. |
| Other | Cell shows #ERROR | Open Apps Script → Executions and inspect the upstream status or runtime error before retrying. |
| Other | A scheduled trigger changed nothing | Triggers do not force custom-function cells to recalculate. Have the trigger write fetched values to a range, or use a refresh input that the formulas reference. |
Every editor can inspect a bound Apps Script project. Anonymous access avoids a credential in this example. When a shared workbook needs authenticated capacity, put the bearer token in a server-side service and let the sheet call that service. The returned rates are still indicative, not settlement quotes.