VBA Excel webservice

Directions API Navigation SDK
1

Hello,
For first, I know that my question could be rediculous, but I didn’t find what I search even if I’m sure that is really easy…

I’m looking for the way to get distance between two adresses with the formula excel =webservice()
I know that I have to put an url and an api key somewhere, but where ? I found this but it doesn’t work :frowning:

https://graphhopper.com/api/1/vrp?key=YOUR_CREATED_API_KEY/maps/point=ADRESS_1&point=ADRESS_2locale=fr-Be&elevation=true&profile=car&use_miles=false&selected_detail=Elevation&layer=Omniscale"

I’m searching this for a project that get the total of kilometers that a cyclist mades in a day

Thanks for helping

2

To calculate the distance between two adresses you should use the /route endpoint, not the /vrp endpoint. The /route endpoint is documented here

You need something like:

curl "https://graphhopper.com/api/1/route?key=ENTER_YOUR_API_KEY_HERE&point=52.5170365,13.388599&point=53.550341,10.000654&profile=bike&calc_points=false"

You can use calc_points=false (like I did in this example) if you only need the distance and not the exact route, instructions etc.

The response will be in JSON format and look something like this:

{
"hints":{"visited_nodes.sum":700,"visited_nodes.average":700.0},
"info":{"copyrights":["GraphHopper","OpenStreetMap contributors"],"took":19},
"paths":[{"distance":301417.309,"weight":36454.624123,"time":64229686,"transfers":0,"snapped_waypoints":"mfp_Iu}upAcxhEfttS"}]
}

You will have to extract the distance from this in your excel sheet.

Note that the point parameters have to be given as GPS coordinates (not just plain text addresses). If you need to translate addresses to coordinates first you can use the /geocode endpoint which is documented here.

3

Thank you so much for this ! I’ll try it immediatly and I’ll tell you if it works !

4

That is prefect ! I found that the distance is in meters (then /1000 to get kilometers) and time in milliseconds (then /1000/60 to get seconds).
You made my day ! I save so much time and I understand better the way how API works… Now I can finish my projet.
Have a nice day

1 Like
5

Glad to hear you made it work :+1: