Calculate driving distance and travel time between addresses in Google Sheets

Road distance or straight line?

There are two different "distances":

• Straight-line (great-circle) distance needs only coordinates and a formula. It's free, instant and unlimited, but it's shorter than any real route. See distance between coordinates in Google Sheets.
• Road distance and travel time need a routing service, which means an API call per pair of addresses. That's what this guide covers.

A =DRIVE() formula with Apps Script

Google publishes an official driving distance sample for Sheets. The version below goes a bit further: it returns miles and minutes in one call and caches each answer, so recalculation doesn't spend quota twice within 6 hours.

1. Open Extensions → Apps Script, paste the code and click Save.
2. With origins in column A and destinations in column B, type =DRIVE(A2, B2) in C2. Miles go in C2 and minutes in D2.
3. Fill the formula down. For kilometres, divide the meter value by 1000 instead of 1609.344.

/**
 * Driving distance (miles) and time (minutes) between two places, from Google Maps.
 * =DRIVE(A2, B2) fills two cells: miles, minutes.
 *
 * @param {string} origin Start address (or "lat,lng").
 * @param {string} destination End address (or "lat,lng").
 * @return Miles and minutes.
 * @customfunction
 */
function DRIVE(origin, destination) {
  origin = String(origin || '').trim();
  destination = String(destination || '').trim();
  if (!origin || !destination) return [['', '']];

  const cache = CacheService.getDocumentCache();
  const key = 'dr:' + Utilities.base64EncodeWebSafe(
    Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, origin + '|' + destination));
  const hit = cache.get(key);
  if (hit) return [JSON.parse(hit)];

  const res = Maps.newDirectionFinder()
    .setOrigin(origin)
    .setDestination(destination)
    .setMode(Maps.DirectionFinder.Mode.DRIVING)
    .getDirections();
  if (res.status !== 'OK' || !res.routes.length) return [['', res.status]];

  const leg = res.routes[0].legs[0];
  const value = [
    Math.round(leg.distance.value / 1609.344 * 10) / 10,   // meters -> miles
    Math.round(leg.duration.value / 60)                    // seconds -> minutes
  ];
  cache.put(key, JSON.stringify(value), 21600);   // 6 hours: the longest CacheService allows
  return [value];
}

Origins and destinations can be addresses, place names or "lat,lng" text. To get walking or cycling instead, change DRIVING to WALKING or BICYCLING. Travel times are typical times from Google Maps, not live traffic for a specific departure.

Walking, transit, avoiding tolls, kilometres

If you need more than driving miles, use this =ROUTE() function instead of (or next to) =DRIVE(). It takes three optional settings: the travel mode, something to avoid, and the units.

/**
 * Distance and travel time between two places, from Google Maps, with options.
 * =ROUTE(A2, B2)                          driving, miles and minutes
 * =ROUTE(A2, B2, "walking", "", "km")     walking, kilometres and minutes
 * =ROUTE(A2, B2, "driving", "tolls")      driving, avoiding tolls
 *
 * @param {string} origin Start address, place name or "lat,lng".
 * @param {string} destination End address, place name or "lat,lng".
 * @param {string} mode driving (default), walking, bicycling or transit.
 * @param {string} avoid Leave empty, or tolls or highways.
 * @param {string} units mi (default) or km.
 * @return Distance and minutes, in two cells.
 * @customfunction
 */
function ROUTE(origin, destination, mode, avoid, units) {
  origin = String(origin || '').trim();
  destination = String(destination || '').trim();
  if (!origin || !destination) return [['', '']];
  mode = String(mode || 'driving').trim().toUpperCase();
  avoid = String(avoid || '').trim().toUpperCase();
  units = String(units || 'mi').trim().toLowerCase();
  const modes = Maps.DirectionFinder.Mode, avoids = Maps.DirectionFinder.Avoid;
  if (['DRIVING', 'WALKING', 'BICYCLING', 'TRANSIT'].indexOf(mode) === -1) {
    return [['', 'Mode must be driving, walking, bicycling or transit']];
  }
  if (avoid && ['TOLLS', 'HIGHWAYS'].indexOf(avoid) === -1) return [['', 'Avoid must be tolls or highways']];
  if (units !== 'mi' && units !== 'km') return [['', 'Units must be mi or km']];

  const cache = CacheService.getDocumentCache();
  const key = 'rt:' + Utilities.base64EncodeWebSafe(Utilities.computeDigest(
    Utilities.DigestAlgorithm.MD5, [origin, destination, mode, avoid, units].join('|')));
  const hit = mode === 'TRANSIT' ? null : cache.get(key);
  if (hit) return [JSON.parse(hit)];

  const finder = Maps.newDirectionFinder()
    .setOrigin(origin)
    .setDestination(destination)
    .setMode(modes[mode]);
  if (avoid) finder.setAvoid(avoids[avoid]);
  if (mode === 'TRANSIT') finder.setDepart(new Date());   // transit needs a departure time: now
  const res = finder.getDirections();
  if (res.status !== 'OK' || !res.routes.length) return [['', res.status]];
  const leg = res.routes[0].legs[0];
  const value = [
    Math.round(leg.distance.value / (units === 'km' ? 1000 : 1609.344) * 10) / 10,
    Math.round(leg.duration.value / 60)
  ];
  if (mode !== 'TRANSIT') cache.put(key, JSON.stringify(value), 21600);   // 6 hours
  return [value];
}

Add it to the same Apps Script project as =DRIVE() (or on its own) and save. A misspelt option gives a short message in the second cell instead of a call, so it doesn’t waste quota. Transit results depend on the departure time, so they aren’t cached, and a transit cell only recalculates when its inputs change; retype the formula if you need a fresh time. Each =ROUTE() call counts against the same daily quota as =DRIVE().

Round trips. The way out and the way back can differ (one-way streets, highway exits), so measure both directions: =INDEX(DRIVE(A2, B2), 1, 1) + INDEX(DRIVE(B2, A2), 1, 1) gives the round-trip miles in one cell, using two route calls.

Limits and how to stay under them

• Quota: Apps Script allows 1,000 direction queries a day on a personal account and 10,000 on Google Workspace. Over that you'll see Service invoked too many times for one day: route. It's the same problem, and the same fixes, as the geocoding quota error.
• Recalculation is what usually burns the quota. Once a row has its distance, copy it and use Paste special → Values only.
• 30 seconds per formula call. A single route is fast, but don't build one formula that loops over hundreds of pairs.
• Terms: these are Google Maps results, and the Maps Platform service terms restrict long-term storage. Read them before you keep thousands of results.

Many rows: menu script or matrix?

If you need distances from every origin to every destination (a matrix), the number of calls grows quickly: 30 stores × 30 customers is 900 routes. That's nearly a full day of personal quota. Options:

• Filter first with straight-line distance (free) and only route the pairs that matter, for example the 3 nearest stores per customer.
• Use a routing API with a matrix endpoint and your own key. Google, Geoapify and others offer one, with billing.

Mileage logs

For reimbursement logs, keep one row per trip with date, from, to, purpose and miles. Use =DRIVE() for the miles, then freeze them as values so the log doesn't change later. Many employers want the actual miles driven, so treat the route distance as an estimate and check your employer's or tax authority's rules.

With an add-on

Troubleshooting

“Unknown function: DRIVE”. The script isn’t saved, or it’s attached to a different spreadsheet. Custom functions only work in the spreadsheet whose Extensions → Apps Script project contains them. Open the editor from this spreadsheet, paste the code, click Save, then retype the formula.

“#REF! Array result was not expanded because it would overwrite data in D2”. The function returns two cells (distance and minutes), and the cell to the right isn’t empty. Clear that column, or wrap the call to keep one value: =INDEX(DRIVE(A2, B2), 1, 1) for distance, =INDEX(DRIVE(A2, B2), 1, 2) for minutes. Each wrapper is a separate call, so use one of them, not both, if quota matters.

NOT_FOUND in the minutes column. Google couldn’t place one of the two addresses. Add the city, state and country, fix typos, or use “lat,lng” for that end.

ZERO_RESULTS. Both places were found, but there’s no route in that mode, for example driving between continents or transit where there’s no service.

Cells stuck on “Loading…” or an execution time error. Filling hundreds of rows at once starts hundreds of calls together. Fill 50 to 100 rows at a time, wait for them to finish, paste them as values, and continue.

A different number from the Google Maps website. Maps on the web picks a route for the current traffic and offers alternatives; the script takes the first route Google returns, with typical times. Small differences are normal. A big one usually means one address matched somewhere else: check it by pasting the address into Google Maps.

The wrong town. A bare name like “Springfield” or “Main St 10” is ambiguous. Add the state and country to every address, ideally in the sheet itself, so the result is repeatable.

Frequently asked questions

Related guides

• How to make a mileage log in Google Sheets that calculates reimbursement
• Distance between two coordinates in Google Sheets (haversine formula)
• A latitude and longitude formula for Google Sheets
• How to geocode addresses in Google Sheets
• All guides

Disclosure: we make MapRows, a Google Sheets add-on mentioned in this guide. We describe the free methods as fairly as we can, including where they are the better choice.