Calculate distance in Google spreadsheet
It has evolved into a distance calculator template with custom functions, with the ability to add results to a trip log.
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. Google account required.
This post describes two ways to perform distance calculations:
- custom functions: in Google spreadsheets you can create custom functions.
It appears to be more reliable and more flexible than the second method.
- importXML: Google Maps has a nice API and Google spreadsheets has a function (importXML) that we can use to parse the results.
- custom functions: 2500 requests per day per system (2500x directions and 2500x address lookup).
- importXML: 50 importXML instances per spreadsheet.
There is a daily limit of 2500 direction requests and 2500 location requests.
The following functions are available in the template. They can be used as custom functions for a variety of distance calculation functions. For example: to calculate the distance, duration (driving/walking/bicycling), lookup an address (or only the zip code).
See the functions tab in the template for details.
|routeDistance||from, to, [miles], [mode]||distance between locations in miles or km|
|routeDuration||from, to, [mode]||duration between locations in minutes|
|routeSummary||from, to, [alternatives], [miles], [mode]||summary of route(s): which route, duration, distance|
|routeLeaveWhen||from, to, arrivalTime, [miles], [mode]||departure time in order to arrive by a certain time|
|routeMapUrl||from, [to], [width], [height]||URL of image showing the departure and arrival locations|
|locationMapUrl||location, [width], [height]||URL of image showing a single location|
|routeDirections||from, to, [miles], [mode], [lang], [region]||table with direction steps|
|locationAddress||location, [lang], [region], [onlyZipCode]||full address (or only zip code) for searched location|
|zipAddress||location, [lang], [region]||zip code for location|
|locationLatLon||location||string with latitude + longitude combination of searched location|
|asTheCrowFlies||from lat, from long, to lat, to long, [miles]||as the crow flies (straight line) distance between coordinates|
|=locationAddress(“white house, washington”)||The White House, 1600 Pennsylvania Avenue Northwest, Washington, DC 20500, USA|
|=locationAddress(“white house, washington”,”fr”)||Maison Blanche, 1600 Pennsylvania Avenue Northwest, Washington, District de Columbia 20500, États-Unis|
|=zipAddress(“white house, washington”)||20500|
|=locationAddress(“38.89768309999999 -77.0364972″)||The White House, President’s Park, Washington, DC 20502, USA|
|=locationLatLon(“white house, washington”)||38.89768309999999 -77.0364972|
It does not always work reliably, you then get #N/A.
How to use importXML to get distance data from Google Maps in your spreadsheet?
- 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
Nice: no API key is needed. See results (unformatted XML output).
- 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):
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”)
The XPath predicate  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:
Show the label of the preferred route (e.g. “A28 and A73″):
Calculate the distance of the preferred route:
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):
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:
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().
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/start_location/lat") &" "& importxml(B20,"//leg/step/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")