How to Get Live Exchange Rates in Google Sheets (GOOGLEFINANCE, Apps Script and a Currency API)

Getting live exchange rates in Google Sheets looks like a solved problem. You type =GOOGLEFINANCE("CURRENCY:USDEUR"), a number appears, and you move on. That works fine for a personal travel budget. It stops working the moment the spreadsheet feeds an invoice, a payroll run, a revenue report or anything a colleague will act on — because of a licence restriction, a documented date quirk and an Apps Script limitation that almost no tutorial mentions.

This guide covers all three practical methods — the built-in GOOGLEFINANCE function, an Apps Script custom function backed by a currency API, and a scheduled rate table refreshed by a time-driven trigger — with the real quota numbers, the exact wording of Google's own caveats, and runnable code. Every claim about Google's behaviour below is quoted from Google's own documentation, and every claim that isn't documented is flagged as such.

---

The Three Ways to Pull Exchange Rates Into Google Sheets

Most people should start with method 1, and switch to method 3 the moment the sheet becomes load-bearing.

---

Method 1: GOOGLEFINANCE — And Exactly What Google Says About It

The basic currency formula

The pattern every tutorial teaches is a CURRENCY: ticker made of two concatenated ISO 4217 currency codes:

=GOOGLEFINANCE("CURRENCY:USDEUR")

To convert an amount in cell A2:

=A2 * GOOGLEFINANCE("CURRENCY:USDEUR")

To build the pair from two cells holding currency codes:

=GOOGLEFINANCE("CURRENCY:" & B2 & C2)

An honest caveat that no competing article makes: the CURRENCY:XXXYYY ticker form does not appear anywhere in Google's official GOOGLEFINANCE documentation. The documented signature is GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval]), and the help page's only currency content is a "currency" attribute (the currency a security is priced in) and a chart example. The CURRENCY: pair syntax works and has for years, but it is community-established behaviour, not a documented contract — which means Google is free to change it without a deprecation notice. Google also publishes no list of supported currency pairs, so any article telling you it supports "about 50 currencies" is guessing.

Historical rates: the attribute rule everyone gets wrong

Pass a date and you get historical data:

=GOOGLEFINANCE("CURRENCY:USDEUR", "close", DATE(2026,1,15))

=GOOGLEFINANCE("CURRENCY:USDEUR", "close", DATE(2026,1,1), DATE(2026,1,31), "DAILY")

Note the attribute. Google documents it plainly: "If any date parameters are specified, the request is considered historical and only the historical attributes are allowed." The historical attributes are open, close, high, low, volume and all — and "price" is not among them. "price" is documented as a real-time attribute only ("Real-time price quote, delayed by up to 20 minutes").

Go and look at the top-ranking Google Sheets currency tutorials right now: most of them hand you GOOGLEFINANCE("CURRENCY:USDEUR", "price", DATE(...)). That combination contradicts Google's own documentation. Use "close".

Two more documented behaviours worth internalising:

• "If start_date is specified but end_date|num_days is not, only the single day's data is returned."
• "Real-time results will be returned as a value within a single cell. Historical data, even for a single day, will be returned as an expanded array with column headers." This is why a historical formula that looks correct spills two columns and a header row into your neat layout, and why you usually need INDEX(...,2,2) around it.

The noon-UTC shift

This one silently corrupts date-matched reports. Verbatim from Google:

"Google treats dates passed into GOOGLEFINANCE as as [sic] noon UTC time. Exchanges that close before that time may be shifted by a day."

If you are reconciling a ledger where every row must use the rate for that posting date, a one-day shift is a real reconciliation break, not a rounding issue. If that describes your sheet, read our guide to exchange rates and tax reporting before you trust a spilled GOOGLEFINANCE array as your audit record.

The delay is 3 minutes, not 20

Nearly every article on this topic tells you GOOGLEFINANCE currency rates are "delayed up to 20 minutes." That 20-minute number comes from the generic securities-quote disclaimer on the "price" attribute. Google's own Google Finance disclaimer page publishes a per-asset delay table, and the row for Currency — global, provided by Morningstar — is 3 minutes. Cryptocurrency is also 3 minutes.

So the data is fresher than the internet believes. What Google does not publish is how often Sheets recalculates the formula, which is the number you actually care about — a 3-minute-old rate sitting in a cell that hasn't recalculated since Tuesday is still a Tuesday rate. Anyone quoting a specific recalculation interval is quoting something Google has never documented.

For comparison, Finexly refreshes rates every minute and returns them on demand, so freshness is a function of when you call, not when the spreadsheet decides to recalculate. If you want to understand what "live" means across providers generally, see where exchange rate APIs get their data.

The usage restriction nobody quotes

This is the single most important paragraph in this article, and it appears in none of the top-ranking tutorials. From Google's GOOGLEFINANCE help page, verbatim:

"Usage restrictions: The data is not for financial industry professional use or use by other professionals at non-financial firms (including government entities). Professional use may be subject to additional licensing fees from a third-party data provider."

And from the Google Finance disclaimer:

"You agree not to copy, modify, reformat, download, store, reproduce, reprocess, transmit or redistribute any data or information found herein or use any such data or information in a commercial enterprise without obtaining prior written consent."

"Google cannot guarantee the accuracy of the exchange rates displayed. You should confirm current rates before making any transactions that could be affected by changes in the exchange rates."

Read that against what your sheet actually does. Pricing a customer invoice, converting supplier bills for the accounts team, or exporting a converted revenue figure into a report you send to a client is not obviously "informational purposes." A licensed exchange rate API removes the question entirely, and that — not raw accuracy — is the real reason finance teams move off GOOGLEFINANCE.

And the hard blocker

"Historical data cannot be downloaded or accessed via the Sheets API or Apps Script. If you attempt to do so, you'll see a #N/A error in place of the values in the corresponding cells of your spreadsheet."

The moment you want to automate anything — a nightly export, a Sheets API pull, a script that snapshots rates — historical GOOGLEFINANCE data is off the table by design. That is the wall that pushes people to method 2.

---

Method 2: An Apps Script Custom Function Backed by a Currency API

A custom function is a JavaScript function in Apps Script that you call from a cell like any built-in. Google explicitly confirms this works with web requests: custom functions "can only call services that don't have access to personal data," and URL Fetch is on the permitted list.

Step 1: Get an API key and store it properly

Grab a free key from the Finexly dashboard — 1,000 requests per month, no credit card. Then, and this matters: do not hard-code it. Two of the most-linked Google Sheets currency tutorials paste the API key straight into the script, in a file that travels with the spreadsheet to anyone you share it with.

In the Apps Script editor (Extensions → Apps Script), run this once from the editor to store the key in Script Properties:

function storeApiKey() {
  PropertiesService.getScriptProperties()
    .setProperty('FINEXLY_API_KEY', 'YOUR_API_KEY');
}

Delete the literal from the file afterwards. Note the documented limits: a property value is capped at 9 KB, and a property store at 500 KB total — ample for a key, not a place to cache data.

Step 2: A single-pair custom function

/**
 * Returns the live exchange rate for a currency pair.
 *
 * @param {string} from Base currency code, e.g. "USD".
 * @param {string} to Quote currency code, e.g. "EUR".
 * @return The exchange rate.
 * @customfunction
 */
function FX_RATE(from, to) {
  if (!from || !to) throw new Error('Both currency codes are required.');

  var pair = String(from).toUpperCase() + '_' + String(to).toUpperCase();
  var cache = CacheService.getScriptCache();
  var hit = cache.get(pair);
  if (hit !== null) return Number(hit);

  var key = PropertiesService.getScriptProperties().getProperty('FINEXLY_API_KEY');
  var res = UrlFetchApp.fetch(
    'https://api.finexly.com/v1/rate?from=' + encodeURIComponent(from) +
      '&to=' + encodeURIComponent(to),
    {
      headers: { Authorization: 'Bearer ' + key },
      muteHttpExceptions: true
    }
  );

  var code = res.getResponseCode();
  var body = JSON.parse(res.getContentText());
  if (code !== 200) {
    throw new Error(body.error ? body.error.code + ': ' + body.error.message : 'HTTP ' + code);
  }

  cache.put(pair, String(body.rate), 300); // 5 minutes
  return body.rate;
}

Use it in a cell:

=FX_RATE("USD","EUR")
=A2 * FX_RATE($B$1, $C$1)

The /v1/rate endpoint returns {"pair": "USD_EUR", "rate": 0.9215}. Full parameter reference is in the Finexly API documentation.

Step 3: Batch the range — the part every competitor misses

Here is the failure mode. Google documents it directly:

"Each time a custom function is used in a spreadsheet, Sheets makes a separate call to the Apps Script server. If your spreadsheet contains dozens (or hundreds, or thousands!) of custom function calls, this process can be slow."

Drag FX_RATE down 400 rows and you have made 400 round trips. With a 30-second-per-execution limit on custom functions, you will hit #ERROR! with the note Exceeded maximum execution time (line 0). long before you hit your API quota.

The fix Google recommends is array batching: accept a range, return an array. Finexly's /v1/convert endpoint takes comma-separated pairs, so the entire column becomes one HTTP request:

/**
 * Converts a column of amounts from one currency to another in a single API call.
 *
 * @param {A2:A400} amounts Range of amounts.
 * @param {string} from Base currency code.
 * @param {string} to Quote currency code.
 * @return {Array} Converted amounts.
 * @customfunction
 */
function FX_CONVERT_RANGE(amounts, from, to) {
  var rate = FX_RATE(from, to);           // one fetch, then cached
  var rows = Array.isArray(amounts) ? amounts : [[amounts]];
  return rows.map(function (row) {
    return row.map(function (v) {
      return (v === '' || v === null) ? '' : Number(v) * rate;
    });
  });
}

=FX_CONVERT_RANGE(A2:A400, "USD", "EUR")

One formula, one network call, 399 fewer round trips. If you need several pairs at once, hit /v1/convert?q=USD_EUR,USD_GBP,USD_JPY and read body["USD_EUR"].rate.

What Google actually says about caching in custom functions

CacheService is used above, but be precise about why. Google's custom functions guide rates the Cache service as "Works, but not particularly useful in custom functions." The caching advantage is real only across separate executions — repeated recalculations of the same pair — not within a single spilled array. Array batching is the documented optimisation; caching is a useful secondary.

The documented cache limits: keys up to 250 characters, values up to 100 KB, a cap of 1,000 items, and an expiry between 1 second and 21,600 seconds (6 hours), defaulting to 600 seconds. There is no published daily quota for cache calls.

One more documented trap, because the workaround circulates widely: adding NOW() as an argument to force a refresh breaks the function. Google: "Custom function arguments must be deterministic … If a custom function tries to return a value based on one of these volatile built-in functions, it displays Loading... indefinitely."

---

Method 3: A Scheduled Rate Table (The Production Pattern)

Custom functions recalculate when Sheets feels like it, which is exactly the wrong property for a sheet that someone else reads. The robust pattern is to stop fetching from cells entirely: write rates into a table on a schedule, then look them up with plain formulas.

The refresh script

var PAIRS = ['USD_EUR', 'USD_GBP', 'USD_JPY', 'USD_CAD', 'USD_AUD', 'USD_CHF'];

function refreshRates() {
  var key = PropertiesService.getScriptProperties().getProperty('FINEXLY_API_KEY');
  var res = UrlFetchApp.fetch(
    'https://api.finexly.com/v1/convert?q=' + PAIRS.join(','),
    { headers: { Authorization: 'Bearer ' + key }, muteHttpExceptions: true }
  );

  if (res.getResponseCode() !== 200) {
    console.error('Finexly refresh failed: ' + res.getContentText());
    return; // keep yesterday's rates rather than blanking the sheet
  }

  var data = JSON.parse(res.getContentText());
  var stamp = new Date();
  var rows = PAIRS.map(function (p) {
    return [p, p.split('_')[0], p.split('_')[1], data[p].rate, stamp];
  });

  var sheet = SpreadsheetApp.getActive().getSheetByName('Rates') ||
              SpreadsheetApp.getActive().insertSheet('Rates');
  sheet.clear();
  sheet.getRange(1, 1, 1, 5)
       .setValues([['Pair', 'From', 'To', 'Rate', 'Updated (UTC)']])
       .setFontWeight('bold');
  sheet.getRange(2, 1, rows.length, 5).setValues(rows);
}

Two details that separate this from the scripts you will find elsewhere. First, one setValues() call, not one per cell — batched range writes are dramatically faster. Second, a failed fetch returns early instead of clearing the table, so a provider hiccup leaves stale-but-labelled rates rather than a sheet full of blanks that silently turn every downstream total into zero.

Creating the trigger

function installTrigger() {
  ScriptApp.getProjectTriggers().forEach(function (t) {
    if (t.getHandlerFunction() === 'refreshRates') ScriptApp.deleteTrigger(t);
  });
  ScriptApp.newTrigger('refreshRates').timeBased().everyHours(1).create();
}

Google documents that time-driven triggers run "as frequently as every minute or as infrequently as once per month," and that the fire time is deliberately fuzzed: "if you create a recurring 9 AM trigger, Apps Script chooses a time between 9 AM and 10 AM." If you use everyMinutes(n), n must be 1, 5, 10, 15 or 30 — no other value is accepted.

Reading the rates

=XLOOKUP("USD_EUR", Rates!A:A, Rates!D:D)
=A2 * XLOOKUP($B$1 & "_" & $C$1, Rates!A:A, Rates!D:D)

Every conversion in the workbook now resolves instantly from local cells, uses one consistent rate, and carries a visible timestamp. That last point is the one auditors ask about.

---

Quotas and Limits You Will Actually Hit

These are Google's published Apps Script numbers — worth knowing before you design around them:

That 2 KB URL cap is the practical ceiling on how many pairs you can cram into a single ?q= batch — comfortably over a hundred, but not unlimited.

On the API side, the Finexly free plan allows 1,000 requests per month at 10 requests per minute. An hourly trigger uses roughly 730 requests a month — inside the free tier with room to spare. Add a handful of ad-hoc custom function calls and you may want the Starter plan; current tiers are on the pricing page. Note that historical rates require a paid plan, so if you need back-dated rates and zero budget, GOOGLEFINANCE remains the honest answer for personal, non-professional use.

Every response carries X-RateLimit-Limit, X-RateLimit-Used and X-RateLimit-Units headers, so you can log usage from res.getAllHeaders() and see a limit coming.

---

Common Errors and How to Fix Them

For deeper patterns on retries, backoff and cache invalidation in production, see our guide to currency API caching and error handling.

---

Which Method Should You Use?

• Personal sheet, quick lookup, no money at stake: GOOGLEFINANCE. It is free, instant, and the licence restriction does not bite.
• A conversion column in a working spreadsheet: the batched Apps Script custom function. Predictable data source, no licence ambiguity, one call per range.
• Anything a colleague, client or auditor reads: the scheduled rate table. One rate per refresh cycle, a visible timestamp, and no dependency on when Sheets decides to recalculate.

Working in Excel instead? The equivalent methods — Power Query, WEBSERVICE and the Currencies data type — are covered in how to get live exchange rates in Excel. If you are still evaluating providers, our currency API comparison sets out the differences, and you can sanity-check any rate against the Finexly currency converter.

---

Frequently Asked Questions

How do I get live exchange rates in Google Sheets for free?

=GOOGLEFINANCE("CURRENCY:USDEUR") costs nothing and needs no setup. Be aware of Google's stated usage restriction — the data "is not for financial industry professional use or use by other professionals at non-financial firms" — and that historical values cannot be read via Apps Script or the Sheets API. For a licensed alternative at zero cost, a free API tier of 1,000 requests per month covers an hourly refresh (about 730 calls) comfortably.

How often does GOOGLEFINANCE update currency rates?

Google's Google Finance disclaimer table lists a 3-minute delay for currency data, sourced from Morningstar — not the 20 minutes commonly repeated online, which is the generic securities-quote figure attached to the "price" attribute. Separately, Google has never published how often Sheets recalculates the formula, so the age of the number in your cell is not something you can rely on.

Can I use GOOGLEFINANCE inside Apps Script?

Not for historical data. Google states: "Historical data cannot be downloaded or accessed via the Sheets API or Apps Script. If you attempt to do so, you'll see a #N/A error." There is also no GOOGLEFINANCE method in any Apps Script service — a script can only read a value a cell has already computed. If you need rates in code, call a currency API with UrlFetchApp.

Why is my Google Sheets currency formula slow or showing #ERROR!?

Each custom function call is a separate round trip to the Apps Script server, and each execution is capped at 30 seconds. Hundreds of individual calls will time out. Accept a range and return an array so one call covers the whole column, cache the rate, and for anything scheduled, move the fetch into a time-driven trigger instead of a cell.

Can I get historical exchange rates in Google Sheets?

Yes, two ways. GOOGLEFINANCE("CURRENCY:USDEUR", "close", DATE(2026,1,1), DATE(2026,1,31), "DAILY") returns a daily series — use "close", since "price" is not a valid historical attribute, and remember Google treats the dates as noon UTC, which can shift a value by a day. Or pull historical rates from an API on a paid plan and write them into a sheet with a trigger, which is the only route that survives automation.

---

Ready to put licensed, minute-fresh exchange rates in your spreadsheet? Get your free Finexly API key — no credit card required. Start with 1,000 requests per month across 170+ currencies, and upgrade when your sheets outgrow it.