Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this community
I'm working with Google Docs and want to create a spreadsheet of the following form:
Point A Point B Mileage Los Angeles Miami 292100 Palo Alto San Francisco 90
In the spreadsheet, Mileage is automatically calculated. I am looking for "distance driven", not "as the crow flies".
Is there a function in Google Spreadsheets that can do this?
With the following little piece of code, you can retrieve the distance between two points.
function getDirection(city1, city2, unit) {
var directions = Maps.newDirectionFinder()
.setOrigin(city1).setDestination(city2)
.setMode(Maps.DirectionFinder.Mode.DRIVING)
.getDirections();
var d = directions.routes[0].legs[0].distance.text;
var distance, value = d.split(" ")[0].replace(",", ""), text = d.split(" ")[1];
if(text == unit) {
distance = value;
} else if(text == "km" && unit == "mi") {
distance = value / 1.6;
} else {
distance = value * 1.6;
}
return Math.round(distance);
}
The MAPs API, build on top of the Google Apps Script, will get the first direction (routes[0]) between the origin and destination. The returned value is stripped from comma's and parsed as an integer.
The distance returned can be either in kilometer or miles. I've added the calculation for "as the crow flies" as well (in miles).
Add the script under Tools>Script editor in the spreadsheet. Press the bug button in the script editor and you're on the go !!
I've created an example file for you: get driving distance
In Google Spreadsheets:
=importXML("http://mapquestapi.com/directions/v1/route?
key=YOUR_KEY_HERE&outFormat=xml&from=" & A2 & "&to="
& B2,"//response/route/distance")
where A2 is the origin and B2 is the destination.
You have to get an API key from mapquest and replace the value YOUR_KEY_HERE with it.
This is what works for me:
=importXML("http://maps.googleapis.com/maps/api/distancematrix/xml?&origins="& <point.A> & "&destinations=" & <point.B>; "//distance/value")
This will yield the driven distance between <point.A> and <point.B> in meters (according to Google Maps, of course).
I do not think there is built in Excel function, or extension, to do this.
You might want to check out this Source Forge page: http://zips.sourceforge.net/
The Excel function is
=IF(SIN(Lat1) * SIN(Lat2) + COS(Lat1) * COS(Lat2) * COS(Long1 - Long2) > 1,
RadiusofEarth * ACOS(1), RadiusofEarth *
ACOS(SIN(Lat1) * SIN(Lat2) + COS(Lat1) * COS(Lat2) * COS(Long1-Long2)))
The page notes that the angles have to be in Radians and that Excel has a degrees to Radians conversion function:
=RADIAN(degrees)
Unless someone else has a slicker answer, this should get you what you want without too much work.
For a complete Google spreadsheet example, in the form of a spreadsheet template, plus information about the Google Maps API: http://winfred.vankuijk.net/2010/12/calculate-distance-in-google-spreadsheet/
Google Developers offers a very robust script for map related functions in Google Sheets that I used and have really enjoyed. Full instructions are on the webpage as well.
I am not familiar enough with the APIs to tell you exactly how to do this, but I would think it would be possible using the Google Maps API.
Google Maps, of course, can provide directions from one point to another through their API. It would be complicated, but possible to route the locations from the spreadsheet through the API and get driving directions, which would give you a driving distance between cities.
You can write the functions on your own which is similar like macro's
http://www.google.com/google-d-s/scripts/yourfirstscript.html
you can find the full document here
PS: Currently It doesn't open for all Google Apps Script
