Aug
10
2015
by eidam

Synchronization of leaflet.js and google spreadsheet

In this short tutorial, I would like to show how to use google spreadsheet as a database for web GIS map.

Using google spreadsheet as a database

Using spreadsheet as a database could have some nice advantages. First one and maybe the most important one is the possibility of easy collaboration. Your sheet could (and also has to) be shared and edited by anyone with the link anytime. So if you have a mapping idea and the collector or owner of the dataset does not have a technical background in databases or you do not want to waste your time on creating one, you could give it a shot. The bad thing is the security and maybe the slower process of getting your data.

 

Creating and sharing your spreadsheet

If you have an google account, you have access to https://docs.google.com where it is able to create a new spreadsheet. Then you fill some data and the next step is to share this sheet to the world. Click on the upper-right button “share” and click “advanced”. Here you should choose “Public on the web – Anyone on the Internet can find and view” and accept with “done”. You also have to navigate to file panel -> publish to the web … In this window set your option and click “publish”. At this point, your sheet is available to queries and your data could be obtained. You also should copy the “id” of your sheet -that is the middle part of url. In my case :

https://docs.google.com/spreadsheets/d/1YIj-UIK6aWD8gf4MN9JpQwmfxmjlfjAZStBrq_obZcM/pubhtml ->

my id is “1YIj-UIK6aWD8gf4MN9JpQwmfxmjlfjAZStBrq_obZcM

 

Setting leaflet.js

Leaflet map will be set as usual:

 

Getting data with Tabletop.js

For obtaining google spreadsheet we use tabletop library that returns a json object with data. The implementation is really easy and it is done just with few lines of code:

In this code, we iterated over all lines in spreadsheet and set new L.marker with geographic position based on values in columns ‘lat’ and ‘lon’.

 

Fun with data

I think, this method has a very good potential. It could replace a small-scale database with very easy maintenance. It also offers the opportunity for collaboration even when your coworkers are not technical experts or you don’t have a lot of free time for creating a good environment for handling data in database.

 

Demonstration

See the Pen leaflet spreadsheet synchronisation by Eidam (@eidam61) on CodePen.