寻找googlesheets脚本中多个点之间的距离 [英] Looking for distance between multiple points in a googlesheets script

问题描述

//改编自developers.google.com/apps-script/quickstart/macros
函数drivingDistance （起源，航点，目的地）{
var directions = getDirections_（起源，目的地）;
返回directions.routes [0] .legs [0] .distance.value;


function getDirections_（origin，waypoint，destination）{
var directionFinder = Maps.newDirectionFinder（）;
directionFinder.setOrigin（origin）;
directionFinder.addWaypoint（waypoint）;
directionFinder.setDestination（destination）;

var directions = directionFinder.getDirections（）;
if（directions.routes.length == 0）{
throw'无法计算这些地址之间的方向。';
}
返回方向;
}


此脚本最初检查A列的原点，然后检查B列的目的地。我试图使用addWaypoint函数来添加第三个点，但不成功。我通读了API并尝试了一些不同的变体，但是恐怕我再次失败了。如何添加多个目的地或路标

解决方案

当存在多个路标时， route 将由多个腿组成，每个腿都有自己的距离。因此，一旦你计算出了路线，你仍然需要仔细检查每条腿，并总结出各个距离才能达到最终值。

一旦我们有了这个想出了一个中间点，当然我们会想要两个。一旦我们有两个，为什么不是三个？最后，为什么不是任意数量的航点？

请参阅上查看。）

/ **
*计算沿路线的行驶距离（以米为单位）。
*
* @param {london，manchester，liverpool} route
*两个或多个地图的逗号分隔的有序列表
* 。第一点
*是'起源'，最后是'目的地'。
*
* @customfunction
* /
函数drivingDistance（route）{
//从gist.github.com/mogsdad/e07d537ff06f444866c5
//改编自developers.google.com/apps-script/quickstart/macros
//如果传入一系列单元格，'route'将是一个二维数组。
//测试一个数组，如果我们有一个，则将其折叠为一个数组。
if（route.constructor === Array）{
var args = route.join（'，'）。split（'，'）;
}
else {
//没有数组？抓取传递给该函数的任意参数。
args =参数;
}

//对于路由只需要一条规则 - 我们需要一个开始和结束
if（args.length< 2）throw new Error（Must have at至少2个航点）

//将我们的航点传递给getDirections_（）。棘手的一点，这个。
var directions = getDirections_.apply（this，args）;

//我们有我们的指示，抓住第一条路线的腿
var legs = directions.routes [0] .legs;

//循环所有腿，并总结距离
var dist = 0;
for（var i = 0; i< legs.length; i ++）{
dist + = legs [i] .distance.value;
}

//完成 - 以米为单位返回值
return dist;
}

/ **
*使用地图服务获取由任意
*路标组成的路线的路线。
* /
函数getDirections_（route）{
//对路由只有一条规则 - 我们需要一个开始和结束
if（arguments.length <2）throw新错误（必须至少有2个航点）

//假设第一个点是起点，最后一个点是目的地。
var origin = arguments [0];
var destination = arguments [arguments.length-1];

//建立我们的路线;原点+所有中点+目的地
var directionFinder = Maps.newDirectionFinder（）;
directionFinder.setOrigin（origin）;
for（var i = 1; i< arguments.length-1; i ++）{
directionFinder.addWaypoint（arguments [i]）;
}
directionFinder.setDestination（destination）;

//从地图服务获取我们的路线;
//如果不能计算路线，则抛出错误。
var directions = directionFinder.getDirections（）;
if（directions.routes.length == 0）{
throw'无法计算这些地址之间的方向。';
}
返回方向;
}


// adapted from developers.google.com/apps-script/quickstart/macros
function drivingDistance(origin, waypoint, destination) {
    var directions = getDirections_(origin, destination);
    return directions.routes[0].legs[0].distance.value;
}

function getDirections_(origin, waypoint, destination) {
    var directionFinder = Maps.newDirectionFinder();
    directionFinder.setOrigin(origin);
    directionFinder.addWaypoint(waypoint);
    directionFinder.setDestination(destination);

    var directions = directionFinder.getDirections();
    if (directions.routes.length == 0) {
    throw 'Unable to calculate directions between these addresses.';
    }
    return directions;
}

This script originally checked column A for origin and then checked column B for destination. I was trying to use the addWaypoint function to add a 3rd point, but have been unsuccessful. I read through the API and tried a few different variations, but am afraid that I was again unsuccessful. How do I add multiple destinations or waypoints

解决方案

When there are multiple waypoints, a route will consist of multiple legs, each of which has its own distance. So once you have the route calculated, you still need to go through each of the legs and sum up the individual distances to arrive at a final value.

Once we have this figured out for one mid-point, surely we'll want it for two. And once we have it for two, why not three? Finally, why not an arbitrary number of waypoints?

See Accept Arbitrary Number of Arguments in Google Scripts Custom Function? for an example of how a custom function with arbitrary parameters will behave in a Spreadsheet.

This modified script handles a route with any number of points. It's fully supported by Auto-completion within Sheets. And it can handle a list of waypoints, or a range of cells. It's also copiously commented to explain each step.

Basic use:

=drivingDistance(A2:D2)

More advanced use, with a more understandable output:

=TEXT(drivingDistance(A2:D2)/1000,"#,### k\m")

Code

Enough jabbering, just give me teh codez! (Updated code is available on gist.github.com.)

/**
 * 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 = route.join(',').split(',');
  }
  else {
    // No array? Grab the arbitrary arguments passed to the function.
    args = arguments;
  }

  // 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) {
  // 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;
}

这篇关于寻找googlesheets脚本中多个点之间的距离的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！