[Pro] Populate map with data from Google Spreadsheet

You are here:

Learn how to create a map that reads data from a Google Spreadsheet and automatically updates when the data in the spreadsheet also updates.

Although the plugin is not able to read directly from a Google Spreadsheet, there are workarounds that allow us to fetch the data in JSON, which is a format compatible with the plugin.

Checkout the video below:


Create a Google Spreadsheet

There are a few thins to consider when creating your spreadsheet:

  • It should have the first row frozen, to serve as the header of the entries
  • Each row should contain information for a specific region (not the columns)
  • There should be a column with the ID of the regions for the map you want to use

Google spreadsheet with information to populate map.

Publish it and get the JSON URL

This might be the most tricky part. I will refer to other tutorials, since this technique might change.

You will need to select the option to ‘Publish to the web’ and make the spreadsheet public.

More information:

For reference, this is the URL format that I used:

https://spreadsheets.google.com/feeds/list/{SPREADSHEET_ID_HERE}/od6/public/values?alt=json

Replacing {SPREADSHEET_ID_HERE} with the id of your spreadsheet, which is in the URL while you edit it. The above works if the spreadsheet contains only one sheet.


Use JSON URL as your data source

In the regions tab of the map edit screen, go to the ‘Other Data Sources’ option and select the JSON data option. Add the URL to the spreadsheet JSON in the Raw JSON Data field.

[Pro] Populate map with data from Google Spreadsheet

The options should be the following ones:

  • JSON ID property: gsx$id.$t
  • JSON data source property: feed.entry

If everything was done correctly you should now be able to see the map being populated with your data.


Choropleth Map (heat map)

If you want to use the choropleth feature, you can enable it and set the source field ID to match the column where you’re storing the numeric values you want the plugin to use to calculate the regions colours.

[Pro] Populate map with data from Google Spreadsheet

In the above example we have the columns named count so we use as the source field ID the value gsx$count.$t to use the values on that column.


Tooltip

You can add the content of any column of your spreadsheet to the tooltip. To do that use the ‘Tooltip Template‘ option and use the placeholder with reference to your columns.

Using Google Spreadsheets, the format for the placeholder will all follow the format {gsx$columnName.$t}

The template accepts HTML code, so for example, you could have the following as the tooltip template:

<strong>{gsx$name.$t}</strong><br>Count: {gsx$count.$t}

blank

Posted by Carlos Moreira

Lead developer of Interactive Geo Maps plugin.