Calling an external exchange-rate API every time a script needs a currency conversion wastes quota and adds latency for data that realistically does not change from one minute to the next, which makes it a good candidate for caching.
This tutorial wraps a currency conversion API call with CacheService's script cache, storing the parsed result for six hours so repeated calls within that window read from cache instead of making a new HTTP request.
getScriptCache() returns a cache shared across every user of the script, which is appropriate for data like exchange rates that are the same for everyone rather than user-specific information that would belong in a different cache scope.
A second function, writeConvertedTotals, shows the cached rates being used in a real batch calculation, converting a column of USD amounts into their equivalent values in whatever currency each row specifies.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Resource | Name | Purpose |
|---|---|---|
| Cache | CacheService.getScriptCache() | put/get JSON |
| Cache key | rates:usd | Cached UrlFetch result |
| TTL | 600 seconds | Expiration |
What it does
getExchangeRateCached checks the script cache for a previously stored rate set before making a network request, and stores freshly fetched rates for six hours so the next call within that window skips UrlFetchApp entirely.
Prerequisites
CacheService stores only strings, so the function calls JSON.stringify before putting the rates object into the cache and JSON.parse when reading a cached hit back out.
Walkthrough
No special authorization is required for CacheService itself; the exchange rate API used here is public, though the same caching wrapper applies just as well around an authenticated request.
Edge cases
The 21600-second argument to cache.put is the maximum Apps Script allows for a single cache entry, six hours, so a production version handling a longer-lived caching need should store a timestamp and re-check it manually rather than assuming a longer put duration is possible.
Testing
If the cache lookup misses and the subsequent UrlFetchApp call also fails, the function throws with the response code included, since serving stale or fabricated rates would be worse than a clear failure for anything involving money.
Hardening
Call getExchangeRateCached twice in quick succession and confirm the second call returns immediately without a new network request, which you can verify by temporarily logging inside the cache-miss branch.
Variations
CacheService documents its storage as best-effort rather than guaranteed, meaning a cache entry can be evicted before its expiration time under memory pressure, so code should always be able to fall back to fetching fresh data rather than assuming a cache hit will always be there.
Full code: getExchangeRateCached() and writeConvertedTotals()
Call getExchangeRateCached to fetch or reuse cached rates, then run writeConvertedTotals against a Currency sheet to see the cache used in a real calculation.
function getExchangeRateCached(baseCurrency) {
var cache = CacheService.getScriptCache();
var cacheKey = 'rate_' + baseCurrency;
var cached = cache.get(cacheKey);
if (cached) {
return JSON.parse(cached);
}
var url = 'https://api.exchangerate.host/latest?base=' + baseCurrency;
var response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
if (response.getResponseCode() !== 200) {
throw new Error('Exchange rate request failed with status ' + response.getResponseCode());
}
var data = JSON.parse(response.getContentText());
cache.put(cacheKey, JSON.stringify(data.rates), 21600);
return data.rates;
}
function writeConvertedTotals() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Currency');
var rates = getExchangeRateCached('USD');
var rows = sheet.getRange('A2:B' + sheet.getLastRow()).getValues();
var output = rows.map(function (row) {
var currencyCode = row[0];
var amountUsd = Number(row[1]) || 0;
var rate = rates[currencyCode] || 1;
return [currencyCode, amountUsd, Math.round(amountUsd * rate * 100) / 100];
});
if (output.length > 0) {
sheet.getRange(2, 1, output.length, 3).setValues(output);
}
}- Line 2: Reads from the script-wide cache shared by every user.
- Line 6: Returns the cached value immediately on a cache hit.
- Line 13: Checks the response code before trusting the fetched data.
- Line 17: Stores the serialized rates for the maximum six-hour duration.
- Line 23: Reuses the cached rates inside a real batch conversion.
- Line 31: Writes every converted row in a single setValues call.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: cache an API response
- 1Cache key includes enough context to avoid collisions between different lookups
- 2JSON.stringify and JSON.parse used consistently when caching non-string data
- 3Cache duration set to 21600 seconds or less, matching the six-hour maximum
- 4Fallback to a fresh UrlFetchApp call confirmed to work if the cache misses
- 5Repeated calls tested to confirm the cache actually avoids a second network request
- 6Money-related fallbacks treated as hard failures rather than stale data
- 7Cache scope (script vs user vs document) matches whether the data is shared or personal
Frequently asked questions
It is designed as a simple key-value byte store rather than a full object database, so anything structured, like the rates object here, needs to be serialized with JSON.stringify before storage and parsed back out after retrieval.
Six hours, or 21600 seconds, is the longest a single script cache entry can live; anything longer requires a different persistence mechanism such as Script Properties combined with your own timestamp check.
No, Google documents the cache as best-effort, meaning entries can be evicted early under memory pressure, so code should never assume a cache hit is guaranteed even within the stated expiration window.
Script cache is shared across every user of the script, user cache is scoped to the individual running the code, and document cache is scoped to the bound document, mirroring the same three-way split available for PropertiesService.
Exchange rates do not meaningfully change minute to minute for most reporting use cases, so caching avoids burning UrlFetchApp quota and network latency on a value that would return nearly identical results anyway.
Call cache.remove with the same key used in cache.put, which is useful when the underlying data changes and you don't want to wait out the remaining cache duration.