How to make a code triggers distance in gscript

I have a script code to determine the distance between the origin and destination addresses, how do I add a trigger code to automatically fill in the distance column, can anyone help me? here is the code

function GOOGLEMAPS(start_address,end_address,return_type) {

var mapObj = Maps.newDirectionFinder();
mapObj.setOrigin(start_address);
mapObj.setDestination(end_address);
var directions = mapObj.getDirections();

var getTheLeg = directions[“routes”][0][“legs”][0];

var meters = getTheLeg[“distance”][“value”];

switch(return_type){
case “miles”:
return meters * 0.000621371;
break;
case “minutes”:
// get duration in seconds
var duration = getTheLeg[“duration”][“value”];
//convert to minutes and return
return duration / 60;
break;
case “hours”:
// get duration in seconds
var duration = getTheLeg[“duration”][“value”];
//convert to hours and return
return duration / 60 / 60;
break;
case “kilometers”:
return meters / 1000;
break;
default:
return “Error: Wrong Unit Type”;
}

}

You can have a time base trigger on the gscript.
It may check all the rows where origin and dest exists but distance does not exist.
And then you can calculate the distance and write the data onto the sheet.
A few things to keep in mind:
The gscript triggers are time based and not based on edit. So those can take time to run and then update your sheet which may take a little bit of more time to sync with glide.
I can help you with an easier method.
Use an experimental column to calculate distance or use inbuilt distance column

what code should I add here so that I can create a trigger on change, and so that it automatically goes directly to the distance column in the spreadsheet

I would have to look at your sheet for that!
its not plug and play as its dependent on column positioning in your sheet!

I would suggest you share this personally rather than open to the community!

do you already know the answer

I will post the script you need to add in the gscript shortly,

1 Like

Have you considered the Glide Distance column?

If that’s not suitable for your use case, and you really need to use a script, then my advice would be to turn it into an Experimental Code Column. This would have the following benefits:

  • You would get instant results in your app, without having to wait for data to sync to/from your Google Sheet
  • You wouldn’t need to mess around with triggers
  • You could re-use it across multiple apps
  • You wouldn’t be subject to the limits that come with the Distance Column
4 Likes