Write a custom function to geocode addresses

Northwind keeps every client office on the Clients sheet, address and all. The moment someone wants to plot those offices on a map, build a delivery route, or sort clients by distance, they need coordinates — and copying each address into a separate geocoding site is exactly the kind of manual job that never gets done.

A =GEOCODE() custom function turns the address column into latitude and longitude without leaving Sheets. It uses the built-in Maps service, so there is no API key to manage, and it caches every result so dragging the formula down a long column does not burn through the daily geocoding quota.

What you’ll need

• A Google Sheet with addresses in a column — for Northwind that is the Clients sheet, address in column B.
• Two empty columns next to the addresses for the latitude and longitude the function spills out.
• Nothing else — the built-in Maps service needs no key or setup.

The script

// How long a geocoding result stays in cache, in seconds. Six hours keeps
// re-calculations off the Maps quota without the data going stale.
const GEOCODE_CACHE_SECONDS = 6 * 60 * 60;

/**
 * Geocodes a single address to a latitude/longitude pair, returned as two
 * cells. Results are cached so re-calculating a sheet does not re-hit the
 * geocoding quota.
 *
 * @param {string} address The address to look up.
 * @return {number[]} A [latitude, longitude] pair, or two blank cells.
 * @customfunction
 */
function GEOCODE(address) {
  // 1. An empty cell is not an error — return blank.
  if (!address) return '';

  // 2. Look for a cached result for this exact address.
  const cache = CacheService.getScriptCache();
  const cacheKey = 'geo:' + address;
  const hit = cache.get(cacheKey);
  if (hit) return JSON.parse(hit);

  // 3. Cache miss — ask the built-in Maps geocoder.
  const res = Maps.newGeocoder().geocode(address);

  // 4. No match found — return two blank cells, not an error.
  if (!res.results.length) return ['', ''];

  // 5. Take the first (best) match and pull out its coordinates.
  const { lat, lng } = res.results[0].geometry.location;
  const out = [lat, lng];

  // 6. Cache the pair so the next recalculation is free.
  cache.put(cacheKey, JSON.stringify(out), GEOCODE_CACHE_SECONDS);
  return out;
}

How it works

1. GEOCODE returns an empty string for an empty cell, so unfilled rows stay clean instead of showing an error.
2. It builds a cache key from the address and checks CacheService first. Custom functions are re-evaluated on every recalculation, so without the cache a busy sheet would call the geocoder again and again for the same addresses.
3. On a cache miss it calls Maps.newGeocoder().geocode(), the anonymous geocoding service built into Apps Script.
4. If the geocoder finds no match, the function returns two blank cells rather than an error, so a slightly wrong address does not break the column.
5. It takes the first result — the geocoder’s best guess — and reads its lat and lng.
6. It stores the pair in cache for six hours (GEOCODE_CACHE_SECONDS) and returns it. Sheets spills the two values into the cell and the one beside it.

Example run

With addresses in column B of the Clients sheet, putting =GEOCODE(B2) in column C produces:

B (Address)	C (Lat)	D (Lng)
10 Downing Street, London	51.5034	-0.1276
1600 Amphitheatre Pkwy, Mountain View	37.4221	-122.0841
Brandenburg Gate, Berlin	52.5163	13.3777

Each formula fills two cells — the latitude where you typed it and the longitude immediately to its right.

Use it

Put the formula in the first empty column next to your address column:

=GEOCODE(B2)

It spills the latitude into that cell and the longitude into the next one, so leave the column to its right empty. Drag the formula down to geocode the whole list at once.

Watch out for

• The built-in geocoder has a daily quota. Caching for six hours means re-calculating the sheet does not re-spend it, but geocoding a few thousand fresh addresses in one pass can still hit the limit — split a very large list across days.
• The function returns the geocoder’s first, best-guess match. A vague address (“Main Street, Springfield”) may resolve to the wrong place — include the postcode or country to keep results accurate.
• A misspelt address returns two blank cells, not an error. Scan for blank rows after geocoding and correct the source addresses.
• Custom functions cannot use user-scoped services, which is why this uses anonymous Maps geocoding. It also means the function cannot reach a paid Google Maps API key — for higher volume you would need a separate non-custom-function script.
• A cached coordinate can be up to six hours old. That is harmless for fixed office addresses; if you geocode something that genuinely moves, shorten GEOCODE_CACHE_SECONDS.