Sample App for route optimisation

Using Google Map API to call the optimized route for A to B point, I generated a quick sample and placed on my portfolio.

User enters the address of origin and destination. Default is set as “driving” but can select the mean to walking, train or bicycle.

Virtual column generate Google Map URL and showing the best fitting route dynamically.

https://www.appsheet.com/samples/Sample-App?appGuidString=237580df-e851-4b3c-ae23-404436737444

26 63 9,292
63 REPLIES 63

Nice man!

Instead of address type field , latlon type data field should work as well.

Ami
Participant V

Google drive has a limit of 1000 queries per day, is there a way to add the google drive api to get more?

We are just opening URL I don’t think this trick will be restricted by max daily usage etc.

We have created our own Google Maps Direction API. If you sign up for a ‘proper’ account and give them credit card details your free queries actually increases. I’ve just checked last month and we did 24263 API calls which should have been $121.32. But it came under the limit set by the “Maps Free Tier”. The key change you have to make it to use the API key they give you in the script.

I wrote a script in google sheets but the API didnt work. Google told me that google sheets dont support their own API

Google Map API unfortunately there is no capability to calculate the opt route based on the multiple user selected waypoints.
This was useful airticle.

Hope as a matter of time, route optimization become natively possible on Google Map + Appsheet combination.

I have a need for this after I finish everything else. Is there any updates on the route optimization with aposheet yet?

Ami
Participant V

I have tried it with excel some time ago, when i entered a few locations and tried to get the best route.
Lets say i have an origin lication and then 5 addresses. What would be the best route to drive between those places. Would it be possible with this app as well?

As far as I read google api docs, there is a way to add another parameter of waypoints in addition to origin and destination. However the sequence of waypoint which come first , second , third is just folllowing the order of list you place to parameters. URL won’t change the sequence at all. If we have sequence of point to go, a followed by b , c , d and finish at e, then yes we are able to calculate the same.

I will look at your app closely later on. Meantime, you are truely a master in my eyes

Enjoy my tips, let me know if you need a help.

Will do, thanks.
I was trying for a while now to incoprate google drive with an app. My client has to plan routes to pick up employees and get them to their working sites. You can do it with google maps, insert the addresses and get a route. However, you need to insert the data manualy, every address. With excel, i built a vba matrix to calculate best route by distance. If i can load my addresses via appsheet to google maps and get the route back, that would be great

I updated my sample app a bit.
Can add waypoints as via points in addition to origin / destination.
Then URL will dynamically generate the route on google map. But after that, user can play around with google map to change the order/sequence of point they visit first, second etc. URL wont calculate the best opt route, but users have flexibility and free hands to change the sequences easily.

not testing how many waypoints we are able to add, but should have bar, depending on user device according to google documents.\

waypoints : Specifies one or more intermediary places to route directions through between the origin and destination . Multiple waypoints can be specified by using the pipe character ( | ) to separate places (for example, Berlin,Germany|Paris,France ). The number of waypoints allowed varies by the platform where the link opens, with up to three waypoints supported on mobile browsers, and a maximum of nine waypoints supported otherwise. Waypoints are displayed on the map in the same order they are listed in the URL. Each waypoint can be either a place name, address, or comma-separated latitude/longitude coordinates. Strings should be URL-escaped, so waypoints such as “Berlin,Germany|Paris,France” should be converted to Berlin%2CGermany%7CParis%2CFrance .

Add waypoints.
Select option, either to type the address manually, or pick up the pin from map, then place latlon (googlespredsheet convert latlong to Address using Script).

Then generate the route on google map.

After that, juggle the sequence on google map to play around.

MultiTech
Participant V

This… is… brilliant!

2X_c_c3afa31adee75c4a3e5e19b18334bd9a0d8a0981.gif


When used on a mobile device with Google Maps installed, the links will open in Gmaps with waypoints and everything.

Just epic sir!

Wow! I off course did not expected that ! Thank you I will try with my phone as well.

Yes, Matt!

On my Iphone, link to google map activated the google map (app) with plots of selected address. And then I could play around, change the order of visiting points and google map looked after that, to re-calc the opt route. Thanks for heading me up.

Thank YOU for finding this capability and making this happen.
2X_3_31e8f2194566ef4a39c084448ee5eadf003ebc2c.gif

I really enjoyed your explanation of the subject. I was very interested in the subject. Could you please send me the link of your test app.

Thank you

Here you can add up to 20 addresses and it will calculate the best route.


Maybe the have an API?

I’m afraid we don’t have that functionality yet.

is this app not available for copy / download…i dont see any such option upon clicking the link u shared

Alejandro
Participant II

Hello:
We want to do something similar but inside a Warehouse, so the person can find the optimal picking list route for a list of items located in the warehouse (XY) type column.
Do you have an idea of how to work this thing out?
Thanks very much in advance

The functionality described in this thread is accomplished via Google magic, it won’t be possible with an XY column.


The best I think you could do would be to create a way for people to indicate where they are, then use that to build some sort of system that sorts items based on the XY values - putting them in a closest to farthest order.

To get your started, I have a sample app that contains most of the pieces you would need to start this:

Click the link below to see it in action (you'll have to create a copy of the app for it to work)

https://www.appsheet.com/samples/Lets-figure-out-smart-boundaries?appGuidString=a7c2835d-2705-4d60-8...


Indeed so!
The app is designed around a central record created by the user; as such, a sample app won’t allow people to see this functionality - that’s why I tried using the “Copy” button from the sample app page.

Oh well, here’s a link to the regular sample app page so you can see it in action and make your own copies.

https://www.appsheet.com/samples/Lets-figure-out-smart-boundaries?appGuidString=a7c2835d-2705-4d60-8...

Thanks very much for your fast response, I had trouble opening the link, do you mine checking it?
Best regards

@Alejandro, the first one was broken yes - the second works (at least for me and for an incogneito window; here is again.
https://www.appsheet.com/samples/Lets-figure-out-smart-boundaries?appGuidString=a7c2835d-2705-4d60-8...


Here’s a quick video showing how to make it all work:


Inside that sample app, there’s a set of slices that aren’t used for anything - but they show all the locations above/below/to the left/to the right of the current location. You might be able to use those to at least find the closest to you.

Jonathan_S
Participant V

Any Updates on Route Optimization?

Strictly via this original google maps URL magic integration - as far as I know.

Hi, so i follow Randy Austin from excel for freelancers, he ceates on excel with vba, here is a link to something he did with excel and integromat https://youtu.be/yJ6qzSdjDWs
You can find around 50:00 how he explains the output from integromat goole map. He has a table of work orders and a default order for each WO starting at 0. The WO are the waypoints in google map.
Wish i could use the same idea with appsheet

So what exactly do you mean by this?

I assume you’re asking about…

That would be this:


All you have to do is literally concatenate() a string for a URL - then simply launch that URL. If G-maps is installed, it launches otherwise it’s through the web interface.

Shared with CloudApp

But this doesn’t do routing; it merely creates waypoints in the same order you give them.

interesting. Good to know. Never knew about this

Hello @Jonathan_S. I have a an contact/leads app that integrates with 3rd party route optimization API.

Really? you have a sample of this?

Yep. Im just trying to make it a sample app right now, but there are restrictions with the route optimization api. Limit of maximum 200 GPS addresses. Above this you may have to start paying, but you would need to check the service. Ive written Google AppScripts code to integrate and run the optimization once a day only if there are updates/additions to a gps address.

Check it out. Addy Demo

If you want I can talk through more details, contact me in private.

EDIT: I havent documented the app very well, but it uses OpenRouteService to do the optimisation.

Top Labels in this Space