Background
This project automates nurse staffing assignments in an ICU by integrating patient acuity, pod constraints, device rotation, and leadership roles into a unified system. Built using Google Apps Script (JavaScript), the solution processes data from a Google Sheets setup comprising a “Manifest” of patient data and a “Staff” roster to create a fair distribution of patients to nurses for each shift. For scalability, the project was initially tested on a "playground" unit consisting of two leadership roles—a charge nurse and a resource nurse—and a maximum of eight bedside nurses caring for approximately 16 patients.
The formation of this program was heavily inspired by the way football and soccer teams create their lineups to take the field. Just as coaches strategically assign players to positions based on their strengths, roles, and the dynamics of the game, this system seeks to do the same with nurse staffing assignments. Each nurse, like a player, brings unique skills and experience to the team, and the goal is to position them where they can perform at their best while maintaining balance across the unit. This approach mirrors the careful planning required to create a lineup that not only meets immediate demands but also adapts to the fluidity and unpredictability of the game—or, in this case, the ICU shift.
The “challenge” to make this program was presented to me by my coworker. The code was initially written in Python, inspired by Automate the Boring Stuff with Python by Al Sweigart and heavily informed by Chapter 15 and 16 from Henry Petroski’s To Engineer Is Human: The Role of Failure in Successful Design, and utilized Microsoft Excel for data handling. However, due to implementation challenges and compatibility issues, transitioning to a Google Sheets script proved to be the most practical solution. I leveraged Claude and Bard/Gemini to assist in converting my Python code into JavaScript, ensuring a smooth adaptation to the Google Apps Script environment.
These are the Rules
Patient rooms are numbered 301 to 316 and divided into two pods: Pod A (rooms 301–308) and Pod B (rooms 309–316). Nurses must be assigned to a single pod, meaning a nurse caring for a patient in Pod A cannot also care for a patient in Pod B. The standard nurse-to-patient ratio is 1:2 but becomes 1:1 for high-acuity patients. These high-acuity cases include patients with devices such as IABP, Impella, CRRT, TTM, LVAD, or Rotoprone. They also encompass patients requiring constant care for reasons such as psych/behavioral issues, stroke with thrombolytic treatment, or “imminent death.” For this model, Psych 1:1 and other high-acuity assignments are treated synonymously. Patients requiring these "Needs" are only assigned one nurse, prohibiting dual assignments. COVID patients are not given special considerations in this version of the system.
There is also the concept of a "Suboptimally Utilized Nurse," or SUN. In the context of this automated staff assignment system, a SUN refers to a nurse assigned to only one patient, not due to the patient's high acuity, but simply because no other patients are available within their assigned pod. The current iteration of the script does not address SUNs and is touched upon later in this blog under Bugs.
Some patients may have multiple devices (e.g., Impella with CRRT), though certain combinations, such as IABP with Impella, are prohibited. To ensure fairness, the system maintains a "device dates" log to track the last time each nurse cared for patients with specific devices or high-acuity needs, rotating assignments equitably among staff. Each patient is assigned an acuity score, which the script uses to distribute workload evenly across nurses within each pod. Leadership roles, such as charge or resource nurses, cannot simultaneously serve as preceptors to avoid conflicts in responsibility.
The Set Up
The Google Sheets implementation includes two key tabs: “Staff” and “Manifest.” The “Staff” tab lists nurse-specific data, including names, qualifications (e.g., eligibility to serve as charge or resource), and "device dates" for tracking prior assignments. While all nurses qualified to serve as charge (C) can also serve as resource (R), the reverse is not always true. Ancillary roles like Code Nurse are noted but do not directly influence patient assignments. Device-specific headers start in Row 1 (Cell D1), with subsequent rows logging the dates of each assignment.
The “Manifest” tab functions as a bed roster, detailing room numbers, acuity scores, and specific care needs. High-acuity patients are flagged as requiring single-nurse assignments. Designations such as "NO" indicate unoccupied beds, while "OTA" marks beds open for admission, allowing nurses to be assigned to these rooms.
The script processes this data to generate nurse-patient assignments that adhere to patient-specific needs, workload balancing, pod constraints, and device rotation. The output is presented in a clear, table-based format, with one column for room numbers and another for assigned nurse names. Each row corresponds to a specific room, providing a structured and efficient overview of nurse assignments for the shift.
The Script
The following section is background behind the code that I used. You can skip over if you have no interest in learning about the machinations behind the script.
1. Create or Clear the Output Sheet
The script checks for the presence of an "Assignments" sheet. If it doesn’t exist, it creates one; if it does, it clears its contents to prepare for new data.
let outputSheet = ss.getSheetByName("Assignments");
if (!outputSheet) {
outputSheet = ss.insertSheet("Assignments");
}
outputSheet.clearContents();
2. Read Staff Data
This chunk reads the "Staff" sheet, captures headers, and identifies columns corresponding to various devices (e.g., IABP, Impella) for tracking nurse assignments.
const staffData = staffSheet.getDataRange().getValues();
const staffHeaders = staffData[0];
let deviceColIndex = {};
for (let col = 3; col < staffHeaders.length; col++) {
const deviceName = staffHeaders[col];
if (deviceName) {
deviceColIndex[deviceName] = col;
}
}
3. Build Nurse Objects
Each nurse is represented as an object with details like their qualifications, assigned rooms, acuity total, and device dates. This prepares the data for assignment logic.
let nurseList = [];
for (let r = 1; r < staffData.length; r++) {
const row = staffData[r];
if (!row[0]) continue;
let nurseObj = {
name: row[0],
canCharge: (row[1] === "C"),
canResource: (row[1] === "C" || row[1] === "R"),
codesPreceptor: row[2],
deviceDates: {},
assignedRooms: [],
assignedAcuityTotal: 0,
hasHighAcuity: false,
isChargeOrResource: false
};
for (let device in deviceColIndex) {
let dateVal = row[deviceColIndex[device]];
nurseObj.deviceDates[device] = dateVal ? new Date(dateVal) : null;
}
nurseList.push(nurseObj);
}
4. Identify Charge and Resource Nurses
This identifies and designates one charge nurse and one resource nurse based on qualifications, marking them as ineligible for bedside assignments.
let chargeNurse = null;
let resourceNurse = null;
for (let n of nurseList) {
if (!chargeNurse && n.canCharge) {
chargeNurse = n;
n.isChargeOrResource = true;
continue;
}
if (!resourceNurse && n.canResource && !n.isChargeOrResource) {
resourceNurse = n;
n.isChargeOrResource = true;
break;
}
}
let bedsideNurses = nurseList.filter(n => !n.isChargeOrResource);
5. Read Manifest Data
This section processes the "Manifest" sheet to extract patient room numbers, acuity scores, and specific needs. High-acuity patients are flagged.
const manifestData = manifestSheet.getDataRange().getValues();
let patients = [];
for (let r = 1; r < manifestData.length; r++) {
const row = manifestData[r];
let roomNumber = row[0];
let acuity = row[1];
let needs = row[2];
if (!roomNumber || !needs || needs === "NO") continue;
const highAcuityTags = ["IABP", "Impella", "CRRT", "TTM", "LVAD", "Rotoprone", "HIGH ACUITY", "Psych 1:1"];
let isHighAcuity = highAcuityTags.includes(needs);
patients.push({
room: roomNumber,
acuity: acuity || 0,
needs: needs,
isHighAcuity: isHighAcuity,
isIABP: (needs === "IABP"),
isImpella: (needs === "Impella")
});
}
patients.sort((a, b) => b.acuity - a.acuity);
6. Pod Logic
Helper functions determine if a room belongs to Pod A or Pod B, enforcing the rule that nurses must stay within a single pod.
function roomIsPodA(rm) {
return rm >= 301 && rm <= 308;
}
function roomIsPodB(rm) {
return rm >= 309 && rm <= 316;
}
7. Check Room Eligibility for Nurses
This function verifies if a nurse can be assigned a specific room based on pod constraints, number of current assignments, and high-acuity limitations.
function canAssignRoom(nurse, roomNumber) {
if (nurse.hasHighAcuity || nurse.assignedRooms.length >= 2) return false;
if (nurse.assignedRooms.length === 0) return true;
let firstRoom = nurse.assignedRooms[0];
let firstIsPodA = roomIsPodA(firstRoom);
let newIsPodA = roomIsPodA(roomNumber);
let firstIsPodB = roomIsPodB(firstRoom);
let newIsPodB = roomIsPodB(roomNumber);
if (firstIsPodA && newIsPodA) return true;
if (firstIsPodB && newIsPodB) return true;
return false;
}
8. Assign Nurses to Patients
Patients are assigned nurses based on their needs, starting with high-acuity cases. Nurses are selected using device rotation or workload balancing logic.
for (let p of patients) {
let chosenNurse = null;
if (p.isHighAcuity) {
if (p.isIABP) {
chosenNurse = findBestNurseForDevice(bedsideNurses, "IABP", p.room);
} else if (p.isImpella) {
chosenNurse = findBestNurseForDevice(bedsideNurses, "Impella", p.room);
} else if (["CRRT", "TTM", "LVAD", "Rotoprone"].includes(p.needs)) {
chosenNurse = findBestNurseForDevice(bedsideNurses, p.needs, p.room);
} else {
chosenNurse = findBestNurseNormal(bedsideNurses, p.room);
}
} else {
chosenNurse = findBestNurseNormal(bedsideNurses, p.room);
}
if (chosenNurse) {
chosenNurse.assignedRooms.push(p.room);
chosenNurse.assignedAcuityTotal += p.acuity;
if (p.isHighAcuity) {
chosenNurse.hasHighAcuity = true;
}
if (deviceColIndex[p.needs] !== undefined) {
chosenNurse.deviceDates[p.needs] = new Date();
}
}
}
9. Prepare Final Output
This aggregates all assignments, including leadership roles, and writes them to the "Assignments" sheet in a clear, tabular format.
let allAssigned = [];
for (let n of bedsideNurses) {
for (let rm of n.assignedRooms) {
allAssigned.push({ room: rm, nurse: n.name });
}
}
allAssigned.sort((a, b) => parseInt(a.room) - parseInt(b.room));
let rowIndex = 1;
outputSheet.getRange(rowIndex, 1).setValue("Leadership Role");
outputSheet.getRange(rowIndex, 2).setValue("Assigned Nurse");
if (chargeNurse) {
rowIndex++;
outputSheet.getRange(rowIndex, 1).setValue("Charge Nurse");
outputSheet.getRange(rowIndex, 2).setValue(chargeNurse.name);
}
if (resourceNurse) {
rowIndex++;
outputSheet.getRange(rowIndex, 1).setValue("Resource Nurse");
outputSheet.getRange(rowIndex, 2).setValue(resourceNurse.name);
}
rowIndex += 2;
outputSheet.getRange(rowIndex, 1).setValue("Room");
outputSheet.getRange(rowIndex, 2).setValue("Assigned Nurse");
for (let i = 0; i < allAssigned.length; i++) {
rowIndex++;
outputSheet.getRange(rowIndex, 1).setValue(allAssigned[i].room);
outputSheet.getRange(rowIndex, 2).setValue(allAssigned[i].nurse);
}
Shortcomings
Even with sophisticated algorithms designed to align staff competency with patient needs, automated staff assignment programs have inherent limitations. One major shortcoming is their inability to account for human factors. These systems often overlook individual preferences, personal circumstances, and interpersonal dynamics within teams. For example, a nurse dealing with personal stress, such as a sick child at home, might need a less demanding shift, or two colleagues with a history of conflict might be assigned to collaborate, reducing efficiency and morale. These subtleties, while critical to a positive work environment, are challenging to capture in an algorithm, potentially leading to dissatisfaction, decreased morale, and even staff turnover.
Another limitation is the system’s difficulty in adapting to real-time changes and unexpected events. A sudden surge in patients, emergency situations, or unforeseen staff absences can disrupt a meticulously pre-planned schedule. While programming for flexibility is possible, automated systems are inherently rigid, which may hinder the rapid adjustments required in dynamic healthcare settings. This rigidity can result in delays in care, uneven workloads, and potential risks to patient safety.
Over-reliance on such automation may undervalue the role of human judgment and intuition. Nurse leaders (charge nurses), with their deep understanding of team dynamics and individual capabilities, bring critical insight that cannot always be replicated by automated systems.
Lastly, resistance from staff can pose significant challenges to implementation. Concerns about job security, loss of control over schedules, and skepticism toward the technology may create apprehension. Successful adoption requires clear communication, comprehensive training, and iterative feedback to address these concerns and ensure that the system benefits both staff and patients.
In many ways, I approached this project as a combination of a Hail Mary, a nuclear option, and a lark. The primary motivation was not necessarily to create staff assignments but to validate them—a way to ensure that assignments were fair, balanced, and aligned with patient needs and nurse competencies. It was born out of necessity during challenging times when the pressure to make quick decisions in high-stakes situations often left little room for reflection or verification.
This program serves as a practical, three-minute solution for moments when the shift is so intense that there’s no time to second-guess or analyze every detail of the assignments. By automating the process, it eliminates the guesswork, providing a structured and reliable framework that can guide decision-making under pressure. While the goal was to bring efficiency and clarity to an often chaotic process, it also carried the weight of being a bold attempt to tackle deeply ingrained inefficiencies in staffing practices.
At the same time, there was an element of curiosity driving this project—an interest in seeing how far automation and algorithms could go in solving such a human-centered problem. This combination of practicality, urgency, and exploration reflects the essence of the program: a tool designed to enhance decision-making, save time, and validate the complex choices nurse leaders make every day in a fast-paced and unpredictable environment.
Bugs
When attempting to display un-assignable rooms—such as beds with no eligible nurses available—issues arose in the program’s execution. Specifically, rooms flagged as “NNA” (No Nurse Available) did not always display properly, disrupting the clarity of the final output. This bug highlights the challenge of incorporating nuanced exceptions into the assignment algorithm while maintaining a coherent and accurate representation of the staffing situation. Addressing this requires refining how the system processes and outputs unassignable cases to ensure transparency and usability.
Previously, I mentioned the concept of a "Suboptimally Utilized Nurse." When attempting to address this situation, the script would end up "skipping" a room, leaving it unassigned. The bug likely arises from the program's strict pod constraints, the distribution of patient acuity within pods, and the algorithm's lack of explicit awareness of "Suboptimally Utilized Nurse" situations. To address this, the algorithm needs to prioritize 1:2 assignments whenever possible, potentially consider pod flexibility in unavoidable "Suboptimally Utilized Nurse" scenarios, and at least identify and flag these situations in its output for human oversight and adjustment.
Future Considerations
A potential enhancement to the automated staff assignment system is the Strength Score. The Strength Score would be calculated based on a nurse's experience, training, proficiency with medical devices, and ability to serve as a charge, resource, or preceptor. This score would ensure that high-acuity patients are assigned to highly experienced nurses, while lower-acuity patients are assigned to less experienced nurses who can benefit from mentorship opportunities.
Another consideration is how to identify float assignments on the roster. The suggestion is to use "NL" followed by a number to designate float nurses, with "NL" standing for "Non-Local." Finally, the Continuity of Care Feature aims to assign the same nurse to a patient on consecutive shifts when possible. This feature would prioritize continuity while also balancing other factors like patient acuity, nurse workload, and device-heavy patient rotation. If no nurse is available for a given assignment, "NNA" (No Nurse Available) would be displayed.
Conclusion
This project represents a bold step toward leveraging technology to address the complexities of ICU nurse staffing. While the program is not without its challenges, including the inherent limitations of algorithms and the bugs encountered during implementation, it reflects an innovative approach to improving efficiency, fairness, and patient care in a demanding healthcare environment. By combining data-driven automation with future considerations like Strength Scores and Continuity of Care, the system has the potential to evolve into a valuable tool for nurse managers and staff alike. Ultimately, this project is a testament to the importance of innovation, experimentation, and the ongoing pursuit of solutions that balance technical rigor with the human element in healthcare.