Google Sheets AutomationDifficulty: Beginner⚡ On Demand

Batch Convert Multi-Currency Columns Using GOOGLEFINANCE Rates

Reads currency codes and amounts, queries historical or live exchange rates, and writes standardized USD amounts across thousands of transaction rows.

Required OAuth Scopes: https://www.googleapis.com/auth/spreadsheets
Code.gs (Apps Script V8 Runtime)
function convertTransactionsToUSD() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Transactions');
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return;
  
  // A: Amount, B: Currency (EUR, GBP, INR), C: Converted USD
  const data = sheet.getRange(2, 1, lastRow - 1, 2).getValues();
  const converted = [];
  
  // Cache currency rates
  const rateCache = {};
  
  for (let i = 0; i < data.length; i++) {
    const amount = data[i][0];
    const curr = String(data[i][1]).toUpperCase().trim();
    
    if (curr === 'USD') {
      converted.push([amount]);
    } else {
      if (!rateCache[curr]) {
        const cellFormula = '=GOOGLEFINANCE("CURRENCY:' + curr + 'USD")';
        const tempSheet = sheet.getParent().insertSheet('__temp');
        tempSheet.getRange('A1').setFormula(cellFormula);
        SpreadsheetApp.flush();
        rateCache[curr] = tempSheet.getRange('A1').getValue();
        sheet.getParent().deleteSheet(tempSheet);
      }
      converted.push([amount * (rateCache[curr] || 1)]);
    }
  }
  
  sheet.getRange(2, 3, converted.length, 1).setValues(converted);
}

Line-by-Line Code Explanation

Line 6: Batches all rows into memory.
Line 10-18: Caches exchange rate lookups to prevent redundant external API hits.
Line 27: Writes all converted USD figures in a single write operation.

Step-by-Step Setup & Deployment

  1. Create a "Transactions" sheet tab with Amount in Col A and Currency code in Col B.
  2. Run `convertTransactionsToUSD` to populate Column C.

Best Practices for Quota & Rate Limit Management

When deploying this automation in Google Workspace, adhere to these operational principles:

  • Batch Reads & Writes: Never execute getValue() or setValue() inside loops. Read ranges into memory in 1 call, process arrays in V8 memory, and commit changes with a single setValues() call.
  • Execution Timeouts: Apps Script terminates scripts exceeding 6 minutes. For multi-thousand row datasets, implement batch pagination with resume timestamps stored in PropertiesService.
  • OAuth Permissions: Scope your manifest (appsscript.json) narrowly to prevent security prompts from alarming external team members.

Frequently Asked Questions

Is Google Apps Script completely free to use?

Yes. Google Apps Script is built into all free Google accounts and Google Workspace editions. There are no server fees or subscription costs for running scripts within daily quotas.

Can I run this script on an automated schedule without keeping my computer on?

Yes. Time-driven triggers execute on Google's cloud infrastructure. Your script will run reliably even when your computer is closed or offline.

More Google Sheets Automation Snippets