Multi-currency ledgers need a reporting currency column for rollups. Keep rates on FxRates and convert in batch.
multiCurrencyConversion maps currency → rateToReport, sets REPORT_CCY to 1, and writes amountReport.
Missing rates leave amountReport blank so bad data is obvious.
Pair with exchange-rate-fetch to refresh FxRates automatically.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Column | Purpose |
|---|---|---|
| FxRates | A currency | ISO code |
| FxRates | B rateToReport | Multiply to reporting ccy |
| Transactions | B currency | Source ccy |
| Transactions | C amount | Source amount |
| Transactions | D amountReport | Converted |
| REPORT_CCY | USD | Reporting currency |
What this script does
Batch conversion of transaction amounts into reporting currency.
Prerequisites
FxRates populated; ISO codes consistent.
- rate means multiply to reporting
- REPORT_CCY forced to 1
- Blank on missing rate
Walkthrough
EUR amount 100, rate 1.1 → 110 USD.
Edge cases
Inverted rates (report per unit foreign) will be wrong — document direction.
- Case-insensitive ccy
- Cent rounding
How to test
USD row remains identical amount.
Hardening for production
Store rateUsed on each row for audit.
Variations
Convert to multiple reporting books.
Full code: multiCurrencyConversion()
Refresh FxRates, set REPORT_CCY, then run multiCurrencyConversion().
/**
* Convert amounts to a reporting currency using a Rates sheet.
*/
const TX = "Transactions"; // A id, B currency, C amount, D amountReport
const RATES = "FxRates"; // A currency, B rateToReport
const REPORT_CCY = "USD";
function multiCurrencyConversion() {
const ss = SpreadsheetApp.getActive();
const rateRows = ss.getSheetByName(RATES).getDataRange().getValues();
const rates = {};
for (let i = 1; i < rateRows.length; i++) {
rates[String(rateRows[i][0]).toUpperCase()] = Number(rateRows[i][1]);
}
rates[REPORT_CCY] = 1;
const sheet = ss.getSheetByName(TX);
const values = sheet.getDataRange().getValues();
for (let i = 1; i < values.length; i++) {
const ccy = String(values[i][1] || "").toUpperCase();
const amt = Number(values[i][2]) || 0;
if (!rates.hasOwnProperty(ccy) || rates[ccy] === "" || isNaN(rates[ccy])) {
values[i][3] = "";
continue;
}
values[i][3] = Math.round(amt * rates[ccy] * 100) / 100;
}
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}- Line 6: Reporting currency code.
- Line 15: Identity rate for reporting ccy.
- Line 26: Conversion.
- Line 4: Column D.
- Line 22: Missing rate → blank output.
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 conversion
- 1Rate direction documented
- 2All currencies present on FxRates
- 3REPORT_CCY correct
- 4ISO codes uppercase-normalized
- 5Audit need for rateUsed considered
- 6Sample convert checked vs finance source
Frequently asked questions
This expects a multiplier to reporting currency. If you store pairs differently, invert in code.
Use transaction date to pick a rate table keyed by date.
Same pattern if you trust the rate source.
Zero looks like a real converted value; blank signals missing rate.
Convert via USD pivot if you only have USD pairs.
Cents are common; use more decimals for FX trading books.
Sometimes — scripts help when you need snapshots and API rates.