A Technical Guide

Purpose

This guide is designed for a Google Sheets user who is comfortable working with Google Sheets formulae and related Google Forms.

Background

Care in the Time of Corona is a map-based tool that faciliates mutual aid during a time when our workforce might be in need. The tool identifies the nearest source of help among volunteers, what kind of help they can provide, and how far they are willing to travel. Further acquaint yourself with the impetus behind the tool.

Designed Workflow

The workflow for the process is that volunteers enter information into a Google Form which, in turn, records information into a linked Google Sheet. (Both of these resources are provided.) The Sheet can then, in turn, serve as a datasource for a map, using Awesome Table, a Google Sheet Add-On. The map shows where your volunteers reside so that those in need can identify the closest resource willing to provide the form of help required.

Minimum Requirements
  1. A Google Suite account
  2. Community with Google accounts
  3. Proficiency with Google Sheets
  4. Proficiency with Google Forms
  5. Geocode by Awesome Table (Add-On to Google Sheets)

Please note: the Google account requirement helps to ensure a minimum level of security so that those who do not have permission to view your datasource -- the spreadsheet of volunteers -- cannot view your map.

Implementation Steps
  1. Make a copy of the Google Sheets template: http://sfei.li/coronamap
  2. Using this new copy, enable/install the add-on called Geocode by Awesome Table:

    Once the add-on is successfully enabled, then...

  3. Open the associated Google Form:
  4. Edit the introductory text on the form. If you edit the form template -- the labels, etc -- then you must also edit the query on the Google Sheet.
  5. Once your form is designed to your liking, then return to the Google Sheet and set up geocoding -- the process of deriving mapping location information from street addresses -- in your Google Sheet. On the sheet called “Form Responses 4,” activate “Geocode on form submit.”

    Activate the form trigger. This will ensure that up to 1000 addresses per day can be geolocated on a map. Do not click the option to create a map at this point. The map must instead be associated with the second sheet, QueryFull, which uses a query to modify the primary entries and insert critical formatting.

  6. Open the second worksheet called “QueryFull” within the new template. Enable Geocoding on that sheet as well by clicking “Start Geocoding.”
  7. Click “Geocode.” Once that operation is complete, create a map and view it.

  8. After logging into Awesome Table using your Google account, you now have a workspace in their free resource area. In the new map, you might find it useful to edit the format. To do so, click “Edit” at the top, then modify the map using the columnar information on the right:
  9. Among the options to consider is the ordering of the elements on the map. For instance, to place the filters above the map, change the order in the layout:
  10. Once you click “Update View,” you should see the effect of this configuration change:
  11. The filters reflect the items designated as filters in your associated Google Sheet.

  12. Once the map is ready to share, ensure that the spreadsheet is shared with the intended audience and then click on the “share” symbol in the map to retrieve your distributable link:
  13. Before sharing a link to the map, however, clear the test data from your Google Sheet in the worksheet called “Form Responses 4.”
  14. Also, distribute your form to your target volunteers by opening your Google Sheet and clicking “Form” → “Send Form.”
Known Limitations
Warranties

When deploying this solution, it is important to bear in mind that the technology is provided by Google (Google Sheets, Google Forms) and Awesome Table. You can learn more about these technology providers if you have questions about their respective tools. 

Each provider offers distinct warranty provisions.

The San Francisco Estuary Institute accordingly offers no warranty or service agreement, explicit or implied, in association with this Google Sheet template or guide.

Number of Addresses

According to their documentation, Awesome Table allows for 1000 geocoded addresses per day. With entries beyond this number, you may need to wait for the daily quota to reset. 

Access

Only viewers with Google accounts will be allowed to access the map. This is to afford some customizable privacy for your community of volunteers.