Automating FxRates keeps multi-currency conversion current without paste errors.
exchangeRateFetch calls a rates API with FX_API_KEY, writes currency/rateToReport/asOf/base, and inverts API units so amounts multiply into BASE.
SYMBOLS lists the currencies you care about; BASE defaults to USD.
Vendor URLs differ — adjust the endpoint and JSON paths to your provider (the host here is illustrative).
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Item | Value | Purpose |
|---|---|---|
| Property | FX_API_KEY | API secret |
| BASE | USD | Reporting currency |
| SYMBOLS | EUR,GBP,… | Fetched currencies |
| FxRates | A–D | currency, rate, asOf, base |
What this script does
HTTP fetch of FX rates into a sheet shaped for conversion scripts.
Prerequisites
API key; UrlFetch; understanding of rate direction.
- Update URL to your vendor
- Authorize UrlFetch
- Schedule daily
Walkthrough
Set FX_API_KEY, run, confirm EUR row and asOf date.
Edge cases
If the API already returns BASE-per-foreign, remove the 1/rate inversion.
- clearContents rebuilds table
- Missing symbols skipped
Rate direction
Always document whether rateToReport means multiply foreign amount to get BASE. The inversion step exists because many APIs return foreign units per BASE.
How to test
Compare one rate to the vendor website the same day.
Hardening for production
Append history to FxRatesHistory instead of overwrite.
Variations
ECB XML feed via UrlFetch + XmlService.
Full code: exchangeRateFetch()
Set FX_API_KEY and vendor URL, then run exchangeRateFetch() on a daily trigger before conversions.
/**
* Fetch FX rates from an HTTP JSON API into FxRates.
*/
const RATES_SHEET = "FxRates";
const BASE = "USD";
const SYMBOLS = ["EUR", "GBP", "INR", "JPY"];
function exchangeRateFetch() {
const apiKey = PropertiesService.getScriptProperties().getProperty("FX_API_KEY");
if (!apiKey) throw new Error("Set FX_API_KEY script property");
const url = "https://api.exchangerate.host/latest?base=" + encodeURIComponent(BASE) +
"&symbols=" + encodeURIComponent(SYMBOLS.join(",")) + "&access_key=" + encodeURIComponent(apiKey);
const resp = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
if (resp.getResponseCode() >= 300) {
throw new Error("FX HTTP " + resp.getResponseCode() + ": " + resp.getContentText().slice(0, 300));
}
const json = JSON.parse(resp.getContentText());
const rates = json.rates || {};
const sheet = SpreadsheetApp.getActive().getSheetByName(RATES_SHEET) ||
SpreadsheetApp.getActive().insertSheet(RATES_SHEET);
const rows = [["currency", "rateToReport", "asOf", "base"]];
rows.push([BASE, 1, json.date || new Date(), BASE]);
SYMBOLS.forEach(function (sym) {
if (rates[sym] == null) return;
// API returns units of sym per BASE; we need BASE per sym to convert sym→BASE
const rateToReport = 1 / Number(rates[sym]);
rows.push([sym, rateToReport, json.date || new Date(), BASE]);
});
sheet.clearContents();
sheet.getRange(1, 1, rows.length, 4).setValues(rows);
Logger.log("Wrote %s FX rows", rows.length - 1);
}- Line 9: Script property for the vendor key.
- Line 13: Requests only needed currencies.
- Line 28: Inverts to reporting multiplier.
- Line 23: Stores the rate date from the API.
- Line 31: Rebuilds FxRates cleanly.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: FX fetch
- 1FX_API_KEY set
- 2Endpoint matches your vendor
- 3BASE and SYMBOLS correct
- 4Inversion logic verified
- 5Trigger after market close if required
- 6Conversion script uses same rateToReport meaning
Frequently asked questions
No — swap URL/JSON parsing for Open Exchange Rates, Fixer, ECB, etc.
So multiCurrencyConversion can multiply amount × rateToReport into BASE.
Call a date-specific endpoint and pass the transaction date.
Cache daily; do not fetch per row.
UrlFetch is server-side — CORS does not apply.
Works in cells sometimes; scripts give controllable snapshots and API choice.
Still store fractional rates; round amounts when converting.