Mapping Google Sheets


If you follow me on twitter you may have seen a few tweets encouraging you to donate to Linkey, a charity that provides support to rough sleepers by donors buying sleeping bags, cold weather gear and toiletry kits in their web shop. It’s an inspiring charity that only started a few months ago. I reached out to a couple of the founders to see if there was any way I could help them and in the midst of our discussions they asked whether I could improve the current map of the shelters that they support, they also wanted to be able to update the map easily as the list of shelters they were working with grew. “No problem, send me the list” I said, so last week I sat down with a small spreadsheet to see how I could knock up a slightly more elegant web map for them.

This is a screen shot of the map that they had on their site (I grabbed a copy so that it would be there as a reference point if Linkey subsequently uses one of the options that I offered).

This is an ok but rather basic map via a service from BatchGeo, which offers a neat copy and paste interface to drop some spreadsheet data and build a simple map. The letters on the pins correlate with a list of shelters and addresses that appears when you scroll the map down in the frame but it isn’t obvious that there is a scrolling option. One downside is the quite large advert that shows at the bottom of the map, you can get rid of ads if you upgrade to the $99/month “pro” option which offers a lot more features but is probably overkill for a charity that only wants to map 20 or 30 points at the moment.

First up I thought I would give QGIS and QGIS2Web a try (sort of reusing what I learnt on geocoding etc in making the US steel plants map a couple of weeks ago) which worked quite easily and produced

The downsides that I discovered with this map were that QGIS2Web support for SVG symbols (I had a nice “bed” symbol that I wanted to use for shelters) was a bit flaky, colours got lost, the OpenLayers version seemed to flicker or not appear (I couldn’t get to the bottom of this. Maybe I could have found a solution and spent some time tweaking to make the map more elegant but I also realised that the only way that the Linkey team would be able to update the map was by asking me to recreate it and re-upload to my server which was not an ideal solution.

I thought there should be a easy way to map a few points and produce some decent looking out put without going through the rigmarole that I had to geocode the point data, map it in QGIS, turn into a web map and then upload to my server. I did a bit of searching and stumbled on an add-on for Google Sheets called Mapping Sheets by The XS. This is a neat add-on which lets you select the address column for geocoding, choose a field for a filter to switch record groups on and off and then publish some GeoJSON to your Google Drive and create a map that calls this data.

Mapping Sheets is very easy to use (just make sure that GeoJSON is shared for public access within your Google Drive) and it’s also very simple to update, after you have edited the Google Sheet or added some rows you just open the add-on, rebuild your map and you are done. Downsides are that there isn’t a way to control the colours of the pins, change symbols or base maps and there is a limit of 50 rows in the free version of Mapping Sheets (the Premium version is only $25/year and gives more control over icons etc and unlimited rows). This is a pretty neat solution.

A bit more googling suggested that there was a direct integration between Google Maps and Google Sheets (as opposed to the map type charts that you can embed in a Google Sheet). Open Google Maps, log into your account, go to My places and select My Maps, create a new map (give it a title and description), add a layer by choosing the Import option and selecting your Google Sheet, select the address column and you are there, you have a map which you can now tweak by varying icons, colours and base maps.

Updating this map is a little more work than MappIng Sheets, once you have edited the Google Sheet you have to go back to My Maps and remove the old layer and add the layer again and restyle it. Plus side is there are no charges and there doesn’t seem to be a limit to the number of rows that you can map. I guess you pay your money (or not) and you can make your choice.

Both of these approaches work pretty well and don’t require any coding, web hosting or other tech competence. I’m sure ESRI have a similar solution using ArcGIS OnLine and MapBox offer a highly customisable solution but I think you would need to be better at JavaScript than me to get it set up. I guess you pay your money (or not) and you can make your choice.

BTW if this was useful, or even if it wasn’t, hop over to Linkey and buy something for some homeless people