drivingDistance() is a Google Sheets custom function that calculates the driving distance along a possibly multi-point route.

drivingDistance.gs

/**
 * 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 = [];
    for (var row=0; row<route.length; row++) {
      for (var col=0; col<route[row].length; col++) {
        // Skip blanks
        if (route[row][col]) args.push(route[row][col]);
      }
    }
  }
  else {
    // No array? Grab the arbitrary arguments passed to the function.
    args = arguments;
  }
  args = args.clean("");  // remove blanks
  
  // 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) {
  // From gist.github.com/mogsdad/e07d537ff06f444866c5
  // 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;
}

// From stackoverflow.com/a/281335/1677912
Array.prototype.clean = function(deleteValue) {
  for (var i = 0; i < this.length; i++) {
    if (this[i] == deleteValue) {         
      this.splice(i, 1);
      i--;
    }
  }
  return this;
};

I used the script but today it stopped working and the error "Exception: Service invoked too many times for one day: route." (Line 74) jumped out. " I bought the API key but I don't know where to put it, can you help?

@iktorns did you get your issue resolved ?