Find the distance (in KM or MI) and driving time between locations stored in Google Sheets. Distances and Time provided by Google Apps Script Maps Service.

drivingDistanceAndTime.gs

/** drivingDistanceAndTime.gs **/ 
/** Find the distance (in KM or MI) and driving time between locations 
stored in Google Sheets. Distances and Time provided by Google Apps Script Maps Service */
/** Note: This is built on top of Google App Scripts Code **/
/** Licensed under the Apache 2.0 License **/
/** By: Sandu Rajapakse - lankanmon **/

/** === USAGE INSTRUCTIONS ===

To use these functions in Google Sheets, place this code in a new script in the Script Editor.
Tools -> Script Editor...
(erase any existing code and paste this)
++++++++++++++++++++++++++++
To get Distance, place following function in a cell (eg. C1):
=DRIVINGDISTANCE(A1,B1)

* Replace A1 with starting address and B1 with destination address *
This will return the distance between the two addresses in meters.
============
To convert meters to Kilometers or Miles, USE:
=METERSTOKILOMETERS(C1)
and
=METERSTOMILES(C1)
* Replace C1 with a Metre value to convert to KM or MI.
============
To do both in one operation, embed first operation in second:
=METERSTOKILOMETERS(DRIVINGDISTANCE(A1,B1))
=METERSTOMILES(DRIVINGDISTANCE(A1,B1))

++++++++++++++++++++++++++++

To get Time, place following function in a cell (eg. D1):
=DRIVINGTIME(A1,B1)
* Replace A1 with starting address and B1 with destination address *
This will return the time between the two addresses in seconds.
============
To convert Seconds to Minutes or Hours, USE:
=SECONDSTOMINUTES(D1)
=SECONDSTOHOURS(D1)
* Replace D1 with a Seconds value to convert to Minutes or Hours.
============
To do both in one operation, embed first operation in second:
=SECONDSTOMINUTES(DRIVINGTIME(A1,B1))
=SECONDSTOHOURS(DRIVINGTIME(A1,B1))

*** Lave a Comment if you have any Questions ***
- lankanmon
*/

/**
 * Function that converts meters to miles.
 *
 * @param {Number} meters - The distance in meters.
 * @return {Number} - The distance in miles.
 */
function metersToMiles(meters) {
  if (typeof meters != 'number') {
    return null;
  }
  return meters / 1000 * 0.621371;
}

/**
 * Function that converts meters to kilometres.
 *
 * @param {Number} meters - The distance in meters.
 * @return {Number} - The distance in kilometers.
 */
function metersToKilometers(meters) {
  if (typeof meters != 'number') {
    return null;
  }
  return meters / 1000;
}

/**
 * Function gets the driving distance between two addresses.
 *
 * @param {String} origin The starting address.
 * @param {String} destination The ending address.
 * @return {Number} The distance in meters.
 */
function drivingDistance(origin, destination) {
  var directions = getDirections_(origin, destination);
  return directions.routes[0].legs[0].distance.value;
}


/**
 * Function that converts seconds to minutes.
 *
 * @param {Number} seconds - The distance in seconds.
 * @return {Number} minutes - The distance in minutes.
 */
function secondsToMinutes(seconds) {
  if (typeof seconds != 'number') {
    return null;
  }
  return seconds / 60;
}

/**
 * Function that converts seconds to hours.
 *
 * @param {Number} seconds - The distance in seconds.
 * @return {Number} hours - The distance in hours.
 */
function secondsToHours(seconds) {
  if (typeof seconds != 'number') {
    return null;
  }
  return ((seconds / 60) / 60);
}

/**
 * Function gets the driving time between two addresses.
 *
 * @param {String} origin The starting address.
 * @param {String} destination The ending address.
 * @return {Number} The time in seconds.
 */
function drivingTime(origin, destination) {
  var directions = getDirections_(origin, destination);
  return directions.routes[0].legs[0].duration.value;
}

/**
 * Shared helper function used to obtain the full set of directions
 * information between two addresses. Uses the Apps Script Maps Service.
 *
 * @param {String} origin The starting address.
 * @param {String} destination The ending address.
 * @return {Object} The directions response object.
 */
function getDirections_(origin, destination) {
  var directionFinder = Maps.newDirectionFinder();
  directionFinder.setOrigin(origin);
  directionFinder.setDestination(destination);
  var directions = directionFinder.getDirections();
  if (directions.routes.length == 0) {
    throw 'Unable to calculate directions between these addresses.';
  }
  return directions;
}