Google Spreadsheets to Google Maps: soup to nuts

Last week, I described how I’d used a Google API to allow a Google Spreadsheet to drive items appearing on a Google Map. I was proud of the work. Rob was less impressed, instead focusing on the fact that the latitude and longitude had to be looked up and input manually based on the postcode or town being mapped. He’s never impressed, that boy.

In a comment on the original post, Mercedes Car Finder (a person, it seems) suggested a Google formula, as follows:


where A1 contains the item to be looked up—the postcode or placename. Let’s assume the formula is entered into B1.

The CSV file resulting from the URL contains four fields. For "York", the first two numbers are 200 and 4. (I have no idea what these are. Anyone?) The last two (53.957702 and -1.082286) are the latitude and longitude respectively.

So the ImportData function brings back all four values, but only stores the first of these (200) in the cell containing the formula, in this case B1. The subsequent three values are accessed through the formulae

Given that the CSV file returned has a single row of data, these formulae bring back the mysterious 4, 53.957702 and -1.082286 respectively.

Unfortunately, Google doesn’t allow you to create latitude in one step through the following syntax:

But nonetheless, if I have a column of postcodes or placenames, all I need is three columns of formulae (the ImportData formula containing the 200, the CONTINUE (3) formula containing the latitude and the CONTINUE (4) formula containing the longitude) to feed the map.

Fabulous. Thanks, Mercedes-selling-person.


3 Responses to “Google Spreadsheets to Google Maps: soup to nuts”

  1. Mercedes Car Finder on September 27th, 2008 02:19

    Firstly, you’re welcome. You link to my site, I provide ammunition to impress “Rob”. Fair’s fare. Although…

    Secondly, while checking your link to my site, (making sure it didn’t have a rel=”nofollow”, which would halt google’s yummy link juice), I noticed that there is an overzealous close curly bracket in your site html. Please explain this.

    Thricely, quote: “So you get 200,5,57.098381,-2.172400 which is the http status code, the accuracy level, latitude and longitude.” And you say “I have no idea what these are. Anyone?”. Luckily I went back in time to write the answer three days ago. and #GGeoAddressAccuracy

    And quadratically, it is in fact possible to create a formula that gets coordinates in one step.

    If you want height in your coords (although it seems to be forever 0 even for Mount Everest, Nepal):

    =ImportXML(“”, “//coordinates”)

    If you want the values in separate cells:

    =SPLIT(ImportXML(“”, “//coordinates”), “,”)

    If you want separate cells, but without the always zero height. All in one formula:

    =SPLIT(SUBSTITUTE(ImportXML(“”&A9, “//coordinates”)&”end”, “,0end”, “”), “,”)

    In case you’re wondering “//xml_tag_name” is a descriptor in a language called XPath. Had to look it up:


  2. Dan on October 4th, 2008 17:43

    Think I’ve sorted the closing curly bracket.
    Good advice on the other stuff too.


  3. jur9en on February 6th, 2009 10:55

    ‘200’ (http status), ‘4’ (Google accuracy parameter)


Leave a Reply