Google Docs: getting round the IMPORTDATA limitation

There is such power to be harnessed in the link between Google Spreadsheets and Google Maps.  But to me, the power is limited by a fundamental constraint.

For Google Maps to map a location from a spreadsheet, it needs two values: a latitude and a longitude.  It can’t map places directly from the more human address and postcode data that you and I use on a daily basis.

Historically, I’ve used the following formula to get coordinate data into a Google Spreadsheet where all I have is a postcode.

=IMPORTDATA(“http://maps.google.co.uk/maps/geo?output=csv&q=”&A1)

With the postcode in A1, a mini-CSV file is created containing four fields that can each be accessed via the CONTINUE function that I’ve recently lamented: HTTP status code, accuracy level, latitude and longitude.

The trouble is, you’re limited to using a maximum of 50 IMPORTDATA functions in any one spreadsheet.  So if I wanted to create a spreadsheet containing the postcodes of the 500+ Starbucks in the UK to drive a map, I’d have to find the latitudes and longitudes in batches of 50, pasting their values over their formulae to create static data.  Hardly ideal.

Does anyone know of a way of surmounting this problem?  If so, I’d love to know about it.  I’d love to be able to generate maps showing things in common, but having a limit of 50 or having to use the ridiculous manual workaround described makes it not worth the bother.

Comments

5 Responses to “Google Docs: getting round the IMPORTDATA limitation”

  1. Floss Agency on May 5th, 2009 14:21

    The only way I can think of actually getting round the IMPORTDATA restriction, would be to create a service that accepted multiple addresses and returned the list of lat/lngs. It wouldn’t be that hard to write a Google App Engine service to do that. You might get problems of running out of url/querystring length, but geocoding just the postcode would solve this. And the other problem would be that you only get 30 seconds to do all the geocoding as this is the time limit GAE sets.

    Or try http://www.batchgeocode.com/

    Btw, your “Stuff I like” links are broken:

    <a href=’http://rulesofthumb.org/perma.php?ruleid=1255http://rulesofthumb.org’

  2. Tom on June 3rd, 2009 21:32

    Another choice:

    “Use the Map Gadget to add interactive maps to your spreadsheets.”

    http://docs.google.com/support/bin/answer.py?answer=91601

  3. Dan on June 3rd, 2009 22:00

    Hm. I get an error. “Your page is missing the Google Maps API.” No info on Google about it…

    Looks good though. If it worked.

  4. Mapping on a grand scale: poetry : Tangential Ramblings on June 30th, 2010 22:55

    […] postcodes to latitude and longitude coordinates (using the advice I’d previously documented here), used this to generate a KML file (through some fancy Excel formulae) which I could then import […]

  5. EP on May 17th, 2011 21:18

    Hope you solved the problem, but you could make several spreadsheets (~10) and on each one have 50 import functions. Then in another spreadsheet, use =ImportRange to get the lat/long from the other spreadsheets; you’d only need one per spreadsheet you’re pulling data from.

Leave a Reply