Personal Protective Equipment (PPE) inventory tool and data flow documentation

Overview

This document describes the data in the PPE inventory tool. This document serves as a history of the changes made and an explanation of how the data is maintained and pulled into the tool.

We created the tool in Google Data Studio, though it is not really a dashboard. Rather, it was created in lieu of uploading a daily spreadsheet each day as the organizations that have personal protective equipment in stock change, or as their inventories change. It allows the public to search by equipment type and get contact information for companies that have that equipment in stock.

Data Source

The data comes from this spreadsheet, which is updated by the Executive Office of Housing and Economic Development, who publishes info on companies that offer personal protective equipment on a regular basis.

The tool automatically refreshes every 15 minutes, though in practice, once updates are made to the spreadsheet, we force-refresh the data in Data Studio.

Spreadsheet Details

Data Transformations

In order to get the data from the spreadsheet into a visual dashboard table that is easy to read and filter, the web analytics team had to perform a number of data transformations.

First, we needed to "stack" the data: Instead of each row showing if one company offered each piece of protective equipment, we needed a separate record for each company for each equipment type. This seemed to be a requirement on the Data Studio side: We weren't able to include all the equipment in a single filter unless we did it this way. See the "INPUT" tab for how the data looks to begin, and the “OUTPUT” tab for the result.

To do this transformation, we used AppsScript, a tool that allows you to manipulate Google Sheets using JavaScript and the Sheets API. You can see the script we wrote in Tools -> Script editor.

Contact Information

The second transformation is to add contact information to the newly structured data. We do this using vlookups that consult the "INPUT" tab. Contact information includes sales representative names, websites, phone, and email.

Some of this data is blank, and per HED's request, we use a "-" instead of an error, blank, or a null value. We also needed to manually place commas between phone numbers, emails, and names if they deviated from the standard entry method (e.g. a missing email address or multiple phone numbers.

AppsScript details

As mentioned above, an AppScript looks for the company name and the PPE they supply then lists them in the “OUTPUT” tab of the spreadsheet.

To run this script, all the data must be cleared from the "OUTPUT" tab and the new data pasted into the "INPUT" tab. If there are extra columns, or if columns are switched, the script will produce incorrect data or break.

We have invited Ryan Boehm from EOHED to run the script himself whenever is necessary. However, we also said that we would continue to assist if something major needed to change.

Dashboard Details

Filters

The PPE tool has one single-select filter (i.e. you may only pick one item at a time) for all equipment types. It then filters for companies that stock that PPE.

To make the dashboard less "noisy," we also apply a filter that removes equipment equal to null (or blank). This removes the blank cells in our spreadsheet that are extensions of the vlookups that don’t have data associated with them yet, but are there to capture new data when it is added. These blanks are represented as “#N/A” within the spreadsheet, but they read as blank or null when pulled in by Data Studio.

Embedding on Mass.gov

The tool is embedded on Mass.gov on this info details page: https://www.mass.gov/info-details/reopening-purchasing-supplies-for-workplace-safety-and-hygiene

The code for the embed is in this Github repo: https://github.com/massgov/mg_utility

Trail map

In the future, we plan to add validation to the AppsScript function so that it alerts us about changes to the data's structure.