Plot Google Spreadsheet geographic data on a map – Google GeoChart JavaScript API

I’m Carlo from TU Delft, and in this video I will show you how to plot your Google Spreadsheet
data on a geographic map for your website, using the Google Charts APIs. Let’s see how! This is the spreadsheet we want to visualise.
Let’s assume we are a group of explorers, travelling throughout the Europe looking for…
plants! Yes, we want to classify different plant species, and for every trip we make
we want to keep track of how many new species we find. As you can see, in this spreadsheet
every trip is logged: dates of arrival and departure are reported, as well as the distance
we travelled on foot and the number of new species we found. Remember to keep this spreadsheet
public and allow at least its reading. Now, it would be cool to have a nice map presenting
an overview of our progresses, where for instance for each country the sum of species found
is showed. For this purpose a JavaScript API is provided by Google, namely the GeoChart
from Google Charts. Let’s move to our HTML document. First off,
we instantiate a “div” element inside the body of the document, which we can call “regions_div”.
We will put all the JavaScript code inside the head tag. And then the first mandatory thing we
need to do is import the script which loads the Google loader (sorry for the word pun),
then, inside a new script tag, we call the load function from the google.charts package
in order to load the packages we need. I suggest you to have a look at the links provided in
this video’s description for a complete reference. In this example we assume to have a function
named “drawRegionsMap” which we have to pass to the “setOnLoadCallback” method. Moreover,
we need to specify and send the query to our Google Spreadsheet, whose data will be stored
in a variable we called “data”. We define another function, let’s call it “drawChart”, which
we will call any time we want to draw or re-draw our plot. We need the URL of the spreadsheet
and the query string. We are interested in the COUNTRY of each trip, logged in the column
‘C’ of our spreadsheet, and in the number of NEW SPECIES FOUND, from column ‘E’. So
we select ‘C’ as first column, the sum of elements in column ‘E’ as second column, and
we group the data by country names, in order to have one single row per country. Then we
set the query and we send it! We also need to specify what to do with the
response of this query, and we do that inside a function we name “handleQueryResponse”, yeah, it’s a
very creative name. This is where we assign a DataTable to the previously defined “data”
variable (again, look at the description for more information if you feel you need more
details). Then we give a label to the second column of our table, let’s call it “New species
found”. Finally we need to create a GeoChart object, passing our “div” element to the constructor,
and call the “draw” method. Well this method takes two arguments, the data object and an
“option” object, which we define and leave empty for now. Now we need to do one last thing, that is define
the “drawRegionsMap” function I mentioned before, in which we simply call the “drawChart” function.
Now let’s give it a look… Oohh nice yes, but maybe we want to centre our map on the Europe,
because we only explore European countries. This is where the “options” object comes in
handy. These options are specified as name-value pairs. The code corresponding to Europe is
‘150’. There are several options that can be used, for example we can change the background
colour, let’s use a light blue, and we can set the legend starting and ending colour,
let’s use two shades of red. Now let’s have a look at our new map… Wow, yeah! Here it is! A simple
but super-cool way to visualise our favourite geographic data. That’s all folks, bye bye!

Tags: , , , , , , , , , ,

One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *