Geocoding a column of addresses one row at a time burns quota and time, and the same office address shows up forty times. geocodeCached(address, opts) normalizes the address, checks CacheService, and only calls the Maps geocoder on a miss. It returns { lat, lng, formatted } or null.
When to use
- Turning a client or delivery address column into lat/lng
- Re-running a sheet script without re-geocoding rows it already did
- Any loop where the same address repeats
How to use this snippet
/**
* Geocode an address with Maps.newGeocoder, cached in the script cache.
* Misses are cached for up to an hour so bad addresses aren't retried every row.
*
* @param {string} address
* @param {{region?: string, language?: string, ttlSeconds?: number}} [opts]
* @return {{lat: number, lng: number, formatted: string}|null}
*/
function geocodeCached(address, opts) {
opts = opts || {};
var key = String(address == null ? '' : address).trim().replace(/\s+/g, ' ');
if (!key) return null;
var cacheKey = 'geo:' + Utilities.base64EncodeWebSafe(
Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, key.toLowerCase()));
var cache = CacheService.getScriptCache();
var hit = cache.get(cacheKey);
if (hit) return JSON.parse(hit);
var geocoder = Maps.newGeocoder();
if (opts.region) geocoder.setRegion(opts.region);
if (opts.language) geocoder.setLanguage(opts.language);
var res = geocoder.geocode(key);
var top = res && res.status === 'OK' && res.results && res.results[0];
var out = top ? {
lat: top.geometry.location.lat,
lng: top.geometry.location.lng,
formatted: top.formatted_address
} : null;
// Cache misses too (shorter), so a bad address isn't retried every row.
var ttl = out ? (opts.ttlSeconds || 21600) : Math.min(opts.ttlSeconds || 21600, 3600);
cache.put(cacheKey, JSON.stringify(out), Math.min(ttl, 21600));
return out;
}
Example
function fillLatLng() {
var sh = SpreadsheetApp.getActive().getSheetByName('Clients');
var addr = sh.getRange('C2:C' + sh.getLastRow()).getValues();
var out = addr.map(function (r) {
var g = geocodeCached(r[0], { region: 'us' });
return g ? [g.lat, g.lng] : ['', ''];
});
sh.getRange(2, 4, out.length, 2).setValues(out);
}
Tips:
- The cache key is case- and whitespace-insensitive, so
1 Main Stand1 main stshare a result. CacheServicecaps entries at 6 hours. For permanent results, write lat/lng back to the sheet (as in the example) and skip rows that already have them.regionbiases results ('us','gb'). It doesn't restrict them.
Happy Coding!
