LearnTutorial

Convert currency in Google Sheets with GOOGLEFINANCE

Use GOOGLEFINANCE for a current rate or a daily history in your sheet. When you need JSON from an API, add the Apps Script function below and call =FXRATE("USD","EUR") in a cell.

1 Oct 2026 · 5 min read

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.
  • CacheService usually 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.

Google Sheets
=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.

Code.gs
// 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:

Sheets formula · pairs
// 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

The 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:

GET /v1/latest/USD?symbols=JPY
{  "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."}
URL Fetch is supported in custom functions

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
  • CacheService usually 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
CodeSymptomFix
401 or 403Cell shows #ACCESS_ERRORInspect 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.
429Cell shows #RATE_LIMITThe 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 quoteCell shows #NO_PAIRThe quote was not in the response. Check the ISO code (for example, JPY, not JYP).
OtherCell 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.
OtherCell shows #ERROROpen Apps Script → Executions and inspect the upstream status or runtime error before retrying.
OtherA scheduled trigger changed nothingTriggers 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.
Keep credentials out of shared spreadsheets

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.

Questions

Can a Google Sheets custom function call an external API?

Yes. Google lists URL Fetch as a service supported in custom functions. Apps Script → Executions shows request failures when you need to diagnose one.

Is this good for a financial model in Sheets?

Yes. A =FXRATE() cell gives a multi-currency model, budget, or pricing sheet an indicative FX input instead of a hardcoded rate. It recalculates when Sheets reruns the formula, subject to the ten-minute cache and normal Sheets recalculation behavior.

Can I use these rates in a commercial product?

Do not make this anonymous spreadsheet function the data layer for a product. Put exchangerate.dev behind your server, store the key there, and choose a plan whose current terms cover the product. The spreadsheet should receive only the result it needs.

Will this fit the free tier with frequent edits?

It can fit a small private workbook. Anonymous access is capped per IP, and Google egress may be shared. CacheService usually prevents a repeated pair from making another request for ten minutes, but Google may evict cached values early. Use a keyed proxy when the workbook needs predictable account-level capacity.

Does this work in Excel too?

Apps Script is specific to Google Sheets. For Excel, use Power Query M instead. See the Excel tutorial at /learn/excel-exchange-rates.

Keep reading

Start with a free API key

5,000 requests a month on the free plan. No credit card.

Get a free key