# 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.

## 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.

File: `.env`

```shell
CXR_API_KEY=cxr_your_secret_key
```

## 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.

File: `Code.gs`

```javascript
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;
}
```

## 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.

```text
=CXR_CONVERT(100, "EUR", "GBP")
```

## 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.

File: `Code.gs`

```javascript
// 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);
}
```

## Format the amount for display

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

```text
=TEXT(CXR_CONVERT(100, "EUR", "GBP"), "#,##0.00")
```

## Related links

- [API documentation](https://currencyexchangerate-api.com/docs.md)
- [All integration guides](https://currencyexchangerate-api.com/guides.md)
- [Get a free API key](https://currencyexchangerate-api.com/auth/login)