Live Currency Conversion in Google Sheets

Spreadsheets are where a lot of currency conversion actually happens: expense reports, invoices in several currencies, a price list for another market, a budget with suppliers abroad. Google Sheets has a built-in way to fetch exchange rates, and for many sheets it is enough. This guide shows what it does and does not do, and how to add three custom functions, FXRATE, FXCONVERT and FXRATES, backed by the FxFeed API, for sheets that need historical rates on a specific date, many currencies, or rates you can reason about.

The script and every formula below were tested against the live API.

The built-in option: GOOGLEFINANCE

Google Sheets has a built-in function, GOOGLEFINANCE, that can look up currency rates. It needs no setup, which makes it a good first stop. Google's own documentation of the function states some limits worth knowing before you build on it:

• "Quotes are not sourced from all markets and may be delayed up to 20 minutes."
• "Information is provided 'as is' and solely for informational purposes, not for trading purposes or advice."
• "Historical data cannot be downloaded or accessed via the Sheets API or Apps Script." Scripts that read those cells get #N/A.

For a quick look at today's rate those limits rarely matter. When a sheet has to value transactions at the rate of their date, feed other tools, or say where its numbers come from, an API-backed function is easier to rely on.

Install the FxFeed functions

The functions are a single Apps Script file. Installing it takes about three minutes:

1. Get a key. Get a free API key. The free plan includes 1,000 requests a month with daily data, and the script is built to use very few of them.
2. Open Apps Script. In your spreadsheet, choose Extensions → Apps Script.
3. Paste the script. Replace the contents of Code.gs with the FxFeed script and click Save.
4. Reload the spreadsheet. A new FxFeed menu appears.
5. Set the key. Choose FxFeed → Set API key and paste your key. Google asks you once to authorize the script, because it connects to an external service.

The key is stored in the script's properties, not in a cell, so it does not travel with a copied range, a download or a published sheet. The full install guide, with the script shown inline, is on the Google Sheets documentation page.

FXRATE: one exchange rate

=FXRATE("USD", "EUR")

returns how many euros one US dollar buys, at the latest rate. Add a date for a past day:

=FXRATE("EUR", "USD", "2024-01-02")

That formula returns 1.095601. The date can be text in YYYY-MM-DD form or, more usefully, a date cell: =FXRATE("EUR", "USD", A2). Dates are read in the spreadsheet's own time zone, so a date typed into a cell means that calendar day wherever you are.

Rates are published on working days. For a Saturday, a Sunday or a bank holiday, the functions use the rate of the last working day before it, which is the usual convention for booking weekend transactions. History goes back to 1999.

FXCONVERT: amounts, one or a column at a time

=FXCONVERT(100, "USD", "EUR", "2024-01-02")

returns 91.2741: 100 dollars in euros at that day's rate. Pass a range to convert a whole column in one formula:

=FXCONVERT(C2:C200, "USD", "EUR")

FXCONVERT multiplies by exactly the rate FXRATE returns for the same arguments, so a converted column always agrees with a rate column beside it. Blank cells stay blank. Wrap it in ROUND for display, =ROUND(FXCONVERT(C2, "USD", "EUR"), 2), or use a currency number format.

FXRATES: a table of rates

=FXRATES("USD", "EUR,GBP,JPY")

fills three rows: the currency code in the first column, the rate in the second. Leave out the second argument for every currency the API quotes (160+) against that base, and add a date as the third argument for a past day. It is a handy lookup table: put it on a hidden sheet and use VLOOKUP or XLOOKUP against it from the rest of the workbook.

Recipe: invoices in several currencies, valued on their date

A typical sheet has one row per invoice:

	A	B	C	D
1	Date	Currency	Amount	Amount in EUR
2	2024-01-02	USD	1,200.00	=FXCONVERT(C2, B2, "EUR", A2)
3	2024-01-05	GBP	850.00	=FXCONVERT(C3, B3, "EUR", A3)
4	2024-01-06	USD	99.00	=FXCONVERT(C4, B4, "EUR", A4)

Fill column D down and every invoice is converted at the rate of its own date; row 4, a Saturday, uses Friday's rate. If an invoice is already in euros, FXCONVERT returns the amount unchanged without making a request.

To show the rate used next to each row, which auditors and accountants usually want, add =FXRATE(B2, "EUR", A2) in column E.

Recipe: a price list for another market

Keep your prices in one currency and let the sheet derive the rest:

=ROUND(FXCONVERT(B2:B50, "USD", "GBP"), 2)

Because this uses the latest rate, the result changes when rates move. If a published price list must stay fixed, point the date argument at a cell holding the day the prices were set, so the sheet is reproducible later.

How the functions save your quota

Every request counts towards your plan's monthly allowance, so the script is careful with them:

• One request per base currency and day. The first FXRATE("USD", …) fetches every USD rate at once; every other USD formula on the same day reuses it.
• Caching. Latest rates are cached for an hour and past days for six hours, the longest Apps Script's CacheService keeps anything.
• Ranges are one request. FXCONVERT(C2:C200, …) costs one request, not two hundred.
• Formulas that cannot succeed make no request. A mistyped currency code or date is reported before anything is sent.

A sheet with a handful of base currencies spends only a few requests an hour while it is open, which fits comfortably in the free plan. If you need hourly rather than daily rates, or more requests, the paid plans start at $9 a month.

Google Sheets decides when to recalculate custom functions, mostly when their arguments change. To force a fresh latest rate, change an argument or re-enter the formula.

When something goes wrong

A formula that fails shows #ERROR!; hover over the cell to read the reason. The most common ones:

• No FxFeed API key: choose FxFeed → Set API key. If the menu is missing, reload the spreadsheet.
• FxFeed rejected the API key (401): copy the key again from your dashboard and set it again.
• Monthly request limit reached (402): the message includes a link to upgrade; otherwise the count starts again on the first of the month (UTC).
• Rate limit reached (429): hundreds of formulas asked at the same moment. The script already retries; wait a few seconds.
• "from" must be a three-letter currency code: use ISO codes such as USD, EUR or JPY. The currency list shows every code.

Beyond spreadsheets

The functions call the same API you would use from code, so a sheet can be a prototype for something bigger. When it outgrows Sheets, the Python guide and the JavaScript guide show the same requests in code, and the API reference documents every endpoint. Comparing data providers first? See how FxFeed compares with Open Exchange Rates, Fixer and ExchangeRate-API.

Get a free API key, paste the script, and type =FXRATE("USD", "EUR").