Automating Incident Management and Post-Incident Reviews

The Business Situation

Alder Peak Components is a fictional 86-person manufacturer that uses networked production equipment, cloud applications, shared workstations, and barcode-based material tracking. Its IT and infrastructure function consists of an IT manager and a systems administrator. The operations team includes an operations manager and four production supervisors.

The business uses Google Workspace for email, documents, spreadsheets, and internal communication. Operational incidents are normally discussed in chat and resolved by whichever technical or production employee is available. Important details may remain in chat messages, individual notes, or email threads.

Alder Peak handles approximately 12 reportable IT or production-technology incidents each month. Eight normally require a formal post-incident review because they are higher-severity events, repeat incidents, or events that interrupt production.

The existing process has no dependable incident identifier, central owner, consistent severity scale, structured recovery record, or closure approval. Managers can usually determine whether an incident was resolved, but reconstructing what happened, when it happened, and what corrective work remains can take substantial effort.

The business wants a practical incident management system using its existing Google Workspace environment. Submissions must create ticket records, generate incident IDs, assign owners, send updates, record resolution, track corrective actions, and require approval before closure.

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 workflow proceeds as follows:

1. An employee notices a production-system, network, workstation, application, or equipment-control issue.
2. The employee posts a message in an operations or IT chat channel.
3. An available technician or supervisor begins investigating.
4. Progress is discussed across chat, email, telephone calls, and conversations on the production floor.
5. Once service is restored, the technical employee posts a short update or tells the supervisor that the incident is resolved.
6. For serious incidents, the IT manager later asks participants to reconstruct the timeline and identify the root cause.
7. Corrective actions are written in an email or a general spreadsheet, without a dependable relationship to the original incident.
8. There is no formal approval confirming that recovery, root cause, corrective actions, and communication obligations have been completed.

The primary dependency is not the availability of technology. It is the absence of a controlled workflow that converts informal communication into assigned, measurable, and reviewable records.

What the New System Needed to Do

The business also wants the option to generate a draft post-incident timeline from notes that an authorized employee has approved for AI processing. This enhancement must not read unrestricted chat history or approve an incident automatically.

Implementation Approaches Considered

Improved Manual Template

A protected spreadsheet template would improve consistency, but employees would still need to create IDs, copy information, assign owners, send reminders, update action registers, and verify closure manually. This option does not address dependency on individual employees.

Forms with Basic Notifications

Google Forms can write responses to Google Sheets and issue simple response notifications. That is useful for intake, but it does not implement a complete incident lifecycle. The business would still need controlled updates, action relationships, retries, approval validation, and consolidated reporting.

Google Workspace with Apps Script

This option retains familiar tools while adding deterministic workflow logic. Apps Script can process form submissions, generate IDs, route ownership, maintain related records, create Drive folders, queue Gmail notifications, send reminders, and record failures.

Dedicated Incident-Management Software

A specialized platform would provide stronger service catalogs, service-level management, configuration relationships, on-call scheduling, and vendor support. Alder Peak’s current volume and relatively small support team do not yet justify the migration and recurring administration involved.

The Workspace and Apps Script approach was selected because it meets the current requirements without forcing employees to adopt a large service-management platform. The tradeoff is that the company must maintain the script and governance controls as an operational system.

The Selected Solution

The existing chat platform remains available for immediate coordination during an active incident. It is not treated as the system of record. Employees must submit significant facts through the update form so that they become part of the controlled timeline.

The automation removes manual ticket-number creation, spreadsheet copying, owner lookup, folder creation, routine email preparation, scheduled reminders, and status-report compilation.

Humans continue to confirm severity, investigate the event, determine root cause, write or approve corrective actions, decide whether recovery is adequate, and approve final closure.

System Architecture and Data Flow

• Intake: A restricted Google Form captures the initial incident report.
• System of record: A Google Sheets workbook stores incidents and related operational records.
• Automation layer: A spreadsheet-bound Apps Script project processes submissions and scheduled work.
• Document storage: Google Drive stores one controlled folder per incident.
• Notifications: Gmail sends queued assignments, updates, reminders, escalations, and approval messages.
• Reporting: Google Sheets formulas, filters, and pivot tables provide operational views.
• AI layer: An optional API call drafts a timeline from notes explicitly approved for AI use.

1. Incident submission: An employee submits the Incident Intake Form. Google Forms writes the response to the intake-response sheet. A spreadsheet form-submit trigger receives the response row and its source-sheet identity.

2. Input validation: Apps Script verifies required fields, the verified reporter email, allowed severity values, the affected-service directory entry, and the detection timestamp. Invalid submissions are recorded in the failure queue rather than becoming incomplete incident records.

3. Duplicate handling: The script creates an event key from the response sheet ID and response row. An already completed event key is ignored. A separate content check looks for an open incident with the same normalized summary and service within six hours. A possible business duplicate is sent to manual review instead of being silently merged.

4. Record creation: The script obtains a lock, increments a year-specific sequence, verifies that the ID is unused, and creates an incident such as INC-2026-0043.

5. Assignment: The affected service is matched to the Directory sheet. The incident receives a primary owner, backup owner for escalation, and closure approver.

6. Document handling: Apps Script creates or reuses a Drive folder named with the incident ID and summary. Uploaded evidence is moved into that folder, and the folder URL is written back to the incident record.

7. Notification: The assignment email is written to a Notifications queue with a unique notification key. A time-driven process sends it through Gmail and records the send result.

8. Operational updates: Authorized employees submit the update form. Apps Script validates the incident ID, actor, update type, status transition, timestamps, and any action fields.

9. Related records: Timeline events and corrective actions are written to their own sheets using the incident ID as the relationship key. Returned timeline and action IDs are retained in their corresponding records.

10. Recovery and review: Recovery confirmation moves the incident into Monitoring. Root cause and corrective actions are then recorded. The owner requests closure after required information and actions are complete.

11. Closure approval: The designated approver or an active delegate approves closure or returns the incident for more information. Approval produces an audit entry and final notification.

12. Failure path: Processing errors are recorded with the event payload, incident ID, error message, retry count, and status. Scheduled retries reprocess idempotently. Repeated failures move to manual review.

Data Structure

The workbook contains related sheets rather than placing the entire workflow in one wide response table. The Incident ID is the parent key used by timeline, action, notification, audit, and AI-draft records.

Related Records

Timeline

Stores multiple chronological events for one incident. Each entry has a Timeline ID, occurrence time, type, note, actor, source event key, AI approval flag, and automation status.

Actions

Stores multiple corrective actions for one incident. Each action has an Action ID, description, owner, due date, status, creation time, completion time, and source event key.

Audit Log

Stores state changes and approval evidence, including previous state, new state, actor, timestamp, and source event key.

Processed Events

Stores each form event key and whether processing is in progress, complete, or failed.

Notifications

Acts as an outbound email queue with recipient, subject, body, attempts, next-attempt time, send time, and error.

Failures

Stores recoverable event payloads and unrecovered errors.

Directory

Maps each affected service to its primary owner, backup owner, and closure approver.

Delegations

Authorizes a named delegate to act for an unavailable approver during a defined period.

The child sheets should use data validation where possible, but Apps Script remains the authoritative validator. Spreadsheet validation improves user entry; it does not replace server-side workflow checks.

Workflow Statuses and Ownership

A generic progress update cannot move an incident directly to Closed. Closure is a separate action requiring an authenticated approver. A returned closure moves the incident back to Monitoring and records the approver’s reason.

Step-by-Step Implementation

Step 1: Prepare the Accounts and Permissions

1. Create or identify a Google Workspace account that will own the spreadsheet, forms, Apps Script project, triggers, and Drive root folder. A controlled automation owner is preferable to an employee account that may be removed unexpectedly.
2. Confirm that the account can create installable Apps Script triggers, send Gmail messages, create Drive folders, and access uploaded files.
3. Create a shared Drive folder or controlled My Drive folder named Incident Management Records. Copy its folder ID from the URL and retain it for the script configuration.
4. Create a Google Sheets workbook named Incident Management Register. The Apps Script project will be bound to this workbook.
5. Create two restricted Google Forms: Incident Intake and Incident Update and Closure.
6. Require verified email collection and restrict responses to authorized organizational users where the Workspace configuration supports that control.
7. Link both forms to the same workbook. Rename the response sheets to Incident Intake Responses and Incident Update Responses.
8. Identify the IT manager, systems administrator, operations manager, service owners, backup owners, and closure approvers.
9. Create at least two test users: one incident reporter and one approver. Do not test closure approval using only the automation owner.
10. Set the spreadsheet and Apps Script project to the business time zone. Date parsing, reminder timing, and dashboard aging should use the same time zone.

No external service account is required. Installable triggers run under the Google account that creates them. That account’s access and sending limits therefore affect the automation.

The required Google Workspace subscription is one that supports the organization’s intended Forms, Drive, Gmail, sharing, identity, and file-upload controls. Product licensing changes over time, so the implementation should verify required features against the organization’s current agreement rather than assuming a particular plan name.

Step 2: Build the Intake

Incident Intake Form

The form automatically supplies the response timestamp and verified email address. If the response sheet uses a localized heading instead of Email Address, update the corresponding script field name.

The form confirmation should state that submission records the incident but does not replace emergency or safety communication. It should instruct employees to use the established urgent escalation channel for immediate safety or production risk.

Incident Update and Closure Form

Use these exact update-type values:

• Progress note
• Communication sent
• Recovery confirmed
• Root cause recorded
• Add corrective action
• Complete corrective action
• Request closure
• Approve closure
• Return for more information

Google Forms can route respondents to different sections based on a multiple-choice answer. Use this to show action fields only for corrective-action updates and to require a note for recovery, root cause, and returned closure. Apps Script must still validate every conditional requirement because form sections alone are not an authorization control.

Duplicate trigger events are prevented with the source event key. Accidental duplicate user submissions are not automatically deleted because two similar reports can represent separate incidents. They are marked for manual comparison.

Step 3: Create the System of Record

Open the Incident Management Register workbook, select the Apps Script option from the spreadsheet’s extension menu, and paste the complete core script provided later in this article. Run setupWorkbook once.

The setup function creates these sheets:

• Incidents
• Timeline
• Actions
• Audit Log
• Processed Events
• Notifications
• Failures
• Directory
• Delegations
• Dashboard
• AI Drafts

Do not rename these system sheets unless the corresponding constants in the script are also changed.

The incident ID is generated under an Apps Script lock. The sequence is stored in script properties by year and checked against the Incidents sheet before use. This prevents ordinary concurrent submissions from receiving the same ID.

Populate the Directory sheet with one row per affected service. Example service values for this scenario are:

• Corporate network
• Production workstations
• Packaging line control network
• Barcode and inventory application
• Google Workspace

Use organizational email addresses rather than personal accounts. Set Active to TRUE. An inactive service cannot receive new incidents.

Create protected filter views for open incidents, manual reviews, awaiting approval, failed automation, and recently closed incidents. Protect system-generated columns from ordinary editors. Owners should submit lifecycle changes through the update form rather than editing the incident row directly.

Step 4: Connect the Tools

The forms connect to the workbook through their response destinations. Apps Script connects to form submissions using an installable spreadsheet form-submit trigger. It connects to Drive and Gmail using the authorization granted to the trigger owner.

Each destination action returns or generates an identifier:

• The incident record receives an Incident ID.
• The Drive operation returns a folder URL.
• Timeline entries receive stable Timeline IDs.
• Corrective actions receive Action IDs.
• Outbound messages receive Notification IDs.
• Form submissions retain the source response row and event key.

If a connection fails, the event remains recoverable from the Processed Events and Failures sheets. The original form-response row is never deleted.

Step 5: Build the Core Automation

Automation 1: Create and Assign an Incident

• Trigger: A row is submitted to Incident Intake Responses.
• Conditions: Required values are present; severity and communication values are allowed; the reporter email is valid; the service is active in Directory.
• Actions: Create the event key, generate an incident ID, check for possible duplicates, assign an owner, create the record, create the Drive folder, move evidence, create timeline and audit entries, and queue notifications.
• Fields updated: All initial incident fields, folder URL, automation status, last run time, and possible duplicate link.
• Notification: Assignment to the owner; additional escalation for SEV1 or a possible duplicate.
• Exception: Invalid or failed events are recorded in Failures and retried without creating a second incident.

Automation 2: Record Progress and Communications

• Trigger: Progress note or Communication sent is submitted through the update form.
• Conditions: Incident exists; actor is an authorized participant; status transition is permitted; note and occurrence time are valid.
• Actions: Create a timeline record, update the incident timestamp and optional status, create an audit entry, and queue appropriate email.
• Fields updated: Updated At, Status, Communication State, Automation Status, and Error Message.
• Notification: Owner receives an operational update; the reporter receives communications only when the incident requires them.
• Exception: Unauthorized actors or invalid transitions are rejected and logged.

Automation 3: Record Recovery and Root Cause

• Trigger: Recovery confirmed or Root cause recorded is submitted.
• Conditions: Actor is authorized and the note is present.
• Actions: Write the recovery timestamp and summary or root-cause text, add a timeline entry, move the status to Monitoring when recovery is confirmed, and create audit evidence.
• Fields updated: Recovery At, Recovery Summary, Root Cause, Status, Updated At, and automation fields.
• Notification: Owner and approved recipients receive a recovery update.
• Exception: Closure cannot proceed if recovery or root cause remains blank.

Automation 4: Manage Corrective Actions

• Trigger: Add corrective action or Complete corrective action is submitted.
• Conditions: The incident exists; the actor is authorized; description, owner, and due date are valid for creation; Action ID is valid for completion.
• Actions: Create or update the related Actions row, write a timeline entry, update the parent incident, and queue notification.
• Fields updated: Action status, completion time, incident Updated At, and automation fields.
• Notification: Action owner receives the new assignment; incident owner receives completion updates.
• Exception: Closure requests are rejected while any related action remains open.

Automation 5: Approve Closure

• Trigger: Request closure, Approve closure, or Return for more information is submitted.
• Conditions: Recovery and root cause exist; open corrective-action count is zero; the requester is the incident owner; the approver is the designated person or an active delegate.
• Actions: Move to Awaiting Closure Approval, send the approval request, validate the decision, record approval evidence, and either close the incident or return it to Monitoring.
• Fields updated: Closure Approver, Approval Status, Status, Closed At, Updated At, and automation fields.
• Notification: Approver receives the request; owner and reporter receive the final result.
• Exception: Invalid approval identities, open actions, and missing review information prevent closure.

Step 6: Add Approvals, Reminders, and Escalations

Alder Peak uses one sequential closure approval. The incident owner completes the review and requests closure. The service’s closure approver then approves or returns it. Parallel approval was not selected because the current incidents normally have one accountable operational approver.

If a future policy requires both IT and operations approval, add a separate Approval Decisions sheet containing one row per required approver. Closure should occur only when every required row is Approved.

These timings are representative business rules, not universal incident-management standards. Each organization should define them according to operational risk and staffing.

The Delegations sheet supports unavailable approvers. A delegation row contains the principal approver, delegate, start time, end time, and Active flag. The original approver remains on the incident record, while the audit log records the delegate who actually submitted the decision.

Rejection is implemented as Return for more information. A return requires a note, records Approval Status as Returned, and moves the incident to Monitoring. The owner must address the issue and submit a new closure request.

Step 7: Add Documents and File Management

Create a root folder that only the incident-management team and automation owner can access. Do not use unrestricted public links.

The script creates a folder with this naming pattern:

INC-2026-0043 - Packaging line control network unavailable

Uploaded intake and update files are moved into the incident folder. The folder inherits access from the controlled root folder. The incident row stores the folder URL instead of duplicating files in the spreadsheet.

• Use the Incident ID at the start of every manually added file name.
• Retain original evidence files rather than replacing them.
• If an updated file is needed, add a version indicator and retain the prior version according to policy.
• Restrict upload types and file sizes in the form.
• Do not upload passwords, private keys, authentication tokens, or unnecessary personal information.
• Archive closed incident folders according to the organization’s retention schedule.
• If an upload cannot be moved, the incident remains recoverable and the error is recorded for retry.

The implementation uses Drive file moves that are suitable for files owned or accessible by the automation account. Organizations using shared drives or more complex ownership rules should test file movement with their exact Drive configuration. An advanced Drive API implementation may be preferable where ownership or cross-drive moves are restricted.

Step 8: Add Reporting and Operational Views

The Dashboard sheet provides summary counts. Create protected filter views or separate query sheets for:

• New and Assigned incidents
• Incidents awaiting owner action
• Overdue incidents based on severity
• Incomplete records
• Manual Review incidents
• Returned closure requests
• Incidents by owner and service
• Open corrective actions
• Corrective actions due in the next seven days
• Overdue corrective actions
• Recently closed incidents
• Failed automation events
• Dead-letter notifications
• Average detection-to-recovery time
• Average recovery-to-closure time
• Incident volume by severity, status, and service

Useful calculated durations are:

Recovery duration hours = (Recovery At - Detected At) × 24
Review duration hours = (Closed At - Recovery At) × 24
Incident age hours = (Current time - Detected At) × 24

Use blank checks before calculating durations so open incidents do not produce misleading values. A pivot table can summarize incident count by month, affected service, severity, owner, and root-cause category if a controlled category field is later added.

The workbook updates immediately after form processing. Pivot tables and formulas should be checked by the IT manager weekly. Alert thresholds should focus on open SEV1 incidents, overdue corrective actions, incidents awaiting closure for more than 24 hours, automation failures, and dead-letter email records.

Step 9: Add Security and Governance Controls

• Restrict both forms to approved internal identities where possible.
• Use verified email collection rather than trusting a typed email field.
• Protect incident IDs, ownership, status, approval, audit, and automation columns.
• Allow lifecycle changes through the update form rather than direct spreadsheet edits.
• Limit workbook access to the IT manager, systems administrator, and authorized operations managers.
• Grant dashboard-only users view access when edit access is unnecessary.
• Restrict the Drive root folder and disable public link sharing.
• Store API keys in Apps Script properties, never in spreadsheet cells or source-code literals.
• Review trigger ownership before an employee changes roles or leaves.
• Retain Processed Events, Audit Log, and Failures as operational evidence.
• Back up the workbook and script before significant changes.
• Document the lawful and policy basis for any employee-related information stored in incident records.
• Do not send unrestricted operational notes to an external AI service.
• Require human review of every AI-generated timeline.

Google Sheets is not a substitute for a specialized regulated records platform. Organizations subject to formal safety, privacy, legal-hold, cybersecurity, or industry-specific obligations should validate retention, access, evidence integrity, and regional processing requirements before deployment.

Step 10: Deploy and Test

1. Build the forms and workbook in a non-production test folder.
2. Paste the script into the spreadsheet-bound Apps Script project.
3. Replace the spreadsheet ID, root folder ID, administrator email, and update form URL.
4. Run setupWorkbook and inspect all generated sheets.
5. Replace the Directory placeholder row with valid test services and internal test accounts.
6. Run installTriggers. Review and grant the requested spreadsheet, Gmail, Drive, and trigger permissions.
7. Submit low-risk test incidents for every severity and affected service.
8. Use separate reporter, owner, action-owner, and approver accounts during testing.
9. Inspect Apps Script Executions, Processed Events, Notifications, Failures, Timeline, Actions, and Audit Log after each test.
10. Complete user acceptance testing with the IT manager and one operations supervisor.
11. Pilot the process with one production area or one incident category.
12. Document how employees report an incident, add updates, complete actions, approve closure, and recover a failed event.
13. Activate the process for all in-scope incidents after the pilot issues are corrected.
14. Retain the previous manual template for a short rollback period, but stop parallel entry after the controlled launch date.

The IT manager owns the operational workflow. The systems administrator is the backup technical owner. Launch communication should state which incidents must be submitted, how emergency escalation still works, and why direct spreadsheet editing is prohibited.

Code and Configuration

The following complete Google Apps Script is placed in the Apps Script project bound to the Incident Management Register spreadsheet. Open the spreadsheet, open the Apps Script editor from the extension menu, replace the default file contents, and paste this script.

Replace YOUR_SPREADSHEET_ID, YOUR_FOLDER_ID, YOUR_EMAIL_ADDRESS, and YOUR_UPDATE_FORM_URL. The trigger owner will be asked for permission to read and write the workbook, manage the configured Drive folder and files, send Gmail messages, and create triggers.

const CONFIG = Object.freeze({
  SPREADSHEET_ID: 'YOUR_SPREADSHEET_ID',
  ROOT_FOLDER_ID: 'YOUR_FOLDER_ID',
  ADMIN_EMAIL: 'YOUR_EMAIL_ADDRESS',
  UPDATE_FORM_URL: 'YOUR_UPDATE_FORM_URL',
  INTAKE_RESPONSE_SHEET: 'Incident Intake Responses',
  UPDATE_RESPONSE_SHEET: 'Incident Update Responses',
  MAX_EVENT_RETRIES: 3,
  MAX_EMAIL_ATTEMPTS: 5
});

const SHEETS = Object.freeze({
  INCIDENTS: 'Incidents',
  TIMELINE: 'Timeline',
  ACTIONS: 'Actions',
  AUDIT: 'Audit Log',
  EVENTS: 'Processed Events',
  NOTIFICATIONS: 'Notifications',
  FAILURES: 'Failures',
  DIRECTORY: 'Directory',
  DELEGATIONS: 'Delegations',
  DASHBOARD: 'Dashboard',
  AI_DRAFTS: 'AI Drafts'
});

const HEADERS = Object.freeze({
  [SHEETS.INCIDENTS]: [
    'Incident ID', 'Created At', 'Updated At', 'Form Event Key',
    'Detected At', 'Summary', 'Description', 'Severity',
    'Affected Service', 'Impact', 'Reporter Email', 'Owner Email',
    'Status', 'Communications Required', 'Communication State',
    'Recovery At', 'Recovery Summary', 'Root Cause',
    'Closure Approver Email', 'Approval Status', 'Closed At',
    'Evidence Folder URL', 'External Form Row', 'Automation Status',
    'Last Automation Run', 'Retry Count', 'Error Message', 'Notes',
    'Potential Duplicate Of'
  ],
  [SHEETS.TIMELINE]: [
    'Timeline ID', 'Incident ID', 'Occurred At', 'Logged At', 'Type',
    'Note', 'Actor Email', 'Source Event Key', 'Approved for AI',
    'Automation Status'
  ],
  [SHEETS.ACTIONS]: [
    'Action ID', 'Incident ID', 'Description', 'Owner Email',
    'Due Date', 'Status', 'Created At', 'Completed At',
    'Source Event Key', 'Completion Note'
  ],
  [SHEETS.AUDIT]: [
    'Audit ID', 'Incident ID', 'Timestamp', 'Actor Email', 'Action',
    'Previous State', 'New State', 'Source Event Key', 'Details'
  ],
  [SHEETS.EVENTS]: [
    'Event Key', 'Source Sheet', 'Source Row', 'Incident ID',
    'Processed At', 'Status', 'Payload JSON', 'Error'
  ],
  [SHEETS.NOTIFICATIONS]: [
    'Notification ID', 'Incident ID', 'Created At', 'To', 'Subject',
    'Body', 'Status', 'Attempt Count', 'Next Attempt At', 'Sent At',
    'Last Error'
  ],
  [SHEETS.FAILURES]: [
    'Failure ID', 'Event Key', 'Source Sheet', 'Source Row',
    'Incident ID', 'Failed At', 'Error', 'Retry Count', 'Status',
    'Payload JSON', 'Resolution Notes'
  ],
  [SHEETS.DIRECTORY]: [
    'Affected Service', 'Primary Owner Email', 'Backup Owner Email',
    'Closure Approver Email', 'Active'
  ],
  [SHEETS.DELEGATIONS]: [
    'Principal Email', 'Delegate Email', 'Start At', 'End At', 'Active'
  ],
  [SHEETS.DASHBOARD]: ['Metric', 'Value'],
  [SHEETS.AI_DRAFTS]: [
    'Draft ID', 'Incident ID', 'Created At', 'Created By', 'Model',
    'Prompt Version', 'Input Note IDs', 'Draft JSON',
    'Validation Status', 'Review Status', 'Reviewer', 'Reviewed At',
    'Error'
  ]
});

const ALLOWED_SEVERITIES = ['SEV1', 'SEV2', 'SEV3', 'SEV4'];
const ALLOWED_COMMUNICATIONS = ['Yes', 'No'];
const UPDATE_TYPES = [
  'Progress note',
  'Communication sent',
  'Recovery confirmed',
  'Root cause recorded',
  'Add corrective action',
  'Complete corrective action',
  'Request closure',
  'Approve closure',
  'Return for more information'
];

const STATUS_TRANSITIONS = Object.freeze({
  'Assigned': ['Investigating', 'Mitigating', 'Monitoring'],
  'Manual Review': ['Assigned', 'Investigating'],
  'Investigating': ['Mitigating', 'Monitoring'],
  'Mitigating': ['Investigating', 'Monitoring'],
  'Monitoring': ['Investigating', 'Mitigating', 'Awaiting Closure Approval'],
  'Awaiting Closure Approval': ['Monitoring', 'Closed'],
  'Closed': []
});

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Incident Automation')
    .addItem('Set up workbook', 'setupWorkbook')
    .addItem('Install triggers', 'installTriggers')
    .addSeparator()
    .addItem('Process notification queue', 'processNotificationQueue')
    .addItem('Send scheduled reminders', 'sendScheduledReminders')
    .addItem('Retry failed events', 'retryFailedEvents')
    .addItem('Run reconciliation', 'reconcileAutomation')
    .addToUi();
}

function setupWorkbook() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  Object.keys(HEADERS).forEach(function(sheetName) {
    ensureSheet_(ss, sheetName, HEADERS[sheetName]);
  });

  const directory = ss.getSheetByName(SHEETS.DIRECTORY);
  if (directory.getLastRow() === 1) {
    directory.appendRow([
      'REPLACE_WITH_AFFECTED_SERVICE',
      'YOUR_PRIMARY_OWNER_EMAIL',
      'YOUR_BACKUP_OWNER_EMAIL',
      'YOUR_CLOSURE_APPROVER_EMAIL',
      false
    ]);
  }

  setupDashboard_(ss);
  applyValidations_(ss);
  SpreadsheetApp.flush();
  console.log('Workbook setup completed.');
}

function installTriggers() {
  validateConfiguration_();

  const handlers = [
    'onSpreadsheetFormSubmit',
    'processNotificationQueue',
    'sendScheduledReminders',
    'retryFailedEvents',
    'reconcileAutomation'
  ];

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

  const ss = getSs_();

  ScriptApp.newTrigger('onSpreadsheetFormSubmit')
    .forSpreadsheet(ss)
    .onFormSubmit()
    .create();

  ScriptApp.newTrigger('processNotificationQueue')
    .timeBased()
    .everyMinutes(5)
    .create();

  ScriptApp.newTrigger('sendScheduledReminders')
    .timeBased()
    .everyMinutes(15)
    .create();

  ScriptApp.newTrigger('retryFailedEvents')
    .timeBased()
    .everyMinutes(15)
    .create();

  ScriptApp.newTrigger('reconcileAutomation')
    .timeBased()
    .everyDays(1)
    .atHour(2)
    .create();

  console.log('Installable triggers created.');
}

function onSpreadsheetFormSubmit(e) {
  if (!e || !e.range) {
    throw new Error('This function requires a spreadsheet form-submit event.');
  }

  const sourceSheet = e.range.getSheet();
  const sourceSheetName = sourceSheet.getName();
  const eventKey = sourceSheet.getSheetId() + ':' + e.range.getRow();
  const payload = {
    sheetName: sourceSheetName,
    row: e.range.getRow(),
    named: namedValuesFromRange_(e.range)
  };

  if (!beginEvent_(eventKey, payload)) {
    console.log('Duplicate completed event ignored: ' + eventKey);
    return;
  }

  let incidentId = '';

  try {
    incidentId = routePayload_(payload, eventKey);
    completeEvent_(eventKey, incidentId);
    markIncidentAutomation_(incidentId, 'Complete', '');
    processNotificationQueue();
  } catch (error) {
    incidentId = incidentId ||
      String(payload.named['Incident ID'] || '') ||
      findIncidentIdByEvent_(eventKey);

    failEvent_(eventKey, error);
    markIncidentAutomation_(incidentId, 'Failed', error.message);
    recordFailure_(eventKey, payload, incidentId, error);

    try {
      if (isConfiguredEmail_(CONFIG.ADMIN_EMAIL)) {
        enqueueNotification_(
          'FAILURE:' + eventKey,
          incidentId,
          CONFIG.ADMIN_EMAIL,
          'Incident automation failed: ' + eventKey,
          'The incident automation failed.\n\n' +
          'Event: ' + eventKey + '\n' +
          'Incident: ' + (incidentId || 'Not yet assigned') + '\n' +
          'Error: ' + error.message + '\n\n' +
          'Review the Failures sheet and Apps Script execution log.'
        );
        processNotificationQueue();
      }
    } catch (notificationError) {
      console.error('Could not queue failure notification: ' +
        notificationError.message);
    }

    console.error(error.stack || error.message);
    throw error;
  }
}

function routePayload_(payload, eventKey) {
  if (payload.sheetName === CONFIG.INTAKE_RESPONSE_SHEET) {
    return processIntake_(payload, eventKey);
  }

  if (payload.sheetName === CONFIG.UPDATE_RESPONSE_SHEET) {
    return processUpdate_(payload, eventKey);
  }

  throw new Error('Unrecognized form response sheet: ' + payload.sheetName);
}

function processIntake_(payload, eventKey) {
  const ss = getSs_();
  const incidents = ss.getSheetByName(SHEETS.INCIDENTS);
  const named = payload.named;

  const summary = requiredText_(named, 'Summary', 300);
  const detectedAt = requiredDate_(named, 'Detected at');
  const severity = requiredChoice_(named, 'Severity', ALLOWED_SEVERITIES);
  const service = requiredText_(named, 'Affected service', 200);
  const impact = requiredText_(named, 'Impact', 4000);
  const description = requiredText_(
    named,
    'Description and current symptoms',
    10000
  );
  const communicationsRequired = requiredChoice_(
    named,
    'Communications required',
    ALLOWED_COMMUNICATIONS
  );
  const reporterEmail = requiredEmail_(named, 'Email Address');

  if (detectedAt.getTime() > Date.now() + 5 * 60 * 1000) {
    throw new Error('Detected at cannot be materially in the future.');
  }

  const directory = getDirectoryEntry_(service);
  let row = findRowByValue_(incidents, 'Form Event Key', eventKey);
  let incidentId;
  let duplicateOf = '';

  if (!row) {
    incidentId = generateIncidentId_();
    duplicateOf = findPotentialDuplicate_(
      service,
      summary,
      detectedAt,
      incidentId
    );

    appendObject_(incidents, {
      'Incident ID': incidentId,
      'Created At': new Date(),
      'Updated At': new Date(),
      'Form Event Key': eventKey,
      'Detected At': detectedAt,
      'Summary': summary,
      'Description': description,
      'Severity': severity,
      'Affected Service': service,
      'Impact': impact,
      'Reporter Email': reporterEmail,
      'Owner Email': directory.primaryOwner,
      'Status': duplicateOf ? 'Manual Review' : 'Assigned',
      'Communications Required': communicationsRequired,
      'Communication State': 'Not sent',
      'Recovery At': '',
      'Recovery Summary': '',
      'Root Cause': '',
      'Closure Approver Email': '',
      'Approval Status': 'Not requested',
      'Closed At': '',
      'Evidence Folder URL': '',
      'External Form Row': payload.row,
      'Automation Status': 'Processing',
      'Last Automation Run': new Date(),
      'Retry Count': 0,
      'Error Message': '',
      'Notes': '',
      'Potential Duplicate Of': duplicateOf
    });

    row = findRowByValue_(incidents, 'Incident ID', incidentId);
  } else {
    incidentId = getObjectByRow_(incidents, row)['Incident ID'];
  }

  let incident = getObjectByRow_(incidents, row);
  const folder = ensureIncidentFolder_(row, incident);
  moveEvidenceFiles_(named['Evidence files'], folder);

  appendTimeline_(
    incidentId,
    detectedAt,
    'Incident created',
    description,
    reporterEmail,
    eventKey
  );

  appendAudit_(
    incidentId,
    reporterEmail,
    'Incident created',
    '',
    incident['Status'],
    eventKey,
    'Severity ' + severity + '; affected service ' + service
  );

  enqueueNotification_(
    'ASSIGNMENT:' + eventKey,
    incidentId,
    directory.primaryOwner,
    severity + ' incident assigned: ' + incidentId,
    'A new incident has been assigned to you.\n\n' +
    'Incident: ' + incidentId + '\n' +
    'Severity: ' + severity + '\n' +
    'Service: ' + service + '\n' +
    'Summary: ' + summary + '\n' +
    'Impact: ' + impact + '\n\n' +
    'Submit updates at: ' + CONFIG.UPDATE_FORM_URL
  );

  if (duplicateOf) {
    const recipients = uniqueEmails_([
      directory.primaryOwner,
      directory.backupOwner,
      CONFIG.ADMIN_EMAIL
    ]);

    enqueueNotification_(
      'DUPLICATE:' + eventKey,
      incidentId,
      recipients.join(','),
      'Possible duplicate incident requires review: ' + incidentId,
      'Incident ' + incidentId + ' may duplicate ' + duplicateOf + '.\n\n' +
      'The record has been placed in Manual Review. Confirm whether the ' +
      'reports represent the same operational event before changing status.'
    );
  } else if (severity === 'SEV1') {
    const recipients = uniqueEmails_([
      directory.backupOwner,
      CONFIG.ADMIN_EMAIL
    ]);

    if (recipients.length) {
      enqueueNotification_(
        'SEV1:' + eventKey,
        incidentId,
        recipients.join(','),
        'SEV1 incident created: ' + incidentId,
        'A SEV1 incident has been created.\n\n' +
        'Incident: ' + incidentId + '\n' +
        'Service: ' + service + '\n' +
        'Owner: ' + directory.primaryOwner + '\n' +
        'Summary: ' + summary
      );
    }
  }

  updateObjectRow_(incidents, row, {
    'Evidence Folder URL': folder.getUrl(),
    'Automation Status': 'Complete',
    'Last Automation Run': new Date(),
    'Error Message': ''
  });

  return incidentId;
}

function processUpdate_(payload, eventKey) {
  const ss = getSs_();
  const incidents = ss.getSheetByName(SHEETS.INCIDENTS);
  const actions = ss.getSheetByName(SHEETS.ACTIONS);
  const named = payload.named;

  const incidentId = requiredText_(named, 'Incident ID', 50).toUpperCase();
  if (!/^INC-\d{4}-\d{4,}$/.test(incidentId)) {
    throw new Error('Incident ID format is invalid.');
  }

  const incidentRow = findRowByValue_(incidents, 'Incident ID', incidentId);
  if (!incidentRow) {
    throw new Error('Incident not found: ' + incidentId);
  }

  let incident = getObjectByRow_(incidents, incidentRow);
  const actorEmail = requiredEmail_(named, 'Email Address');
  const updateType = requiredChoice_(named, 'Update type', UPDATE_TYPES);
  const occurredAt = requiredDate_(named, 'Occurred at');
  const note = optionalText_(named, 'Note', 10000);
  const newStatus = optionalText_(named, 'New status', 100);
  const actionIdInput = optionalText_(named, 'Action ID', 100).toUpperCase();

  if (occurredAt.getTime() > Date.now() + 5 * 60 * 1000) {
    throw new Error('Occurred at cannot be materially in the future.');
  }

  let actionForAuthorization = null;
  if (updateType === 'Complete corrective action') {
    if (!actionIdInput) {
      throw new Error('Action ID is required to complete an action.');
    }
    const actionRow = findRowByValue_(actions, 'Action ID', actionIdInput);
    if (!actionRow) {
      throw new Error('Corrective action not found: ' + actionIdInput);
    }
    actionForAuthorization = getObjectByRow_(actions, actionRow);
    if (actionForAuthorization['Incident ID'] !== incidentId) {
      throw new Error('The action does not belong to this incident.');
    }
  }

  const approvalDecision =
    updateType === 'Approve closure' ||
    updateType === 'Return for more information';

  const actionOwnerCompletion =
    actionForAuthorization &&
    normalizeEmail_(actionForAuthorization['Owner Email']) === actorEmail;

  if (!approvalDecision &&
      !actionOwnerCompletion &&
      !isIncidentParticipant_(incident, actorEmail)) {
    throw new Error('The submitting user is not authorized for this incident.');
  }

  const folder = ensureIncidentFolder_(incidentRow, incident);
  moveEvidenceFiles_(named['Evidence files'], folder);

  const previousStatus = String(incident['Status']);
  const patch = {
    'Updated At': new Date(),
    'Automation Status': 'Processing',
    'Last Automation Run': new Date(),
    'Error Message': ''
  };

  let auditAction = updateType;
  let notificationContext = updateType;

  if (updateType === 'Progress note') {
    requireNote_(note, updateType);

    if (newStatus && newStatus !== 'No change') {
      assertTransition_(previousStatus, newStatus);
      patch['Status'] = newStatus;
    }

    appendTimeline_(
      incidentId,
      occurredAt,
      'Progress note',
      note,
      actorEmail,
      eventKey
    );
  }

  if (updateType === 'Communication sent') {
    requireNote_(note, updateType);
    patch['Communication State'] =
      'Sent ' + formatDateTime_(occurredAt);

    appendTimeline_(
      incidentId,
      occurredAt,
      'Communication sent',
      note,
      actorEmail,
      eventKey
    );
  }

  if (updateType === 'Recovery confirmed') {
    requireNote_(note, updateType);
    assertTransition_(previousStatus, 'Monitoring');

    patch['Recovery At'] = occurredAt;
    patch['Recovery Summary'] = note;
    patch['Status'] = 'Monitoring';

    appendTimeline_(
      incidentId,
      occurredAt,
      'Recovery confirmed',
      note,
      actorEmail,
      eventKey
    );
  }

  if (updateType === 'Root cause recorded') {
    requireNote_(note, updateType);
    patch['Root Cause'] = note;

    appendTimeline_(
      incidentId,
      occurredAt,
      'Root cause recorded',
      note,
      actorEmail,
      eventKey
    );
  }

  if (updateType === 'Add corrective action') {
    requireOwnerOrApprover_(incident, actorEmail);
    requireNote_(note, updateType);

    const actionOwner = requiredEmail_(named, 'Action owner email');
    const dueDate = requiredDate_(named, 'Action due date');
    const existingActionRow = findRowByValue_(
      actions,
      'Source Event Key',
      eventKey
    );

    let actionId;
    if (existingActionRow) {
      actionId = getObjectByRow_(actions, existingActionRow)['Action ID'];
    } else {
      actionId = generateActionId_(incidentId);

      appendObject_(actions, {
        'Action ID': actionId,
        'Incident ID': incidentId,
        'Description': note,
        'Owner Email': actionOwner,
        'Due Date': dueDate,
        'Status': 'Open',
        'Created At': new Date(),
        'Completed At': '',
        'Source Event Key': eventKey,
        'Completion Note': ''
      });
    }

    appendTimeline_(
      incidentId,
      occurredAt,
      'Corrective action added',
      actionId + ': ' + note,
      actorEmail,
      eventKey
    );

    enqueueNotification_(
      'ACTION:' + eventKey,
      incidentId,
      actionOwner,
      'Corrective action assigned: ' + actionId,
      'A corrective action has been assigned to you.\n\n' +
      'Incident: ' + incidentId + '\n' +
      'Action: ' + actionId + '\n' +
      'Description: ' + note + '\n' +
      'Due: ' + formatDate_(dueDate) + '\n\n' +
      'Submit completion at: ' + CONFIG.UPDATE_FORM_URL
    );

    notificationContext = 'Corrective action ' + actionId + ' added';
  }

  if (updateType === 'Complete corrective action') {
    const actionRow = findRowByValue_(actions, 'Action ID', actionIdInput);
    const action = getObjectByRow_(actions, actionRow);

    if (String(action['Status']) !== 'Completed') {
      updateObjectRow_(actions, actionRow, {
        'Status': 'Completed',
        'Completed At': occurredAt,
        'Completion Note': note
      });
    }

    appendTimeline_(
      incidentId,
      occurredAt,
      'Corrective action completed',
      actionIdInput + (note ? ': ' + note : ''),
      actorEmail,
      eventKey
    );

    notificationContext = 'Corrective action ' + actionIdInput + ' completed';
  }

  if (updateType === 'Request closure') {
    requireOwner_(incident, actorEmail);

    if (!incident['Recovery At']) {
      throw new Error('Recovery must be confirmed before requesting closure.');
    }

    if (!String(incident['Root Cause'] || '').trim()) {
      throw new Error('Root cause must be recorded before requesting closure.');
    }

    if (countOpenActions_(incidentId) > 0) {
      throw new Error(
        'All corrective actions must be completed before requesting closure.'
      );
    }

    assertTransition_(previousStatus, 'Awaiting Closure Approval');
    const directory = getDirectoryEntry_(incident['Affected Service']);

    patch['Status'] = 'Awaiting Closure Approval';
    patch['Closure Approver Email'] = directory.closureApprover;
    patch['Approval Status'] = 'Pending';

    appendTimeline_(
      incidentId,
      occurredAt,
      'Closure requested',
      note || 'Closure requested by incident owner.',
      actorEmail,
      eventKey
    );
  }

  if (updateType === 'Approve closure') {
    if (previousStatus !== 'Awaiting Closure Approval') {
      throw new Error('The incident is not awaiting closure approval.');
    }

    if (!isAuthorizedApprover_(
      incident['Closure Approver Email'],
      actorEmail
    )) {
      throw new Error('The submitting user is not the authorized approver.');
    }

    if (countOpenActions_(incidentId) > 0) {
      throw new Error('The incident still has open corrective actions.');
    }

    assertTransition_(previousStatus, 'Closed');
    patch['Status'] = 'Closed';
    patch['Approval Status'] = 'Approved';
    patch['Closed At'] = occurredAt;

    appendTimeline_(
      incidentId,
      occurredAt,
      'Closure approved',
      note || 'Closure approved.',
      actorEmail,
      eventKey
    );
  }

  if (updateType === 'Return for more information') {
    if (previousStatus !== 'Awaiting Closure Approval') {
      throw new Error('The incident is not awaiting closure approval.');
    }

    if (!isAuthorizedApprover_(
      incident['Closure Approver Email'],
      actorEmail
    )) {
      throw new Error('The submitting user is not the authorized approver.');
    }

    requireNote_(note, updateType);
    assertTransition_(previousStatus, 'Monitoring');

    patch['Status'] = 'Monitoring';
    patch['Approval Status'] = 'Returned';

    appendTimeline_(
      incidentId,
      occurredAt,
      'Closure returned',
      note,
      actorEmail,
      eventKey
    );
  }

  updateObjectRow_(incidents, incidentRow, patch);
  incident = getObjectByRow_(incidents, incidentRow);

  appendAudit_(
    incidentId,
    actorEmail,
    auditAction,
    previousStatus,
    incident['Status'],
    eventKey,
    note || notificationContext
  );

  queueUpdateNotifications_(
    incident,
    actorEmail,
    updateType,
    notificationContext,
    eventKey
  );

  updateObjectRow_(incidents, incidentRow, {
    'Automation Status': 'Complete',
    'Last Automation Run': new Date(),
    'Error Message': ''
  });

  return incidentId;
}

function queueUpdateNotifications_(
  incident,
  actorEmail,
  updateType,
  context,
  eventKey
) {
  const incidentId = incident['Incident ID'];
  const owner = normalizeEmail_(incident['Owner Email']);

  enqueueNotification_(
    'UPDATE:OWNER:' + eventKey,
    incidentId,
    owner,
    'Incident update: ' + incidentId,
    'Incident ' + incidentId + ' was updated.\n\n' +
    'Update: ' + context + '\n' +
    'Status: ' + incident['Status'] + '\n' +
    'Submitted by: ' + actorEmail + '\n\n' +
    'Submit another update at: ' + CONFIG.UPDATE_FORM_URL
  );

  if (updateType === 'Request closure') {
    const recipient = effectiveApproverRecipient_(
      incident['Closure Approver Email']
    );

    enqueueNotification_(
      'APPROVAL:' + eventKey,
      incidentId,
      recipient,
      'Closure approval required: ' + incidentId,
      'Incident ' + incidentId + ' is awaiting closure approval.\n\n' +
      'Summary: ' + incident['Summary'] + '\n' +
      'Recovery: ' + incident['Recovery Summary'] + '\n' +
      'Root cause: ' + incident['Root Cause'] + '\n\n' +
      'Submit the approval decision at: ' + CONFIG.UPDATE_FORM_URL
    );
  }

  const reporterShouldReceive =
    String(incident['Communications Required']) === 'Yes' &&
    [
      'Communication sent',
      'Recovery confirmed',
      'Approve closure',
      'Return for more information'
    ].indexOf(updateType) !== -1;

  if (reporterShouldReceive) {
    enqueueNotification_(
      'UPDATE:REPORTER:' + eventKey,
      incidentId,
      normalizeEmail_(incident['Reporter Email']),
      'Incident status: ' + incidentId,
      'Incident ' + incidentId + ' has been updated.\n\n' +
      'Summary: ' + incident['Summary'] + '\n' +
      'Status: ' + incident['Status'] + '\n' +
      'Update type: ' + updateType
    );
  }
}

function sendScheduledReminders() {
  validateConfiguration_();

  const incidents = getSs_().getSheetByName(SHEETS.INCIDENTS);
  const values = getDataObjects_(incidents);
  const now = new Date();

  const intervalMinutes = {
    'SEV1': 30,
    'SEV2': 120,
    'SEV3': 480,
    'SEV4': 1440
  };

  values.forEach(function(incident) {
    const status = String(incident['Status'] || '');
    if (!incident['Incident ID'] || status === 'Closed') {
      return;
    }

    const updatedAt = asDate_(incident['Updated At']);
    if (!updatedAt) {
      return;
    }

    if (status === 'Awaiting Closure Approval') {
      const interval = 1440;
      const ageMinutes = (now.getTime() - updatedAt.getTime()) / 60000;
      if (ageMinutes < interval) {
        return;
      }

      const bucket = Math.floor(ageMinutes / interval);
      const approver = effectiveApproverRecipient_(
        incident['Closure Approver Email']
      );

      enqueueNotification_(
        'CLOSURE-REMINDER:' + incident['Incident ID'] + ':' +
          updatedAt.getTime() + ':' + bucket,
        incident['Incident ID'],
        approver,
        'Closure decision overdue: ' + incident['Incident ID'],
        'Incident ' + incident['Incident ID'] +
          ' is awaiting a closure decision.\n\n' +
          'Submit the decision at: ' + CONFIG.UPDATE_FORM_URL
      );
      return;
    }

    const severity = String(incident['Severity']);
    const interval = intervalMinutes[severity];
    if (!interval) {
      return;
    }

    const ageMinutes = (now.getTime() - updatedAt.getTime()) / 60000;
    if (ageMinutes < interval) {
      return;
    }

    const bucket = Math.floor(ageMinutes / interval);
    const directory = getDirectoryEntry_(incident['Affected Service']);
    const recipients = [normalizeEmail_(incident['Owner Email'])];

    if (bucket >= 2) {
      recipients.push(directory.backupOwner);
      recipients.push(CONFIG.ADMIN_EMAIL);
    }

    enqueueNotification_(
      'STALE:' + incident['Incident ID'] + ':' +
        updatedAt.getTime() + ':' + bucket,
      incident['Incident ID'],
      uniqueEmails_(recipients).join(','),
      'Incident update overdue: ' + incident['Incident ID'],
      'Incident ' + incident['Incident ID'] +
        ' has not received an update within its expected interval.\n\n' +
        'Severity: ' + severity + '\n' +
        'Status: ' + status + '\n' +
        'Last update: ' + formatDateTime_(updatedAt) + '\n\n' +
        'Submit an update at: ' + CONFIG.UPDATE_FORM_URL
    );
  });

  processNotificationQueue();
}

function processNotificationQueue() {
  validateConfiguration_();

  const lock = LockService.getScriptLock();
  if (!lock.tryLock(1000)) {
    console.log('Notification processor is already running.');
    return;
  }

  try {
    const sheet = getSs_().getSheetByName(SHEETS.NOTIFICATIONS);
    const records = getDataObjectsWithRows_(sheet);
    const now = new Date();

    records.forEach(function(item) {
      const record = item.object;
      const status = String(record['Status']);
      const attempts = Number(record['Attempt Count'] || 0);
      const nextAttempt = asDate_(record['Next Attempt At']);

      if (['Pending', 'Retry'].indexOf(status) === -1) {
        return;
      }

      if (attempts >= CONFIG.MAX_EMAIL_ATTEMPTS) {
        updateObjectRow_(sheet, item.row, {
          'Status': 'Dead Letter'
        });
        return;
      }

      if (nextAttempt && nextAttempt.getTime() > now.getTime()) {
        return;
      }

      try {
        const recipient = String(record['To']);
        if (!isEmailList_(recipient)) {
          throw new Error('Notification recipient is invalid: ' + recipient);
        }

        GmailApp.sendEmail(
          recipient,
          String(record['Subject']),
          String(record['Body']),
          {
            name: 'Incident Management Automation',
            replyTo: isConfiguredEmail_(CONFIG.ADMIN_EMAIL)
              ? CONFIG.ADMIN_EMAIL
              : undefined
          }
        );

        updateObjectRow_(sheet, item.row, {
          'Status': 'Sent',
          'Attempt Count': attempts + 1,
          'Sent At': new Date(),
          'Last Error': ''
        });
      } catch (error) {
        const newAttempts = attempts + 1;
        const delayMinutes = Math.min(
          60,
          Math.pow(2, newAttempts) * 5
        );

        updateObjectRow_(sheet, item.row, {
          'Status': newAttempts >= CONFIG.MAX_EMAIL_ATTEMPTS
            ? 'Dead Letter'
            : 'Retry',
          'Attempt Count': newAttempts,
          'Next Attempt At': new Date(Date.now() + delayMinutes * 60000),
          'Last Error': error.message
        });

        console.error(
          'Notification failed: ' +
          record['Notification ID'] + '; ' + error.message
        );
      }
    });
  } finally {
    lock.releaseLock();
  }
}

function retryFailedEvents() {
  validateConfiguration_();

  const failures = getSs_().getSheetByName(SHEETS.FAILURES);
  const records = getDataObjectsWithRows_(failures);

  records.forEach(function(item) {
    const failure = item.object;
    const status = String(failure['Status']);
    const retries = Number(failure['Retry Count'] || 0);

    if (['Pending', 'Retry'].indexOf(status) === -1 ||
        retries >= CONFIG.MAX_EVENT_RETRIES) {
      return;
    }

    let payload;
    try {
      payload = JSON.parse(String(failure['Payload JSON']));
    } catch (error) {
      updateObjectRow_(failures, item.row, {
        'Status': 'Manual Review',
        'Resolution Notes': 'Stored payload is not valid JSON.'
      });
      return;
    }

    try {
      const incidentId = routePayload_(payload, failure['Event Key']);
      completeEvent_(failure['Event Key'], incidentId);
      markIncidentAutomation_(incidentId, 'Complete', '');

      updateObjectRow_(failures, item.row, {
        'Incident ID': incidentId,
        'Retry Count': retries + 1,
        'Status': 'Recovered',
        'Resolution Notes': 'Recovered automatically at ' +
          formatDateTime_(new Date())
      });
    } catch (error) {
      const nextRetry = retries + 1;
      const incidentId =
        String(payload.named['Incident ID'] || failure['Incident ID'] || '');

      markIncidentAutomation_(incidentId, 'Failed', error.message);

      updateObjectRow_(failures, item.row, {
        'Incident ID': incidentId,
        'Failed At': new Date(),
        'Error': error.message,
        'Retry Count': nextRetry,
        'Status': nextRetry >= CONFIG.MAX_EVENT_RETRIES
          ? 'Manual Review'
          : 'Retry'
      });

      console.error('Retry failed for ' + failure['Event Key'] +
        ': ' + error.message);
    }
  });

  processNotificationQueue();
}

function reconcileAutomation() {
  validateConfiguration_();

  const ss = getSs_();
  const incidents = getDataObjects_(ss.getSheetByName(SHEETS.INCIDENTS));
  const notifications = getDataObjects_(
    ss.getSheetByName(SHEETS.NOTIFICATIONS)
  );

  const failedIncidents = incidents.filter(function(record) {
    return String(record['Automation Status']) === 'Failed';
  });

  const missingOwners = incidents.filter(function(record) {
    return record['Incident ID'] &&
      String(record['Status']) !== 'Closed' &&
      !isConfiguredEmail_(record['Owner Email']);
  });

  const deadLetters = notifications.filter(function(record) {
    return String(record['Status']) === 'Dead Letter';
  });

  if (!failedIncidents.length &&
      !missingOwners.length &&
      !deadLetters.length) {
    return;
  }

  const dayKey = Utilities.formatDate(
    new Date(),
    Session.getScriptTimeZone(),
    'yyyyMMdd'
  );

  enqueueNotification_(
    'RECONCILIATION:' + dayKey,
    '',
    CONFIG.ADMIN_EMAIL,
    'Incident automation reconciliation requires review',
    'Daily reconciliation found the following:\n\n' +
    'Failed incidents: ' + failedIncidents.length + '\n' +
    'Open incidents without valid owners: ' + missingOwners.length + '\n' +
    'Dead-letter notifications: ' + deadLetters.length + '\n\n' +
    'Review the Incidents, Failures, and Notifications sheets.'
  );

  processNotificationQueue();
}

function beginEvent_(eventKey, payload) {
  const sheet = getSs_().getSheetByName(SHEETS.EVENTS);
  const row = findRowByValue_(sheet, 'Event Key', eventKey);

  if (row) {
    const existing = getObjectByRow_(sheet, row);
    if (String(existing['Status']) === 'Complete') {
      return false;
    }

    updateObjectRow_(sheet, row, {
      'Processed At': new Date(),
      'Status': 'Processing',
      'Payload JSON': truncate_(JSON.stringify(payload), 45000),
      'Error': ''
    });
    return true;
  }

  appendObject_(sheet, {
    'Event Key': eventKey,
    'Source Sheet': payload.sheetName,
    'Source Row': payload.row,
    'Incident ID': '',
    'Processed At': new Date(),
    'Status': 'Processing',
    'Payload JSON': truncate_(JSON.stringify(payload), 45000),
    'Error': ''
  });

  return true;
}

function completeEvent_(eventKey, incidentId) {
  const sheet = getSs_().getSheetByName(SHEETS.EVENTS);
  const row = findRowByValue_(sheet, 'Event Key', eventKey);
  if (!row) {
    return;
  }

  updateObjectRow_(sheet, row, {
    'Incident ID': incidentId,
    'Processed At': new Date(),
    'Status': 'Complete',
    'Error': ''
  });
}

function failEvent_(eventKey, error) {
  const sheet = getSs_().getSheetByName(SHEETS.EVENTS);
  const row = findRowByValue_(sheet, 'Event Key', eventKey);
  if (!row) {
    return;
  }

  updateObjectRow_(sheet, row, {
    'Processed At': new Date(),
    'Status': 'Failed',
    'Error': truncate_(error.message, 5000)
  });
}

function recordFailure_(eventKey, payload, incidentId, error) {
  const sheet = getSs_().getSheetByName(SHEETS.FAILURES);
  const row = findRowByValue_(sheet, 'Event Key', eventKey);

  if (row) {
    updateObjectRow_(sheet, row, {
      'Incident ID': incidentId,
      'Failed At': new Date(),
      'Error': truncate_(error.message, 5000),
      'Status': 'Pending',
      'Payload JSON': truncate_(JSON.stringify(payload), 45000)
    });
    return;
  }

  appendObject_(sheet, {
    'Failure ID': stableId_('FAIL-', eventKey),
    'Event Key': eventKey,
    'Source Sheet': payload.sheetName,
    'Source Row': payload.row,
    'Incident ID': incidentId,
    'Failed At': new Date(),
    'Error': truncate_(error.message, 5000),
    'Retry Count': 0,
    'Status': 'Pending',
    'Payload JSON': truncate_(JSON.stringify(payload), 45000),
    'Resolution Notes': ''
  });
}

function enqueueNotification_(
  uniqueKey,
  incidentId,
  recipient,
  subject,
  body
) {
  if (!recipient) {
    return;
  }

  if (!isEmailList_(recipient)) {
    throw new Error('Invalid notification recipient: ' + recipient);
  }

  const sheet = getSs_().getSheetByName(SHEETS.NOTIFICATIONS);
  const notificationId = stableId_('NTF-', uniqueKey);
  const existing = findRowByValue_(
    sheet,
    'Notification ID',
    notificationId
  );

  if (existing) {
    return notificationId;
  }

  appendObject_(sheet, {
    'Notification ID': notificationId,
    'Incident ID': incidentId,
    'Created At': new Date(),
    'To': recipient,
    'Subject': truncate_(subject, 250),
    'Body': truncate_(body, 20000),
    'Status': 'Pending',
    'Attempt Count': 0,
    'Next Attempt At': new Date(),
    'Sent At': '',
    'Last Error': ''
  });

  return notificationId;
}

function appendTimeline_(
  incidentId,
  occurredAt,
  type,
  note,
  actorEmail,
  eventKey
) {
  const sheet = getSs_().getSheetByName(SHEETS.TIMELINE);
  const timelineId = stableId_('TL-', eventKey + ':' + type);
  const existing = findRowByValue_(sheet, 'Timeline ID', timelineId);

  if (existing) {
    return timelineId;
  }

  appendObject_(sheet, {
    'Timeline ID': timelineId,
    'Incident ID': incidentId,
    'Occurred At': occurredAt,
    'Logged At': new Date(),
    'Type': type,
    'Note': note,
    'Actor Email': actorEmail,
    'Source Event Key': eventKey,
    'Approved for AI': 'No',
    'Automation Status': 'Complete'
  });

  return timelineId;
}

function appendAudit_(
  incidentId,
  actorEmail,
  action,
  previousState,
  newState,
  eventKey,
  details
) {
  const sheet = getSs_().getSheetByName(SHEETS.AUDIT);
  const auditId = stableId_('AUD-', eventKey + ':' + action);
  const existing = findRowByValue_(sheet, 'Audit ID', auditId);

  if (existing) {
    return auditId;
  }

  appendObject_(sheet, {
    'Audit ID': auditId,
    'Incident ID': incidentId,
    'Timestamp': new Date(),
    'Actor Email': actorEmail,
    'Action': action,
    'Previous State': previousState,
    'New State': newState,
    'Source Event Key': eventKey,
    'Details': truncate_(details, 10000)
  });

  return auditId;
}

function ensureIncidentFolder_(incidentRow, incident) {
  if (incident['Evidence Folder URL']) {
    const existingId = extractDriveIds_(
      incident['Evidence Folder URL']
    )[0];

    if (existingId) {
      try {
        return DriveApp.getFolderById(existingId);
      } catch (error) {
        console.warn('Stored folder could not be opened; attempting recovery.');
      }
    }
  }

  const root = DriveApp.getFolderById(CONFIG.ROOT_FOLDER_ID);
  const folderName = safeFileName_(
    incident['Incident ID'] + ' - ' + incident['Summary']
  );
  const matching = root.getFoldersByName(folderName);
  const folder = matching.hasNext()
    ? matching.next()
    : root.createFolder(folderName);

  const incidents = getSs_().getSheetByName(SHEETS.INCIDENTS);
  updateObjectRow_(incidents, incidentRow, {
    'Evidence Folder URL': folder.getUrl()
  });

  return folder;
}

function moveEvidenceFiles_(value, folder) {
  if (!value) {
    return;
  }

  const fileIds = extractDriveIds_(String(value));
  fileIds.forEach(function(fileId) {
    const file = DriveApp.getFileById(fileId);
    file.moveTo(folder);
  });
}

function findPotentialDuplicate_(service, summary, detectedAt, newId) {
  const sheet = getSs_().getSheetByName(SHEETS.INCIDENTS);
  const records = getDataObjects_(sheet);
  const normalizedSummary = normalizeText_(summary);
  const sixHours = 6 * 60 * 60 * 1000;

  for (let i = records.length - 1; i >= 0; i--) {
    const record = records[i];

    if (!record['Incident ID'] ||
        record['Incident ID'] === newId ||
        String(record['Status']) === 'Closed') {
      continue;
    }

    const existingDetectedAt = asDate_(record['Detected At']);
    if (!existingDetectedAt) {
      continue;
    }

    const sameService =
      String(record['Affected Service']) === String(service);
    const sameSummary =
      normalizeText_(record['Summary']) === normalizedSummary;
    const closeInTime =
      Math.abs(
        existingDetectedAt.getTime() - detectedAt.getTime()
      ) <= sixHours;

    if (sameService && sameSummary && closeInTime) {
      return String(record['Incident ID']);
    }
  }

  return '';
}

function generateIncidentId_() {
  const lock = LockService.getScriptLock();
  lock.waitLock(30000);

  try {
    const year = Utilities.formatDate(
      new Date(),
      Session.getScriptTimeZone(),
      'yyyy'
    );
    const propertyName = 'INCIDENT_SEQUENCE_' + year;
    const properties = PropertiesService.getScriptProperties();
    let sequence = Number(properties.getProperty(propertyName) || 0);
    const incidents = getSs_().getSheetByName(SHEETS.INCIDENTS);
    let candidate;

    do {
      sequence += 1;
      candidate = 'INC-' + year + '-' +
        String(sequence).padStart(4, '0');
    } while (findRowByValue_(incidents, 'Incident ID', candidate));

    properties.setProperty(propertyName, String(sequence));
    return candidate;
  } finally {
    lock.releaseLock();
  }
}

function generateActionId_(incidentId) {
  const lock = LockService.getScriptLock();
  lock.waitLock(30000);

  try {
    const sheet = getSs_().getSheetByName(SHEETS.ACTIONS);
    const actions = getDataObjects_(sheet).filter(function(action) {
      return String(action['Incident ID']) === incidentId;
    });

    let sequence = actions.length + 1;
    let candidate;

    do {
      candidate = 'ACT-' + incidentId.substring(4) + '-' +
        String(sequence).padStart(2, '0');
      sequence += 1;
    } while (findRowByValue_(sheet, 'Action ID', candidate));

    return candidate;
  } finally {
    lock.releaseLock();
  }
}

function countOpenActions_(incidentId) {
  return getDataObjects_(
    getSs_().getSheetByName(SHEETS.ACTIONS)
  ).filter(function(action) {
    return String(action['Incident ID']) === incidentId &&
      ['Completed', 'Cancelled'].indexOf(String(action['Status'])) === -1;
  }).length;
}

function getDirectoryEntry_(service) {
  const sheet = getSs_().getSheetByName(SHEETS.DIRECTORY);
  const records = getDataObjects_(sheet);

  const match = records.find(function(record) {
    return String(record['Affected Service']).trim() === String(service).trim() &&
      isTrue_(record['Active']);
  });

  if (!match) {
    throw new Error(
      'No active Directory entry exists for affected service: ' + service
    );
  }

  const result = {
    primaryOwner: normalizeEmail_(match['Primary Owner Email']),
    backupOwner: normalizeEmail_(match['Backup Owner Email']),
    closureApprover: normalizeEmail_(match['Closure Approver Email'])
  };

  if (!isConfiguredEmail_(result.primaryOwner)) {
    throw new Error('Directory primary owner is invalid for ' + service);
  }

  if (!isConfiguredEmail_(result.closureApprover)) {
    throw new Error('Directory closure approver is invalid for ' + service);
  }

  return result;
}

function isIncidentParticipant_(incident, actorEmail) {
  const directory = getDirectoryEntry_(incident['Affected Service']);
  const allowed = uniqueEmails_([
    incident['Reporter Email'],
    incident['Owner Email'],
    incident['Closure Approver Email'],
    directory.primaryOwner,
    directory.backupOwner,
    directory.closureApprover,
    CONFIG.ADMIN_EMAIL
  ]);

  return allowed.indexOf(actorEmail) !== -1;
}

function requireOwner_(incident, actorEmail) {
  if (normalizeEmail_(incident['Owner Email']) !== actorEmail) {
    throw new Error('Only the incident owner can perform this action.');
  }
}

function requireOwnerOrApprover_(incident, actorEmail) {
  const owner = normalizeEmail_(incident['Owner Email']);
  const approver = normalizeEmail_(incident['Closure Approver Email']);
  const directory = getDirectoryEntry_(incident['Affected Service']);

  const allowed = uniqueEmails_([
    owner,
    approver,
    directory.closureApprover,
    CONFIG.ADMIN_EMAIL
  ]);

  if (allowed.indexOf(actorEmail) === -1) {
    throw new Error(
      'Only the incident owner or designated approver can perform this action.'
    );
  }
}

function isAuthorizedApprover_(principalEmail, actorEmail) {
  const principal = normalizeEmail_(principalEmail);
  const actor = normalizeEmail_(actorEmail);

  if (principal === actor) {
    return true;
  }

  const sheet = getSs_().getSheetByName(SHEETS.DELEGATIONS);
  const now = new Date();

  return getDataObjects_(sheet).some(function(record) {
    if (!isTrue_(record['Active'])) {
      return false;
    }

    const start = asDate_(record['Start At']);
    const end = asDate_(record['End At']);

    return normalizeEmail_(record['Principal Email']) === principal &&
      normalizeEmail_(record['Delegate Email']) === actor &&
      (!start || start.getTime() <= now.getTime()) &&
      (!end || end.getTime() >= now.getTime());
  });
}

function effectiveApproverRecipient_(principalEmail) {
  const principal = normalizeEmail_(principalEmail);
  const sheet = getSs_().getSheetByName(SHEETS.DELEGATIONS);
  const now = new Date();

  const delegation = getDataObjects_(sheet).find(function(record) {
    if (!isTrue_(record['Active'])) {
      return false;
    }

    const start = asDate_(record['Start At']);
    const end = asDate_(record['End At']);

    return normalizeEmail_(record['Principal Email']) === principal &&
      (!start || start.getTime() <= now.getTime()) &&
      (!end || end.getTime() >= now.getTime()) &&
      isConfiguredEmail_(record['Delegate Email']);
  });

  return delegation
    ? normalizeEmail_(delegation['Delegate Email'])
    : principal;
}

function assertTransition_(fromStatus, toStatus) {
  if (fromStatus === toStatus) {
    return;
  }

  const allowed = STATUS_TRANSITIONS[fromStatus] || [];
  if (allowed.indexOf(toStatus) === -1) {
    throw new Error(
      'Status transition is not allowed: ' +
      fromStatus + ' to ' + toStatus
    );
  }
}

function markIncidentAutomation_(incidentId, status, errorMessage) {
  if (!incidentId) {
    return;
  }

  const sheet = getSs_().getSheetByName(SHEETS.INCIDENTS);
  const row = findRowByValue_(sheet, 'Incident ID', incidentId);
  if (!row) {
    return;
  }

  const record = getObjectByRow_(sheet, row);
  const retryCount = Number(record['Retry Count'] || 0);

  updateObjectRow_(sheet, row, {
    'Automation Status': status,
    'Last Automation Run': new Date(),
    'Retry Count': status === 'Failed' ? retryCount + 1 : retryCount,
    'Error Message': status === 'Failed'
      ? truncate_(errorMessage, 5000)
      : ''
  });
}

function findIncidentIdByEvent_(eventKey) {
  const sheet = getSs_().getSheetByName(SHEETS.INCIDENTS);
  const row = findRowByValue_(sheet, 'Form Event Key', eventKey);
  return row ? String(getObjectByRow_(sheet, row)['Incident ID']) : '';
}

function namedValuesFromRange_(range) {
  const sheet = range.getSheet();
  const lastColumn = sheet.getLastColumn();
  const headers = sheet.getRange(1, 1, 1, lastColumn).getValues()[0];
  const values = sheet.getRange(
    range.getRow(),
    1,
    1,
    lastColumn
  ).getValues()[0];

  const named = {};
  headers.forEach(function(header, index) {
    if (header) {
      named[String(header).trim()] = values[index];
    }
  });

  return named;
}

function requiredText_(named, field, maxLength) {
  const value = String(named[field] || '').trim();
  if (!value) {
    throw new Error(field + ' is required.');
  }

  if (value.length > maxLength) {
    throw new Error(field + ' exceeds the permitted length.');
  }

  return value;
}

function optionalText_(named, field, maxLength) {
  const value = String(named[field] || '').trim();
  if (value.length > maxLength) {
    throw new Error(field + ' exceeds the permitted length.');
  }
  return value;
}

function requiredDate_(named, field) {
  const value = named[field];
  const date = asDate_(value);

  if (!date) {
    throw new Error(field + ' must contain a valid date and time.');
  }

  return date;
}

function requiredEmail_(named, field) {
  const value = normalizeEmail_(named[field]);
  if (!isConfiguredEmail_(value)) {
    throw new Error(field + ' must contain a valid email address.');
  }
  return value;
}

function requiredChoice_(named, field, allowed) {
  const value = requiredText_(named, field, 200);
  if (allowed.indexOf(value) === -1) {
    throw new Error(
      field + ' contains an unsupported value: ' + value
    );
  }
  return value;
}

function requireNote_(note, updateType) {
  if (!note) {
    throw new Error('A note is required for ' + updateType + '.');
  }
}

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

  if (Object.prototype.toString.call(value) === '[object Date]' &&
      !isNaN(value.getTime())) {
    return value;
  }

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

function normalizeEmail_(value) {
  return String(value || '').trim().toLowerCase();
}

function isConfiguredEmail_(value) {
  const email = normalizeEmail_(value);
  return /^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(email) &&
    email.indexOf('your_') === -1;
}

function isEmailList_(value) {
  const emails = String(value || '')
    .split(',')
    .map(normalizeEmail_)
    .filter(Boolean);

  return emails.length > 0 && emails.every(isConfiguredEmail_);
}

function uniqueEmails_(values) {
  return Array.from(new Set(
    values.map(normalizeEmail_).filter(isConfiguredEmail_)
  ));
}

function normalizeText_(value) {
  return String(value || '')
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, ' ')
    .trim();
}

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

function safeFileName_(value) {
  return truncate_(
    String(value).replace(/[\\/:*?"<>|#%]/g, '_').trim(),
    180
  );
}

function extractDriveIds_(value) {
  const matches = String(value || '').match(/[-\w]{25,}/g) || [];
  return Array.from(new Set(matches));
}

function stableId_(prefix, input) {
  const digest = Utilities.computeDigest(
    Utilities.DigestAlgorithm.SHA_256,
    String(input),
    Utilities.Charset.UTF_8
  );

  return prefix + Utilities.base64EncodeWebSafe(digest)
    .replace(/=+$/g, '')
    .substring(0, 12);
}

function truncate_(value, maxLength) {
  const text = String(value || '');
  return text.length > maxLength
    ? text.substring(0, maxLength)
    : text;
}

function formatDateTime_(date) {
  return Utilities.formatDate(
    date,
    Session.getScriptTimeZone(),
    'yyyy-MM-dd HH:mm:ss'
  );
}

function formatDate_(date) {
  return Utilities.formatDate(
    date,
    Session.getScriptTimeZone(),
    'yyyy-MM-dd'
  );
}

function getSs_(source) {
  if (source) {
    return source;
  }

  const active = SpreadsheetApp.getActiveSpreadsheet();
  if (active) {
    return active;
  }

  if (!CONFIG.SPREADSHEET_ID ||
      CONFIG.SPREADSHEET_ID.indexOf('YOUR_') === 0) {
    throw new Error('Replace CONFIG.SPREADSHEET_ID.');
  }

  return SpreadsheetApp.openById(CONFIG.SPREADSHEET_ID);
}

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

  if (sheet.getLastRow() === 0) {
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  } else {
    const current = sheet.getRange(
      1,
      1,
      1,
      Math.max(sheet.getLastColumn(), headers.length)
    ).getValues()[0];

    headers.forEach(function(header, index) {
      if (current[index] !== header) {
        sheet.getRange(1, index + 1).setValue(header);
      }
    });
  }

  sheet.setFrozenRows(1);
  return sheet;
}

function appendObject_(sheet, object) {
  const headers = getHeaders_(sheet);
  const row = headers.map(function(header) {
    return Object.prototype.hasOwnProperty.call(object, header)
      ? object[header]
      : '';
  });

  sheet.appendRow(row);
  return sheet.getLastRow();
}

function updateObjectRow_(sheet, row, patch) {
  const headers = getHeaders_(sheet);
  const headerIndex = {};

  headers.forEach(function(header, index) {
    headerIndex[header] = index + 1;
  });

  Object.keys(patch).forEach(function(field) {
    if (!headerIndex[field]) {
      throw new Error(
        'Field "' + field + '" does not exist on sheet ' + sheet.getName()
      );
    }

    sheet.getRange(row, headerIndex[field]).setValue(patch[field]);
  });
}

function getObjectByRow_(sheet, row) {
  const headers = getHeaders_(sheet);
  const values = sheet.getRange(row, 1, 1, headers.length).getValues()[0];
  const object = {};

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

  return object;
}

function getDataObjects_(sheet) {
  return getDataObjectsWithRows_(sheet).map(function(item) {
    return item.object;
  });
}

function getDataObjectsWithRows_(sheet) {
  const lastRow = sheet.getLastRow();
  const lastColumn = sheet.getLastColumn();

  if (lastRow < 2 || lastColumn === 0) {
    return [];
  }

  const headers = getHeaders_(sheet);
  const values = sheet.getRange(
    2,
    1,
    lastRow - 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
    };
  });
}

function getHeaders_(sheet) {
  const lastColumn = sheet.getLastColumn();
  if (lastColumn === 0) {
    return [];
  }

  return sheet.getRange(1, 1, 1, lastColumn)
    .getValues()[0]
    .map(function(value) {
      return String(value).trim();
    });
}

function findRowByValue_(sheet, header, value) {
  if (value === null || value === undefined || value === '') {
    return 0;
  }

  const headers = getHeaders_(sheet);
  const column = headers.indexOf(header) + 1;

  if (!column || sheet.getLastRow() < 2) {
    return 0;
  }

  const finder = sheet.getRange(
    2,
    column,
    sheet.getLastRow() - 1,
    1
  ).createTextFinder(String(value))
    .matchEntireCell(true)
    .matchCase(false);

  const cell = finder.findNext();
  return cell ? cell.getRow() : 0;
}

function setupDashboard_(ss) {
  const sheet = ss.getSheetByName(SHEETS.DASHBOARD);
  const values = [
    ['Metric', 'Value'],
    [
      'Open incidents',
      '=COUNTIFS(Incidents!M:M,"<>Closed",Incidents!A:A,"<>")'
    ],
    [
      'Awaiting closure approval',
      '=COUNTIF(Incidents!M:M,"Awaiting Closure Approval")'
    ],
    [
      'Manual review',
      '=COUNTIF(Incidents!M:M,"Manual Review")'
    ],
    [
      'Failed incident automation',
      '=COUNTIF(Incidents!X:X,"Failed")'
    ],
    [
      'Overdue corrective actions',
      '=COUNTIFS(Actions!F:F,"Open",Actions!E:E,"<"&NOW())'
    ],
    [
      'Dead-letter notifications',
      '=COUNTIF(Notifications!G:G,"Dead Letter")'
    ]
  ];

  sheet.getRange(1, 1, values.length, 2).setValues(values);
  sheet.setFrozenRows(1);
}

function applyValidations_(ss) {
  const incidentSheet = ss.getSheetByName(SHEETS.INCIDENTS);
  const statusColumn = getHeaders_(incidentSheet).indexOf('Status') + 1;
  const severityColumn = getHeaders_(incidentSheet).indexOf('Severity') + 1;
  const approvalColumn =
    getHeaders_(incidentSheet).indexOf('Approval Status') + 1;

  const statusRule = SpreadsheetApp.newDataValidation()
    .requireValueInList(Object.keys(STATUS_TRANSITIONS), true)
    .setAllowInvalid(false)
    .build();

  const severityRule = SpreadsheetApp.newDataValidation()
    .requireValueInList(ALLOWED_SEVERITIES, true)
    .setAllowInvalid(false)
    .build();

  const approvalRule = SpreadsheetApp.newDataValidation()
    .requireValueInList(
      ['Not requested', 'Pending', 'Approved', 'Returned'],
      true
    )
    .setAllowInvalid(false)
    .build();

  const rows = Math.max(incidentSheet.getMaxRows() - 1, 1);
  incidentSheet.getRange(2, statusColumn, rows, 1)
    .setDataValidation(statusRule);
  incidentSheet.getRange(2, severityColumn, rows, 1)
    .setDataValidation(severityRule);
  incidentSheet.getRange(2, approvalColumn, rows, 1)
    .setDataValidation(approvalRule);
}

function validateConfiguration_() {
  const required = {
    SPREADSHEET_ID: CONFIG.SPREADSHEET_ID,
    ROOT_FOLDER_ID: CONFIG.ROOT_FOLDER_ID,
    ADMIN_EMAIL: CONFIG.ADMIN_EMAIL,
    UPDATE_FORM_URL: CONFIG.UPDATE_FORM_URL
  };

  Object.keys(required).forEach(function(key) {
    const value = String(required[key] || '');
    if (!value || value.indexOf('YOUR_') !== -1) {
      throw new Error('Replace CONFIG.' + key + ' before activation.');
    }
  });

  if (!isConfiguredEmail_(CONFIG.ADMIN_EMAIL)) {
    throw new Error('CONFIG.ADMIN_EMAIL is invalid.');
  }
}

Deployment and Authorization

1. Save the project.
2. Run setupWorkbook manually from the editor.
3. Review the authorization request and grant only the required access.
4. Replace the placeholder Directory row with valid service mappings.
5. Run installTriggers.
6. Open the Apps Script trigger list and confirm that one spreadsheet form-submit trigger and four time-driven triggers exist.
7. Submit a test incident and confirm that Incidents, Timeline, Audit Log, Processed Events, Notifications, and Drive are updated.
8. Submit an update and confirm that the verified email heading is exactly Email Address. If it differs, change both references in the script.

Duplicate Execution, Logs, and Recovery

The source sheet ID and row number form a stable event key. A completed event is ignored if the trigger is delivered again. Timeline, audit, action, and notification records also use stable identifiers derived from the event key.

Open the Apps Script Executions page to inspect trigger runs, duration, console output, and stack traces. Operational failures are also written to the Failures sheet. The scheduled retry process attempts each failed event up to three times. After that, the record remains in Manual Review for correction and controlled replay.

To recover manually, correct the underlying Directory, permission, configuration, or data issue. Change the corresponding failure status from Manual Review to Retry if an additional automated attempt is authorized, or run retryFailedEvents from the custom menu.

A Gmail send can succeed immediately before the spreadsheet status update fails. Gmail does not provide a general application-level idempotency key for this use. The Notification ID in the subject context and queue reduces uncertainty, but the small send-and-record failure window should be recognized during reconciliation.

Likely Configuration Errors

• The form response sheet name does not match the configured name.
• The form question heading differs in capitalization or punctuation.
• The verified email column has a localized heading.
• The affected service does not match Directory exactly.
• The trigger owner cannot access the root folder or uploaded file.
• A placeholder email remains in Directory.
• The update form URL or root folder ID was not replaced.
• The trigger owner’s Gmail or Apps Script service limits have been reached.

Failure Handling and Operational Reliability

The Failures sheet functions as a lightweight dead-letter queue for form events. The Notifications sheet has its own Dead Letter state for email that exhausts retry attempts.

Idempotency is applied at multiple levels: source event, timeline entry, audit entry, action creation, and notification creation. Reconciliation compares failed incident states, missing owners, and dead-letter messages and sends a daily summary when attention is required.

Staff recover a failed record by locating its Event Key, reviewing the source response, checking the corresponding Processed Events and Failure rows, fixing the cause, and authorizing a retry. They should not delete failed rows because those rows preserve the operational history.

A Complete Example

An operator at Alder Peak notices that the packaging line control network is no longer reporting controller status. The operator submits the Incident Intake Form with these values:

• Detected at: 2026-05-14 09:12
• Severity: SEV2
• Affected service: Packaging line control network
• Summary: Packaging controller monitoring unavailable
• Impact: The packaging cell is paused while control connectivity is verified.
• Description: Monitoring stopped receiving controller data after a network interruption. No safety event was reported.
• Communications required: Yes

Google Forms writes the response to row 44 of Incident Intake Responses. Apps Script creates an event key from the sheet ID and row number. The event is not found in Processed Events, so processing continues.

The service value matches an active Directory row. The script assigns the primary production-controls owner, obtains the annual sequence lock, and generates INC-2026-0043.

The duplicate check finds no open incident with the same normalized summary and service within six hours. The record enters Assigned rather than Manual Review.

Apps Script creates a folder named INC-2026-0043 - Packaging controller monitoring unavailable, moves the uploaded screenshot into it, and writes the folder URL to the incident.

A Timeline ID is generated for the intake event. An Audit ID records the initial status and severity. A Notification ID is queued for the assigned owner. Gmail sends the assignment and the queue records the sent time.

At 09:18, the owner submits a Progress note and changes the status to Investigating. At 09:27, another progress note states that a managed switch stopped forwarding traffic after an incomplete configuration change. The incident moves to Mitigating.

At 09:36, the owner submits Recovery confirmed. The recovery note states that the previous switch configuration was restored and controller telemetry returned. Apps Script records Recovery At, stores the recovery summary, and changes the status to Monitoring.

Later, the owner submits Root cause recorded. The official root cause states that the configuration deployment lacked a validation check for the controller network segment.

The owner then creates corrective action ACT-2026-0043-01: add a pre-deployment validation check for the packaging controller segment. The action has an assigned owner and due date. The action owner receives a Gmail notification.

When the action is completed, the action owner submits its Action ID and a completion note. Apps Script changes the action to Completed and adds the event to the incident timeline.

The incident owner requests closure. The script confirms that recovery and root cause are present and that no action remains open. The status changes to Awaiting Closure Approval, and the service’s designated approver receives a Gmail request.

The approver submits Approve closure using the restricted form. The verified email matches the designated approver. Apps Script writes Approval Status as Approved, Status as Closed, and the closure timestamp. The audit record identifies the actor and source event.

If the approver had requested more evidence, Return for more information would have moved the incident back to Monitoring, retained the reason in Timeline and Audit Log, and required a new closure request.

Implementation Cost

All amounts below are representative planning assumptions for this fictional scenario. They are not verified client results, vendor quotations, or current product pricing.

An existing software subscription does not make the implementation costless. Requirements work, configuration, permission design, testing, support, and maintenance still require accountable labour.

Estimated Time and Cost Savings

The savings model uses these representative assumptions:

• 12 incidents per month
• 95 minutes of current administrative handling per incident
• 38 minutes of administrative handling after core automation
• 15 percent exception rate
• 20 minutes of manual review for each exception
• Three hours of monthly system maintenance
• $60 loaded hourly labour cost
• $0 incremental recurring software cost in the core scenario
• $2,280 internal implementation cost

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

Current calculation: 12 × 95 ÷ 60 = 19.0 hours

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

New direct handling: 12 × 38 ÷ 60 = 7.6 hours

Exception handling: 12 × 15% × 20 ÷ 60 = 0.6 hours

Maintenance: 3.0 hours

Total new monthly labour: 7.6 + 0.6 + 3.0 = 11.2 hours

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

Monthly hours recovered calculation: 19.0 – 11.2 = 7.8 hours

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

Labour value calculation: 7.8 × $60 = $468 per month

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

Net value calculation: $468 – $0 = $468 per month

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

Payback calculation: $2,280 ÷ $468 = approximately 4.9 months

The monthly maintenance labour has already been included in the new-process hours. If additional recurring software is required, its monthly amount must also be subtracted from the labour value.

Recovered time does not automatically reduce payroll. It may provide additional operating capacity, quicker incident turnaround, reduced overtime, fewer administrative tasks, or the ability to manage higher incident volume without adding equivalent coordination work.

Non-Financial Benefits

• Clear ownership from the moment an incident is created
• Consistent severity and status definitions
• Fewer manual follow-ups
• More complete recovery and root-cause records
• Visible corrective-action deadlines
• Better closure evidence
• More consistent communication with reporters
• Improved analysis of incident volume and recurring services
• Reduced dependency on one employee’s memory
• More reliable post-incident review preparation

Readers should replace incident volume, current handling time, new handling time, exception rate, review time, maintenance time, labour cost, recurring software cost, and implementation cost with their own measured values.

Adding AI to the Automation

AI is added only after the rule-based workflow operates reliably. The core benefits, including IDs, validation, assignment, status control, notifications, reminders, corrective-action tracking, approval, and audit evidence, do not require AI.

Potential AI uses include summarizing approved notes, drafting a timeline, identifying missing information, suggesting categories, comparing similar incident narratives, and extracting named systems from unstructured descriptions.

Required fields, severity lists, status transitions, due-date comparisons, owner lookups, identity checks, approval rules, duplicate event keys, and open-action counts remain deterministic. AI would add risk and inconsistency to tasks that exact rules already handle well.

The Recommended AI Enhancement

The recommended enhancement is a draft post-incident timeline generated from Timeline rows that an authorized reviewer has marked Approved for AI as Yes.

• Trigger: An authorized reviewer selects an incident row and manually runs the timeline-draft function.
• AI input: Incident ID, summary, severity, affected service, and approved timeline notes with IDs and timestamps.
• System instruction: Use only supplied facts, preserve uncertainty, cite source note IDs, and do not make closure or fault decisions.
• Expected output: Structured JSON containing a summary, ordered timeline, information gaps, and confidence level.
• Validation: Apps Script validates required keys, incident ID, arrays, source-note references, and confidence values.
• Record update: Output is written to AI Drafts, not directly to the official Timeline or Incidents sheets.
• Human review: The incident owner edits and approves the draft before any content is copied into the formal review.
• Low confidence: Low-confidence drafts remain in Draft status and must not be used without additional evidence.
• Prohibited data: Credentials, private keys, authentication tokens, unrestricted chat exports, sensitive personal data, and unrelated confidential information.
• Failure behavior: Record the API error in AI Drafts and continue with manual timeline preparation.

Reusable AI Prompt

SYSTEM INSTRUCTION

You assist with drafting an operational incident timeline.

Use only the incident data and approved notes provided by the user.
Do not invent events, times, causes, impacts, actions, or conclusions.
Do not decide whether the incident should be closed.
Do not assign blame.
Preserve explicit uncertainty.
Every timeline event must cite one or more supplied source_note_ids.
If information is missing or conflicting, place it in information_gaps.
Return only JSON matching the supplied schema.

USER PROMPT

Create a draft post-incident timeline from this approved incident data:

{{INCIDENT_JSON}}

Requirements:
1. Order events by occurred_at.
2. Keep each event factual and concise.
3. Cite the source_note_ids supporting each event.
4. Do not infer a root cause unless an approved note states it.
5. Identify missing detection, impact, communication, recovery, root-cause,
   or corrective-action information in information_gaps.
6. Set confidence to low, medium, or high based on completeness and
   consistency of the supplied notes.

Expected Structured Output

{
  "incident_id": "INC-2026-0043",
  "draft_summary": "Controller monitoring was interrupted and later restored.",
  "timeline": [
    {
      "occurred_at": "2026-05-14T09:12:00Z",
      "event": "Loss of controller monitoring was detected.",
      "source_note_ids": ["TL-example001"]
    }
  ],
  "information_gaps": [
    "The approved notes do not identify when the first stakeholder update was sent."
  ],
  "confidence": "medium"
}

The following optional Apps Script code uses an API that supports structured JSON responses through the chat-completions request pattern. Confirm current model support and API requirements in the selected provider’s official documentation before deployment.

Store OPENAI_API_KEY and OPENAI_MODEL in Apps Script project properties. Set the model property to a model available to the organization that supports the requested structured-output format. Do not place the key in a spreadsheet cell.

const AI_CONFIG = Object.freeze({
  ENDPOINT: 'https://api.openai.com/v1/chat/completions',
  PROMPT_VERSION: 'incident-timeline-v1',
  MAX_ATTEMPTS: 3
});

function draftTimelineForSelectedIncident() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const activeSheet = ss.getActiveSheet();
  const activeRange = activeSheet.getActiveRange();

  if (activeSheet.getName() !== SHEETS.INCIDENTS ||
      !activeRange ||
      activeRange.getRow() < 2) {
    throw new Error(
      'Select an incident row on the Incidents sheet before running this function.'
    );
  }

  const incident = getObjectByRow_(
    activeSheet,
    activeRange.getRow()
  );
  const incidentId = String(incident['Incident ID'] || '');

  if (!incidentId) {
    throw new Error('The selected row does not contain an Incident ID.');
  }

  const timelineSheet = ss.getSheetByName(SHEETS.TIMELINE);
  const approvedNotes = getDataObjects_(timelineSheet)
    .filter(function(note) {
      return String(note['Incident ID']) === incidentId &&
        String(note['Approved for AI']).trim().toLowerCase() === 'yes';
    })
    .map(function(note) {
      return {
        source_note_id: String(note['Timeline ID']),
        occurred_at: asDate_(note['Occurred At'])
          ? asDate_(note['Occurred At']).toISOString()
          : '',
        type: String(note['Type']),
        note: String(note['Note'])
      };
    });

  if (!approvedNotes.length) {
    throw new Error(
      'No timeline rows are marked Approved for AI = Yes for this incident.'
    );
  }

  const input = {
    incident_id: incidentId,
    summary: String(incident['Summary']),
    severity: String(incident['Severity']),
    affected_service: String(incident['Affected Service']),
    approved_notes: approvedNotes
  };

  const user = Session.getActiveUser().getEmail() || CONFIG.ADMIN_EMAIL;
  const draftSheet = ss.getSheetByName(SHEETS.AI_DRAFTS);
  const draftId = stableId_(
    'AID-',
    incidentId + ':' + new Date().toISOString()
  );

  try {
    const result = requestTimelineDraft_(input);
    validateTimelineDraft_(
      result.output,
      incidentId,
      approvedNotes.map(function(note) {
        return note.source_note_id;
      })
    );

    appendObject_(draftSheet, {
      'Draft ID': draftId,
      'Incident ID': incidentId,
      'Created At': new Date(),
      'Created By': user,
      'Model': result.model,
      'Prompt Version': AI_CONFIG.PROMPT_VERSION,
      'Input Note IDs': approvedNotes.map(function(note) {
        return note.source_note_id;
      }).join(','),
      'Draft JSON': JSON.stringify(result.output, null, 2),
      'Validation Status': 'Valid',
      'Review Status': 'Draft',
      'Reviewer': '',
      'Reviewed At': '',
      'Error': ''
    });

    console.log('AI draft created: ' + draftId);
    return draftId;
  } catch (error) {
    appendObject_(draftSheet, {
      'Draft ID': draftId,
      'Incident ID': incidentId,
      'Created At': new Date(),
      'Created By': user,
      'Model': getAiProperty_('OPENAI_MODEL'),
      'Prompt Version': AI_CONFIG.PROMPT_VERSION,
      'Input Note IDs': approvedNotes.map(function(note) {
        return note.source_note_id;
      }).join(','),
      'Draft JSON': '',
      'Validation Status': 'Failed',
      'Review Status': 'Manual fallback',
      'Reviewer': '',
      'Reviewed At': '',
      'Error': truncate_(error.message, 5000)
    });

    console.error(error.stack || error.message);
    throw error;
  }
}

function requestTimelineDraft_(incidentInput) {
  const apiKey = getAiProperty_('OPENAI_API_KEY');
  const model = getAiProperty_('OPENAI_MODEL');

  const systemInstruction = [
    'You assist with drafting an operational incident timeline.',
    'Use only the incident data and approved notes provided by the user.',
    'Do not invent events, times, causes, impacts, actions, or conclusions.',
    'Do not decide whether the incident should be closed.',
    'Do not assign blame.',
    'Preserve explicit uncertainty.',
    'Every timeline event must cite supplied source_note_ids.',
    'Put missing or conflicting information in information_gaps.',
    'Return only JSON matching the supplied schema.'
  ].join(' ');

  const userPrompt = [
    'Create a draft post-incident timeline from this approved incident data:',
    JSON.stringify(incidentInput),
    'Order events by occurred_at.',
    'Keep events factual and concise.',
    'Do not infer root cause unless an approved note states it.',
    'Identify missing detection, impact, communication, recovery,',
    'root-cause, or corrective-action information.',
    'Set confidence to low, medium, or high.'
  ].join('\n');

  const requestBody = {
    model: model,
    messages: [
      {
        role: 'system',
        content: systemInstruction
      },
      {
        role: 'user',
        content: userPrompt
      }
    ],
    response_format: {
      type: 'json_schema',
      json_schema: {
        name: 'incident_timeline_draft',
        strict: true,
        schema: {
          type: 'object',
          additionalProperties: false,
          properties: {
            incident_id: {
              type: 'string'
            },
            draft_summary: {
              type: 'string'
            },
            timeline: {
              type: 'array',
              items: {
                type: 'object',
                additionalProperties: false,
                properties: {
                  occurred_at: {
                    type: 'string'
                  },
                  event: {
                    type: 'string'
                  },
                  source_note_ids: {
                    type: 'array',
                    items: {
                      type: 'string'
                    }
                  }
                },
                required: [
                  'occurred_at',
                  'event',
                  'source_note_ids'
                ]
              }
            },
            information_gaps: {
              type: 'array',
              items: {
                type: 'string'
              }
            },
            confidence: {
              type: 'string',
              enum: ['low', 'medium', 'high']
            }
          },
          required: [
            'incident_id',
            'draft_summary',
            'timeline',
            'information_gaps',
            'confidence'
          ]
        }
      }
    }
  };

  let lastError;

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

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

    if (status >= 200 && status < 300) {
      const parsed = JSON.parse(body);

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

      return {
        model: String(parsed.model || model),
        output: JSON.parse(parsed.choices[0].message.content)
      };
    }

    lastError = new Error(
      'AI API returned HTTP ' + status + ': ' + truncate_(body, 2000)
    );

    if (status !== 429 && status < 500) {
      break;
    }

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

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

function validateTimelineDraft_(
  output,
  expectedIncidentId,
  approvedNoteIds
) {
  if (!output || typeof output !== 'object') {
    throw new Error('AI output is not an object.');
  }

  if (output.incident_id !== expectedIncidentId) {
    throw new Error('AI output contains the wrong Incident ID.');
  }

  if (!Array.isArray(output.timeline) ||
      !Array.isArray(output.information_gaps)) {
    throw new Error('AI output arrays are missing.');
  }

  if (['low', 'medium', 'high'].indexOf(output.confidence) === -1) {
    throw new Error('AI confidence value is invalid.');
  }

  output.timeline.forEach(function(event, index) {
    if (!event.event ||
        !event.occurred_at ||
        !Array.isArray(event.source_note_ids) ||
        !event.source_note_ids.length) {
      throw new Error(
        'Timeline event ' + index + ' is incomplete.'
      );
    }

    if (!asDate_(event.occurred_at)) {
      throw new Error(
        'Timeline event ' + index + ' has an invalid date.'
      );
    }

    event.source_note_ids.forEach(function(noteId) {
      if (approvedNoteIds.indexOf(noteId) === -1) {
        throw new Error(
          'AI output cites an unapproved source note: ' + noteId
        );
      }
    });
  });
}

function getAiProperty_(name) {
  const value = PropertiesService.getScriptProperties()
    .getProperty(name);

  if (!value || value.indexOf('YOUR_') === 0) {
    throw new Error(
      'Set the Apps Script property ' + name + ' before using AI.'
    );
  }

  return value;
}

To test the AI enhancement, mark two or more non-sensitive Timeline rows as approved, select the corresponding incident row, and run draftTimelineForSelectedIncident. Inspect AI Drafts. Confirm that the output cites only approved note IDs and that no official incident field changes.

Likely errors include unsupported structured-output options for the selected model, an invalid API key, an unavailable model, malformed JSON, a cited note that was not approved, a rate limit, or an organizational network restriction. The fallback is manual timeline drafting from the approved notes.

Benefits of the AI Enhancement

• Less time spent arranging approved notes chronologically
• More consistent draft formatting
• Faster identification of missing communication or recovery details
• Source-note citations for reviewer verification
• Quicker preparation of a first post-incident review draft
• Improved handling of longer unstructured notes

These are AI-specific benefits. Assignment, notifications, status control, reminders, corrective-action tracking, audit evidence, and closure approval are already provided by the core automation.

What Remains Rule-Based or Human-Controlled

The AI output remains a draft. It cannot close the incident, alter the official root cause, complete corrective actions, or send high-impact communications.

Estimating the Additional Value of AI

Assume eight of the 12 monthly incidents require formal timeline drafting.

Core automation timeline effort: 8 × 12 = 96 minutes

AI review effort: 8 × 4 = 32 minutes

Expected correction effort: 8 × 25% × 3 = 6 minutes

Expected failure fallback: 8 × 5% × 12 = 4.8 minutes

Total AI-assisted effort: 32 + 6 + 4.8 = 42.8 minutes

Additional capacity recovered: 96 – 42.8 = 53.2 minutes per month

Representative labour value: 53.2 ÷ 60 × $60 = $53.20 per month

Net after AI usage budget: $53.20 – $10 = $43.20 per month

The additional financial value is modest at this volume. The stronger justification may be faster review preparation and more consistent identification of missing information. AI does not eliminate review time, corrections, or service failures.

Testing Checklist

Use synthetic sample data and non-sensitive files before processing real incident information.

Ongoing Maintenance

The IT manager is the primary system owner. The systems administrator is the backup owner. Both should understand form configuration, workbook structure, trigger ownership, failure recovery, and notification processing.

API credentials should be rotated according to company policy. Integration and model changes should be tested in a copied workbook before production deployment. Script versions should be documented, and significant changes should include a rollback copy.

Closed records should be archived according to retention policy without breaking Incident ID relationships. Former employees must be removed from forms, sheets, folders, Directory, Delegations, triggers, and API access promptly.

When to Move to Dedicated Software

The Google Workspace implementation can remain appropriate while incident volume, workflow complexity, permission requirements, and support capacity remain manageable. It should not be replaced merely because a dedicated platform exists.

Reassess the architecture when one or more of these conditions appear:

• Incident volume grows enough to create spreadsheet or trigger performance concerns.
• The business needs formal service-level agreements and breach calculations.
• Multiple locations require separate queues, calendars, and escalation teams.
• On-call scheduling and automated paging become necessary.
• Incident, problem, change, asset, and configuration records need formal relationships.
• Advanced role-based permissions are required at the individual record or field level.
• Regulatory or audit requirements demand stronger evidence controls.
• Exception rates or maintenance hours rise consistently.
• External vendors need controlled portal access.
• Employees require a mobile application or offline operation.
• Customer-facing status pages or self-service portals are required.
• Complex API integrations are needed across monitoring, manufacturing, identity, security, and asset platforms.
• The organization requires contracted platform support and defined availability commitments.
• Security risk can no longer be managed through spreadsheet, Drive, and script controls.

Relevant upgrade categories include IT service-management platforms, security incident-response platforms, manufacturing execution integrations, computerized maintenance-management systems, and custom workflow applications. Migration should preserve Incident IDs, timeline history, action relationships, approval evidence, and document links.

Implementation Checklist

• Confirm the incidents and departments included in scope.
• Define severity, status, reminder, escalation, and closure rules.
• Confirm Google Forms, Sheets, Apps Script, Gmail, and Drive are approved.
• Assign a primary automation owner and backup owner.
• Create test and production folders, forms, and workbooks.
• Restrict forms to approved identities and collect verified email addresses.
• Create the incident, timeline, action, audit, event, notification, and failure structures.
• Populate and approve the affected-service Directory.
• Define approver delegation rules.
• Map every source field to its destination.
• Configure unique Incident IDs and event idempotency.
• Implement owner assignment and possible-duplicate review.
• Implement controlled status transitions.
• Implement corrective-action creation and completion.
• Implement sequential closure approval and returned closure handling.
• Configure reminders and escalations by severity.
• Create the notification queue and Gmail retry process.
• Create the Drive folder structure and file controls.
• Protect system-generated spreadsheet fields.
• Create dashboards, filters, failure views, and overdue-action views.
• Replace every placeholder configuration value.
• Install and authorize the form-submit and time-driven triggers.
• Test normal, invalid, duplicate, failure, retry, delegation, rejection, and closure scenarios.
• Test reporting and audit evidence against source records.
• Document launch, support, rollback, and manual recovery procedures.
• Validate implementation-cost assumptions with actual internal rates.
• Validate savings assumptions using measured handling times.
• Deploy AI only after the core workflow is stable.
• Restrict AI input to approved and appropriately sanitized notes.
• Require human review of every AI-generated draft.
• Establish daily, weekly, monthly, and quarterly maintenance responsibilities.
• Document the criteria that would justify migration to dedicated software.