Resource Planning Across Projects, Skills, and Availability

The Business Situation

Cedar Vale Industrial Projects is a fictional 86-person business that designs and manufactures packaging and material-handling equipment. Its workforce includes mechanical engineers, electrical engineers, programmers, fabricators, installers, and project managers.

The company typically manages 24 active or scheduled customer projects, seven project managers, 58 employees who can be allocated to project work, and approximately 145 current or future named allocations. About 220 requirement, allocation, or availability changes are processed each month.

The operations planning manager is responsible for balancing project demand against employee capacity. Project managers define required roles and dates, department supervisors nominate employees, and an HR coordinator records approved leave. The operations director reviews material conflicts and approves exceptions.

The business uses Google Workspace, but its planning process is divided among project spreadsheets, a shared leave calendar, email, and informal supervisor notes. Project managers can see their own schedules, but nobody has a dependable combined view of demand, skills, planned leave, named allocations, and unresolved capacity conflicts.

The immediate requirement is not a full enterprise resource management system. It is a controlled planning register that can support mobile changes, calculate weekly capacity, identify exceptions, preserve human approval, and show scenarios without altering the approved baseline plan.

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.

The Existing Process

The original process runs in the following order:

1. A project manager adds role requirements to an individual project spreadsheet.
2. The project manager emails department supervisors to ask who might be available.
3. Supervisors check their own team schedules, remembered commitments, and the shared leave calendar.
4. Suggested names are returned by email or entered into another spreadsheet.
5. The operations planning manager copies allocations into a consolidated workbook.
6. The HR coordinator sends approved leave changes separately.
7. The planning manager manually adjusts capacity calculations and highlights apparent conflicts.
8. Project managers review the workbook during a weekly planning meeting.
9. Decisions made during the meeting are copied back into project schedules and follow-up emails.

The central problem is not simply that spreadsheets are being used. The problem is that separate spreadsheets have no governed data model, reliable synchronization, ownership rules, automated exception handling, or protected scenario structure.

What the New System Needed to Do

Implementation Approaches Considered

Improved spreadsheets

A single protected workbook would reduce some reconciliation. It would not provide sufficiently controlled mobile forms, row-level experiences, role-sensitive actions, or dependable event processing. It also would leave managers editing operational tables directly.

AppSheet with Google Workspace

This option retained the existing Google environment while adding mobile forms and governed views. Google Apps Script could perform calculations that were too interconnected for simple spreadsheet formulas, and Gmail could send controlled exception summaries.

Airtable

Airtable was a credible alternative because the planning data is relational. It was not selected for this representative scenario because the business wanted to retain Google Sheets as the operational register and avoid introducing another core data administration platform.

Project platform and BI tool

This would be suitable if task-level schedules were already standardized and maintained accurately. Cedar Vale’s project files did not yet contain consistent role, skill, leave, or named-capacity information, so a reporting layer would have exposed the same data-quality gaps.

Custom application

A custom application would be appropriate at substantially higher scale or with complex scheduling algorithms. It was disproportionate for 58 allocatable employees, 24 projects, and a planning horizon of 16 weeks.

The Selected Solution

The selected implementation uses AppSheet as the controlled mobile interface, Google Sheets as the system of record, Google Apps Script as the calculation and workflow layer, Gmail for notifications, and Google Drive for the workbook and limited supporting documents.

Existing Google accounts and Gmail were retained. Direct editing of operational tabs was removed for ordinary users. Project managers and supervisors entered changes through AppSheet, while the operations planning manager retained controlled spreadsheet access for administration and recovery.

Human control remained over requirement approval, named allocation confirmation, high-severity overrides, skill acceptance, leave approval, and project priority. The automation calculated consequences and routed exceptions, but it did not make staffing decisions.

System Architecture and Data Flow

1. Mobile entry: A project manager, supervisor, or HR coordinator creates or updates a record in AppSheet.
2. App validation: AppSheet checks required fields, allowed values, dates, references, and user permissions before synchronization.
3. Register update: AppSheet writes the validated row to the appropriate Google Sheets table and marks its automation status as Pending.
4. Event initiation: An AppSheet automation bot sends a webhook containing an event ID, entity type, record ID, event type, and shared secret to an Apps Script web application.
5. Idempotency check: Apps Script checks the Events table. A completed event with the same event ID is acknowledged without repeating the calculation.
6. Planning rebuild: Apps Script expands requirements and allocations across the configured weekly horizon, subtracts approved leave, and calculates remaining capacity.
7. Exception generation: The script creates deterministic exception IDs for overallocations, leave overlaps, skill mismatches, unfilled requirements, invalid dates, and duplicate entries.
8. Record update: The source row is marked Processed, output tables are refreshed, and the event receives a Completed status.
9. Notification: New high- and medium-severity exceptions are grouped by owner and sent through Gmail. Previously notified exceptions are not sent again unless they are resolved and later recur.
10. Fallback processing: A five-minute time trigger scans for Pending rows. This recovers changes when a webhook is unavailable or delayed.
11. Reporting: AppSheet dashboards and protected Sheets views read the calculated capacity, demand, and exception tables.
12. Failure path: A failed event is logged, the source row receives an Error status, the planning owner is notified, and an authorized administrator can reprocess the record.

• Intake: AppSheet forms for projects, requirements, allocations, skills, and approved availability changes
• System of record: Structured Google Sheets tables
• Automation layer: Google Apps Script web app plus scheduled fallback triggers
• Document storage: Restricted Google Drive folders
• Notifications: Gmail messages generated by Apps Script
• Reporting: AppSheet dashboards, slices, charts, and protected Sheets analysis views
• AI layer: Optional structured conflict briefing created from open exception data

Data Structure

The workbook uses separate tabs as logical tables. IDs, rather than row numbers, connect the records. A project can have multiple requirements and allocations. An employee can have multiple skills, allocations, and leave records. Requirements and allocations belong to a scenario, while employee capacity and approved leave are shared across scenarios.

The scenario identifier uses controlled values such as Baseline, Option-A, and Option-B. A scenario is created by copying relevant baseline requirements and allocations with new IDs and a new ScenarioID. The approved baseline is never edited merely to test a possible staffing change.

The system does not store sensitive reasons for leave. The planning calculation only needs employee ID, dates, hours per day, and approval status.

Workflow Statuses and Ownership

High-severity conflicts do not automatically cancel or reassign work. The planning manager investigates and either changes dates, hours, or employees; returns the proposal; or requests a documented override from the operations director.

Step-by-Step Implementation

Step 1: Prepare the Accounts and Permissions

1. Create or identify a dedicated Google Workspace automation account. It owns the workbook, Apps Script project, web-app deployment, and Gmail notifications.
2. Confirm that the AppSheet subscription and administrative policy support the required number of authenticated users, automation bots, webhook tasks, and deployed applications. Product entitlements can change, so verify current licensing rather than relying on an assumed plan name.
3. Create a restricted shared-drive or Drive folder named Resource Planning. Add subfolders for System, Exports, Skill Evidence, and Documentation.
4. Create the Google Sheets workbook in the System folder. Only the automation owner, planning system owner, and backup owner receive direct edit access.
5. Create a shared planning address or Google Group for system alerts. The code uses a configured planner address rather than embedding a personal mailbox.
6. Define AppSheet roles in the Users table: Admin, Planner, ProjectManager, Supervisor, HR, Executive, and Employee.
7. Create at least one test user for each role. Use non-production sample projects and employee IDs during testing.
8. Decide whether the Apps Script web app may be accessible to AppSheet’s server-side webhook. If organizational policy prohibits a token-protected public endpoint, disable the webhook and use the five-minute scheduled scan as the primary trigger.
9. Store secrets in Apps Script Properties, not in spreadsheet cells or AppSheet user-visible fields.

The Apps Script deployment executes as the automation owner. That account therefore needs access to the workbook and permission to send Gmail messages. Ordinary AppSheet users do not need direct access to the underlying spreadsheet.

Permission boundary: AppSheet slices and views improve the user experience, but they are not substitutes for security filters. Apply security filters and application authorization before adding convenience views.

Step 2: Build the Intake

Create AppSheet tables from the Projects, Employees, Skills, EmployeeSkills, Requirements, Allocations, Leave, Exceptions, Users, CapacityWeekly, and DemandWeekly tabs. Keep Events, Settings, and AI_Briefs restricted to administrators unless a controlled monitoring view is required.

Use UNIQUEID() as the initial value for each key column. Use reference columns for projects, employees, requirements, skills, and users instead of free-text entry.

Configure enum values rather than allowing arbitrary status text. Use conditional display rules so rejection reasons appear only when Rejected is selected and override reasons appear only when an override is requested or approved.

For exact duplicate prevention, an allocation can use a validation expression based on employee, requirement, dates, scenario, and active status. Do not block all overlapping allocations because legitimate employees may divide a week among several projects. The capacity engine should detect excessive total hours instead.

COUNT(
  SELECT(
    Allocations[AllocationID],
    AND(
      [EmployeeID] = [_THISROW].[EmployeeID],
      [RequirementID] = [_THISROW].[RequirementID],
      [StartDate] = [_THISROW].[StartDate],
      [EndDate] = [_THISROW].[EndDate],
      [ScenarioID] = [_THISROW].[ScenarioID],
      NOT(IN([Status], {"Rejected", "Cancelled"})),
      [AllocationID] <> [_THISROW].[AllocationID]
    )
  )
) = 0

Add a short privacy notice to the leave form. Explain that the planning application requires dates and hours only and must not contain medical information or detailed absence reasons.

Set the confirmation message to tell users that the record has been saved and that capacity calculations may take several minutes if the fallback scheduled process is used.

Hard deletion should be disabled for normal users. Status changes to Cancelled preserve history and make webhook recovery possible.

Step 3: Create the System of Record

Run the supplied setupWorkbook function in the bound Apps Script project. It creates the required tabs and exact headers without overwriting existing populated tables.

Use the following naming rules:

• Project IDs: AppSheet-generated text keys such as PRJ-7F21A9 or raw UNIQUEID() values
• Employee IDs: stable internal identifiers such as EMP-1042
• Requirement IDs: generated text keys
• Allocation IDs: generated text keys
• Scenario IDs: controlled values such as Baseline, Option-A, and Option-B
• Capacity keys: scenario, employee, and Monday week date combined by the script
• Exception IDs: SHA-256-derived deterministic values generated by the script

The output tabs, CapacityWeekly and DemandWeekly, are rebuilt by the automation. Users must not edit them directly. Exceptions preserves workflow status and resolution notes while refreshing calculated details.

Create filtered AppSheet slices for Baseline, each proposed scenario, open exceptions, automation errors, and the current 16-week horizon. Use Monday as the standard week start and confirm that the spreadsheet and Apps Script project use the same organizational time zone.

The Settings table contains configurable values such as planning email, horizon weeks, reminder age, and severities that require immediate notification. Keep this table administrator-only.

Step 4: Connect the Tools

Configure an AppSheet bot for add and update events on Projects, Requirements, Allocations, EmployeeSkills, and Leave. Before the webhook task, use a data-change action to set AutomationStatus to Pending. Then send a body similar to the following:

{
  "token": "YOUR_WEBHOOK_SHARED_SECRET",
  "eventId": "Allocation-<<[AllocationID]>>-<<[LastModified]>>",
  "entityType": "Allocation",
  "entityId": "<<[AllocationID]>>",
  "eventType": "UPSERT"
}

AppSheet interface labels may vary by release. The required behavior is a data-change event followed by a row update and an HTTP POST to the deployed web-app URL.

The script returns JSON such as:

{
  "ok": true,
  "duplicate": false,
  "eventId": "Allocation-A1B2C3-2026-07-15T10:30:00",
  "capacityRows": 1856,
  "demandRows": 312,
  "activeExceptions": 17
}

Step 5: Build the Core Automation

The core automation runs in this exact order:

1. Validate the webhook secret, event ID, entity type, and entity ID.
2. Acquire a script lock so two events cannot rebuild the workbook simultaneously.
3. Look for the event ID in Events.
4. Return a successful duplicate response when the event already completed.
5. Record or update the event as Processing.
6. Read projects, employees, skills, requirements, allocations, leave, and settings.
7. Validate references and dates.
8. Expand active requirements and allocations over the configured Monday-based planning horizon.
9. Prorate weekly hours when a record covers fewer than five weekdays.
10. Subtract approved leave from each employee’s base weekly capacity.
11. Write CapacityWeekly and DemandWeekly.
12. Generate deterministic exceptions.
13. Preserve acknowledged and approved-override statuses for continuing exceptions.
14. Resolve previously open exceptions that are no longer detected.
15. Mark the source record and event as completed.
16. Group new material exceptions by owner and send Gmail messages.

• Trigger: AppSheet webhook or five-minute Pending-row scan
• Conditions: Valid event, recognized entity, authorized token, and non-duplicate event ID
• Actions: Recalculate capacity and demand, upsert exceptions, update audit fields, and notify owners
• Fields updated: AutomationStatus, LastAutomationRun, ErrorMessage, event status, calculated output fields, and notification status
• Notification: One grouped email per owner for new high- or medium-severity exceptions
• Exception: Processing failures are recorded in Events and on the source row

Duplicate events are prevented through the Events table. Duplicate exception records are prevented with deterministic keys composed from conflict type, scenario, entity, employee, project, and week.

Users recover a failed record by correcting its data and setting AutomationStatus to Pending through an administrator action. An Apps Script administrator can also call reprocessRecord with the entity type and ID.

Step 6: Add Approvals, Reminders, and Escalations

Create AppSheet actions for Submit, Confirm, Reject, Return for Information, Acknowledge Exception, and Approve Override. Restrict each action by the signed-in user’s role.

A proposed allocation follows this approval sequence:

1. The project manager submits the allocation as Proposed.
2. The automation calculates its impact.
3. If there is no unresolved high-severity exception, the planning manager can set it to Confirmed.
4. If a blocking conflict exists, the planning manager changes the allocation, returns it, or requests an override.
5. Only the operations director or delegated approver can set an exception to Override Approved.
6. The action records ApprovedBy, ApprovedAt, and OverrideReason.
7. The final confirmation triggers another calculation and a confirmation email when required.

Use an AppSheet action condition comparable to the following when controlling the Confirm action:

AND(
  LOOKUP(USEREMAIL(), "Users", "Email", "AccessRole") = "Planner",
  COUNT(
    SELECT(
      Exceptions[ExceptionID],
      AND(
        [ScenarioID] = [_THISROW].[ScenarioID],
        [EmployeeID] = [_THISROW].[EmployeeID],
        [Status] = "Open",
        [Severity] = "High"
      )
    )
  ) = 0
)

The daily Apps Script reminder finds high- and medium-severity exceptions that remain Open or Acknowledged beyond the configured reminder age. If the owner is inactive, the script uses the active delegate from Users. If neither is valid, it routes the message to the planning mailbox.

Rejection and return actions require notes. An approved override requires the approving user, timestamp, reason, affected scenario, and exception ID. Those fields provide audit evidence without relying on email history.

Step 7: Add Documents and File Management

The core resource plan does not require a separate document for every allocation. Avoid creating unnecessary folders that increase administration.

Use the restricted Drive structure for these limited purposes:

• Resource Planning/System for the workbook
• Resource Planning/Exports/YYYY/MM for approved planning snapshots
• Resource Planning/Skill Evidence/EmployeeID for optional certificates or competency evidence
• Resource Planning/Documentation for data definitions, role descriptions, recovery instructions, and release notes

If evidence files are attached through AppSheet, store only the resulting file path or Drive link in EmployeeSkills. Restrict evidence access to HR, authorized supervisors, and administrators. Project managers should see the verified proficiency result, not necessarily the underlying document.

Use a naming convention such as EmployeeID_SkillID_ValidUntil. Do not replace a file silently. Add a new file, update the current link, and retain the previous version according to the organization’s retention policy.

Missing evidence should create a review flag only when evidence is required by policy. It should not automatically reject an employee assignment. Failed uploads remain visible as incomplete records and are handled by HR or the responsible supervisor.

Step 8: Add Reporting and Operational Views

Create the following AppSheet slices and dashboard views:

• New requirements awaiting planning review
• Proposed allocations awaiting confirmation
• Employees above 100 percent utilization
• Employees with less than eight hours remaining capacity
• Unfilled requirements by project, role, and week
• Potential leave overlaps
• Skill mismatches and expiring skills
• Open and acknowledged exceptions by owner
• Overdue exceptions
• Rejected or returned proposals
• Baseline compared with Option-A or Option-B
• Upcoming project starts and completion dates
• Recently confirmed allocations
• Automation errors and failed events
• AI briefings awaiting human review

Capacity and demand source data refresh after each successful rebuild. The scheduled fallback establishes an expected maximum delay of approximately five minutes when the webhook is not used.

Useful calculated reporting measures include:

Utilization percentage = Allocated hours / Available hours

Remaining hours = Available hours - Allocated hours

Requirement gap = MAX(0, Required hours - Allocated hours)

Open exception age = Current date - Detected date

The operations planning manager owns operational dashboards. The operations director owns escalation thresholds and baseline approval. Executive views should aggregate by department, role, week, and scenario rather than exposing unnecessary employee-level details.

Step 9: Add Security and Governance Controls

• Use authenticated AppSheet users and require organizational sign-in.
• Apply security filters based on the Users table and USEREMAIL().
• Give direct workbook edit access only to the system owner and backup owner.
• Protect output tabs and formula or configuration areas.
• Store webhook secrets and API keys in Apps Script Properties.
• Do not place credentials in AppSheet columns, email templates, or source code.
• Limit leave data to dates, hours, and approval status.
• Restrict skill-evidence files independently from planning records.
• Remove former users from AppSheet, Google Groups, Drive permissions, and the Users table promptly.
• Review Apps Script executions, failed Events rows, and Gmail delivery errors.
• Retain approved allocation and override evidence according to operational and contractual requirements.
• Back up the workbook before structural changes and export periodic baseline snapshots.
• Do not send names, emails, leave reasons, medical data, or confidential customer details to the optional AI service.
• Require human approval for allocation confirmation and all high-severity overrides.

A representative AppSheet security filter can distinguish administrators from project managers. The exact expression depends on which tables each role may see.

OR(
  IN(
    LOOKUP(USEREMAIL(), "Users", "Email", "AccessRole"),
    {"Admin", "Planner", "Executive"}
  ),
  [ProjectManagerEmail] = USEREMAIL()
)

Views and slices must not be treated as security controls because synchronized data may still be present on a device. Use security filters to restrict which rows are delivered.

Step 10: Deploy and Test

1. Build the workbook and application in a non-production Drive folder.
2. Load sample employees, projects, requirements, allocations, and leave records.
3. Run setupWorkbook and review every generated header.
4. Set the spreadsheet time zone and Apps Script project time zone consistently.
5. Configure Script Properties for the spreadsheet ID, webhook secret, and planning email.
6. Deploy the Apps Script project as a web app that executes as the automation owner.
7. Configure the AppSheet webhook with the deployed URL and secret.
8. Install the scheduled fallback and reminder triggers.
9. Run test cases for normal, duplicate, conflict, approval, and failure paths.
10. Complete user acceptance testing with one project manager, one supervisor, HR, the planning manager, and the backup administrator.
11. Pilot the system with three projects for two weekly planning cycles.
12. Compare calculated hours with the previous workbook before switching the remaining projects.
13. Freeze the legacy workbook and retain it as read-only history.
14. Activate the production application in phases by department.
15. Publish a short operating guide covering status definitions, approval responsibilities, and error recovery.

If a serious calculation issue is found, disable the AppSheet webhook, pause scheduled triggers, restore the most recent workbook copy, and temporarily return to the last approved baseline snapshot. Correct and retest the script before reactivation.

Code and Configuration

The following complete Google Apps Script creates the workbook structure, processes AppSheet webhooks, rebuilds weekly capacity and demand, creates exceptions, sends Gmail notifications, installs fallback triggers, and optionally creates an AI conflict brief.

Place the script in the resource-planning workbook. Open the workbook, open the Apps Script editor from its extensions menu, replace the default editor content, and paste the complete script below.

const CFG = {
  SHEETS: {
    Settings: ["Key", "Value"],
    Users: ["Email", "FullName", "AccessRole", "Active", "DelegateEmail"],
    Projects: ["ProjectID", "ProjectName", "ClientCode", "StartDate", "EndDate",
      "ProjectManagerEmail", "Priority", "Status", "ScenarioID",
      "AutomationStatus", "LastModified", "LastAutomationRun", "ErrorMessage"],
    Employees: ["EmployeeID", "EmployeeName", "WorkEmail", "Department",
      "PrimaryRole", "WeeklyCapacityHours", "Active", "ManagerEmail",
      "AutomationStatus", "LastModified", "LastAutomationRun", "ErrorMessage"],
    Skills: ["SkillID", "SkillName", "Category", "Active"],
    EmployeeSkills: ["EmployeeSkillID", "EmployeeID", "SkillID", "Proficiency",
      "ValidUntil", "AutomationStatus", "LastModified", "LastAutomationRun",
      "ErrorMessage"],
    Requirements: ["RequirementID", "ProjectID", "Role", "SkillID",
      "MinimumProficiency", "StartDate", "EndDate", "HoursPerWeek",
      "ScenarioID", "Priority", "Status", "ApprovedBy", "ApprovedAt",
      "AutomationStatus", "LastModified", "LastAutomationRun", "ErrorMessage"],
    Allocations: ["AllocationID", "RequirementID", "ProjectID", "EmployeeID",
      "Role", "StartDate", "EndDate", "HoursPerWeek", "ScenarioID", "Status",
      "Notes", "ApprovedBy", "ApprovedAt", "OverrideReason",
      "AutomationStatus", "LastModified", "LastAutomationRun", "ErrorMessage"],
    Leave: ["LeaveID", "EmployeeID", "StartDate", "EndDate", "HoursPerDay",
      "LeaveType", "ApprovalStatus", "AutomationStatus", "LastModified",
      "LastAutomationRun", "ErrorMessage"],
    CapacityWeekly: ["CapacityKey", "WeekStart", "ScenarioID", "EmployeeID",
      "PrimaryRole", "BaseCapacityHours", "ApprovedLeaveHours",
      "AvailableHours", "AllocatedHours", "RemainingHours",
      "UtilizationPct", "LastCalculatedAt"],
    DemandWeekly: ["DemandKey", "WeekStart", "ScenarioID", "RequirementID",
      "ProjectID", "Role", "SkillID", "RequiredHours", "AllocatedHours",
      "GapHours", "LastCalculatedAt"],
    Exceptions: ["ExceptionID", "Type", "Severity", "ScenarioID", "EntityType",
      "EntityID", "ProjectID", "EmployeeID", "WeekStart", "Description",
      "OwnerEmail", "Status", "DetectedAt", "LastSeenAt", "ResolutionNotes",
      "NotificationStatus", "NotifiedAt"],
    Events: ["EventID", "ReceivedAt", "EntityType", "EntityID", "EventType",
      "Status", "AttemptCount", "ErrorMessage", "CompletedAt"],
    AI_Briefs: ["BriefID", "CreatedAt", "ScenarioID", "PeriodStart",
      "PeriodEnd", "InputExceptionCount", "Model", "SummaryJSON",
      "ReviewStatus", "ReviewerEmail", "ReviewedAt"]
  },
  ENTITY_MAP: {
    Project: {sheet: "Projects", id: "ProjectID"},
    Employee: {sheet: "Employees", id: "EmployeeID"},
    Skill: {sheet: "Skills", id: "SkillID"},
    EmployeeSkill: {sheet: "EmployeeSkills", id: "EmployeeSkillID"},
    Requirement: {sheet: "Requirements", id: "RequirementID"},
    Allocation: {sheet: "Allocations", id: "AllocationID"},
    Leave: {sheet: "Leave", id: "LeaveID"}
  }
};

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("Resource Planning")
    .addItem("Set up workbook", "setupWorkbook")
    .addItem("Rebuild planning model", "manualRebuild")
    .addItem("Process pending changes", "processPendingChanges")
    .addItem("Generate AI conflict brief", "generateAiConflictBrief")
    .addToUi();
}

function setupWorkbook() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  if (!ss) {
    throw new Error("Open the bound spreadsheet before running setupWorkbook.");
  }

  PropertiesService.getScriptProperties()
    .setProperty("SPREADSHEET_ID", ss.getId());

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

  const defaults = {
    PlannerEmail: "YOUR_EMAIL_ADDRESS",
    HorizonWeeks: "16",
    ReminderDays: "2",
    NotifySeverities: "High,Medium",
    DefaultScenario: "Baseline"
  };

  const settings = readObjects_("Settings");
  const existing = {};
  settings.forEach(function(row) {
    existing[String(row.Key).trim()] = true;
  });

  const sheet = ss.getSheetByName("Settings");
  Object.keys(defaults).forEach(function(key) {
    if (!existing[key]) {
      sheet.appendRow([key, defaults[key]]);
    }
  });

  console.log("Workbook setup completed for spreadsheet " + ss.getId());
}

function installTriggers() {
  const handlers = ["processPendingChanges", "dailyExceptionReminder"];
  ScriptApp.getProjectTriggers().forEach(function(trigger) {
    if (handlers.indexOf(trigger.getHandlerFunction()) !== -1) {
      ScriptApp.deleteTrigger(trigger);
    }
  });

  ScriptApp.newTrigger("processPendingChanges")
    .timeBased()
    .everyMinutes(5)
    .create();

  ScriptApp.newTrigger("dailyExceptionReminder")
    .timeBased()
    .everyDays(1)
    .atHour(7)
    .create();

  console.log("Fallback and reminder triggers installed.");
}

function doGet() {
  return jsonOutput_({
    ok: true,
    service: "resource-planning-automation",
    time: new Date().toISOString()
  });
}

function doPost(e) {
  try {
    const raw = e && e.postData ? e.postData.contents : "";
    if (!raw) {
      throw new Error("Request body is empty.");
    }

    const payload = JSON.parse(raw);
    validateWebhook_(payload);

    const result = withScriptLock_(function() {
      return processWebhookEvent_(payload);
    });

    return jsonOutput_(result);
  } catch (error) {
    console.error(error.stack || error);
    return jsonOutput_({
      ok: false,
      error: safeError_(error)
    });
  }
}

function validateWebhook_(payload) {
  const expected = PropertiesService.getScriptProperties()
    .getProperty("WEBHOOK_SECRET");

  if (!expected || expected === "YOUR_WEBHOOK_SHARED_SECRET") {
    throw new Error("WEBHOOK_SECRET is not configured.");
  }

  if (!payload || String(payload.token || "") !== expected) {
    throw new Error("Webhook authentication failed.");
  }

  if (!payload.eventId || String(payload.eventId).length > 200) {
    throw new Error("A valid eventId is required.");
  }

  if (!CFG.ENTITY_MAP[payload.entityType]) {
    throw new Error("Unsupported entityType: " + payload.entityType);
  }

  if (!payload.entityId || String(payload.entityId).length > 120) {
    throw new Error("A valid entityId is required.");
  }

  const allowedEvents = ["UPSERT", "ADD", "UPDATE", "DELETE"];
  if (allowedEvents.indexOf(String(payload.eventType || "UPSERT")) === -1) {
    throw new Error("Unsupported eventType.");
  }
}

function processWebhookEvent_(payload) {
  const eventId = String(payload.eventId);
  const existing = findObjectByValue_("Events", "EventID", eventId);

  if (existing && String(existing.object.Status) === "Completed") {
    return {
      ok: true,
      duplicate: true,
      eventId: eventId
    };
  }

  const attempt = existing
    ? Number(existing.object.AttemptCount || 0) + 1
    : 1;

  if (existing) {
    updateObjectRow_("Events", existing.row, {
      ReceivedAt: new Date(),
      Status: "Processing",
      AttemptCount: attempt,
      ErrorMessage: "",
      CompletedAt: ""
    });
  } else {
    appendObject_("Events", {
      EventID: eventId,
      ReceivedAt: new Date(),
      EntityType: payload.entityType,
      EntityID: payload.entityId,
      EventType: payload.eventType || "UPSERT",
      Status: "Processing",
      AttemptCount: attempt,
      ErrorMessage: "",
      CompletedAt: ""
    });
  }

  try {
    const result = rebuildPlanningModel_();
    markSourceRecord_(
      payload.entityType,
      payload.entityId,
      "Processed",
      ""
    );

    const current = findObjectByValue_("Events", "EventID", eventId);
    updateObjectRow_("Events", current.row, {
      Status: "Completed",
      ErrorMessage: "",
      CompletedAt: new Date()
    });

    try {
      notifyNewExceptions_();
    } catch (notificationError) {
      console.error("Notification failure: " + safeError_(notificationError));
    }

    result.ok = true;
    result.duplicate = false;
    result.eventId = eventId;
    return result;
  } catch (error) {
    const failed = findObjectByValue_("Events", "EventID", eventId);
    if (failed) {
      updateObjectRow_("Events", failed.row, {
        Status: "Failed",
        ErrorMessage: safeError_(error),
        CompletedAt: new Date()
      });
    }

    markSourceRecord_(
      payload.entityType,
      payload.entityId,
      "Error",
      safeError_(error)
    );

    reportFatalError_("Webhook event " + eventId, error);
    throw error;
  }
}

function processPendingChanges() {
  return withScriptLock_(function() {
    const pending = listPendingRows_();
    if (pending.length === 0) {
      console.log("No pending records found.");
      return {ok: true, pending: 0};
    }

    try {
      const result = rebuildPlanningModel_();
      pending.forEach(function(item) {
        updateObjectRow_(item.sheet, item.row, {
          AutomationStatus: "Processed",
          LastAutomationRun: new Date(),
          ErrorMessage: ""
        });
      });

      try {
        notifyNewExceptions_();
      } catch (notificationError) {
        console.error("Notification failure: " + safeError_(notificationError));
      }

      result.ok = true;
      result.pending = pending.length;
      return result;
    } catch (error) {
      pending.forEach(function(item) {
        updateObjectRow_(item.sheet, item.row, {
          AutomationStatus: "Error",
          LastAutomationRun: new Date(),
          ErrorMessage: safeError_(error)
        });
      });

      reportFatalError_("Scheduled pending-row processing", error);
      throw error;
    }
  });
}

function manualRebuild() {
  return withScriptLock_(function() {
    const result = rebuildPlanningModel_();
    notifyNewExceptions_();
    console.log(JSON.stringify(result));
    return result;
  });
}

function reprocessRecord(entityType, entityId) {
  if (!CFG.ENTITY_MAP[entityType]) {
    throw new Error("Unsupported entityType.");
  }

  const map = CFG.ENTITY_MAP[entityType];
  const record = findObjectByValue_(map.sheet, map.id, entityId);
  if (!record) {
    throw new Error("Record not found: " + entityType + " " + entityId);
  }

  updateObjectRow_(map.sheet, record.row, {
    AutomationStatus: "Pending",
    ErrorMessage: ""
  });

  return processPendingChanges();
}

function rebuildPlanningModel_() {
  const ss = openBook_();
  const settings = getSettings_();
  const timeZone = ss.getSpreadsheetTimeZone();
  const horizonWeeks = Math.max(1, Number(settings.HorizonWeeks || 16));
  const now = new Date();
  const firstWeek = monday_(now);
  const weeks = [];

  for (let i = 0; i < horizonWeeks; i++) {
    const week = new Date(firstWeek);
    week.setDate(week.getDate() + (i * 7));
    weeks.push(week);
  }

  const projects = readObjects_("Projects");
  const employees = readObjects_("Employees");
  const employeeSkills = readObjects_("EmployeeSkills");
  const requirements = readObjects_("Requirements");
  const allocations = readObjects_("Allocations");
  const leave = readObjects_("Leave");

  const projectById = keyBy_(projects, "ProjectID");
  const employeeById = keyBy_(employees, "EmployeeID");
  const requirementById = keyBy_(requirements, "RequirementID");
  const plannerEmail = String(settings.PlannerEmail || "");

  const activeEmployees = employees.filter(function(row) {
    return truthy_(row.Active);
  });

  const scenarios = {};
  scenarios[String(settings.DefaultScenario || "Baseline")] = true;

  requirements.forEach(function(row) {
    if (isActiveRequirement_(row)) {
      scenarios[String(row.ScenarioID || "Baseline")] = true;
    }
  });

  allocations.forEach(function(row) {
    if (isActiveAllocation_(row)) {
      scenarios[String(row.ScenarioID || "Baseline")] = true;
    }
  });

  const scenarioList = Object.keys(scenarios);
  const skillMap = {};

  employeeSkills.forEach(function(row) {
    const key = String(row.EmployeeID) + "|" + String(row.SkillID);
    skillMap[key] = {
      proficiency: Number(row.Proficiency || 0),
      validUntil: toDate_(row.ValidUntil)
    };
  });

  const allocatedByEmployee = {};
  const allocatedByRequirement = {};
  const generatedExceptions = [];
  const duplicateSignatures = {};

  function addException(data) {
    generatedExceptions.push(makeException_(data, now, timeZone));
  }

  allocations.filter(isActiveAllocation_).forEach(function(allocation) {
    const employee = employeeById[String(allocation.EmployeeID)];
    const project = projectById[String(allocation.ProjectID)];
    const requirement = requirementById[String(allocation.RequirementID)];
    const start = toDate_(allocation.StartDate);
    const end = toDate_(allocation.EndDate);
    const scenario = String(allocation.ScenarioID || "Baseline");
    const owner = ownerFor_(project, employee, plannerEmail);

    if (!employee || !project || !requirement || !start || !end || end < start) {
      addException({
        type: "INVALID_ALLOCATION",
        severity: "High",
        scenario: scenario,
        entityType: "Allocation",
        entityId: allocation.AllocationID,
        projectId: allocation.ProjectID,
        employeeId: allocation.EmployeeID,
        description: "Allocation has an invalid reference or date range.",
        ownerEmail: owner
      });
      return;
    }

    const projectStart = toDate_(project.StartDate);
    const projectEnd = toDate_(project.EndDate);
    if ((projectStart && start < projectStart) ||
        (projectEnd && end > projectEnd)) {
      addException({
        type: "PROJECT_DATE_MISMATCH",
        severity: "Medium",
        scenario: scenario,
        entityType: "Allocation",
        entityId: allocation.AllocationID,
        projectId: allocation.ProjectID,
        employeeId: allocation.EmployeeID,
        description: "Allocation dates extend outside the project date range.",
        ownerEmail: owner
      });
    }

    const signature = [
      allocation.EmployeeID,
      allocation.RequirementID,
      dateKey_(start, timeZone),
      dateKey_(end, timeZone),
      scenario
    ].join("|");

    if (duplicateSignatures[signature]) {
      addException({
        type: "DUPLICATE_ALLOCATION",
        severity: "Medium",
        scenario: scenario,
        entityType: "Allocation",
        entityId: allocation.AllocationID,
        projectId: allocation.ProjectID,
        employeeId: allocation.EmployeeID,
        description: "Allocation duplicates another active allocation.",
        ownerEmail: owner
      });
    } else {
      duplicateSignatures[signature] = allocation.AllocationID;
    }

    if (requirement.SkillID) {
      const skill = skillMap[
        String(allocation.EmployeeID) + "|" + String(requirement.SkillID)
      ];
      const minimum = Number(requirement.MinimumProficiency || 0);
      const expired = skill && skill.validUntil && skill.validUntil < end;

      if (!skill || skill.proficiency < minimum || expired) {
        addException({
          type: "SKILL_MISMATCH",
          severity: "High",
          scenario: scenario,
          entityType: "Allocation",
          entityId: allocation.AllocationID,
          projectId: allocation.ProjectID,
          employeeId: allocation.EmployeeID,
          description: "Employee skill does not meet the requirement or expires before the allocation ends.",
          ownerEmail: owner
        });
      }
    }

    weeks.forEach(function(week) {
      const hours = hoursForWeek_(
        start,
        end,
        Number(allocation.HoursPerWeek || 0),
        week
      );

      if (hours <= 0) {
        return;
      }

      const employeeKey = [
        allocation.EmployeeID,
        scenario,
        dateKey_(week, timeZone)
      ].join("|");

      allocatedByEmployee[employeeKey] =
        Number(allocatedByEmployee[employeeKey] || 0) + hours;

      const requirementKey = [
        allocation.RequirementID,
        scenario,
        dateKey_(week, timeZone)
      ].join("|");

      allocatedByRequirement[requirementKey] =
        Number(allocatedByRequirement[requirementKey] || 0) + hours;
    });
  });

  const capacityRows = [];

  activeEmployees.forEach(function(employee) {
    scenarioList.forEach(function(scenario) {
      weeks.forEach(function(week) {
        const base = Number(employee.WeeklyCapacityHours || 0);
        let leaveHours = 0;

        leave.forEach(function(item) {
          if (String(item.EmployeeID) !== String(employee.EmployeeID) ||
              String(item.ApprovalStatus) !== "Approved") {
            return;
          }

          const start = toDate_(item.StartDate);
          const end = toDate_(item.EndDate);
          if (!start || !end) {
            return;
          }

          leaveHours += weekdayOverlap_(start, end, week) *
            Number(item.HoursPerDay || 0);
        });

        const key = [
          employee.EmployeeID,
          scenario,
          dateKey_(week, timeZone)
        ].join("|");

        const allocated = Number(allocatedByEmployee[key] || 0);
        const available = Math.max(0, base - leaveHours);
        const remaining = available - allocated;
        const utilization = available > 0 ? allocated / available : "";

        capacityRows.push({
          CapacityKey: [
            scenario,
            employee.EmployeeID,
            dateKey_(week, timeZone)
          ].join("|"),
          WeekStart: week,
          ScenarioID: scenario,
          EmployeeID: employee.EmployeeID,
          PrimaryRole: employee.PrimaryRole,
          BaseCapacityHours: round2_(base),
          ApprovedLeaveHours: round2_(leaveHours),
          AvailableHours: round2_(available),
          AllocatedHours: round2_(allocated),
          RemainingHours: round2_(remaining),
          UtilizationPct: utilization === "" ? "" : round4_(utilization),
          LastCalculatedAt: now
        });

        const owner = ownerFor_(
          null,
          employee,
          plannerEmail
        );

        if (allocated > available + 0.01) {
          addException({
            type: "EMPLOYEE_OVERALLOCATED",
            severity: allocated - available >= 8 ? "High" : "Medium",
            scenario: scenario,
            entityType: "EmployeeWeek",
            entityId: employee.EmployeeID + "|" + dateKey_(week, timeZone),
            employeeId: employee.EmployeeID,
            weekStart: week,
            description: "Employee " + employee.EmployeeID +
              " is allocated " + round2_(allocated) +
              " hours against " + round2_(available) +
              " available hours.",
            ownerEmail: owner
          });
        }

        if (leaveHours > 0 && allocated > 0) {
          addException({
            type: "POTENTIAL_LEAVE_OVERLAP",
            severity: "Medium",
            scenario: scenario,
            entityType: "EmployeeWeek",
            entityId: employee.EmployeeID + "|" + dateKey_(week, timeZone),
            employeeId: employee.EmployeeID,
            weekStart: week,
            description: "Employee " + employee.EmployeeID +
              " has approved leave and project allocation in the same week.",
            ownerEmail: owner
          });
        }
      });
    });
  });

  const demandRows = [];

  requirements.filter(isActiveRequirement_).forEach(function(requirement) {
    const project = projectById[String(requirement.ProjectID)];
    const start = toDate_(requirement.StartDate);
    const end = toDate_(requirement.EndDate);
    const scenario = String(requirement.ScenarioID || "Baseline");
    const owner = ownerFor_(project, null, plannerEmail);

    if (!project || !start || !end || end < start) {
      addException({
        type: "INVALID_REQUIREMENT",
        severity: "High",
        scenario: scenario,
        entityType: "Requirement",
        entityId: requirement.RequirementID,
        projectId: requirement.ProjectID,
        description: "Requirement has an invalid project reference or date range.",
        ownerEmail: owner
      });
      return;
    }

    weeks.forEach(function(week) {
      const required = hoursForWeek_(
        start,
        end,
        Number(requirement.HoursPerWeek || 0),
        week
      );

      if (required <= 0) {
        return;
      }

      const key = [
        requirement.RequirementID,
        scenario,
        dateKey_(week, timeZone)
      ].join("|");

      const allocated = Number(allocatedByRequirement[key] || 0);
      const gap = Math.max(0, required - allocated);

      demandRows.push({
        DemandKey: [
          scenario,
          requirement.RequirementID,
          dateKey_(week, timeZone)
        ].join("|"),
        WeekStart: week,
        ScenarioID: scenario,
        RequirementID: requirement.RequirementID,
        ProjectID: requirement.ProjectID,
        Role: requirement.Role,
        SkillID: requirement.SkillID,
        RequiredHours: round2_(required),
        AllocatedHours: round2_(allocated),
        GapHours: round2_(gap),
        LastCalculatedAt: now
      });

      if (gap > 0.01) {
        const highPriority =
          String(requirement.Priority) === "Critical" || gap >= 16;

        addException({
          type: "REQUIREMENT_UNFILLED",
          severity: highPriority ? "High" : "Medium",
          scenario: scenario,
          entityType: "RequirementWeek",
          entityId: requirement.RequirementID + "|" + dateKey_(week, timeZone),
          projectId: requirement.ProjectID,
          weekStart: week,
          description: "Requirement " + requirement.RequirementID +
            " has an unfilled gap of " + round2_(gap) + " hours.",
          ownerEmail: owner
        });
      }
    });
  });

  writeObjects_("CapacityWeekly", capacityRows);
  writeObjects_("DemandWeekly", demandRows);
  const exceptionResult = upsertExceptions_(generatedExceptions, now);

  return {
    capacityRows: capacityRows.length,
    demandRows: demandRows.length,
    activeExceptions: exceptionResult.active,
    resolvedExceptions: exceptionResult.resolved
  };
}

function makeException_(data, now, timeZone) {
  const weekKey = data.weekStart
    ? dateKey_(data.weekStart, timeZone)
    : "";

  const identity = [
    data.type,
    data.scenario || "Baseline",
    data.entityType || "",
    data.entityId || "",
    data.projectId || "",
    data.employeeId || "",
    weekKey
  ].join("|");

  return {
    ExceptionID: "EX-" + hashText_(identity).substring(0, 16),
    Type: data.type,
    Severity: data.severity || "Medium",
    ScenarioID: data.scenario || "Baseline",
    EntityType: data.entityType || "",
    EntityID: data.entityId || "",
    ProjectID: data.projectId || "",
    EmployeeID: data.employeeId || "",
    WeekStart: data.weekStart || "",
    Description: data.description || "",
    OwnerEmail: data.ownerEmail || "",
    Status: "Open",
    DetectedAt: now,
    LastSeenAt: now,
    ResolutionNotes: "",
    NotificationStatus: "Pending",
    NotifiedAt: ""
  };
}

function upsertExceptions_(generated, now) {
  const oldRows = readObjects_("Exceptions");
  const oldById = keyBy_(oldRows, "ExceptionID");
  const generatedIds = {};
  const output = [];
  let active = 0;
  let resolved = 0;

  generated.forEach(function(item) {
    generatedIds[String(item.ExceptionID)] = true;
    const old = oldById[String(item.ExceptionID)];

    if (old) {
      item.DetectedAt = old.DetectedAt || item.DetectedAt;
      item.ResolutionNotes = old.ResolutionNotes || "";

      if (String(old.Status) === "Override Approved") {
        item.Status = "Override Approved";
      } else if (String(old.Status) === "Acknowledged") {
        item.Status = "Acknowledged";
      } else {
        item.Status = "Open";
      }

      if (String(old.Status) === "Resolved") {
        item.NotificationStatus = "Pending";
        item.NotifiedAt = "";
      } else {
        item.NotificationStatus = old.NotificationStatus || "Pending";
        item.NotifiedAt = old.NotifiedAt || "";
      }
    }

    output.push(item);
    active++;
  });

  oldRows.forEach(function(old) {
    if (!generatedIds[String(old.ExceptionID)]) {
      if (String(old.Status) !== "Resolved") {
        old.Status = "Resolved";
        old.LastSeenAt = now;
        resolved++;
      }
      output.push(old);
    }
  });

  output.sort(function(a, b) {
    return String(a.ExceptionID).localeCompare(String(b.ExceptionID));
  });

  writeObjects_("Exceptions", output);
  return {active: active, resolved: resolved};
}

function notifyNewExceptions_() {
  const settings = getSettings_();
  const permitted = String(settings.NotifySeverities || "High,Medium")
    .split(",")
    .map(function(value) {
      return value.trim();
    });

  const exceptions = readObjects_("Exceptions");
  const groups = {};
  let changed = false;

  exceptions.forEach(function(item) {
    if (String(item.Status) !== "Open" ||
        String(item.NotificationStatus) !== "Pending") {
      return;
    }

    if (permitted.indexOf(String(item.Severity)) === -1) {
      item.NotificationStatus = "Suppressed";
      changed = true;
      return;
    }

    const recipient = effectiveRecipient_(
      item.OwnerEmail,
      settings.PlannerEmail
    );

    if (!recipient) {
      item.NotificationStatus = "Invalid recipient";
      changed = true;
      return;
    }

    if (!groups[recipient]) {
      groups[recipient] = [];
    }
    groups[recipient].push(item);
  });

  Object.keys(groups).forEach(function(recipient) {
    const items = groups[recipient];
    const body = [
      "New resource-planning exceptions require review.",
      "",
      items.slice(0, 30).map(function(item) {
        return [
          item.Severity,
          item.Type,
          item.ExceptionID,
          item.ProjectID || "No project",
          item.EmployeeID || "No employee",
          item.Description
        ].join(" | ");
      }).join("\n"),
      "",
      "Open the resource-planning application to acknowledge or resolve them."
    ].join("\n");

    try {
      withRetry_(function() {
        GmailApp.sendEmail(
          recipient,
          "Resource planning exceptions: " + items.length + " new",
          body,
          {name: "Resource Planning Automation"}
        );
      }, 3);

      items.forEach(function(item) {
        item.NotificationStatus = "Sent";
        item.NotifiedAt = new Date();
        changed = true;
      });
    } catch (error) {
      console.error("Email to " + recipient + " failed: " + safeError_(error));
    }
  });

  if (changed) {
    writeObjects_("Exceptions", exceptions);
  }
}

function dailyExceptionReminder() {
  return withScriptLock_(function() {
    const settings = getSettings_();
    const reminderDays = Math.max(1, Number(settings.ReminderDays || 2));
    const cutoff = new Date();
    cutoff.setDate(cutoff.getDate() - reminderDays);

    const groups = {};
    readObjects_("Exceptions").forEach(function(item) {
      if (["Open", "Acknowledged"].indexOf(String(item.Status)) === -1) {
        return;
      }

      if (["High", "Medium"].indexOf(String(item.Severity)) === -1) {
        return;
      }

      const detected = toDate_(item.DetectedAt);
      if (!detected || detected > cutoff) {
        return;
      }

      const recipient = effectiveRecipient_(
        item.OwnerEmail,
        settings.PlannerEmail
      );

      if (!recipient) {
        return;
      }

      if (!groups[recipient]) {
        groups[recipient] = [];
      }
      groups[recipient].push(item);
    });

    Object.keys(groups).forEach(function(recipient) {
      const items = groups[recipient];
      const body = [
        "Resource-planning exceptions remain unresolved.",
        "",
        items.slice(0, 40).map(function(item) {
          return [
            item.Severity,
            item.ExceptionID,
            item.Type,
            item.Description
          ].join(" | ");
        }).join("\n"),
        "",
        "Review ownership, resolution, or escalation in the planning application."
      ].join("\n");

      withRetry_(function() {
        GmailApp.sendEmail(
          recipient,
          "Resource planning reminder: " + items.length + " unresolved",
          body,
          {name: "Resource Planning Automation"}
        );
      }, 3);
    });

    return {ok: true, recipients: Object.keys(groups).length};
  });
}

function generateAiConflictBrief() {
  return withScriptLock_(function() {
    const props = PropertiesService.getScriptProperties();
    const apiKey = props.getProperty("AI_API_KEY");
    const model = props.getProperty("AI_MODEL");

    if (!apiKey || apiKey === "YOUR_API_KEY") {
      throw new Error("AI_API_KEY is not configured.");
    }

    if (!model || model === "YOUR_MODEL_NAME") {
      throw new Error("AI_MODEL is not configured.");
    }

    const settings = getSettings_();
    const ss = openBook_();
    const timeZone = ss.getSpreadsheetTimeZone();
    const openItems = readObjects_("Exceptions")
      .filter(function(item) {
        return ["Open", "Acknowledged"].indexOf(String(item.Status)) !== -1 &&
          ["High", "Medium"].indexOf(String(item.Severity)) !== -1;
      })
      .slice(0, 60);

    if (openItems.length === 0) {
      throw new Error("No open high- or medium-severity exceptions exist.");
    }

    const input = {
      period_start: dateKey_(monday_(new Date()), timeZone),
      horizon_weeks: Number(settings.HorizonWeeks || 16),
      exceptions: openItems.map(function(item) {
        return {
          exception_id: String(item.ExceptionID),
          type: String(item.Type),
          severity: String(item.Severity),
          scenario_id: String(item.ScenarioID),
          project_id: String(item.ProjectID || ""),
          employee_ref: String(item.EmployeeID || ""),
          week_start: item.WeekStart
            ? dateKey_(toDate_(item.WeekStart), timeZone)
            : "",
          description: redactEmails_(String(item.Description || ""))
        };
      })
    };

    const systemInstruction = [
      "You summarize validated resource-planning exceptions for a human operations planner.",
      "Use only facts present in the supplied JSON.",
      "Do not infer employee performance, health, protected characteristics, or customer information.",
      "Do not approve, reject, assign, or override work.",
      "Prioritize by supplied severity, timing, and explicit capacity gap.",
      "Reference only exception IDs present in the input.",
      "Return JSON that exactly matches the required schema."
    ].join(" ");

    const schema = {
      type: "object",
      additionalProperties: false,
      required: ["overview", "priorities", "data_quality_notes"],
      properties: {
        overview: {type: "string"},
        priorities: {
          type: "array",
          maxItems: 12,
          items: {
            type: "object",
            additionalProperties: false,
            required: [
              "exception_id",
              "risk",
              "summary",
              "recommended_next_step",
              "questions_for_planner"
            ],
            properties: {
              exception_id: {type: "string"},
              risk: {type: "string", enum: ["high", "medium", "low"]},
              summary: {type: "string"},
              recommended_next_step: {type: "string"},
              questions_for_planner: {
                type: "array",
                items: {type: "string"}
              }
            }
          }
        },
        data_quality_notes: {
          type: "array",
          items: {type: "string"}
        }
      }
    };

    const requestBody = {
      model: model,
      messages: [
        {role: "system", content: systemInstruction},
        {
          role: "user",
          content: "Create the planning brief from this validated JSON:\n" +
            JSON.stringify(input)
        }
      ],
      response_format: {
        type: "json_schema",
        json_schema: {
          name: "resource_conflict_brief",
          strict: true,
          schema: schema
        }
      }
    };

    const response = fetchJsonWithRetry_(
      "https://api.openai.com/v1/chat/completions",
      {
        method: "post",
        contentType: "application/json",
        headers: {
          Authorization: "Bearer " + apiKey
        },
        payload: JSON.stringify(requestBody),
        muteHttpExceptions: true
      },
      4
    );

    if (!response.choices ||
        !response.choices[0] ||
        !response.choices[0].message ||
        !response.choices[0].message.content) {
      throw new Error("AI response did not contain structured content.");
    }

    const brief = JSON.parse(response.choices[0].message.content);
    validateAiBrief_(brief, input.exceptions);

    const periodStart = monday_(new Date());
    const periodEnd = new Date(periodStart);
    periodEnd.setDate(
      periodEnd.getDate() + (Number(settings.HorizonWeeks || 16) * 7) - 1
    );

    appendObject_("AI_Briefs", {
      BriefID: "BR-" + hashText_(
        new Date().toISOString() + JSON.stringify(brief)
      ).substring(0, 16),
      CreatedAt: new Date(),
      ScenarioID: "Multiple",
      PeriodStart: periodStart,
      PeriodEnd: periodEnd,
      InputExceptionCount: input.exceptions.length,
      Model: model,
      SummaryJSON: JSON.stringify(brief),
      ReviewStatus: "Pending Human Review",
      ReviewerEmail: "",
      ReviewedAt: ""
    });

    console.log(JSON.stringify(brief));
    return brief;
  });
}

function validateAiBrief_(brief, inputExceptions) {
  if (!brief || typeof brief.overview !== "string" ||
      !Array.isArray(brief.priorities) ||
      !Array.isArray(brief.data_quality_notes)) {
    throw new Error("AI output failed schema validation.");
  }

  const allowedIds = {};
  inputExceptions.forEach(function(item) {
    allowedIds[String(item.exception_id)] = true;
  });

  brief.priorities.forEach(function(item) {
    if (!allowedIds[String(item.exception_id)]) {
      throw new Error("AI output referenced an unknown exception ID.");
    }

    if (["high", "medium", "low"].indexOf(String(item.risk)) === -1) {
      throw new Error("AI output returned an invalid risk value.");
    }
  });
}

function fetchJsonWithRetry_(url, options, attempts) {
  let lastError;

  for (let attempt = 0; attempt < attempts; attempt++) {
    try {
      const response = UrlFetchApp.fetch(url, options);
      const status = response.getResponseCode();
      const text = response.getContentText();

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

      if (status !== 429 && status < 500) {
        throw new Error("AI API returned HTTP " + status + ": " + text);
      }

      lastError = new Error("Transient AI API error HTTP " + status);
    } catch (error) {
      lastError = error;
    }

    Utilities.sleep(Math.pow(2, attempt) * 1000);
  }

  throw lastError || new Error("AI API request failed.");
}

function listPendingRows_() {
  const sourceSheets = [
    "Projects",
    "Employees",
    "EmployeeSkills",
    "Requirements",
    "Allocations",
    "Leave"
  ];

  const pending = [];

  sourceSheets.forEach(function(name) {
    readObjectsWithRows_(name).forEach(function(item) {
      if (String(item.object.AutomationStatus) === "Pending") {
        pending.push({
          sheet: name,
          row: item.row
        });
      }
    });
  });

  return pending;
}

function markSourceRecord_(entityType, entityId, status, errorMessage) {
  const map = CFG.ENTITY_MAP[entityType];
  if (!map) {
    return;
  }

  const record = findObjectByValue_(map.sheet, map.id, entityId);
  if (!record) {
    return;
  }

  updateObjectRow_(map.sheet, record.row, {
    AutomationStatus: status,
    LastAutomationRun: new Date(),
    ErrorMessage: errorMessage || ""
  });
}

function effectiveRecipient_(ownerEmail, fallbackEmail) {
  const users = readObjects_("Users");
  const owner = String(ownerEmail || "").trim().toLowerCase();
  const fallback = String(fallbackEmail || "").trim().toLowerCase();

  const user = users.find(function(item) {
    return String(item.Email || "").trim().toLowerCase() === owner;
  });

  if (user && truthy_(user.Active) && validEmail_(owner)) {
    return owner;
  }

  if (user && validEmail_(String(user.DelegateEmail || ""))) {
    return String(user.DelegateEmail).trim();
  }

  return validEmail_(fallback) ? fallback : "";
}

function ownerFor_(project, employee, fallback) {
  if (project && validEmail_(String(project.ProjectManagerEmail || ""))) {
    return String(project.ProjectManagerEmail).trim();
  }

  if (employee && validEmail_(String(employee.ManagerEmail || ""))) {
    return String(employee.ManagerEmail).trim();
  }

  return validEmail_(String(fallback || "")) ? String(fallback).trim() : "";
}

function isActiveRequirement_(row) {
  return ["Active", "Proposed"].indexOf(String(row.Status)) !== -1;
}

function isActiveAllocation_(row) {
  return ["Confirmed", "Proposed"].indexOf(String(row.Status)) !== -1;
}

function hoursForWeek_(start, end, hoursPerWeek, weekStart) {
  if (!start || !end || !hoursPerWeek) {
    return 0;
  }

  return hoursPerWeek * weekdayOverlap_(start, end, weekStart) / 5;
}

function weekdayOverlap_(rangeStart, rangeEnd, weekStart) {
  const weekEnd = new Date(weekStart);
  weekEnd.setDate(weekEnd.getDate() + 6);

  const start = rangeStart > weekStart
    ? new Date(rangeStart)
    : new Date(weekStart);

  const end = rangeEnd < weekEnd
    ? new Date(rangeEnd)
    : weekEnd;

  if (end < start) {
    return 0;
  }

  let count = 0;
  const cursor = new Date(start);
  cursor.setHours(0, 0, 0, 0);

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

  return count;
}

function monday_(value) {
  const date = new Date(value);
  date.setHours(0, 0, 0, 0);
  const offset = (date.getDay() + 6) % 7;
  date.setDate(date.getDate() - offset);
  return date;
}

function toDate_(value) {
  if (!value) {
    return null;
  }

  if (value instanceof Date && !isNaN(value.getTime())) {
    return new Date(value);
  }

  const dateOnly = String(value).match(/^(\d{4})-(\d{2})-(\d{2})$/);
  if (dateOnly) {
    return new Date(
      Number(dateOnly[1]),
      Number(dateOnly[2]) - 1,
      Number(dateOnly[3])
    );
  }

  const parsed = new Date(value);
  return isNaN(parsed.getTime()) ? null : parsed;
}

function dateKey_(date, timeZone) {
  return Utilities.formatDate(date, timeZone, "yyyy-MM-dd");
}

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

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

function truthy_(value) {
  return value === true ||
    ["true", "yes", "1", "active"].indexOf(
      String(value || "").trim().toLowerCase()
    ) !== -1;
}

function validEmail_(value) {
  return /^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(String(value || "").trim());
}

function redactEmails_(value) {
  return String(value).replace(
    /[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}/gi,
    "[email removed]"
  );
}

function hashText_(value) {
  const bytes = Utilities.computeDigest(
    Utilities.DigestAlgorithm.SHA_256,
    String(value),
    Utilities.Charset.UTF_8
  );

  return bytes.map(function(byte) {
    const unsigned = byte < 0 ? byte + 256 : byte;
    return ("0" + unsigned.toString(16)).slice(-2);
  }).join("");
}

function withScriptLock_(callback) {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(30000)) {
    throw new Error("Another planning calculation is running.");
  }

  try {
    return callback();
  } finally {
    lock.releaseLock();
  }
}

function withRetry_(callback, attempts) {
  let lastError;

  for (let attempt = 0; attempt < attempts; attempt++) {
    try {
      return callback();
    } catch (error) {
      lastError = error;
      Utilities.sleep(Math.pow(2, attempt) * 1000);
    }
  }

  throw lastError;
}

function reportFatalError_(context, error) {
  try {
    const settings = getSettings_();
    const recipient = String(settings.PlannerEmail || "");

    if (!validEmail_(recipient)) {
      console.error(context + ": " + safeError_(error));
      return;
    }

    withRetry_(function() {
      GmailApp.sendEmail(
        recipient,
        "Resource planning automation failure",
        context + "\n\n" + safeError_(error),
        {name: "Resource Planning Automation"}
      );
    }, 2);
  } catch (secondaryError) {
    console.error("Failure notification also failed: " +
      safeError_(secondaryError));
  }
}

function safeError_(error) {
  return String(error && error.message ? error.message : error)
    .substring(0, 1000);
}

function getSettings_() {
  const result = {};
  readObjects_("Settings").forEach(function(row) {
    result[String(row.Key).trim()] = row.Value;
  });
  return result;
}

function keyBy_(rows, keyName) {
  const result = {};
  rows.forEach(function(row) {
    const key = String(row[keyName] || "");
    if (key) {
      result[key] = row;
    }
  });
  return result;
}

function openBook_() {
  const id = PropertiesService.getScriptProperties()
    .getProperty("SPREADSHEET_ID");

  if (!id || id === "YOUR_SPREADSHEET_ID") {
    throw new Error("SPREADSHEET_ID is not configured.");
  }

  return SpreadsheetApp.openById(id);
}

function ensureSheet_(ss, name, headers) {
  let sheet = ss.getSheetByName(name);

  if (!sheet) {
    sheet = ss.insertSheet(name);
  }

  const lastColumn = sheet.getLastColumn();
  const lastRow = sheet.getLastRow();

  if (lastRow === 0 || lastColumn === 0) {
    ensureSheetSize_(sheet, 1, headers.length);
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
    sheet.setFrozenRows(1);
    return;
  }

  const existing = sheet.getRange(
    1,
    1,
    1,
    Math.min(lastColumn, headers.length)
  ).getValues()[0];

  for (let i = 0; i < existing.length; i++) {
    if (String(existing[i]) !== String(headers[i])) {
      throw new Error(
        "Header mismatch in " + name + " at column " + (i + 1)
      );
    }
  }

  if (lastColumn < headers.length) {
    ensureSheetSize_(sheet, 1, headers.length);
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  }

  sheet.setFrozenRows(1);
}

function ensureSheetSize_(sheet, rows, columns) {
  if (sheet.getMaxRows() < rows) {
    sheet.insertRowsAfter(
      sheet.getMaxRows(),
      rows - sheet.getMaxRows()
    );
  }

  if (sheet.getMaxColumns() < columns) {
    sheet.insertColumnsAfter(
      sheet.getMaxColumns(),
      columns - sheet.getMaxColumns()
    );
  }
}

function readObjects_(name) {
  return readObjectsWithRows_(name).map(function(item) {
    return item.object;
  });
}

function readObjectsWithRows_(name) {
  const ss = openBook_();
  const sheet = ss.getSheetByName(name);
  const headers = CFG.SHEETS[name];

  if (!sheet || sheet.getLastRow() < 2) {
    return [];
  }

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

  return values.map(function(row, index) {
    const object = {};
    headers.forEach(function(header, column) {
      object[header] = row[column];
    });

    return {
      row: index + 2,
      object: object
    };
  }).filter(function(item) {
    return Object.keys(item.object).some(function(key) {
      return item.object[key] !== "";
    });
  });
}

function writeObjects_(name, objects) {
  const ss = openBook_();
  const sheet = ss.getSheetByName(name);
  const headers = CFG.SHEETS[name];

  ensureSheetSize_(sheet, Math.max(2, objects.length + 1), headers.length);

  if (sheet.getLastRow() > 1) {
    sheet.getRange(
      2,
      1,
      sheet.getLastRow() - 1,
      headers.length
    ).clearContent();
  }

  if (objects.length === 0) {
    return;
  }

  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);
}

function appendObject_(name, object) {
  const ss = openBook_();
  const sheet = ss.getSheetByName(name);
  const headers = CFG.SHEETS[name];

  sheet.appendRow(headers.map(function(header) {
    return object[header] === undefined ? "" : object[header];
  }));
}

function findObjectByValue_(name, field, value) {
  const rows = readObjectsWithRows_(name);

  for (let i = 0; i < rows.length; i++) {
    if (String(rows[i].object[field]) === String(value)) {
      return rows[i];
    }
  }

  return null;
}

function updateObjectRow_(name, rowNumber, updates) {
  const ss = openBook_();
  const sheet = ss.getSheetByName(name);
  const headers = CFG.SHEETS[name];
  const current = sheet.getRange(
    rowNumber,
    1,
    1,
    headers.length
  ).getValues()[0];

  Object.keys(updates).forEach(function(field) {
    const index = headers.indexOf(field);
    if (index !== -1) {
      current[index] = updates[field];
    }
  });

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

function jsonOutput_(object) {
  return ContentService
    .createTextOutput(JSON.stringify(object))
    .setMimeType(ContentService.MimeType.JSON);
}

Configuration and Deployment

1. Save the script and run setupWorkbook manually.
2. Review the requested permissions. The script requires spreadsheet access, Gmail sending, external HTTP requests for optional AI, trigger management, and script-property access.
3. Open the Apps Script project settings and add WEBHOOK_SECRET with a long random value.
4. Confirm that SPREADSHEET_ID was stored by the setup function. If necessary, add it manually using YOUR_SPREADSHEET_ID as the replacement pattern.
5. Replace YOUR_EMAIL_ADDRESS in Settings with the planning mailbox.
6. Run installTriggers and authorize the scheduled triggers.
7. Deploy the script as a web application that executes as the automation owner. Select an access option permitted by the organization’s policy and compatible with the AppSheet webhook.
8. Copy the deployment URL into the AppSheet webhook task.
9. Use the same secret in the webhook JSON. Keep it visible only to application administrators.
10. Submit a test allocation and inspect Apps Script Executions, Events, CapacityWeekly, DemandWeekly, and Exceptions.

The webhook is the immediate trigger. The five-minute time trigger is the recovery path. Duplicate processing is prevented by EventID, while concurrent calculations are prevented by a script lock.

Execution logs are available in the Apps Script Executions view. Business-level failures are also written to Events and the source record’s ErrorMessage field.

For optional AI, add Script Properties named AI_API_KEY and AI_MODEL. Select a model that supports the configured structured-output format. The API key is sent in the Authorization header as a bearer token. Requests use an HTTP POST with JSON content and retry HTTP 429 and server errors with exponential delays.

If the AI provider or selected model does not support the shown structured-output option, disable the AI trigger and update the request configuration only after validating the provider’s current official API documentation. The core planning automation does not depend on AI.

Failure Handling and Operational Reliability

The Events table acts as a lightweight dead-letter queue for failed webhook events. It shows entity, record ID, attempt count, failure message, and completion time.

Reconciliation consists of comparing Pending and Error source rows with failed Events, confirming that every active employee-scenario-week has a CapacityWeekly row, and reviewing the last successful calculation timestamp.

Rate-limit and quota monitoring remains important. AppSheet synchronization volume, Gmail sending, Apps Script runtime, spreadsheet size, and optional AI API use must remain within the organization’s current service limits.

A Complete Example

A project manager enters a new electrical commissioning requirement for project PRJ-24017:

• Requirement ID: REQ-A81F2C
• Role: Controls Engineer
• Required skill: PLC-ADV
• Minimum proficiency: 4
• Date range: August 3 through August 21
• Demand: 32 hours per week
• Scenario: Baseline
• Priority: High

The supervisor proposes employee EMP-1042 for 24 hours per week. AppSheet creates allocation ALC-91D4BE, writes it to Allocations with status Proposed, and marks AutomationStatus as Pending.

The AppSheet bot sends an event such as Allocation-ALC-91D4BE-2026-07-15T10:30:00. Apps Script records that event as Processing and reads the operational tables.

The script confirms that the employee has proficiency 4 for PLC-ADV. It then finds these validated facts for the week beginning August 3:

• Base capacity: 40 hours
• Approved leave: 8 hours
• Available capacity: 32 hours
• Existing confirmed allocation: 20 hours
• New proposed allocation: 24 hours
• Total allocation: 44 hours
• Overallocated amount: 12 hours
• Requirement gap: 8 hours because only 24 of 32 required hours are named

The automation writes a CapacityWeekly record for Baseline|EMP-1042|2026-08-03. It creates a high-severity employee-overallocated exception, a medium potential-leave-overlap exception, and a medium unfilled-requirement exception.

The project manager receives one grouped Gmail alert. The allocation remains Proposed. The planning manager can change the assignment, split eight hours to another qualified employee, alter the dates, or request a documented override. The script does not choose among those options.

The manager assigns employee EMP-1088 for the missing eight hours and reduces EMP-1042 to 12 hours. A second event recalculates the plan. The capacity and requirement exceptions are no longer detected, so their statuses become Resolved. The planning manager confirms both allocations, with approver and timestamp stored in the register.

Implementation Cost

All figures below are representative budgeting assumptions, not published product pricing or verified client results. Licensing must be confirmed with current vendors and adjusted for the number and type of users.

An externally led implementation could instead be budgeted as a professional services engagement. A representative planning range might be $12,000 to $18,000 depending on data cleanup, security review, AppSheet complexity, integrations, training, and support. This would normally replace some internal build effort rather than simply being added to it.

Estimated Time and Cost Savings

The representative estimate uses these assumptions:

• 220 planning changes per month
• 13 minutes of current handling per change
• 4 minutes of handling after core automation
• 12 percent of records require exception review
• 8 minutes of review for each exception record
• 3 hours of monthly system maintenance
• $48 loaded hourly labor cost for planning work
• $220 recurring monthly core tool allowance
• $8,000 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 may represent additional planning capacity, quicker response to project changes, reduced overtime, fewer administrative updates, and less dependency on the planning manager.

Non-financial benefits include clearer ownership, fewer follow-up emails, more consistent statuses, earlier visibility of shortages, auditable overrides, dependable scenario comparison, and better planning-meeting preparation.

Readers should replace the monthly volume, handling times, exception rate, review time, labor cost, licensing allowance, maintenance effort, and implementation cost with their own measured figures.

Adding AI to the Automation

AI should be added only after the underlying project, employee, skill, leave, allocation, and exception data is validated. It is not needed for capacity arithmetic, date comparison, exact duplicate detection, approval thresholds, or access control.

Useful optional AI applications include:

• Summarizing a long exception queue for the planning meeting
• Grouping related conflicts across projects and weeks
• Drafting questions for project managers
• Identifying descriptions that appear incomplete or inconsistent
• Summarizing scenario differences after deterministic calculations
• Supporting semantic search across approved planning notes

The core automation provides mobile entry, synchronization, capacity calculations, validation, exception generation, reminders, and reporting. AI adds support for reading and summarizing unstructured exception information. It does not create the underlying operational accuracy.

The Recommended AI Enhancement

The recommended enhancement is a planning brief generated from validated open exceptions. It runs manually or on a controlled daily schedule after the deterministic calculation completes.

• Trigger: Planning manager action or daily scheduled execution
• AI input: Exception ID, type, severity, scenario, project ID, pseudonymous employee reference, week, and validated description
• Excluded data: Names, emails, leave reasons, medical information, protected characteristics, customer details, and document contents
• Output: Structured JSON containing an overview, prioritized exceptions, suggested review steps, questions, and data-quality notes
• Validation: Every returned exception ID must exist in the input and every risk value must use the allowed enumeration
• Record update: A new AI_Briefs row is stored with Pending Human Review status
• Human review: The planning manager accepts, edits, or discards the briefing
• Failure behavior: No operational records change; the planner uses the normal exception dashboard

The reusable system instruction is:

You summarize validated resource-planning exceptions for a human operations planner. Use only facts present in the supplied JSON. Do not infer employee performance, health, protected characteristics, or customer information. Do not approve, reject, assign, or override work. Prioritize by supplied severity, timing, and explicit capacity gap. Reference only exception IDs present in the input. Return JSON that exactly matches the required schema.

The reusable user prompt is:

Create the planning brief from this validated JSON:

{
  "period_start": "YYYY-MM-DD",
  "horizon_weeks": 16,
  "exceptions": [
    {
      "exception_id": "EX-IDENTIFIER",
      "type": "EMPLOYEE_OVERALLOCATED",
      "severity": "High",
      "scenario_id": "Baseline",
      "project_id": "PRJ-ID",
      "employee_ref": "EMP-ID",
      "week_start": "YYYY-MM-DD",
      "description": "Validated exception description"
    }
  ]
}

A valid output follows this structure:

{
  "overview": "Three high-priority conflicts require planning review.",
  "priorities": [
    {
      "exception_id": "EX-1234567890ABCDEF",
      "risk": "high",
      "summary": "The employee is allocated above validated weekly availability.",
      "recommended_next_step": "Review allocation hours and qualified alternatives.",
      "questions_for_planner": [
        "Can part of the allocation move to another week?",
        "Is another qualified employee available?"
      ]
    }
  ],
  "data_quality_notes": [
    "One requirement does not have a named allocation."
  ]
}

Low-confidence handling is conservative. The implementation does not ask the model for a numeric confidence score because self-reported model confidence is not a dependable control. Any unknown exception ID, malformed JSON, unsupported risk value, or service failure causes rejection of the entire brief.

Benefits of the AI Enhancement

• Less planner time spent reading repetitive exception descriptions
• More consistent briefing structure across planning cycles
• Quicker identification of the conflicts that require discussion first
• Suggested questions that help managers prepare for the meeting
• Better handling of a growing queue of unstructured resolution notes
• A reviewable summary without changing validated planning records

These are AI-specific benefits. Capacity visibility, exception accuracy, status ownership, reminders, and reporting already come from the rule-based system.

What Remains Rule-Based or Human-Controlled

Estimating the Additional Value of AI

The AI estimate uses 22 planning briefing cycles per month. Without AI, the planner spends approximately 15 minutes reviewing and drafting each exception summary. With AI, human review takes six minutes per briefing.

• AI correction rate: 15 percent of briefings
• Correction effort: 4 additional minutes
• AI service failure rate assumption: 3 percent
• Fallback effort after failure: 15 minutes
• AI usage allowance: $18 per month
• Loaded planning labor cost: $48 per hour

This estimate does not assume that AI eliminates review or errors. Its value depends on exception volume, briefing frequency, output quality, model cost, and the planner’s actual editing time.

Testing Checklist

Use sample data and test accounts before processing real employee or project information.

Ongoing Maintenance

Every script, AppSheet expression, table field, role, and recovery procedure should be documented. Structural changes must be tested on a copy of the workbook before production deployment.

At least two people should know how to inspect Apps Script executions, disable triggers, update the web-app deployment, restore a workbook copy, and reprocess a failed record.

When to Move to Dedicated Software

The implementation should not be replaced merely because it uses low-code and spreadsheet components. It remains appropriate while volumes, calculations, controls, and maintenance are manageable.

Review dedicated resource-management, professional-services automation, manufacturing planning, workforce scheduling, or project portfolio software when several of these conditions appear:

• Hundreds of concurrent projects or thousands of active allocations
• Daily or shift-level scheduling rather than weekly capacity planning
• Complex calendars, overtime rules, union rules, or location-specific working patterns
• Multiple legal entities, countries, plants, or time zones
• Formal regulatory validation or immutable audit requirements
• Advanced skill certification, expiry, and competency workflows
• Real-time integration with HR, payroll, ERP, manufacturing, or project accounting systems
• Persistent API, spreadsheet-size, synchronization, or script-runtime constraints
• Increasing exception rates that require scheduling optimization algorithms
• Customer-facing portals or external staffing partners
• Offline planning requirements beyond the mobile platform’s practical limits
• Advanced forecasting, simulation, financial costing, or portfolio optimization
• Security requirements that prohibit the selected webhook architecture
• Excessive administrator effort or dependence on unsupported custom code
• A requirement for contractual vendor support and formal service commitments

A migration can preserve the data definitions developed here. Projects, employees, skills, requirements, allocations, scenarios, statuses, exceptions, and audit events provide a useful foundation for evaluating dedicated software.

Implementation Checklist

• Confirm planning volume, horizon, roles, skills, scenarios, and approval rules.
• Select AppSheet, Google Sheets, Apps Script, Gmail, and Drive responsibilities.
• Create the automation owner, backup owner, planning mailbox, and test users.
• Confirm licensing features and organizational webhook policy.
• Create the restricted Drive structure and planning workbook.
• Run the workbook setup script and verify exact headers.
• Load and validate employees, users, projects, skills, and baseline records.
• Configure AppSheet keys, references, enum values, forms, and validations.
• Apply security filters and role-based actions.
• Create baseline and scenario views.
• Configure Pending status updates and webhook payloads.
• Store spreadsheet ID, webhook secret, planning email, and optional AI credentials securely.
• Deploy and authorize the Apps Script web app.
• Install fallback-processing and reminder triggers.
• Verify field mappings between AppSheet, Sheets, Apps Script, and Gmail.
• Test capacity, leave, demand, skill, duplicate, and date calculations.
• Configure approval, rejection, return, delegation, and override actions.
• Create new-work, overdue, exception, utilization, gap, and failure views.
• Configure document permissions and optional skill-evidence handling.
• Run normal, failure, retry, security, and reconciliation tests with sample data.
• Pilot with a limited project group before full activation.
• Document rollback, recovery, support, and escalation procedures.
• Replace representative implementation and savings assumptions with measured figures.
• Add AI only after deterministic calculations and exception data are reliable.
• Require human review of every AI briefing.
• Assign primary and backup maintenance owners.
• Review volume, security, integration, audit, and dedicated-software upgrade criteria annually.