[Pro] Populate map with data from Google Spreadsheet
Learn how to create a map that reads data from a Google Spreadsheet and automatically updates when the data in the spreadsheet also updates.
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.
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”‘.
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 API Key
- Go to Google Cloud Console
- Create a new project;
- Go to API Library and search for Google Sheets API and enable it.
- Go Credentials > Click Create credentials > API key (you should also explore the ‘Restrict Key’ options) – Copy the new API Key;
- You might also need to Create credentials > Service Account;
Use the API Key created.
Google Sheet ID
You can find the spreadsheet ID in the edit screen of your Google Spreadsheet:
That part of the URL is the Spreadsheet ID which you should use in the options above.
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.
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.
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.
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.
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
Using the other approach you can simply use the column names
The template accepts HTML code, so for example, you could have the following as the tooltip template: