Building Workforce Capacity Planning in Google Workspace

The Business Situation

Ridgeway Project Partners is a fictional 70-person project delivery consultancy. Its workforce includes 56 billable delivery employees, seven project and practice managers, and seven people working in operations, finance, sales support, and People Operations.

The company typically has 20 to 30 active or probable projects. Engagements range from four weeks to nine months, and demand changes when proposals move between stages, start dates shift, or clients request different skills.

Resource planning is shared between the operations director, People Operations manager, project managers, and practice leads. Executive leadership uses the resulting forecast to make decisions about recruitment, contractor extensions, project timing, and sales commitments.

Note: This case study is provided as a representative example of the types of AI integration and digital transformation solutions Intelligex designs and delivers. Actual engagements are tailored to each client’s goals, constraints, existing systems, timeline, and available resources, so the approach, tools, and outcomes may vary.

Before this implementation, project demand forecasts, employee capacity, planned leave, open roles, and contractor commitments were held in different Google Sheets. Changes were requested through Gmail or discussed in meetings. One operations analyst reconciled the information each week.

The company processed approximately 85 planning updates per month. These included new project demand lines, revised dates, leave records, approved open roles, contractor commitments, and weekly forecast confirmations.

The resulting report could show a staffing problem only after someone had reconciled the inputs. It could not reliably answer questions such as:

• Which roles have a capacity shortage during the next 13 weeks?
• How does committed demand differ from probability-weighted demand?
• Does an approved open role close the expected gap?
• Which forecasts have not been reviewed recently?
• Who owns each outstanding correction or planning decision?
• When should leadership consider hiring, contracting, rescheduling, or declining work?

The business needed a controlled planning process, not just another summary spreadsheet. The system had to collect updates, validate them, assign review tasks, calculate scenarios, report capacity risks, and preserve human control over workforce decisions.

The Existing Process

The original workflow followed this sequence:

1. A project manager emailed the operations analyst when a project was won, delayed, extended, or re-scoped.
2. The analyst copied the change into a project forecast spreadsheet.
3. People Operations maintained a separate employee roster and leave spreadsheet.
4. Recruitment maintained an open-roles list, while contractor commitments were tracked in email and a finance worksheet.
5. Each Friday, the analyst copied selected values into a capacity workbook.
6. The analyst grouped demand and supply by role, manually applied project probabilities, and checked for shortages.
7. Questions were emailed back to project managers when dates, probabilities, or role assumptions were missing.
8. A static summary was emailed to the operations director and leadership team.

A major issue was role-name inconsistency. For example, Data Analyst, Data Analytics, and Analyst could be treated as separate capacity pools even when the business intended them to represent the same planning role.

Planned leave was also difficult to reconcile. The planning workbook needed the number of unavailable hours by week, but the leave sheet stored date ranges and total hours. The analyst manually divided leave across weeks.

Forecast probability was applied inconsistently. Some probable projects were included at full demand, while others were omitted. This made comparisons between weekly reports unreliable.

Because follow-ups were sent manually, overdue updates had no systematic reminder or escalation path. Leadership could see a capacity gap without knowing whether it reflected a real shortage or an unconfirmed forecast.

What the New System Needed to Do

The system also needed a manual override. An authorized planner had to be able to correct a record, reassign a task, close a forecast line, or restore a prior value from history without changing the automation code.

Implementation Approaches Considered

Improving the current spreadsheets

This option could standardize role names and add better formulas. It would be quick, but it would not solve unstructured email intake, duplicate updates, review ownership, reminders, or reliable event processing.

Google Workspace automation

This approach retained tools already familiar to employees. Forms provided controlled intake, Sheets provided a visible system of record, Apps Script handled validation and automation, Gmail delivered notifications, and Drive held supporting files.

Its main trade-off was technical ownership. Someone had to maintain the script, monitor trigger failures, and prevent unauthorized structural changes to the workbook.

No-code database and integration platform

A no-code database would provide stronger relational views and user interfaces than Sheets. It was a credible alternative, especially if the process expanded to more departments. For the representative scenario, it added recurring platform costs and duplicated capabilities already available in Google Workspace.

Dedicated resource-management software

A dedicated platform would be appropriate if Ridgeway needed skills matrices, timesheet integration, project financials, scheduling at employee level, or resource-request workflows across many practices. The immediate requirement was role-level capacity planning, so the implementation burden was not yet justified.

Custom application

A custom application offered the most control but required database administration, identity management, hosting, release management, security testing, and ongoing development. That operating model was disproportionate for 70 employees and approximately 85 monthly updates.

The Selected Solution

Ridgeway selected a Google Workspace implementation using the requested tool stack, with Google Drive added for controlled document storage.

The company retained its Google Workspace environment and did not introduce a second operational database. Raw Google Forms response tabs were retained as immutable intake evidence, while controlled master sheets became the planning system of record.

The implementation removed manual copying, weekly leave allocation, probability calculations, basic reminder emails, and repeated report assembly.

It did not automate recruitment authorization, contractor approval, project acceptance, employee assignment, or changes to approved leave. Those decisions remained with the responsible managers.

System Architecture and Data Flow

• Intake: One Google Form with conditional sections for four update types.
• System of record: A controlled Google Sheets workbook containing master and operational sheets.
• Automation layer: A spreadsheet-bound Google Apps Script project with form and time-based triggers.
• Document storage: A restricted Google Drive root folder with one subfolder per record.
• Notifications: Gmail messages sent by the account that owns the installed triggers.
• Reporting: A generated 13-week Capacity_Weekly sheet and filtered dashboard views.
• AI layer: Optional API call using only validated role-level totals, with human review before distribution.

1. A manager submits an update through Google Forms. The linked response sheet receives the original answers and timestamp.
2. An installable spreadsheet form-submit trigger passes the response row to Apps Script.
3. The script creates an event key from the spreadsheet ID, response-tab name, and row number. It checks Processed_Events to prevent the same event from being processed twice.
4. The script validates required fields, allowed statuses, dates, numeric limits, email addresses, and supporting-document requirements.
5. For a new record, a deterministic ID is generated from the event key. For an update, the supplied record ID is located and the prior values are written to Record_History.
6. The relevant Demand, Leave, Open_Roles, or Contractors record is created or updated with a Pending Review status.
7. Any accessible Google Drive upload is copied into the record folder. The folder URL is written back to the master record.
8. A review task is created or refreshed. Gmail notifies the reviewer and confirms receipt to the submitter.
9. An authorized reviewer marks the task Complete and selects Approved, Return for correction, or Rejected. The synchronization routine updates the related record.
10. Only Validated and Active records enter the capacity calculation.
11. The scheduled refresh creates committed, expected, and upside rows for each role and week.
12. The model calculates employee capacity, leave, contractor capacity, open-role capacity, demand, utilization, and gaps.
13. A sustained confirmed-capacity gap generates a planning indicator and appears in the weekly digest. It does not open a job or commit a contractor.
14. Daily routines send reminders, escalate overdue tasks, refresh calculations, and log failures.

If a submission fails validation, its raw form response remains available. The script creates an Exceptions record and sends a correction notification. An administrator can correct the source response or submit a replacement, then retry the response row if appropriate.

Data Structure

The workbook uses separate sheets for distinct record types. Demand, leave, open roles, and contractors have one-to-many relationships with Tasks and Record_History through Record ID.

Workflow Statuses and Ownership

Task status is separate from record status. Tasks use Open, In Progress, Complete, and Cancelled. A completed review task also requires a decision. If a reviewer marks a task Complete without a decision, the synchronization routine returns it to In Progress.

A record can move backward when a reviewer requests correction or when a validated record is changed. A record is rejected only by a human reviewer. The capacity model never rejects a project, candidate, employee request, or hiring proposal.

Step-by-Step Implementation

Step 1: Prepare the Accounts and Permissions

1. Create or identify the Google Workspace account that will own the planning workbook, Forms, Drive folder, and installed Apps Script triggers. A controlled automation account is preferable when company policy permits it.
2. Do not share the account password. Use normal account delegation, group-based access, and administrator-controlled recovery.
3. Create a restricted Google Drive root folder named Workforce Capacity Planning.
4. Give edit access to the operations planning lead and designated technical owner. Give reviewers only the access needed for their task and source sheets.
5. Create a Google Sheets workbook named Workforce Capacity Planning System inside the controlled folder.
6. Create a development copy of the workbook and form. Do not test trigger behavior against production employee data.
7. Identify four operational mailboxes or users: planning lead, People Operations reviewer, hiring lead, and executive escalation owner.
8. Create at least one test user for each role. Confirm that test users can open the form and receive Gmail messages.
9. Confirm that the trigger-owning account can send the expected email volume. Apps Script and Gmail quotas vary by account type and can change. Review the official Apps Script quotas documentation.

The core implementation does not require API credentials. The optional AI enhancement requires an API key stored in Apps Script Properties, never in a sheet cell or source file.

Apps Script installable triggers execute as the account that created them. If that account is suspended or loses access, the automation can fail even when other users still have workbook access.

Step 2: Build the Intake

Create one Google Form named Workforce Planning Update and link it to the planning workbook. Interface labels can change, but the required action is to configure the form to write responses into the workbook.

The first question is a required multiple-choice field named Update type. Use these exact values:

• Project demand
• Planned leave
• Open role
• Contractor capacity

Use conditional sections so the respondent sees only the relevant fields.

The leave section collects Employee ID, Employee email, Leave start date, Leave end date, and Leave hours total. Leave must already be approved in the company’s authoritative HR process. The capacity form is not the leave-approval system.

The open-role section collects Capacity role, Target start date, Weekly capacity hours, Hiring status, and Role owner email. Hiring status uses Proposed, Approved, Open, Filled, or Paused.

The contractor section collects Contractor ID, Capacity role, Contractor start date, Contractor end date, Weekly capacity hours, Contractor status, and Contractor owner email. Contractor status uses Proposed, Confirmed, Active, Ended, or Cancelled.

Set the confirmation message to explain that the update has been received but is pending validation. Do not imply that a hiring, leave, contractor, or project decision has been approved.

Google Forms provides basic required-field and number validation. Apps Script repeats the checks because browser validation does not protect against later edits, configuration mistakes, or imported responses.

Step 3: Create the System of Record

The initialization function in the code section creates these sheets:

• Employees
• Demand
• Leave
• Open_Roles
• Contractors
• Tasks
• Record_History
• Processed_Events
• Exceptions
• Capacity_Weekly
• Dashboard
• AI_Summaries

Do not rename columns after deployment unless the code and all dashboard formulas are updated at the same time. Protect the generated Capacity_Weekly sheet and system ID columns from normal editors.

Load the Employees sheet from the approved HR roster. Use one standardized planning role per employee. The minimum fields are Employee ID, name, email, role, team, manager email, weekly capacity, employment dates, and employment status.

The generated IDs use type-specific prefixes:

• DEM for demand
• LEA for leave
• ROL for open roles
• CON for contractors
• TSK for tasks
• EXC for exceptions
• HIS for record history

For new form records, the identifier is derived from the source event key. Retrying the same response therefore generates the same record ID instead of creating a second record.

Create filtered views for Pending Review, Needs Correction, active demand, open tasks, overdue tasks, unresolved exceptions, and automation warnings. Sheet views are safer than sorting the shared master range directly.

Step 4: Connect the Tools

The spreadsheet-bound script authenticates through Google authorization granted by the trigger owner. No webhook is required for the core workflow.

The script stores external identifiers in the source record wherever relevant. These include the source event key, generated Record ID, Task ID through the Tasks relationship, and Drive folder URL.

Google Forms question titles must be unique because the automation uses them as field names. If a question title changes, update the corresponding lookup in the script before accepting new responses.

Step 5: Build the Core Automation

The submission automation runs in this order:

• Trigger: A new row is added to a linked Google Forms response tab.
• Conditions: The event has not been processed, Update type is allowed, and all type-specific fields pass validation.
• Actions: Generate or locate the record, archive the prior state, copy supporting files, upsert the master record, create a review task, send notifications, and mark the event processed.
• Fields updated: Record ID, validation status, source event key, document link, audit timestamps, automation status, retry count, and error message.
• Notification: A review request goes to the responsible reviewer and a receipt goes to the submitter.
• Exception: Invalid or failed events are written to Exceptions and remain available for controlled retry.

Duplicate records are controlled at two levels. Processed_Events prevents the same response row from being handled twice. A deterministic Record ID prevents a partially completed event from creating a second master record during manual recovery.

When Existing record ID is supplied, the script verifies that the record exists in the selected entity. The prior row is serialized into Record_History before the new values replace it.

Ownership is assigned by update type. Demand and contractor reviews go to the planning lead, leave reviews go to People Operations, and open-role reviews go to the hiring lead.

A new or changed record always returns to Pending Review. This prevents an unreviewed edit from immediately changing leadership reporting.

Users recover a failed response by correcting the underlying issue, such as a missing employee record or inaccessible file, and then running the administrator function for the original response tab and row. Successful events are not retried unless their Processed_Events row is deliberately removed by an authorized technical owner.

Step 6: Add Approvals, Reminders, and Escalations

The review task is a data-validation control, not a substitute for formal HR, project, recruitment, or procurement approval.

• Demand review: The planning lead checks project code, role, dates, hours, probability, and stage.
• Leave review: People Operations confirms that the dates and hours match the authoritative approved leave record.
• Open-role review: The hiring lead confirms the role, planned capacity, target date, and current authorization status.
• Contractor review: The planning lead confirms that only approved or sufficiently reliable capacity is marked Confirmed or Active.

The representative workflow uses one validation reviewer per submission. Parallel approval was not selected because it would add delay without improving the role-level planning calculation. A business with formal workforce authorization can add sequential tasks, but the final hiring or contractor decision should remain outside the capacity model.

Review tasks are due two business days after submission. The routine sends a reminder when the task is due within one day. It escalates overdue tasks at these representative intervals:

• One overdue day: reminder to the owner.
• Three overdue days: reminder to the owner with the planning lead copied.
• Five overdue days: reminder to the owner with the executive escalation owner copied.

If an approver is unavailable, an authorized planner changes Owner Email on the task to the approved delegate. The original due date and escalation history remain visible.

Rejected items are closed and excluded from capacity. Items returned for information enter Needs Correction. The submitter must send an update using the existing Record ID, which creates a fresh review task.

Approval evidence consists of the task decision, reviewer email, completed timestamp, reviewer notes, related Record ID, and the history snapshot created before later changes.

Step 7: Add Documents and File Management

Create this Google Drive hierarchy:

• Workforce Capacity Planning
• Demand – DEM identifier
• Leave – LEA identifier
• Open role – ROL identifier
• Contractor capacity – CON identifier

The script copies each accessible uploaded file into the record folder. The copy is named with the Record ID followed by the original filename. Existing files with the same generated name are not copied again during retry.

The root folder controls inherited access. Do not enable public or organization-wide link sharing unless the content has been approved for that audience.

A corrected submission adds a new file copy rather than silently replacing historical evidence. If strict versioning is required, include a date or version in the source filename and retain the previous copy under the company’s records policy.

The form should not collect medical details or leave reasons. For leave records, the capacity system normally requires only dates, total unavailable hours, and the authoritative employee identifier.

Large files and inaccessible files can fail because of storage, ownership, permission, or quota restrictions. The script logs a document exception and continues with a warning when the planning record can still be reviewed safely.

Step 8: Add Reporting and Operational Views

The generated Capacity_Weekly sheet is the reporting source. Each refresh replaces its contents with current scenario rows.

The Dashboard sheet contains a scenario selector and a QUERY formula that reads Capacity_Weekly. Charts can be added for demand versus confirmed capacity, demand versus planned capacity, gap by role, and utilization by week.

The refresh runs daily and on Monday morning, with an additional manual menu action. Dashboard ownership belongs to the planning lead. The technical owner maintains formulas and script-generated columns.

A representative alert threshold is a confirmed gap of at least 40 hours per week for three consecutive weeks. This threshold must be calibrated to the company’s project size, standard workweek, hiring lead time, and tolerance for contractor use.

Step 9: Add Security and Governance Controls

• Restrict edit access to master and generated sheets.
• Use filtered views rather than distributing copied workbooks.
• Protect Record ID, audit, formula, and generated columns.
• Limit Employees and Leave access to authorized planning and People Operations users.
• Do not store API keys, passwords, or tokens in sheet cells.
• Store optional API credentials in Apps Script Properties.
• Review Drive sharing and Gmail recipients before launch.
• Remove former employees from forms, folders, groups, scripts, and workbook permissions promptly.
• Retain raw responses, history, tasks, and exceptions according to the company’s records schedule.
• Use Google Workspace activity and Apps Script execution logs as supporting operational evidence.
• Back up the workbook before structural changes and maintain a tested development copy.
• Send only aggregated role-level values to an AI service.
• Prohibit names, leave details, performance information, compensation, health information, and client-confidential text from AI prompts.

The system supports operational planning and may contain employee data. The company should assess applicable privacy, employment, contractual, and records obligations before deployment.

Step 10: Deploy and Test

1. Build the form, workbook, and script in a development folder.
2. Run initializeWorkbook from the Apps Script editor.
3. Replace every configuration placeholder.
4. Load a fictional employee roster and planning-role list.
5. Submit sample records for every update type.
6. Run each menu action manually and inspect the resulting sheets and emails.
7. Complete review tasks using each available decision.
8. Test duplicate events, invalid records, inaccessible files, and overdue dates.
9. Compare calculated capacity to a manually prepared test calculation.
10. Ask a pilot group consisting of one project manager, one People Operations reviewer, the planning lead, and the technical owner to complete user acceptance testing.
11. Create installed triggers only after manual tests succeed.
12. Run the pilot for two planning cycles before relying on the dashboard for leadership decisions.
13. Document the form fields, status definitions, role taxonomy, review process, exception recovery, and support contacts.
14. Activate production access in phases and retain the previous weekly workbook as read-only rollback evidence for an agreed period.

The support owner should check Apps Script executions and Exceptions daily during launch. If a material calculation issue appears, disable the time-based triggers, retain form responses, and revert leadership reporting to the last reviewed snapshot while the issue is corrected.

Code and Configuration

The implementation uses a spreadsheet-bound Google Apps Script project. Open the planning workbook, open the Apps Script editor from the workbook’s extensions area, and place the following code in a file named Code.gs.

Replace the spreadsheet ID, Drive folder ID, and notification email placeholders. The code requests access to the workbook, Google Drive, Gmail, and installed triggers.

const CONFIG = Object.freeze({
  SPREADSHEET_ID: "YOUR_SPREADSHEET_ID",
  ROOT_FOLDER_ID: "YOUR_FOLDER_ID",
  PLANNING_LEAD_EMAIL: "YOUR_PLANNING_LEAD_EMAIL",
  PEOPLE_OPS_EMAIL: "YOUR_PEOPLE_OPS_EMAIL",
  HIRING_LEAD_EMAIL: "YOUR_HIRING_LEAD_EMAIL",
  EXECUTIVE_ESCALATION_EMAIL: "YOUR_EXECUTIVE_EMAIL",
  FORECAST_HORIZON_WEEKS: 13,
  REVIEW_DUE_BUSINESS_DAYS: 2,
  STALE_FORECAST_DAYS: 7,
  HIRING_GAP_HOURS: 40,
  HIRING_GAP_CONSECUTIVE_WEEKS: 3,
  MAX_WEEKLY_HOURS: 400
});

const SHEET_HEADERS = Object.freeze({
  Employees: [
    "Employee ID", "Employee Name", "Employee Email", "Role", "Team",
    "Manager Email", "Weekly Capacity Hours", "Employment Start Date",
    "Employment End Date", "Employment Status", "Last HR Sync At", "Notes"
  ],
  Demand: [
    "Record ID", "Project Code", "Project Name", "Role", "Start Date",
    "End Date", "Weekly Hours", "Probability Pct", "Stage",
    "Project Manager Email", "Submitted By Email", "Validation Status",
    "Record Status", "Document Link", "Notes", "Created At", "Updated At",
    "Last Validated At", "Source Event Key", "Automation Status",
    "Last Automation Run", "Retry Count", "Error Message"
  ],
  Leave: [
    "Record ID", "Employee ID", "Employee Email", "Start Date", "End Date",
    "Total Hours", "Submitted By Email", "Validation Status", "Record Status",
    "Document Link", "Notes", "Created At", "Updated At",
    "Last Validated At", "Source Event Key", "Automation Status",
    "Last Automation Run", "Retry Count", "Error Message"
  ],
  Open_Roles: [
    "Record ID", "Role", "Target Start Date", "Weekly Capacity Hours",
    "Hiring Status", "Owner Email", "Submitted By Email", "Validation Status",
    "Record Status", "Document Link", "Notes", "Created At", "Updated At",
    "Last Validated At", "Source Event Key", "Automation Status",
    "Last Automation Run", "Retry Count", "Error Message"
  ],
  Contractors: [
    "Record ID", "Contractor ID", "Role", "Start Date", "End Date",
    "Weekly Capacity Hours", "Contractor Status", "Owner Email",
    "Submitted By Email", "Validation Status", "Record Status",
    "Document Link", "Notes", "Created At", "Updated At",
    "Last Validated At", "Source Event Key", "Automation Status",
    "Last Automation Run", "Retry Count", "Error Message"
  ],
  Tasks: [
    "Task ID", "Related Record ID", "Related Type", "Task Type",
    "Owner Email", "Status", "Decision", "Due Date", "Created At",
    "Completed At", "Escalation Level", "Last Reminder At", "Details",
    "Reviewer Notes"
  ],
  Record_History: [
    "History ID", "Record ID", "Record Type", "Change Type",
    "Snapshot JSON", "Changed At", "Source Event Key", "Changed By"
  ],
  Processed_Events: [
    "Event Key", "Processed At", "Record ID", "Outcome"
  ],
  Exceptions: [
    "Exception ID", "Source Event Key", "Related Record ID",
    "Exception Type", "Status", "Owner Email", "Error Message",
    "Payload", "Created At", "Resolved At", "Retry Count"
  ],
  Capacity_Weekly: [
    "Generated At", "Scenario", "Week Start", "Role",
    "Employee Capacity Hours", "Leave Hours", "Net Employee Capacity Hours",
    "Contractor Capacity Hours", "Confirmed Capacity Hours",
    "Open Role Capacity Hours", "Planned Capacity Hours", "Demand Hours",
    "Confirmed Gap Hours", "Planned Gap Hours", "Utilization Pct",
    "Hiring Trigger", "Data Quality Flags"
  ],
  AI_Summaries: [
    "Summary ID", "Generated At", "Scenario", "Period Start", "Period End",
    "Model", "Input Row Count", "Summary JSON", "Review Status",
    "Reviewer Notes"
  ]
});

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("Capacity Planning")
    .addItem("Initialize workbook", "initializeWorkbook")
    .addItem("Refresh capacity model", "refreshCapacityModel")
    .addItem("Synchronize completed reviews", "syncCompletedReviewTasks")
    .addItem("Run reminders now", "sendTaskReminders")
    .addItem("Run weekly planning cycle", "weeklyPlanningCycle")
    .addSeparator()
    .addItem("Install production triggers", "installTriggers")
    .addToUi();
}

function getSpreadsheet_() {
  if (CONFIG.SPREADSHEET_ID === "YOUR_SPREADSHEET_ID") {
    throw new Error("Replace CONFIG.SPREADSHEET_ID before running the script.");
  }
  return SpreadsheetApp.openById(CONFIG.SPREADSHEET_ID);
}

function initializeWorkbook() {
  const ss = getSpreadsheet_();

  Object.keys(SHEET_HEADERS).forEach(function(name) {
    ensureSheet_(ss, name, SHEET_HEADERS[name]);
  });

  ensureSheet_(ss, "Dashboard", []);

  applyValidation_("Tasks", "Status",
    ["Open", "In Progress", "Complete", "Cancelled"]);
  applyValidation_("Tasks", "Decision",
    ["Approved", "Return for correction", "Rejected"]);
  applyValidation_("Demand", "Validation Status",
    ["Pending Review", "Validated", "Needs Correction", "Rejected"]);
  applyValidation_("Leave", "Validation Status",
    ["Pending Review", "Validated", "Needs Correction", "Rejected"]);
  applyValidation_("Open_Roles", "Validation Status",
    ["Pending Review", "Validated", "Needs Correction", "Rejected"]);
  applyValidation_("Contractors", "Validation Status",
    ["Pending Review", "Validated", "Needs Correction", "Rejected"]);

  setupDashboard_();
  SpreadsheetApp.flush();
  console.log("Workbook initialization completed.");
}

function ensureSheet_(ss, name, requiredHeaders) {
  let sheet = ss.getSheetByName(name);
  if (!sheet) {
    sheet = ss.insertSheet(name);
  }

  if (!requiredHeaders.length) {
    return sheet;
  }

  const existingHeaders = sheet.getLastColumn() > 0
    ? sheet.getRange(1, 1, 1, sheet.getLastColumn()).getDisplayValues()[0]
    : [];

  if (existingHeaders.filter(String).length === 0) {
    sheet.getRange(1, 1, 1, requiredHeaders.length).setValues([requiredHeaders]);
  } else {
    const missing = requiredHeaders.filter(function(header) {
      return existingHeaders.indexOf(header) === -1;
    });

    if (missing.length) {
      sheet.getRange(1, existingHeaders.length + 1, 1, missing.length)
        .setValues([missing]);
    }
  }

  sheet.setFrozenRows(1);
  sheet.getRange(1, 1, 1, sheet.getLastColumn()).setFontWeight("bold");
  return sheet;
}

function applyValidation_(sheetName, header, allowedValues) {
  const sheet = getSpreadsheet_().getSheetByName(sheetName);
  const column = getHeaderIndex_(sheet, header) + 1;
  const rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(allowedValues, true)
    .setAllowInvalid(false)
    .build();

  sheet.getRange(2, column, Math.max(sheet.getMaxRows() - 1, 1), 1)
    .setDataValidation(rule);
}

function setupDashboard_() {
  const sheet = getSpreadsheet_().getSheetByName("Dashboard");

  sheet.getRange("A1").setValue("Scenario");
  if (!sheet.getRange("B1").getValue()) {
    sheet.getRange("B1").setValue("Expected");
  }

  const scenarioRule = SpreadsheetApp.newDataValidation()
    .requireValueInList(["Committed", "Expected", "Upside"], true)
    .setAllowInvalid(false)
    .build();

  sheet.getRange("B1").setDataValidation(scenarioRule);
  sheet.getRange("A3").setFormula(
    `=QUERY(Capacity_Weekly!A:Q,"select C,D,L,I,M,O,P,Q where B = '"&B1&"' order by C,D",1)`
  );
}

function installTriggers() {
  const handlers = [
    "onPlanningFormSubmit",
    "dailyOperations",
    "weeklyPlanningCycle"
  ];

  ScriptApp.getProjectTriggers().forEach(function(trigger) {
    if (handlers.indexOf(trigger.getHandlerFunction()) !== -1) {
      ScriptApp.deleteTrigger(trigger);
    }
  });

  ScriptApp.newTrigger("onPlanningFormSubmit")
    .forSpreadsheet(CONFIG.SPREADSHEET_ID)
    .onFormSubmit()
    .create();

  ScriptApp.newTrigger("dailyOperations")
    .timeBased()
    .everyDays(1)
    .atHour(8)
    .create();

  ScriptApp.newTrigger("weeklyPlanningCycle")
    .timeBased()
    .onWeekDay(ScriptApp.WeekDay.MONDAY)
    .atHour(6)
    .create();

  console.log("Production triggers installed.");
}

function onPlanningFormSubmit(e) {
  if (!e || !e.range) {
    throw new Error("This function must run from a spreadsheet form-submit trigger.");
  }
  processResponseRange_(e.range);
}

function retryResponseRow(sheetName, rowNumber) {
  const sheet = getSpreadsheet_().getSheetByName(sheetName);
  if (!sheet) {
    throw new Error("Response sheet not found: " + sheetName);
  }
  if (!Number.isInteger(rowNumber) || rowNumber < 2) {
    throw new Error("rowNumber must be an integer greater than or equal to 2.");
  }
  return processResponseRange_(sheet.getRange(rowNumber, 1, 1, sheet.getLastColumn()));
}

function processResponseRange_(range) {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(30000)) {
    throw new Error("Could not obtain the processing lock within 30 seconds.");
  }

  let context;

  try {
    context = buildEventContext_(range);

    if (isProcessedEvent_(context.eventKey)) {
      console.log("Duplicate event skipped: " + context.eventKey);
      return { status: "duplicate", eventKey: context.eventKey };
    }

    const result = processSubmission_(context);
    appendObject_("Processed_Events", {
      "Event Key": context.eventKey,
      "Processed At": new Date(),
      "Record ID": result.recordId,
      "Outcome": "Completed"
    });

    console.log(JSON.stringify(result));
    return result;
  } catch (error) {
    const message = error && error.message ? error.message : String(error);

    logException_({
      eventKey: context ? context.eventKey : "UNAVAILABLE",
      recordId: context && context.recordId ? context.recordId : "",
      type: "Submission Processing",
      owner: CONFIG.PLANNING_LEAD_EMAIL,
      message: message,
      payload: context ? context.values : {}
    });

    if (context && context.submitterEmail) {
      safeSendEmail_(
        context.submitterEmail,
        "Workforce planning update requires attention",
        "The update could not be processed.\n\nReason: " + message +
          "\n\nReference: " + context.eventKey,
        {},
        context.eventKey
      );
    }

    console.error(message);
    throw error;
  } finally {
    lock.releaseLock();
  }
}

function buildEventContext_(range) {
  const sheet = range.getSheet();
  const row = range.getRow();
  const lastColumn = sheet.getLastColumn();
  const headers = sheet.getRange(1, 1, 1, lastColumn).getDisplayValues()[0];
  const rawValues = sheet.getRange(row, 1, 1, lastColumn).getValues()[0];
  const values = {};

  headers.forEach(function(header, index) {
    if (header) {
      values[header] = rawValues[index];
    }
  });

  return {
    sheetName: sheet.getName(),
    rowNumber: row,
    eventKey: [
      sheet.getParent().getId(),
      sheet.getName(),
      row
    ].join("|"),
    values: values,
    recordId: "",
    submitterEmail: ""
  };
}

function processSubmission_(context) {
  const updateType = requireText_(
    answer_(context.values, ["Update type"]),
    "Update type"
  );

  const typeConfig = getTypeConfig_(updateType);
  const submittedBy = requireEmail_(
    answer_(context.values, ["Submitted by email", "Email Address"]),
    "Submitted by email"
  );

  context.submitterEmail = submittedBy;

  const suppliedId = text_(
    answer_(context.values, ["Existing record ID"])
  ).toUpperCase();

  if (suppliedId && !findRecord_(typeConfig.sheet, suppliedId)) {
    throw new Error(
      "Existing record ID was not found in " + typeConfig.sheet + ": " + suppliedId
    );
  }

  const recordId = suppliedId ||
    stableRecordId_(typeConfig.prefix, context.eventKey);

  context.recordId = recordId;

  const existing = findRecord_(typeConfig.sheet, recordId);
  const now = new Date();

  if (existing) {
    appendObject_("Record_History", {
      "History ID": randomId_("HIS"),
      "Record ID": recordId,
      "Record Type": updateType,
      "Change Type": "Pre-update snapshot",
      "Snapshot JSON": truncate_(JSON.stringify(existing), 30000),
      "Changed At": now,
      "Source Event Key": context.eventKey,
      "Changed By": submittedBy
    });
  }

  const evidenceRequired = sameText_(
    answer_(context.values, ["Evidence required"]), "Yes"
  );
  const evidenceLinks = text_(
    answer_(context.values, ["Supporting file links"])
  );

  if (evidenceRequired && !evidenceLinks) {
    throw new Error("Supporting file links are required for this update.");
  }

  let documentLink = existing ? existing["Document Link"] : "";
  let archiveWarning = "";

  if (evidenceLinks) {
    try {
      documentLink = archiveEvidence_(
        recordId, updateType, evidenceLinks
      ) || documentLink;
    } catch (error) {
      archiveWarning = error.message;
      logException_({
        eventKey: context.eventKey,
        recordId: recordId,
        type: "Document Archive",
        owner: CONFIG.PLANNING_LEAD_EMAIL,
        message: error.message,
        payload: { links: evidenceLinks }
      });
    }
  }

  const common = {
    "Record ID": recordId,
    "Submitted By Email": submittedBy,
    "Validation Status": "Pending Review",
    "Record Status": getRecordStatus_(
      answer_(context.values, ["Record action"])
    ),
    "Document Link": documentLink,
    "Notes": text_(answer_(context.values, ["Notes"])),
    "Created At": existing && existing["Created At"]
      ? existing["Created At"] : now,
    "Updated At": now,
    "Last Validated At": existing
      ? existing["Last Validated At"] : "",
    "Source Event Key": context.eventKey,
    "Automation Status": archiveWarning
      ? "Completed with warning" : "Completed",
    "Last Automation Run": now,
    "Retry Count": existing
      ? Number(existing["Retry Count"] || 0) : 0,
    "Error Message": archiveWarning
  };

  let record;

  if (typeConfig.sheet === "Demand") {
    record = buildDemandRecord_(context.values, common);
  } else if (typeConfig.sheet === "Leave") {
    record = buildLeaveRecord_(context.values, common);
  } else if (typeConfig.sheet === "Open_Roles") {
    record = buildOpenRoleRecord_(context.values, common);
  } else {
    record = buildContractorRecord_(context.values, common);
  }

  upsertObject_(typeConfig.sheet, "Record ID", record);

  const dueDate = addBusinessDays_(new Date(), CONFIG.REVIEW_DUE_BUSINESS_DAYS);
  const taskId = upsertOpenTask_({
    "Related Record ID": recordId,
    "Related Type": updateType,
    "Task Type": "Submission review",
    "Owner Email": typeConfig.reviewOwner,
    "Due Date": dueDate,
    "Details": "Review and validate " + updateType + " record " + recordId + "."
  });

  safeSendEmail_(
    typeConfig.reviewOwner,
    "Workforce planning review required: " + recordId,
    "A " + updateType + " update is awaiting review.\n\n" +
      "Record ID: " + recordId + "\n" +
      "Task ID: " + taskId + "\n" +
      "Due date: " + formatDate_(dueDate) + "\n" +
      "Submitted by: " + submittedBy,
    {},
    context.eventKey
  );

  safeSendEmail_(
    submittedBy,
    "Workforce planning update received: " + recordId,
    "Your update was recorded and is pending review.\n\n" +
      "Record ID: " + recordId + "\n" +
      "Update type: " + updateType + "\n" +
      "This message does not represent approval of a workforce decision.",
    {},
    context.eventKey
  );

  return {
    status: "completed",
    recordId: recordId,
    taskId: taskId,
    destinationSheet: typeConfig.sheet
  };
}

function getTypeConfig_(updateType) {
  const key = text_(updateType).toLowerCase();
  const mapping = {
    "project demand": {
      sheet: "Demand",
      prefix: "DEM",
      reviewOwner: CONFIG.PLANNING_LEAD_EMAIL
    },
    "planned leave": {
      sheet: "Leave",
      prefix: "LEA",
      reviewOwner: CONFIG.PEOPLE_OPS_EMAIL
    },
    "open role": {
      sheet: "Open_Roles",
      prefix: "ROL",
      reviewOwner: CONFIG.HIRING_LEAD_EMAIL
    },
    "contractor capacity": {
      sheet: "Contractors",
      prefix: "CON",
      reviewOwner: CONFIG.PLANNING_LEAD_EMAIL
    }
  };

  if (!mapping[key]) {
    throw new Error("Unsupported Update type: " + updateType);
  }
  return mapping[key];
}

function buildDemandRecord_(values, common) {
  const start = requireDate_(
    answer_(values, ["Forecast start date"]), "Forecast start date"
  );
  const end = requireDate_(
    answer_(values, ["Forecast end date"]), "Forecast end date"
  );

  validateDateOrder_(start, end);

  const stage = requireAllowed_(
    answer_(values, ["Project stage"]),
    ["In Delivery", "Signed", "Verbal", "Proposal", "On Hold"],
    "Project stage"
  );

  return Object.assign({}, common, {
    "Project Code": requireText_(
      answer_(values, ["Project code"]), "Project code"
    ),
    "Project Name": requireText_(
      answer_(values, ["Project name"]), "Project name"
    ),
    "Role": requireText_(
      answer_(values, ["Demand role"]), "Demand role"
    ),
    "Start Date": start,
    "End Date": end,
    "Weekly Hours": requireNumber_(
      answer_(values, ["Weekly demand hours"]),
      "Weekly demand hours", 0.01, CONFIG.MAX_WEEKLY_HOURS
    ),
    "Probability Pct": requireNumber_(
      answer_(values, ["Probability percent"]),
      "Probability percent", 0, 100
    ),
    "Stage": stage,
    "Project Manager Email": requireEmail_(
      answer_(values, ["Project manager email"]),
      "Project manager email"
    )
  });
}

function buildLeaveRecord_(values, common) {
  const start = requireDate_(
    answer_(values, ["Leave start date"]), "Leave start date"
  );
  const end = requireDate_(
    answer_(values, ["Leave end date"]), "Leave end date"
  );

  validateDateOrder_(start, end);

  return Object.assign({}, common, {
    "Employee ID": requireText_(
      answer_(values, ["Employee ID"]), "Employee ID"
    ),
    "Employee Email": requireEmail_(
      answer_(values, ["Employee email"]), "Employee email"
    ),
    "Start Date": start,
    "End Date": end,
    "Total Hours": requireNumber_(
      answer_(values, ["Leave hours total"]),
      "Leave hours total", 0.01, CONFIG.MAX_WEEKLY_HOURS
    )
  });
}

function buildOpenRoleRecord_(values, common) {
  return Object.assign({}, common, {
    "Role": requireText_(
      answer_(values, ["Capacity role"]), "Capacity role"
    ),
    "Target Start Date": requireDate_(
      answer_(values, ["Target start date"]), "Target start date"
    ),
    "Weekly Capacity Hours": requireNumber_(
      answer_(values, ["Weekly capacity hours"]),
      "Weekly capacity hours", 0.01, CONFIG.MAX_WEEKLY_HOURS
    ),
    "Hiring Status": requireAllowed_(
      answer_(values, ["Hiring status"]),
      ["Proposed", "Approved", "Open", "Filled", "Paused"],
      "Hiring status"
    ),
    "Owner Email": requireEmail_(
      answer_(values, ["Role owner email"]), "Role owner email"
    )
  });
}

function buildContractorRecord_(values, common) {
  const start = requireDate_(
    answer_(values, ["Contractor start date"]), "Contractor start date"
  );
  const end = requireDate_(
    answer_(values, ["Contractor end date"]), "Contractor end date"
  );

  validateDateOrder_(start, end);

  return Object.assign({}, common, {
    "Contractor ID": requireText_(
      answer_(values, ["Contractor ID"]), "Contractor ID"
    ),
    "Role": requireText_(
      answer_(values, ["Capacity role"]), "Capacity role"
    ),
    "Start Date": start,
    "End Date": end,
    "Weekly Capacity Hours": requireNumber_(
      answer_(values, ["Weekly capacity hours"]),
      "Weekly capacity hours", 0.01, CONFIG.MAX_WEEKLY_HOURS
    ),
    "Contractor Status": requireAllowed_(
      answer_(values, ["Contractor status"]),
      ["Proposed", "Confirmed", "Active", "Ended", "Cancelled"],
      "Contractor status"
    ),
    "Owner Email": requireEmail_(
      answer_(values, ["Contractor owner email"]),
      "Contractor owner email"
    )
  });
}

function syncCompletedReviewTasks() {
  const tasks = getRows_("Tasks");
  let processed = 0;

  tasks.forEach(function(task) {
    if (!sameText_(task["Status"], "Complete") || task["Completed At"]) {
      return;
    }

    const decision = text_(task["Decision"]);
    if (!decision) {
      updateRowObject_("Tasks", task._row, {
        "Status": "In Progress",
        "Details": text_(task["Details"]) +
          " A decision is required before completion."
      });
      return;
    }

    const relatedSheet = relatedSheetName_(task["Related Type"]);
    const record = findRecord_(relatedSheet, task["Related Record ID"]);

    if (!record) {
      logException_({
        eventKey: "TASK|" + task["Task ID"],
        recordId: task["Related Record ID"],
        type: "Review Synchronization",
        owner: CONFIG.PLANNING_LEAD_EMAIL,
        message: "Related record was not found.",
        payload: task
      });
      return;
    }

    const now = new Date();
    const taskType = text_(task["Task Type"]);
    const updates = {
      "Updated At": now,
      "Last Automation Run": now,
      "Error Message": ""
    };

    if (decision === "Approved") {
      updates["Validation Status"] = "Validated";
      updates["Last Validated At"] = now;
      updates["Automation Status"] = "Completed";
    } else if (decision === "Return for correction") {
      updates["Validation Status"] = "Needs Correction";
      updates["Automation Status"] = "Completed";
    } else if (decision === "Rejected") {
      updates["Validation Status"] = "Rejected";
      updates["Record Status"] = "Closed";
      updates["Automation Status"] = "Completed";
    } else {
      throw new Error("Unsupported review decision: " + decision);
    }

    if (taskType === "Forecast attestation" && decision === "Approved") {
      updates["Validation Status"] = "Validated";
      updates["Last Validated At"] = now;
    }

    updateRecordById_(relatedSheet, task["Related Record ID"], updates);
    updateRowObject_("Tasks", task._row, {
      "Completed At": now
    });

    const recipient = record["Submitted By Email"];
    if (recipient) {
      safeSendEmail_(
        recipient,
        "Workforce planning review completed: " + task["Related Record ID"],
        "Decision: " + decision + "\n" +
          "Record ID: " + task["Related Record ID"] + "\n" +
          "Reviewer notes: " + text_(task["Reviewer Notes"]),
        {},
        "TASK|" + task["Task ID"]
      );
    }

    processed++;
  });

  console.log("Completed review tasks synchronized: " + processed);
  return processed;
}

function dailyOperations() {
  syncCompletedReviewTasks();
  sendTaskReminders();
  refreshCapacityModel();
}

function weeklyPlanningCycle() {
  syncCompletedReviewTasks();
  generateStaleForecastTasks_();
  refreshCapacityModel();
  sendPlanningDigest_();
}

function sendTaskReminders() {
  const tasks = getRows_("Tasks");
  const today = dayOnly_(new Date());

  tasks.forEach(function(task) {
    if (["Complete", "Cancelled"].indexOf(text_(task["Status"])) !== -1) {
      return;
    }

    const due = toDateOrNull_(task["Due Date"]);
    if (!due) {
      return;
    }

    const lastReminder = toDateOrNull_(task["Last Reminder At"]);
    if (lastReminder && sameCalendarDay_(lastReminder, today)) {
      return;
    }

    const daysUntilDue = Math.ceil(
      (dayOnly_(due).getTime() - today.getTime()) / 86400000
    );

    if (daysUntilDue > 1) {
      return;
    }

    const overdueDays = Math.max(0, -daysUntilDue);
    let level = 0;
    let cc = "";

    if (overdueDays >= 5) {
      level = 3;
      cc = CONFIG.EXECUTIVE_ESCALATION_EMAIL;
    } else if (overdueDays >= 3) {
      level = 2;
      cc = CONFIG.PLANNING_LEAD_EMAIL;
    } else if (overdueDays >= 1) {
      level = 1;
    }

    const subject = overdueDays
      ? "Overdue workforce planning task: " + task["Task ID"]
      : "Workforce planning task due soon: " + task["Task ID"];

    safeSendEmail_(
      task["Owner Email"],
      subject,
      "Task ID: " + task["Task ID"] + "\n" +
        "Related record: " + task["Related Record ID"] + "\n" +
        "Due date: " + formatDate_(due) + "\n" +
        "Overdue days: " + overdueDays + "\n" +
        "Details: " + text_(task["Details"]),
      cc ? { cc: cc } : {},
      "TASK|" + task["Task ID"]
    );

    updateRowObject_("Tasks", task._row, {
      "Escalation Level": level,
      "Last Reminder At": new Date()
    });
  });
}

function generateStaleForecastTasks_() {
  const now = new Date();

  getRows_("Demand").forEach(function(record) {
    if (!sameText_(record["Validation Status"], "Validated") ||
        !sameText_(record["Record Status"], "Active")) {
      return;
    }

    const checkedAt = toDateOrNull_(
      record["Last Validated At"] || record["Updated At"]
    );

    if (!checkedAt ||
        daysBetween_(checkedAt, now) >= CONFIG.STALE_FORECAST_DAYS) {
      upsertOpenTask_({
        "Related Record ID": record["Record ID"],
        "Related Type": "Project demand",
        "Task Type": "Forecast attestation",
        "Owner Email": record["Project Manager Email"],
        "Due Date": addBusinessDays_(
          now, CONFIG.REVIEW_DUE_BUSINESS_DAYS
        ),
        "Details": "Confirm or update the current project forecast."
      });
    }
  });
}

function refreshCapacityModel() {
  const employees = getRows_("Employees");
  const demand = getRows_("Demand");
  const leave = getRows_("Leave");
  const roles = getRows_("Open_Roles");
  const contractors = getRows_("Contractors");
  const roleLabels = {};

  function registerRole(value) {
    const role = text_(value);
    if (role && !roleLabels[role.toLowerCase()]) {
      roleLabels[role.toLowerCase()] = role;
    }
  }

  employees.forEach(function(row) { registerRole(row["Role"]); });
  demand.forEach(function(row) { registerRole(row["Role"]); });
  roles.forEach(function(row) { registerRole(row["Role"]); });
  contractors.forEach(function(row) { registerRole(row["Role"]); });

  const employeeMap = {};
  employees.forEach(function(row) {
    employeeMap[text_(row["Employee ID"]).toLowerCase()] = row;
  });

  const weeks = [];
  let week = mondayOfWeek_(new Date());

  for (let i = 0; i < CONFIG.FORECAST_HORIZON_WEEKS; i++) {
    weeks.push(new Date(week));
    week = addDays_(week, 7);
  }

  const scenarios = ["Committed", "Expected", "Upside"];
  const generatedAt = new Date();
  const output = [];

  scenarios.forEach(function(scenario) {
    weeks.forEach(function(weekStart) {
      Object.keys(roleLabels).sort().forEach(function(roleKey) {
        const role = roleLabels[roleKey];
        const weekEnd = addDays_(weekStart, 6);

        const employeeCapacity = employees.reduce(function(total, row) {
          if (!sameText_(row["Role"], role) ||
              !sameText_(row["Employment Status"], "Active") ||
              !overlaps_(
                toDateOrNull_(row["Employment Start Date"]),
                toDateOrNull_(row["Employment End Date"]),
                weekStart,
                weekEnd
              )) {
            return total;
          }

          return total + prorateWeeklyHours_(
            Number(row["Weekly Capacity Hours"] || 0),
            toDateOrNull_(row["Employment Start Date"]),
            toDateOrNull_(row["Employment End Date"]),
            weekStart
          );
        }, 0);

        const leaveHours = leave.reduce(function(total, row) {
          if (!sameText_(row["Validation Status"], "Validated") ||
              !sameText_(row["Record Status"], "Active")) {
            return total;
          }

          const employee = employeeMap[
            text_(row["Employee ID"]).toLowerCase()
          ];

          if (!employee || !sameText_(employee["Role"], role)) {
            return total;
          }

          return total + allocateRangeHours_(
            Number(row["Total Hours"] || 0),
            toDateOrNull_(row["Start Date"]),
            toDateOrNull_(row["End Date"]),
            weekStart
          );
        }, 0);

        const netEmployee = Math.max(0, employeeCapacity - leaveHours);

        const contractorCapacity = contractors.reduce(function(total, row) {
          const statusIncluded = ["Confirmed", "Active"].indexOf(
            text_(row["Contractor Status"])
          ) !== -1;

          if (!statusIncluded ||
              !sameText_(row["Validation Status"], "Validated") ||
              !sameText_(row["Record Status"], "Active") ||
              !sameText_(row["Role"], role)) {
            return total;
          }

          return total + prorateWeeklyHours_(
            Number(row["Weekly Capacity Hours"] || 0),
            toDateOrNull_(row["Start Date"]),
            toDateOrNull_(row["End Date"]),
            weekStart
          );
        }, 0);

        const openRoleCapacity = roles.reduce(function(total, row) {
          const statusIncluded = ["Approved", "Open"].indexOf(
            text_(row["Hiring Status"])
          ) !== -1;

          if (!statusIncluded ||
              !sameText_(row["Validation Status"], "Validated") ||
              !sameText_(row["Record Status"], "Active") ||
              !sameText_(row["Role"], role)) {
            return total;
          }

          return total + prorateWeeklyHours_(
            Number(row["Weekly Capacity Hours"] || 0),
            toDateOrNull_(row["Target Start Date"]),
            null,
            weekStart
          );
        }, 0);

        let staleForecast = false;
        let pendingExcluded = false;

        const demandHours = demand.reduce(function(total, row) {
          if (!sameText_(row["Role"], role) ||
              !overlaps_(
                toDateOrNull_(row["Start Date"]),
                toDateOrNull_(row["End Date"]),
                weekStart,
                weekEnd
              )) {
            return total;
          }

          if (!sameText_(row["Validation Status"], "Validated")) {
            pendingExcluded = true;
            return total;
          }

          if (!sameText_(row["Record Status"], "Active")) {
            return total;
          }

          const lastValidated = toDateOrNull_(row["Last Validated At"]);
          if (!lastValidated ||
              daysBetween_(lastValidated, generatedAt) >=
                CONFIG.STALE_FORECAST_DAYS) {
            staleForecast = true;
          }

          const baseHours = prorateWeeklyHours_(
            Number(row["Weekly Hours"] || 0),
            toDateOrNull_(row["Start Date"]),
            toDateOrNull_(row["End Date"]),
            weekStart
          );

          return total + baseHours * demandWeight_(row, scenario);
        }, 0);

        const confirmedCapacity = netEmployee + contractorCapacity;
        const plannedCapacity = confirmedCapacity + openRoleCapacity;
        const confirmedGap = confirmedCapacity - demandHours;
        const plannedGap = plannedCapacity - demandHours;
        const flags = [];

        if (pendingExcluded) {
          flags.push("Pending records excluded");
        }
        if (staleForecast) {
          flags.push("Stale forecast included");
        }
        if (employeeCapacity === 0 && demandHours > 0) {
          flags.push("Demand has no active employee capacity");
        }

        output.push({
          "Generated At": generatedAt,
          "Scenario": scenario,
          "Week Start": weekStart,
          "Role": role,
          "Employee Capacity Hours": round2_(employeeCapacity),
          "Leave Hours": round2_(leaveHours),
          "Net Employee Capacity Hours": round2_(netEmployee),
          "Contractor Capacity Hours": round2_(contractorCapacity),
          "Confirmed Capacity Hours": round2_(confirmedCapacity),
          "Open Role Capacity Hours": round2_(openRoleCapacity),
          "Planned Capacity Hours": round2_(plannedCapacity),
          "Demand Hours": round2_(demandHours),
          "Confirmed Gap Hours": round2_(confirmedGap),
          "Planned Gap Hours": round2_(plannedGap),
          "Utilization Pct": confirmedCapacity > 0
            ? demandHours / confirmedCapacity : "",
          "Hiring Trigger": "",
          "Data Quality Flags": flags.join("; ")
        });
      });
    });
  });

  applyHiringTriggers_(output);
  replaceSheetData_("Capacity_Weekly", output);

  console.log("Capacity rows generated: " + output.length);
  return output.length;
}

function demandWeight_(row, scenario) {
  const stage = text_(row["Stage"]);

  if (stage === "On Hold") {
    return 0;
  }
  if (scenario === "Committed") {
    return ["In Delivery", "Signed"].indexOf(stage) !== -1 ? 1 : 0;
  }
  if (scenario === "Expected") {
    return Math.max(0, Math.min(100,
      Number(row["Probability Pct"] || 0))) / 100;
  }
  return 1;
}

function applyHiringTriggers_(rows) {
  const groups = {};

  rows.forEach(function(row) {
    const key = row["Scenario"] + "|" + row["Role"];
    groups[key] = groups[key] || [];
    groups[key].push(row);
  });

  Object.keys(groups).forEach(function(key) {
    const group = groups[key].sort(function(a, b) {
      return a["Week Start"] - b["Week Start"];
    });

    let run = [];

    function markRun() {
      if (run.length < CONFIG.HIRING_GAP_CONSECUTIVE_WEEKS) {
        run = [];
        return;
      }

      run.forEach(function(row) {
        row["Hiring Trigger"] = row["Planned Gap Hours"] >= 0
          ? "Open role already planned"
          : "Review hire, contractor, timing, or scope";
      });
      run = [];
    }

    group.forEach(function(row) {
      if (row["Confirmed Gap Hours"] <= -CONFIG.HIRING_GAP_HOURS) {
        run.push(row);
      } else {
        markRun();
      }
    });

    markRun();
  });
}

function sendPlanningDigest_() {
  const rows = getRows_("Capacity_Weekly").filter(function(row) {
    return sameText_(row["Scenario"], "Expected") &&
      (text_(row["Hiring Trigger"]) ||
       Number(row["Confirmed Gap Hours"] || 0) <
         -CONFIG.HIRING_GAP_HOURS);
  });

  const lines = rows.slice(0, 25).map(function(row) {
    return formatDate_(toDateOrNull_(row["Week Start"])) + " | " +
      row["Role"] + " | demand " + row["Demand Hours"] +
      " | confirmed gap " + row["Confirmed Gap Hours"] +
      " | planned gap " + row["Planned Gap Hours"] +
      " | " + text_(row["Hiring Trigger"]);
  });

  const body = lines.length
    ? "Expected-scenario capacity items:\n\n" + lines.join("\n")
    : "No expected-scenario items crossed the configured alert threshold.";

  safeSendEmail_(
    CONFIG.PLANNING_LEAD_EMAIL,
    "Weekly workforce capacity planning digest",
    body + "\n\nReview the workbook before making workforce decisions.",
    { cc: CONFIG.EXECUTIVE_ESCALATION_EMAIL },
    "WEEKLY_DIGEST|" + formatDate_(new Date())
  );
}

function archiveEvidence_(recordId, updateType, links) {
  if (CONFIG.ROOT_FOLDER_ID === "YOUR_FOLDER_ID") {
    throw new Error("Replace CONFIG.ROOT_FOLDER_ID before archiving files.");
  }

  const ids = extractDriveIds_(links);
  if (!ids.length) {
    throw new Error("No accessible Google Drive file ID was found.");
  }

  const root = DriveApp.getFolderById(CONFIG.ROOT_FOLDER_ID);
  const folderName = updateType + " - " + recordId;
  const matchingFolders = root.getFoldersByName(folderName);
  const folder = matchingFolders.hasNext()
    ? matchingFolders.next()
    : root.createFolder(folderName);

  ids.forEach(function(fileId) {
    const source = DriveApp.getFileById(fileId);
    const copyName = recordId + " - " + source.getName();

    if (!folder.getFilesByName(copyName).hasNext()) {
      source.makeCopy(copyName, folder);
    }
  });

  return folder.getUrl();
}

function extractDriveIds_(value) {
  const matches = text_(value).match(/[-\w]{25,}/g) || [];
  return matches.filter(function(id, index) {
    return matches.indexOf(id) === index;
  });
}

function upsertOpenTask_(data) {
  const tasks = getRows_("Tasks");
  const existing = tasks.find(function(task) {
    return sameText_(task["Related Record ID"], data["Related Record ID"]) &&
      sameText_(task["Task Type"], data["Task Type"]) &&
      ["Open", "In Progress"].indexOf(text_(task["Status"])) !== -1;
  });

  if (existing) {
    updateRowObject_("Tasks", existing._row, {
      "Owner Email": data["Owner Email"],
      "Due Date": data["Due Date"],
      "Details": data["Details"]
    });
    return existing["Task ID"];
  }

  const taskId = randomId_("TSK");
  appendObject_("Tasks", {
    "Task ID": taskId,
    "Related Record ID": data["Related Record ID"],
    "Related Type": data["Related Type"],
    "Task Type": data["Task Type"],
    "Owner Email": requireEmail_(data["Owner Email"], "Task owner email"),
    "Status": "Open",
    "Decision": "",
    "Due Date": data["Due Date"],
    "Created At": new Date(),
    "Completed At": "",
    "Escalation Level": 0,
    "Last Reminder At": "",
    "Details": data["Details"],
    "Reviewer Notes": ""
  });

  return taskId;
}

function isProcessedEvent_(eventKey) {
  return getRows_("Processed_Events").some(function(row) {
    return row["Event Key"] === eventKey;
  });
}

function findRecord_(sheetName, recordId) {
  return getRows_(sheetName).find(function(row) {
    return sameText_(row["Record ID"], recordId);
  }) || null;
}

function updateRecordById_(sheetName, recordId, updates) {
  const row = findRecord_(sheetName, recordId);
  if (!row) {
    throw new Error("Record not found: " + sheetName + " / " + recordId);
  }
  updateRowObject_(sheetName, row._row, updates);
}

function upsertObject_(sheetName, keyHeader, object) {
  const sheet = getSpreadsheet_().getSheetByName(sheetName);
  const headers = getHeaders_(sheet);
  const rows = getRows_(sheetName);
  const keyValue = object[keyHeader];
  const existing = rows.find(function(row) {
    return sameText_(row[keyHeader], keyValue);
  });

  const values = headers.map(function(header) {
    return Object.prototype.hasOwnProperty.call(object, header)
      ? object[header]
      : existing ? existing[header] : "";
  });

  if (existing) {
    sheet.getRange(existing._row, 1, 1, headers.length).setValues([values]);
  } else {
    sheet.appendRow(values);
  }
}

function appendObject_(sheetName, object) {
  const sheet = getSpreadsheet_().getSheetByName(sheetName);
  const headers = getHeaders_(sheet);
  sheet.appendRow(headers.map(function(header) {
    return Object.prototype.hasOwnProperty.call(object, header)
      ? object[header] : "";
  }));
}

function updateRowObject_(sheetName, rowNumber, updates) {
  const sheet = getSpreadsheet_().getSheetByName(sheetName);
  const headers = getHeaders_(sheet);
  const current = sheet.getRange(rowNumber, 1, 1, headers.length).getValues()[0];

  headers.forEach(function(header, index) {
    if (Object.prototype.hasOwnProperty.call(updates, header)) {
      current[index] = updates[header];
    }
  });

  sheet.getRange(rowNumber, 1, 1, headers.length).setValues([current]);
}

function replaceSheetData_(sheetName, objects) {
  const sheet = getSpreadsheet_().getSheetByName(sheetName);
  const headers = SHEET_HEADERS[sheetName];

  sheet.clearContents();
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  sheet.setFrozenRows(1);

  if (objects.length) {
    const values = objects.map(function(object) {
      return headers.map(function(header) {
        return object[header] === undefined ? "" : object[header];
      });
    });
    sheet.getRange(2, 1, values.length, headers.length).setValues(values);
  }

  const weekColumn = headers.indexOf("Week Start") + 1;
  const utilizationColumn = headers.indexOf("Utilization Pct") + 1;

  if (objects.length && weekColumn > 0) {
    sheet.getRange(2, weekColumn, objects.length, 1)
      .setNumberFormat("yyyy-mm-dd");
  }
  if (objects.length && utilizationColumn > 0) {
    sheet.getRange(2, utilizationColumn, objects.length, 1)
      .setNumberFormat("0.0%");
  }
}

function getRows_(sheetName) {
  const sheet = getSpreadsheet_().getSheetByName(sheetName);
  if (!sheet || sheet.getLastRow() < 2) {
    return [];
  }

  const headers = getHeaders_(sheet);
  const values = sheet.getRange(
    2, 1, sheet.getLastRow() - 1, headers.length
  ).getValues();

  return values.map(function(row, index) {
    const object = { _row: index + 2 };
    headers.forEach(function(header, column) {
      object[header] = row[column];
    });
    return object;
  }).filter(function(object) {
    return headers.some(function(header) {
      return object[header] !== "";
    });
  });
}

function getHeaders_(sheet) {
  if (!sheet || sheet.getLastColumn() === 0) {
    return [];
  }
  return sheet.getRange(1, 1, 1, sheet.getLastColumn())
    .getDisplayValues()[0];
}

function getHeaderIndex_(sheet, header) {
  const index = getHeaders_(sheet).indexOf(header);
  if (index === -1) {
    throw new Error("Missing header in " + sheet.getName() + ": " + header);
  }
  return index;
}

function logException_(data) {
  const ss = getSpreadsheet_();
  ensureSheet_(ss, "Exceptions", SHEET_HEADERS.Exceptions);

  const existing = getRows_("Exceptions").find(function(row) {
    return row["Source Event Key"] === data.eventKey &&
      row["Exception Type"] === data.type &&
      row["Status"] === "Open";
  });

  if (existing) {
    updateRowObject_("Exceptions", existing._row, {
      "Related Record ID": data.recordId || existing["Related Record ID"],
      "Owner Email": data.owner || existing["Owner Email"],
      "Error Message": truncate_(data.message, 5000),
      "Payload": truncate_(JSON.stringify(data.payload || {}), 30000),
      "Retry Count": Number(existing["Retry Count"] || 0) + 1
    });
    return existing["Exception ID"];
  }

  const exceptionId = randomId_("EXC");
  appendObject_("Exceptions", {
    "Exception ID": exceptionId,
    "Source Event Key": data.eventKey || "",
    "Related Record ID": data.recordId || "",
    "Exception Type": data.type || "Unknown",
    "Status": "Open",
    "Owner Email": data.owner || CONFIG.PLANNING_LEAD_EMAIL,
    "Error Message": truncate_(data.message || "", 5000),
    "Payload": truncate_(JSON.stringify(data.payload || {}), 30000),
    "Created At": new Date(),
    "Resolved At": "",
    "Retry Count": 0
  });
  return exceptionId;
}

function safeSendEmail_(to, subject, body, options, eventKey) {
  try {
    requireEmail_(to, "Notification recipient");
    GmailApp.sendEmail(to, subject, body, options || {});
    return true;
  } catch (error) {
    logException_({
      eventKey: eventKey || "EMAIL|" + new Date().toISOString(),
      recordId: "",
      type: "Notification Failure",
      owner: CONFIG.PLANNING_LEAD_EMAIL,
      message: error.message,
      payload: { to: to, subject: subject }
    });
    console.error("Email failed: " + error.message);
    return false;
  }
}

function relatedSheetName_(relatedType) {
  return getTypeConfig_(relatedType).sheet;
}

function getRecordStatus_(answer) {
  return sameText_(answer, "Close record") ? "Closed" : "Active";
}

function answer_(values, names) {
  for (let i = 0; i < names.length; i++) {
    if (Object.prototype.hasOwnProperty.call(values, names[i]) &&
        values[names[i]] !== "") {
      return values[names[i]];
    }
  }
  return "";
}

function requireText_(value, label) {
  const result = text_(value);
  if (!result) {
    throw new Error(label + " is required.");
  }
  return result;
}

function requireEmail_(value, label) {
  const email = requireText_(value, label).toLowerCase();
  if (!/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(email)) {
    throw new Error(label + " is not a valid email address.");
  }
  return email;
}

function requireNumber_(value, label, minimum, maximum) {
  const number = Number(value);
  if (!Number.isFinite(number)) {
    throw new Error(label + " must be a number.");
  }
  if (number < minimum || number > maximum) {
    throw new Error(
      label + " must be between " + minimum + " and " + maximum + "."
    );
  }
  return number;
}

function requireDate_(value, label) {
  const date = toDateOrNull_(value);
  if (!date) {
    throw new Error(label + " must be a valid date.");
  }
  return date;
}

function requireAllowed_(value, allowed, label) {
  const result = requireText_(value, label);
  if (allowed.indexOf(result) === -1) {
    throw new Error(label + " must be one of: " + allowed.join(", "));
  }
  return result;
}

function validateDateOrder_(start, end) {
  if (dayOnly_(end).getTime() < dayOnly_(start).getTime()) {
    throw new Error("End date cannot be before start date.");
  }
}

function text_(value) {
  if (value === null || value === undefined) {
    return "";
  }
  return String(value).trim();
}

function sameText_(left, right) {
  return text_(left).toLowerCase() === text_(right).toLowerCase();
}

function stableRecordId_(prefix, seed) {
  const digest = Utilities.computeDigest(
    Utilities.DigestAlgorithm.SHA_256,
    seed,
    Utilities.Charset.UTF_8
  );

  const hex = digest.map(function(byte) {
    return ("0" + ((byte & 255).toString(16))).slice(-2);
  }).join("").slice(0, 10).toUpperCase();

  return prefix + "-" + hex;
}

function randomId_(prefix) {
  return prefix + "-" + Utilities.getUuid()
    .replace(/-/g, "")
    .slice(0, 10)
    .toUpperCase();
}

function addBusinessDays_(date, count) {
  const result = dayOnly_(date);
  let added = 0;

  while (added < count) {
    result.setDate(result.getDate() + 1);
    const day = result.getDay();
    if (day !== 0 && day !== 6) {
      added++;
    }
  }
  return result;
}

function mondayOfWeek_(date) {
  const result = dayOnly_(date);
  const day = result.getDay();
  const difference = day === 0 ? -6 : 1 - day;
  result.setDate(result.getDate() + difference);
  return result;
}

function addDays_(date, days) {
  const result = new Date(date);
  result.setDate(result.getDate() + days);
  return result;
}

function dayOnly_(date) {
  return new Date(date.getFullYear(), date.getMonth(), date.getDate());
}

function toDateOrNull_(value) {
  if (!value) {
    return null;
  }
  const date = value instanceof Date ? new Date(value) : new Date(value);
  return Number.isNaN(date.getTime()) ? null : date;
}

function overlaps_(start, end, rangeStart, rangeEnd) {
  const effectiveStart = start || new Date(1900, 0, 1);
  const effectiveEnd = end || new Date(2999, 11, 31);
  return dayOnly_(effectiveStart) <= dayOnly_(rangeEnd) &&
    dayOnly_(effectiveEnd) >= dayOnly_(rangeStart);
}

function prorateWeeklyHours_(weeklyHours, start, end, weekStart) {
  const weekEnd = addDays_(weekStart, 6);
  if (!overlaps_(start, end, weekStart, weekEnd)) {
    return 0;
  }

  const overlapStart = start && dayOnly_(start) > dayOnly_(weekStart)
    ? dayOnly_(start) : dayOnly_(weekStart);
  const overlapEnd = end && dayOnly_(end) < dayOnly_(weekEnd)
    ? dayOnly_(end) : dayOnly_(weekEnd);

  return weeklyHours * businessDaysInclusive_(overlapStart, overlapEnd) / 5;
}

function allocateRangeHours_(totalHours, start, end, weekStart) {
  if (!start || !end) {
    return 0;
  }

  const weekEnd = addDays_(weekStart, 6);
  if (!overlaps_(start, end, weekStart, weekEnd)) {
    return 0;
  }

  const totalDays = businessDaysInclusive_(start, end);
  if (totalDays === 0) {
    return 0;
  }

  const overlapStart = dayOnly_(start) > dayOnly_(weekStart)
    ? dayOnly_(start) : dayOnly_(weekStart);
  const overlapEnd = dayOnly_(end) < dayOnly_(weekEnd)
    ? dayOnly_(end) : dayOnly_(weekEnd);

  return totalHours *
    businessDaysInclusive_(overlapStart, overlapEnd) / totalDays;
}

function businessDaysInclusive_(start, end) {
  let count = 0;
  let cursor = dayOnly_(start);
  const finalDay = dayOnly_(end);

  while (cursor <= finalDay) {
    const day = cursor.getDay();
    if (day !== 0 && day !== 6) {
      count++;
    }
    cursor = addDays_(cursor, 1);
  }

  return count;
}

function daysBetween_(start, end) {
  return Math.floor(
    (dayOnly_(end).getTime() - dayOnly_(start).getTime()) / 86400000
  );
}

function sameCalendarDay_(left, right) {
  return dayOnly_(left).getTime() === dayOnly_(right).getTime();
}

function formatDate_(date) {
  if (!date) {
    return "";
  }
  return Utilities.formatDate(
    date,
    getSpreadsheet_().getSpreadsheetTimeZone(),
    "yyyy-MM-dd"
  );
}

function round2_(number) {
  return Math.round((Number(number) + Number.EPSILON) * 100) / 100;
}

function truncate_(value, maximumLength) {
  const result = text_(value);
  return result.length > maximumLength
    ? result.slice(0, maximumLength)
    : result;
}

After pasting the code:

1. Replace all configuration placeholders.
2. Save the project.
3. Run initializeWorkbook manually.
4. Review the requested permissions and authorize the script using the intended automation owner.
5. Confirm that all sheets and headers were created.
6. Submit one test form response.
7. Run retryResponseRow("Form Responses 1", 2) only if the trigger was not yet installed and row 2 contains the test response. Replace the sheet name and row as required.
8. Inspect Demand, Tasks, Processed_Events, Exceptions, and Record_History.
9. Complete the task with an Approved decision and run syncCompletedReviewTasks.
10. Run refreshCapacityModel and inspect Capacity_Weekly.
11. Run installTriggers after testing succeeds.

The form-submit trigger is installable rather than a simple trigger because the process requires authorized Gmail and Drive actions. See the official installable trigger documentation.

No web application deployment is required. The script runs from installed spreadsheet and time-based triggers.

Use the Apps Script Executions view to inspect trigger runs, duration, logs, authorization errors, and stack traces. Common problems include changed form question titles, missing sheet headers, inaccessible Drive uploads, suspended trigger owners, invalid email addresses, and Gmail or Apps Script quota limits.

Duplicate execution is prevented by the event key and deterministic record ID. Failed events are recorded in Exceptions. The manual retry function processes the same response row after the underlying problem has been corrected.

Failure Handling and Operational Reliability

The Exceptions sheet acts as a practical manual-review queue. It is not a full message queue, but it serves the same recovery purpose at this volume. Open exceptions should show an owner, event key, error message, source payload, created date, and retry count.

Staff reconcile the system weekly by comparing:

• Form response count against Processed_Events count
• Pending Review records against open submission-review tasks
• Validated active records against Capacity_Weekly source assumptions
• Open exceptions against unresolved support activity
• Weekly digest items against dashboard alert rows

The deterministic event key provides idempotency, meaning that repeating the same operation produces the same master identifier instead of an additional record. This is important when a trigger stops after creating the record but before completing every downstream action.

A Complete Example

A Ridgeway project manager submits a Project demand update for project PX-241, an analytics implementation engagement.

• Project code: PX-241
• Project name: Analytics Operating Model
• Demand role: Data Analyst
• Forecast start date: 2026-08-03
• Forecast end date: 2026-09-25
• Weekly demand hours: 120
• Probability percent: 80
• Project stage: Proposal
• Project manager email: a fictional test address
• Record action: Save as active

The response is written to row 18 of the linked response sheet. The script constructs an event key from the workbook ID, response-sheet name, and row 18. It generates the representative identifier DEM-1A2B3C4D5E.

The script validates the date order, hours, probability, stage, and email address. It creates a Demand record with Pending Review status and generates task TSK-72F6A304B1 for the planning lead, due in two business days.

Gmail sends a receipt to the project manager and a review request to the planning lead. The supporting scope document is copied into the Drive folder named Project demand - DEM-1A2B3C4D5E.

The planning lead checks the source document and confirms that the role, dates, and probability are reasonable. The task is marked Complete with an Approved decision. The synchronization routine changes the demand record to Validated and records the validation timestamp.

In the Committed scenario, the proposal contributes zero hours because it is not Signed or In Delivery. In the Expected scenario, it contributes 96 hours per full week because 120 hours are multiplied by 80 percent. In the Upside scenario, it contributes 120 hours.

For one August week, validated Data Analyst capacity is 160 employee hours. Planned leave removes 24 hours, leaving 136 confirmed hours. Expected demand from PX-241 is 96 hours, so the confirmed gap is positive 40 hours.

A second validated project later adds 120 probability-weighted hours for the same role. Expected demand becomes 216 hours. Confirmed capacity remains 136 hours, producing a negative 80-hour gap.

An approved open Data Analyst role contributes 40 planned hours from its target start date. Planned capacity becomes 176 hours, leaving a negative 40-hour planned gap.

If the confirmed gap remains at or below negative 40 hours for at least three consecutive weeks, the rule sets the indicator to Review hire, contractor, timing, or scope. Gmail includes it in the weekly planning digest.

Leadership then reviews project timing, recruitment progress, contractor availability, and commercial priorities. The system supplies validated evidence but does not choose or authorize the response.

Implementation Cost

All amounts below are representative planning assumptions in US dollars. They are not verified client costs. Google Workspace licensing, local labor rates, security requirements, and implementation complexity will vary.

The optional professional implementation amount would normally replace part of the internal technical-build assumption rather than simply being added to it.

Estimated Time and Cost Savings

The representative calculation uses these assumptions:

• 85 planning updates per month
• 22 minutes of current handling and allocated reconciliation time per update
• 6 minutes of new human handling per update
• 10 percent exception rate
• 12 minutes of manual review per exception
• 4 monthly maintenance hours
• $55 loaded hourly labor cost
• $0 incremental recurring core software cost
• $8,120 one-time implementation cost

Current monthly labour hours: Monthly volume × current minutes per record ÷ 60

New monthly labour hours: Monthly volume × new minutes per record ÷ 60, plus exception handling and maintenance

Monthly hours recovered: Current monthly labour hours minus new monthly labour hours

Estimated monthly labour value: Monthly hours recovered × loaded hourly labour cost

Net estimated monthly value: Monthly labour value minus recurring tool costs

Estimated payback period: One-time implementation cost ÷ net estimated monthly value

Recovered time does not automatically reduce payroll. It can provide additional planning capacity, quicker turnaround, reduced overtime, less dependence on one analyst, or the ability to handle more project changes without adding administrative work.

Non-financial benefits include clearer ownership, fewer follow-up emails, more consistent probability treatment, better audit evidence, earlier visibility of sustained gaps, and a common vocabulary for workforce decisions.

Readers should replace the update volume, handling time, exception rate, maintenance time, loaded labor rate, licensing assumptions, implementation cost, and hiring threshold with their own measured figures.

Adding AI to the Automation

AI should be added only after the core data, statuses, calculations, and review process operate reliably.

The following functions do not require AI:

• Checking required fields
• Validating dates and percentages
• Matching exact employee and role identifiers
• Calculating capacity, leave, demand, and gaps
• Applying scenario weights
• Identifying three consecutive shortage weeks
• Sending scheduled reminders
• Controlling permissions and approvals

These tasks are better handled by deterministic rules because the expected result is precise and testable.

Potential AI uses include summarizing capacity risks, grouping similar role-level issues, drafting questions for project managers, identifying themes in reviewer notes, and creating a plain-language leadership briefing from validated data.

The core automation creates reliable data and workflow control. AI contributes specifically by reducing reading and drafting time when converting many validated weekly rows into a concise narrative.

The Recommended AI Enhancement

The recommended enhancement generates a draft six-week capacity-risk summary from the Expected scenario.

• Trigger: Manual execution after the weekly capacity refresh.
• AI input: Role, week, demand, confirmed capacity, planned capacity, gaps, hiring indicator, and data-quality flags.
• Excluded input: Employee names, leave reasons, client narrative, compensation, health data, performance data, and applicant information.
• Output: Structured JSON containing an executive summary, up to five risks, assumptions, and data-quality notes.
• Human review: The output is saved with Pending Review status and emailed as a draft.
• Low confidence: The model must cite data-quality limitations and recommend verification rather than invent missing values.
• Failure behavior: Keep the rule-based dashboard available and log the API error.

Use this reusable system instruction:

You are assisting with workforce capacity planning.

Use only the validated role-level data supplied by the user.
Do not infer employee performance, health, protected characteristics, or individual suitability.
Do not make hiring, termination, contractor, project-acceptance, or employee-assignment decisions.
Do not invent missing figures.

Identify material capacity risks, cite the role and week supporting each risk, distinguish confirmed capacity from planned open-role capacity, and recommend a human review action.

If data-quality flags are present, explain how they limit the conclusion.
Return only JSON matching the supplied schema.

The user prompt is assembled in this form:

Prepare a draft workforce capacity risk summary for the next six weeks.

Scenario: Expected
Planning rules:
- Confirmed capacity includes active employees net of leave plus confirmed contractors.
- Planned capacity also includes approved or open recruitment roles.
- A hiring trigger is an investigation indicator, not an authorization.
- Negative gap means demand exceeds capacity.

Validated weekly data:
{{CAPACITY_ROWS_JSON}}

An expected response has this structure:

{
  "period": {
    "start": "2026-08-03",
    "end": "2026-09-13"
  },
  "executive_summary": "Expected demand exceeds confirmed capacity for two roles. One gap is partly covered by an approved open role.",
  "risks": [
    {
      "role": "Data Analyst",
      "week_start": "2026-08-17",
      "severity": "high",
      "issue": "Expected demand exceeds confirmed and planned capacity.",
      "evidence": "Demand is 216 hours, confirmed capacity is 136 hours, and planned capacity is 176 hours.",
      "recommended_human_action": "Review recruitment timing, contractor availability, project timing, and scope."
    }
  ],
  "assumptions": [
    "Probability-weighted demand is appropriate for the Expected scenario."
  ],
  "data_quality_notes": [
    "One overlapping demand record has not been validated and is excluded."
  ]
}

Add the following code to a second Apps Script file named AI.gs in the same project. It uses the helpers and configuration from Code.gs.

const AI_CONFIG = Object.freeze({
  ENDPOINT: "https://api.openai.com/v1/responses",
  SUMMARY_WEEKS: 6,
  MAX_ATTEMPTS: 3
});

function generateAiRiskSummary() {
  const properties = PropertiesService.getScriptProperties();
  const apiKey = properties.getProperty("OPENAI_API_KEY");
  const model = properties.getProperty("OPENAI_MODEL");

  if (!apiKey) {
    throw new Error("Set OPENAI_API_KEY in Apps Script Properties.");
  }
  if (!model) {
    throw new Error("Set OPENAI_MODEL to a supported model name.");
  }

  const periodStart = mondayOfWeek_(new Date());
  const periodEnd = addDays_(
    periodStart, AI_CONFIG.SUMMARY_WEEKS * 7 - 1
  );

  const sourceRows = getRows_("Capacity_Weekly").filter(function(row) {
    const week = toDateOrNull_(row["Week Start"]);
    return sameText_(row["Scenario"], "Expected") &&
      week &&
      dayOnly_(week) >= dayOnly_(periodStart) &&
      dayOnly_(week) <= dayOnly_(periodEnd);
  });

  if (!sourceRows.length) {
    throw new Error(
      "No Expected-scenario capacity rows exist for the summary period."
    );
  }

  const aiRows = sourceRows.map(function(row) {
    return {
      week_start: formatDate_(toDateOrNull_(row["Week Start"])),
      role: text_(row["Role"]),
      demand_hours: finiteNumber_(row["Demand Hours"], "Demand Hours"),
      confirmed_capacity_hours: finiteNumber_(
        row["Confirmed Capacity Hours"], "Confirmed Capacity Hours"
      ),
      planned_capacity_hours: finiteNumber_(
        row["Planned Capacity Hours"], "Planned Capacity Hours"
      ),
      confirmed_gap_hours: finiteNumber_(
        row["Confirmed Gap Hours"], "Confirmed Gap Hours"
      ),
      planned_gap_hours: finiteNumber_(
        row["Planned Gap Hours"], "Planned Gap Hours"
      ),
      hiring_trigger: text_(row["Hiring Trigger"]),
      data_quality_flags: text_(row["Data Quality Flags"])
    };
  });

  const systemInstruction = [
    "You are assisting with workforce capacity planning.",
    "Use only the validated role-level data supplied by the user.",
    "Do not infer employee performance, health, protected characteristics,",
    "or individual suitability.",
    "Do not make hiring, termination, contractor, project-acceptance,",
    "or employee-assignment decisions.",
    "Do not invent missing figures.",
    "Identify material capacity risks and cite the role and week supporting",
    "each risk.",
    "Distinguish confirmed capacity from planned open-role capacity.",
    "Recommend only a human review action.",
    "If data-quality flags are present, explain how they limit conclusions.",
    "Return only JSON matching the supplied schema."
  ].join(" ");

  const userPrompt = [
    "Prepare a draft workforce capacity risk summary for the next six weeks.",
    "Scenario: Expected.",
    "Confirmed capacity includes active employees net of leave plus",
    "confirmed contractors.",
    "Planned capacity also includes approved or open recruitment roles.",
    "A hiring trigger is an investigation indicator, not an authorization.",
    "Negative gap means demand exceeds capacity.",
    "Validated weekly data:",
    JSON.stringify(aiRows)
  ].join("\n");

  const schema = {
    type: "object",
    additionalProperties: false,
    required: [
      "period",
      "executive_summary",
      "risks",
      "assumptions",
      "data_quality_notes"
    ],
    properties: {
      period: {
        type: "object",
        additionalProperties: false,
        required: ["start", "end"],
        properties: {
          start: { type: "string" },
          end: { type: "string" }
        }
      },
      executive_summary: { type: "string" },
      risks: {
        type: "array",
        maxItems: 5,
        items: {
          type: "object",
          additionalProperties: false,
          required: [
            "role",
            "week_start",
            "severity",
            "issue",
            "evidence",
            "recommended_human_action"
          ],
          properties: {
            role: { type: "string" },
            week_start: { type: "string" },
            severity: {
              type: "string",
              enum: ["low", "medium", "high"]
            },
            issue: { type: "string" },
            evidence: { type: "string" },
            recommended_human_action: { type: "string" }
          }
        }
      },
      assumptions: {
        type: "array",
        items: { type: "string" }
      },
      data_quality_notes: {
        type: "array",
        items: { type: "string" }
      }
    }
  };

  const requestBody = {
    model: model,
    input: [
      {
        role: "system",
        content: [
          { type: "input_text", text: systemInstruction }
        ]
      },
      {
        role: "user",
        content: [
          { type: "input_text", text: userPrompt }
        ]
      }
    ],
    text: {
      format: {
        type: "json_schema",
        name: "capacity_risk_summary",
        strict: true,
        schema: schema
      }
    }
  };

  const responseJson = fetchAiWithRetry_(
    AI_CONFIG.ENDPOINT,
    apiKey,
    requestBody
  );

  const outputText = extractResponseText_(responseJson);
  let summary;

  try {
    summary = JSON.parse(outputText);
  } catch (error) {
    throw new Error("AI response was not valid JSON: " + error.message);
  }

  validateAiSummary_(summary);

  const summaryId = randomId_("AIS");
  appendObject_("AI_Summaries", {
    "Summary ID": summaryId,
    "Generated At": new Date(),
    "Scenario": "Expected",
    "Period Start": periodStart,
    "Period End": periodEnd,
    "Model": model,
    "Input Row Count": aiRows.length,
    "Summary JSON": JSON.stringify(summary),
    "Review Status": "Pending Review",
    "Reviewer Notes": ""
  });

  safeSendEmail_(
    CONFIG.PLANNING_LEAD_EMAIL,
    "AI draft capacity summary requires human review",
    "Summary ID: " + summaryId + "\n\n" +
      JSON.stringify(summary, null, 2) +
      "\n\nValidate every figure against the rule-based dashboard.",
    {},
    "AI|" + summaryId
  );

  return {
    summaryId: summaryId,
    inputRows: aiRows.length,
    summary: summary
  };
}

function fetchAiWithRetry_(endpoint, apiKey, body) {
  let lastError;

  for (let attempt = 1; attempt <= AI_CONFIG.MAX_ATTEMPTS; attempt++) {
    try {
      const response = UrlFetchApp.fetch(endpoint, {
        method: "post",
        contentType: "application/json",
        headers: {
          Authorization: "Bearer " + apiKey
        },
        payload: JSON.stringify(body),
        muteHttpExceptions: true
      });

      const status = response.getResponseCode();
      const text = response.getContentText();

      if (status >= 200 && status < 300) {
        return JSON.parse(text);
      }

      if (status === 429 || status >= 500) {
        lastError = new Error(
          "AI API temporary error " + status + ": " + text
        );
        Utilities.sleep(
          Math.pow(2, attempt - 1) * 1000 +
          Math.floor(Math.random() * 500)
        );
        continue;
      }

      throw new Error("AI API error " + status + ": " + text);
    } catch (error) {
      lastError = error;
      if (attempt < AI_CONFIG.MAX_ATTEMPTS) {
        Utilities.sleep(Math.pow(2, attempt - 1) * 1000);
      }
    }
  }

  logException_({
    eventKey: "AI|" + new Date().toISOString(),
    recordId: "",
    type: "AI Service Failure",
    owner: CONFIG.PLANNING_LEAD_EMAIL,
    message: lastError ? lastError.message : "Unknown AI service failure",
    payload: { endpoint: endpoint }
  });

  throw lastError || new Error("AI service failed after retries.");
}

function extractResponseText_(response) {
  const output = response && response.output;
  if (!Array.isArray(output)) {
    throw new Error("AI response did not contain an output array.");
  }

  for (let i = 0; i < output.length; i++) {
    const content = output[i].content;
    if (!Array.isArray(content)) {
      continue;
    }

    for (let j = 0; j < content.length; j++) {
      if (content[j].type === "output_text" && content[j].text) {
        return content[j].text;
      }
    }
  }

  throw new Error("AI response did not contain output_text.");
}

function validateAiSummary_(summary) {
  if (!summary || typeof summary !== "object") {
    throw new Error("AI summary must be an object.");
  }

  if (!summary.period ||
      typeof summary.executive_summary !== "string" ||
      !Array.isArray(summary.risks) ||
      !Array.isArray(summary.assumptions) ||
      !Array.isArray(summary.data_quality_notes)) {
    throw new Error("AI summary is missing required fields.");
  }

  if (summary.risks.length > 5) {
    throw new Error("AI summary returned more than five risks.");
  }

  summary.risks.forEach(function(risk) {
    if (["low", "medium", "high"].indexOf(risk.severity) === -1) {
      throw new Error("AI summary contains an invalid severity.");
    }

    [
      "role",
      "week_start",
      "issue",
      "evidence",
      "recommended_human_action"
    ].forEach(function(field) {
      if (typeof risk[field] !== "string" || !risk[field].trim()) {
        throw new Error("AI risk is missing field: " + field);
      }
    });
  });
}

function finiteNumber_(value, label) {
  const number = Number(value);
  if (!Number.isFinite(number)) {
    throw new Error(label + " contains a non-numeric value.");
  }
  return number;
}

Store the credentials in Apps Script Properties using the keys OPENAI_API_KEY and OPENAI_MODEL. Use an organization-approved model and review the current official Responses API documentation before deployment.

The request uses bearer-token authentication, an HTTP POST request, JSON content, and structured-output validation. Pagination is not relevant because the automation sends one summary request rather than listing remote records.

The retry function retries HTTP 429 responses and server errors with exponential delay. Other client errors fail immediately. The raw API key is never written to Sheets or logs.

Benefits of the AI Enhancement

• Reduces the time needed to read many role-week rows.
• Produces a consistent first draft for the weekly leadership briefing.
• Distinguishes confirmed capacity from capacity that depends on open roles.
• Draws attention to data-quality flags that could weaken a conclusion.
• Creates structured risk records that can be sampled and compared over time.
• Helps leaders focus their review on material role and timing combinations.

These are AI-specific benefits. Record validation, scenario calculations, reminders, audit history, and hiring-trigger rules are benefits of the core automation and remain available if the AI service is disabled.

What Remains Rule-Based or Human-Controlled

Estimating the Additional Value of AI

The representative AI comparison uses four weekly summary records per month.

Core automation summary effort is 4 × 40 minutes, or 160 minutes per month.

AI-assisted effort is 4 × 15 minutes for review, plus 4 × 25 percent × 10 minutes for correction, plus 4 × 5 percent × 40 minutes for manual fallback. This equals approximately 78 minutes.

The estimated additional capacity is 82 minutes, or approximately 1.37 hours per month. At an assumed leadership labor value of $90 per hour, that is approximately $123 of monthly labor capacity before the $10 AI allowance, producing a representative net value of approximately $113.

This estimate does not claim that AI eliminates errors or review. Its value depends on the number of weekly rows, briefing complexity, prompt quality, correction rate, model cost, and the discipline of human reviewers.

Testing Checklist

Use fictional sample data before processing real employee, project, or client information.

Ongoing Maintenance

The planning lead is the primary business owner. The People Operations manager is the backup business owner. A named technical owner maintains Apps Script, forms, formulas, triggers, and exception recovery.

Credential rotation applies to the optional AI key and any future API credentials. Google account authorization should be reviewed when trigger ownership, permissions, or administrators change.

Changes must be tested in the development copy. After deployment, update the operating guide, data dictionary, role mapping, change record, and recovery instructions.

When to Move to Dedicated Software

The Google Workspace implementation can remain appropriate while the workflow is role-level, the data volume is moderate, and a small planning team owns the process.

Ridgeway should evaluate dedicated resource-management, professional-services automation, workforce-planning, or custom database software when several of these conditions appear:

• Employee-level scheduling replaces role-level capacity planning.
• Transaction volume or workbook size causes slow refreshes or frequent quota problems.
• Multiple business units require complex and isolated permissions.
• Timesheets, project financials, skills, rates, and utilization must be integrated.
• Formal regulatory or customer audit controls exceed spreadsheet governance.
• Exception rates or script-maintenance hours increase materially.
• Many locations require localized calendars, currencies, or labor rules.
• Planners require advanced optimization, mobile access, or offline operation.
• Managers need a customer-facing or employee self-service portal.
• APIs must synchronize with HR, CRM, finance, recruitment, and project systems at high frequency.
• Vendor support commitments and service-level agreements become mandatory.
• The security risk of consolidated employee and project data exceeds the current control model.

Migration should be based on measured limitations, not solely on company growth. If the process remains understandable, maintainable, secure, and reliable, the existing implementation may continue to be the proportionate option.

Implementation Checklist

• Confirm the planning horizon, scenarios, role taxonomy, and alert thresholds.
• Document business requirements and decisions that must remain human-controlled.
• Confirm Google Forms, Sheets, Apps Script, Gmail, and Drive as the selected tools.
• Assign the business owner, backup owner, technical owner, and executive sponsor.
• Create production and development accounts, folders, forms, and workbooks.
• Configure least-privilege permissions and restricted sharing.
• Build the common and conditional form fields with exact question names.
• Create the master, task, history, exception, event, reporting, and AI sheets.
• Load and validate the employee roster and planning-role list.
• Configure stable IDs, statuses, allowed values, and date rules.
• Map every form field to its destination field.
• Configure the form-submit trigger and deterministic duplicate prevention.
• Configure record upserts and prior-state history.
• Configure review tasks, decisions, ownership, and due dates.
• Configure reminders, escalation intervals, delegation, and rejection handling.
• Create the Drive folder structure and file naming rules.
• Configure Gmail receipts, review notices, reminders, escalations, and digests.
• Build the 13-week committed, expected, and upside calculations.
• Create operational views for pending, overdue, exception, failure, and shortage records.
• Protect generated fields, sensitive data, scripts, and credentials.
• Replace all code placeholders and authorize the intended trigger owner.
• Run normal, failure, duplicate, retry, approval, reporting, and security tests.
• Pilot with representative users before production activation.
• Document one-time cost, recurring cost, maintenance, and savings assumptions.
• Add AI only after the core automation is stable and validated.
• Restrict AI input to approved aggregated data and require human review.
• Assign daily, weekly, monthly, quarterly, and annual maintenance tasks.
• Define measurable criteria for migration to dedicated software.