Integration guide

Add currency exchange rates and conversion to Google Sheets

Create a Google Apps Script custom function. Store the key in Script Properties, use UrlFetchApp on the server, and CacheService until nextUpdateAt.

1. Get a free API key

Sign up at https://currencyexchangerate-api.com/auth/login, then create a key under API keys in the console. The free plan includes 3,000 requests per month with no credit card. Store the secret as CXR_API_KEY in server-side environment configuration; never expose it in browser code.

.env

shell
CXR_API_KEY=cxr_your_secret_key

2. Create a reusable client and cache rates

Use the standard HTTP client. Cache every /latest response until nextUpdateAt: rates change at most once by then, and reusing the response saves quota. The helper also exposes /convert.

Code.gs

Google Apps Script
const API_BASE_URL = 'https://api.currencyexchangerate-api.com/api/v1'; /** Formula: =CXR_CONVERT(amount, from, to) */function CXR_CONVERT(amount, from, to) {  const key = PropertiesService.getScriptProperties().getProperty('CXR_API_KEY');  const cache = CacheService.getScriptCache();  const cacheKey = 'rate:' + from + ':' + to;  let body = cache.get(cacheKey);  if (!body) {    const query =      'base=' + encodeURIComponent(from) + '&symbols=' + encodeURIComponent(to);    const response = UrlFetchApp.fetch(API_BASE_URL + '/latest?' + query, {      headers: { Authorization: 'Bearer ' + key },      muteHttpExceptions: true,    });    body = response.getContentText();    const status = response.getResponseCode();    if (status >= 400) {      const detail = JSON.parse(body).detail || body;      throw new Error('API status ' + status + ': ' + detail);    }    const data = JSON.parse(body);    // CacheService allows at most 21600 seconds (6 hours)    const remaining = Math.floor((Date.parse(data.nextUpdateAt) - Date.now()) / 1000);    const seconds = Math.min(21600, Math.max(1, remaining));    cache.put(cacheKey, body, seconds);  }  const rate = JSON.parse(body).rates[to];  if (rate === undefined) {    throw new Error('No rate for ' + to);  }  return amount * rate;}

3. Use it in Google Sheets

Call the helper from a server route, controller, view, command, or script. Format converted values for the user’s locale.

Sheet formula

Sheet formula
=CXR_CONVERT(100, "EUR", "GBP")

4. Handle API errors

A 401 means the key is invalid. A 429 can mean a rate limit or quota limit; read Retry-After in seconds before retrying. Other errors use application/problem+json.

Code.gs

Google Apps Script
// Use in place of the generic throw after// UrlFetchApp.fetch(..., { muteHttpExceptions: true })function apiError(response) {  const status = response.getResponseCode();  if (status === 401) {    return new Error('Check CXR_API_KEY');  }  if (status === 429) {    const retryAfter = response.getHeaders()['Retry-After'];    return new Error('Retry after ' + retryAfter + ' seconds');  }  const detail = JSON.parse(response.getContentText()).detail;  return new Error(detail || 'API status ' + status);}

5. Format the amount for display

Use locale-aware currency formatting instead of adding a symbol manually.

Sheet formula

Sheet formula
=TEXT(CXR_CONVERT(100, "EUR", "GBP"), "#,##0.00")