Google Sheets: Currency APIs Compared for Live Exchange Rates
Google Sheets is the go-to tool for finance teams, accountants, freelancers, and small businesses that need to work with multiple currencies. Whether you are building an invoice template, tracking international expenses, forecasting multi-currency revenue, or reconciling foreign transactions, you need reliable exchange rate data inside your spreadsheet.
Google Sheets has a built-in GOOGLEFINANCE function, and for a lot of spreadsheets it is genuinely all you need. Its limits show up when the rate source matters: Google documents that quotes may be delayed by up to 20 minutes, it does not expose official central-bank rates, it cannot be called from Apps Script, and there is no SLA or support channel if it returns #N/A.
This article compares six ways to get exchange rate data into Google Sheets in 2026: the native GOOGLEFINANCE function, the keyless Frankfurter API, and four API-key services including our own. Vendor facts were re-checked against each provider's public pricing page on 24 October 2026. We run AllRatesToday, so read our own entry with that in mind; there is a plain which one should you pick section at the end.
Side-by-Side Comparison
How the six options stack up for Google Sheets users (free tier and entry paid price as published on 24 October 2026):
| API | Google Sheets Add-on | Apps Script | Free Tier | Update Frequency | Historical | Entry Paid Plan |
|---|---|---|---|---|---|---|
| GOOGLEFINANCE | Built-in function | Not callable | Free | Delayed up to 20 min | Yes (price series) | n/a |
| Frankfurter | No | Manual setup (no key) | Keyless, no caps | Daily (ECB) | Free, to 1999 | n/a (free) |
| AllRatesToday | Yes | REST API + CSV | Free key, non-commercial | Every 60 s | Paid (from €4.99/mo) | €4.99/mo |
| Open Exchange Rates | No | Manual setup | 1,000 req/mo, USD base | Hourly | Paid only | $12/mo |
| Fixer | No | Manual setup | 100 req/mo, non-commercial | Hourly | Included | $14.99/mo |
| ExchangeRate-API | No | Manual setup | 1,500 req/mo | Daily | Paid only | $10/mo (Pro) |
One thing the table hides: Google Sheets caches IMPORTDATA results and refreshes them at most about once an hour, and time-driven Apps Script triggers run at best once a minute. An API that updates every 60 seconds does not make your cells update every 60 seconds; for hourly or daily sheets, every option here is fast enough.
1. AllRatesToday
AllRatesToday (our service) is a REST API for mid-market rates updated every 60 seconds, plus official rates from central banks and tax authorities (see the central bank rates API). There is a Google Sheets add-on for pulling rates into cells without code, every table endpoint can return CSV for IMPORTDATA, and the API is straightforward to call from Apps Script. The free API key is for non-commercial use; historical rates are on paid plans from €4.99/month.
Using the Google Sheets Add-on
Install the add-on, enter your API key, and pull rates directly into cells. It supports:
- Latest rates for 160+ currencies
- Historical rates for past dates (paid plans)
- Batch conversion across multiple currency pairs
- Auto-refresh on a schedule you control
Using IMPORTDATA with AllRatesToday
The simplest way to get a rate into a cell without an add-on is the IMPORTDATA formula with the AllRatesToday CSV endpoint:
=IMPORTDATA("https://allratestoday.com/api/v1/rates?source=USD&target=EUR&format=csv&api_key=YOUR_API_KEY") For multiple currencies at once:
=IMPORTDATA("https://allratestoday.com/api/v1/rates?source=USD&target=EUR,GBP,JPY,CAD,AUD&format=csv&api_key=YOUR_API_KEY") Tip: Store your API key in a named range (e.g., API_KEY) and reference it in formulas: =IMPORTDATA("https://allratestoday.com/api/v1/rates?source=USD&target=EUR&format=csv&api_key=" & API_KEY). This keeps your key in one place and makes it easy to update.
Google Apps Script — Custom Function
For maximum flexibility, write a custom Apps Script function that you can call like any built-in formula:
/**
* Fetches the real-time exchange rate from AllRatesToday.
* Usage: =GETRATE("USD", "EUR")
*
* @param {string} source - Source currency code (e.g., "USD")
* @param {string} target - Target currency code (e.g., "EUR")
* @return {number} The current exchange rate
* @customfunction
*/
function GETRATE(source, target) {
var API_KEY = PropertiesService.getScriptProperties().getProperty("ART_API_KEY");
var url = "https://allratestoday.com/api/v1/rates"
+ "?source=" + encodeURIComponent(source)
+ "&target=" + encodeURIComponent(target);
var options = {
"method": "get",
"headers": {
"Authorization": "Bearer " + API_KEY
},
"muteHttpExceptions": true
};
var response = UrlFetchApp.fetch(url, options);
var rows = JSON.parse(response.getContentText()); // array of rows
return rows[0].rate;
} Now you can use it in any cell:
=GETRATE("USD", "EUR")
=GETRATE("GBP", "JPY")
=GETRATE(A2, B2) // Reference cells for dynamic pairs Apps Script — Convert an Amount
/**
* Converts an amount from one currency to another using AllRatesToday.
* Usage: =CONVERTCURRENCY(1000, "USD", "EUR")
*
* @param {number} amount - Amount to convert
* @param {string} source - Source currency code
* @param {string} target - Target currency code
* @return {number} The converted amount
* @customfunction
*/
function CONVERTCURRENCY(amount, source, target) {
var rate = GETRATE(source, target);
return Math.round(amount * rate * 100) / 100;
} Apps Script — Fetch Historical Rates
/**
* Fetches the exchange rate for a specific historical date (paid plans).
* Usage: =HISTORICALRATE("USD", "EUR", "2026-01-15")
*
* @param {string} source - Source currency code
* @param {string} target - Target currency code
* @param {string} date - Date in YYYY-MM-DD format
* @return {number} The exchange rate on that date
* @customfunction
*/
function HISTORICALRATE(source, target, date) {
var API_KEY = PropertiesService.getScriptProperties().getProperty("ART_API_KEY");
var url = "https://allratestoday.com/api/v1/rates"
+ "?source=" + encodeURIComponent(source)
+ "&target=" + encodeURIComponent(target)
+ "&time=" + encodeURIComponent(date);
var options = {
"method": "get",
"headers": {
"Authorization": "Bearer " + API_KEY
},
"muteHttpExceptions": true
};
var response = UrlFetchApp.fetch(url, options);
var rows = JSON.parse(response.getContentText()); // array of rows
return rows[0].rate;
} Apps Script — Auto-Refresh Rates on a Schedule
/**
* Updates all exchange rates in the "Rates" sheet.
* Set this to run on a time-driven trigger (e.g., every hour).
*/
function refreshAllRates() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Rates");
var API_KEY = PropertiesService.getScriptProperties().getProperty("ART_API_KEY");
var pairs = [
["USD", "EUR"], ["USD", "GBP"], ["USD", "JPY"],
["USD", "CAD"], ["USD", "AUD"], ["USD", "CHF"]
];
for (var i = 0; i < pairs.length; i++) {
var url = "https://allratestoday.com/api/v1/rates"
+ "?source=" + pairs[i][0]
+ "&target=" + pairs[i][1];
var options = {
"method": "get",
"headers": { "Authorization": "Bearer " + API_KEY },
"muteHttpExceptions": true
};
var response = UrlFetchApp.fetch(url, options);
var rows = JSON.parse(response.getContentText()); // array of rows
// Write to columns: A = source, B = target, C = rate, D = timestamp
sheet.getRange(i + 2, 1).setValue(pairs[i][0]);
sheet.getRange(i + 2, 2).setValue(pairs[i][1]);
sheet.getRange(i + 2, 3).setValue(rows[0].rate);
sheet.getRange(i + 2, 4).setValue(new Date());
}
} Setting up auto-refresh: In Google Sheets, go to Extensions → Apps Script → Triggers, and create a time-driven trigger for refreshAllRates. You can set it to run every minute, every 5 minutes, hourly, or daily.
- Data source: institutional foreign exchange market data
- Update frequency: Every 60 seconds at the API
- Currencies: 160+ including majors, minors, and exotics; official central-bank rates as a separate endpoint
- Rate type: Mid-market (no bank markup)
- Free tier: Free API key, no credit card, non-commercial use
- Google Sheets: Add-on, Apps Script, CSV output for IMPORTDATA
2. Open Exchange Rates
Open Exchange Rates is a long-established provider with a large tutorial base. There is no Google Sheets add-on, so you write a short Apps Script. The free tier gives 1,000 requests per month, updated hourly, with USD as the only base currency; you can still derive EUR/GBP cross rates by dividing. Paid plans start at $12/month.
// Apps Script for Open Exchange Rates
function getOXRRate(target) {
var APP_ID = "YOUR_APP_ID";
var url = "https://openexchangerates.org/api/latest.json?app_id=" + APP_ID;
var response = UrlFetchApp.fetch(url);
var data = JSON.parse(response.getContentText());
return data.rates[target];
// Note: always returns rates relative to USD on free tier
} - Free tier: 1,000 requests/month, USD base only
- Update frequency: Hourly on free, more frequent on paid
- Paid from: $12/month
- Google Sheets add-on: No
- Limitation: Free plan locked to USD base and 1,000 calls; historical and time-series endpoints are paid
3. Fixer (and currencylayer)
Fixer is part of APILayer. Its free tier is small at 100 requests per month and is for non-commercial use, but it is better than it used to be: rates update hourly, HTTPS is supported, EUR is the base currency, and historical dates are included on the free plan. Paid plans start at $14.99/month. There is no Google Sheets add-on. Its sibling currencylayer has a similar free plan (daily updates, HTTPS, historical included) and the same $14.99 entry price, with USD as the base.
// Apps Script for Fixer.io
function getFixerRate(source, target) {
var API_KEY = "YOUR_API_KEY";
var url = "https://data.fixer.io/api/latest"
+ "?access_key=" + API_KEY
+ "&base=EUR" // Free tier: EUR base only
+ "&symbols=" + target;
var response = UrlFetchApp.fetch(url);
var data = JSON.parse(response.getContentText());
return data.rates[target];
} - Free tier: 100 requests/month, EUR base, non-commercial, historical included
- Update frequency: Hourly
- Paid from: $14.99/month
- Google Sheets add-on: No
- Limitation: 100 requests/month covers roughly three refreshes a day for one sheet; any more needs a paid plan
4. GOOGLEFINANCE Function (Built-in)
Google Sheets includes a built-in GOOGLEFINANCE function that fetches currency rates with no setup, no key and no cost. For most personal and casual sheets it is the right answer. Its limitations matter mainly when you need a documented rate source or automation.
Basic usage
=GOOGLEFINANCE("CURRENCY:USDEUR")
=GOOGLEFINANCE("CURRENCY:GBPJPY")
=1000 * GOOGLEFINANCE("CURRENCY:USDEUR") Historical rates
Historical series work for currency pairs using the "price" attribute with a start and end date. The result is a two-column table (date, rate), so wrap it in INDEX to pull a single value:
=GOOGLEFINANCE("CURRENCY:USDEUR", "price", DATE(2026,1,1), DATE(2026,1,31))
=INDEX(GOOGLEFINANCE("CURRENCY:USDEUR", "price", DATE(2026,1,15)), 2, 2) Where GOOGLEFINANCE falls short
- No SLA or support: Google provides no uptime guarantee, and when a cell shows
#N/Athere is nobody to ask. - Delayed data: Google states that quotes may be delayed by up to 20 minutes.
- No official rates: the source is not documented and there is no way to request a specific central bank or tax-authority rate.
- Cannot be used in Apps Script: GOOGLEFINANCE is a spreadsheet-only function. You cannot call it from Apps Script code.
- Breaks automation: If you build automations that depend on GOOGLEFINANCE values,
#N/Aerrors will propagate through your entire spreadsheet. - No batch endpoint: Each GOOGLEFINANCE call is independent. Getting rates for 20 currencies means 20 separate function calls that may or may not resolve.
When GOOGLEFINANCE is the right choice: personal budgeting, travel planning, quick lookups and any sheet where a market rate within 20 minutes is fine. Reach for an API when you need a named source (an official rate for tax or audit), many currencies in one call, or rates inside Apps Script automation.
5. ExchangeRate-API
ExchangeRate-API offers a simple REST endpoint and is widely used. There is no Google Sheets add-on, so you write a short Apps Script. The free tier gives 1,500 requests per month with daily-updated rates; the Pro plan is $10/month.
// Apps Script for ExchangeRate-API
function getERApiRate(source, target) {
var API_KEY = "YOUR_API_KEY";
var url = "https://v6.exchangerate-api.com/v6/" + API_KEY + "/latest/" + source;
var response = UrlFetchApp.fetch(url);
var data = JSON.parse(response.getContentText());
return data.conversion_rates[target];
} - Free tier: 1,500 requests/month
- Update frequency: Daily on free
- Paid from: $10/month (Pro)
- Google Sheets add-on: No
- Limitation: Daily updates only on free; historical data requires a paid plan
6. Frankfurter (free ECB rates, no key)
Frankfurter is an open-source API that republishes the European Central Bank reference rates. There is no API key, no sign-up and no published request cap. Rates are daily (the ECB fixes once per business day around 16:00 CET), and history goes back to 1999. If your sheet only needs ECB reference rates, it is hard to beat on price.
// Apps Script for Frankfurter (no key)
function getFrankfurterRate(source, target) {
var url = "https://api.frankfurter.app/latest?from=" + source + "&to=" + target;
var data = JSON.parse(UrlFetchApp.fetch(url).getContentText());
return data.rates[target];
} With IMPORTDATA you can read the JSON as text and extract the number:
=VALUE(REGEXEXTRACT(JOIN(",", IMPORTDATA("https://api.frankfurter.app/latest?from=USD&to=EUR")), "EURD+([d.]+)")) - Free tier: Keyless, no caps
- Update frequency: Daily (ECB reference rates)
- Historical: Free, back to 1999
- Google Sheets add-on: No
- Limitation: ECB rates only (about 30 currencies, EUR-centred), one fix per business day, no SLA or support
Real-World Use Cases for Spreadsheet Users
Invoice template with auto-conversion
Build an invoice template that automatically converts line items to your client's currency:
// Cell setup:
// A1: "Invoice Currency" B1: "EUR"
// A2: "Your Currency" B2: "USD"
// A3: "Exchange Rate" B3: =GETRATE(B2, B1)
// A4: "Rate Updated" B4: =NOW()
// Line items:
// Column D (Amount USD) | Column E (Amount EUR)
// D6: 500 | E6: =D6 * $B$3
// D7: 1200 | E7: =D7 * $B$3
// D8: 350 | E8: =D8 * $B$3 Multi-currency expense tracker
/**
* Bulk-converts a column of amounts in various currencies to a single base currency.
* Designed for expense tracking sheets.
*
* @param {Range} amounts - Range of expense amounts
* @param {Range} currencies - Range of currency codes (same size as amounts)
* @param {string} baseCurrency - Your base/home currency
* @return {number[]} Array of converted amounts
* @customfunction
*/
function CONVERTEXPENSES(amounts, currencies, baseCurrency) {
var API_KEY = PropertiesService.getScriptProperties().getProperty("ART_API_KEY");
var results = [];
// Fetch all rates at once to minimize API calls
var url = "https://allratestoday.com/api/v1/rates?source=" + baseCurrency;
var options = {
"method": "get",
"headers": { "Authorization": "Bearer " + API_KEY },
"muteHttpExceptions": true
};
var response = UrlFetchApp.fetch(url, options);
// Response is an array of rows (rate, source, target, time); index it by target
var allRates = {};
JSON.parse(response.getContentText()).forEach(function (row) {
allRates[row.target] = row.rate;
});
for (var i = 0; i < amounts.length; i++) {
var amount = amounts[i][0];
var currency = currencies[i][0];
if (!amount || !currency) {
results.push([""]);
continue;
}
if (currency === baseCurrency) {
results.push([amount]);
} else {
// Convert: foreign amount / rate = base amount
var rate = allRates[currency];
results.push([Math.round((amount / rate) * 100) / 100]);
}
}
return results;
} Monthly exchange rate dashboard
/**
* Populates a dashboard with end-of-month rates for the current year.
* Run manually or on a monthly trigger. Dated rates need a paid plan.
*/
function buildMonthlyDashboard() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Dashboard");
var API_KEY = PropertiesService.getScriptProperties().getProperty("ART_API_KEY");
var baseCurrency = "USD";
var targets = ["EUR", "GBP", "JPY", "CAD", "AUD"];
// Headers
sheet.getRange(1, 1).setValue("Month");
for (var t = 0; t < targets.length; t++) {
sheet.getRange(1, t + 2).setValue(baseCurrency + "/" + targets[t]);
}
var year = new Date().getFullYear();
var currentMonth = new Date().getMonth(); // 0-indexed
for (var m = 0; m < currentMonth; m++) {
// Last day of each month
var lastDay = new Date(year, m + 1, 0);
var dateStr = Utilities.formatDate(lastDay, "UTC", "yyyy-MM-dd");
sheet.getRange(m + 2, 1).setValue(Utilities.formatDate(lastDay, "UTC", "MMM yyyy"));
for (var t = 0; t < targets.length; t++) {
var url = "https://allratestoday.com/api/v1/rates"
+ "?source=" + baseCurrency
+ "&target=" + targets[t]
+ "&time=" + dateStr;
var options = {
"method": "get",
"headers": { "Authorization": "Bearer " + API_KEY },
"muteHttpExceptions": true
};
var response = UrlFetchApp.fetch(url, options);
var rows = JSON.parse(response.getContentText());
sheet.getRange(m + 2, t + 2).setValue(rows[0].rate);
}
}
} GOOGLEFINANCE vs. AllRatesToday: Head-to-Head
| Feature | GOOGLEFINANCE | AllRatesToday |
|---|---|---|
| Support | None (community forums) | Email + documentation |
| Update speed | Delayed up to 20 minutes | Every 60 seconds at the API (cells refresh as often as your trigger or IMPORTDATA allows) |
| Currencies | Major and many minor pairs | 160+, plus official central-bank rates |
| Apps Script | Not callable from script | Full REST API support |
| Historical data | Free, price series by date range | Paid plans from €4.99/mo |
| Rate type | Market rate, source not documented | Mid-market, or a named central bank |
| Add-on | Built-in function only | Add-on available |
| Cost | Free | Free key (non-commercial); paid from €4.99/mo |
Which One Should You Pick?
- Most casual sheets: GOOGLEFINANCE. Budgets, travel, quick conversions and anything where a market rate within 20 minutes is fine. Free, built in, nothing to install.
- Free ECB rates with IMPORTDATA or Apps Script: Frankfurter. No key, no caps, daily ECB reference rates and history to 1999. The limit is that it is ECB-only, so about 30 currencies and one fix per day.
- Official central-bank rates, many currencies or frequent updates with an API key: AllRatesToday. Mid-market rates every 60 seconds, 160+ currencies, official rates from central banks and tax authorities, an add-on, and CSV for
IMPORTDATA. Historical rates and commercial use need a plan from €4.99/month. - Already using one of the incumbents: stay. Open Exchange Rates ($12), Fixer ($14.99) and ExchangeRate-API ($10 Pro) all work fine from Apps Script; the free tiers differ mainly in base currency, update frequency and whether history is included (see the table above).
Quick Reference
- Add-on: Install from Google Workspace Marketplace
- IMPORTDATA:
=IMPORTDATA("https://allratestoday.com/api/v1/rates?source=USD&target=EUR&format=csv&api_key=YOUR_API_KEY") - Apps Script: Use
UrlFetchApp.fetch()with Bearer token auth - Get rates:
GET /api/v1/rates?source=USD&target=EUR - Historical (paid plans):
GET /api/v1/rates?source=USD&target=EUR&time=2026-01-15 - Auth:
Authorization: Bearer YOUR_API_KEY
Get Exchange Rates in Google Sheets in 30 Seconds
Sign up for a free API key, install the Google Sheets add-on or paste an IMPORTDATA formula, and pull mid-market or official central-bank rates into your spreadsheet. No credit card required; free key is for non-commercial use. Details on the AllRatesToday exchange rate API page.