Mobile Quality Inspections with AppSheet and Google Forms

The Business Situation

Cedar Vale Components is a fictional 48-person manufacturer of small fabricated assemblies. Its quality department consists of a quality manager, two inspectors, and a quality coordinator. Six production cell leads participate in containment, rework, and corrective-action work.

The company completes approximately 220 incoming, in-process, and final inspections each month. Inspectors check work orders, item codes, lot or serial numbers, visual conditions, dimensional measurements, tolerances, quantities, and product markings. Failed inspections may require photographs, disposition approval, containment, rework, and final quality sign-off.

The existing process used paper inspection sheets, photographs stored in individual Google Drive folders, and a Google Sheets register maintained by the quality coordinator. Production employees received defect notifications through email or verbal instructions.

This arrangement worked at a lower volume, but it no longer provided reliable status visibility. The quality coordinator spent increasing time transcribing inspection results, matching photographs to jobs, chasing corrective actions, and preparing monthly summaries.

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

The Existing Process

The original workflow followed a consistent but mostly manual sequence:

1. An inspector collected a printed inspection sheet from the quality office.
2. The inspector copied the work order, item, lot, and quantity from the production traveler.
3. Measurements were written beside printed tolerances. Pass or fail decisions were calculated manually.
4. Photographs were taken on a phone and later uploaded to a personal or departmental Drive folder.
5. If a defect was found, the inspector wrote a description and notified a production lead by email or in person.
6. The quality coordinator entered the inspection summary into a spreadsheet.
7. Corrective actions and disposition decisions were tracked through email.
8. Completed paper records were scanned or filed in a cabinet.
9. The quality coordinator manually reconciled paper records, spreadsheet entries, photographs, and email approvals before producing reports.

Measurement tolerances were especially vulnerable to inconsistency. Some inspectors used the current item specification while others relied on a printed sheet that might not reflect the latest approved limits. A measurement could also be within tolerance but marked as failed, or outside tolerance but marked as passed.

The business needed to preserve human inspection judgment while making data capture, tolerance evaluation, defect creation, action assignment, reminders, and reporting more consistent.

What the New System Needed to Do

The system also needed to work with the company’s existing Google Workspace environment. The manufacturer wanted to avoid a large platform replacement until its inspection templates, ownership rules, and approval workflow were better standardized.

Implementation Approaches Considered

Google Forms and Sheets alone could handle the routine inspection form, but they would make child measurements and action ownership cumbersome. Airtable offered a credible alternative, but the manufacturer did not already use it as a governed business platform.

A Microsoft 365 design would be technically viable for an organization using SharePoint, Power Apps, and Power Automate. It was not selected because Cedar Vale’s identities, files, and operating data were already in Google Workspace.

A dedicated quality platform offered the most formal long-term feature set. It was not selected initially because the company first needed to standardize its inspection data, templates, disposition rules, and role assignments.

The Selected Solution

The selected implementation used AppSheet as the primary mobile inspection application. Google Forms provided a simpler controlled intake path for fixed inspections and an operational fallback when a user did not need the complete AppSheet interface.

Both intake paths wrote into a Google Sheets workbook. Google Apps Script normalized Google Form submissions, evaluated pending inspections, created defects and corrective actions, generated PDF reports in Google Drive, sent notifications, and maintained audit and error logs.

Existing Google Workspace identities, Drive storage, and Sheets knowledge were retained. Manual transcription, tolerance calculations, defect-register updates, initial action assignment, routine reminders, and report assembly were removed.

Inspectors still performed the physical inspection. Quality and operations managers retained control over disposition, approval, exceptions, and final sign-off. The automation did not release products, approve scrap, or accept out-of-tolerance work without authorized human action.

System Architecture and Data Flow

• Intake: AppSheet for complete mobile inspections and Google Forms for fixed inspection submissions.
• System of record: A structured Google Sheets workbook with related tables.
• Automation layer: A spreadsheet-bound Google Apps Script project using installed form and time-based triggers.
• Document storage: Restricted Google Drive folders containing photographs and generated PDF reports.
• Notifications: Email sent by the automation owner through Apps Script.
• Reporting: AppSheet dashboard views and Google Sheets pivots.
• AI layer: Optional defect categorization and missing-information suggestions after core processing succeeds.

1. Master data is loaded. Quality staff maintain active item codes, specification limits, units, quality owners, users, and backup approvers in Sheets.
2. An inspector starts an inspection. AppSheet creates a client-generated inspection key, or Google Forms records a response in its linked response sheet.
3. Input is validated. Required fields, dropdown values, email addresses, quantities, and numeric formats are checked before or immediately after submission.
4. Form data is normalized. The installed form-submit trigger converts one wide Form response into an inspection plus related measurement, checklist, and photo rows.
5. AppSheet data synchronizes. AppSheet writes inspections and child records directly to their Sheets tabs. Submitted inspections receive an automation status of Pending.
6. The processing trigger runs. A time-based Apps Script trigger scans for pending inspections. A script lock prevents concurrent runs from processing the same record.
7. Measurements are evaluated. Numeric values are compared with lower and upper limits. The result is written as Pass or Fail.
8. Checklist results are evaluated. Any failed check contributes to the overall failure result.
9. A defect is created when required. The script creates one initial defect record and stores its returned defect ID.
10. Corrective work is assigned. The item’s quality owner or production owner receives a corrective-action record and due date.
11. Approvals are created. Routine failures receive quality disposition approval. Higher-risk quantities or dispositions receive sequential operations and quality approval.
12. Documents are assembled. A restricted Drive folder is created. Available photographs are copied or linked, and a PDF inspection report is generated.
13. Notifications are sent. Owners receive links to the relevant AppSheet queue and inspection record.
14. Human work continues. Production records containment or rework evidence. Authorized approvers accept, reject, or return the disposition.
15. Final sign-off closes the record. The quality approver signs off in AppSheet. A final report is created and closure is logged.
16. Failures follow a separate path. Transient failures are retried. Invalid or repeatedly failing records move to Manual Review and appear in the automation-failure view.

Data Structure

The Google Sheets workbook uses one tab per entity. The first row contains stable field names. Tabs are not renamed after AppSheet and Apps Script are connected.

Measurements and checklist results are stored as child rows rather than fixed columns. This allows AppSheet inspections to contain as many characteristics as the inspection template requires. The Google Form uses two fixed measurements and three fixed checks, which Apps Script converts into the same child-table structure.

The Defects table includes separate fields for deterministic defect type, human-approved disposition, AI category suggestion, AI summary, confidence, AI status, and AI error. AI suggestions never overwrite approved quality fields automatically.

Workflow Statuses and Ownership

A failed measurement cannot be changed to Pass merely by changing the inspection status. The underlying value or approved limits must be corrected by an authorized user, and the inspection must be reprocessed.

Rejection returns the record to the owner rather than deleting the defect. The rejection, comments, approver, and response time remain in Approvals and Audit_Log. Closure requires a valid sign-off identity and timestamp, completed or waived corrective actions, and approved disposition requests.

Step-by-Step Implementation

Step 1: Prepare the Accounts and Permissions

1. Create or designate a Google Workspace automation owner. This account owns the workbook, Form, Apps Script project, AppSheet application, and restricted Drive folders.
2. Confirm that the organization has the AppSheet features required for authenticated users, data synchronization, images, security filters, and automation. Licensing varies, so the required features should be confirmed against the organization’s current subscription.
3. Create a restricted Drive folder named Quality Inspection Records. Grant editor access only to the automation owner and designated quality administrators.
4. Create a Google Sheets workbook named Quality Inspection System inside the controlled quality folder.
5. Create a Google Form owned by the automation account and link it to the workbook. The response tab may initially be named Form Responses 1.
6. Create an AppSheet application from the workbook. Use authenticated access and restrict users to approved company accounts.
7. Create test identities representing an inspector, cell lead, quality approver, operations approver, and unauthorized employee.
8. Prepare separate test item codes and work orders. Do not use production records while configuring or testing.
9. Record the spreadsheet ID, root Drive folder ID, AppSheet image folder ID, quality manager email, and operations manager email.

The Apps Script project requests access to Sheets, Drive, Docs, email, external HTTPS requests if AI is enabled, and trigger management. The automation owner should authorize only the scopes required by the implemented functions.

App users should not receive direct edit access to the source workbook unless their job requires it. AppSheet access rules and source-file permissions must both be reviewed. A filtered app interface does not compensate for an unnecessarily shared workbook.

Step 2: Build the Intake

The implementation has two intake paths.

Google Form intake

Google Forms file upload requires the responder to sign in to a Google account and stores uploads in Drive. For this reason, the Form is restricted to authorized internal inspectors. It is not used as an anonymous public form.

The confirmation message states that a submitted inspection is not complete until processing succeeds and that inspectors should contact the quality coordinator if they do not receive confirmation.

The item dropdown must be synchronized when active items change. For a small item list, quality staff can update it through a controlled procedure. At higher item volumes, the Form becomes less suitable than AppSheet because Google Forms does not provide a live relational item lookup.

AppSheet intake

AppSheet is configured with Inspections as the parent form and Measurements, Checklist_Results, and Inspection_Photos as inline child tables.

• Inspection_ID initial value: CONCATENATE("INS-", UNIQUEID())
• Source initial value: "APPSHEET"
• Source_Key initial value: [Inspection_ID]
• Inspector_Email initial value: USEREMAIL()
• Status initial value: "DRAFT"
• Overall_Result initial value: "PENDING"
• Automation_Status initial value: "PENDING"
• Retry_Count initial value: 0

The Item_Code column is a Ref to Items. Its valid values are restricted to active items:

SELECT(Items[Item_Code], [Active] = TRUE)

Quantity_Affected is shown when the inspector records an observed issue. Its validation expression is:

AND(
  [Quantity_Affected] >= 0,
  [Quantity_Affected] <= [Quantity_Inspected]
)

AppSheet image columns store image paths and synchronize file content when connectivity is available. Inspectors using offline mode must keep the app open long enough for the later synchronization to finish. The workflow does not treat an unsynchronized local photo as stored evidence.

The Submit action is a grouped action that sets Status to SUBMITTED, Automation_Status to PENDING, Last_Automation_Run to blank, and Error_Message to blank. The action is available only when required parent and child data are complete.

Step 3: Create the System of Record

Create the following tabs:

• Items
• Users
• Inspections
• Measurements
• Checklist_Results
• Inspection_Photos
• Defects
• Corrective_Actions
• Approvals
• Audit_Log
• Error_Log

The complete Apps Script in the Code and Configuration section creates missing tabs and headers without overwriting existing rows.

Configure AppSheet keys and references as follows:

For AppSheet measurement rows, use the following deterministic result expression as an immediate user aid:

IFS(
  OR(
    ISBLANK([Measured_Value]),
    ISBLANK([Lower_Limit]),
    ISBLANK([Upper_Limit])
  ), "INCOMPLETE",
  AND(
    [Measured_Value] >= [Lower_Limit],
    [Measured_Value] <= [Upper_Limit]
  ), "PASS",
  TRUE, "FAIL"
)

Apps Script recalculates and persists the result after submission. The mobile expression improves immediate feedback, while server-side processing provides a consistent recorded result.

Add uniqueness checks for primary keys in AppSheet and protect key columns from editing. In Sheets, freeze the header row and protect formula-free system columns such as Automation_Status, Retry_Count, Document_Link, and Final_Report_Link from normal users.

Filtered AppSheet slices include My Draft Inspections, Submitted Inspections, Open Defects, My Corrective Actions, Requested Approvals, Awaiting Sign-off, Manual Review, and Closed Inspections.

Step 4: Connect the Tools

Google Form response labels must match the labels used by the script. If the Form is translated or renamed, update the field aliases in the normalization function.

AppSheet writes directly to Sheets through its data connection. Apps Script edit triggers are not relied upon for AppSheet changes because API and application writes do not behave like a person editing a cell. The time-based pending-record scanner provides a consistent integration boundary.

Step 5: Build the Core Automation

Automation 1: Normalize a Google Form response

• Trigger: Installed spreadsheet form-submit trigger.
• Conditions: Required Form fields exist and Source_Key has not already been processed.
• Actions: Look up the item, create the inspection, create child measurements, create checklist rows, create photo rows, and process the inspection.
• Fields updated: Source, Source_Key, item, inspector, quantities, status, result, owner, and automation fields.
• Notification: Sent after complete processing, not merely after Form submission.
• Exception: The error is written to Error_Log and visible in Apps Script execution history.

Automation 2: Process pending inspections

• Trigger: Time-based trigger every five minutes.
• Conditions: Automation_Status is PENDING or RETRY and Status is SUBMITTED or CLOSED.
• Actions: Acquire a script lock, validate master data, calculate measurement results, evaluate checks, create defects and actions, create approvals, generate reports, and send notifications.
• Fields updated: Overall_Result, Status, Document_Link, Automation_Status, Last_Automation_Run, Retry_Count, and Error_Message.
• Notification: Inspector and assigned owner receive the result.
• Exception: Transient errors retry up to three times; validation failures enter Manual Review immediately.

Automation 3: Create defects and corrective actions

• Trigger: At least one measurement or checklist failure, an observed defect description, or an existing linked defect.
• Conditions: No existing defect or action already exists for the inspection.
• Actions: Create a defect, set disposition to the proposed value or Hold for Review, create a containment action, assign the item owner, and calculate a due date.
• Fields updated: Defect ID, action ID, owner, due date, status, quantity affected, and AI status.
• Notification: Assigned owner receives the inspection and action identifiers.
• Exception: Duplicate detection uses the inspection foreign key before creating another row.

The exact action order is important. The script writes the business record before sending notification. It stores returned Drive URLs before marking the automation as processed. A notification audit event is written after successful delivery so a retry does not knowingly send the same event again.

Users recover a failed record by correcting the source data, changing Automation_Status from MANUAL_REVIEW to PENDING, setting Status to SUBMITTED when appropriate, and allowing the next scheduled run to process it.

Step 6: Add Approvals, Reminders, and Escalations

The selected approval rules are:

• A passing inspection requires final quality sign-off but no defect disposition approval.
• A failed inspection with fewer than 10 affected units and a normal hold or rework proposal requires one quality disposition approval.
• A failed inspection with 10 or more affected units, SCRAP, or USE_AS_IS requires operations approval followed by quality approval.
• Approvers may approve, reject, return for more information, or reassign when authorized.
• Final sign-off is unavailable until corrective actions are Completed or Waived and all required approvals are Approved.

High-risk approvals are sequential. The second approval remains Waiting until all lower-sequence approvals are approved. If future policy requires parallel reviews, multiple approvals can use the same sequence number. The provided script requests the next sequence only when all lower sequences are approved.

In AppSheet, create an Approve action that is visible only when:

AND(
  [Status] = "REQUESTED",
  [Approver_Email] = USEREMAIL()
)

The action sets Status to APPROVED, Responded_At to NOW(), and Responded_By to USEREMAIL(). A separate Reject action sets Status to REJECTED and requires comments.

Corrective-action reminders are sent on the due date and while overdue. The same reminder is sent no more than once per record per calendar day. Awaiting sign-off records receive a reminder after two days and escalate to a configured backup or quality manager after four days.

If an approver is unavailable, the quality manager may reassign the approval to an active backup user. The reassignment preserves the original approval row and adds an audit event. Approval time limits do not result in automatic approval.

Step 7: Add Documents and File Management

The root folder contains one folder per inspection:

Quality Inspection Records/
  INS-4F7A9C12/
    Photo-001-surface-defect.jpg
    Photo-002-label.jpg
    INS-4F7A9C12-inspection-report.pdf
    INS-4F7A9C12-final-report.pdf

Files use the inspection ID as the primary naming component. Original Google Form uploads remain in the Form upload folder. The script copies them into the inspection folder rather than moving them, which avoids breaking the original Form response.

AppSheet stores image paths relative to its configured application folder. The script uses the configured AppSheet image folder ID to resolve those paths. If the path cannot be resolved, the path remains in the photo record and the inspection enters an error or evidence-review workflow according to policy.

The script copies a file only when a file with the expected inspection-specific name does not already exist. This makes retries less likely to create duplicate files.

Drive permissions are inherited from the restricted root folder. Public link sharing is disabled. The report contains image links and attempts to embed supported images smaller than the configured reporting threshold. Unsupported or oversized images remain linked instead of being embedded.

Final retention and deletion periods must follow the company’s quality, customer, contractual, and regulatory obligations. Records should not be automatically deleted merely because an inspection is closed.

Step 8: Add Reporting and Operational Views

AppSheet provides operational views for individual users. Google Sheets pivots provide monthly analysis by item, inspection type, defect type, owner, disposition, and result.

Useful calculated metrics include submission-to-processing time, action cycle time, sign-off cycle time, pass rate, defect count, overdue-action count, and automation-failure count. Pass rate must be interpreted with the sampling method and inspection type rather than treated as a standalone quality measure.

The dashboard owner is the quality manager. The quality coordinator reviews refresh and data-completeness issues. Sheets pivots refresh when source data changes, while AppSheet reflects data after synchronization.

Step 9: Add Security and Governance Controls

• Require authenticated company accounts for both AppSheet and Google Forms.
• Grant workbook and folder access according to least privilege.
• Use role fields in Users to control approval and sign-off actions.
• Make primary keys, automation fields, report links, and audit fields read-only in AppSheet.
• Restrict approval actions to the assigned approver.
• Restrict final sign-off to active quality approvers.
• Store API secrets in Apps Script Properties, not in cells or source code.
• Do not use public Drive links for inspection photos or reports.
• Review Apps Script execution logs and Drive activity for administrative investigation.
• Remove former employees from Workspace groups, AppSheet access, Drive permissions, and active Users rows.
• Back up the workbook and verify that related Drive records remain available.
• Define retention rules based on customer, product, and regulatory requirements.
• Do not send controlled drawings, customer-sensitive text, personal information, or export-controlled data to an AI service without explicit approval.
• Keep disposition, product release, scrap, use-as-is, and final sign-off under human control.

AppSheet security filters may reduce the rows synchronized to a device, but they must be tested with each role. Sensitive data should not be placed in a broadly shared source workbook and hidden only through a user-interface view.

Step 10: Deploy and Test

1. Build the workbook, Form, AppSheet app, and Apps Script project using test data.
2. Run the setup function to create tabs and headers.
3. Configure item specifications and test users.
4. Authorize the script and install triggers from the automation-owner account.
5. Submit test records through both Google Forms and AppSheet.
6. Inspect Apps Script execution logs, Audit_Log, Error_Log, Drive files, and notification emails.
7. Conduct user acceptance testing with one inspector, one cell lead, the quality coordinator, and the quality manager.
8. Pilot the system on one inspection type or production cell for two weeks.
9. Compare digital records with the existing paper process during the pilot.
10. Correct field labels, role rules, tolerance data, and notification timing before broader activation.
11. Publish the AppSheet application only to approved users.
12. Retain a documented rollback procedure. During the initial pilot, this may mean returning to controlled paper forms while preserving submitted digital records for investigation.
13. Provide a one-page inspector guide, an action-owner guide, and an administrator runbook.
14. Assign the quality coordinator as primary support owner and a technically capable operations employee as backup.

Launch communication should explain what constitutes a submitted inspection, how photo synchronization works, how failures are reported, and which decisions still require manager approval.

Code and Configuration

The Apps Script project is bound to the Quality Inspection System workbook. Open the workbook, open the Apps Script editor from the Extensions menu, replace the default editor content with the following code, and replace the marked configuration values.

The script creates missing tables, normalizes Form submissions, processes AppSheet and Form records, evaluates tolerances, creates defects and approvals, generates reports, sends reminders, records failures, and optionally calls an AI service.

const CONFIG = Object.freeze({
  SPREADSHEET_ID: 'YOUR_SPREADSHEET_ID',
  ROOT_FOLDER_ID: 'YOUR_FOLDER_ID',
  APPSHEET_IMAGE_FOLDER_ID: 'YOUR_APPSHEET_IMAGE_FOLDER_ID',
  QUALITY_MANAGER_EMAIL: 'YOUR_EMAIL_ADDRESS',
  OPERATIONS_MANAGER_EMAIL: 'YOUR_EMAIL_ADDRESS',
  TIME_ZONE: 'Etc/UTC',
  MAX_RECORDS_PER_RUN: 25,
  MAX_RETRIES: 3,
  MAX_EMBED_IMAGE_BYTES: 5000000,
  AI_ENABLED: false,
  OPENAI_MODEL: 'gpt-4.1-mini'
});

const TABLE_HEADERS = Object.freeze({
  Items: [
    'Item_Code', 'Description', 'Active', 'Quality_Owner_Email',
    'M1_Name', 'M1_Lower', 'M1_Upper', 'M1_Unit',
    'M2_Name', 'M2_Lower', 'M2_Upper', 'M2_Unit'
  ],
  Users: [
    'Email', 'Name', 'Role', 'Area', 'Active', 'Backup_Email'
  ],
  Inspections: [
    'Inspection_ID', 'Source', 'Source_Key', 'Created_At', 'Submitted_At',
    'Inspector_Email', 'Job_Number', 'Item_Code', 'Lot_Serial',
    'Inspection_Type', 'Quantity_Inspected', 'Quantity_Affected',
    'Defect_Description', 'Proposed_Disposition', 'Owner_Email',
    'Status', 'Overall_Result', 'Document_Link', 'Final_Report_Link',
    'Folder_ID', 'Automation_Status', 'Last_Automation_Run',
    'Retry_Count', 'Error_Message', 'Signoff_Email', 'Signoff_At', 'Notes'
  ],
  Measurements: [
    'Measurement_ID', 'Inspection_ID', 'Characteristic', 'Measured_Value',
    'Lower_Limit', 'Upper_Limit', 'Unit', 'Result', 'Created_At'
  ],
  Checklist_Results: [
    'Check_Result_ID', 'Inspection_ID', 'Check_Name', 'Result',
    'Notes', 'Created_At'
  ],
  Inspection_Photos: [
    'Photo_ID', 'Inspection_ID', 'Source', 'Image_Path',
    'Stored_File_URL', 'Caption', 'Captured_At'
  ],
  Defects: [
    'Defect_ID', 'Inspection_ID', 'Created_At', 'Defect_Type',
    'Description', 'Quantity_Affected', 'Disposition', 'Owner_Email',
    'Status', 'Corrective_Action_Required', 'AI_Status', 'AI_Category',
    'AI_Summary', 'AI_Missing_Info', 'AI_Suggested_Priority',
    'AI_Confidence', 'AI_Error'
  ],
  Corrective_Actions: [
    'Action_ID', 'Defect_ID', 'Inspection_ID', 'Action',
    'Owner_Email', 'Due_Date', 'Status', 'Approved_By',
    'Approved_At', 'Completion_Notes', 'Evidence_Link'
  ],
  Approvals: [
    'Approval_ID', 'Inspection_ID', 'Defect_ID', 'Approval_Type',
    'Sequence', 'Approver_Email', 'Status', 'Requested_At',
    'Responded_At', 'Responded_By', 'Comments'
  ],
  Audit_Log: [
    'Event_ID', 'Record_Type', 'Record_ID', 'Event_Type',
    'Event_At', 'Actor', 'Details'
  ],
  Error_Log: [
    'Error_ID', 'Inspection_ID', 'Function', 'Error_At',
    'Retry_Count', 'Message', 'Stack', 'Resolved'
  ]
});

const AI_SYSTEM_INSTRUCTION =
  'You assist a manufacturing quality team by structuring an existing defect ' +
  'description. Do not approve disposition, product release, scrap, use-as-is, ' +
  'or corrective action. Use only the supplied facts. Do not infer an unseen ' +
  'condition. Return JSON matching the supplied schema. If information is ' +
  'missing, identify it explicitly.';

const AI_SCHEMA = {
  type: 'object',
  additionalProperties: false,
  properties: {
    category: {
      type: 'string',
      enum: [
        'DIMENSIONAL', 'VISUAL', 'MATERIAL', 'ASSEMBLY',
        'DOCUMENTATION', 'OTHER'
      ]
    },
    summary: { type: 'string', maxLength: 240 },
    missing_information: {
      type: 'array',
      items: {
        type: 'string',
        enum: [
          'LOCATION', 'QUANTITY', 'MEASUREMENT',
          'PHOTO', 'LOT_OR_SERIAL', 'NONE'
        ]
      }
    },
    suggested_priority: {
      type: 'string',
      enum: ['LOW', 'MEDIUM', 'HIGH']
    },
    confidence: {
      type: 'number',
      minimum: 0,
      maximum: 1
    },
    rationale: {
      type: 'string',
      maxLength: 240
    }
  },
  required: [
    'category', 'summary', 'missing_information',
    'suggested_priority', 'confidence', 'rationale'
  ]
};

function setupWorkbook() {
  const ss = openSpreadsheet_();

  Object.keys(TABLE_HEADERS).forEach(function(sheetName) {
    let sheet = ss.getSheetByName(sheetName);

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

    const requiredHeaders = TABLE_HEADERS[sheetName];
    const currentLastColumn = Math.max(sheet.getLastColumn(), 1);
    const currentHeaders = sheet.getRange(1, 1, 1, currentLastColumn)
      .getDisplayValues()[0]
      .filter(function(value) {
        return String(value).trim() !== '';
      });

    const missing = requiredHeaders.filter(function(header) {
      return currentHeaders.indexOf(header) === -1;
    });

    if (currentHeaders.length === 0) {
      sheet.getRange(1, 1, 1, requiredHeaders.length)
        .setValues([requiredHeaders]);
    } else if (missing.length > 0) {
      sheet.getRange(
        1,
        currentHeaders.length + 1,
        1,
        missing.length
      ).setValues([missing]);
    }

    sheet.setFrozenRows(1);
  });

  logAudit_(
    'SYSTEM',
    CONFIG.SPREADSHEET_ID,
    'WORKBOOK_SETUP',
    Session.getEffectiveUser().getEmail(),
    'Required tabs and headers verified.'
  );
}

function installTriggers() {
  const ss = openSpreadsheet_();
  const handlers = [
    'onFormSubmit',
    'processPendingInspections',
    'sendDailyReminders',
    'enrichPendingDefectsWithAI'
  ];

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

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

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

  ScriptApp.newTrigger('sendDailyReminders')
    .timeBased()
    .everyDays(1)
    .atHour(7)
    .create();

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

  logAudit_(
    'SYSTEM',
    CONFIG.SPREADSHEET_ID,
    'TRIGGERS_INSTALLED',
    Session.getEffectiveUser().getEmail(),
    'Form, processing, reminder, and optional AI triggers installed.'
  );
}

function onFormSubmit(event) {
  const lock = LockService.getScriptLock();

  if (!lock.tryLock(20000)) {
    throw new Error('Could not acquire the Form normalization lock.');
  }

  try {
    const inspectionId = normalizeFormSubmission_(event);
    if (inspectionId) {
      processInspection_(inspectionId);
    }
  } catch (error) {
    logError_('', 'onFormSubmit', 0, error);
    throw error;
  } finally {
    lock.releaseLock();
  }
}

function normalizeFormSubmission_(event) {
  if (!event || !event.range || !event.namedValues) {
    throw validationError_(
      'This function must run from an installed spreadsheet form-submit trigger.'
    );
  }

  const named = event.namedValues;
  const sourceKey =
    'FORM-' + event.range.getSheet().getSheetId() + '-' + event.range.getRow();

  if (findRecord_('Inspections', 'Source_Key', sourceKey)) {
    return '';
  }

  const inspectorEmail = requiredNamedValue_(
    named,
    ['Inspector email', 'Email Address']
  ).toLowerCase();

  if (!isValidEmail_(inspectorEmail)) {
    throw validationError_('Inspector email is invalid.');
  }

  const itemCode = requiredNamedValue_(named, ['Item code']);
  const item = findRecord_('Items', 'Item_Code', itemCode);

  if (!item || !isTrue_(item.Active)) {
    throw validationError_('Item code is missing or inactive: ' + itemCode);
  }

  const jobNumber = requiredNamedValue_(named, ['Job or work order']);
  const lotSerial = requiredNamedValue_(named, ['Lot or serial']);
  const inspectionType = requiredNamedValue_(named, ['Inspection type'])
    .toUpperCase();

  const allowedTypes = ['INCOMING', 'IN_PROCESS', 'FINAL', 'SERVICE'];
  if (allowedTypes.indexOf(inspectionType) === -1) {
    throw validationError_('Inspection type is not allowed.');
  }

  const quantityInspected = toNumber_(
    requiredNamedValue_(named, ['Quantity inspected']),
    'Quantity inspected'
  );

  if (!Number.isInteger(quantityInspected) || quantityInspected <= 0) {
    throw validationError_(
      'Quantity inspected must be an integer greater than zero.'
    );
  }

  const observedIssue = namedValue_(named, ['Observed issue?']).toUpperCase();
  const quantityAffectedRaw = namedValue_(named, ['Quantity affected']);
  const quantityAffected = quantityAffectedRaw === ''
    ? 0
    : toNumber_(quantityAffectedRaw, 'Quantity affected');

  if (
    !Number.isInteger(quantityAffected) ||
    quantityAffected < 0 ||
    quantityAffected > quantityInspected
  ) {
    throw validationError_(
      'Quantity affected must be from zero to quantity inspected.'
    );
  }

  const measurement1 = namedValue_(named, ['Measurement 1']);
  const measurement2 = namedValue_(named, ['Measurement 2']);

  if (String(item.M1_Name || '').trim() !== '' && measurement1 === '') {
    throw validationError_('Measurement 1 is required for this item.');
  }

  const checks = [
    {
      name: 'Surface condition',
      value: requiredNamedValue_(named, ['Surface condition'])
    },
    {
      name: 'Label and marking',
      value: requiredNamedValue_(named, ['Label and marking'])
    },
    {
      name: 'Assembly completeness',
      value: requiredNamedValue_(named, ['Assembly completeness'])
    }
  ];

  checks.forEach(function(check) {
    const result = check.value.toUpperCase();
    if (['PASS', 'FAIL', 'N/A'].indexOf(result) === -1) {
      throw validationError_('Invalid checklist value for ' + check.name);
    }
    check.value = result;
  });

  const inspectionId = newId_('INS');
  const now = new Date();
  const proposedDisposition = namedValue_(
    named,
    ['Proposed disposition']
  ).toUpperCase();

  appendRecord_('Inspections', {
    Inspection_ID: inspectionId,
    Source: 'GOOGLE_FORM',
    Source_Key: sourceKey,
    Created_At: now,
    Submitted_At: now,
    Inspector_Email: inspectorEmail,
    Job_Number: jobNumber,
    Item_Code: itemCode,
    Lot_Serial: lotSerial,
    Inspection_Type: inspectionType,
    Quantity_Inspected: quantityInspected,
    Quantity_Affected: quantityAffected,
    Defect_Description: namedValue_(named, ['Defect description']),
    Proposed_Disposition: proposedDisposition || 'NONE',
    Owner_Email: String(item.Quality_Owner_Email || '').toLowerCase(),
    Status: 'SUBMITTED',
    Overall_Result: 'PENDING',
    Automation_Status: 'PENDING',
    Retry_Count: 0,
    Notes: observedIssue === 'YES' ? 'Issue reported by inspector.' : ''
  });

  appendFormMeasurement_(
    inspectionId,
    item.M1_Name,
    measurement1,
    item.M1_Lower,
    item.M1_Upper,
    item.M1_Unit
  );

  appendFormMeasurement_(
    inspectionId,
    item.M2_Name,
    measurement2,
    item.M2_Lower,
    item.M2_Upper,
    item.M2_Unit
  );

  checks.forEach(function(check) {
    appendRecord_('Checklist_Results', {
      Check_Result_ID: newId_('CHK'),
      Inspection_ID: inspectionId,
      Check_Name: check.name,
      Result: check.value,
      Created_At: now
    });
  });

  const photoValues = splitMultiValue_(
    namedValue_(named, ['Inspection photos'])
  );

  photoValues.forEach(function(photoValue) {
    appendRecord_('Inspection_Photos', {
      Photo_ID: newId_('PHT'),
      Inspection_ID: inspectionId,
      Source: 'GOOGLE_FORM',
      Image_Path: photoValue,
      Caption: 'Google Form inspection upload',
      Captured_At: now
    });
  });

  logAudit_(
    'INSPECTION',
    inspectionId,
    'FORM_NORMALIZED',
    inspectorEmail,
    'Google Form response normalized from ' + sourceKey
  );

  return inspectionId;
}

function appendFormMeasurement_(
  inspectionId,
  characteristic,
  measuredValue,
  lowerLimit,
  upperLimit,
  unit
) {
  if (String(characteristic || '').trim() === '') {
    return;
  }

  if (String(measuredValue || '').trim() === '') {
    return;
  }

  appendRecord_('Measurements', {
    Measurement_ID: newId_('MEA'),
    Inspection_ID: inspectionId,
    Characteristic: characteristic,
    Measured_Value: toNumber_(measuredValue, characteristic),
    Lower_Limit: toNumber_(lowerLimit, characteristic + ' lower limit'),
    Upper_Limit: toNumber_(upperLimit, characteristic + ' upper limit'),
    Unit: unit,
    Result: 'PENDING',
    Created_At: new Date()
  });
}

function processPendingInspections() {
  const lock = LockService.getScriptLock();

  if (!lock.tryLock(20000)) {
    console.log('Another inspection-processing run is active.');
    return;
  }

  try {
    const records = listRecords_('Inspections');
    let processed = 0;

    for (let index = 0; index < records.length; index += 1) {
      if (processed >= CONFIG.MAX_RECORDS_PER_RUN) {
        break;
      }

      const record = records[index];
      const automationStatus = String(
        record.Automation_Status || ''
      ).toUpperCase();
      const status = String(record.Status || '').toUpperCase();

      if (
        ['PENDING', 'RETRY'].indexOf(automationStatus) === -1 ||
        ['SUBMITTED', 'CLOSED'].indexOf(status) === -1
      ) {
        continue;
      }

      try {
        if (status === 'CLOSED') {
          processClosedInspection_(record.Inspection_ID);
        } else {
          processInspection_(record.Inspection_ID);
        }
      } catch (error) {
        recordProcessingFailure_(record, error);
      }

      processed += 1;
    }

    advanceApprovals_();
  } finally {
    lock.releaseLock();
  }
}

function processInspection_(inspectionId) {
  let inspection = findRecord_(
    'Inspections',
    'Inspection_ID',
    inspectionId
  );

  if (!inspection) {
    throw validationError_('Inspection was not found: ' + inspectionId);
  }

  requireInspectionFields_(inspection);

  const item = findRecord_('Items', 'Item_Code', inspection.Item_Code);
  if (!item || !isTrue_(item.Active)) {
    throw validationError_('Inspection item is inactive or missing.');
  }

  const measurements = listRecordsBy_(
    'Measurements',
    'Inspection_ID',
    inspectionId
  );

  if (String(item.M1_Name || '').trim() !== '' && measurements.length === 0) {
    throw validationError_('Required measurements are missing.');
  }

  let measurementFailed = false;
  const failedCharacteristics = [];

  measurements.forEach(function(measurement) {
    const value = toNumber_(
      measurement.Measured_Value,
      measurement.Characteristic
    );
    const lower = toNumber_(
      measurement.Lower_Limit,
      measurement.Characteristic + ' lower limit'
    );
    const upper = toNumber_(
      measurement.Upper_Limit,
      measurement.Characteristic + ' upper limit'
    );

    if (lower > upper) {
      throw validationError_(
        'Lower limit exceeds upper limit for ' +
        measurement.Characteristic
      );
    }

    const result = value >= lower && value <= upper
      ? 'PASS'
      : 'FAIL';

    updateRow_('Measurements', measurement.__row, {
      Measured_Value: value,
      Lower_Limit: lower,
      Upper_Limit: upper,
      Result: result
    });

    if (result === 'FAIL') {
      measurementFailed = true;
      failedCharacteristics.push(String(measurement.Characteristic));
    }
  });

  const checks = listRecordsBy_(
    'Checklist_Results',
    'Inspection_ID',
    inspectionId
  );

  if (checks.length === 0) {
    throw validationError_('At least one checklist result is required.');
  }

  const failedChecks = checks.filter(function(check) {
    return String(check.Result || '').toUpperCase() === 'FAIL';
  });

  checks.forEach(function(check) {
    const result = String(check.Result || '').toUpperCase();
    if (['PASS', 'FAIL', 'N/A'].indexOf(result) === -1) {
      throw validationError_(
        'Invalid checklist result for ' + check.Check_Name
      );
    }
  });

  let defect = findRecord_('Defects', 'Inspection_ID', inspectionId);
  const describedDefect =
    String(inspection.Defect_Description || '').trim() !== '';
  const failed =
    measurementFailed ||
    failedChecks.length > 0 ||
    describedDefect ||
    Boolean(defect);

  if (failed && !defect) {
    defect = createDefect_(
      inspection,
      failedCharacteristics,
      failedChecks
    );
  }

  if (failed && defect) {
    createCorrectiveActionIfMissing_(inspection, defect);
    createApprovalsIfMissing_(inspection, defect);
  }

  const nextStatus = failed
    ? 'ACTION_REQUIRED'
    : 'AWAITING_SIGNOFF';

  updateRecordByKey_(
    'Inspections',
    'Inspection_ID',
    inspectionId,
    {
      Owner_Email:
        inspection.Owner_Email || item.Quality_Owner_Email,
      Overall_Result: failed ? 'FAIL' : 'PASS',
      Status: nextStatus,
      Last_Automation_Run: new Date(),
      Error_Message: ''
    }
  );

  SpreadsheetApp.flush();

  inspection = findRecord_(
    'Inspections',
    'Inspection_ID',
    inspectionId
  );

  const reportUrl = createInspectionReport_(
    inspection,
    measurements,
    checks,
    defect,
    false
  );

  updateRecordByKey_(
    'Inspections',
    'Inspection_ID',
    inspectionId,
    {
      Document_Link: reportUrl,
      Automation_Status: 'PROCESSED',
      Last_Automation_Run: new Date(),
      Retry_Count: 0,
      Error_Message: ''
    }
  );

  const recipient = failed
    ? String(inspection.Owner_Email || item.Quality_Owner_Email)
    : String(inspection.Inspector_Email);

  sendOnce_(
    'INSPECTION',
    inspectionId,
    'PROCESSING_RESULT',
    recipient,
    'Inspection ' + inspectionId + ' processed',
    [
      'Inspection: ' + inspectionId,
      'Job: ' + inspection.Job_Number,
      'Item: ' + inspection.Item_Code,
      'Result: ' + (failed ? 'FAIL' : 'PASS'),
      'Status: ' + nextStatus,
      'Report: ' + reportUrl
    ].join('\n')
  );

  logAudit_(
    'INSPECTION',
    inspectionId,
    'PROCESSING_COMPLETED',
    'Apps Script',
    'Result ' + (failed ? 'FAIL' : 'PASS')
  );
}

function requireInspectionFields_(inspection) {
  const required = [
    'Inspection_ID', 'Inspector_Email', 'Job_Number',
    'Item_Code', 'Lot_Serial', 'Inspection_Type'
  ];

  required.forEach(function(field) {
    if (String(inspection[field] || '').trim() === '') {
      throw validationError_('Missing required field: ' + field);
    }
  });

  if (!isValidEmail_(inspection.Inspector_Email)) {
    throw validationError_('Inspector email is invalid.');
  }

  const quantity = toNumber_(
    inspection.Quantity_Inspected,
    'Quantity_Inspected'
  );

  if (!Number.isInteger(quantity) || quantity <= 0) {
    throw validationError_('Quantity_Inspected must be a positive integer.');
  }
}

function createDefect_(inspection, failedCharacteristics, failedChecks) {
  const descriptionParts = [];

  if (String(inspection.Defect_Description || '').trim() !== '') {
    descriptionParts.push(String(inspection.Defect_Description).trim());
  }

  if (failedCharacteristics.length > 0) {
    descriptionParts.push(
      'Failed measurements: ' + failedCharacteristics.join(', ')
    );
  }

  if (failedChecks.length > 0) {
    descriptionParts.push(
      'Failed checks: ' +
      failedChecks.map(function(check) {
        return check.Check_Name;
      }).join(', ')
    );
  }

  const defectId = newId_('DEF');
  const defectType = failedCharacteristics.length > 0
    ? 'DIMENSIONAL'
    : 'CHECKLIST';

  appendRecord_('Defects', {
    Defect_ID: defectId,
    Inspection_ID: inspection.Inspection_ID,
    Created_At: new Date(),
    Defect_Type: defectType,
    Description: descriptionParts.join(' | '),
    Quantity_Affected: Number(inspection.Quantity_Affected || 0),
    Disposition:
      String(inspection.Proposed_Disposition || 'HOLD').toUpperCase(),
    Owner_Email: inspection.Owner_Email,
    Status: 'OPEN',
    Corrective_Action_Required: 'YES',
    AI_Status: CONFIG.AI_ENABLED ? 'PENDING' : 'NOT_REQUESTED'
  });

  logAudit_(
    'DEFECT',
    defectId,
    'DEFECT_CREATED',
    'Apps Script',
    'Created from inspection ' + inspection.Inspection_ID
  );

  return findRecord_('Defects', 'Defect_ID', defectId);
}

function createCorrectiveActionIfMissing_(inspection, defect) {
  const existing = findRecord_(
    'Corrective_Actions',
    'Inspection_ID',
    inspection.Inspection_ID
  );

  if (existing) {
    return existing;
  }

  const actionId = newId_('ACT');
  appendRecord_('Corrective_Actions', {
    Action_ID: actionId,
    Defect_ID: defect.Defect_ID,
    Inspection_ID: inspection.Inspection_ID,
    Action: 'Contain affected material and document correction or rework.',
    Owner_Email: defect.Owner_Email || inspection.Owner_Email,
    Due_Date: addBusinessDays_(new Date(), 3),
    Status: 'OPEN'
  });

  logAudit_(
    'ACTION',
    actionId,
    'ACTION_CREATED',
    'Apps Script',
    'Corrective action created for ' + defect.Defect_ID
  );

  return findRecord_('Corrective_Actions', 'Action_ID', actionId);
}

function createApprovalsIfMissing_(inspection, defect) {
  const existing = listRecordsBy_(
    'Approvals',
    'Inspection_ID',
    inspection.Inspection_ID
  );

  if (existing.length > 0) {
    return;
  }

  const quantityAffected = Number(inspection.Quantity_Affected || 0);
  const disposition = String(
    inspection.Proposed_Disposition || 'HOLD'
  ).toUpperCase();

  const highRisk =
    quantityAffected >= 10 ||
    ['SCRAP', 'USE_AS_IS'].indexOf(disposition) !== -1;

  if (highRisk) {
    appendApproval_(
      inspection,
      defect,
      'OPERATIONS_DISPOSITION',
      1,
      CONFIG.OPERATIONS_MANAGER_EMAIL,
      'REQUESTED'
    );

    appendApproval_(
      inspection,
      defect,
      'QUALITY_DISPOSITION',
      2,
      CONFIG.QUALITY_MANAGER_EMAIL,
      'WAITING'
    );
  } else {
    appendApproval_(
      inspection,
      defect,
      'QUALITY_DISPOSITION',
      1,
      CONFIG.QUALITY_MANAGER_EMAIL,
      'REQUESTED'
    );
  }
}

function appendApproval_(
  inspection,
  defect,
  approvalType,
  sequence,
  approverEmail,
  status
) {
  if (!isValidEmail_(approverEmail)) {
    throw validationError_(
      'Configured approver email is invalid for ' + approvalType
    );
  }

  const approvalId = newId_('APR');

  appendRecord_('Approvals', {
    Approval_ID: approvalId,
    Inspection_ID: inspection.Inspection_ID,
    Defect_ID: defect.Defect_ID,
    Approval_Type: approvalType,
    Sequence: sequence,
    Approver_Email: approverEmail,
    Status: status,
    Requested_At: status === 'REQUESTED' ? new Date() : ''
  });

  if (status === 'REQUESTED') {
    sendOnce_(
      'APPROVAL',
      approvalId,
      'APPROVAL_REQUESTED',
      approverEmail,
      'Approval requested for ' + inspection.Inspection_ID,
      'Review approval ' + approvalId +
        ' for inspection ' + inspection.Inspection_ID + '.'
    );
  }
}

function advanceApprovals_() {
  const approvals = listRecords_('Approvals');
  const inspectionIds = [];

  approvals.forEach(function(approval) {
    if (
      approval.Inspection_ID &&
      inspectionIds.indexOf(approval.Inspection_ID) === -1
    ) {
      inspectionIds.push(approval.Inspection_ID);
    }
  });

  inspectionIds.forEach(function(inspectionId) {
    const inspection = findRecord_(
      'Inspections',
      'Inspection_ID',
      inspectionId
    );

    if (!inspection || String(inspection.Status) === 'CLOSED') {
      return;
    }

    const related = listRecordsBy_(
      'Approvals',
      'Inspection_ID',
      inspectionId
    );

    const rejected = related.some(function(approval) {
      return String(approval.Status).toUpperCase() === 'REJECTED';
    });

    if (rejected) {
      updateRecordByKey_(
        'Inspections',
        'Inspection_ID',
        inspectionId,
        { Status: 'RETURNED_FOR_INFO' }
      );

      sendOnce_(
        'INSPECTION',
        inspectionId,
        'APPROVAL_REJECTED',
        inspection.Owner_Email,
        'Inspection returned for information',
        'Inspection ' + inspectionId +
          ' has a rejected disposition approval.'
      );
      return;
    }

    related.forEach(function(approval) {
      if (String(approval.Status).toUpperCase() !== 'WAITING') {
        return;
      }

      const sequence = Number(approval.Sequence);
      const lowerApproved = related
        .filter(function(other) {
          return Number(other.Sequence) < sequence;
        })
        .every(function(other) {
          return String(other.Status).toUpperCase() === 'APPROVED';
        });

      if (lowerApproved) {
        updateRow_('Approvals', approval.__row, {
          Status: 'REQUESTED',
          Requested_At: new Date()
        });

        sendOnce_(
          'APPROVAL',
          approval.Approval_ID,
          'APPROVAL_REQUESTED',
          approval.Approver_Email,
          'Approval requested for ' + inspectionId,
          'Review approval ' + approval.Approval_ID +
            ' for inspection ' + inspectionId + '.'
        );
      }
    });

    const refreshedApprovals = listRecordsBy_(
      'Approvals',
      'Inspection_ID',
      inspectionId
    );

    const allApproved =
      refreshedApprovals.length > 0 &&
      refreshedApprovals.every(function(approval) {
        return String(approval.Status).toUpperCase() === 'APPROVED';
      });

    const actions = listRecordsBy_(
      'Corrective_Actions',
      'Inspection_ID',
      inspectionId
    );

    const actionsComplete =
      actions.length > 0 &&
      actions.every(function(action) {
        return ['COMPLETED', 'WAIVED'].indexOf(
          String(action.Status).toUpperCase()
        ) !== -1;
      });

    if (allApproved && actionsComplete) {
      updateRecordByKey_(
        'Inspections',
        'Inspection_ID',
        inspectionId,
        { Status: 'AWAITING_SIGNOFF' }
      );

      sendOnce_(
        'INSPECTION',
        inspectionId,
        'SIGNOFF_READY',
        CONFIG.QUALITY_MANAGER_EMAIL,
        'Inspection ready for sign-off',
        'Inspection ' + inspectionId +
          ' has completed actions and approvals.'
      );
    }
  });
}

function processClosedInspection_(inspectionId) {
  const inspection = findRecord_(
    'Inspections',
    'Inspection_ID',
    inspectionId
  );

  if (!inspection) {
    throw validationError_('Closed inspection was not found.');
  }

  if (
    !isValidEmail_(inspection.Signoff_Email) ||
    !inspection.Signoff_At
  ) {
    throw validationError_(
      'Closed inspection requires sign-off email and time.'
    );
  }

  const actions = listRecordsBy_(
    'Corrective_Actions',
    'Inspection_ID',
    inspectionId
  );

  const incompleteAction = actions.some(function(action) {
    return ['COMPLETED', 'WAIVED'].indexOf(
      String(action.Status).toUpperCase()
    ) === -1;
  });

  if (incompleteAction) {
    throw validationError_(
      'Closed inspection has an incomplete corrective action.'
    );
  }

  const approvals = listRecordsBy_(
    'Approvals',
    'Inspection_ID',
    inspectionId
  );

  const incompleteApproval = approvals.some(function(approval) {
    return String(approval.Status).toUpperCase() !== 'APPROVED';
  });

  if (incompleteApproval) {
    throw validationError_(
      'Closed inspection has an incomplete approval.'
    );
  }

  const reportUrl = createInspectionReport_(
    inspection,
    listRecordsBy_('Measurements', 'Inspection_ID', inspectionId),
    listRecordsBy_('Checklist_Results', 'Inspection_ID', inspectionId),
    findRecord_('Defects', 'Inspection_ID', inspectionId),
    true
  );

  updateRecordByKey_(
    'Inspections',
    'Inspection_ID',
    inspectionId,
    {
      Final_Report_Link: reportUrl,
      Automation_Status: 'PROCESSED',
      Last_Automation_Run: new Date(),
      Retry_Count: 0,
      Error_Message: ''
    }
  );

  sendOnce_(
    'INSPECTION',
    inspectionId,
    'INSPECTION_CLOSED',
    inspection.Inspector_Email,
    'Inspection closed: ' + inspectionId,
    'Final report: ' + reportUrl
  );

  logAudit_(
    'INSPECTION',
    inspectionId,
    'INSPECTION_CLOSED',
    inspection.Signoff_Email,
    'Final report created.'
  );
}

function createInspectionReport_(
  inspection,
  measurements,
  checks,
  defect,
  isFinal
) {
  const existingUrl = isFinal
    ? inspection.Final_Report_Link
    : inspection.Document_Link;

  if (String(existingUrl || '').trim() !== '') {
    return existingUrl;
  }

  const folder = getOrCreateInspectionFolder_(inspection);
  const reportType = isFinal ? 'final-report' : 'inspection-report';
  const documentName =
    inspection.Inspection_ID + '-' + reportType;

  const doc = DocumentApp.create(documentName);
  const body = doc.getBody();

  body.appendParagraph(
    isFinal ? 'Final Quality Inspection Report' : 'Quality Inspection Report'
  ).setHeading(DocumentApp.ParagraphHeading.HEADING1);

  body.appendTable([
    ['Inspection ID', String(inspection.Inspection_ID)],
    ['Job', String(inspection.Job_Number)],
    ['Item', String(inspection.Item_Code)],
    ['Lot or serial', String(inspection.Lot_Serial)],
    ['Inspection type', String(inspection.Inspection_Type)],
    ['Inspector', String(inspection.Inspector_Email)],
    ['Quantity inspected', String(inspection.Quantity_Inspected)],
    ['Quantity affected', String(inspection.Quantity_Affected || 0)],
    ['Overall result', String(inspection.Overall_Result)],
    ['Status', String(inspection.Status)]
  ]);

  body.appendParagraph('Measurements')
    .setHeading(DocumentApp.ParagraphHeading.HEADING2);

  const measurementRows = [
    ['Characteristic', 'Value', 'Lower', 'Upper', 'Unit', 'Result']
  ];

  measurements.forEach(function(measurement) {
    measurementRows.push([
      String(measurement.Characteristic),
      String(measurement.Measured_Value),
      String(measurement.Lower_Limit),
      String(measurement.Upper_Limit),
      String(measurement.Unit || ''),
      String(measurement.Result)
    ]);
  });

  body.appendTable(measurementRows);

  body.appendParagraph('Checklist')
    .setHeading(DocumentApp.ParagraphHeading.HEADING2);

  const checkRows = [['Check', 'Result', 'Notes']];
  checks.forEach(function(check) {
    checkRows.push([
      String(check.Check_Name),
      String(check.Result),
      String(check.Notes || '')
    ]);
  });
  body.appendTable(checkRows);

  if (defect) {
    body.appendParagraph('Defect')
      .setHeading(DocumentApp.ParagraphHeading.HEADING2);
    body.appendTable([
      ['Defect ID', String(defect.Defect_ID)],
      ['Type', String(defect.Defect_Type)],
      ['Description', String(defect.Description)],
      ['Disposition', String(defect.Disposition)],
      ['Status', String(defect.Status)]
    ]);
  }

  const photos = listRecordsBy_(
    'Inspection_Photos',
    'Inspection_ID',
    inspection.Inspection_ID
  );

  if (photos.length > 0) {
    body.appendParagraph('Photographic Evidence')
      .setHeading(DocumentApp.ParagraphHeading.HEADING2);
  }

  photos.forEach(function(photo, index) {
    const sourceFile = resolvePhotoFile_(photo.Image_Path);

    if (!sourceFile) {
      body.appendParagraph(
        'Photo could not be embedded: ' + String(photo.Image_Path)
      );
      return;
    }

    const safeName =
      'Photo-' + String(index + 1).padStart(3, '0') + '-' +
      sanitizeFileName_(sourceFile.getName());

    let copiedFile;
    const existingCopies = folder.getFilesByName(safeName);

    if (existingCopies.hasNext()) {
      copiedFile = existingCopies.next();
    } else {
      copiedFile = sourceFile.makeCopy(safeName, folder);
    }

    updateRow_('Inspection_Photos', photo.__row, {
      Stored_File_URL: copiedFile.getUrl()
    });

    body.appendParagraph(
      String(photo.Caption || safeName) + ': ' + copiedFile.getUrl()
    );

    if (copiedFile.getSize() <= CONFIG.MAX_EMBED_IMAGE_BYTES) {
      try {
        const image = body.appendImage(copiedFile.getBlob());
        image.setWidth(450);
      } catch (error) {
        body.appendParagraph(
          'Image format could not be embedded. Use the file link.'
        );
      }
    }
  });

  if (isFinal) {
    body.appendParagraph('Final Sign-off')
      .setHeading(DocumentApp.ParagraphHeading.HEADING2);
    body.appendTable([
      ['Approved by', String(inspection.Signoff_Email)],
      ['Approved at', formatDate_(inspection.Signoff_At)]
    ]);
  }

  body.appendParagraph(
    'Generated at ' + formatDate_(new Date()) +
    ' by the quality inspection automation.'
  );

  doc.saveAndClose();

  const docFile = DriveApp.getFileById(doc.getId());
  docFile.moveTo(folder);

  const pdfBlob = docFile.getAs(MimeType.PDF)
    .setName(documentName + '.pdf');
  const pdfFile = folder.createFile(pdfBlob);

  return pdfFile.getUrl();
}

function getOrCreateInspectionFolder_(inspection) {
  if (String(inspection.Folder_ID || '').trim() !== '') {
    try {
      return DriveApp.getFolderById(inspection.Folder_ID);
    } catch (error) {
      console.log('Stored folder ID is unavailable; creating a new folder.');
    }
  }

  requireConfiguredValue_(
    CONFIG.ROOT_FOLDER_ID,
    'ROOT_FOLDER_ID'
  );

  const root = DriveApp.getFolderById(CONFIG.ROOT_FOLDER_ID);
  const folderName = sanitizeFileName_(inspection.Inspection_ID);
  const existing = root.getFoldersByName(folderName);
  const folder = existing.hasNext()
    ? existing.next()
    : root.createFolder(folderName);

  updateRecordByKey_(
    'Inspections',
    'Inspection_ID',
    inspection.Inspection_ID,
    { Folder_ID: folder.getId() }
  );

  return folder;
}

function resolvePhotoFile_(pathOrUrl) {
  const value = String(pathOrUrl || '').trim();

  if (value === '') {
    return null;
  }

  const driveId = extractDriveId_(value);
  if (driveId) {
    try {
      return DriveApp.getFileById(driveId);
    } catch (error) {
      return null;
    }
  }

  if (
    !CONFIG.APPSHEET_IMAGE_FOLDER_ID ||
    CONFIG.APPSHEET_IMAGE_FOLDER_ID.indexOf('YOUR_') === 0
  ) {
    return null;
  }

  try {
    let folder = DriveApp.getFolderById(
      CONFIG.APPSHEET_IMAGE_FOLDER_ID
    );
    const parts = value.replace(/^\/+/, '').split('/');
    const fileName = parts.pop();

    parts.forEach(function(folderName) {
      if (!folderName) {
        return;
      }
      const matchingFolders = folder.getFoldersByName(folderName);
      if (!matchingFolders.hasNext()) {
        throw new Error('Photo subfolder not found: ' + folderName);
      }
      folder = matchingFolders.next();
    });

    const files = folder.getFilesByName(fileName);
    return files.hasNext() ? files.next() : null;
  } catch (error) {
    console.log('Photo resolution failed: ' + error.message);
    return null;
  }
}

function extractDriveId_(value) {
  const patterns = [
    /\/d\/([A-Za-z0-9_-]{20,})/,
    /[?&]id=([A-Za-z0-9_-]{20,})/,
    /^([A-Za-z0-9_-]{20,})$/
  ];

  for (let index = 0; index < patterns.length; index += 1) {
    const match = String(value).match(patterns[index]);
    if (match) {
      return match[1];
    }
  }

  return '';
}

function sendDailyReminders() {
  const today = new Date();
  const dateKey = Utilities.formatDate(
    today,
    CONFIG.TIME_ZONE,
    'yyyy-MM-dd'
  );

  listRecords_('Corrective_Actions').forEach(function(action) {
    const status = String(action.Status || '').toUpperCase();
    if (['COMPLETED', 'WAIVED'].indexOf(status) !== -1) {
      return;
    }

    const dueDate = toDate_(action.Due_Date);
    if (!dueDate || dueDate.getTime() > today.getTime()) {
      return;
    }

    sendOnce_(
      'ACTION',
      action.Action_ID,
      'ACTION_REMINDER_' + dateKey,
      action.Owner_Email,
      'Corrective action due or overdue',
      'Action ' + action.Action_ID +
        ' for inspection ' + action.Inspection_ID +
        ' was due ' + formatDate_(dueDate) + '.'
    );
  });

  listRecords_('Inspections').forEach(function(inspection) {
    if (String(inspection.Status) !== 'AWAITING_SIGNOFF') {
      return;
    }

    const submittedAt = toDate_(
      inspection.Submitted_At || inspection.Created_At
    );
    const age = daysBetween_(submittedAt, today);

    if (age >= 2) {
      sendOnce_(
        'INSPECTION',
        inspection.Inspection_ID,
        'SIGNOFF_REMINDER_' + dateKey,
        CONFIG.QUALITY_MANAGER_EMAIL,
        'Inspection awaiting sign-off',
        'Inspection ' + inspection.Inspection_ID +
          ' has awaited sign-off for ' + age + ' days.'
      );
    }

    if (age >= 4) {
      const backup = findBackupEmail_(
        CONFIG.QUALITY_MANAGER_EMAIL
      );

      sendOnce_(
        'INSPECTION',
        inspection.Inspection_ID,
        'SIGNOFF_ESCALATION_' + dateKey,
        backup || CONFIG.OPERATIONS_MANAGER_EMAIL,
        'Escalation: inspection awaiting sign-off',
        'Inspection ' + inspection.Inspection_ID +
          ' has awaited sign-off for ' + age + ' days.'
      );
    }
  });
}

function enrichPendingDefectsWithAI() {
  if (!CONFIG.AI_ENABLED) {
    return;
  }

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

  if (!apiKey) {
    throw new Error(
      'OPENAI_API_KEY is missing from Apps Script Properties.'
    );
  }

  const defects = listRecords_('Defects');
  let processed = 0;

  for (let index = 0; index < defects.length; index += 1) {
    if (processed >= CONFIG.MAX_RECORDS_PER_RUN) {
      break;
    }

    const defect = defects[index];
    if (String(defect.AI_Status).toUpperCase() !== 'PENDING') {
      continue;
    }

    const inspection = findRecord_(
      'Inspections',
      'Inspection_ID',
      defect.Inspection_ID
    );

    try {
      const result = callDefectAI_(apiKey, defect, inspection);
      validateAIResult_(result);

      updateRow_('Defects', defect.__row, {
        AI_Status: 'REVIEW_REQUIRED',
        AI_Category: result.category,
        AI_Summary: result.summary,
        AI_Missing_Info: result.missing_information.join(', '),
        AI_Suggested_Priority: result.suggested_priority,
        AI_Confidence: result.confidence,
        AI_Error: ''
      });

      logAudit_(
        'DEFECT',
        defect.Defect_ID,
        'AI_SUGGESTION_CREATED',
        'Apps Script',
        'AI output requires human review.'
      );
    } catch (error) {
      updateRow_('Defects', defect.__row, {
        AI_Status: 'AI_ERROR',
        AI_Error: String(error.message).slice(0, 1000)
      });
      logError_(
        defect.Inspection_ID,
        'enrichPendingDefectsWithAI',
        0,
        error
      );
    }

    processed += 1;
  }
}

function callDefectAI_(apiKey, defect, inspection) {
  const input = {
    defect_id: defect.Defect_ID,
    inspection_type: inspection ? inspection.Inspection_Type : '',
    item_code: inspection ? inspection.Item_Code : '',
    quantity_inspected: inspection
      ? Number(inspection.Quantity_Inspected || 0)
      : 0,
    quantity_affected: Number(defect.Quantity_Affected || 0),
    deterministic_defect_type: defect.Defect_Type,
    defect_description: defect.Description,
    proposed_disposition: defect.Disposition
  };

  const payload = {
    model: CONFIG.OPENAI_MODEL,
    instructions: AI_SYSTEM_INSTRUCTION,
    input: JSON.stringify(input),
    text: {
      format: {
        type: 'json_schema',
        name: 'defect_triage',
        strict: true,
        schema: AI_SCHEMA
      }
    }
  };

  let lastError = null;

  for (let attempt = 0; attempt < 3; attempt += 1) {
    const response = UrlFetchApp.fetch(
      'https://api.openai.com/v1/responses',
      {
        method: 'post',
        contentType: 'application/json',
        headers: {
          Authorization: 'Bearer ' + apiKey
        },
        payload: JSON.stringify(payload),
        muteHttpExceptions: true
      }
    );

    const statusCode = response.getResponseCode();
    const responseText = response.getContentText();

    if (statusCode >= 200 && statusCode < 300) {
      const parsed = JSON.parse(responseText);
      const outputText = extractResponseOutputText_(parsed);
      return JSON.parse(outputText);
    }

    lastError = new Error(
      'AI request failed with HTTP ' +
      statusCode + ': ' + responseText.slice(0, 500)
    );

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

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

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

function extractResponseOutputText_(response) {
  const output = response.output || [];

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

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

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

function validateAIResult_(result) {
  const categories = [
    'DIMENSIONAL', 'VISUAL', 'MATERIAL',
    'ASSEMBLY', 'DOCUMENTATION', 'OTHER'
  ];
  const priorities = ['LOW', 'MEDIUM', 'HIGH'];

  if (!result || categories.indexOf(result.category) === -1) {
    throw new Error('AI category is invalid.');
  }

  if (
    priorities.indexOf(result.suggested_priority) === -1 ||
    !Array.isArray(result.missing_information)
  ) {
    throw new Error('AI structured output is invalid.');
  }

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

function recordProcessingFailure_(record, error) {
  const retryCount = Number(record.Retry_Count || 0) + 1;
  const isValidation = Boolean(error.isValidationError);
  const manualReview =
    isValidation || retryCount >= CONFIG.MAX_RETRIES;

  updateRecordByKey_(
    'Inspections',
    'Inspection_ID',
    record.Inspection_ID,
    {
      Status: manualReview ? 'MANUAL_REVIEW' : record.Status,
      Automation_Status: manualReview ? 'MANUAL_REVIEW' : 'RETRY',
      Last_Automation_Run: new Date(),
      Retry_Count: retryCount,
      Error_Message: String(error.message).slice(0, 1000)
    }
  );

  logError_(
    record.Inspection_ID,
    'processPendingInspections',
    retryCount,
    error
  );

  if (manualReview && isValidEmail_(CONFIG.QUALITY_MANAGER_EMAIL)) {
    sendOnce_(
      'INSPECTION',
      record.Inspection_ID,
      'MANUAL_REVIEW_REQUIRED',
      CONFIG.QUALITY_MANAGER_EMAIL,
      'Inspection requires manual review',
      'Inspection ' + record.Inspection_ID +
        ' failed processing: ' + error.message
    );
  }
}

function sendOnce_(
  recordType,
  recordId,
  eventType,
  recipient,
  subject,
  body
) {
  if (hasAuditEvent_(recordType, recordId, eventType)) {
    return;
  }

  if (!isValidEmail_(recipient)) {
    throw validationError_(
      'Notification recipient is invalid for ' + eventType
    );
  }

  MailApp.sendEmail({
    to: recipient,
    subject: subject,
    body: body
  });

  logAudit_(
    recordType,
    recordId,
    eventType,
    'Apps Script',
    'Email sent to ' + recipient
  );
}

function hasAuditEvent_(recordType, recordId, eventType) {
  return listRecords_('Audit_Log').some(function(event) {
    return (
      String(event.Record_Type) === String(recordType) &&
      String(event.Record_ID) === String(recordId) &&
      String(event.Event_Type) === String(eventType)
    );
  });
}

function logAudit_(
  recordType,
  recordId,
  eventType,
  actor,
  details
) {
  appendRecord_('Audit_Log', {
    Event_ID: newId_('EVT'),
    Record_Type: recordType,
    Record_ID: recordId,
    Event_Type: eventType,
    Event_At: new Date(),
    Actor: actor,
    Details: details
  });
}

function logError_(
  inspectionId,
  functionName,
  retryCount,
  error
) {
  appendRecord_('Error_Log', {
    Error_ID: newId_('ERR'),
    Inspection_ID: inspectionId,
    Function: functionName,
    Error_At: new Date(),
    Retry_Count: retryCount,
    Message: String(error.message || error).slice(0, 1000),
    Stack: String(error.stack || '').slice(0, 5000),
    Resolved: false
  });
}

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

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

function updateRecordByKey_(
  sheetName,
  keyField,
  keyValue,
  changes
) {
  const record = findRecord_(sheetName, keyField, keyValue);
  if (!record) {
    throw new Error(
      'Record not found in ' + sheetName + ': ' + keyValue
    );
  }
  updateRow_(sheetName, record.__row, changes);
}

function updateRow_(sheetName, rowNumber, changes) {
  const sheet = getSheet_(sheetName);
  const headers = getHeaders_(sheet);
  const row = sheet.getRange(
    rowNumber,
    1,
    1,
    headers.length
  ).getValues()[0];

  Object.keys(changes).forEach(function(field) {
    const columnIndex = headers.indexOf(field);
    if (columnIndex === -1) {
      throw new Error(
        'Missing field ' + field + ' in ' + sheetName
      );
    }
    row[columnIndex] = changes[field];
  });

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

function findRecord_(sheetName, keyField, keyValue) {
  const records = listRecords_(sheetName);

  for (let index = 0; index < records.length; index += 1) {
    if (String(records[index][keyField]) === String(keyValue)) {
      return records[index];
    }
  }

  return null;
}

function listRecordsBy_(sheetName, field, value) {
  return listRecords_(sheetName).filter(function(record) {
    return String(record[field]) === String(value);
  });
}

function listRecords_(sheetName) {
  const sheet = getSheet_(sheetName);
  const lastRow = sheet.getLastRow();
  const lastColumn = sheet.getLastColumn();

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

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

  return rows.map(function(row, index) {
    const record = { __row: index + 2 };
    headers.forEach(function(header, columnIndex) {
      record[header] = row[columnIndex];
    });
    return record;
  });
}

function getHeaders_(sheet) {
  return sheet.getRange(
    1,
    1,
    1,
    sheet.getLastColumn()
  ).getDisplayValues()[0].map(function(header) {
    return String(header).trim();
  });
}

function getSheet_(sheetName) {
  const sheet = openSpreadsheet_().getSheetByName(sheetName);
  if (!sheet) {
    throw new Error('Missing sheet: ' + sheetName);
  }
  return sheet;
}

function openSpreadsheet_() {
  if (
    CONFIG.SPREADSHEET_ID &&
    CONFIG.SPREADSHEET_ID.indexOf('YOUR_') !== 0
  ) {
    return SpreadsheetApp.openById(CONFIG.SPREADSHEET_ID);
  }

  const active = SpreadsheetApp.getActiveSpreadsheet();
  if (!active) {
    throw new Error('Replace YOUR_SPREADSHEET_ID in CONFIG.');
  }
  return active;
}

function namedValue_(namedValues, labels) {
  for (let index = 0; index < labels.length; index += 1) {
    const value = namedValues[labels[index]];
    if (value !== undefined && value !== null) {
      return Array.isArray(value)
        ? value.join(', ').trim()
        : String(value).trim();
    }
  }
  return '';
}

function requiredNamedValue_(namedValues, labels) {
  const value = namedValue_(namedValues, labels);
  if (value === '') {
    throw validationError_(
      'Missing required Form value: ' + labels[0]
    );
  }
  return value;
}

function splitMultiValue_(value) {
  if (!value) {
    return [];
  }

  return String(value)
    .split(/\s*,\s*|\n+/)
    .map(function(item) {
      return item.trim();
    })
    .filter(function(item) {
      return item !== '';
    });
}

function toNumber_(value, fieldName) {
  if (
    value === '' ||
    value === null ||
    value === undefined
  ) {
    throw validationError_(
      'Missing numeric value for ' + fieldName
    );
  }

  const number = Number(value);
  if (!Number.isFinite(number)) {
    throw validationError_(
      'Invalid numeric value for ' + fieldName
    );
  }
  return number;
}

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

function formatDate_(value) {
  const date = toDate_(value);
  return date
    ? Utilities.formatDate(
        date,
        CONFIG.TIME_ZONE,
        'yyyy-MM-dd HH:mm:ss'
      )
    : '';
}

function daysBetween_(start, end) {
  const startDate = toDate_(start);
  const endDate = toDate_(end);
  if (!startDate || !endDate) {
    return 0;
  }
  return Math.floor(
    (endDate.getTime() - startDate.getTime()) / 86400000
  );
}

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

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

  return result;
}

function findBackupEmail_(email) {
  const user = findRecord_('Users', 'Email', email);
  if (
    user &&
    isTrue_(user.Active) &&
    isValidEmail_(user.Backup_Email)
  ) {
    return String(user.Backup_Email);
  }
  return '';
}

function isTrue_(value) {
  return value === true ||
    String(value).toUpperCase() === 'TRUE' ||
    String(value).toUpperCase() === 'YES';
}

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

function newId_(prefix) {
  return prefix + '-' +
    Utilities.getUuid().replace(/-/g, '').slice(0, 12).toUpperCase();
}

function sanitizeFileName_(value) {
  return String(value || '')
    .replace(/[\\/:*?"<>|#%{}]/g, '-')
    .slice(0, 150);
}

function requireConfiguredValue_(value, name) {
  if (!value || String(value).indexOf('YOUR_') === 0) {
    throw new Error('Replace the configuration value for ' + name);
  }
}

function validationError_(message) {
  const error = new Error(message);
  error.isValidationError = true;
  return error;
}

After pasting the code:

1. Replace YOUR_SPREADSHEET_ID, YOUR_FOLDER_ID, YOUR_APPSHEET_IMAGE_FOLDER_ID, and the approver email placeholders.
2. Set the correct time zone.
3. Run setupWorkbook manually.
4. Review the authorization request and confirm it matches the implemented services.
5. Populate Items and Users with test records.
6. Run installTriggers manually.
7. Submit a Google Form response and confirm that normalized rows appear.
8. Submit an AppSheet inspection and wait for the scheduled processor.
9. Inspect Apps Script Executions for logs and errors.

Duplicate Form execution is prevented with Source_Key. Duplicate defect, action, and report creation is prevented by looking up existing related records and file names. A script lock prevents overlapping processing runs.

Failed records store Retry_Count, Error_Message, and an Error_Log row. After three failed attempts, or immediately after a deterministic validation error, the record enters Manual Review. Authorized staff can correct it and set Automation_Status back to PENDING.

To enable the optional AI function, add OPENAI_API_KEYAI_ENABLED to true. Keep the key out of the workbook and source code.

Failure Handling and Operational Reliability

Error_Log functions as the dead-letter queue for this implementation. An unresolved row identifies the inspection, function, retry number, message, stack details, and resolution status.

Every weekday, the quality coordinator reviews Manual Review, open Error_Log rows, submitted inspections older than the trigger interval, and report links that remain blank. A monthly reconciliation compares inspection IDs with measurement, defect, action, approval, folder, and report records.

Idempotency is implemented at several levels: source-event keys, unique record IDs, related-record lookups, file-name checks, audit-event checks, and a script lock. Because Sheets does not provide a multi-table transaction, partial writes remain possible and are handled through reconciliation and manual recovery.

A Complete Example

An inspector uses AppSheet to inspect work order WO-18427 for item BRK-220, lot L0715-04. The app creates inspection ID INS-A1B2C3D4E5F6.

• Inspection type: FINAL
• Quantity inspected: 12
• Quantity affected: 12
• Inspector: an authenticated quality inspector
• Proposed disposition: REWORK
• Photo count: 2

The first measurement is Hole Diameter. The measured value is 10.04 mm, with an approved lower limit of 9.95 and upper limit of 10.05. The script records PASS.

The second measurement is Flange Width. The measured value is 24.82 mm, with an approved lower limit of 24.90 and upper limit of 25.10. The script records FAIL.

Surface condition passes. Assembly completeness passes. Label and marking fails because the lot mark is incomplete.

AppSheet writes the inspection, two measurement rows, three checklist rows, and two photo rows to Sheets. The Submit action changes Status to SUBMITTED and Automation_Status to PENDING.

During the next processing run, Apps Script evaluates the measurements and checks. It determines that the overall result is FAIL. It creates defect ID DEF-11AA22BB33CC and corrective-action ID ACT-44DD55EE66FF.

The item’s configured owner receives the corrective action to contain the lot and document rework. Because 12 units are affected, the high-risk approval threshold applies. The script creates an operations disposition approval at sequence 1 and a quality disposition approval at sequence 2.

A Drive folder named INS-A1B2C3D4E5F6 is created. The two available photos are copied into the folder, and the initial inspection PDF is generated. The returned PDF URL is stored in Document_Link.

The operations manager reviews the photographs and action plan in AppSheet and approves rework. The next scheduled scan changes the quality approval from Waiting to Requested. The quality manager approves the disposition.

The production lead records completion notes, adds rework evidence, and changes the corrective action to COMPLETED. The approval scanner confirms that all actions and approvals are complete, then changes the inspection to AWAITING_SIGNOFF.

The quality manager performs final review and uses the sign-off action. AppSheet records the approver email and timestamp, sets Status to CLOSED, and resets Automation_Status to PENDING. Apps Script validates the closure, creates the final PDF, stores Final_Report_Link, logs closure, and sends the final confirmation.

If the flange-width value had been blank instead of 24.82, the inspection would have entered Manual Review. No disposition would have been automatically approved, and quality staff would have corrected or confirmed the missing value before processing continued.

Implementation Cost

All amounts below are representative assumptions in US dollars. They are not verified client results or vendor quotations. Actual licensing, consulting, and internal labor costs must be confirmed for the organization.

A company with existing AppSheet licensing may have a lower incremental software cost, but it still incurs implementation, testing, administration, support, and maintenance effort.

Estimated Time and Cost Savings

The calculations use administrative handling time around the inspection. They do not assume that the physical measurement and inspection work disappears.

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

Calculation: 220 × 18 ÷ 60 = 66.00 hours

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

Calculation: 220 × 6 ÷ 60 + 220 × 10% × 8 ÷ 60 + 4 = 28.93 hours

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

Calculation: 66.00 − 28.93 = 37.07 hours

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

Calculation: 37.07 × $42 = $1,556.94

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

Calculation: $1,556.94 − $160 = $1,396.94

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

Calculation: $9,320 ÷ $1,396.94 = approximately 6.7 months

Recovered time does not automatically reduce payroll. It may provide additional inspection capacity, quicker turnaround, reduced overtime, less reconciliation, and lower dependency on the quality coordinator.

Non-financial benefits include clearer ownership, more consistent tolerance checks, fewer missing photographs, faster defect notification, improved auditability, more reliable status reporting, and a more consistent experience for inspectors and production leads.

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

Adding AI to the Automation

AI is optional and should be added only after the deterministic inspection workflow is stable. Required fields, exact tolerance comparisons, item lookups, quantities, due dates, permissions, and approval thresholds are better handled with validation and normal automation.

Potential AI applications include defect-description categorization, summarization, missing-information detection, semantic search, and grouping similar defect narratives for analysis.

The core automation already provides mobile capture, tolerance validation, defect creation, action assignment, reminders, reporting, and document generation. AI does not create those benefits. Its value is limited to interpreting unstructured text more consistently and reducing some reading and classification effort.

The Recommended AI Enhancement

The recommended enhancement structures a defect description after the deterministic defect record has been created. It suggests a category, concise summary, missing information, and priority for human review.

• Trigger: Defect AI_Status changes to PENDING.
• AI input: Defect ID, inspection type, item code, quantities, deterministic defect type, description, and proposed disposition.
• System instruction: The model may structure supplied facts but may not approve disposition, release, scrap, use-as-is, or corrective action.
• Expected output: Strict JSON matching the approved schema.
• Validation: Category, priority, missing-information values, and confidence range are checked in code.
• Record update: Suggestions are written only to AI-specific fields.
• Human review: A quality employee accepts, corrects, or ignores the suggestion.
• Low confidence: Values below the organization’s review threshold remain suggestions and should be highlighted.
• Prohibited data: Controlled drawings, personal information, customer-confidential content, export-controlled data, and other restricted content.
• Failure behavior: AI_Status becomes AI_ERROR while the normal quality workflow continues.

The reusable prompt is:

System instruction:

You assist a manufacturing quality team by structuring an existing defect description.
Do not approve disposition, product release, scrap, use-as-is, or corrective action.
Use only the supplied facts.
Do not infer an unseen condition.
Return JSON matching the supplied schema.
If information is missing, identify it explicitly.

User input:

Analyze the supplied defect record.
Choose the closest controlled category.
Create a factual summary of no more than 240 characters.
Identify missing information needed for human review.
Suggest a priority, but do not make a final decision.
Return only schema-compliant JSON.

Defect record:
{{DEFECT_RECORD_JSON}}

An expected result is:

{
  "category": "DIMENSIONAL",
  "summary": "Flange width measured below the approved lower limit on the inspected lot.",
  "missing_information": [
    "PHOTO"
  ],
  "suggested_priority": "MEDIUM",
  "confidence": 0.91,
  "rationale": "The supplied record contains an explicit out-of-tolerance dimensional value."
}

The complete API call, structured-output schema, retries, validation, logging, and record update are included in the Apps Script code. API usage should be monitored by request count, defect count, model, response size, error rate, and monthly cost.

Benefits of the AI Enhancement

• Reduces the time needed to read and summarize free-text defect descriptions.
• Suggests consistent categories across inspectors with different writing styles.
• Highlights missing location, quantity, measurement, photo, or lot information.
• Creates concise text that can improve defect queues and management reports.
• Supports later analysis of recurring themes in unstructured descriptions.

These benefits are specific to unstructured interpretation. The AI does not improve the mathematical accuracy of tolerance checks, enforce permissions, create reliable unique IDs, or replace approval controls.

What Remains Rule-Based or Human-Controlled

Estimating the Additional Value of AI

Assume 22 defect records per month, which corresponds to the representative 10 percent exception rate.

The AI estimate assumes 75 percent of suggestions require two minutes of review, 20 percent require five minutes of correction, and 5 percent fail and fall back to the five-minute non-AI process.

Additional monthly capacity: 1.83 − 1.01 = approximately 0.82 hours

Additional labor value: 0.82 × $42 = approximately $34.44 per month

Less representative AI allowance: $34.44 − $10 = approximately $24.44 net additional monthly value

This modest direct value means the stronger justification may be more consistent categorization and clearer reporting rather than labor savings alone. AI does not eliminate correction, review, or service failures.

Testing Checklist

Use sample items, users, photographs, and work orders before processing real quality information.

Ongoing Maintenance

The quality coordinator is the primary system owner. A technically capable operations employee is the backup owner. The Workspace administrator controls account recovery and organization-wide access.

Documentation must include table definitions, AppSheet expressions, script configuration, trigger ownership, folder IDs, approval rules, test evidence, known limitations, and manual-recovery procedures.

Credentials and authorizations should be rotated or renewed according to company policy. Former users must be removed from AppSheet access, Drive permissions, Workspace groups, approval assignments, and active master data.

When to Move to Dedicated Software

This implementation should not be replaced merely because it uses low-code and productivity tools. It remains appropriate while its volume, risk, maintenance, and governance are understood and controlled.

Indicators that a dedicated quality management system or custom application should be evaluated include:

• Transaction volume causes slow synchronization or long script runs.
• Inspection templates require complex revision control and electronic signatures.
• Multiple sites need tightly separated data, permissions, and administration.
• Formal regulatory requirements exceed the available audit and validation controls.
• Supplier quality, calibration, training, audits, CAPA, and document control must operate in one governed system.
• ERP, manufacturing execution, laboratory, or equipment integrations become extensive.
• Offline inspections require more reliable conflict resolution and device management.
• Customers or suppliers need a secure external portal.
• Spreadsheet maintenance and reconciliation consume excessive administrator time.
• Exception rates indicate that the workflow no longer matches operational reality.
• Advanced analytics require a transactional database and formal data warehouse.
• Vendor support, service-level commitments, and formal validation documentation become mandatory.
• Security risk increases because too many users, files, and integrations depend on one automation owner.

Possible next steps include a commercial quality management system, an ERP quality module, an AppSheet database or cloud database backend, or a custom inspection application. The appropriate choice depends on requirements rather than volume alone.

Implementation Checklist

• Confirm inspection types, volumes, users, and business requirements.
• Define pass, fail, defect, disposition, action, approval, and sign-off rules.
• Select Google Forms, AppSheet, Sheets, Apps Script, and Drive roles.
• Create the automation-owner account and backup owner.
• Create restricted folders and the system-of-record workbook.
• Configure users, roles, backups, items, specifications, and owners.
• Build and validate the Google Form.
• Build AppSheet parent and child forms.
• Configure keys, references, valid values, and read-only fields.
• Confirm field mapping for both intake paths.
• Install and authorize the Apps Script project.
• Configure form-submit, processing, reminder, and optional AI triggers.
• Test unique IDs and duplicate prevention.
• Test tolerance calculations and checklist results.
• Test defect and corrective-action creation.
• Test sequential approvals, rejection, reassignment, and sign-off.
• Test reminders, escalation, and unavailable approvers.
• Test photo storage, report creation, version handling, and access.
• Create operational and management reporting views.
• Review least-privilege permissions and shared-link restrictions.
• Test retries, Error_Log, Manual Review, and recovery.
• Complete user acceptance testing with sample data.
• Pilot one inspection process before broader deployment.
• Document implementation, support, rollback, and maintenance procedures.
• Replace representative cost and savings assumptions with measured figures.
• Enable AI only after governance, data restrictions, and human review are approved.
• Assign primary and backup maintenance owners.
• Define the volume, compliance, security, and complexity thresholds for evaluating dedicated software.