Add distance and ETA functions with Maps

Northwind delivers studio kits to clients across the country, and the logistics sheet needs a drive time and distance for every job. Looking each one up in a maps app and typing the answer back in is tedious, and the numbers go stale the moment a route plan changes.

These two custom functions put the lookup in the cell. =DRIVE_TIME(A2, B2) returns the driving ETA between two addresses, and =DRIVE_DISTANCE_KM(A2, B2) returns the distance in kilometres. Both share a cached route lookup, so a sheet full of formulas does not hammer the Maps service.

What you’ll need

• A Google Sheet with an origin and a destination in their own columns. Each can be a postcode, a full address or a place name.
• Nothing else — the Maps service and CacheService are built into Apps Script, no API key required.

The functions

// How long a route lookup stays cached, in seconds (6 hours).
const ROUTE_CACHE_TTL = 6 * 60 * 60;

/**
 * Custom function: driving ETA between two locations.
 * Use it in a cell, e.g. =DRIVE_TIME("London", "Manchester")
 *
 * @param {string} from  Origin address, postcode or place name.
 * @param {string} to    Destination address, postcode or place name.
 * @return {string} A human-readable duration, or '' if not found.
 * @customfunction
 */
function DRIVE_TIME(from, to) {
  // Bail out on empty inputs so the Maps service is never called for nothing.
  if (!from || !to) return '';
  const route = lookupRoute(from, to);
  return route ? route.duration.text : '';
}

/**
 * Custom function: driving distance between two locations, in kilometres.
 * Use it in a cell, e.g. =DRIVE_DISTANCE_KM("London", "Manchester")
 *
 * @param {string} from  Origin address, postcode or place name.
 * @param {string} to    Destination address, postcode or place name.
 * @return {number|string} Distance in km, or '' if not found.
 * @customfunction
 */
function DRIVE_DISTANCE_KM(from, to) {
  if (!from || !to) return '';
  const route = lookupRoute(from, to);
  // Maps returns distance in metres — convert to km.
  return route ? route.distance.value / 1000 : '';
}

/**
 * Looks up the first driving route between two points and returns its
 * leg. Results are cached so repeated formulas reuse one Maps call.
 *
 * @param {string} from  Origin.
 * @param {string} to    Destination.
 * @return {Object|null} The route leg, or null if no route exists.
 */
function lookupRoute(from, to) {
  // 1. Check the cache first — a hit means no Maps call at all.
  const cache = CacheService.getScriptCache();
  const key = `route:${from}->${to}`;
  const hit = cache.get(key);
  if (hit) return JSON.parse(hit);

  // 2. Cache miss — ask the Maps service for driving directions.
  const directions = Maps.newDirectionFinder()
    .setOrigin(from)
    .setDestination(to)
    .setMode(Maps.DirectionFinder.Mode.DRIVING)
    .getDirections();

  // 3. No route found (bad address, no road link) — give up cleanly.
  if (!directions.routes.length) return null;

  // 4. Cache the first leg for later formulas, then return it.
  const leg = directions.routes[0].legs[0];
  cache.put(key, JSON.stringify(leg), ROUTE_CACHE_TTL);
  return leg;
}

How it works

1. DRIVE_TIME and DRIVE_DISTANCE_KM are custom functions — the @customfunction tag makes each callable straight from a cell.
2. Both check their two arguments are present first. An empty origin or destination returns a blank cell instead of a wasted lookup.
3. Both delegate to lookupRoute, which is where the real work — and the caching — happens.
4. lookupRoute builds a cache key from the origin and destination and checks CacheService first. On a hit it returns the stored route leg without touching the Maps service at all.
5. On a miss it calls Maps.newDirectionFinder for a driving route. If no route comes back it returns null, and the caller shows an empty cell.
6. A successful lookup is cached for six hours (ROUTE_CACHE_TTL), so a column of formulas — or a recalculation — reuses one Maps call per unique pair.
7. DRIVE_TIME returns the human-readable duration.text; DRIVE_DISTANCE_KM takes distance.value in metres and divides by 1000.

Example run

With origins and destinations in columns A and B and the formulas in C and D:

From	To	=DRIVE_TIME(A,B)	=DRIVE_DISTANCE_KM(A,B)
Northwind HQ, London	Manchester	4 hours 12 mins	335.6
Northwind HQ, London	Brighton	1 hour 38 mins	86.4
Northwind HQ, London	(blank)

The third row is blank because the destination cell is empty — the guard skips the lookup.

Use them

There is nothing to schedule — these are custom functions:

1. Paste the script into the spreadsheet’s bound Apps Script project and save.
2. Back in the sheet, reference your origin and destination cells:

=DRIVE_TIME("Northwind HQ, London", B2)
=DRIVE_DISTANCE_KM("Northwind HQ, London", B2)

3. Approve the authorisation prompt the first time a function runs.

Watch out for

• The Maps service has a daily directions quota. A cache miss costs one lookup; the six-hour cache absorbs repeats, but a sheet full of unique routes recalculated often can still hit the limit.
• Custom functions run with a 30-second limit. A slow cluster of fresh lookups in one recalculation can time cells out — let the cache warm up, or paste results as static values once correct.
• CacheService entries are capped at 100 KB each and can be evicted under pressure. A route leg is small, so this is rarely an issue, but do not treat the cache as permanent storage.
• A cached route does not update for live traffic or roadworks. The six-hour TTL keeps numbers reasonably fresh; shorten ROUTE_CACHE_TTL if you need tighter accuracy.
• Vague inputs give vague routes. “Manchester” picks a city centre point — use full addresses or postcodes when the exact start and end matter.