How to Pull Live Exchange Rates into Google Sheets with an API

Google Sheets is the go-to tool for quick financial analysis, budgeting, and reporting. But the moment you work with multiple currencies, you run into a problem: Google's built-in GOOGLEFINANCE function for exchange rates is unreliable, often returns errors, and gives you no control over the data source. A much better approach is to connect an exchange rate API to Google Sheets using Apps Script.

This tutorial shows you how to fetch live exchange rates from the Exchange Rate API, write them into your spreadsheet, set up automatic daily refreshes, and build practical currency conversion formulas. By the end, you will have a self-updating rates table that powers all your multi-currency calculations.

Why Not Use GOOGLEFINANCE?

The GOOGLEFINANCE("CURRENCY:USDEUR") function seems like the obvious choice, but it has well-known problems:

• It frequently returns #N/A errors, especially in heavily used spreadsheets
• It has no SLA or guarantee of availability
• It does not support historical date lookups in a reliable format
• It covers a limited set of currency pairs
• You cannot authenticate or control the data source

An exchange rate API in Google Sheets via Apps Script gives you full control: you pick the provider, you decide when to refresh, and you get consistent, structured data every time.

Prerequisites

1. A Google account with access to Google Sheets
2. An API key from exchange-rateapi.com (free, no credit card required)
3. Basic familiarity with Google Sheets (no coding experience required; we provide copy-paste code)

Step 1: Create the Rates Sheet

Open a new Google Sheet and rename the first tab to Rates. Set up the following column headers in row 1:

This is where the script will write the exchange rate data.

Step 2: Add the Apps Script

Go to Extensions > Apps Script. Delete any code in the default Code.gs file and paste the following:

    const API_KEY = "YOUR_API_KEY";
    const BASE_CURRENCY = "USD";
    const API_URL = `https://api.allratestoday.com/v1/latest?base=${BASE_CURRENCY}`;
    
    function fetchExchangeRates() {
      const options = {
        method: "get",
        headers: {
          Authorization: `Bearer ${API_KEY}`,
        },
        muteHttpExceptions: true,
      };
    
      const response = UrlFetchApp.fetch(API_URL, options);
      const statusCode = response.getResponseCode();
    
      if (statusCode !== 200) {
        Logger.log(`API error: ${statusCode} - ${response.getContentText()}`);
        SpreadsheetApp.getUi().alert(
          `Failed to fetch rates. HTTP ${statusCode}`
        );
        return;
      }
    
      const data = JSON.parse(response.getContentText());
      const rates = data.rates;
      const date = data.date;
    
      const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Rates");
    
      // Clear existing data (keep headers)
      const lastRow = sheet.getLastRow();
      if (lastRow > 1) {
        sheet.getRange(2, 1, lastRow - 1, 3).clearContent();
      }
    
      // Write new data
      const currencies = Object.keys(rates).sort();
      const rows = currencies.map((currency) => [
        currency,
        rates[currency],
        date,
      ]);
    
      if (rows.length > 0) {
        sheet.getRange(2, 1, rows.length, 3).setValues(rows);
      }
    
      Logger.log(`Updated ${rows.length} exchange rates for ${date}`);
    }
    

Click the save icon (or Ctrl+S) and name the project something like "Exchange Rate Updater".

Step 3: Run the Script Manually

1. In the Apps Script editor, select fetchExchangeRates from the function dropdown
2. Click the Run button (play icon)
3. The first time, Google will ask you to authorize the script. Review the permissions and click Allow
4. Switch back to your spreadsheet. The Rates tab should now have 160+ rows of currency data

You now have a working exchange rate API Google Sheets integration. The next step is to automate it.

Step 4: Set Up Automatic Daily Refresh

In the Apps Script editor, click the clock icon on the left sidebar (Triggers), then click Add Trigger:

• Function to run: fetchExchangeRates
• Event source: Time-driven
• Type of time-based trigger: Day timer
• Time of day: 6:00 AM to 7:00 AM (or whenever you want fresh rates)

Click Save. The script will now run automatically every day, updating your Rates sheet before you start work.

Hourly Refresh (Optional)

If you need more frequent updates, change the trigger type to Hour timer and set it to run every hour. The free tier of Exchange Rate API gives you 1,500 requests per month, so hourly refreshes (about 720 per month) fit comfortably within the limit.

Step 5: Build a Currency Conversion Formula

Now that you have a live rates table, you can convert any amount with a VLOOKUP formula. In a new tab called Converter, set up this layout:

In cell B4, enter this formula:

    =B1 / VLOOKUP(B2, Rates!A:B, 2, FALSE) * VLOOKUP(B3, Rates!A:B, 2, FALSE)
    

This performs a cross-rate conversion: it divides the amount by the "from" rate (to get the base currency equivalent) and multiplies by the "to" rate. Since the Rates sheet updates daily, this formula always uses current data.

Step 6: Fetch Historical Rates

For financial reporting, you often need the exchange rate on a specific past date. Add this function to your Apps Script:

    function fetchHistoricalRates(dateString) {
      const url = `https://api.allratestoday.com/v1/historical?date=${dateString}&base=${BASE_CURRENCY}`;
    
      const options = {
        method: "get",
        headers: {
          Authorization: `Bearer ${API_KEY}`,
        },
        muteHttpExceptions: true,
      };
    
      const response = UrlFetchApp.fetch(url, options);
    
      if (response.getResponseCode() !== 200) {
        Logger.log(`Error fetching historical rates: ${response.getContentText()}`);
        return;
      }
    
      const data = JSON.parse(response.getContentText());
      const rates = data.rates;
    
      const sheet =
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Historical") ||
        SpreadsheetApp.getActiveSpreadsheet().insertSheet("Historical");
    
      // Clear and write
      sheet.clear();
      sheet.getRange(1, 1, 1, 3).setValues([["Currency", "Rate", "Date"]]);
    
      const currencies = Object.keys(rates).sort();
      const rows = currencies.map((c) => [c, rates[c], dateString]);
      sheet.getRange(2, 1, rows.length, 3).setValues(rows);
    
      Logger.log(`Fetched historical rates for ${dateString}`);
    }
    

Call it from another function or create a custom menu:

    function onOpen() {
      SpreadsheetApp.getUi()
        .createMenu("Exchange Rates")
        .addItem("Refresh Latest Rates", "fetchExchangeRates")
        .addItem("Fetch Historical Rates...", "showHistoricalDialog")
        .addToUi();
    }
    
    function showHistoricalDialog() {
      const ui = SpreadsheetApp.getUi();
      const response = ui.prompt(
        "Historical Rates",
        "Enter date (YYYY-MM-DD):",
        ui.ButtonSet.OK_CANCEL
      );
    
      if (response.getSelectedButton() === ui.Button.OK) {
        fetchHistoricalRates(response.getResponseText().trim());
      }
    }
    

Now you have a custom menu in your spreadsheet that lets anyone fetch rates for any date without touching the code.

Step 7: Multi-Currency Invoice Template

Here is a practical example. Create a tab called Invoice with this structure:

The Rate column pulls the live EUR/USD rate from your Rates sheet. Every time the rates refresh, the USD amounts update automatically. This pattern works for any scenario where you receive or pay in one currency and report in another.

Handling Errors

The script includes basic error handling, but you can make it more robust by adding email notifications when the refresh fails:

    function fetchExchangeRates() {
      // ... existing code ...
    
      if (statusCode !== 200) {
        Logger.log(`API error: ${statusCode}`);
        MailApp.sendEmail(
          Session.getActiveUser().getEmail(),
          "Exchange Rate Refresh Failed",
          `The exchange rate refresh failed with HTTP ${statusCode}. ` +
          `Check your API key at https://exchange-rateapi.com.`
        );
        return;
      }
    
      // ... rest of function ...
    }
    

Now you will get an email if the daily refresh fails for any reason.

Tips for Working with Exchange Rates in Sheets

1. Format the Rate column. Right-click the Rate column and set the format to Number with 4-6 decimal places. Some rates (like USD/JPY at 149.32) need fewer decimals, while others (like USD/BTC) need more.

1. Freeze the header row. Go to View > Freeze > 1 row so headers stay visible as you scroll through 160+ currencies.

1. Use Named Ranges. Select the Rates data and create a named range called ExchangeRates. Then your VLOOKUP formulas become more readable: =VLOOKUP("EUR", ExchangeRates, 2, FALSE).

1. Combine with Data Validation. Create a dropdown list of currencies from the Rates sheet so users can only select valid currency codes.

1. Track rate changes over time. Add a script that appends daily rates to a log sheet instead of overwriting. This gives you a local historical record without extra API calls.

Security Notes

Your API key is stored in the Apps Script code, which is visible to anyone who has edit access to the spreadsheet. If you are sharing the sheet with others:

• Consider using the Script Properties store: go to Project Settings > Script Properties and add a property called API_KEY. Then reference it in code as PropertiesService.getScriptProperties().getProperty("API_KEY").
• Only share the spreadsheet with view access if collaborators do not need to modify the script.

Conclusion

Connecting an exchange rate API to Google Sheets takes about 10 minutes and gives you a self-updating currency data source that is far more reliable than GOOGLEFINANCE. With the Apps Script integration shown in this tutorial, you get 160+ currencies, automatic daily (or hourly) refreshes, historical rate lookups, and the foundation for invoices, reports, and dashboards that always use current exchange rates.

Ready to add live exchange rates to your spreadsheets? Sign up for a free API key at exchange-rateapi.com and paste the code above into your next Google Sheet.