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.
![[Pro] Populate map with data from Google Spreadsheet](https://interactive-geo-maps.intercom-attachments-1.com/i/o/962941480/e24f660d10c8c0103bcb7828/sheets_api_key.png?expires=1722102300&signature=e10c1cc52f4eaadbb6ccdce862184c6471adeddf8fcdffaadf908514282750de)
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](https://interactive-geo-maps.intercom-attachments-1.com/i/o/962941486/a58742242f91151c77505b9f/spreadsheet_id.png?expires=1722102300&signature=860b997cffe166fb30daf58e462da70b322fb65e0be0b1e837fdc136d9a3eb84)
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](https://interactive-geo-maps.intercom-attachments-1.com/i/o/962941496/3829506d602b513d5c0abf4a/sheet_range.png?expires=1722102300&signature=a473410e914344a6a6fb199146cfe28e8d069fb6bc181db4b0e54dbd41e2d004)
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](https://interactive-geo-maps.intercom-attachments-1.com/i/o/962941502/3aa9c2f0e88756ccdc577798/headers.png?expires=1722102300&signature=7ed128068934d59be65c9f894ecfeb2bb6efe341ba6885ce0a42c14be9cc77bd)
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.
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](https://interactive-geo-maps.intercom-attachments-1.com/i/o/962941507/95b5f23425eec10ad8d4aa42/heatmap_google_spreadsheet.png?expires=1722102300&signature=8b6b832c20f23ced83e72fa3609abc25d07f14ffa79ddaf38a36ceae4d8dd157)
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}