Calculate distance in Google spreadsheet
The challenge: calculate the distance between two locations (zip codes, addresses, etc.) using a spreadsheet. The distance should be based on driving directions.
The solution: Google Maps has a nice API and Google spreadsheets has a function (importxml) that we can use for this. Quite useful for quickly finding distances to use in your mileage reports.
Not interested in all of the nitty gritty details? Simply download the spreadsheet template.
Update (July 2011): now with button that adds the results to a separate log sheet.
Update (February 2012): now with map images of From and To locations, version information, latitude/longitude information.
Spreadsheet template
A ready-to-go spreadsheet with all of the examples is available as a template. Google account required.
Fill out From and To locations and the spreadsheet will calculate the distance and driving time based on the recommended route. You can copy the result to a trip log.
The rest of this page: only relevant if you are interested in the technical details.
How to get distance data from Google Maps in your spreadsheet?
In short:
- create a URL that uses the Google Maps Directions API which returns XML output;
- use the Google spreadsheets function importxml() to parse the XML file, and use an XPath command to extract the info you need.
Google Maps API for directions
Example URL:
http://maps.googleapis.com/maps/api/directions/xml?origin=miami&destination=atlanta&sensor=false
Nice: no API key is needed. See results (unformatted XML output).
Explanation
- the Google Maps API for directions is well documented
- in your spreadsheet point the origin=… and destination=… to spreadsheet cells that contain the origin and destination, the importxml() commands will be run every time you change the URL. Nice.
- sensor=false: don’t use browser location sensor (required).
Some of the optional parameters:
- units=metric: show km (alternative: imperial)
- region=nl: country that should be used as default for origin or destination
- language=nl: language to use for the results
- alternatives=false: do not show alternative routes, only show the recommended one
The resulting XML file contains 1 route (or if you ask for alternatives: 1 or more), and for each route 1 leg (I am not using via waypoints), and each leg consists of steps (turn left at …, etc.).
Example parse commands
Assumed: the URL is created in cell B20. Please note: you can have a maximum of 50 importxml commands in a spreadsheet.
The base command to use to get e.g. the distance (depending on your units used: in feet or meters):
=importxml($B$20,"//leg/distance/value")
The first argument is the URL, the second argument is the XPath command.
Show the start and finish (e.g. when using “brentwood” as origin, it will show: “Brentwood, TN, USA”)
=importxml(B20,"//leg[1]/start_address") =importxml(B20,"//leg[last()]/end_address")
The XPath predicate [1] and [last()] are used in case the route has multiple legs (in our examples they don’t)
Alternatively you could use the spreadsheet index() function:
=index(importxml(B20,"//leg/start_address"),1) =index(importxml(B20,"//leg/end_address"),1)
Show the label of the preferred route (e.g. “A28 and A73″):
=importxml(B20,"//route/summary")
Calculate the distance of the preferred route:
=roundup(importxml(B20,"//leg/distance/value")/1000,0)
The result of this field is always in meters, so we divide the result by 1000 to get kilometers, and we round it up.
And calculate the duration (the result is in seconds, so divide the result by 60 to get the minutes):
=roundup(importxml(B20,"//leg/duration/value")/60,0)
You can also ask for alternatives, assuming &alternatives=true is added to the URL (stored in B21), the same function that showed 1 route summary, now shows a list.
The spreadsheet automatically sees it is an array of results that need to be displayed.
Let’s calculate the average distance of the routes:
=roundup(average(importxml(B21,"//leg/distance/value"))/1000,0)
Average works fine with an array.
The spreadsheet will get confused if you try to perform a calculation on an array, but that is easy to fix using arrayformula().
=arrayformula(roundup(importxml(B21,"//leg/distance/value")/1000,0))
The XML contains latitude + longitude information, each step of the route has a lat+long pair.
The lat/long of the start location can be found in the the first step of the route list.
=importxml(B20,"//leg/step[1]/start_location/lat") &" "& importxml(B20,"//leg/step[1]/start_location/lng")
The lat/long of the destination can be found in the last step of the route list, there’s an XPath predicate for that: [last()].
=importxml(B20,"//leg/step[last()]/end_location/lat") &" "& importxml(B20,"//leg/step[last()]/end_location/lng")
65 Responses to “Calculate distance in Google spreadsheet”
Leave a Reply
« iTunes Match: remove low bitrate files after upgrade | Home | 2e Kamerverkiezingen: woorden in beeld »
Hello,
Has anyone got a version that can be run without using Google Docs. I would like to add this to an already existing workbook that I have.
There must be some way of using VBA scripts instead of the importxml function.
Thanks for the help everyone.
Phil.
Winfred hi,
Conrats from me for this application.
I have the same question as Philip Young. I am trying to save this Google Docs spreadsheet as a Microsoft Excel 2003 file and run it from there. Unluckily nothing seems to happen. Is there a way to use Microsoft Excel 2003 or later to run this amazing application?
Thanks again
KP
Hi KP (and Philip), sorry… the spreadsheet relies on functions only available in Google spreadsheet.
Great resource – thanks –
Has anyone figured out how to calculate flight distances using google spreadsheets – I’ve converted my origin and destination to Lat / Lng – but don’t know how to call google.maps.geometry.spherical.computeDistanceBetween in the spreadsheet.
I don’t see that function listed in the Google Maps services for Google Apps, but it is pretty straightforward -be it cryptic- to calculate it yourself using a custom function.
E.g.
var R = 6371; // radius earth in km
var metersToMiles = 1609.3440006;
var convertToRad = Math.PI / 180;
var dLat = (toLat-fromLat) * convertToRad;
var dLon = (toLong-fromLong) * convertToRad;
var lat1 = fromLat * convertToRad;
var lat2 = toLat * convertToRad;
var a = Math.sin(dLat/2) * Math.sin(dLat/2) +
Math.sin(dLon/2) * Math.sin(dLon/2) * Math.cos(lat1) * Math.cos(lat2);
var c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a));
var d = R * c;
return (d / metersToMiles) *1000; // in miles
//return (d) *1000; // in km
}
Thanks heaps – this has been a big time saver
Nice work. Is there a way to increase the accuracy of the result? My application involves distances under 3 miles and it isn’t very useful when rounded to the nearest whole number. Google Maps when used by itself spits out tenths of a mile. That would be accurate enough.
Thanks!
Sure, just change/remove the roundup command in e.g. cell F4. The number returned by Google is in meters, I convert it to miles or kilometers and round it up.
Winfred..
Thank you so much for this application. It is exactly what I have been searching for..
Jessica
Thanks a lot! Do you know if there is a way to also get a suggested route and time for public transit?
Thanks!
Justin
Hi Justin,
This can be done by adding “&mode=transit&departure_time=…” The departure time is the number of seconds since 1/1/1970 (e.g. http://www.epochconverter.com). Because you asked, I added this functionality to v1.5 of the spreadsheet. Select the transport mode in the lower right. Still in Beta, because for public transport time I still need to find a reliable way to determine the earliest departure time.
Thanks so much for saving me HOURS! I never thought I would love a spreadsheet!
The multi addresses tab is exactly what I have been looking for. I did a few spot checks and it seems like the distances it returns are different from what google maps (or your main distance calc) return. The multi addresses tab is overestimating distances by a lot.
Hi Nathan, glad you like it. The multi-address was a proof of concept, so it showed the results in the default format: kilometers. I updated the sheet so it now uses the settings from the main sheet.
Winfred, thank you so much! This worked perfectly for what I needed!
Where is the tab for multi address?
Want to produce spread sheet for several routes each route with more than one stop.
Thanks
See the bottom of the screen for the tabs.
I was looking more for multi address as the distance calculator so it would show the route on the map. Thanks.
Hi Winfred,
Tools works perfect, thank you. Can I also call it from a larger xlsx sheet where, amongst others, I want to calculate distances. I see several routes:
- download your doc as an excel sheet ==> loss of functionality
- upload my excel sheet as a google doc ==> it is 30MB+ and heavy on calcs, not sure if this works
- use the Get data from Web function in excel.
What is your view?
[...] quick google search and i stumbled upon this page which has a very nifty google docs spreadsheet with the functions baked in. the author has done a [...]
If anybody wants to use this inside of javascript, do this:
getDistance = function(start, end) {
var start = start,
end = end,
xhReq = new XMLHttpRequest(),
geoUrl = ‘http://maps.googleapis.com/maps/api/directions/json?origin=’+start+’&destination=’+end+’&sensor=false’;
xhReq.open(“GET”, geoUrl, false);
xhReq.send(null);
var jsonObject = JSON.parse(xhReq.responseText);
return = parseFloat(jsonObject.routes[0].legs[0].distance.text);
}
getDistance(‘enschede’, ‘arnhem’);
Nice!
Hi, I’m interested in this spreadsheet to calculate multiple distances, but the sheet isn’t available anymore. (Error 404 file not found)
Can anybody provide a working link?
Thanks in advance
What link did you try? I checked the link (e.g. Download button) just now and I did not get a 404 message.
https://drive.google.com/previewtemplate?id=0AjmrJQ99vtxhdHBTZEg3SmRicS00MVNESXZJYVhvY0E#
It’s working now,
Thanks
But I keep getting following error: “error: The xPath query did not return any data.”
(Using Firefox on Ubuntu Linux)
It helps if you can be more specific. Under what conditions do you get this message? Feel free to contact me by mail.
I’ve got the same problem as Jurgen, all the calculated fields report that error, e.g. field C2 & C3 with the default values for start & end address
Ok, thanks. I can now reproduce it with the published template. I will look into it. Thanks for reporting it. It may be a temporary Google issue, as the XML still seems to be responsive and valid.
I checked again a few hours later and the same spreadsheet was working fine again. Looks like it was indeed a temporary Google glitch. Do you still see the issue?
seems Ok now