How to manage Preparedness Google Sheets#
General principles#
WHO AFRO Polio Rapid Response Team (RRT) is using Google sheets in order to track preparedness at country, region and district levels ahead of polio outbreak responses.
The Polio Outbreaks platform takes snapshots of the WHO AFRO preparedness Google sheets every 24 hours in order to track the level of preparedness ahead of polio outbreak responses. This way, the preparedness level can be monitored on the dedicated Preparedness dashboard. These snapshots are done on round basis (one preparedness sheet per round), and as long as the outbreak response round has not started yet. Past the round start date as entered into the platform, the daily snapshots stop.
Creating a new preparedness Google sheet#
BEFORE generating a new spreadsheet#
The generated Google sheets will take the scope as entered and available in the platform at the moment of the spreadsheet generation. Please check ahead of the Google sheet generation that:
- the country's geographic data is up to date. If this is not the case, please contact Bluesquare team and provide the latest shape files of the given country.
- the campaign scope is up to date.If this is not the case, please update the scope in the "Scope" tab.
Modifying the spreadsheet after the generation can lead to synchronization issues and poor data quality.
Generating the Google sheet from the platform#
From Polio Outbreaks platform, access the menu entry "Campaigns". Select the campaign you would like to create a preparedness Google sheet for using the "Edit" button in the Action column.
Note that it's not possible to generate a preparedness spreadsheet if the round has already started.
From the selected Campaign view, access the "Preparedness" tab. Select the round you would like to generate a spreadsheet for. Click on "Generate a spreadsheet".
The sheet will be generated using a Google account, setting the user generating the spreadsheet as the default owner.
Setting up the link#
Access the newly generated spreadsheet by clicking on the dedicated button. Once on the Google sheet, access the sharing settings on the top right corner. Set the link to open access to all, copy the open url and paste it back to the platform. The link can now be shared with users.
Additional important information#
- Languages
The preparedness Google sheets are available in French, English and Portuguese. The platform picks the language set as default in the Polio > Configuration > Country settings, where a preferred language can be configured per country among French, English and Portuguese.
- Protected fields
In order for spreadsheets to remain standardized according to WHO AFRO RRT team requirements, the Google sheets are generated with data locks. For instance, users will only be able to edit certain cells, and with pre-assigned values, typically 0 to 10 to rate preparedness levels. If the template needs to evolve, please contact Bluesquare team.
- Exclude worksheets
Countries sometimes add custom worksheets. Depending on the format and content of those sheets, they can interfere with the parsing of the data and result in errors. The most common cases are already handled, i.e: the worksheets are ignored by the parser. New worksheets can be added to the parser's blacklist by going to
Refreshing preparedness sheets data#
As emphasised above, automatic snapshots of the preparedness spreadsheets are done in the platform every 24 hours for the upcoming rounds (i.e. rounds which start dates as available in the platform are in the future). These snapshots take place every day at 7AM UTC.
Manual refresh from the platform#
However, it is possible to refresh preparedness sheets manually via the platform. In order to do this, please access via the Menu the entry "Campaigns", select the campaign you would like to update preparedness data for, and then access the Preparedness tab. Here, if a spreadsheet is already available, the action button "Refresh preparedness data" is enabled. Click on it and it will launch a refresh of the data.
A preview snapshot appears below.
Link with the Preparedness dashboard#
Users can visualize preparedness data evolution using the Preparedness dashboard. This dashboard is done using Power BI. Automatic refreshes happen every hour. However, it is possible to launch a manual refresh for platform users using the "Launch refresh" button in the bottom left corner of the dashboard. This will synchronize with the latest platform data.
Important to know
The dashboard refresh does NOT launch a general preparedness Google sheet synchronization for all campaigns in the platform. In order to see the latest preparedness sheet data, do not forget to synchronize at campaign level as well (as described in above section).
Automatic emails#
The platform sends automatic emails when synchonization has failed. It can sometimes be transient issues, and a manual refresh can then solve the issue. However, if the issue persists, please contact Bluesquare team.