Custom Sheets function to be used with ARRAYFORMULA

Question

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)))}

Answer 1

I believe your goal as follows.

• You want to use DrivingKms with ={"DrivingKms";ARRAYFORMULA(IF(ISBLANK(A2:A),"",drivingkms(F2:F, M2:M)))}.

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

Modification points:

• In your script, I think that DrivingMeters is correct. But when DrivingKms is used with ={"DrivingKms";ARRAYFORMULA(IF(ISBLANK(A2:A),"",drivingkms(F2:F, M2:M)))}, only the 1st element is used. I think that this is the reason of your issue.
• When drivingkms(F2:F, M2:M) is used, F2:F and M2:M are the 2 dimensional array, respectively. In your case, it is required to considere this.

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:

• Please use above script with V8.

References:

• Custom Functions in Google Sheets
• map()