If you maintain a lot of data in Google Spreadsheet, you’ve probably at least once thought, “Hey, I should be able to get all those locations and quickly plot them in a Google Map”. Well, you can of course. But doing this is not quite as obvious as you might expect.
With Google writing both the spreadsheet and the map-making software, there should be several easy ways to do this. You’d expect to see a File > Export as Map option or a File > Download as KML. But no, there’s nothing like that. What you soon realise is there are a number of ways to do this, but none of them are quite what you’d expect.
I’m sure you’ve done this before. What starts out as a simple decision to wrangle a bit of data turns into a couple of days of mashing the data through the wrong shaped sieve, then you suddenly come across several better options you wish you’d known about to begin with. Today you get to skip this headache, as I’ll give you the details of each method right here. Read all the way to the end before making your choice. You’ve been warned!
Google Spreadsheet Mapper
For many years now, Google has been revising its Spreadsheet Mapper tool in order to give you an easy way to create KML files for Google Earth maps and matching URLs for Google Maps. It comes with extremely thorough instructions and it isn’t too unwieldy to use. But somehow, it still seems just a little too clunky. When all you want to do is get two columns of data and make it show up on a map, the Google Spreadsheet mapper route is an overkill.
What is great about this method is that you can change the styling easily and produce map location data that looks fabulous. It also lets you export a KML file for importing into Google Earth, which few of the other methods do. But it really is more than most people need, and too much fuss for users with simple needs in mind.
Also, don’t try anything smart with the Spreadsheet Mapper, like importing data from another spreadsheet automatically. If you do, it will run the script each time it checks the other spreadsheet for new data. This will result in the following error:
error: Script invoked too many times per second for this Google user account
Google Fusion Tables
Google Fusion Tables is magic, really. You need to publish the spreadsheet with the data in it in order to make it work, but then Google Fusion Tables quickly creates a map table with all of the locations on it using Google Map tools to do so. The only problem is that it is a Google Map visualization, so it can’t be used as a stand alone Google Map — you cannot share it with friends as a Google Map, though you can embed the visualization.
Map A List
Map A List is a website designed to take the headache out of this process entirely. You will need to register in order to use it, but that only takes a minute and it is well worth it. Your maps will stay synced with your Google Spreadsheet; you can embed the map; and you can save the map as KML. Again, it uses Google Map tools (the Google Maps API). You are offered some customisation, but things are kept generally simple. I would say Map A List is a good method for getting a KML file for Google Earth and the best way to get a map that stays synced.
Google Spreadsheets Map Wizard Tool
If you’re a web developer, you may like the Google Spreadsheets Map Wizard Tool. It does its best to make the process easy, but leaves you with a need to geocode your locations yourself, which none of the other options do. It might be useful to certain people, but certainly not all will appreciate the extra hassle.
Use Google Maps To Import Data From CSV Or Google Spreadsheet
Yup, you can search all day before you realise that this whole process can be done FROM Google Maps. Since Google revised its map software, you can actually create a new map, create a new layer and click “Import” to get your data from your Google Spreadsheet.
Read this guide on how to import data from Google Spreadsheets into Google Maps for full instructions. In brief, you can import data from a simple spreadsheet very easily, this spreadsheet can reference other spreadsheets, it can be customised easily, embedded, and exported as KML. However, it won’t stay synced with your spreadsheet. You need to re-import the spreadsheet each time you change it.
Chart Within Your Spreadsheet
For some purposes, it’s enough to be able to create a map chart within your spreadsheet. This works excellently when you want to see the amount of people living in any given location easily. To do it, simply create a chart and choose the map option.
A Non-Google Option
If you’re not keen to use Google Spreadsheets or Google Maps online, there is a service called EditGrid you can try. EditGrid will let you export KML directly from the spreadsheet in order to import into Google Earth.
Obviously, importing into a Google Map is the best option for most people’s needs, however these other methods could be very useful if you have a specific need in mind. Which tool do you like best and why? What information do you want to display on a Google Map with the help of a Google Spreadsheet?