There’s been a proliferation of open data made available by all levels government, but much of it is still in a pretty raw form and can be hard to manipulate and visualize.
Google Sheets provides some powerful data import functions that can automatically populate a spreadsheet from a data feed, and Geosheets adds mapping and geospatial functions to Google Sheets. So the two combined can allow you to make a map from open data very quickly and without downloading anything.
Note: Geosheets is no longer active and the tutorial below is deprecated.
As an example, we’re going to map all of the polling places in New York City, as made available on NYC’s Open Data portal.
1. Create a new spreadsheet with Geosheets enabled.
Either install the Geosheets add-on or make a copy of the Geosheets demo spreadsheet which has Geosheets pre-installed for use in a single spreadsheet.
Geosheets adds custom functions starting with the GEO_ prefix to Google Sheets, so you can write formulas like =GEO_MAP(…) and =GEO_GEOCODE(…).
2. Import the open data file using the IMPORTDATA function.
In cell E3 so that you have a little room to work, write this formula:
=IMPORTDATA(“https://data.cityofnewyork.us/api/views/utqd-4534/rows.csv?accessType=DOWNLOAD”)
IMPORTDATA is a built-in Google Sheets function that takes the url of a CSV or TSV file and imports that data into your sheet. We got this url by clicking on the “Export” button on the open data page mentioned above, finding the “CSV” link, and copying the link url. See the screenshot below.
You should see a large table of data populate in a second or two. One of the columns is called “LOCATION” which contains the latitude and longitude of each polling place, which we’ll use to plot them on the map.
3. (Optional) Add columns for the size and appearance of the map markers.
If your spreadsheet has columns with special headers like “Color” or “Radius”, the values in those column will influence how Geosheets draws your map.
In cells A3 and B3, add column headers called “Type” and “Radius”. In cell A4 type the value “circle” and then paste it all the way down column A. Similarly, in cell B4 type the value “3” and paste it all the way down column B. This will tell Geosheets to render each point as a circle of radius 3 pixels, rather than the default teardrop marker shape. Because we have a large number of points, this will make the resulting map easier to understand.
In cell C3 add a column header called “Color”. We’re going to assign a color to each borough. There is already a unique id for each borough, found in the column labeled “BOROUGH”, which should be in column P. In cell C4 write the formula =GEO_ASSIGN_COLORS(P4:P1203). This will assign a unique color for each value found in column P, so in one step this color-codes every point.
In cell D3 add a column header called “Label”. When making a Geosheets map, any text in a column titled “Label” will show up when your mouse hovers over a placemark on the map. In cell D4 write the formula =E4, then copy cell D4 and paste it all the way down column D. In other words, we’re going to copy the values from the “SITE_NAME” column into a new column called “Label”, since this feels like the appropriate text to show when you hover over a polling place on the map. If you’d like, you can also just rename the “SITE_NAME” column instead.
4. Draw the map using the GEO_MAP function.
In cell A1, write this formula:
=GEO_MAP(A3:P1203, “nyc-polling-places”, “LOCATION”)
Here we’re calling the GEO_MAP function Geosheets to draw a map from the data found in the range A3:P1203, which should be all the data in your spreadsheet. We need to give the map a name in the second argument, so we’ve chosen “nyc-polling-places”. Finally, since our latitude and longitude values are not in the first column, we need to tell Geosheets where to find them, so we pass “LOCATION” as the third argument, since that’s the header for the column containing our coordinates.
In a moment cell A1 should populate with the url of your newly-created map. If you install the Geosheets add-on, you’ll see a preview of the map in a sidebar. In the meantime, if you visit the map page at that url you should see a gear icon that lets you change the map’s settings, including its name, description, and the base map layer.
About the Author
Jonathan Goldman is a former Google Maps and YouTube engineer who is part of a development team that has created Geosheets as a way to bring mapping and geospatial functions directly into Google Spreadsheets.
Learn more about Geosheets at www.geosheets.com and find more examples and tutorials at the Geosheets blog, blog.geosheets.com.
