What We built
COVID-19:
Google Sheet Project to Notify Members of Positive Contact
When we begin to "open up" there will be lots of tracking solutions to reduce the magnitude of future outbreaks. Things like schools and movie theaters and the grocery store will have great, complex solutions to understand how an individual's positive COVID test needs to be reported to those exposed.
But many of our most valuable interactions are not through big companies -- it's your Boy Scout meeting, poker night, soccer practice...and they need a pretty simple solution if they need to contact their members.
We've built a Google Script to accompany a simple spreadsheet.
On one page of the sheet you can just track a list of meetings and who attended.
In the event of positive notification, you select the REPORTING MEMBER and the REPORT DATE into the control page
You have the option of either contacting people that had DIRECT contact with the reporting member, or also those that had indirect contact (e.g., they contacted someone who had direct contact with the reporting member). The group attendees will then receive a message (via Twilio) letting them know about either DIRECT or INDIRECT contact with a member reporting COVID positive.
We skipped the whole "referring people to the latest info"...that's not really the role of your soccer coach and the right answer changes regularly.
That's it.
What's Cool About This
First -- this is for the average, non-developer type user. All you have to do is figure out how to sign up for Twilio and locate your account information and API auth key. That means the car pool mom can use it, the after school tutor or soccer coach.
Second -- it's just one way because we couldn't figure out how to meet HIPAA requirements. Our initial product plan had all kinds of two way data collection to better understand the population and see if people notified were showing symptoms. We couldn't get past the rules around storing health information, and also decided it wasn't the job of the soccer coach to track anything, just to notify in case of risk.
Third - we imagined notification for both DIRECT (I was in proximity of the member who tested positive) and INDIRECT (I was in proximity of someone who WAS in proximity of the reporting member). You start with a grid of members and dates they attended meetings. Our mental challenge was "how do you turn that grid into a contact map of direct/indirect". Our answer felt ELEGANT in the end.
a. You take the chronological stream of meetings of the REPORTING MEMBER into a binary series of 1 (they were at a meeting) and 0 (not at the meeting). Convert it to a real number.
b. Then, for all members, create their MEETING BINARY VALUE and sort biggest to smallest (this will make it so we only have to go through the list once. As we iterate through each member, we BINARY COMPARE the BINARY MEETING value of each person to the reporting member. We do an AND compare to see if someone was in a meeting with the reporting member and add them to our CONTACT LIST. Then, if we are going to notify for INDIRECT CONTACT, we do an OR with the SELECTION MEETING BINARY and continue down the list. Simple and easy.
The Setup
Import the Excel file into Google Sheets or make a copy of: https://docs.google.com/spreadsheets/d/1VtQYDMrCE2QNl8mx6LPT4zAhutFEF5R5cuabzWwuSUA/edit?usp=sharing
Make yourself of copy of this Google Script (or create it from the file in this repository): https://script.google.com/d/1l0QKkbW01bCHJst_z6wmI9wWcnVDA22DERC598HBIRK2zo-hGS7NVfZD/edit?usp=sharing'
Put your Twilio settings into the "Twilio Settings" tab of the Google Sheet
Usage: follow the instructions on the "Outreach" tab.
Demo Link
We put some sample data in the spreadsheet to the cool application of DIRECT and INDIRECT. To send messages you'll have to add your own Twilio creds.
Google Sheet: https://docs.google.com/spreadsheets/d/1VtQYDMrCE2QNl8mx6LPT4zAhutFEF5R5cuabzWwuSUA/edit?usp=sharing
Google Script:https://script.google.com/d/1l0QKkbW01bCHJst_z6wmI9wWcnVDA22DERC598HBIRK2zo-hGS7NVfZD/edit?usp=sharing
Top comments (0)