a Flo + a Blog = a Flog my ブログ

Use google maps API from google docs

I am looking for a dojo where I could go practice to. I needed to find out which one would be closest to my work or home place...

I had that list of address representing some place of interests - all of the aikido dojos. And course I knew my home address. While google doc offered me an easy way to map all of those addresses on a map, I wanted to get some extra information such as the duration or distance for directions between my place and the listed address.

With the following formula one can easily find plenty of information:

For a duration

=INDEX(importXML("http://maps.googleapis.com/maps/api/directions/xml?origin="&SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE( D2 ;CHAR(13);" ");CHAR(10);" ")); " "; "+")&"&destination="&SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE( $D$9 ;CHAR(13);" ");CHAR(10);" ")); " "; "+")&"&sensor=false" ; "//leg/duration/text[last()]" ) ; 1)

For a distance

=INDEX(importXML("http://maps.googleapis.com/maps/api/directions/xml?origin="&SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE( D2 ;CHAR(13);" ");CHAR(10);" ")); " "; "+")&"&destination="&SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE( $D$9 ;CHAR(13);" ");CHAR(10);" ")); " "; "+")&"&sensor=false" ; "//leg/distance/text[last()]" ) ; 1)

You will understand that my home address would be in the cell D9 and that each address are in a D# cell.

Of course using the XPath Syntax, you can refer to any of the xml tag and get the value you need in a cell as you notice that the only difference between those are the parsing I am doing with //leg/distance.

importXML is pretty neat for parsing into xml data but use it with moderation as you can only use it 50 times in a spreadsheet.