[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.

There are 2 ways to have the spreadsheet populate the map. The first one would be using a specific URL that retrieves your spreadsheet data as a JSON file, which the map can read. This approach however might not work in the future. The second approach would require a Google API Key, but it’s safer to use in the future and the one we recommend.

Creating 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;
  • If you’re building a markers map, the spreadsheet should contain a column for latitudes and another for longitudes.
  • Avoid cells without values, specially in the last column.

Google spreadsheet with information to populate map.

Make the Spreadsheet Public

You will need to select the option to ‘Publish to the web’ and make the spreadsheet public, by going to ‘Share’ and select the option that ‘anyone with the link can “view”‘.

Method 1: Using the Google Spreadsheets API

This approach is available since version 1.5.0 and it will be the preferred method in the future, since the JSON method might stop working, if Google deprecates the older API.

This feature is available for regions and round markers.

You can go to Regions/Markers > Other Data Sources > Enable the option and select ‘Google Spreadsheet’.

Google spreadsheet options to populate map

Google API Key

  • Go to Google Cloud Console
  • Create a new project;
  • Click Create credentials > API key (you should also explore the ‘Restrict Key’ options) – Copy the new API Key;
  • Go to API Library and search for Google Sheets API and enable it.

Use the API Key in the field above.

[Pro] Populate map with data from Google Spreadsheet

Google Sheet ID

You can find the spreadsheet ID in the edit screen of your Google Spreadsheet:

https://docs.google.com/spreadsheets/d/SPREADSHEET-ID/edit#gid=0

That part of the URL is the Spreadsheet ID which you should use in the options above.

[Pro] Populate map with data from Google Spreadsheet

Sheet Range

Usually here you would write the name of your Sheet, which by default is Sheet1.

You can also use A1 or R1C1 notation for more complex queries of specific rows.

[Pro] Populate map with data from Google Spreadsheet

Property Fields

These will be the titles of your column headers in the spreadsheet. Avoid using special characters and even spaces in the spreadsheet, to avoid errors with these options.

The ID property field is mandatory when using regions and markers. When using markers, the latitude and longitude property fields are also mandatory. The Action Content property is optional.

[Pro] Populate map with data from Google Spreadsheet

Pro Tip

If you use an advanced range selector or your spreadsheet doesn’t have column headers, in the property fields, tooltip template and heatmap rules, you can identify the column by using numbers, instead of the column names. The first column will be 0 and so on. For example, in the example above, City=0, Lat=1, Long=2 and so on.

Filling up the information above should read the data from your spreadsheet into the map.

Method 2: JSON Approach

This method will stop working in the future, as google will deprecate the API that allowed us to use this method. Use the method described above instead.

You can check the video below to understand how you can populate your map using JSON provided by the google spreadsheets.


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.


Creating a 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 if you’re using the JSON approach, or simply count if you’re using the Spreadsheet option, to use the values on that column.


Populating the 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 the JSON approach, the format for the placeholder will all follow the format {gsx$columnName.$t}

Using the other approach you can simply use the column names {columnName}

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}

Posted by Carlos Moreira

Lead developer of Interactive Geo Maps plugin.