Shortest distance in km google map-CodePudding

i use this script in google sheets to calculate my mileage charges.

    /**
* Get Distance between 2 different addresses.
* @param start_address Address as string Ex. "300 N LaSalles St, Chicago, IL"
* @param end_address Address as string Ex. "900 N LaSalles St, Chicago, IL"
* @param return_type Return type as string Ex. "miles" or "kilometers" or "minutes" or "hours"
* @customfunction
*/

function GOOGLEMAPS(start_address,end_address,return_type) {

  // https://www.chicagocomputerclasses.com/
  // Nov 2017
  // improvements needed
  
  var mapObj = Maps.newDirectionFinder();
  mapObj.setOrigin(start_address);
  mapObj.setDestination(end_address);
  var directions = mapObj.getDirections();
  
  var getTheLeg = directions["routes"][0]["legs"][0];
  
  var meters = getTheLeg["distance"]["value"];
  
  switch(return_type){
    case "miles":
      return meters * 0.000621371;
      break;
    case "minutes":
        // get duration in seconds
        var duration = getTheLeg["duration"]["value"];
        //convert to minutes and return
        return duration / 60;
      break;
    case "hours":
        // get duration in seconds
        var duration = getTheLeg["duration"]["value"];
        //convert to hours and return
        return duration / 60 / 60;
      break;      
    case "kilometers":
      return meters / 1000;
      break;
    default:
      return "Error: Wrong Unit Type";
   }
  
}

that works fine, but I would like the script to choose the shortest route in distance and not in time, like in this example :

It gives me 22.7km but I would like it to give me the shortest distance each time, in this case 21.3km

What can I change in the script so that it takes the shortest route in distance and not in time? Thank you!

CodePudding user response：

I believe your goal is as follows.

You want to retrieve the minimum distance.
You want to achieve this using a custom function of Google Apps Script.

In this case, in order to retrieve the multiple routes, I used setAlternatives(useAlternatives). And, the minimum distance is retrieved from the retrieved multiple routes.

When this is reflected in your script, it becomes as follows. Please modify your script as follows.

From:

var mapObj = Maps.newDirectionFinder();
mapObj.setOrigin(start_address);
mapObj.setDestination(end_address);
var directions = mapObj.getDirections();

var getTheLeg = directions["routes"][0]["legs"][0];

var meters = getTheLeg["distance"]["value"];

To:

var mapObj = Maps.newDirectionFinder().setAlternatives(true);
mapObj.setOrigin(start_address);
mapObj.setDestination(end_address);
var directions = mapObj.getDirections();
var getTheLeg = directions["routes"][0]["legs"][0];
var meters = Math.min(...directions.routes.map(({legs: [{distance: {value}}]}) => value));

If you want to use the value of getTheLeg from the same routes of meters, please modify as follows.

From

  var mapObj = Maps.newDirectionFinder();
  mapObj.setOrigin(start_address);
  mapObj.setDestination(end_address);
  var directions = mapObj.getDirections();

  var getTheLeg = directions["routes"][0]["legs"][0];

  var meters = getTheLeg["distance"]["value"];

  var mapObj = Maps.newDirectionFinder().setAlternatives(true);
  mapObj.setOrigin(start_address);
  mapObj.setDestination(end_address);
  var directions = mapObj.getDirections();
  var {getTheLeg, meters} = directions.routes.map(({legs: [{duration, distance}]}) => ({duration, distance})).reduce((o, {duration, distance}, i) => {
    if (i == 0 || o.meters > distance.value) {
      o.meters = distance.value;
      o.getTheLeg.duration.value = duration.value;
    }
    return o;
  }, {getTheLeg: {duration: {value: 0}}, meters: 0});

Note:

In this case, please enable the V8 runtime of the Google Apps Script project, if you didn't enable it.

From:

To:

Note:

References: