Why does the formula return #ERROR! with 'Service Maps not enabled'?

You need to explicitly add the Maps service in the Apps Script editor. Go to Extensions > Apps Script, click the + next to Services, find Google Maps, and click Add. The Maps identifier must match — it is Maps, not MapsApp or MapsService.

Can I get travel time (duration) instead of distance?

Yes. Each leg also has a duration object with a value in seconds. Replace leg.distance.value with leg.duration.value in the reduce, then divide by 3600 for hours or 60 for minutes. You can return both from the same API call to avoid burning a second quota hit.

Does this work with transit or walking directions?

Change Maps.DirectionFinder.Mode.DRIVING to Maps.DirectionFinder.Mode.WALKING or Maps.DirectionFinder.Mode.TRANSIT. Transit distance is often less useful than transit duration, since transit routes do not follow road geometry. Walking mode is accurate for pedestrian routing.

The cache key collision — what if two different address pairs share the same concatenated string?

It is theoretically possible if an origin ends with the pipe character, but real addresses never contain pipes. If you are using data where that is a risk, switch the separator to something like ' ### ' or use Utilities.base64Encode on the key string to make it unambiguous.

// Sheets · Apps Script

Get driving distance between two addresses in Google Sheets.

Use Google Apps Script and the Maps service to calculate driving distance between two addresses directly in a Sheets formula, with caching to stay inside the Maps quota.

“I want a custom Sheets formula that returns the driving distance between two address cells without leaving the spreadsheet or hitting a paid API.”

The script

copy · paste · trigger

drivingDistance.gs

Apps Script

// Returns driving distance between two addresses.
// Unit: 'km' (default) or 'mi'. Results are cached for 6 hours.
function DRIVING_DISTANCE(origin, destination, unit) {
  var cache = CacheService.getScriptCache();
  var key = origin + '|' + destination;
  var cached = cache.get(key);
  if (cached) return Number(cached);

  var finder = Maps.newDirectionFinder()
    .setOrigin(origin)
    .setDestination(destination)
    .setMode(Maps.DirectionFinder.Mode.DRIVING)
    .getDirections();

  var legs = finder.routes[0].legs;
  var metres = legs.reduce(function(sum, leg) { return sum + leg.distance.value; }, 0);
  var result = (unit === 'mi') ? metres / 1609.344 : metres / 1000;
  result = Math.round(result * 10) / 10;

  cache.put(key, String(result), 21600);
  return result;
}

Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

Where the distance actually lives in the API response

The Maps service returns a directions object with a routes array. Each route contains a legs array — one leg per waypoint segment. Each leg has a distance object whose value property is an integer in metres. A direct A-to-B trip has exactly one leg, but the reduce handles multi-leg routes without any extra logic.

Dividing by 1000 gives kilometres; dividing by 1609.344 gives miles. The function rounds to one decimal place, which is precise enough for routing work and avoids floating-point noise like 42.300000000004.

One thing that bit me early: the API returns the distance for the chosen route, not the shortest possible route. Google defaults to its recommended route (usually the fastest). If you need shortest-distance specifically, there is no direct flag — you would have to request multiple alternatives and pick the minimum, which burns extra quota calls.

Enabling the Maps service and staying inside quota

Open your script from Extensions > Apps Script, then go to Services (the + icon in the left sidebar) and add Maps. Without this step the script throws a ReferenceError on Maps.newDirectionFinder.

The Maps service quota for consumer Workspace accounts is 1,000 direction requests per day per script. That sounds comfortable until you have a sheet with 200 rows and someone hits Ctrl+A, Ctrl+C, Ctrl+V, triggering a recalculation storm. CacheService.getScriptCache stores results for 21,600 seconds (6 hours), so repeated lookups for the same address pair cost nothing. The cache key concatenates origin and destination with a pipe separator — make sure your address strings are trimmed and consistent, because 'Chicago, IL' and 'chicago, il' produce two separate cache entries.

If you hit the daily limit, the function throws an exception that Sheets displays as #ERROR!. Adding a try/catch that returns the string 'QUOTA' instead makes the failure visible without breaking the whole column.

Using the formula in your sheet

Once the script is saved, use it like any built-in function: =DRIVING_DISTANCE(A2, B2) returns kilometres, and =DRIVING_DISTANCE(A2, B2, "mi") returns miles. Full street addresses work, as do city names, ZIP codes, and Plus Codes — the Maps geocoder handles the lookup internally.

For a column of origin/destination pairs, drag the formula down. The first run populates the cache; subsequent refreshes within 6 hours return instantly from cache. If you want to force a fresh lookup (e.g., after correcting a typo), delete the row's cache entry by calling CacheService.getScriptCache().remove(origin + '|' + destination) from the script editor, or just wait for the 6-hour TTL to expire.

The third unit argument is optional. Omitting it defaults to kilometres. Passing anything other than 'mi' also defaults to kilometres, so 'km' and '' both work as fallbacks.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions

Why does the formula return #ERROR! with 'Service Maps not enabled'?: You need to explicitly add the Maps service in the Apps Script editor. Go to Extensions > Apps Script, click the + next to Services, find Google Maps, and click Add. The Maps identifier must match — it is Maps, not MapsApp or MapsService.
Can I get travel time (duration) instead of distance?: Yes. Each leg also has a duration object with a value in seconds. Replace leg.distance.value with leg.duration.value in the reduce, then divide by 3600 for hours or 60 for minutes. You can return both from the same API call to avoid burning a second quota hit.
Does this work with transit or walking directions?: Change Maps.DirectionFinder.Mode.DRIVING to Maps.DirectionFinder.Mode.WALKING or Maps.DirectionFinder.Mode.TRANSIT. Transit distance is often less useful than transit duration, since transit routes do not follow road geometry. Walking mode is accurate for pedestrian routing.
The cache key collision — what if two different address pairs share the same concatenated string?: It is theoretically possible if an origin ends with the pipe character, but real addresses never contain pipes. If you are using data where that is a risk, switch the separator to something like ' ### ' or use Utilities.base64Encode on the key string to make it unambiguous.

// one good script a week