Calculate Distancing Apps Script "google sheet" (Start point, Stop point, End Point)

[Mhd]

Hello all
I want this function to give me the path distance from the starting point to the ending point.
I also want to add points within the path as rest points and force the path to pass through them to calculate the actual distance.
Of course, these points in the function are preferred to be optional for the user.
ِAny help to do that, please

CODE "Apps Script":

function DrivingMeters(Start, Stoppoint1,Stoppoint2,End) {
var directions = Maps.newDirectionFinder()
.setOrigin(Start)
.addWaypoint(Stoppoint1)
.addWaypoint(Stoppoint2)
.setDestination(End)
.getDirections();
return directions.routes[0].legs[0].distance.value;
}

[Martin Hawksey]

There is a nice tutorial here with some custom apps script functions that would be easy to build upon https://www.labnol.org/google-maps-sheets-200817

This is a slightly modified version of one of them that can take option waypoint addresses

 

/**

 * Calculate the distance between two

 * locations on Google Maps.

 * @link https://www.labnol.org/google-maps-sheets-200817

 *

 * =GOOGLEMAPS_DISTANCE("NY 10005", "Hoboken NJ", "walking", value_or_array)

 *

 * @param {String} origin The address of starting point

 * @param {String} destination The address of destination

 * @param {String} mode The mode of travel (driving, walking, bicycling or transit)

 * @param {String|Array} waypoints To include as part of route

 * @return {String} The distance in KM

 * @customFunction

 */

const GOOGLEMAPS_DISTANCE = (origin, destination, mode, waypoints) => {

  const route = Maps.newDirectionFinder()

    .setOrigin(origin)

    .setDestination(destination)

    .setMode(mode);

  if(waypoints){

    Array.isArray(waypoints) ?

      waypoints.map(row => row.map(cell => route.addWaypoint(cell))) :

      route.addWaypoint(waypoints);

  }

  const { routes: [data] = [] } = route.getDirections();

  if (!data) {

    throw new Error('No route found!');

  }

  const { legs: [{ distance: { text: distance } } = {}] = [] } = data;

  return distance;

};

[Mhd]

I appreciate this
Thank you so much

I added this function to my script
But it does not calculate the total distance from the starting point to the arrival point through the waypoints (it calc from start point to the first waypoint only "inaccurately")

As shown in the links below:
https://docs.google.com/spreadsheets/d/1LKbZN5HvDxvLFjJky4-uo8aND1iqaYZYZeLc66cAd3w/edit#gid=0

[Faustino Rodriguez]

In that case you would need to use distance.value instead of distance.text and also sum up each legs distance(s)

Try replacing the "const { legs: ..." line with the following line

let distance = data.legs.reduce((p,v) => (p += v.distance.value, p), 0);

Does that work for you?

[Mhd]

Yes, it works very accurately!
Thank you so much

I still have some problems that need to be solved
If you work with big data, It would be difficult to write a custom function for each line

In the data I'm working on, there are four different cases (a path without waypoints - a path with one waypoint - a path with two waypoints - a path with three waypoints).

Unfortunately, with each of these cases, I need to modify the function argument in order to work correctly.

I used  "IF" conditional functions to solve this problem as found in the attached link https://docs.google.com/spreadsheets/d/1LKbZN5HvDxvLFjJky4-uo8aND1iqaYZYZeLc66cAd3w/edit?userstoinvite=m.ha...@gmail.com&actionButton=1#gid=0

But I would be grateful if we could solve this problem without "IF",

Can we make the 4th argument in the custom function in AppScript, optional, and at the same time it can override the lack of data in the array?

[Martin Hawksey]

Just to manage your expectations here I don't see Google Sheets as a 'big data' solution:

    Each time a custom function is used in a spreadsheet, Google Sheets makes a separate call to the Apps Script server. If your spreadsheet contains dozens (or hundreds, or thousands!) of custom function calls, this process can be quite slow.

With the Google Map Direction query a @gmail.com account is limited to 1,000 queries a day or 10,000 if using a Google Workspace account.

If the amount of data you are looking at is 50 rows a custom function will be ok, more than this and with the quotas in mind my recommendation would be switching to a custom script which can process multiple rows in batches (similar to the example here https://stackoverflow.com/a/64949690/1027723)

[Mhd]

Yes this Error: "Exception: Service invoked too many times for one day: route" makes this custom function not useful.

However; with my very simple knowledge of App script and java, I will be very thankful if you can help to switch this custom function to a custom script!