Get the distance between 2 addresses in Google Sheets using Google Maps

GoogleDistance.gs

/**
* Get the distance between 2 different addresses.
* @param {string} origin_address The origin/start address as string Eg. "102 Petty France, London, SW1H 9AJ".
* @param {string} destination_address The destination/end address as string Eg. "10 Whitechapel High Street, London, E1 8QS".
* @param {string} travel_mode The mode of travel as string. Default: DRIVING. Options: BICYCLING, TRANSIT, WALKING.
* @param {string} return_type The return type as string. Default: MILES. Options: KILOMETERS, MINUTES, HOURS, STEPS.
* @return the distance between 2 different addresses.
* @customfunction
*/
function GOOGLEDISTANCE(origin_address,destination_address,travel_mode,return_type) {
  Utilities.sleep(1000);
  
  var travelMode = "";
  
  switch(travel_mode) {
    case "BICYCLING":
    case "bicycling":
      travelMode = Maps.DirectionFinder.Mode.BICYCLING;
      break;
    case "DRIVING":
    case "driving":
      travelMode = Maps.DirectionFinder.Mode.DRIVING;
      break;
    case "TRANSIT":
    case "transit":
      travelMode = Maps.DirectionFinder.Mode.TRANSIT;
      break;
    case "WALKING":
    case "walking":
      travelMode = Maps.DirectionFinder.Mode.WALKING;
      break;
    default:
      // Default to driving
      travelMode = Maps.DirectionFinder.Mode.DRIVING;
      //return "Error: Wrong travel mode";
  }

  // var auth = Maps.setAuthentication(clientId, signingKey);
  
  var directions = Maps.newDirectionFinder()
  .setRegion('UK')
  .setLanguage('en-GB')
  .setOrigin(origin_address)
  .setDestination(destination_address)
  .setMode(travelMode)
  .getDirections();

  if (directions.status !== "OK") 
    return "Error: " + directions.status;
  
  var route = directions.routes[0].legs[0];
  var time = route.duration.value;
  var distance = route.distance.value;
  
  var steps = route.steps.map(function(step) {
      return step.html_instructions.replace(/<[^>]+>/g, "");
  }).join("\n");
  
  switch(return_type) {
    case "MILES":
    case "miles":
      return distance * 0.000621371;
      break;
    case "KILOMETERS":
    case "kilometers":
      return distance / 1000;
      break;
    case "MINUTES":
    case "minutes":
      return time / 60;
      break;
    case "HOURS":
    case "hours":
      return time / 60 / 60;
      break;
    case "STEPS":
    case "steps":
      return steps;
      break;
    default:
      // Default to miles
      return distance * 0.000621371;
      //return "Error: Wrong return type";
  }

}

Amazing, thank you, this is really incredible!

Haven't ever used a custom function before on Google Sheets, so if anyone else finds this via google, you just have to add this as a custom script under Tools > Script editor. Then you can use the GOOGLEDISTANCE function like any other sheets function.

For example =GOOGLEDISTANCE(A1, A2, "diriving", "minutes")
Or =GOOGLEDISTANCE("100 Someplace", "200 otherplace", "driving", "minutes")
Or you can also use lat, long co-ords: =GOOGLEDISTANCE("22.5985, 47.6767", "45.3545, 23.44545", "walking", "minutes")

This is perfect, thanks! 2 questions: (1) would it work as well or better with GPS coordinates (lat/long)? and (2) I need to populate ~30K cells and I understand there may be a charge. Do you know anything more about that?

@spookyUnknowsUser
could you help I'm giving the coordinates and the function is calculating the double distance in KM also if i change the kilometers to minutes or anything else it is giving me the same results.

Sorry, I'm not really familiar with coding. I see on line 38 you have setAuthentication, but this has been discontinued by google. https://developers.google.com/apps-script/reference/maps/maps

How should the I update this just using my API key?

thanks

@foose212 seems like we are now to use a regular HTTP API request, using the Google Scripts URLfetch service

Hello,

Does anyone know how i can use the =GOOGLEDISTANCE formula to determine the clossest location?

I have 3 traininglocations and want to determine wich location is the clossest to the employers house. I tryed with using the =MIN formula, but dont get the values right.

Currently the code is giving me the distance in miles when I request for it to be in Kilometres, anyone had this problem?

@eliza13-fry Have you checked the spelling of kilometers (it’s “er” not “re” at the end)?