Plotting Points on a Map Using a Spreadsheet

By mattcen

A friend asked me recently about how to create a map with points on it using little more than the data in a spreadsheet.

This wasn’t the first time I’d considered this problem, but I’ve never found a tool to make it simple to have a map that auto-updates when its source spreadsheet changes. Until now. After asking some friends on the internet, I was informed that National Map, a website from Australia which is built using Terria, should do the job nicely.

I’ve just determined that similar can be done on the Terria website, without the NationalMap branding, but with slightly reduced features.

Here’s how.

  1. Create a Google Sheet that includes columns called ‘lon’ and ‘lat’, and store the longitude and latitude of each row’s data point in these columns. The expected datum is WGS84
  2. In Google Sheets, go to File -> Share -> Publish to web
  3. In the resulting dialogue box, on the Link tab, select the Sheet you’d like to visualise, and set the format to CSV
  4. Click Publish
  5. Copy the resulting Share URL
  6. Head over to either NationalMap or Terria
  7. At the top of the left-hand panel, click the Upload or “Load local/web data” button
  8. In the dialogue box, select “Add Web Data”
  9. Specify the file format as CSV, and paste in the Google Sheets URL you copied earlier, before clicking Add
  10. Click Done in the corner of the dialogue box
  11. Click the Share/Print button on the top-right of the page, and copy the URL there
  12. Share that URL with anyone would like to show your map

In NationalMap, you can also click the kebab menu button beside the data on the left pane, and select Edit Style, to change how your data looks on the map, but I’ve not played with this feature much, so using it is left as an exercise to the reader.

If you’re more interested in hosting your own map (e.g. on GitHub Pages) using the contents of a Google Sheet, check out Steve Bennett’s Community map template. At time of writing, it hasn’t been updated since mid-2021 but it’s probably simple enough (especially if you know Vue.js) to update/adapt.