Bespoke automation of medical workforce rostering using Google’s free cloud applications

Introduction

In order to provide consistent, safe and high-quality care, it is necessary to ensure that medical staff are optimally deployed at all times.1 There must not only be a sufficient number of doctors covering each service, but they must also be of the appropriate level of experience. Timetabling doctors to ensure these requirements are met is challenging since each will participate in several duty rosters (e.g. emergency cover, ward cover and on-call) in addition to their usual clinic, ward and theatre timetables.

A doctor’s overall timetable is therefore a composite of several timetables which describe specific clinical duties, non-clinical duties and leave. The status quo in many departments is to keep track of each of these individual timetables in spreadsheets which are often owned by different members of staff.

Approaching administration in this fashion has two significant drawbacks. First, it is inefficient. Storing rosters in separate spreadsheets owned by the same or distinct members of the department imposes a significant time investment in manual crosschecking. This is particularly costly for a department when a doctor is involved in administration as it will reduce departmental income as well as the doctor’s clinical performance.2 Second, it can give rise to unsafe staffing levels. Miscommunication could lead to a doctor being timetabled to two duties at once, or leave being approved when insufficient cover is available. Certain combinations of duty could also lead to illegal working patterns (e.g. under the European Working Time Convention3).

There exist many off-the-shelf electronic rostering systems which aim to streamline staff rostering. Some of these offer free versions which are often limited to a small number of employees or a reduced feature set. These are excellent options to track employee leave, sickness, pay and similar. However, the needs of a unit within a hospital are often starkly different to other units within the same hospital, so use of a generic system will often fail to alleviate many of the manual administrative tasks needed to run a specialised clinical service. For instance, a valid outpatient clinic timetable (i.e. allocation of staff to particular clinic rooms or services) might need to take into account leave rotas, on-call rotas, theatre rotas and the distribution of staff competency.

More complete automation of medical workforce management requires a more flexible solution that allows rule-based interaction of multiple clinical timetables and rosters, as well as the addition of custom functionality to meet local needs. This paper describes a pilot study to assess the feasibility of a bespoke cloud-hosted administration system that stores all rosters describing a doctor’s clinical duties, and automates many of the processes needed to deploy staff optimally. The performance and user satisfaction of this system in a busy tertiary Ophthalmology department over a 2-year period is described. The system described is fully customised to the needs of the department and runs on Google’s zero cost cloud architecture.

Methods

Figure 1 shows an overview of the system’s architecture. Users interact with the system via a website hosted on Google Sites. Their individual rosters (on-call, emergency cover and leave) are stored in Google Sheets. Google Forms are used to submit requests into the Google Sheets. Scripts, programmed in Google Apps Script, are triggered by the submission of a new request, and at certain pre-defined times.

Request permission

Results

Over the period August 2014 to June 2016, the system automatically handled 563 leave requests from doctors in the Ophthalmology Department at Addenbrooke’s Hospital (Cambridge, U.K.). As the validity of leave requests was checked automatically against departmental rules, the rejection rate of leave requests by managers fell to 0.3%. As a result of the low rejection rate, clerical staff could be informed of medical staffing levels at the time of initial request rather than waiting for following leave approval: a mean of 87 days’ notice (median 71 days).

There were no instances of staff being given approval for leave when they were on vital clinical duties. Three hundred on-call swaps were automatically processed with no unexpected gaps in the roster. Around 3300 emails were automatically generated and 1000 PDF forms (for central hospital administration) automatically completed.

The online user satisfaction form was completed by 24 members of staff (20 doctors, 2 nurses and 2 administrators who interfaced directly with the system):

1. 100% (n = 24) were satisfied with the system.

2. 100% (n = 12) preferred it to the previous system (the remaining 12 had joined after the change in system).

3. 100% (n = 20) preferred it to any systems they had encountered in other hospitals (the remaining 4 had never worked elsewhere).

Discussion

This pilot study assessed the feasibility of introducing a bespoke, unified, cloud-based system to manage the clinical and leave commitments of doctors in a large Ophthalmology department. The user satisfaction survey confirms that automation of many aspects of clinical and personal timetabling was well received.

By building a bespoke administrative solution using Google’s cloud services, it was possible to tailor the application precisely to the requirements of the individual unit. This offers some obvious advantages over off-the-shelf rostering solutions, as well as allowing for additional functionality. For example, although our unit had no strict requirements for the specific appearance of PDFs generated, the central hospital administration requires leave and expenses requests to be documented on a particular form. Using the PDF editing functions of Google App Script, it was possible for the system to output electronically signed PDFs on the hospitals preferred paperwork. It was also possible to output the clinic timetables (which allocate doctors to particular clinics each day) in the format that the department’s administrators and nurses were used to. By using a scripting language to perform validity check on incoming requests (e.g. leave requests), there is no limit to the complexity of rules that can be applied in processing requests or allocating personnel.

Formally, expressing efficiency gains brought about by the system is not possible, since no formal pre-intervention assessment was made. However, it is possible informally to estimate the gains. Figure 2 shows the leave requesting pathway in the department before and after automation. Prior to automation, a valid leave request required six steps to fully process, all of which were carried out by one of four humans (applicant, responsible consultant, departmental administrator and ‘admin registrar’). After automation, the pathway was reduced to four steps, only 2 of which required human input (applicant and responsible consultant). Over the 20-month study period, 563 leave requests were submitted. Automation therefore averted 2252 human interventions in leave processing alone, while the remaining human interventions were substantially streamlined. There were also no incidences of inappropriate leave approval where critical clinical duty was already allocated during the study period. Although not formally measured, these incidences did occur prior to automation.

Request permission

It is possible to identify reasons for the satisfaction among both doctors and administrators. For doctors, the system obviates the need to complete physical forms and chase managers for leave approval, and allows access to live rosters for all clinical duties. Administrative staff are able to plan further ahead due to increased notice of intended leave, and no longer have to perform manual comparison across multiple spreadsheets to work out staffing levels. Examining Figure 2, the ‘admin registrar’ (whose duties were felt to be excessively burdensome in an open letter from the Royal College of Ophthalmologists4) and the departmental administrator particularly benefit by being removed almost entirely from duties in leave approval and on-call swapping, freeing them up for other duties.

A number of challenges were anticipated in transitioning to this system. Technically, a lack of funding meant that it was not possible to use the preferred infrastructure of a cloud hosted website with an SQL database to store the rosters. Using Google Sheets and scripts allowed simulation of a relational database by treating each sheet as a table, and coding queries into scripts. This is an inefficient but workable solution. Google Apps Script has excellent functionality within Google Sheets to perform all other automated tasks. On the human front, there was initial concern that doctors would tolerate the level of automation proposed. Leave rules, for example, are often treated flexibly, but the system will issue an automatic rejection if they are breached. Surprisingly, this rigidity led to little discontent.

Although an in-house solution like this offers extensive tailoring of functionality, the solution is not zero cost overall. In this pilot, we used Google’s cloud-based applications (Forms, Sheets, Sites and Google App Script) which are available at no cost. However, development time of approximately 5 days was required initially, with a further hour or two per month needed to perform updates such as new staff joining. The technical skills required to set up and program the application are well within those of many members of an IT department. Using free cloud services (in this case, from Google) is a viable and financially attractive approach. However, more complex tasks could easily be automated were the system powered by a true relational database. The current commercial alternative is an off-the-shelf online rostering solution, though these allow for far less customisation of function.

Overall, this pilot study suggests that a bespoke, cloud hosted, centralised and automated repository of timetables of clinical duties is popular, reduces workload and supports planning for safe staffing levels across multiple clinical services. While the solution described here might not be suitable for all organisations, the study illustrates that automation of administrative functions (significantly beyond those supported by off-the-shelf offerings) can be achieved by bespoke solutions commissioned at the departmental level.