google sheets - Looking for distance between multiple points in a googlesheets script

Question

// adapted from developers.google.com/apps-script/quickstart/macros
function drivingDistance(origin, waypoint, destination) {
    var directions = getDirections_(origin, destination);
    return directions.routes[0].legs[0].distance.value;
}

function getDirections_(origin, waypoint, destination) {
    var directionFinder = Maps.newDirectionFinder();
    directionFinder.setOrigin(origin);
    directionFinder.addWaypoint(waypoint);
    directionFinder.setDestination(destination);

    var directions = directionFinder.getDirections();
    if (directions.routes.length == 0) {
    throw 'Unable to calculate directions between these addresses.';
    }
    return directions;
}

This script originally checked column A for origin and then checked column B for destination. I was trying to use the addWaypoint function to add a 3rd point, but have been unsuccessful. I read through the API and tried a few different variations, but am afraid that I was again unsuccessful. How do I add multiple destinations or waypoints

Answer 1

When there are multiple waypoints, a route will consist of multiple legs, each of which has its own distance. So once you have the route calculated, you still need to go through each of the legs and sum up the individual distances to arrive at a final value.

Once we have this figured out for one mid-point, surely we'll want it for two. And once we have it for two, why not three? Finally, why not an arbitrary number of waypoints?

See Accept Arbitrary Number of Arguments in Google Scripts Custom Function? for an example of how a custom function with arbitrary parameters will behave in a Spreadsheet.

This modified script handles a route with any number of points. It's fully supported by Auto-completion within Sheets. And it can handle a list of waypoints, or a range of cells. It's also copiously commented to explain each step.

Basic use:

=drivingDistance(A2:D2)

More advanced use, with a more understandable output:

=TEXT(drivingDistance(A2:D2)/1000,"#,### km")

Code

Enough jabbering, just give me teh codez! (Updated code is available on gist.github.com.)

/**
 * Calculate the driving distance (in meters) along a route.
 *
 * @param {"london","manchester","liverpool"} route
 *        Comma separated ordered list of two or more map
 *        waypoints to include in route. First point
 *        is 'origin', last is 'destination'.
 *
 * @customfunction
 */
function drivingDistance(route) {
  // From gist.github.com/mogsdad/e07d537ff06f444866c5
  // Adapted from developers.google.com/apps-script/quickstart/macros
  // If a range of cells is passed in, 'route' will be a two-dimensional array.
  // Test for an array, and if we have one, collapse it to a single array.
  if (route.constructor === Array) {
    var args = route.join(',').split(',');
  }
  else {
    // No array? Grab the arbitrary arguments passed to the function.
    args = arguments;
  }
  
  // Just one rule to a route - we need a beginning and an end
  if (args.length < 2) throw new Error( "Must have at least 2 waypoints." )
  
  // Pass our waypoints to getDirections_(). Tricky bit, this.
  var directions = getDirections_.apply(this, args);

  // We have our directions, grab the first route's legs
  var legs = directions.routes[0].legs;
  
  // Loop through all legs, and sum up distances
  var dist = 0;
  for (var i=0; i<legs.length; i++) {
    dist += legs[i].distance.value;
  }
  
  // Done - return the value in meters
  return dist;
}

/**
 * Use Maps service to get directions for a route consisting of an arbitrary
 * set of waypoints.
 */
function getDirections_(route) {
  // Just one rule to a route - we need a beginning and an end
  if (arguments.length < 2) throw new Error( "Must have at least 2 waypoints." )
  
  // Assume first point is origin, last is destination.
  var origin = arguments[0];
  var destination = arguments[arguments.length-1];
  
  // Build our route; origin + all midpoints + destination
  var directionFinder = Maps.newDirectionFinder();
  directionFinder.setOrigin(origin);
  for ( var i=1; i<arguments.length-1; i++ ) {
    directionFinder.addWaypoint(arguments[i]);
  }
  directionFinder.setDestination(destination);

  // Get our directions from Map service;
  // throw an error if no route can be calculated.
  var directions = directionFinder.getDirections();
  if (directions.routes.length == 0) {
    throw 'Unable to calculate directions between these addresses.';
  }
  return directions;
}