Driving distances

[Srikanth_S]

Hi, 

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Is-there-a-easy-way-for-Appsheet-to-calculate-t...

I have seen the above driving distances function which uses script - to be used in google sheets. 

Can we use this function in a report (google doc) using a BOT etc., 

I have the following tables: 

1) Staff table with columns: email id, Name, Home Location (LatLong)

2) Log  table with columns: Date, Customer, Check-In Location(LatLong), CheckOut Location (LatLong). 

I am looking to generate a daily report of the Staff where the table looks somewhat like this

[Starting Location] [Customer Name] [Reached Location] [Distance Covered]

I am looking at achieving the following:

At the start of the day (first record for the day by the Staff), the BOT has to consider [Starting Location] as [Home Location] from Staff. Similarly Ending location shall be [Home Location]. [Distance Covered] shall be driving distances between [Starting Location] and [Reached Location]. 

Will it be possible to achieve without copying the data into another table - directly in the report (google doc)?

Regards, 

Srikanth. 

[1minManager · Accepted solution]

Yes you can do that.  You need to create a column in the log table which the script would update.  You'd setup the script to run on data change.  The only issue you'd need to be aware of is that I wouldn't suggest you trigger the bot from the creation of the Log Table record.  Since you might create a 'race condition' whereby Appsheet saves the record, the Google Script runs, but appsheet creates the report BEFORE the Google Script returns the distance data.

Simon@1minManager.com