Is there a (easy) way for Appsheet to calculate the "driving" distance between two addresses?

AppSheet AppSheet Q&A
1

Is there an easy way to calculate the distance between two points (addresses)?

We use a google script to calculate the distance between addresses using google sheets.
(See Script Below)

Is there a way to have AppSheet call the script and get the distance in a cell?

  function jobdistance(start_address, end_address){
  
    /* 
    * Get Distance between 2 different address.
    * @param start_address = Address as string. Ex: "1350 SW 57th ave, miami, Florida";
    * @param end_address = Address as string. Ex:  end_address = "5880 SW 27th Street, Miami, Floirida";
    * @ CUSTOMFUCNTION
    */
    var mapObj = Maps.newDirectionFinder();
    mapObj.setOrigin(start_address);
    mapObj.setDestination(end_address);
    var directions = mapObj.getDirections();
    var meters = directions["routes"][0]["legs"][0]["distance"]["value"];
    var miles = meters * 0.00062137;
    var time = directions["routes"][0]["legs"]["value"];
    return miles
  
  } //end function jobdistance  ***
1 Like
2

Thanks for your prompt reply.

Please note that our google script uses google maps to get a “driving” distance between two addresses.

It appears the Appsheet DISTANCE() function is a little different as it seems to use the lat and long to calculate the straight line distance between two points.

Additional Questions:
1- Is there a function to get lat and long from an address in the US?
2-Is there a way in Appsheet to calculate the “Driving” distance between two addresses?
2-Is there a way to call a google sheet script from within Appsheet?

3

Felix.P_Tong:

It appears the Appsheet DISTANCE() function is a little different as it seems to use the lat and long to calculate the straight line distance between two points.

This is correct.

1- Is there a function to get lat and long from an address in the US? ：No
2-Is there a way in Appsheet to calculate the “Driving” distance between two addresses? ：No
2-Is there a way to call a google sheet script from within Appsheet? : No , only solution is you push your custom function (expression) into google spreadsheet cell to run your script to get distance you need.

3 Likes
4

T_K thank you for your prompt response and clarifications.

Will look at how to add google sheet script into the workflow.

Thanks again.

5

Just add your function as expression formula for the spreadsheet.

1 Like
6

T_K

I am new at Appsheet (1 week in… so please excuse if this is a silly question, But)

Do you mean using the “Spreadsheet formula option” under Data Validity section of the column properties? (Hope I got this description right - if not - I will try to send a pic).

Based on Appsheet documentation this is used to pass a formula like “C2 = C3 + C4”

Questions:

1-this seems intended to calculate the value of another cell based on the value entered for the cell selected (is this correct?).
2-can we assign a “spreadsheet” formula like “=C3 + C4” to calculate the value of the cell itself?
3-how can we pass along a function that looks like “=DISTANCE(A,B)” and applied to the cell selected.

thanks in advance for your help.

7

Felix.P_Tong:

jobdistance(start_address, end_address)

My understanding is you have your cunstom function made by GAS like ↑

Then for instance, you have start address in A2 cell and end address B2 cell, then you push your expression into C3 cell with

=jobdistance(A3, B2)

This is basically nothing to do with Appsheet, but how to deal with Google Spreadsheet.

1 Like
8

T_K.

Thank you for your response.

That clarify things. Will try and report back if find issues using this.

9

Hi, can we use this function in google doc and use it as a template to get report?

10

Hi, @FTong , @Koichi_Tsuji .

This seems to not be working by me, how do I set up the inside of this script?

Any specifics on how to set it up in GSheets?