javascriptgoogle-apps-scriptgoogle-sheetsgoogle-directions-apigoogle-sheets-custom-function

Custom Sheets function to be used with ARRAYFORMULA

I am trying to use the following custom function DrivingKms with ARRAYFORMULA so that it recursively calculates the distance down the specified columns.

How can I update it so that it works with ARRAYFORMULA?

function DrivingKms(origin, destination) {
  
  return DrivingMeters(origin, destination)/1000;
}

function DrivingMeters(origin, destination) {
  var directions = Maps.newDirectionFinder()
  .setOrigin(origin)
  .setDestination(destination)
  .getDirections();
  return directions.routes[0].legs[0].distance.value;
}

When I call the function in an ARRAYFORMULA, from all the way down, it only converts the first two location points (F2 and M2).

={"DrivingKms";ARRAYFORMULA(IF(ISBLANK(A2:A),"",drivingkms(F2:F, M2:M)))}
Solution

  • I believe your goal as follows.

    For this, I would like to propose the following modified scripts.

    Modification points:

    Pattern 1:

    In this pattern, the result values are retrieved in the loop. Please modify DrivingKms as follows.

    Modified script:

    function DrivingKms(origin, destination) {
  return origin.map(([v], i) => [v && destination[i][0] ? DrivingMeters(v, destination[i][0]) / 1000 : ""]);
}

    Pattern 2:

    In this pattern, from recursively calculates the distance down the specified columns., the result values are retrieved in the recursive loop. For this, at first, values for using at calc is created. Please modify DrivingKms as follows.

    Modified script:

    function DrivingKms(origin, destination) {
  const calc = (values) => values.map ? values.map(calc) : DrivingMeters(...values.split(",")) / 1000;
  return calc(origin.map(([v], i) => [v, destination[i][0]].join(",")).filter(([a, b]) => a && b));
}

    Note:

    References: