Import Official Exchange Rates into Google Sheets via API

If you need official currency exchange rates in Google Sheets for accounting, reporting, or dashboards, you can pull them directly from the Global Exchange Rates API using a simple Apps Script.

The rates come from official institutions (e.g. central banks), making them suitable for enterprise use.

Step-by-Step

1. Create a new Google Sheet

Create a new spreadsheet and name the first tab Exchange Rates.

2. Open Apps Script

Go to Extensions → Apps Script

3. Paste the following script

function GLOBAL_EXCHANGE_RATES(apiKey, baseCurrency, provider) {
  if (!apiKey) {
    throw new Error("API key is required.");
  }
  if (!baseCurrency) {
    baseCurrency = "EUR"; // Default to EUR if not provided
  }

  let url = `https://api.globalexchangerates.org/v1/latest?base=${baseCurrency}`;
  if (provider) {
    url += `&provider=${encodeURIComponent(provider)}`;
  }

  const options = {
    method: 'get',
    headers: {
      'Subscription-Key': apiKey
    },
    muteHttpExceptions: true
  };

  try {
    const response = UrlFetchApp.fetch(url, options);
    const json = response.getContentText();
    const data = JSON.parse(json);

    if (response.getResponseCode() !== 200 || !data.exchangeRates) {
      throw new Error(`API Error: ${data.message || 'Could not fetch rates.'}`);
    }

    const rates = data.exchangeRates;
    let result = [["Currency", "Rate against " + baseCurrency]];

    result.push([data.base, 1.0]); // Include base currency explicitly

    for (const currency in rates) {
      result.push([currency, rates[currency]]);
    }

    return result;

  } catch (e) {
    return [["Error"], [e.message]];
  }
}

4. Save the script

Click the floppy disk icon or hit Ctrl + S. You can name the project anything.

5. Use the function in your sheet

Go back to your spreadsheet and in cell A1, enter:

GLOBAL_EXCHANGE_RATES("YOUR_API_KEY_HERE", "EUR", "ECB")

Replace "YOUR_API_KEY_HERE" with your actual key from our developer portal. You can request the exchange rates from one of 50+ central banks available in our API, or leave it empty to fetch our exchange rates.

These values will refresh automatically when the sheet is opened or recalculated. To reduce API usage, you can schedule the script to refresh once per day via Triggers.

You can now combine the exchange rates with your data, for example by blending these rates with transactional data or connecting this sheet to Looker Studio or another BI tool.