How to Conduct Facility Tracking Using Google Apps 


In the midst of Hurricane Sandy, local health departments across Maryland raced around the clock to ensure that vulnerable, critical care facilities—including dialysis, assisted living, hospice care and nursing homes—were not left without power.  

Historically, facility tracking in Maryland has presented a profound logistical challenge at the state level: each power status update needed to be manually collated from dozens of spreadsheets, each emailed in at hourly intervals from reporting local health departments, all against the backdrop of a state emergency. 

During Hurricane Sandy, events unfolded differently in Maryland. We used a new facility tracking system that saved personnel time and provided better results. The system was automated and cost the state no additional financial resources to implement. The system would also be easy for any state or locality to adopt. 

Instead of manually collecting the status of the thousands of facilities, we established an automated system using the freely available ‘Google Apps’ tools. Using the online spreadsheets enabled by Google, status updates made by local health departments were instantly reported into a 'Master' spreadsheet that contained information collected from all the local health departments.  

Moreover, the system automatically identified the most at-risk facilities, pulling them into a separate table for instantaneous analysis.   

master tracking sheet image 1.png
sub image 1.png
Master Spreadsheet Automatically Culls At-Risk Facilities

The data, in turn, could be shared across the emergency operations center, allowing for real-time mapping of power statuses throughout the state. As an example, the local health department in Allegany County could enter into its spreadsheet a problem with a nursing home. Within seconds, it would appear on a map and on the tablets of key officials responsible for deploying critical resources at the state emergency operations center.

Are you interested in implementing this system—using free, online tools—in your jurisdiction? We have made templates of all ‘Google Apps’ spreadsheet files—the key components of the facility tracking system—available online.

Here is what to do with them:

First, create a copy of the “Local Tracking Sheet” for each local health department, populate it with information regarding all of the vulnerable facilities overseen in the locality (one row per facility), and share this spreadsheet with the department itself (through the ‘Google Apps’ share function).

 local tracking image 2.png
Each Local Health Department’s Tracking Sheet Tracks All Overseen Facilities


sharing function image 3.png
Sharing Function Enables Dynamic Linking of Facility Spreadsheets

Second, the “Complete_List” tab on the “Master Tracking Sheet” needs to be updated in order to aggregate the information from the local health departments. This can be done using the 'ImportRange' function, which populates the rows with the data from each respective department’s spreadsheet. 

master tracking image 4.png
ImportRange Function Enables Instant Aggregation of Local Health Department Information

Third, if desired, the master sheet should be populated with queries of interest using the vMerge tool. For instance, to set up a sheet displaying only those counties and facilities without power, the query =VMERGE(QUERY(Complete_List!A2:W100, "select I,J,B,C,D,E,F,G,Q,V,W where A=3")) is used in the template. For more details on how to use the vMerge query tool, see document 4 below (“Spreadsheet creation instructions”).

 master tracking image 5.png
Customizable Query Allows for Facility Data to Be Selectively Sorted

Fourth, upon state “activation” of facility tracking, each local health department should record, for each overseen critical care facility, power status (on a 1-4 scale), date of update and any associated comments in its “Local Tracking Sheet.” These comments are then updated automatically on the master sheet. The state should make any response comments and record the date/time during which the status was recorded appropriately on the master sheet itself.

local tracking image 6.png
Recorded Local and State Comments Allow for Bilateral Communication During Emergency

Fifth, the master sheet should be leveraged to provide a complete snapshot over local facilities, and be integrated with mapping tools using GIS software to generate visual assessments of power outages throughout the state.

 master spreadsheet image 7.png
Master Spreadsheet Provides Instant Snapshot of Facility Statuses

 map image 8.png

Complete Integration with GIS Mapping Tools

To ensure maximum utility of the tracking system, the state office should frequently re-update the complete facilities list with proper credentials, addresses and coordinates.

More details, including key templates, are included in the attached files:

1.       Local Tracking Sheet Template A -- Provides a sample template file (with ‘dummy’ data) for a local health department’s tracking spreadsheet.

[1] Example Local Tracking Sheet A.xlsx[1] Example Local Tracking Sheet A.xlsx 

2.       Local Tracking Sheet Template B -- Provides another sample template file (with ‘dummy’ data) for a local health department’s tracking spreadsheet.

[2] Example Local Tracking Sheet B.xlsx[2] Example Local Tracking Sheet B.xlsx

3.       Master Planning Sheet Template -- Provides a sample template file (with ‘dummy’ data) for central/master tracking spreadsheet.

[3] Example Master Tracking Sheet.xlsx[3] Example Master Tracking Sheet.xlsx

4.       Spreadsheet Creation Instructions (with vMerge Query Details) -- Contains a step-by-step guide for how the system’s spreadsheets can be constructed from scratch using Google Apps. Also details the use of the vMerge Query function.

[4] Spreadsheet Creation Instructions.docx[4] Spreadsheet Creation Instructions.docx

5.       Instructions For Use -- Describes how each local tracking spreadsheet might be used during an emergency; provides template of a usage guide to distribute to local health departments.

[5] Instructions for Use.docx[5] Instructions for Use.docx