Mapping data from a Google Sheet

Data in a table is at the heart of much GIS. In the demonstration below, we walk through using a Google Sheet as our table and plug it in to an ArcGIS Online Map - so that it displays the new or edited data in the table in near real time.

tables are made of rows and columns. Rows are vertical strips or fields of similar data. Usually, each row has a data type. A row may contain text, integers, real numbers, or many other types of infromation. Columns or records are horizontal and represent a single sample, occurrence, or thing. A table can hold a very large number of records.

Create a few new data records, similar to what you see in line 2. The data in the spreadsheet will reset every 30 minutes but is publicly visible until that time.

Watch the data above appear on the map below. Click the markers to see details about the point. If your tabular data are correctly entered above, it may take up to two minutes to display on the map below. Remember this table and map are publicly available, anyone, anywhere can enter data and it becomes visible to everyone - until the table resets.

Click one of the map markers below. Can you see how the data from the table flows into the marker pop-up? Do you see how the column label also displays in the pop-up? It helps describe the record. The latitude and longitude columns are displayed but they are also used by ArcGIS to place the map marker.

1. Create the Google Sheet
2. At https://drive.google.com, add/create a new sheet
3. Add a latitude, longitude, and name column. Add others if you like. Remember a column that uses integer data, can be useful for mapping purposes.
4. Name your sheet and be sure it is saved.
5. Press the Share button in the upper right. A window will pop-up with the second half titled, "Get Link". Click the hyperlinked "Change". Set it to anyone with the link can edit.
6. Press "Copy Link" and press "Done".
7. In your storymap, add an "Embed" widget and drop your new link into the widget. This should produce an embedded Google Sheet, similar to what is above.
8. Link your Google Sheet to the map
9. In your Google Sheet, go to the File menu
10. Select "Share" and "Publish to Web".
11. Set the pull downs to: "Sheet1" and type: "CSV".
12. Copy the link and close the window.
13. Login to ArcGIS Online and create a new classic map viewer map.
14. Select "Add". Select "Add layer from Web".
15. Set the pull-down to CSV and paste your URL.
16. When you see you Google Sheet layer in your map, click the three dots to the right of the layer name. Select "Rename" and pick an appropriate title.
17. Click the three dots again and select "Refresh Interval". I recommend setting it to 1 minute, however if you have many users or expect to have a large dataset, a slower refresh rate will improve performance of the map.
18. Save the map. Press the "Share" button and select "Everyone". Copy the URL presented.
19. In your storymap, add a second "Embed" widget and paste your shared map URL. It should appare similar to the map above.
20. Save your new storymap and set its sharing to "Everyone".
21. Test your storymap in a private or Incognito browser to make sure sharing is correct.
22. IMPORTANT: Be sure you understand the above process will let anyone in the world write anything in your Google Sheet. Silliness and naughtiness may ensue. This method is best for small groups or limited time use with a class. After you have collected the data, you can change the Google Sheet sharing to "view only".

Some time ago, Charlie Fitzpatrick created the Table Time activity to help teach the importance of thinking about, organizing, and analyzing data in a table.  Take a look here!