0

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)))}
Rubén
  • 34,714
  • 9
  • 70
  • 166
Roggie
  • 1,157
  • 3
  • 16
  • 40

1 Answers1

2

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:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Yes, you have understood the issue correctly, although when I make both of your suggested changes I'm getting this result error `TypeError: Cannot read property 'legs' of undefined` – Roggie Jul 21 '20 at 01:56
  • sorry, I was referencing the incorrect columns in my formula. Using both of you suggestions works! :) - a side question: do you know the daily quota limit set by Google to calculate distance? – Roggie Jul 21 '20 at 02:09
  • 2
    @Roggie Thank you for replying. I'm glad your issue was resolved. About your new question, this official document might be useful. [Ref](https://developers.google.com/apps-script/reference/maps/maps#setAuthentication(String,String)) And, when you want to obtain the detail limitation, how about testing it in your environment? Because I have no information about the detail of current limitation. This is due to my poor experience. I deeply apologize for this. – Tanaike Jul 21 '20 at 02:15
  • @Roggie Also, these threads might be useful. https://stackoverflow.com/q/21418298 https://webapps.stackexchange.com/q/78222 – Tanaike Jul 21 '20 at 02:29