Invoices in EUR, reports in USD. getDailyRate(from, to, date) returns the exchange rate for a given day, or the latest one if you leave out the date. It uses the free Frankfurter API (ECB reference rates) and caches each answer so a 500-row sheet makes one request per currency pair per day.
When to use
- Converting expense or invoice amounts on the date they happened
- Monthly reports in one currency from transactions in several
- Any loop where the same pair and date repeat
How to use this snippet
/**
* Exchange rate from -> to for a day (ECB reference rates via frankfurter.app).
* Cached in the script cache for 6 hours per pair and day.
*
* @param {string} from ISO code, e.g. 'EUR'
* @param {string} to ISO code, e.g. 'USD'
* @param {Date|string} [date] omit for the latest rate
* @return {number}
*/
function getDailyRate(from, to, date) {
from = String(from || '').toUpperCase(); to = String(to || '').toUpperCase();
if (!from || !to) throw new Error('getDailyRate: from and to are required');
if (from === to) return 1;
var tz = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
var day = date ? Utilities.formatDate(new Date(date), tz, 'yyyy-MM-dd') : 'latest';
var key = 'fx:' + day + ':' + from + ':' + to;
var cache = CacheService.getScriptCache();
var hit = cache.get(key);
if (hit) return Number(hit);
var res = UrlFetchApp.fetch('https://api.frankfurter.app/' + day + '?from=' + from + '&to=' + to,
{ muteHttpExceptions: true });
if (res.getResponseCode() !== 200) throw new Error('getDailyRate: HTTP ' + res.getResponseCode());
var rate = JSON.parse(res.getContentText()).rates[to];
if (typeof rate !== 'number') throw new Error('getDailyRate: no rate for ' + from + '->' + to);
// Past dates never change; "latest" refreshes every 6 hours.
cache.put(key, String(rate), 21600);
return rate;
}
Example
function convertExpenses() {
var sh = SpreadsheetApp.getActive().getSheetByName('Expenses');
var rows = sh.getRange(2, 1, sh.getLastRow() - 1, 3).getValues(); // Date, Amount, Currency
var out = rows.map(function (r) {
return [r[1] * getDailyRate(r[2], 'USD', r[0])];
});
sh.getRange(2, 4, out.length, 1).setValues(out);
}
Tips:
- ECB publishes rates on working days only. For a weekend or holiday date, the API returns the previous working day's rate.
- Write the converted amount back to the sheet. A custom function that calls
UrlFetchAppon every recalc is slow and can hit quotas. - Need a currency ECB doesn't publish? Swap the URL for your provider and keep the cache logic.
Happy Coding!
