Getting real distance and arrival time between two points with Maps class in Apps Script.

[HBT]

I’ve been using Appsheet since late 2019. If you ask what is the biggest feature that has been developed since then, I can say Call Script. I love this feature. This feature liberates us completely.
For me, the gateway to the world from Appsheet is Call Script.
I was using Script execution feature with Triggers before.
However, as the work to be done increased, it turned into a code mess.
Now, I have turned off the triggers and run the working codes separately with call scripts. It’s an incredibly beautiful thing.

I would like to express my deep gratitude to all the decision makers and coders who have brought the Call Script feature to the Appsheet.

As far as I know, there is no expression in the appsheet expressions that shows the actual distance between two locations and the time of arrival between two locations.

For this, we can obtain the actual distance and estimated time of arrival between two locations by using the AppScritpte Maps class.

Here are the codes:

function getdistanceAndDuration(xDestination) { 
 try{
var ss=SpreadsheetApp
           .openById('Type your Google SSheet Id')
           .getSheetByName('SheetNameWithStartingOrigin');
var xGetWarehouseLocation = ss.getRange(2, 4).getValue().toString().replace(' ', '');

    var directions = Maps.newDirectionFinder()
      .setOrigin(xGetWarehouseLocation)
      .setDestination(xDestination)
      .setMode(Maps.DirectionFinder.Mode.DRIVING)
      .getDirections();

    var route = directions.routes[0];
    var distanceKM = Number(route.legs[0].distance.text.toString().replace('km', ''));
    var distanceMetre = Number(route.legs[0].distance.value);
    var distanceTime = route.legs[0].duration.text.toString().replace('hours', 'Saat').replace('mins', 'Dəqiqə');

    return {
      xdistanceKM: distanceKM,
      xdistanceMetre: distanceMetre,
      xdistanceTime: distanceTime
    }
  } catch (err) {
    /* If an error occurs in the code, 
     * you can see it in your application. 
     * So, did the script work correctly or 
     * did it return an error?
     */
    console.log(err.stack)
    var xError = err.stack
    return {
      xdistanceKM: "0",
      xdistanceMetre: "0",
      distanceTime: xError
    }
  };
}

For those who don’t know how to use call script

1. Create New Boot

2. Create a custom event

   • Select event type : Data change
   • Add and Updates
   • Select table
   • Specifying the condition. For example [Distance]=0

3. Create a custom step

   • Step name : getDestAndDur

   • Run a task

   • Call a script

   • Select appscript project file

   • select function name for example : getdistanceAndDuration(xDestination)

   • Select function Parameters [location] for example xDestination

   • Return value : Object

   • specific type

     Name****Type	Type
     xdistanceKM	Decimal
     xdistanceMeter	Number
     xdistanceTime	Text

4. Add step

   • Create Costum Step
   • Run a Data
   • Settings : Set Row values
     Set these column(s)
   • [DistanceKM] =[getDestAndDur].[xdistanceKM]
   • [DistanceMeter] =[getDestAndDur].[xdistanceMeter ]
   • [DistanceTime] =[getDestAndDur].[xdistanceTime ]

[Joseph_Seddik]

@HBT Thank you!

[Joseph_Seddik]

@nico This is for you

[nico]

Very cool! This is a great example of some of the powerful things that possible, getting the driving time estimate between two points on a map is not a trivial thing!

Thanks for creating this tip, I think people will find it helpful.

[Suvrutt_Gurjar]

Thank you @HBT for a neat tip . There are occasional questions in the community about actual distance between two points. Your tip will help those requirements.

[Ryan_Wagner]

I will definitely be trying this. Thanks!

[Fabian_Weller]

Thank you very much @HBT for sharing this.
What is you use case for that?
Do you need a Google Maps API Key for that?
Can you include parameters like departure_time and traffic_model?
This could be useful in areas with much traffic. You could choose a departure_time that has usually a lot of traffic, and the traffic_model “pessimistic” to get the longest time needed.

[Yoelyk]

Hi @HBT , thanks for this script! However, when trying to implement it, I’m getting this error, see attached.

Any help and/or a step by step guit on how to set everything within the script, would be really helpful!

Thanks in advance!

[Yoelyk]

This is where I’m taking the SSheet ID