如何修改Google Sheets谷歌地图里程脚本以获取最短距离而非时间优先路线

核心修改说明 #

• 调用Google Maps方向查询接口时，主动指定最短距离优先的优化策略，替换默认的最短耗时优先策略
• 增加多路线遍历校验逻辑，遍历所有返回的可选路线，主动筛选出实际距离最小的路线，避免单取第一条路线产生的误差

修改后的完整代码 #

/**
* 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) {
  
  var mapObj = Maps.newDirectionFinder();
  mapObj.setOrigin(start_address);
  mapObj.setDestination(end_address);
  // 新增：指定优化策略为最短距离
  mapObj.setOptimizeFor(Maps.DirectionFinder.Optimization.SHORTEST);
  var directions = mapObj.getDirections();
  
  var routes = directions["routes"];
  var minDistance = Infinity;
  var targetLeg = null;
  // 新增：遍历所有路线找到距离最短的分段
  for (var i = 0; i < routes.length; i++) {
    var currentLeg = routes[i]["legs"][0];
    var currentDistance = currentLeg["distance"]["value"];
    if (currentDistance < minDistance) {
      minDistance = currentDistance;
      targetLeg = currentLeg;
    }
  }

  var meters = targetLeg["distance"]["value"];
  
  switch(return_type){
    case "miles":
      return meters * 0.000621371;
      break;
    case "minutes":
        var duration = targetLeg["duration"]["value"];
        return duration / 60;
      break;
    case "hours":
        var duration = targetLeg["duration"]["value"];
        return duration / 60 / 60;
      break;      
    case "kilometers":
      return meters / 1000;
      break;
    default:
      return "Error: Wrong Unit Type";
   }
  
}

内容的提问来源于stack exchange，提问作者Hugo