One of the most common map visualizations is to show a collection of features and an associated numeric value, for example annual rainfall or income levels in different places. Color-coding each feature based on its value is often the easiest way to visualize the data. But how to assign colors to convey the magnitude of each value?
The answer is to assign each value to colors along a spectrum. But designing a good palette for a color spectrum and then interpolating and assigning values is a big undertaking for a small project, if not prohibitively difficult.
Fortunately, Geosheets supports color-coding using carefully designed spectrums. We use the viridis family of color palettes, which were designed to be attractive and colorful, but also to show contrast amongst values clearly, and to be comprehensible even to people who suffer from colorblindness.
These are the four viridis palettes:
To use them, invoke the function GEO_COLOR_FROM_PALETTE on a column of numeric values. For example, here's how you would invoke this on a data table with numeric data in column B to be color-coded, entering the given formula in cell C2 in order to populate column C:
A B C D 1 City Value Color Formula 2 Chicago 42 #297a8e =GEO_COLOR_FROM_PALETTE(B2:B4) 3 Seattle 11 #440154 4 Houston 87 #fbe723
Let's walk through a full example.
1. Open a new spreadsheet with Geosheets activated.
Install the Geosheets add-on and then create a new spreadsheet. To activate Geosheets on a new spreadsheet, find the "Add-ons" menu within Google Sheets, then the "Geosheets" menu item, and finally click "Open Geosheets".
2. Import the sample data using IMPORTHTML.
In cell A1 type this formula:
=IMPORTHTML("https://www.apartmentlist.com/rentonomics/national-rent-data/", "table", 1)
This is a Google Sheets built-in function that will scrape that url and import the first table element it finds on the page (for the second table, pass 2 instead of 1, etc). In a moment you should see your spreadsheet populate with the data from that page.
3. Create a new Color column and populate it using GEO_COLOR_FROM_PALETTE.
In column F, the first empty column, create a new header in cell F1 called "Color". Then in cell F2 write this formula:
This will look at all the numeric values in column B, which contains the median 1-bedroom rents, and assign a color to each row. The first second argument, "plasma", tells the function which of the above four palettes to use, since we're choosing to use a palette other than the default. The function finds the minimum and maximum prices in column B and puts those at the ends of the color spectrum, and linearly interpolates all values in between. You can optionally pass a 3rd and 4th argument to the function, which are a fixed minimum and maximum to use, for example if you want the values to be colored on a fixed scale of say $0 to $5000.
4. Draw the map using GEO_MAP.
In any empty cell, say, somewhere in column H, write this formula:
This tells Geosheets to create a map using the data in columns A through F, and gives the map the label "rents". Geosheets assumes that you have put location names in the first column unless you tell it otherwise, so it will geocode each city in column A, and place a marker with the given color at each point. The rest of the attributes in your table will be visible when you click on each marker.
The final map should look something like this:
View the full-page map here: https://www.geosheets.com/map/s:DlAEERAn/rents
The final spreadsheet should look something like this:
You can view the full spreadsheet and make a copy here: https://docs.google.com/spreadsheets/d/1HtbawOWSPfDCp_eXXQ5Qavxp4MRmVdThW_K_EDdiku0/edit