Plan and approve standby staffing recovery with Gemini, Google Sheets, and Gmail

Quick Overview

This workflow runs daily to detect next-day staffing shortages from Google Sheets, generates a standby staffing proposal with Google Gemini, requests manager approval via Gmail, and—only after approval—emails invitations to selected standby staff while tracking decisions and notification status in Sheets.

How it works

1. Runs every day at 6 PM and loads configuration values such as the Google Sheets document ID, target date (tomorrow), and notification email.
2. Reads the next-day workload, scheduled shifts, and standby availability from Google Sheets and calculates capacity gaps per time slot, including validation of dates, times, breaks, and email formats.
3. If a shortage exists, sends the calculated gaps and standby availability (without emails) to Google Gemini to generate proposed standby assignments.
4. Validates the Gemini proposal against the original Google Sheets data (availability, slot boundaries, non-overlap, and no overstaffing) and emails the manager if the proposal fails validation.
5. If the proposal is valid and no StaffingPlans entry already exists for the target date, saves the plan to the StaffingPlans sheet and sends a Gmail Approve/Reject message that waits up to 24 hours for a response.
6. Records the manager’s decision back to the matching Pending review row in Google Sheets and, if approved and the update is verified, emails each selected standby staff member their invitation and then marks the plan’s notification status as Sent only after all Gmail sends succeed.

Setup

1. Create Google Sheets and Gmail credentials in n8n, and add Google Gemini credentials for the LangChain Google Gemini node.
2. Prepare one Google Sheets spreadsheet with four tabs named Workload, StaffSchedule, StandbyStaff, and StaffingPlans (or update the sheet names in the configuration step).
3. In the configuration step, replace YOUR_SPREADSHEET_ID and [email protected], and adjust units_per_person_per_hour and the schedule/timezone if needed.
4. Ensure your sheets include the expected columns (for example: Workload: date, time_slot, workload_units; StaffSchedule: date, staff_id, start_time, end_time, break_start_end; StandbyStaff: date, staff_id, available_start, available_end, email; StaffingPlans: the columns the workflow writes such as targetDate, reviewStatus, proposedAssignments, and notificationStatus).
5. Test with controlled data for a single target date (including at least one shortage) and confirm the manager can approve via Gmail before activating the schedule.