Nonconformance and Corrective Action Workflow in Google Workspace

The Business Situation

Alder Peak Components is a fictional 68-person manufacturer producing machined and assembled components for industrial customers. The representative business has one quality manager, two quality technicians, four manufacturing supervisors, a production manager, and approximately ten employees who may own containment or corrective actions.

The company processes approximately 35 nonconformance records each month. Issues originate from incoming inspection, in-process inspection, final inspection, customer returns, supplier discrepancies, and employee observations.

The business already uses Google Workspace for email, shared files, spreadsheets, and internal forms. Its quality team records basic issue details in a Google Sheet, but containment, root-cause analysis, corrective actions, effectiveness checks, and closure are coordinated through email and verbal follow-up.

That process records the existence of an issue without providing a reliable way to manage its complete lifecycle. Due dates are inconsistent, responsibility is unclear, evidence is stored in different folders, and recurring issues are difficult to identify.

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 manufacturing and quality teams wanted one workflow that would preserve their existing Google Workspace investment while adding controlled ownership, due dates, evidence, approval, escalation, cost-of-quality reporting, and a practical mobile interface for the production floor.

The Existing Process

The original process followed these general steps:

1. An inspector or supervisor identified a problem and submitted a short form, sent an email, or told a quality technician.
2. A quality technician copied available information into a shared spreadsheet.
3. The quality technician emailed a supervisor and asked for immediate containment.
4. Root-cause analysis was documented in an email, a local document, or a spreadsheet note.
5. Corrective actions were distributed through email without a consistent action register.
6. The quality manager periodically reviewed open rows and asked for updates.
7. A row was marked closed when the team believed the issue had been addressed.

The largest problem was not the absence of data. The business already had rows, emails, photographs, and inspection notes. The problem was that these items were not connected to a controlled sequence with defined entry conditions, owners, exit conditions, and approval evidence.

What the New System Needed to Do

The workflow also needed a manual override. Quality staff had to be able to place an issue on hold, reassign an action, request more information, reject a duplicate record, reopen an ineffective action, and recover a partially processed submission.

Implementation Approaches Considered

Improving the Existing Spreadsheet

Adding protected columns, dropdowns, and conditional formatting would reduce some entry errors. It would not provide a strong mobile experience, related action records, controlled evidence uploads, role-aware views, or reliable escalation. It was considered an interim improvement rather than a complete solution.

Using the Existing Google Workspace Environment

This approach retained familiar tools while adding AppSheet as the operational interface and Apps Script as the automation layer. It provided sufficient structure for the representative volume without introducing a separate database platform.

Using a No-Code Database and Integration Platform

A no-code database could provide stronger relational features than a spreadsheet. It would also add another system of record and another permission model. For this scenario, the expected volume and number of users did not justify moving quality records out of the existing Workspace environment.

Purchasing Dedicated QMS Software

A dedicated quality management system would be appropriate if the business required supplier portals, formal document control, calibration management, training records, advanced audit workflows, or regulated electronic signatures. Those requirements were outside the representative implementation scope.

Building a Custom Application

A custom application offered the greatest flexibility but created more responsibility for authentication, database management, backups, security testing, deployment, and support. That effort was disproportionate to 35 monthly records and an 18-person user group.

The Selected Solution

The selected implementation connected Google Forms, Google Sheets, AppSheet, Google Apps Script, Google Drive, and Gmail. The company retained its existing Google Workspace environment and added an AppSheet application for controlled operational use.

Manual data copying, informal reminder emails, folder creation, identifier generation, and spreadsheet status reconciliation were removed. Root-cause approval, effectiveness judgment, corrective-action acceptance, and final closure remained human-controlled.

AppSheet was selected instead of allowing routine edits directly in the spreadsheet. It could present role-specific forms and views while applying validation expressions before a user saved a change. Direct spreadsheet access was limited to system administrators and selected quality personnel.

System Architecture and Data Flow

• Intake: Google Forms linked to a dedicated response sheet.
• System of record: Structured tables in Google Sheets.
• Automation layer: Installable Google Apps Script triggers.
• Document storage: A controlled Google Drive root folder with one subfolder per issue.
• Notifications: Gmail messages sent by the automation account.
• Reporting: AppSheet slices and dashboards supported by Google Sheets summary views.
• AI layer: Optional structured classification through an approved AI API after the rule-based workflow is stable.

1. Submission: An employee submits the Google Form. The linked response sheet receives the timestamp, issue details, reporter email, quantity, severity, containment information, and any uploaded evidence.
2. Trigger: An installable spreadsheet form-submit trigger runs under the authorized automation account. The trigger receives the submitted row and named form values.
3. Idempotency check: Apps Script creates a source key from the response sheet ID and row number. If that key already exists in the Issues table, the event is treated as a duplicate trigger and no second record is created.
4. Validation: The script checks required fields, allowed categories, quantity, severity, risk values, and email format. Invalid records enter Manual Review instead of proceeding silently.
5. Record creation: A unique issue ID is generated and written to the Issues sheet. The script also creates a normalized recurrence key and searches recent records for a potential duplicate.
6. Ownership: The Users table is searched for an active quality owner and an active department manager. Critical or safety-related records are assigned to the quality manager.
7. Documents: Apps Script creates an issue folder in Google Drive. Form-uploaded files are copied into it, and the folder URL is written to the issue record.
8. Actions: Containment and root-cause actions are created in the Actions sheet. Each action receives an ID, owner, due date, status, and connection back to the issue.
9. Notification: The issue owner and action owner receive an email containing the issue ID, severity, due date, and available evidence link.
10. Operational work: Users open AppSheet to update containment, document root cause, create corrective actions, enter quality costs, upload evidence, and request approval.
11. Scheduled monitoring: An hourly Apps Script trigger checks due dates, sends reminders, increases escalation levels, creates effectiveness checks, aggregates costs, and identifies invalid completed actions.
12. Closure: A quality approver reviews evidence and records the final closure decision in AppSheet. The automation records the approved closure and completion date.
13. Failure path: If a post-processing step fails, the issue remains in the system with an Automation Status of Partial or Failed. It appears in the manual-review queue and can be retried without creating a duplicate issue.

Data Structure

The spreadsheet contains five controlled tables. Each table occupies its own sheet, begins with one fixed header row, and uses a permanent identifier rather than a row number as its key.

Issues Table

Actions Table

Costs Table

The Costs table stores multiple cost entries for each issue. This avoids forcing scrap, rework, downtime, freight, inspection, and prevention work into one estimate.

Users and AuditLog Tables

The Users table maps active employees to role, department, and backup approver. The AuditLog table records automation events and approval events with an event ID, timestamp, issue ID, action ID, event type, actor, source, old value, new value, and details.

The key relationships are one Issue to many Actions, one Issue to many Costs, and one Issue to many AuditLog events. Deleting an issue is disabled in AppSheet. Records are closed or marked as duplicates instead of being physically removed.

Workflow Statuses and Ownership

A root cause returned as Changes Requested moves back to Root Cause Analysis. An effectiveness result of Ineffective moves the issue back to Corrective Action In Progress and requires a revised action. A record can close only after a named human approver records approval and the approval timestamp.

Step-by-Step Implementation

Step 1: Prepare the Accounts and Permissions

1. Create or select a Google Workspace account that will own the form, spreadsheet, Apps Script project, and controlled Drive folder. An organizational automation account is preferable to a personal employee account.
2. Confirm that the selected Workspace subscriptions support the required Google services and that the AppSheet licensing arrangement covers every operational user. Product entitlements should be verified against current vendor documentation.
3. Create a Drive root folder named Quality_Nonconformance_System. Share it only with the quality administration group and the automation account.
4. Create a Google Form under the automation account and link it to a new response spreadsheet.
5. Create an AppSheet application using the same spreadsheet as its data source.
6. Identify 18 representative users: three quality users, four department managers, the production manager, selected action owners, and one backup administrator.
7. Create Google Groups where appropriate for quality administrators and read-only management users. Avoid granting broad access to individual files when a managed group can be used.
8. Use a separate copy of the spreadsheet and Drive folder for development and user acceptance testing. Do not point test automation at the production root folder.
9. Record the spreadsheet ID, root folder ID, quality manager email, and operations manager email. These values are inserted into the Apps Script configuration.
10. If the optional AI enhancement will be tested, store its API key in Apps Script Script Properties. Do not place credentials in cells or source code.

Installable Apps Script triggers run as the user who creates them. That user needs permission to read and write the spreadsheet, create files under the root folder, copy form uploads, and send Gmail notifications. A separate cloud service account is not required for this implementation.

Step 2: Build the Intake

Create an internal Google Form titled Quality Issue and Nonconformance Submission. Use the exact question names below because the Apps Script uses named form values.

Google Forms file upload requires users to sign in. This implementation therefore treats the form as an internal intake channel. External customer or supplier submissions would need a different controlled upload design.

The confirmation message tells the reporter that the submission has been received, that the quality team may request clarification, and that urgent safety or compliance concerns must also follow the company’s immediate escalation procedure. The form must not imply that submitting it replaces emergency or stop-work requirements.

Duplicate prevention occurs at two levels. A source submission key prevents one form row from being processed twice. A recurrence key and potential-duplicate search identify similar records, but the system does not automatically discard them because two defects affecting the same part may be separate valid events.

Step 3: Create the System of Record

Use these sheet names exactly:

• Issues
• Actions
• Costs
• Users
• AuditLog

The setup function in the supplied Apps Script creates these sheets and their headers. If a sheet already exists with a different header structure, the function stops instead of overwriting data.

Populate the Users table before testing. Each row includes Email, Name, Role, Department, Active, and Backup Approver Email. Supported roles are Administrator, Quality Manager, Quality Engineer, Department Manager, Action Owner, and Read Only.

Use permanent keys rather than sheet row numbers. Apps Script creates issue IDs such as NCR-2026-0001. AppSheet-created action and cost records use these initial-value expressions:

Action ID:
CONCATENATE("ACT-", UNIQUEID())

Cost ID:
CONCATENATE("COST-", UNIQUEID())

Configure the AppSheet Actions table so that Issue ID is a Ref column pointing to Issues. Configure Costs in the same way. Enable the child relationship behavior only if users should add actions and costs from an issue detail view.

Useful AppSheet validation expressions include:

Action status values:
LIST(
  "Not Started",
  "In Progress",
  "Blocked",
  "Awaiting Verification",
  "Complete",
  "Cancelled"
)

Action evidence Required_If:
[Status] = "Complete"

Active action owner Valid_If:
SELECT(
  Users[Email],
  [Active] = TRUE
)

Non-negative cost validation:
AND(
  OR(ISBLANK([Quantity]), [Quantity] >= 0),
  OR(ISBLANK([Unit Cost]), [Unit Cost] >= 0),
  OR(ISBLANK([Hours]), [Hours] >= 0),
  OR(ISBLANK([Hourly Rate]), [Hourly Rate] >= 0)
)

Cost Amount App formula:
ROUND(
  (
    IF(ISBLANK([Quantity]), 0, [Quantity])
    * IF(ISBLANK([Unit Cost]), 0, [Unit Cost])
  )
  +
  (
    IF(ISBLANK([Hours]), 0, [Hours])
    * IF(ISBLANK([Hourly Rate]), 0, [Hourly Rate])
  ),
  2
)

Create filtered AppSheet slices rather than asking users to filter the source spreadsheet. Restrict direct editing of system fields such as Issue ID, Source Submission Key, Automation Status, Retry Count, escalation values, recurrence values, and aggregated cost totals.

Step 4: Connect the Tools

AppSheet changes do not reliably invoke a spreadsheet edit trigger in the same way that a human spreadsheet edit does. The implementation therefore uses an hourly time-based Apps Script trigger to reconcile AppSheet changes, evaluate completed actions, aggregate costs, and send escalations.

The returned identifiers are written back to the source record. These include Issue ID, Action ID, Drive folder URL, cost IDs, and optional AI review status. No workflow depends on a sheet row remaining in the same position.

Step 5: Build the Core Automation

Automation 1: Create and Validate an Issue

• Trigger: A new Google Form response is added to the linked spreadsheet.
• Conditions: The source submission key does not already exist.
• Actions: Validate fields, generate an issue ID, calculate due dates, assign owners, create a recurrence key, and append the issue.
• Fields updated: Issue ID, owner, status, due dates, recurrence fields, Automation Status, and Last Automation Run.
• Notification: Reporter, issue owner, and containment owner receive appropriate notices.
• Exception: Invalid records enter Manual Review with an Error Message.

Automation 2: Create Default Actions and Evidence Folder

• Trigger: A valid issue record is created.
• Conditions: The issue does not already have the required action types or folder link.
• Actions: Create the Drive folder, copy form uploads, create containment and root-cause actions, and store their identifiers.
• Fields updated: Document Folder URL, Evidence Links, status, and automation result.
• Notification: Action owners receive due dates and issue details.
• Exception: Successful components remain in place; failed components are retried without duplication.

Automation 3: Reconcile Workflow Progress

• Trigger: Hourly scheduled Apps Script execution.
• Conditions: The issue is open and related action records meet their exit criteria.
• Actions: Move eligible records to the next stage, create an effectiveness action, aggregate costs, and record audit events.
• Fields updated: Status, Effectiveness Due, effectiveness result, closure date, cost totals, and Last Automation Run.
• Notification: The next owner is notified when human action is required.
• Exception: A completed action without evidence is flagged and cannot advance the issue.

Automation 4: Remind and Escalate

• Trigger: Hourly scheduled Apps Script execution.
• Conditions: An open action is due tomorrow, due today, or overdue and has not received a reminder that day.
• Actions: Calculate overdue age, select recipients, send a reminder, and update escalation fields.
• Fields updated: Last Reminder At, Reminder Count, Escalation Level, and Error Message.
• Notification: Level one goes to the owner, level two includes quality management, and level three includes operations management.
• Exception: If the owner is inactive, the configured backup approver is included.

The workflow uses the source key for event idempotency and checks for existing action types before creating default actions. This prevents duplicate records when a trigger is retried or a quality administrator runs manual recovery.

Step 6: Add Approvals, Reminders, and Escalations

Configure AppSheet actions for controlled approval rather than allowing approvers to type directly into approval fields.

Approve Root Cause

The approval action is available only when the record is in Root Cause Review, the root-cause summary is populated, at least one corrective action exists, and the signed-in user has an active quality role.

AND(
  [Status] = "Root Cause Review",
  ISNOTBLANK([Root Cause Summary]),
  COUNT(
    SELECT(
      Actions[Action ID],
      AND(
        [Issue ID] = [_THISROW].[Issue ID],
        [Action Type] = "Corrective Action"
      )
    )
  ) > 0,
  IN(
    USEREMAIL(),
    SELECT(
      Users[Email],
      AND(
        [Active] = TRUE,
        IN(
          [Role],
          LIST("Quality Manager", "Quality Engineer")
        )
      )
    )
  )
)

The action writes Approved, USEREMAIL(), NOW(), and Corrective Action In Progress into the corresponding approval and status fields.

Request Root-Cause Changes

A separate action writes Changes Requested and returns the issue to Root Cause Analysis. The approver must enter a note explaining what is missing or unsupported.

Complete an Action

Use a grouped AppSheet action that validates evidence, sets Status to Complete, sets Completed At to NOW(), and records the signed-in user. Hide direct status editing where practical so users cannot bypass completion controls.

Approve Closure

The closure action is available only in Closure Review, after an effectiveness result of Effective, with no open corrective or effectiveness actions. It records Closure Approval, Closure Approved By, Closure Approved At, Status, Closed At, and Last Updated By.

Reminder timing is configured as follows:

• One reminder is sent on the day before an action is due.
• Level one escalation begins one day overdue and goes to the action owner.
• Level two begins four days overdue and includes the quality manager.
• Level three begins eight days overdue and includes the operations manager.
• Only one automated reminder is sent per action per calendar day.
• Critical issues can use shorter deadlines and immediate management notification.

If an approver is unavailable, an administrator marks the user inactive and configures a backup approver. The automation copies the backup on reminders, but reassignment and approval still require a signed-in person. The system does not impersonate an unavailable approver.

Step 7: Add Documents and File Management

Under the controlled Drive root, use this folder structure:

Quality_Nonconformance_System/
  Issues/
    NCR-2026-0001/
      Form Uploads
      Containment
      Root Cause
      Corrective Actions
      Effectiveness
      Closure

The supplied script creates the issue folder. The implementation can create the stage folders in advance or add them when the first file for that stage is uploaded.

Configure AppSheet file columns to store files under an issue-specific relative path such as:

CONCATENATE(
  "Issues/",
  [Issue ID],
  "/Corrective Actions"
)

The AppSheet application’s controlled file location must be aligned with the approved Drive root. Interface labels can vary by AppSheet version, so the implementation should verify the actual resolved storage path with a test upload before launch.

• Use the original file extension and prefix replacement files with a revision date.
• Do not overwrite evidence after approval. Add a new version and preserve the prior file.
• Restrict public link sharing. Evidence links should inherit organizational access rules.
• Set file-type and file-size controls appropriate to the Workspace environment.
• Flag actions completed without an evidence file.
• Record failed uploads in the action Error Message and manual-review view.
• Apply the company’s retention schedule to closed issue folders.

Step 8: Add Reporting and Operational Views

Create the following AppSheet slices and views:

• New and Unassigned: Submitted or Manual Review records without a valid owner.
• My Open Actions: Open actions where Owner Email equals USEREMAIL().
• Overdue Actions: Open actions with Due Date earlier than TODAY().
• Root Cause Reviews: Issues waiting for quality approval.
• Effectiveness Due: Effectiveness checks due within seven days.
• Closure Reviews: Issues ready for final approval.
• Automation Failures: Issues with Partial, Failed Validation, or Retry Pending status.
• Recurring Issues: Issues where Recurring Flag is true.
• Recently Closed: Issues closed in the previous 30 days.
• Cost Review: Issues with high estimated failure cost or missing cost entries.

Create an AppSheet dashboard combining open issues by status, overdue actions by owner, recurrence by defect category, and cost by cost type. For more detailed analysis, use protected Google Sheets pivot tables based on the Issues, Actions, and Costs sheets.

Recommended calculated metrics include:

• Days open
• Containment response time
• Root-cause cycle time
• Corrective-action cycle time
• Percentage of actions overdue
• Issues reopened after an ineffective check
• Repeat count by part, process, area, and defect category
• Internal failure cost by month
• External failure cost by customer-return category
• Prevention and appraisal spending attached to corrective work

Dashboard ownership remains with the quality manager. The quality team reviews failed automation daily, overdue action trends weekly, and cost-of-quality and recurring-issue trends monthly.

Step 9: Add Security and Governance Controls

• Use least-privilege access for the spreadsheet, form, Drive root, and AppSheet application.
• Allow routine users to work through AppSheet rather than opening the source spreadsheet.
• Use AppSheet security filters so quality administrators can see all issues while department users see only their department or assigned actions.
• Protect system-managed spreadsheet columns from manual edits.
• Store API credentials in Apps Script Script Properties.
• Do not include customer personal data, employee medical data, controlled technical data, or legal conclusions in an AI request unless an approved governance process explicitly allows it.
• Retain Apps Script execution logs, the AuditLog sheet, approval timestamps, and Google file history according to policy.
• Remove former employees from Google Groups, AppSheet users, Drive permissions, and the Users table.
• Back up the spreadsheet and controlled evidence folder on a scheduled basis.
• Require human approval for root cause, corrective-action acceptance, effectiveness judgment, cost approval when required, and final closure.

A representative AppSheet security filter for the Issues table is:

OR(
  IN(
    LOOKUP(USEREMAIL(), "Users", "Email", "Role"),
    LIST("Administrator", "Quality Manager", "Quality Engineer")
  ),
  [Owner Email] = USEREMAIL(),
  [Department] =
    LOOKUP(USEREMAIL(), "Users", "Email", "Department")
)

Security filters must be tested with accounts from each role. A view filter alone is not a substitute for a data security filter.

Step 10: Deploy and Test

1. Complete configuration in a development copy of the spreadsheet, form, app, and Drive folder.
2. Run the setup function and populate test users.
3. Authorize the script with the automation account.
4. Install the form-submit and hourly triggers.
5. Submit at least one test case for every severity, department, and issue type.
6. Use separate test accounts for a reporter, action owner, quality approver, operations manager, and unauthorized user.
7. Run user acceptance testing with the quality team and two production supervisors.
8. Verify record IDs, action IDs, folders, evidence access, emails, approval controls, dashboards, cost totals, and audit records.
9. Pilot the system with one production area while the original process remains available as a rollback option.
10. After the pilot, freeze structural spreadsheet changes and activate the production form and AppSheet application.
11. Publish a short operating procedure defining submission, containment, root cause, action completion, approval, escalation, and recovery responsibilities.
12. Monitor trigger executions and the Automation Failures view daily during the first two weeks.

Rollback consists of disabling the installable triggers, making the intake form unavailable, and returning users to the controlled prior process. Existing records and evidence remain preserved for reconciliation.

Code and Configuration

The following Google Apps Script is placed in the spreadsheet linked to the Google Form. Open the spreadsheet, open the Apps Script editor from the spreadsheet’s extension tools, replace the configuration placeholders, and paste the complete script into a script file.

The script creates the system sheets, processes form submissions, generates records and actions, creates Drive folders, copies uploaded files, sends reminders, escalates overdue actions, reconciles workflow stages, aggregates quality costs, supports manual recovery, and optionally calls an AI API.

const CONFIG = Object.freeze({
  SPREADSHEET_ID: 'YOUR_SPREADSHEET_ID',
  ROOT_FOLDER_ID: 'YOUR_FOLDER_ID',
  QUALITY_MANAGER_EMAIL: 'YOUR_EMAIL_ADDRESS',
  OPERATIONS_MANAGER_EMAIL: 'YOUR_EMAIL_ADDRESS',
  TIME_ZONE: 'Etc/UTC',
  AI_ENABLED: false,
  AI_MODEL: 'YOUR_AI_MODEL',
  SHEETS: {
    ISSUES: 'Issues',
    ACTIONS: 'Actions',
    COSTS: 'Costs',
    USERS: 'Users',
    AUDIT: 'AuditLog'
  }
});

const HEADERS = Object.freeze({
  Issues: [
    'Issue ID',
    'Created At',
    'Source Submission Key',
    'Reporter Email',
    'Department',
    'Area',
    'Issue Type',
    'Defect Category',
    'Part or Process',
    'Lot or Work Order',
    'Quantity Affected',
    'Severity',
    'Risk Flag',
    'Description',
    'Containment Summary',
    'Owner Email',
    'Status',
    'Containment Due',
    'Root Cause Due',
    'Corrective Action Due',
    'Effectiveness Due',
    'Root Cause Method',
    'Root Cause Summary',
    'Cause Category',
    'Root Cause Approval',
    'Root Cause Approved By',
    'Root Cause Approved At',
    'Effectiveness Result',
    'Effectiveness Verified By',
    'Effectiveness Verified At',
    'Closure Approval',
    'Closure Approved By',
    'Closure Approved At',
    'Potential Duplicate Of',
    'Recurrence Key',
    'Similar Issues 90d',
    'Recurring Flag',
    'Document Folder URL',
    'Evidence Links',
    'Automation Status',
    'Last Automation Run',
    'Retry Count',
    'Error Message',
    'Last Reminder At',
    'Escalation Level',
    'Closed At',
    'Internal Failure Cost',
    'External Failure Cost',
    'Appraisal Cost',
    'Prevention Cost',
    'Total Cost of Quality',
    'AI Category Suggestion',
    'AI Summary',
    'AI Missing Information',
    'AI Recurrence Terms',
    'AI Confidence',
    'AI Review Status',
    'Notes',
    'Last Updated',
    'Last Updated By'
  ],
  Actions: [
    'Action ID',
    'Issue ID',
    'Action Type',
    'Description',
    'Owner Email',
    'Due Date',
    'Status',
    'Evidence File',
    'Completed At',
    'Verified By',
    'Verification Result',
    'Created At',
    'Last Updated',
    'Last Reminder At',
    'Reminder Count',
    'Escalation Level',
    'Error Message'
  ],
  Costs: [
    'Cost ID',
    'Issue ID',
    'Cost Type',
    'Description',
    'Quantity',
    'Unit Cost',
    'Hours',
    'Hourly Rate',
    'Amount',
    'Cost Date',
    'Entered By',
    'Approved By',
    'Created At',
    'Last Updated'
  ],
  Users: [
    'Email',
    'Name',
    'Role',
    'Department',
    'Active',
    'Backup Approver Email'
  ],
  AuditLog: [
    'Event ID',
    'Timestamp',
    'Issue ID',
    'Action ID',
    'Event Type',
    'Old Value',
    'New Value',
    'Actor',
    'Source',
    'Details'
  ]
});

const FORM_FIELDS = Object.freeze({
  REPORTER: 'Reporter Email',
  DEPARTMENT: 'Department',
  AREA: 'Area or Work Center',
  ISSUE_TYPE: 'Issue Type',
  PART_PROCESS: 'Part or Process',
  LOT_WORK_ORDER: 'Lot or Work Order',
  QUANTITY: 'Quantity Affected',
  SEVERITY: 'Severity',
  RISK: 'Immediate Safety or Compliance Risk?',
  DESCRIPTION: 'Description',
  CONTAINMENT: 'Containment Taken',
  EVIDENCE: 'Evidence Upload'
});

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Quality Workflow')
    .addItem('Set Up System Sheets', 'setupSystem')
    .addItem('Install Triggers', 'installTriggers')
    .addItem('Run Scheduled Checks Now', 'runScheduledChecks')
    .addItem('Retry Selected Issue', 'retrySelectedIssue')
    .addItem('Run AI Review for Selected Issue', 'runAiReviewForSelectedIssue')
    .addToUi();
}

function setupSystem() {
  assertConfiguration_();
  const ss = getSpreadsheet_();

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

  applyDataValidation_();
  Logger.log('System sheets created and validated.');
}

function installTriggers() {
  assertConfiguration_();
  const ss = getSpreadsheet_();
  const managedFunctions = ['handleFormSubmit', 'runScheduledChecks'];

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

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

  ScriptApp.newTrigger('runScheduledChecks')
    .timeBased()
    .everyHours(1)
    .create();

  Logger.log('Form-submit and hourly triggers installed.');
}

function handleFormSubmit(e) {
  let issueId = '';

  try {
    issueId = processFormSubmission_(e);
    Logger.log('Processed issue: ' + issueId);
  } catch (error) {
    Logger.log(error.stack || error.message);

    try {
      appendAudit_(
        issueId,
        '',
        'FORM_PROCESSING_FAILED',
        '',
        '',
        'Apps Script',
        'Form Submit Trigger',
        error.message
      );
    } catch (auditError) {
      Logger.log('Audit logging also failed: ' + auditError.message);
    }

    try {
      sendEmailWithRetry_(
        [CONFIG.QUALITY_MANAGER_EMAIL],
        'Quality workflow form processing failed',
        'A form submission could not be processed. Review Apps Script executions and the form response sheet.\n\nError: ' +
          error.message
      );
    } catch (emailError) {
      Logger.log('Failure notification could not be sent: ' + emailError.message);
    }

    throw error;
  }
}

function processFormSubmission_(e) {
  if (!e || !e.range || !e.namedValues) {
    throw new Error(
      'This function requires an installable spreadsheet form-submit event.'
    );
  }

  const lock = LockService.getScriptLock();
  lock.waitLock(30000);

  try {
    const sourceSheet = e.range.getSheet();
    const sourceKey = sourceSheet.getSheetId() + ':' + e.range.getRow();
    const existing = findObjectByField_(
      CONFIG.SHEETS.ISSUES,
      'Source Submission Key',
      sourceKey
    );

    if (existing) {
      appendAudit_(
        existing['Issue ID'],
        '',
        'DUPLICATE_EVENT_IGNORED',
        sourceKey,
        sourceKey,
        'Apps Script',
        'Form Submit Trigger',
        'The source form row was already processed.'
      );
      return existing['Issue ID'];
    }

    const namedValues = e.namedValues;
    const issueId = nextId_(
      'NCR',
      CONFIG.SHEETS.ISSUES,
      'Issue ID',
      4
    );
    const createdAt = new Date();
    const severity = clean_(firstValue_(namedValues, FORM_FIELDS.SEVERITY));
    const riskFlag = clean_(firstValue_(namedValues, FORM_FIELDS.RISK));
    const department = clean_(
      firstValue_(namedValues, FORM_FIELDS.DEPARTMENT)
    );
    const issueType = clean_(
      firstValue_(namedValues, FORM_FIELDS.ISSUE_TYPE)
    );
    const partProcess = clean_(
      firstValue_(namedValues, FORM_FIELDS.PART_PROCESS)
    );
    const area = clean_(firstValue_(namedValues, FORM_FIELDS.AREA));
    const quantity = numberOrBlank_(
      firstValue_(namedValues, FORM_FIELDS.QUANTITY)
    );

    const ownerEmail = chooseIssueOwner_(
      severity,
      riskFlag,
      department
    );
    const dueDates = calculateDueDates_(severity, createdAt);
    const recurrenceKey = buildRecurrenceKey_(
      partProcess,
      issueType,
      area
    );
    const similarRecords = findRecentSimilarIssues_(
      recurrenceKey,
      createdAt,
      90
    );
    const potentialDuplicate = findPotentialDuplicate_(
      partProcess,
      clean_(
        firstValue_(namedValues, FORM_FIELDS.LOT_WORK_ORDER)
      ),
      issueType,
      createdAt
    );

    const issue = {
      'Issue ID': issueId,
      'Created At': createdAt,
      'Source Submission Key': sourceKey,
      'Reporter Email': clean_(
        firstValue_(namedValues, FORM_FIELDS.REPORTER)
      ),
      'Department': department,
      'Area': area,
      'Issue Type': issueType,
      'Defect Category': '',
      'Part or Process': partProcess,
      'Lot or Work Order': clean_(
        firstValue_(namedValues, FORM_FIELDS.LOT_WORK_ORDER)
      ),
      'Quantity Affected': quantity,
      'Severity': severity,
      'Risk Flag': riskFlag,
      'Description': clean_(
        firstValue_(namedValues, FORM_FIELDS.DESCRIPTION)
      ),
      'Containment Summary': clean_(
        firstValue_(namedValues, FORM_FIELDS.CONTAINMENT)
      ),
      'Owner Email': ownerEmail,
      'Status': 'Submitted',
      'Containment Due': dueDates.containment,
      'Root Cause Due': dueDates.rootCause,
      'Corrective Action Due': dueDates.correctiveAction,
      'Effectiveness Due': '',
      'Root Cause Method': '',
      'Root Cause Summary': '',
      'Cause Category': '',
      'Root Cause Approval': 'Pending',
      'Root Cause Approved By': '',
      'Root Cause Approved At': '',
      'Effectiveness Result': '',
      'Effectiveness Verified By': '',
      'Effectiveness Verified At': '',
      'Closure Approval': 'Pending',
      'Closure Approved By': '',
      'Closure Approved At': '',
      'Potential Duplicate Of': potentialDuplicate || '',
      'Recurrence Key': recurrenceKey,
      'Similar Issues 90d': similarRecords.length + 1,
      'Recurring Flag': similarRecords.length + 1 >= 3,
      'Document Folder URL': '',
      'Evidence Links': '',
      'Automation Status': 'Processing',
      'Last Automation Run': new Date(),
      'Retry Count': 0,
      'Error Message': '',
      'Last Reminder At': '',
      'Escalation Level': 0,
      'Closed At': '',
      'Internal Failure Cost': 0,
      'External Failure Cost': 0,
      'Appraisal Cost': 0,
      'Prevention Cost': 0,
      'Total Cost of Quality': 0,
      'AI Category Suggestion': '',
      'AI Summary': '',
      'AI Missing Information': '',
      'AI Recurrence Terms': '',
      'AI Confidence': '',
      'AI Review Status': CONFIG.AI_ENABLED ? 'Pending' : 'Not Enabled',
      'Notes': '',
      'Last Updated': new Date(),
      'Last Updated By': 'Apps Script'
    };

    appendObject_(CONFIG.SHEETS.ISSUES, issue);

    const validationErrors = validateIssue_(issue);

    if (validationErrors.length > 0) {
      updateObjectById_(
        CONFIG.SHEETS.ISSUES,
        'Issue ID',
        issueId,
        {
          'Status': 'Manual Review',
          'Automation Status': 'Failed Validation',
          'Error Message': validationErrors.join(' | '),
          'Last Automation Run': new Date(),
          'Last Updated': new Date()
        }
      );

      appendAudit_(
        issueId,
        '',
        'VALIDATION_FAILED',
        '',
        'Manual Review',
        'Apps Script',
        'Form Submit Trigger',
        validationErrors.join(' | ')
      );

      sendEmailWithRetry_(
        uniqueValidEmails_([
          CONFIG.QUALITY_MANAGER_EMAIL,
          ownerEmail
        ]),
        'Manual review required for ' + issueId,
        'A quality issue failed validation and requires review.\n\n' +
          'Issue: ' + issueId + '\n' +
          'Errors: ' + validationErrors.join(' | ')
      );

      return issueId;
    }

    const postProcessingErrors = [];
    let folderUrl = '';
    let evidenceLinks = '';

    try {
      const issueFolder = getOrCreateIssueFolder_(issueId);
      folderUrl = issueFolder.getUrl();
      evidenceLinks = copyFormUploads_(
        firstValue_(namedValues, FORM_FIELDS.EVIDENCE),
        issueFolder
      ).join('\n');
    } catch (error) {
      postProcessingErrors.push('Drive: ' + error.message);
    }

    try {
      ensureDefaultActions_(issue);
    } catch (error) {
      postProcessingErrors.push('Actions: ' + error.message);
    }

    try {
      notifyNewIssue_(issue, folderUrl);
    } catch (error) {
      postProcessingErrors.push('Notification: ' + error.message);
    }

    updateObjectById_(
      CONFIG.SHEETS.ISSUES,
      'Issue ID',
      issueId,
      {
        'Status': 'Containment Required',
        'Document Folder URL': folderUrl,
        'Evidence Links': evidenceLinks,
        'Automation Status':
          postProcessingErrors.length > 0 ? 'Partial' : 'Complete',
        'Error Message': postProcessingErrors.join(' | '),
        'Last Automation Run': new Date(),
        'Last Updated': new Date()
      }
    );

    appendAudit_(
      issueId,
      '',
      'ISSUE_CREATED',
      'Submitted',
      'Containment Required',
      'Apps Script',
      'Form Submit Trigger',
      postProcessingErrors.length > 0
        ? postProcessingErrors.join(' | ')
        : 'Issue, actions, folder, and notifications created.'
    );

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

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

  try {
    refreshCostTotals_();
    checkActionDeadlines_();
    progressAllIssues_();
  } catch (error) {
    Logger.log(error.stack || error.message);

    appendAudit_(
      '',
      '',
      'SCHEDULED_CHECK_FAILED',
      '',
      '',
      'Apps Script',
      'Hourly Trigger',
      error.message
    );

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

  if (CONFIG.AI_ENABLED) {
    processAiQueue_(10);
  }
}

function checkActionDeadlines_() {
  const actions = getObjects_(CONFIG.SHEETS.ACTIONS);
  const today = startOfDay_(new Date());

  actions.forEach(function(action) {
    try {
      if (
        ['Complete', 'Cancelled'].indexOf(clean_(action.Status)) !== -1
      ) {
        return;
      }

      const dueDate = asDate_(action['Due Date']);
      if (!dueDate) {
        updateObjectById_(
          CONFIG.SHEETS.ACTIONS,
          'Action ID',
          action['Action ID'],
          {'Error Message': 'A valid due date is required.'}
        );
        return;
      }

      const daysOverdue = daysBetween_(
        startOfDay_(dueDate),
        today
      );
      const daysUntilDue = daysBetween_(
        today,
        startOfDay_(dueDate)
      );
      const lastReminder = asDate_(action['Last Reminder At']);

      if (
        lastReminder &&
        startOfDay_(lastReminder).getTime() === today.getTime()
      ) {
        return;
      }

      let shouldNotify = false;
      let escalationLevel = Number(action['Escalation Level'] || 0);

      if (daysUntilDue === 1 || daysUntilDue === 0) {
        shouldNotify = true;
      }

      if (daysOverdue >= 1) {
        shouldNotify = true;
        escalationLevel = 1;
      }

      if (daysOverdue >= 4) {
        escalationLevel = 2;
      }

      if (daysOverdue >= 8) {
        escalationLevel = 3;
      }

      if (!shouldNotify) {
        return;
      }

      const recipients = [action['Owner Email']];
      const backup = findBackupApprover_(action['Owner Email']);

      if (backup) {
        recipients.push(backup);
      }

      if (escalationLevel >= 2) {
        recipients.push(CONFIG.QUALITY_MANAGER_EMAIL);
      }

      if (escalationLevel >= 3) {
        recipients.push(CONFIG.OPERATIONS_MANAGER_EMAIL);
      }

      const subjectPrefix =
        daysOverdue > 0 ? 'Overdue quality action' : 'Quality action due';

      sendEmailWithRetry_(
        uniqueValidEmails_(recipients),
        subjectPrefix + ': ' + action['Action ID'],
        'Action: ' + action['Action ID'] + '\n' +
          'Issue: ' + action['Issue ID'] + '\n' +
          'Type: ' + action['Action Type'] + '\n' +
          'Due date: ' + formatDate_(dueDate) + '\n' +
          'Days overdue: ' + Math.max(daysOverdue, 0) + '\n' +
          'Escalation level: ' + escalationLevel + '\n\n' +
          clean_(action.Description)
      );

      updateObjectById_(
        CONFIG.SHEETS.ACTIONS,
        'Action ID',
        action['Action ID'],
        {
          'Last Reminder At': new Date(),
          'Reminder Count': Number(action['Reminder Count'] || 0) + 1,
          'Escalation Level': escalationLevel,
          'Error Message': '',
          'Last Updated': new Date()
        }
      );

      appendAudit_(
        action['Issue ID'],
        action['Action ID'],
        'ACTION_REMINDER_SENT',
        '',
        String(escalationLevel),
        'Apps Script',
        'Hourly Trigger',
        'Reminder sent to: ' +
          uniqueValidEmails_(recipients).join(', ')
      );
    } catch (error) {
      Logger.log(
        'Action check failed for ' +
          action['Action ID'] +
          ': ' +
          error.message
      );

      updateObjectById_(
        CONFIG.SHEETS.ACTIONS,
        'Action ID',
        action['Action ID'],
        {
          'Error Message': error.message,
          'Last Updated': new Date()
        }
      );
    }
  });
}

function progressAllIssues_() {
  const issues = getObjects_(CONFIG.SHEETS.ISSUES);
  const actions = getObjects_(CONFIG.SHEETS.ACTIONS);
  const actionsByIssue = {};

  actions.forEach(function(action) {
    const issueId = clean_(action['Issue ID']);

    if (!actionsByIssue[issueId]) {
      actionsByIssue[issueId] = [];
    }

    actionsByIssue[issueId].push(action);
  });

  issues.forEach(function(issue) {
    try {
      const status = clean_(issue.Status);

      if (
        ['Closed', 'Rejected or Duplicate', 'On Hold', 'Manual Review']
          .indexOf(status) !== -1
      ) {
        return;
      }

      const related = actionsByIssue[issue['Issue ID']] || [];
      const containment = actionsOfType_(related, 'Containment');
      const rootCause = actionsOfType_(related, 'Root Cause Analysis');
      const corrective = actionsOfType_(related, 'Corrective Action');
      const effectiveness = actionsOfType_(related, 'Effectiveness Check');
      let newStatus = status;
      const updates = {};

      flagInvalidCompletedActions_(related);

      if (
        status === 'Containment Required' &&
        allActionsComplete_(containment)
      ) {
        newStatus = 'Root Cause Analysis';
      }

      if (
        status === 'Root Cause Analysis' &&
        allActionsComplete_(rootCause) &&
        clean_(issue['Root Cause Summary'])
      ) {
        newStatus = 'Root Cause Review';
      }

      if (
        clean_(issue['Root Cause Approval']) === 'Changes Requested'
      ) {
        newStatus = 'Root Cause Analysis';
      }

      if (
        clean_(issue['Root Cause Approval']) === 'Approved' &&
        corrective.length > 0 &&
        ['Root Cause Review', 'Root Cause Analysis']
          .indexOf(newStatus) !== -1
      ) {
        newStatus = 'Corrective Action In Progress';
      }

      if (
        newStatus === 'Corrective Action In Progress' &&
        allActionsComplete_(corrective)
      ) {
        if (effectiveness.length === 0) {
          const effectivenessDue = addBusinessDays_(new Date(), 14);
          appendObject_(CONFIG.SHEETS.ACTIONS, {
            'Action ID': nextId_(
              'ACT',
              CONFIG.SHEETS.ACTIONS,
              'Action ID',
              5
            ),
            'Issue ID': issue['Issue ID'],
            'Action Type': 'Effectiveness Check',
            'Description':
              'Verify that corrective actions prevented recurrence and did not introduce a new problem.',
            'Owner Email': issue['Owner Email'],
            'Due Date': effectivenessDue,
            'Status': 'Not Started',
            'Evidence File': '',
            'Completed At': '',
            'Verified By': '',
            'Verification Result': '',
            'Created At': new Date(),
            'Last Updated': new Date(),
            'Last Reminder At': '',
            'Reminder Count': 0,
            'Escalation Level': 0,
            'Error Message': ''
          });
          updates['Effectiveness Due'] = effectivenessDue;
        }

        newStatus = 'Effectiveness Review';
      }

      if (
        newStatus === 'Effectiveness Review' &&
        effectiveness.length > 0 &&
        allActionsComplete_(effectiveness)
      ) {
        const check = effectiveness[0];
        const result = clean_(check['Verification Result']);

        if (result === 'Effective') {
          newStatus = 'Closure Review';
          updates['Effectiveness Result'] = result;
          updates['Effectiveness Verified By'] = check['Verified By'];
          updates['Effectiveness Verified At'] = check['Completed At'];
        } else if (
          result === 'Ineffective' ||
          result === 'Inconclusive'
        ) {
          newStatus = 'Corrective Action In Progress';
          updates['Effectiveness Result'] = result;
          updates['Root Cause Approval'] = 'Pending';
        }
      }

      if (
        newStatus === 'Closure Review' &&
        clean_(issue['Closure Approval']) === 'Approved' &&
        clean_(issue['Closure Approved By'])
      ) {
        newStatus = 'Closed';
        updates['Closed At'] =
          asDate_(issue['Closure Approved At']) || new Date();
      }

      updates.Status = newStatus;
      updates['Last Automation Run'] = new Date();
      updates['Last Updated'] = new Date();

      updateObjectById_(
        CONFIG.SHEETS.ISSUES,
        'Issue ID',
        issue['Issue ID'],
        updates
      );

      if (newStatus !== status) {
        appendAudit_(
          issue['Issue ID'],
          '',
          'STATUS_CHANGED',
          status,
          newStatus,
          'Apps Script',
          'Hourly Trigger',
          'Workflow exit conditions were satisfied.'
        );
      }
    } catch (error) {
      updateObjectById_(
        CONFIG.SHEETS.ISSUES,
        'Issue ID',
        issue['Issue ID'],
        {
          'Automation Status': 'Partial',
          'Error Message': error.message,
          'Last Automation Run': new Date()
        }
      );
    }
  });
}

function refreshCostTotals_() {
  const costs = getObjects_(CONFIG.SHEETS.COSTS);
  const totals = {};

  costs.forEach(function(cost) {
    const issueId = clean_(cost['Issue ID']);
    const amount = roundCurrency_(
      numberOrZero_(cost.Quantity) * numberOrZero_(cost['Unit Cost']) +
      numberOrZero_(cost.Hours) * numberOrZero_(cost['Hourly Rate'])
    );

    if (!totals[issueId]) {
      totals[issueId] = {
        'Internal Failure': 0,
        'External Failure': 0,
        'Appraisal': 0,
        'Prevention': 0
      };
    }

    if (Object.prototype.hasOwnProperty.call(
      totals[issueId],
      cost['Cost Type']
    )) {
      totals[issueId][cost['Cost Type']] += amount;
    }

    if (Number(cost.Amount || 0) !== amount) {
      updateObjectById_(
        CONFIG.SHEETS.COSTS,
        'Cost ID',
        cost['Cost ID'],
        {
          'Amount': amount,
          'Last Updated': new Date()
        }
      );
    }
  });

  getObjects_(CONFIG.SHEETS.ISSUES).forEach(function(issue) {
    const value = totals[issue['Issue ID']] || {
      'Internal Failure': 0,
      'External Failure': 0,
      'Appraisal': 0,
      'Prevention': 0
    };

    const total = roundCurrency_(
      value['Internal Failure'] +
      value['External Failure'] +
      value.Appraisal +
      value.Prevention
    );

    updateObjectById_(
      CONFIG.SHEETS.ISSUES,
      'Issue ID',
      issue['Issue ID'],
      {
        'Internal Failure Cost':
          roundCurrency_(value['Internal Failure']),
        'External Failure Cost':
          roundCurrency_(value['External Failure']),
        'Appraisal Cost': roundCurrency_(value.Appraisal),
        'Prevention Cost': roundCurrency_(value.Prevention),
        'Total Cost of Quality': total
      }
    );
  });
}

function retrySelectedIssue() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const row = sheet.getActiveRange().getRow();

  if (
    sheet.getName() !== CONFIG.SHEETS.ISSUES ||
    row < 2
  ) {
    throw new Error('Select a data row in the Issues sheet.');
  }

  const issue = objectFromRow_(sheet, row);
  const errors = validateIssue_(issue);

  if (errors.length > 0) {
    updateObjectById_(
      CONFIG.SHEETS.ISSUES,
      'Issue ID',
      issue['Issue ID'],
      {
        'Status': 'Manual Review',
        'Automation Status': 'Failed Validation',
        'Error Message': errors.join(' | '),
        'Retry Count': Number(issue['Retry Count'] || 0) + 1,
        'Last Automation Run': new Date()
      }
    );
    throw new Error(errors.join(' | '));
  }

  const issueFolder = getOrCreateIssueFolder_(issue['Issue ID']);
  ensureDefaultActions_(issue);
  notifyNewIssue_(issue, issueFolder.getUrl());

  updateObjectById_(
    CONFIG.SHEETS.ISSUES,
    'Issue ID',
    issue['Issue ID'],
    {
      'Status':
        issue.Status === 'Manual Review'
          ? 'Containment Required'
          : issue.Status,
      'Document Folder URL': issueFolder.getUrl(),
      'Automation Status': 'Recovered',
      'Error Message': '',
      'Retry Count': Number(issue['Retry Count'] || 0) + 1,
      'Last Automation Run': new Date(),
      'Last Updated': new Date()
    }
  );

  appendAudit_(
    issue['Issue ID'],
    '',
    'MANUAL_RETRY_COMPLETED',
    issue['Automation Status'],
    'Recovered',
    Session.getActiveUser().getEmail(),
    'Spreadsheet Menu',
    'Missing folder, actions, and notifications were reconciled.'
  );
}

function ensureDefaultActions_(issue) {
  const existing = getObjects_(CONFIG.SHEETS.ACTIONS).filter(
    function(action) {
      return action['Issue ID'] === issue['Issue ID'];
    }
  );
  const existingTypes = existing.map(function(action) {
    return action['Action Type'];
  });
  const departmentOwner =
    findUserByRole_('Department Manager', issue.Department) ||
    issue['Owner Email'];

  if (existingTypes.indexOf('Containment') === -1) {
    appendObject_(CONFIG.SHEETS.ACTIONS, {
      'Action ID': nextId_(
        'ACT',
        CONFIG.SHEETS.ACTIONS,
        'Action ID',
        5
      ),
      'Issue ID': issue['Issue ID'],
      'Action Type': 'Containment',
      'Description':
        'Control affected product or process, identify scope, and attach containment evidence.',
      'Owner Email': departmentOwner,
      'Due Date': issue['Containment Due'],
      'Status': 'Not Started',
      'Evidence File': '',
      'Completed At': '',
      'Verified By': '',
      'Verification Result': '',
      'Created At': new Date(),
      'Last Updated': new Date(),
      'Last Reminder At': '',
      'Reminder Count': 0,
      'Escalation Level': 0,
      'Error Message': ''
    });
  }

  if (existingTypes.indexOf('Root Cause Analysis') === -1) {
    appendObject_(CONFIG.SHEETS.ACTIONS, {
      'Action ID': nextId_(
        'ACT',
        CONFIG.SHEETS.ACTIONS,
        'Action ID',
        5
      ),
      'Issue ID': issue['Issue ID'],
      'Action Type': 'Root Cause Analysis',
      'Description':
        'Complete a supported root-cause analysis and document the evidence used.',
      'Owner Email': departmentOwner,
      'Due Date': issue['Root Cause Due'],
      'Status': 'Not Started',
      'Evidence File': '',
      'Completed At': '',
      'Verified By': '',
      'Verification Result': '',
      'Created At': new Date(),
      'Last Updated': new Date(),
      'Last Reminder At': '',
      'Reminder Count': 0,
      'Escalation Level': 0,
      'Error Message': ''
    });
  }
}

function notifyNewIssue_(issue, folderUrl) {
  const containmentOwner =
    findUserByRole_('Department Manager', issue.Department) ||
    issue['Owner Email'];
  const recipients = uniqueValidEmails_([
    issue['Reporter Email'],
    issue['Owner Email'],
    containmentOwner
  ]);

  sendEmailWithRetry_(
    recipients,
    'Quality issue assigned: ' + issue['Issue ID'],
    'Issue: ' + issue['Issue ID'] + '\n' +
      'Severity: ' + issue.Severity + '\n' +
      'Department: ' + issue.Department + '\n' +
      'Part or process: ' + issue['Part or Process'] + '\n' +
      'Containment due: ' +
        formatDate_(asDate_(issue['Containment Due'])) + '\n' +
      'Folder: ' + (folderUrl || 'Folder creation pending') + '\n\n' +
      clean_(issue.Description)
  );
}

function validateIssue_(issue) {
  const errors = [];
  const requiredText = [
    'Reporter Email',
    'Department',
    'Area',
    'Issue Type',
    'Part or Process',
    'Severity',
    'Risk Flag',
    'Description',
    'Owner Email'
  ];

  requiredText.forEach(function(field) {
    if (!clean_(issue[field])) {
      errors.push(field + ' is required');
    }
  });

  if (!isValidEmail_(issue['Reporter Email'])) {
    errors.push('Reporter Email is invalid');
  }

  if (!isValidEmail_(issue['Owner Email'])) {
    errors.push('Owner Email is invalid');
  }

  if (
    ['Minor', 'Major', 'Critical'].indexOf(issue.Severity) === -1
  ) {
    errors.push('Severity is not an allowed value');
  }

  if (['Yes', 'No'].indexOf(issue['Risk Flag']) === -1) {
    errors.push('Risk Flag must be Yes or No');
  }

  if (
    issue['Quantity Affected'] === '' ||
    Number(issue['Quantity Affected']) <= 0
  ) {
    errors.push('Quantity Affected must be greater than zero');
  }

  return errors;
}

function calculateDueDates_(severity, startDate) {
  const rules = {
    Critical: {containment: 0, rootCause: 3, correctiveAction: 10},
    Major: {containment: 1, rootCause: 5, correctiveAction: 20},
    Minor: {containment: 2, rootCause: 10, correctiveAction: 30}
  };
  const selected = rules[severity] || rules.Major;

  return {
    containment: addBusinessDays_(startDate, selected.containment),
    rootCause: addBusinessDays_(startDate, selected.rootCause),
    correctiveAction:
      addBusinessDays_(startDate, selected.correctiveAction)
  };
}

function chooseIssueOwner_(severity, riskFlag, department) {
  if (severity === 'Critical' || riskFlag === 'Yes') {
    return (
      findUserByRole_('Quality Manager', '') ||
      CONFIG.QUALITY_MANAGER_EMAIL
    );
  }

  return (
    findUserByRole_('Quality Engineer', department) ||
    findUserByRole_('Quality Engineer', '') ||
    CONFIG.QUALITY_MANAGER_EMAIL
  );
}

function findUserByRole_(role, department) {
  const users = getObjects_(CONFIG.SHEETS.USERS);

  const match = users.find(function(user) {
    const departmentMatches =
      !department ||
      !clean_(user.Department) ||
      clean_(user.Department) === department;

    return (
      clean_(user.Role) === role &&
      isTrue_(user.Active) &&
      departmentMatches &&
      isValidEmail_(user.Email)
    );
  });

  return match ? match.Email : '';
}

function findBackupApprover_(email) {
  const users = getObjects_(CONFIG.SHEETS.USERS);
  const user = users.find(function(candidate) {
    return clean_(candidate.Email).toLowerCase() ===
      clean_(email).toLowerCase();
  });

  if (!user) {
    return '';
  }

  if (!isTrue_(user.Active)) {
    return isValidEmail_(user['Backup Approver Email'])
      ? user['Backup Approver Email']
      : '';
  }

  return '';
}

function buildRecurrenceKey_(partProcess, issueType, area) {
  return [
    normalizeKeyPart_(partProcess),
    normalizeKeyPart_(issueType),
    normalizeKeyPart_(area)
  ].join('|');
}

function findRecentSimilarIssues_(recurrenceKey, referenceDate, days) {
  const threshold =
    startOfDay_(referenceDate).getTime() - days * 86400000;

  return getObjects_(CONFIG.SHEETS.ISSUES).filter(function(issue) {
    const created = asDate_(issue['Created At']);

    return (
      issue['Recurrence Key'] === recurrenceKey &&
      created &&
      created.getTime() >= threshold
    );
  });
}

function findPotentialDuplicate_(
  partProcess,
  lotWorkOrder,
  issueType,
  referenceDate
) {
  if (!lotWorkOrder) {
    return '';
  }

  const threshold =
    startOfDay_(referenceDate).getTime() - 30 * 86400000;

  const match = getObjects_(CONFIG.SHEETS.ISSUES).find(
    function(issue) {
      const created = asDate_(issue['Created At']);

      return (
        normalizeKeyPart_(issue['Part or Process']) ===
          normalizeKeyPart_(partProcess) &&
        normalizeKeyPart_(issue['Lot or Work Order']) ===
          normalizeKeyPart_(lotWorkOrder) &&
        normalizeKeyPart_(issue['Issue Type']) ===
          normalizeKeyPart_(issueType) &&
        created &&
        created.getTime() >= threshold &&
        issue.Status !== 'Closed'
      );
    }
  );

  return match ? match['Issue ID'] : '';
}

function getOrCreateIssueFolder_(issueId) {
  const root = DriveApp.getFolderById(CONFIG.ROOT_FOLDER_ID);
  const issuesFolder = getOrCreateChildFolder_(root, 'Issues');
  return getOrCreateChildFolder_(issuesFolder, issueId);
}

function getOrCreateChildFolder_(parent, name) {
  const folders = parent.getFoldersByName(name);
  return folders.hasNext() ? folders.next() : parent.createFolder(name);
}

function copyFormUploads_(rawValue, destinationFolder) {
  const text = clean_(rawValue);

  if (!text) {
    return [];
  }

  const ids = text.match(/[-\w]{25,}/g) || [];
  const copiedUrls = [];

  ids.forEach(function(fileId) {
    const sourceFile = DriveApp.getFileById(fileId);
    const copiedFile = sourceFile.makeCopy(
      sourceFile.getName(),
      destinationFolder
    );
    copiedUrls.push(copiedFile.getUrl());
  });

  return copiedUrls;
}

function flagInvalidCompletedActions_(actions) {
  actions.forEach(function(action) {
    if (
      action.Status === 'Complete' &&
      (
        !clean_(action['Evidence File']) ||
        !asDate_(action['Completed At'])
      )
    ) {
      updateObjectById_(
        CONFIG.SHEETS.ACTIONS,
        'Action ID',
        action['Action ID'],
        {
          'Error Message':
            'Completed actions require evidence and a completion timestamp.'
        }
      );
    }
  });
}

function actionsOfType_(actions, type) {
  return actions.filter(function(action) {
    return action['Action Type'] === type &&
      action.Status !== 'Cancelled';
  });
}

function allActionsComplete_(actions) {
  return (
    actions.length > 0 &&
    actions.every(function(action) {
      return (
        action.Status === 'Complete' &&
        clean_(action['Evidence File']) &&
        asDate_(action['Completed At'])
      );
    })
  );
}

function runAiReviewForSelectedIssue() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const row = sheet.getActiveRange().getRow();

  if (
    sheet.getName() !== CONFIG.SHEETS.ISSUES ||
    row < 2
  ) {
    throw new Error('Select a data row in the Issues sheet.');
  }

  const issue = objectFromRow_(sheet, row);
  runAiReviewForIssue(issue['Issue ID']);
}

function processAiQueue_(maximumRecords) {
  const pending = getObjects_(CONFIG.SHEETS.ISSUES)
    .filter(function(issue) {
      return issue['AI Review Status'] === 'Pending';
    })
    .slice(0, maximumRecords);

  pending.forEach(function(issue) {
    try {
      runAiReviewForIssue(issue['Issue ID']);
    } catch (error) {
      Logger.log(
        'AI review failed for ' +
          issue['Issue ID'] +
          ': ' +
          error.message
      );
    }
  });
}

function runAiReviewForIssue(issueId) {
  if (!CONFIG.AI_ENABLED) {
    throw new Error('AI_ENABLED is false.');
  }

  const apiKey = PropertiesService
    .getScriptProperties()
    .getProperty('OPENAI_API_KEY');

  if (!apiKey) {
    throw new Error(
      'OPENAI_API_KEY is not set in Script Properties.'
    );
  }

  if (
    !CONFIG.AI_MODEL ||
    CONFIG.AI_MODEL === 'YOUR_AI_MODEL'
  ) {
    throw new Error('Replace YOUR_AI_MODEL with an approved model.');
  }

  const issue = findObjectByField_(
    CONFIG.SHEETS.ISSUES,
    'Issue ID',
    issueId
  );

  if (!issue) {
    throw new Error('Issue not found: ' + issueId);
  }

  const aiInput = {
    issue_type: redactBasic_(issue['Issue Type']),
    area: redactBasic_(issue.Area),
    part_or_process: redactBasic_(issue['Part or Process']),
    lot_or_work_order:
      redactBasic_(issue['Lot or Work Order']),
    quantity_affected: Number(issue['Quantity Affected'] || 0),
    severity: redactBasic_(issue.Severity),
    risk_flag: redactBasic_(issue['Risk Flag']),
    description: redactBasic_(issue.Description),
    containment_summary:
      redactBasic_(issue['Containment Summary'])
  };

  const schema = {
    type: 'object',
    properties: {
      category: {
        type: 'string',
        enum: [
          'Product Defect',
          'Process Deviation',
          'Supplier Issue',
          'Documentation',
          'Equipment',
          'Measurement',
          'Packaging',
          'Safety/Compliance',
          'Other'
        ]
      },
      normalized_summary: {type: 'string'},
      missing_information: {
        type: 'array',
        items: {type: 'string'}
      },
      recurrence_terms: {
        type: 'array',
        items: {type: 'string'}
      },
      confidence: {
        type: 'number',
        minimum: 0,
        maximum: 1
      },
      review_reason: {type: 'string'}
    },
    required: [
      'category',
      'normalized_summary',
      'missing_information',
      'recurrence_terms',
      'confidence',
      'review_reason'
    ],
    additionalProperties: false
  };

  const payload = {
    model: CONFIG.AI_MODEL,
    store: false,
    input: [
      {
        role: 'system',
        content: aiSystemInstruction_()
      },
      {
        role: 'user',
        content: JSON.stringify(aiInput)
      }
    ],
    text: {
      format: {
        type: 'json_schema',
        name: 'nonconformance_triage',
        strict: true,
        schema: schema
      }
    }
  };

  try {
    const responseData = fetchJsonWithRetry_(
      'https://api.openai.com/v1/responses',
      {
        method: 'post',
        contentType: 'application/json',
        headers: {
          Authorization: 'Bearer ' + apiKey
        },
        payload: JSON.stringify(payload),
        muteHttpExceptions: true
      },
      3
    );

    const outputText = extractResponseText_(responseData);
    const result = JSON.parse(outputText);

    if (
      typeof result.confidence !== 'number' ||
      result.confidence < 0 ||
      result.confidence > 1
    ) {
      throw new Error('AI confidence value is invalid.');
    }

    updateObjectById_(
      CONFIG.SHEETS.ISSUES,
      'Issue ID',
      issueId,
      {
        'AI Category Suggestion': result.category,
        'AI Summary': result.normalized_summary,
        'AI Missing Information':
          result.missing_information.join(' | '),
        'AI Recurrence Terms':
          result.recurrence_terms.join(' | '),
        'AI Confidence': result.confidence,
        'AI Review Status':
          result.confidence < 0.75
            ? 'Human Review Required'
            : 'Suggested',
        'Last Updated': new Date()
      }
    );

    appendAudit_(
      issueId,
      '',
      'AI_REVIEW_COMPLETED',
      'Pending',
      result.confidence < 0.75
        ? 'Human Review Required'
        : 'Suggested',
      'Apps Script',
      'AI API',
      'AI output stored as a suggestion only.'
    );
  } catch (error) {
    updateObjectById_(
      CONFIG.SHEETS.ISSUES,
      'Issue ID',
      issueId,
      {
        'AI Review Status': 'Failed',
        'Last Updated': new Date()
      }
    );

    appendAudit_(
      issueId,
      '',
      'AI_REVIEW_FAILED',
      'Pending',
      'Failed',
      'Apps Script',
      'AI API',
      error.message
    );

    throw error;
  }
}

function aiSystemInstruction_() {
  return [
    'You assist a manufacturing quality team with intake review.',
    'Classify and summarize only the supplied nonconformance facts.',
    'Do not determine disposition, root cause, corrective action,',
    'regulatory status, employee fault, product release, or closure.',
    'Do not invent measurements, specifications, causes, or evidence.',
    'List missing information as questions for a human reviewer.',
    'Return only JSON that matches the supplied schema.'
  ].join(' ');
}

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

  for (let attempt = 1; attempt <= maximumAttempts; attempt++) {
    try {
      const response = UrlFetchApp.fetch(url, options);
      const status = response.getResponseCode();
      const body = response.getContentText();

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

      if (status === 429 || status >= 500) {
        throw new Error(
          'Retryable API response ' + status + ': ' + body
        );
      }

      throw new Error(
        'Non-retryable API response ' + status + ': ' + body
      );
    } catch (error) {
      lastError = error;

      if (attempt < maximumAttempts) {
        Utilities.sleep(Math.pow(2, attempt - 1) * 1000);
      }
    }
  }

  throw lastError;
}

function extractResponseText_(data) {
  if (data.output_text) {
    return data.output_text;
  }

  const output = data.output || [];

  for (let i = 0; i < output.length; i++) {
    const content = output[i].content || [];

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

  throw new Error('The AI response did not contain output text.');
}

function sendEmailWithRetry_(recipients, subject, body) {
  const validRecipients = uniqueValidEmails_(recipients);

  if (validRecipients.length === 0) {
    throw new Error('No valid email recipients were supplied.');
  }

  let lastError;

  for (let attempt = 1; attempt <= 3; attempt++) {
    try {
      GmailApp.sendEmail(
        validRecipients.join(','),
        subject,
        body,
        {name: 'Quality Workflow'}
      );
      return;
    } catch (error) {
      lastError = error;

      if (attempt < 3) {
        Utilities.sleep(Math.pow(2, attempt - 1) * 1000);
      }
    }
  }

  throw lastError;
}

function appendAudit_(
  issueId,
  actionId,
  eventType,
  oldValue,
  newValue,
  actor,
  source,
  details
) {
  appendObject_(CONFIG.SHEETS.AUDIT, {
    'Event ID': Utilities.getUuid(),
    'Timestamp': new Date(),
    'Issue ID': issueId,
    'Action ID': actionId,
    'Event Type': eventType,
    'Old Value': oldValue,
    'New Value': newValue,
    'Actor': actor,
    'Source': source,
    'Details': details
  });
}

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

  if (!sheet) {
    sheet = ss.insertSheet(sheetName);
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
    sheet.setFrozenRows(1);
    return sheet;
  }

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

  const current = sheet
    .getRange(1, 1, 1, headers.length)
    .getValues()[0];

  if (JSON.stringify(current) !== JSON.stringify(headers)) {
    throw new Error(
      'Header mismatch in sheet ' +
        sheetName +
        '. Use a clean sheet or migrate headers before setup.'
    );
  }

  return sheet;
}

function applyDataValidation_() {
  setListValidation_(
    CONFIG.SHEETS.ISSUES,
    'Severity',
    ['Minor', 'Major', 'Critical']
  );
  setListValidation_(
    CONFIG.SHEETS.ISSUES,
    'Risk Flag',
    ['Yes', 'No']
  );
  setListValidation_(
    CONFIG.SHEETS.ISSUES,
    'Status',
    [
      'Submitted',
      'Manual Review',
      'Containment Required',
      'Root Cause Analysis',
      'Root Cause Review',
      'Corrective Action In Progress',
      'Effectiveness Review',
      'Closure Review',
      'Closed',
      'On Hold',
      'Rejected or Duplicate'
    ]
  );
  setListValidation_(
    CONFIG.SHEETS.ACTIONS,
    'Status',
    [
      'Not Started',
      'In Progress',
      'Blocked',
      'Awaiting Verification',
      'Complete',
      'Cancelled'
    ]
  );
  setListValidation_(
    CONFIG.SHEETS.COSTS,
    'Cost Type',
    [
      'Internal Failure',
      'External Failure',
      'Appraisal',
      'Prevention'
    ]
  );
}

function setListValidation_(sheetName, fieldName, values) {
  const sheet = getSpreadsheet_().getSheetByName(sheetName);
  const headers = getHeaders_(sheet);
  const column = headers.indexOf(fieldName) + 1;

  if (column === 0) {
    throw new Error(
      'Field not found for validation: ' + fieldName
    );
  }

  const rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(values, true)
    .setAllowInvalid(false)
    .build();

  sheet.getRange(2, column, 1999, 1).setDataValidation(rule);
}

function nextId_(prefix, sheetName, idField, padding) {
  const year = Utilities.formatDate(
    new Date(),
    CONFIG.TIME_ZONE,
    'yyyy'
  );
  const propertyKey = prefix + '_COUNTER_' + year;
  const properties = PropertiesService.getScriptProperties();
  let counter = Number(properties.getProperty(propertyKey) || 0);
  let candidate;

  do {
    counter++;
    candidate =
      prefix +
      '-' +
      year +
      '-' +
      String(counter).padStart(padding, '0');
  } while (
    findObjectByField_(sheetName, idField, candidate)
  );

  properties.setProperty(propertyKey, String(counter));
  return candidate;
}

function getSpreadsheet_() {
  return SpreadsheetApp.openById(CONFIG.SPREADSHEET_ID);
}

function getObjects_(sheetName) {
  const sheet = getSpreadsheet_().getSheetByName(sheetName);

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

  const values = sheet
    .getRange(
      1,
      1,
      sheet.getLastRow(),
      sheet.getLastColumn()
    )
    .getValues();
  const headers = values.shift();

  return values
    .filter(function(row) {
      return row.some(function(value) {
        return value !== '';
      });
    })
    .map(function(row, index) {
      const object = {_rowNumber: index + 2};

      headers.forEach(function(header, column) {
        object[header] = row[column];
      });

      return object;
    });
}

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

  sheet.appendRow(row);
}

function updateObjectById_(
  sheetName,
  idField,
  idValue,
  updates
) {
  const sheet = getSpreadsheet_().getSheetByName(sheetName);
  const headers = getHeaders_(sheet);
  const idColumn = headers.indexOf(idField) + 1;

  if (idColumn === 0) {
    throw new Error('ID field not found: ' + idField);
  }

  const lastRow = sheet.getLastRow();

  if (lastRow < 2) {
    throw new Error('No records exist in ' + sheetName);
  }

  const ids = sheet
    .getRange(2, idColumn, lastRow - 1, 1)
    .getValues()
    .flat();
  const index = ids.findIndex(function(value) {
    return String(value) === String(idValue);
  });

  if (index === -1) {
    throw new Error(
      'Record not found in ' +
        sheetName +
        ': ' +
        idValue
    );
  }

  const rowNumber = index + 2;

  Object.keys(updates).forEach(function(field) {
    const column = headers.indexOf(field) + 1;

    if (column === 0) {
      throw new Error(
        'Update field not found in ' +
          sheetName +
          ': ' +
          field
      );
    }

    sheet.getRange(rowNumber, column).setValue(updates[field]);
  });
}

function findObjectByField_(sheetName, fieldName, value) {
  return getObjects_(sheetName).find(function(object) {
    return String(object[fieldName]) === String(value);
  }) || null;
}

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

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

  return object;
}

function getHeaders_(sheet) {
  return sheet
    .getRange(1, 1, 1, sheet.getLastColumn())
    .getValues()[0];
}

function firstValue_(namedValues, fieldName) {
  const value = namedValues[fieldName];

  if (Array.isArray(value)) {
    return value.join(', ');
  }

  return value || '';
}

function addBusinessDays_(startDate, numberOfDays) {
  const result = new Date(startDate);
  let added = 0;

  if (numberOfDays === 0) {
    while (
      result.getDay() === 0 ||
      result.getDay() === 6
    ) {
      result.setDate(result.getDate() + 1);
    }
    return result;
  }

  while (added < numberOfDays) {
    result.setDate(result.getDate() + 1);

    if (
      result.getDay() !== 0 &&
      result.getDay() !== 6
    ) {
      added++;
    }
  }

  return result;
}

function startOfDay_(date) {
  const value = new Date(date);
  value.setHours(0, 0, 0, 0);
  return value;
}

function daysBetween_(firstDate, secondDate) {
  return Math.floor(
    (startOfDay_(secondDate).getTime() -
      startOfDay_(firstDate).getTime()) /
      86400000
  );
}

function formatDate_(date) {
  return date
    ? Utilities.formatDate(
        date,
        CONFIG.TIME_ZONE,
        'yyyy-MM-dd'
      )
    : '';
}

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

  const date = value instanceof Date
    ? new Date(value)
    : new Date(value);

  return isNaN(date.getTime()) ? null : date;
}

function clean_(value) {
  return value === null || value === undefined
    ? ''
    : String(value).trim();
}

function numberOrBlank_(value) {
  const cleaned = clean_(value);

  if (!cleaned) {
    return '';
  }

  const number = Number(cleaned);
  return isNaN(number) ? '' : number;
}

function numberOrZero_(value) {
  const number = Number(value);
  return isNaN(number) ? 0 : number;
}

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

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

function isValidEmail_(value) {
  return /^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(clean_(value));
}

function uniqueValidEmails_(emails) {
  const seen = {};

  return emails
    .filter(isValidEmail_)
    .filter(function(email) {
      const key = clean_(email).toLowerCase();

      if (seen[key]) {
        return false;
      }

      seen[key] = true;
      return true;
    });
}

function isTrue_(value) {
  return value === true ||
    clean_(value).toLowerCase() === 'true' ||
    clean_(value).toLowerCase() === 'yes';
}

function redactBasic_(value) {
  return clean_(value)
    .replace(
      /[^\s@]+@[^\s@]+\.[^\s@]+/g,
      '[EMAIL REDACTED]'
    )
    .replace(
      /(?:\+?\d[\d\s().-]{7,}\d)/g,
      '[PHONE REDACTED]'
    )
    .substring(0, 8000);
}

function assertConfiguration_() {
  const placeholders = [
    CONFIG.SPREADSHEET_ID,
    CONFIG.ROOT_FOLDER_ID,
    CONFIG.QUALITY_MANAGER_EMAIL,
    CONFIG.OPERATIONS_MANAGER_EMAIL
  ];

  if (
    placeholders.some(function(value) {
      return !value || value.indexOf('YOUR_') === 0;
    })
  ) {
    throw new Error(
      'Replace all required CONFIG placeholders before running the script.'
    );
  }
}

Authorization and Deployment

1. Replace YOUR_SPREADSHEET_ID, YOUR_FOLDER_ID, and the email placeholders.
2. Set TIME_ZONE to the organization’s approved Apps Script time zone.
3. Save the project and run setupSystem manually.
4. Review the requested permissions for Sheets, Drive, Gmail, external requests if AI is enabled, and trigger management.
5. Populate the Users table.
6. Run installTriggers manually.
7. Submit a test form response and confirm that an issue, two actions, a Drive folder, and emails are created.
8. Open Apps Script Executions to inspect trigger status, duration, logs, and error messages.
9. No web application deployment is required. The script runs from installable spreadsheet and time-based triggers.

If optional AI is enabled, add a Script Property named OPENAI_API_KEY, replace YOUR_AI_MODEL with an approved model identifier, and change AI_ENABLED to true. Test with synthetic quality descriptions before sending business data.

Likely implementation errors include mismatched form question names, incorrect folder IDs, missing Drive access, invalid user email values, duplicate or altered sheet headers, unavailable Gmail permissions, and an AppSheet file path that does not resolve to the expected Drive location.

Failure Handling and Operational Reliability

The Automation Failures slice acts as a practical dead-letter queue. It contains records that could not complete normal post-processing and retains the business record, source key, retry count, and error message.

Idempotency is based on the source submission key, permanent issue IDs, and checks for existing default action types. Reconciliation updates individual cells rather than rewriting entire rows, which reduces the risk of overwriting a concurrent AppSheet edit.

Staff identify failures through the AppSheet queue, Apps Script Executions, trigger-owner failure notices, action Error Message fields, and the AuditLog. A daily review compares recent form responses to created issue records and confirms that no response is stranded.

A Complete Example

An in-process inspector at Alder Peak Components finds a hole diameter outside tolerance on a batch of machined housings. The inspector submits the internal form with these values:

• Department: Machining
• Area or Work Center: Mill Cell 2
• Issue Type: In-Process
• Part or Process: Housing H-417
• Lot or Work Order: WO-84216
• Quantity Affected: 12
• Severity: Major
• Immediate Safety or Compliance Risk: No
• Description: Twelve units measured above the drawing limit at final in-process inspection.
• Containment Taken: Work order stopped and all remaining units moved to the hold area.
• Evidence: Inspection photograph and measurement sheet

The form writes the response to its linked sheet. The installable trigger receives the row and creates source key 184920331:27. No issue has that source key, so processing continues.

The script assigns NCR-2026-0038, validates the quantity and allowed categories, selects the assigned quality engineer, and assigns the machining supervisor as the containment and root-cause action owner.

The recurrence key becomes housing h 417|in process|mill cell 2. Two matching records exist in the prior 90 days, so Similar Issues 90d becomes 3 and Recurring Flag becomes true. The system does not conclude that the same root cause applies.

A Drive folder named NCR-2026-0038 is created. The form files are copied into it. The script creates a containment action due in one business day and a root-cause action due in five business days.

The machining supervisor receives the containment notice. In AppSheet, the supervisor attaches a controlled hold tag list and inspection evidence, then completes the containment action. The scheduled reconciliation advances the issue to Root Cause Analysis.

The team uses a five-why analysis and documents a worn locating pin that allowed fixture movement. A corrective action is created to replace the pin, add a fixture inspection frequency, and update the setup checklist. Quality reviews the evidence and approves the root cause.

Related cost records are entered:

After all corrective actions are completed with evidence, the script creates an effectiveness check due in 14 business days. A quality technician reviews two subsequent work orders, records no recurrence, attaches the verification report, and selects Effective.

The issue moves to Closure Review. The quality manager reviews the full record, cost entries, approvals, and evidence before approving closure. The final record retains its source key, action IDs, folder URL, approval identity, timestamps, cost total, recurrence flag, and audit events.

Implementation Cost

All amounts below are representative planning assumptions, not vendor quotations or verified client costs. Actual licensing, labor rates, security requirements, migration effort, and consulting scope should be confirmed before implementation.

The example treats existing Google Workspace licensing as an existing business expense, not as costless software. It also includes maintenance labor even though the underlying Apps Script hosting does not require separate server administration.

Estimated Time and Cost Savings

The estimate counts administrative coordination time, not the technical effort required to investigate defects, contain product, perform rework, or implement corrective actions. Those quality activities remain necessary.

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

Actual calculation: 35 × 85 ÷ 60 = 49.58 hours

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

Actual calculation: 35 × 22 ÷ 60 + 35 × 15% × 20 ÷ 60 + 4 = 18.58 hours

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

Actual calculation: 49.58 – 18.58 = 31.00 hours

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

Actual calculation: 31.00 × $45 = $1,395

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

Actual calculation: $1,395 – $210 = $1,185

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

Actual calculation: $12,100 ÷ $1,185 = approximately 10.2 months

Recovered time does not automatically reduce payroll. It may provide additional quality-team capacity, quicker response, less overtime, fewer administrative follow-ups, greater transaction capacity, or reduced dependency on a single employee.

Cost-of-Quality Analysis

The workflow also makes failure and prevention costs more visible. For planning purposes, Alder Peak Components could model a month with 24 costed internal failures averaging $310, three external failures averaging $900, $1,575 of issue-specific appraisal work, and $2,200 of prevention work.

The automation does not eliminate these costs. It provides a consistent method to record and analyze them. Any avoided-defect benefit should be measured from verified recurrence reduction and should not be counted until a reasonable baseline and comparison period exist.

Non-financial benefits include clearer ownership, fewer follow-ups, faster containment visibility, more complete records, better auditability, consistent processing, stronger recurring-issue reporting, and a more predictable experience for reporters and action owners.

Readers should replace the volume, handling times, exception rate, labor cost, licensing allowance, maintenance effort, implementation cost, and cost-of-quality assumptions with their own measurements.

Adding AI to the Automation

AI is optional and should be added only after the deterministic intake, action, escalation, evidence, approval, and reporting workflow is reliable.

Useful AI applications include summarizing unstructured descriptions, suggesting a defect category, identifying potentially missing context, extracting recurrence terms, and comparing narratives that do not share exact wording.

AI is not needed for required fields, due-date calculations, severity thresholds, email validation, exact duplicate-event detection, owner lookup, cost formulas, permissions, status transitions, or closure rules. Those controls are more reliable as forms, lookups, formulas, and deterministic workflow logic.

The core automation provides ownership, due dates, reminders, evidence links, status controls, and reports. AI adds support for reading and organizing unstructured descriptions. It does not replace the quality investigation.

The Recommended AI Enhancement

The recommended enhancement is structured intake assistance. After the core issue record is created, an approved AI model receives a limited, redacted subset of the issue description and returns a category suggestion, normalized summary, missing-information questions, recurrence terms, and confidence score.

• Trigger: AI Review Status equals Pending and the scheduled queue processor runs.
• AI input: Issue type, area, part or process, lot or work order, quantity, severity, risk flag, description, and containment summary.
• Excluded input: Reporter email, customer personal data, employee evaluations, legal advice, controlled technical data not approved for the service, and unrelated attachments.
• Expected output: Strict JSON matching the approved schema.
• Validation: Schema validation plus a confidence value between zero and one.
• Record update: Suggestions are written to dedicated AI fields, not authoritative quality fields.
• Human review: A quality user accepts, changes, or rejects the suggestion.
• Low confidence: Scores below 0.75 enter Human Review Required.
• Failure behavior: AI Review Status becomes Failed and normal processing continues.
• Logging: The AuditLog records completion or failure without storing API credentials.
• Cost monitoring: The quality system owner reviews request count, model choice, and invoice data monthly.

The reusable system instruction is:

You assist a manufacturing quality team with intake review.

Classify and summarize only the supplied nonconformance facts.

Do not determine disposition, root cause, corrective action, regulatory
status, employee fault, product release, or closure.

Do not invent measurements, specifications, causes, or evidence.

List missing information as questions for a human reviewer.

Return only JSON that matches the supplied schema.

The reusable user prompt template is:

Review the following nonconformance intake record.

Issue type: {{issue_type}}
Area: {{area}}
Part or process: {{part_or_process}}
Lot or work order: {{lot_or_work_order}}
Quantity affected: {{quantity_affected}}
Severity entered by reporter: {{severity}}
Safety or compliance risk entered by reporter: {{risk_flag}}
Description: {{description}}
Containment summary: {{containment_summary}}

Return:
1. One allowed category
2. A factual normalized summary
3. Missing-information questions
4. Recurrence search terms
5. Confidence from 0 to 1
6. A short reason human review may be needed

The required structured output is:

{
  "category": "Process Deviation",
  "normalized_summary": "Twelve H-417 housings were measured above the specified hole-diameter limit during in-process inspection at Mill Cell 2.",
  "missing_information": [
    "What was the measured range?",
    "What drawing revision and inspection method were used?"
  ],
  "recurrence_terms": [
    "H-417",
    "hole diameter",
    "Mill Cell 2",
    "fixture movement"
  ],
  "confidence": 0.84,
  "review_reason": "The description does not include the measured values or drawing revision."
}

The complete API call, authentication, retry behavior, JSON schema, response extraction, and record update are included in the supplied Apps Script. The API uses bearer-token authentication, a POST request, JSON content, and exponential retry for rate-limit and server errors. One response is requested per issue, so pagination is not applicable.

Benefits of the AI Enhancement

• Less time spent rewriting long issue descriptions into a consistent summary.
• More consistent first-pass categorization of unstructured reports.
• Quicker identification of missing measurements, specifications, lot references, or containment details.
• Better recurrence searches when reporters use different wording for similar defects.
• Improved preparation for human triage and management reporting.

These benefits are specific to unstructured text processing. Assignment, deadlines, reminders, escalation, evidence storage, approvals, cost totals, and closure control come from the rule-based automation and remain available if the AI service is disabled.

What Remains Rule-Based or Human-Controlled

Estimating the Additional Value of AI

Assume the core automation requires 22 administrative minutes per issue. Of that time, approximately six minutes is spent reading, rewriting, and applying first-pass categories to unstructured descriptions.

Gross minutes addressed: 35 × 6 = 210 minutes

Human review time: 35 × 1.5 = 52.5 minutes

Correction time: 35 × 12% × 3 = 12.6 minutes

Failure fallback time: 35 × 5% × 6 = 10.5 minutes

Net additional time recovered: 210 – 52.5 – 12.6 – 10.5 = 134.4 minutes, or approximately 2.24 hours per month

Estimated labor value: 2.24 × $45 = approximately $101 per month

Estimated net value after AI usage allowance: $101 – $15 = approximately $86 per month

The result is modest at 35 monthly records. The stronger justification may be consistency and search quality rather than labor savings. AI does not eliminate correction, failed requests, or human review.

Testing Checklist

Use synthetic sample data and non-production files before processing real quality information.

Ongoing Maintenance

Credentials should be rotated according to company policy. Former users should be removed promptly from Google Groups, AppSheet access, Drive sharing, and the Users table. Script and app changes should be tested in the development environment and documented before production deployment.

The system owner should also monitor Apps Script quotas, trigger failures, Gmail sending behavior, AppSheet synchronization, Drive storage, API changes, and AI usage. Exact quotas and product behavior can vary by account and subscription, so current platform information should be reviewed periodically.

When to Move to Dedicated Software

The Google Workspace implementation can remain appropriate while transaction volume, permissions, integrations, and compliance requirements remain manageable. It should not be replaced solely because it is based on configurable business tools.

Reassessment is appropriate when:

• Issue and action volume causes spreadsheet or synchronization performance problems.
• Multiple plants require local controls, cross-site routing, or consolidated enterprise reporting.
• The business requires formal electronic signatures or regulated audit controls beyond the configured workflow.
• Supplier and customer portals are required.
• Quality processes expand into document control, calibration, training, audits, risk management, or supplier qualification.
• Permissions become too complex for practical maintenance.
• Exception rates or manual recovery effort continue to increase.
• Integration is required with enterprise resource planning, manufacturing execution, laboratory, metrology, or customer systems.
• Offline mobile operation becomes necessary.
• Formal vendor support and service-level commitments are required.
• Advanced workflow versioning, immutable audit records, or validated change control becomes mandatory.
• Maintenance effort approaches the cost of a supported quality management platform.

Relevant categories include quality management systems, manufacturing execution systems, product lifecycle management platforms, and custom applications backed by a managed relational database. Migration should be based on documented requirements and total operating cost rather than transaction volume alone.

Implementation Checklist

• Confirm nonconformance, corrective-action, effectiveness, and closure requirements.
• Define severity, ownership, approval, reminder, and escalation rules.
• Confirm Google Workspace and AppSheet feature availability.
• Create development and production accounts, folders, forms, and spreadsheets.
• Configure least-privilege permissions and user roles.
• Create Issues, Actions, Costs, Users, and AuditLog structures.
• Build the Google Form with exact field names and validation.
• Configure AppSheet relationships, security filters, slices, forms, and controlled actions.
• Map every intake field to the system of record.
• Install the complete Apps Script and replace configuration placeholders.
• Authorize Drive, Gmail, Sheets, trigger, and optional API permissions.
• Install the form-submit and hourly triggers.
• Configure ownership assignment and backup approvers.
• Configure root-cause and closure approvals.
• Test reminders, escalations, rejection, reassignment, and ineffective checks.
• Verify Drive folder creation, AppSheet evidence paths, access, and retention.
• Create operational dashboards and cost-of-quality reports.
• Protect system-managed fields and source-sheet access.
• Test duplicate events, partial failures, authentication errors, and manual recovery.
• Run user acceptance testing with sample data.
• Document deployment, rollback, support, and escalation procedures.
• Validate implementation and recurring-cost assumptions.
• Replace savings assumptions with measured process times and labor rates.
• Enable AI only after core workflow testing is complete.
• Define AI data restrictions, confidence thresholds, review, logging, and fallback.
• Name a primary maintenance owner and backup owner.
• Define the volume, compliance, integration, security, and maintenance criteria that would trigger a platform reassessment.