Automating Supplier Shortage and Damage Claims

The Business Situation

Larkspur Equipment Works is a fictional 82-person manufacturer of commercial material-handling equipment. The company receives components and fabricated parts from approximately 60 active suppliers.

The supplier claims process involves three receiving employees, two buyers, a procurement manager, a quality coordinator, and an operations director who reviews unusually high-value claims. A procurement administrator maintains the current spreadsheet and shared procurement inbox.

The business processes an average of 48 supplier claims per month. The typical mix is approximately 24 short shipments, 14 damaged-goods claims, eight price discrepancies, and two other receiving exceptions.

Receiving employees currently report problems by email. Photos may be attached to the email, uploaded to an individual Google Drive folder, or sent later through a messaging application. A buyer copies selected details into a spreadsheet and contacts the supplier. Follow-up dates are tracked through personal calendars and inbox flags.

This process makes it difficult to determine whether every claim has sufficient evidence, whether the supplier was contacted, who owns the next action, and whether a credit or replacement was ultimately received. The business needs a controlled workflow without purchasing a full supplier quality management platform before its requirements and transaction volume justify one.

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

The Existing Process

The original process follows this sequence:

1. A receiving employee identifies a shortage, damaged item, or price discrepancy.
2. The employee photographs the shipment and sends an email to procurement.
3. A buyer searches for the purchase order, receipt, supplier contact, quantity, and expected unit value.
4. The buyer creates or updates a row in a shared spreadsheet.
5. If the claim appears significant, the buyer forwards the email to a manager for approval.
6. The buyer writes a separate supplier email and attaches the evidence that can be located.
7. The buyer creates a personal reminder to follow up with the supplier.
8. When the supplier replies, the buyer updates the spreadsheet if time permits.
9. The claim is marked complete after a credit memo or replacement arrives, although supporting evidence is not always linked to the row.

A spreadsheet alone does not solve these problems because the weak points occur before and after the row is created. The business needs controlled intake, consistent evidence storage, approval routing, supplier notifications, reminders, resolution capture, and reporting around the spreadsheet.

What the New System Needed to Do

Implementation Approaches Considered

Improved Email and Spreadsheet Procedures

This option could standardize email subjects, spreadsheet columns, and folder names. It would be inexpensive to begin, but reminders, duplicate prevention, approvals, and evidence reconciliation would remain manual. It would reduce variation without creating a controlled process.

No-Code Database and Automation Platform

A no-code database could offer stronger record views and easier visual workflow design. It would be appropriate if Larkspur Equipment Works needed several external integrations or a supplier-facing portal. For the current scope, it would duplicate capabilities already available in the company’s Google Workspace environment.

Dedicated Supplier Quality Software

Dedicated software becomes more attractive when supplier corrective actions, formal nonconformance records, ERP integration, supplier scorecards, and regulatory controls must operate in one platform. The representative business does not yet have that level of complexity.

Selected Approach

The Google Workspace approach was selected because the company already uses Google Forms, Sheets, Drive, and Gmail. Apps Script supplies the transaction control, folder creation, document generation, approvals, reminders, and error handling that the native applications do not provide by themselves.

The Selected Solution

The existing Google Workspace environment was retained. The shared inbox continued to receive supplier replies, while Google Sheets remained the operational system of record.

Manual folder creation, spreadsheet copying, approval forwarding, standard supplier-email drafting, deadline reminders, and routine status updates were removed. Employees still decide whether the claim is valid, whether a supplier should be held responsible, whether a high-value claim should be sent, and whether a credit or replacement is acceptable.

System Architecture and Data Flow

• Intake: A signed-in employee submits the supplier claim form with structured fields and evidence files.
• System of record: The Claims sheet stores one row per claim and the ActivityLog sheet stores append-only events.
• Automation layer: An installable Apps Script form-submit trigger processes intake and resolution events. A daily time trigger runs reminders and retries.
• Document storage: Google Drive stores claim, evidence, and correspondence folders. Apps Script creates Google Docs and PDF summaries.
• Notifications: MailApp sends internal approval requests and external supplier claim notices.
• Reporting: Google Sheets filter views, formulas, pivot tables, and charts read from the Claims sheet.
• AI layer: Optional structured-output analysis reviews selected claim text without making approval or liability decisions.

1. Submission: Google Forms writes a response into the Intake Responses sheet. The spreadsheet form-submit trigger receives the source sheet and row number.
2. Idempotency check: Apps Script builds a source event key from the spreadsheet ID, sheet name, and response row. If that key already exists, processing stops without creating another claim.
3. Validation: The script validates email addresses, claim type, required references, values, dates, quantities, requested resolution, and damage evidence.
4. Record creation: The script generates an ID such as CLM-2026-00041 and appends a Claims row. Invalid submissions are retained with a Needs Information status rather than silently discarded.
5. Duplicate review: A business duplicate key is calculated from supplier, purchase order, receipt, claim type, and item. An existing open match sends the new claim to Manual Review.
6. File organization: Apps Script creates year, claim, Evidence, and Correspondence folders. Uploaded files are renamed with the claim ID and moved into the evidence folder.
7. Approval routing: Damage claims go to the quality coordinator. Shortage, price, and other claims go to the procurement manager. Claims worth at least $2,500 require the operations director after the first approval.
8. Supplier notice: After final approval, Apps Script generates an approved PDF, attaches eligible evidence, sends the notice to the supplier, stores the notification timestamp, and changes the status to Awaiting Supplier.
9. Monitoring: A daily trigger reminds approvers, warns owners about approaching deadlines, marks late records Supplier Overdue, and escalates prolonged delays.
10. Resolution: An authorized owner submits the Resolution Form. Apps Script validates the update, stores the supplier reference, credit or replacement evidence, generates a closure document, and records the final status.
11. Failure path: A failed script action sets Automation Status to Failed, increments Retry Count, writes the error to ErrorQueue, and notifies the automation administrator. The daily job retries eligible records up to the configured limit.

Data Structure

Claims Sheet

Supporting Sheets

Workflow Statuses and Ownership

A record can move backward when an approver returns it for information, when a supplier response requires clarification, or when an administrator identifies incorrect data. Closure and rejection are not automatically reversed. Reopening requires an authorized employee to document the reason and resubmit the record.

Step-by-Step Implementation

Step 1: Prepare the Accounts and Permissions

1. Create or designate an organizational Google Workspace account that owns the forms, spreadsheet, Apps Script project, and Drive root folder. Avoid ownership by an individual buyer.
2. Confirm that the account can use Google Forms file uploads, Apps Script installable triggers, web app deployments, Drive, Docs, and Gmail sending. Required features and quotas depend on the organization’s Workspace agreement.
3. Create a Google Group or shared operational address such as supplierclaims@YOUR_DOMAIN. Use it as the reply-to address and operational notification recipient.
4. Create the internal Drive root folder named Supplier Claims. Share it only with procurement, receiving management, quality, and the automation owner.
5. Create two Forms owned by the automation account: Supplier Claim Intake and Supplier Claim Resolution.
6. Link both forms to one Google Sheets workbook. Rename the response tabs exactly Intake Responses and Resolution Responses, or change the corresponding constants in the script.
7. Use organizational sign-in and verified email collection for both forms. Restrict the Resolution Form to authorized employees.
8. Create test identities for a receiving submitter, buyer, quality approver, procurement approver, operations approver, and unauthorized employee.
9. Use a separate test workbook and test Drive folder until user acceptance testing is complete. Do not point the test script at the production folder.

The Apps Script project needs permission to read and update the spreadsheet, create and move Drive files, create Google Docs, send email, make external requests if AI is enabled, and run unattended triggers. The approval web app must be restricted to the organization and must reject users whose identity cannot be verified.

Step 2: Build the Intake

Configure the Supplier Claim Intake Form with the following fields. Exact Google Forms interface labels can change, but the question titles must match the script or the constants and mappings must be adjusted.

The confirmation message should state that submission does not mean the supplier claim has been approved. It should tell the employee that the generated claim ID will be emailed after processing.

Add a brief privacy notice stating that uploads must contain only shipment, product, invoice, and receiving evidence required for the claim. Employees should not upload unrelated personal, payment-card, health, or employee-relations data.

The Resolution Form should collect:

• Claim ID
• Action
• Supplier Reference
• Resolution Summary
• Credit Memo Number
• Replacement Tracking
• Resolved Value
• New Response Deadline
• Resolution Attachment Files

The Action field uses the controlled values Record Supplier Response, Close with Credit, Close with Replacement, Extend Supplier Deadline, and Withdraw Claim.

Google Forms blocks blank required responses, while Apps Script performs cross-field validation. For example, it can require evidence for a damaged-goods claim and a credit memo number only when the claim closes with credit.

Step 3: Create the System of Record

Open the linked Google Sheets workbook and use the Apps Script setup function to create the Claims, SupplierRouting, ActivityLog, and ErrorQueue sheets. The script writes fixed headers and creates the installable triggers.

Populate SupplierRouting with these columns:

The supplier name comparison is case-insensitive and trims extra spaces. If no active match exists, the procurement manager becomes the owner.

Protect the Claims sheet so normal users cannot edit system fields. Give the automation owner, procurement manager, and designated administrator edit access. Buyers should use the Resolution Form rather than editing rows directly.

Create filter views for each operational queue. Protect ActivityLog and ErrorQueue from row deletion. If corrections are permitted, require an explanatory note and write a corresponding ActivityLog event.

Step 4: Connect the Tools

The returned Drive folder IDs, file IDs, URLs, supplier reference, and approval result are written back to the Claims row. This prevents later steps from searching by file name or email subject.

Step 5: Build the Core Automation

Automation 1: Intake Processing

• Trigger: A new row is submitted to Intake Responses.
• Conditions: The source event key has not already been processed.
• Actions: Generate the claim ID, validate data, check duplicates, assign owner, append the claim, create folders, move evidence, generate an intake PDF, and route approval.
• Fields updated: Claim ID, dates, owner, status, priority, folder fields, document fields, duplicate fields, and automation fields.
• Notification: Send the submitter and owner the new claim ID, or send a needs-information notice.
• Exception: Mark the record Failed or Manual Review and write an ErrorQueue or ActivityLog event.

Automation 2: Approval Processing

• Trigger: An approver opens the web app and submits a decision.
• Conditions: The claim is pending, the token is valid, and the signed-in user is the current or delegated approver.
• Actions: Record the decision, route a second approval if required, or generate and send the approved supplier claim.
• Fields updated: Approval stage, status, approver, approval date, notes, token fields, supplier notification date, and document fields.
• Notification: Notify the supplier only after final approval. Notify internal users on return or rejection.
• Exception: Deny unauthorized access and retain approval status if document or email delivery fails.

Automation 3: Resolution Processing

• Trigger: An authorized employee submits the Resolution Form.
• Conditions: The Claim ID exists, the updater is permitted, the source event is new, and action-specific fields are present.
• Actions: Update the supplier response, deadline, credit, replacement, withdrawal, or closure fields; move attachments; generate a closure PDF; and log the event.
• Fields updated: Supplier response, resolution type, supplier reference, resolved value, closed date, status, and automation fields.
• Notification: Confirm the update to the owner, submitter, and shared claims address.
• Exception: Reject the update and notify the automation administrator without changing the controlled record.

The Source Event Key prevents a repeated trigger delivery from creating another claim. A separate Duplicate Key identifies likely business duplicates. The latter does not automatically delete or merge anything because two valid line-item claims can share a purchase order and receipt.

Step 6: Add Approvals, Reminders, and Escalations

Use these approval rules:

1. Damaged Goods claims route first to the quality coordinator.
2. Short Shipment, Price Discrepancy, and Other claims route first to the procurement manager.
3. Any claim with an estimated value of at least $2,500 routes to the operations director after the first approval.
4. The supplier receives nothing until all required approval stages are complete.
5. Rejection and Return for Information require a comment.
6. An unavailable approver can be handled by entering an approved delegate in the Delegated Approver Email field. This change should be logged.

The web app uses a two-step interaction. The email link opens a review page, while the decision is committed only after the approver submits the page. This avoids treating automated link scanning as approval.

The daily monitor sends an approval reminder after two days. At three days, it copies the configured backup approver or administrator. The backup does not gain authority merely by receiving the email. An authorized administrator must record delegation or reassignment.

Supplier deadline warnings begin two days before the response deadline. The first missed deadline changes the status to Supplier Overdue. Additional reminders are sent every three days, with the procurement manager copied after escalation.

Approval evidence consists of the claim ID, stage, expected approver, actual signed-in actor, decision, timestamp, comment, and ActivityLog event. Raw approval tokens are never stored.

Step 7: Add Documents and File Management

The Drive structure is:

Supplier Claims
  2026
    CLM-2026-00041 - Supplier Name - PO-10482
      Evidence
      Correspondence
      CLM-2026-00041 - Intake.pdf
      CLM-2026-00041 - Approved.pdf
      CLM-2026-00041 - Closure.pdf

Apps Script creates each folder only once. It first checks stored folder IDs and then checks for an exact folder name before creating a replacement. Uploaded evidence is renamed with the claim ID while retaining the original file name.

The folder remains internal. Suppliers receive approved attachments rather than a Drive folder link. If the permitted attachments exceed the configured email-size threshold, the supplier receives the claim PDF and the owner receives an exception notice explaining that evidence must be sent through an approved transfer method.

Google Drive version history applies to Google Docs, but generated PDFs are intentionally retained as dated workflow evidence. A later approved or closure PDF does not overwrite the intake PDF.

Access follows the root folder. Individual claim folders should not be shared publicly. Retention should follow the organization’s procurement, tax, quality, and legal policies. Closed claims can be moved to an archive area by year after the active retention period.

Step 8: Add Reporting and Operational Views

Create Sheets filter views for:

• New and pending approval claims
• Needs Information
• Manual Review
• Awaiting Supplier
• Supplier Overdue
• Supplier Responded
• Rejected claims
• Open claims by owner
• Deadlines within seven days
• Claims closed in the last 30 days
• Automation Status equal to Failed
• Retry Count greater than or equal to three
• AI Review Status equal to Pending Human Review

A Summary sheet can calculate operational measures with formulas that locate columns by header instead of relying on fixed column letters.

Pending approvals:
=COUNTIF(INDEX(Claims!A:ZZ,0,MATCH("Status",Claims!1:1,0)),"Pending Approval")

Supplier overdue:
=COUNTIF(INDEX(Claims!A:ZZ,0,MATCH("Status",Claims!1:1,0)),"Supplier Overdue")

Automation failures:
=COUNTIF(INDEX(Claims!A:ZZ,0,MATCH("Automation Status",Claims!1:1,0)),"Failed")

Average closed-cycle hours:
=IFERROR(AVERAGE(FILTER(
 (INDEX(Claims!A:ZZ,0,MATCH("Closed Date",Claims!1:1,0))
 -INDEX(Claims!A:ZZ,0,MATCH("Created Date",Claims!1:1,0)))*24,
 INDEX(Claims!A:ZZ,0,MATCH("Closed Date",Claims!1:1,0))<>""
)),0)

Create pivot tables for claim count and value by supplier, claim type, owner, status, and month. Use a calculated claim age based on Created Date for open items and Closed Date for completed items.

The procurement manager owns the dashboard definitions. The automation administrator owns data refresh and formula integrity. Since the dashboard reads directly from Sheets, no separate refresh job is needed, although pivot-table refresh behavior should be tested in the organization’s environment.

Step 9: Add Security and Governance Controls

• Use an organizational automation account rather than a personal account.
• Restrict both forms to appropriate signed-in users.
• Restrict the resolution form more tightly than the intake form.
• Protect system fields and log authorized manual corrections.
• Limit Drive access to procurement, quality, receiving management, and named administrators.
• Do not use publicly accessible Drive links for supplier evidence.
• Store API credentials in Apps Script Properties, not spreadsheet cells or source code.
• Restrict the approval web app to the organization.
• Reject approval requests when the signed-in identity is blank or does not match the approver.
• Remove former employees from Google Groups, Drive access, form access, and routing tables promptly.
• Review Apps Script executions, ActivityLog, ErrorQueue, Gmail sending, and Drive activity.
• Apply the organization’s records-retention and legal-hold requirements before deleting claims.
• Exclude payment-card data, banking credentials, employee medical information, and unrelated personal information.
• If AI is enabled, send only approved fields and redact email addresses and phone-like strings from narrative text.
• Keep supplier liability, financial recovery, approval, and closure decisions under human control.

Step 10: Deploy and Test

1. Build the forms, test spreadsheet, test Drive root folder, and script in a non-production environment.
2. Run setupWorkbook() to create system sheets and install triggers.
3. Deploy the approval application as a web app restricted to the organization. Test whether the signed-in approver email is available in the selected deployment configuration.
4. Copy the deployed web app URL into SETTINGS.WEB_APP_URL, save, and create a new deployment version.
5. Submit sample records for all claim types, approval stages, and resolution actions.
6. Inspect the Apps Script Executions page, Claims sheet, ActivityLog, ErrorQueue, Drive folders, generated PDFs, and delivered emails.
7. Conduct user acceptance testing with receiving, procurement, quality, and operations representatives.
8. Pilot with one buyer and a limited supplier set for two weeks or an equivalent representative volume.
9. Reconcile every pilot form response to a Claims row and every approved claim to its supplier email.
10. Document the rollback procedure. The forms can be closed, triggers disabled, and the prior email process temporarily restored without deleting existing claim records.
11. Move configuration IDs and addresses to production values, reinstall production triggers, and activate the production forms.
12. Send a launch communication that explains the new forms, required evidence, status meanings, owner responsibilities, and support contact.

Code and Configuration

The implementation requires Apps Script because native Google Forms, Sheets, and Drive actions do not provide controlled IDs, dynamic folder creation, cross-field validation, staged approvals, supplier notices, scheduled escalation, and idempotent recovery as one workflow.

Create the script as a bound Apps Script project inside the claims workbook. From the spreadsheet, open Extensions and then Apps Script. Paste the following into Core.gs. Replace every configuration placeholder before production use.

The script uses an installable spreadsheet form-submit trigger and a daily time-driven trigger. Google provides official references for installable triggers and web apps.

const SETTINGS = Object.freeze({
  SPREADSHEET_ID: 'YOUR_SPREADSHEET_ID',
  ROOT_FOLDER_ID: 'YOUR_FOLDER_ID',
  WEB_APP_URL: 'YOUR_WEB_APP_URL',

  INTAKE_SHEET: 'Intake Responses',
  RESOLUTION_SHEET: 'Resolution Responses',
  COLLECTED_EMAIL_HEADER: 'Email Address',

  PROCUREMENT_MANAGER_EMAIL: 'YOUR_PROCUREMENT_MANAGER_EMAIL',
  QUALITY_COORDINATOR_EMAIL: 'YOUR_QUALITY_COORDINATOR_EMAIL',
  OPERATIONS_DIRECTOR_EMAIL: 'YOUR_OPERATIONS_DIRECTOR_EMAIL',
  APPROVAL_BACKUP_EMAIL: 'YOUR_APPROVAL_BACKUP_EMAIL',
  AUTOMATION_ADMIN_EMAIL: 'YOUR_AUTOMATION_ADMIN_EMAIL',
  SHARED_CLAIMS_EMAIL: 'YOUR_EMAIL_ADDRESS',

  HIGH_VALUE_THRESHOLD: 2500,
  APPROVAL_REMINDER_DAYS: 2,
  APPROVAL_ESCALATION_DAYS: 3,
  APPROVAL_TOKEN_HOURS: 168,
  MAX_AUTOMATIC_RETRIES: 3,
  MAX_EMAIL_ATTACHMENT_BYTES: 15 * 1024 * 1024,

  AI_ENABLED: false,
  AI_ENDPOINT: 'https://api.openai.com/v1/responses',
  AI_MODEL: 'YOUR_MODEL_NAME'
});

const CLAIM_HEADERS = [
  'Claim ID',
  'Created Date',
  'Last Updated',
  'Source Event Key',
  'Source Sheet',
  'Source Row',
  'Last Resolution Event Key',
  'Submitter Email',
  'Claim Type',
  'Supplier Name',
  'Supplier Contact Email',
  'PO Number',
  'Receipt Number',
  'Receipt Date',
  'SKU',
  'Quantity Ordered',
  'Quantity Received',
  'Quantity Affected',
  'Unit Value',
  'Claim Value',
  'Currency',
  'Description',
  'Requested Resolution',
  'Response Deadline',
  'Owner Email',
  'Status',
  'Priority',
  'Approval Stage',
  'Approval Status',
  'Current Approver Email',
  'Delegated Approver Email',
  'Approval Token Hash',
  'Approval Token Expires',
  'Approved By',
  'Approval Date',
  'Supplier Notified Date',
  'Supplier Response Summary',
  'Resolution Type',
  'Credit Memo Number',
  'Replacement Tracking',
  'Resolved Value',
  'Supplier Reference',
  'Closed Date',
  'Claim Folder ID',
  'Claim Folder URL',
  'Evidence Folder ID',
  'Evidence Folder URL',
  'Correspondence Folder ID',
  'Claim Document URL',
  'Claim PDF File ID',
  'Evidence File IDs',
  'External System ID',
  'Automation Status',
  'Last Automation Run',
  'Last Reminder Date',
  'Escalation Level',
  'Retry Count',
  'Error Message',
  'Duplicate Key',
  'Potential Duplicate Of',
  'Notes',
  'AI Summary',
  'AI Suggested Category',
  'AI Missing Information',
  'AI Confidence',
  'AI Review Status',
  'AI Last Run',
  'AI Error'
];

const ACTIVITY_HEADERS = [
  'Timestamp',
  'Claim ID',
  'Action',
  'Actor',
  'Details',
  'Source Event Key'
];

const ERROR_HEADERS = [
  'Timestamp',
  'Claim ID',
  'Source',
  'Error',
  'Stack',
  'Resolved',
  'Resolution Notes'
];

const ROUTING_HEADERS = [
  'Supplier Name',
  'Owner Email',
  'Backup Email',
  'Active'
];

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Supplier Claims')
    .addItem('Retry selected claim', 'retrySelectedClaim')
    .addItem('Resubmit selected claim', 'resubmitSelectedClaim')
    .addItem('Run AI review for selected claim', 'runAiReviewForSelectedClaim')
    .addSeparator()
    .addItem('Run daily monitor now', 'runDailyMonitor')
    .addToUi();
}

function setupWorkbook() {
  validateConfiguration_(false);

  const ss = SpreadsheetApp.openById(SETTINGS.SPREADSHEET_ID);
  ensureSheet_(ss, 'Claims', CLAIM_HEADERS);
  ensureSheet_(ss, 'ActivityLog', ACTIVITY_HEADERS);
  ensureSheet_(ss, 'ErrorQueue', ERROR_HEADERS);
  ensureSheet_(ss, 'SupplierRouting', ROUTING_HEADERS);

  if (!ss.getSheetByName(SETTINGS.INTAKE_SHEET)) {
    throw new Error('Missing intake response sheet: ' + SETTINGS.INTAKE_SHEET);
  }

  if (!ss.getSheetByName(SETTINGS.RESOLUTION_SHEET)) {
    throw new Error('Missing resolution response sheet: ' + SETTINGS.RESOLUTION_SHEET);
  }

  installTriggers_(ss);
  ss.toast('Supplier claim sheets and triggers are ready.');
}

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

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

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

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

  sheet.setFrozenRows(1);
  return sheet;
}

function installTriggers_(ss) {
  ScriptApp.getProjectTriggers().forEach(function(trigger) {
    const handler = trigger.getHandlerFunction();

    if (handler === 'handleFormSubmit' || handler === 'runDailyMonitor') {
      ScriptApp.deleteTrigger(trigger);
    }
  });

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

  ScriptApp.newTrigger('runDailyMonitor')
    .timeBased()
    .everyDays(1)
    .atHour(8)
    .create();
}

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

  const sourceSheet = e.range.getSheet().getName();

  try {
    if (sourceSheet === SETTINGS.INTAKE_SHEET) {
      processIntake_(e);
      return;
    }

    if (sourceSheet === SETTINGS.RESOLUTION_SHEET) {
      processResolution_(e);
      return;
    }

    logError_('', 'Unknown form source', new Error('Unexpected sheet: ' + sourceSheet));
  } catch (error) {
    logError_('', 'Form trigger: ' + sourceSheet, error);
    notifyAdmin_('Supplier claim form trigger failed', error.message);
    throw error;
  }
}

function processIntake_(e) {
  validateConfiguration_(true);

  const submitted = getSubmittedRecord_(e);
  const sourceKey = buildSourceEventKey_(e);
  const lock = LockService.getScriptLock();
  let claimId = '';

  lock.waitLock(30000);

  try {
    const existing = findClaimByField_('Source Event Key', sourceKey);

    if (existing) {
      return existing.object['Claim ID'];
    }

    claimId = generateClaimId_();

    const evidenceIds = extractDriveFileIds_(submitted['Evidence Files']);
    const created = new Date();
    const claimType = cleanString_(submitted['Claim Type']);
    const claimValue = toNumber_(submitted['Estimated Claim Value']);
    const responseDeadline = toDate_(submitted['Supplier Response Deadline']);
    const owner = findOwner_(submitted['Supplier Name']);
    const duplicateKey = buildDuplicateKey_(submitted);
    const potentialDuplicate = findOpenDuplicate_(duplicateKey);
    const validationErrors = validateIntake_(submitted, evidenceIds);

    const priority = (
      claimValue >= SETTINGS.HIGH_VALUE_THRESHOLD ||
      daysUntil_(responseDeadline) <= 2
    ) ? 'High' : 'Normal';

    let status = 'Pending Approval';
    let automationStatus = 'Processing';

    if (validationErrors.length > 0) {
      status = 'Needs Information';
      automationStatus = 'Validation Failed';
    } else if (potentialDuplicate) {
      status = 'Manual Review';
    }

    const claim = {
      'Claim ID': claimId,
      'Created Date': created,
      'Last Updated': created,
      'Source Event Key': sourceKey,
      'Source Sheet': e.range.getSheet().getName(),
      'Source Row': e.range.getRow(),
      'Last Resolution Event Key': '',
      'Submitter Email': cleanString_(submitted[SETTINGS.COLLECTED_EMAIL_HEADER]),
      'Claim Type': claimType,
      'Supplier Name': cleanString_(submitted['Supplier Name']),
      'Supplier Contact Email': cleanString_(submitted['Supplier Contact Email']),
      'PO Number': cleanString_(submitted['Purchase Order Number']),
      'Receipt Number': cleanString_(submitted['Receipt Number']),
      'Receipt Date': toDate_(submitted['Receipt Date']),
      'SKU': cleanString_(submitted['Item SKU or Description']),
      'Quantity Ordered': toNumberOrBlank_(submitted['Quantity Ordered']),
      'Quantity Received': toNumberOrBlank_(submitted['Quantity Received']),
      'Quantity Affected': toNumber_(submitted['Quantity Affected']),
      'Unit Value': toNumber_(submitted['Unit Value']),
      'Claim Value': claimValue,
      'Currency': cleanString_(submitted['Currency']),
      'Description': cleanString_(submitted['Claim Details']),
      'Requested Resolution': cleanString_(submitted['Requested Resolution']),
      'Response Deadline': responseDeadline,
      'Owner Email': owner,
      'Status': status,
      'Priority': priority,
      'Approval Stage': '',
      'Approval Status': 'Not Started',
      'Current Approver Email': '',
      'Delegated Approver Email': '',
      'Approval Token Hash': '',
      'Approval Token Expires': '',
      'Approved By': '',
      'Approval Date': '',
      'Supplier Notified Date': '',
      'Supplier Response Summary': '',
      'Resolution Type': '',
      'Credit Memo Number': '',
      'Replacement Tracking': '',
      'Resolved Value': '',
      'Supplier Reference': '',
      'Closed Date': '',
      'Claim Folder ID': '',
      'Claim Folder URL': '',
      'Evidence Folder ID': '',
      'Evidence Folder URL': '',
      'Correspondence Folder ID': '',
      'Claim Document URL': '',
      'Claim PDF File ID': '',
      'Evidence File IDs': evidenceIds.join(','),
      'External System ID': '',
      'Automation Status': automationStatus,
      'Last Automation Run': created,
      'Last Reminder Date': '',
      'Escalation Level': 0,
      'Retry Count': 0,
      'Error Message': validationErrors.join(' | '),
      'Duplicate Key': duplicateKey,
      'Potential Duplicate Of': potentialDuplicate || '',
      'Notes': '',
      'AI Summary': '',
      'AI Suggested Category': '',
      'AI Missing Information': '',
      'AI Confidence': '',
      'AI Review Status': SETTINGS.AI_ENABLED ? 'Not Started' : 'Disabled',
      'AI Last Run': '',
      'AI Error': ''
    };

    appendClaim_(claim);
    logActivity_(claimId, 'Claim created', claim['Submitter Email'], status, sourceKey);
  } finally {
    lock.releaseLock();
  }

  try {
    ensureClaimFolders_(claimId);
    moveIntakeEvidence_(claimId);
    createClaimDocument_(claimId, 'Intake');

    const current = findClaim_(claimId).object;

    if (current['Status'] === 'Needs Information') {
      notifyNeedsInformation_(current);
    } else if (current['Status'] === 'Manual Review') {
      notifyPotentialDuplicate_(current);
    } else {
      routeInitialApproval_(claimId);
    }

    updateClaim_(claimId, {
      'Automation Status': current['Status'] === 'Needs Information'
        ? 'Validation Failed'
        : 'Completed',
      'Last Automation Run': new Date(),
      'Error Message': current['Status'] === 'Needs Information'
        ? current['Error Message']
        : ''
    });

    if (SETTINGS.AI_ENABLED) {
      try {
        runAiReview_(claimId);
      } catch (aiError) {
        updateClaim_(claimId, {
          'AI Review Status': 'Failed',
          'AI Last Run': new Date(),
          'AI Error': aiError.message
        });
        logError_(claimId, 'Optional AI review', aiError);
      }
    }

    return claimId;
  } catch (error) {
    markClaimFailure_(claimId, 'Intake automation', error);
    throw error;
  }
}

function processResolution_(e) {
  const submitted = getSubmittedRecord_(e);
  const sourceKey = buildSourceEventKey_(e);
  const claimId = cleanString_(submitted['Claim ID']);
  const actor = cleanString_(submitted[SETTINGS.COLLECTED_EMAIL_HEADER]).toLowerCase();
  const action = cleanString_(submitted['Action']);

  if (!claimId) {
    throw new Error('Resolution submission is missing Claim ID.');
  }

  const found = findClaim_(claimId);

  if (!found) {
    throw new Error('Claim not found: ' + claimId);
  }

  if (found.object['Last Resolution Event Key'] === sourceKey) {
    return claimId;
  }

  if (!isAuthorizedUpdater_(found.object, actor)) {
    throw new Error('The signed-in user is not authorized to update ' + claimId + '.');
  }

  const validationErrors = validateResolution_(submitted, action);

  if (validationErrors.length > 0) {
    throw new Error(validationErrors.join(' | '));
  }

  const now = new Date();
  const updates = {
    'Last Updated': now,
    'Last Resolution Event Key': sourceKey,
    'Supplier Reference': cleanString_(submitted['Supplier Reference']),
    'Supplier Response Summary': cleanString_(submitted['Resolution Summary']),
    'Automation Status': 'Processing',
    'Last Automation Run': now,
    'Error Message': ''
  };

  if (action === 'Record Supplier Response') {
    updates['Status'] = 'Supplier Responded';
  } else if (action === 'Close with Credit') {
    updates['Status'] = 'Closed - Credit';
    updates['Resolution Type'] = 'Credit';
    updates['Credit Memo Number'] = cleanString_(submitted['Credit Memo Number']);
    updates['Resolved Value'] = toNumber_(submitted['Resolved Value']);
    updates['Closed Date'] = now;
  } else if (action === 'Close with Replacement') {
    updates['Status'] = 'Closed - Replacement';
    updates['Resolution Type'] = 'Replacement';
    updates['Replacement Tracking'] = cleanString_(submitted['Replacement Tracking']);
    updates['Resolved Value'] = toNumber_(submitted['Resolved Value']);
    updates['Closed Date'] = now;
  } else if (action === 'Extend Supplier Deadline') {
    updates['Status'] = 'Awaiting Supplier';
    updates['Response Deadline'] = toDate_(submitted['New Response Deadline']);
    updates['Last Reminder Date'] = '';
    updates['Escalation Level'] = 0;
  } else if (action === 'Withdraw Claim') {
    updates['Status'] = 'Closed - Withdrawn';
    updates['Resolution Type'] = 'Withdrawn';
    updates['Closed Date'] = now;
  } else {
    throw new Error('Unsupported resolution action: ' + action);
  }

  updateClaim_(claimId, updates);
  logActivity_(claimId, action, actor, updates['Supplier Response Summary'], sourceKey);

  try {
    ensureClaimFolders_(claimId);
    moveResolutionAttachments_(claimId, submitted['Resolution Attachment Files']);

    if (String(updates['Status']).indexOf('Closed - ') === 0) {
      createClaimDocument_(claimId, 'Closure');
    }

    updateClaim_(claimId, {
      'Automation Status': 'Completed',
      'Last Automation Run': new Date(),
      'Error Message': ''
    });

    const updated = findClaim_(claimId).object;
    sendInternal_(
      joinEmails_([
        updated['Owner Email'],
        updated['Submitter Email'],
        SETTINGS.SHARED_CLAIMS_EMAIL
      ]),
      claimId + ' updated: ' + updated['Status'],
      buildInternalStatusMessage_(updated)
    );

    return claimId;
  } catch (error) {
    markClaimFailure_(claimId, 'Resolution automation', error);
    throw error;
  }
}

function routeInitialApproval_(claimId) {
  const claim = findClaim_(claimId).object;
  const approver = claim['Claim Type'] === 'Damaged Goods'
    ? SETTINGS.QUALITY_COORDINATOR_EMAIL
    : SETTINGS.PROCUREMENT_MANAGER_EMAIL;

  updateClaim_(claimId, {
    'Status': 'Pending Approval',
    'Approval Stage': 'Stage 1',
    'Approval Status': 'Pending',
    'Current Approver Email': approver,
    'Approval Token Hash': '',
    'Approval Token Expires': '',
    'Last Updated': new Date()
  });

  issueApprovalRequest_(claimId, false, false);
}

function issueApprovalRequest_(claimId, isReminder, escalated) {
  const found = findClaim_(claimId);
  const claim = found.object;
  const approver = cleanString_(claim['Current Approver Email']);

  if (!isEmail_(approver)) {
    throw new Error('Current approver email is missing or invalid.');
  }

  const rawToken = Utilities.getUuid() + Utilities.getUuid();
  const expires = new Date(
    Date.now() + SETTINGS.APPROVAL_TOKEN_HOURS * 60 * 60 * 1000
  );

  updateClaim_(claimId, {
    'Approval Token Hash': hash_(rawToken),
    'Approval Token Expires': expires,
    'Last Reminder Date': new Date(),
    'Escalation Level': escalated ? 1 : Number(claim['Escalation Level'] || 0)
  });

  const reviewUrl = SETTINGS.WEB_APP_URL +
    '?claimId=' + encodeURIComponent(claimId) +
    '&token=' + encodeURIComponent(rawToken);

  const subjectPrefix = isReminder ? 'Reminder: ' : '';
  const subject = subjectPrefix + 'Supplier claim approval ' + claimId;
  const body =
    '<p>A supplier claim is awaiting your approval.</p>' +
    '<p><b>Claim:</b> ' + escapeHtml_(claimId) + '</p>' +
    '<p><b>Type:</b> ' + escapeHtml_(claim['Claim Type']) + '</p>' +
    '<p><b>Supplier:</b> ' + escapeHtml_(claim['Supplier Name']) + '</p>' +
    '<p><b>PO:</b> ' + escapeHtml_(claim['PO Number']) + '</p>' +
    '<p><b>Value:</b> ' +
      escapeHtml_(claim['Currency'] + ' ' + claim['Claim Value']) + '</p>' +
    '<p><a href="' + escapeHtml_(reviewUrl) + '">Open the approval page</a></p>' +
    '<p>Opening the page does not record a decision. You must submit the form.</p>';

  const options = {
    to: approver,
    subject: subject,
    htmlBody: body,
    body: stripHtml_(body),
    replyTo: SETTINGS.SHARED_CLAIMS_EMAIL
  };

  if (escalated && isEmail_(SETTINGS.APPROVAL_BACKUP_EMAIL)) {
    options.cc = SETTINGS.APPROVAL_BACKUP_EMAIL;
  }

  MailApp.sendEmail(options);
  logActivity_(claimId, isReminder ? 'Approval reminder sent' : 'Approval requested',
    Session.getEffectiveUser().getEmail(), approver, '');
}

function doGet(e) {
  try {
    validateConfiguration_(true);

    const claimId = cleanString_(e.parameter.claimId);
    const token = cleanString_(e.parameter.token);
    const actor = getActiveUserEmail_();
    const found = findClaim_(claimId);

    if (!found) {
      return renderMessagePage_('Claim not found', 'The requested claim does not exist.');
    }

    validateApprovalAccess_(found.object, token, actor);
    return renderApprovalPage_(found.object, token, actor);
  } catch (error) {
    return renderMessagePage_('Approval unavailable', error.message);
  }
}

function doPost(e) {
  const claimId = cleanString_(e.parameter.claimId);
  const token = cleanString_(e.parameter.token);
  const decision = cleanString_(e.parameter.decision);
  const comment = cleanString_(e.parameter.comment);
  const actor = getActiveUserEmail_();
  const lock = LockService.getScriptLock();

  try {
    lock.waitLock(30000);

    const found = findClaim_(claimId);

    if (!found) {
      throw new Error('Claim not found.');
    }

    validateApprovalAccess_(found.object, token, actor);

    if (['Approve', 'Reject', 'Return for Information'].indexOf(decision) === -1) {
      throw new Error('Select a valid approval decision.');
    }

    if (decision !== 'Approve' && !comment) {
      throw new Error('A comment is required for rejection or return.');
    }

    handleApprovalDecision_(claimId, decision, comment, actor);
    return renderMessagePage_('Decision recorded',
      claimId + ' was updated with the decision: ' + decision + '.');
  } catch (error) {
    logError_(claimId, 'Approval submission', error);
    return renderMessagePage_('Decision not recorded', error.message);
  } finally {
    try {
      lock.releaseLock();
    } catch (ignored) {
      console.log('Lock was not held.');
    }
  }
}

function validateApprovalAccess_(claim, token, actor) {
  if (claim['Status'] !== 'Pending Approval' ||
      claim['Approval Status'] !== 'Pending') {
    throw new Error('This claim is not awaiting an approval decision.');
  }

  if (!token || hash_(token) !== claim['Approval Token Hash']) {
    throw new Error('The approval token is invalid.');
  }

  const expires = toDate_(claim['Approval Token Expires']);

  if (!expires || expires.getTime() < Date.now()) {
    throw new Error('The approval token has expired. Request a new approval email.');
  }

  if (!actor) {
    throw new Error('Your signed-in organizational identity could not be verified.');
  }

  const allowed = [
    cleanString_(claim['Current Approver Email']).toLowerCase(),
    cleanString_(claim['Delegated Approver Email']).toLowerCase()
  ].filter(String);

  if (allowed.indexOf(actor.toLowerCase()) === -1) {
    throw new Error('You are not the assigned or delegated approver.');
  }
}

function handleApprovalDecision_(claimId, decision, comment, actor) {
  const claim = findClaim_(claimId).object;
  const existingApprovers = cleanString_(claim['Approved By']);
  const approvers = existingApprovers
    ? existingApprovers + ', ' + actor
    : actor;

  logActivity_(claimId, 'Approval decision: ' + decision, actor, comment, '');

  if (decision === 'Reject') {
    updateClaim_(claimId, {
      'Status': 'Rejected',
      'Approval Status': 'Rejected',
      'Approved By': approvers,
      'Approval Date': new Date(),
      'Approval Token Hash': '',
      'Approval Token Expires': '',
      'Notes': appendNote_(claim['Notes'], actor + ': ' + comment),
      'Last Updated': new Date()
    });

    notifyApprovalOutcome_(claimId, 'Rejected', comment);
    return;
  }

  if (decision === 'Return for Information') {
    updateClaim_(claimId, {
      'Status': 'Needs Information',
      'Approval Status': 'Returned',
      'Approved By': approvers,
      'Approval Token Hash': '',
      'Approval Token Expires': '',
      'Notes': appendNote_(claim['Notes'], actor + ': ' + comment),
      'Last Updated': new Date()
    });

    notifyApprovalOutcome_(claimId, 'Returned for Information', comment);
    return;
  }

  const needsSecondStage =
    claim['Approval Stage'] === 'Stage 1' &&
    Number(claim['Claim Value']) >= SETTINGS.HIGH_VALUE_THRESHOLD;

  if (needsSecondStage) {
    updateClaim_(claimId, {
      'Approval Stage': 'Stage 2',
      'Approval Status': 'Pending',
      'Current Approver Email': SETTINGS.OPERATIONS_DIRECTOR_EMAIL,
      'Approved By': approvers,
      'Approval Token Hash': '',
      'Approval Token Expires': '',
      'Notes': appendNote_(claim['Notes'], actor + ': Stage 1 approved. ' + comment),
      'Last Updated': new Date()
    });

    issueApprovalRequest_(claimId, false, false);
    return;
  }

  updateClaim_(claimId, {
    'Status': 'Approved - Send Pending',
    'Approval Status': 'Approved',
    'Approved By': approvers,
    'Approval Date': new Date(),
    'Approval Token Hash': '',
    'Approval Token Expires': '',
    'Current Approver Email': '',
    'Notes': appendNote_(claim['Notes'], actor + ': Final approval. ' + comment),
    'Last Updated': new Date()
  });

  try {
    finalizeApprovedClaim_(claimId);
  } catch (error) {
    markClaimFailure_(claimId, 'Approved claim finalization', error);
    throw error;
  }
}

function finalizeApprovedClaim_(claimId) {
  ensureClaimFolders_(claimId);

  let claim = findClaim_(claimId).object;
  let pdfFile = null;

  if (claim['Claim PDF File ID']) {
    try {
      const existing = DriveApp.getFileById(claim['Claim PDF File ID']);

      if (existing.getName().indexOf('Approved') !== -1) {
        pdfFile = existing;
      }
    } catch (ignored) {
      pdfFile = null;
    }
  }

  if (!pdfFile) {
    pdfFile = createClaimDocument_(claimId, 'Approved');
  }

  claim = findClaim_(claimId).object;
  sendSupplierNotice_(claim, pdfFile);

  updateClaim_(claimId, {
    'Status': 'Awaiting Supplier',
    'Supplier Notified Date': new Date(),
    'Automation Status': 'Completed',
    'Last Automation Run': new Date(),
    'Last Reminder Date': '',
    'Escalation Level': 0,
    'Error Message': '',
    'Last Updated': new Date()
  });

  logActivity_(claimId, 'Supplier notice sent',
    Session.getEffectiveUser().getEmail(), claim['Supplier Contact Email'], '');
}

function sendSupplierNotice_(claim, pdfFile) {
  if (!isEmail_(claim['Supplier Contact Email'])) {
    throw new Error('Supplier contact email is invalid.');
  }

  const attachments = [];
  const omitted = [];
  let totalBytes = 0;

  if (pdfFile) {
    const pdfBlob = pdfFile.getBlob().setName(pdfFile.getName());
    totalBytes += pdfFile.getSize();
    attachments.push(pdfBlob);
  }

  const evidenceIds = cleanString_(claim['Evidence File IDs'])
    .split(',')
    .map(function(value) { return value.trim(); })
    .filter(String);

  evidenceIds.forEach(function(fileId) {
    try {
      const file = DriveApp.getFileById(fileId);
      const mime = file.getMimeType();
      const allowed = mime.indexOf('image/') === 0 || mime === MimeType.PDF;

      if (!allowed ||
          totalBytes + file.getSize() > SETTINGS.MAX_EMAIL_ATTACHMENT_BYTES) {
        omitted.push(file.getName());
        return;
      }

      attachments.push(file.getBlob().setName(file.getName()));
      totalBytes += file.getSize();
    } catch (error) {
      omitted.push(fileId);
    }
  });

  const subject = 'Supplier claim ' + claim['Claim ID'] +
    ' for purchase order ' + claim['PO Number'];

  const body =
    '<p>Please review the attached supplier claim.</p>' +
    '<p><b>Claim ID:</b> ' + escapeHtml_(claim['Claim ID']) + '</p>' +
    '<p><b>Purchase order:</b> ' + escapeHtml_(claim['PO Number']) + '</p>' +
    '<p><b>Receipt:</b> ' + escapeHtml_(claim['Receipt Number']) + '</p>' +
    '<p><b>Claim type:</b> ' + escapeHtml_(claim['Claim Type']) + '</p>' +
    '<p><b>Requested resolution:</b> ' +
      escapeHtml_(claim['Requested Resolution']) + '</p>' +
    '<p><b>Requested response date:</b> ' +
      escapeHtml_(formatValue_(claim['Response Deadline'])) + '</p>' +
    '<p>Please reply to this email and include the claim ID in your response.</p>';

  MailApp.sendEmail({
    to: claim['Supplier Contact Email'],
    cc: claim['Owner Email'],
    subject: subject,
    htmlBody: body,
    body: stripHtml_(body),
    attachments: attachments,
    replyTo: SETTINGS.SHARED_CLAIMS_EMAIL
  });

  if (omitted.length > 0) {
    sendInternal_(
      joinEmails_([claim['Owner Email'], SETTINGS.AUTOMATION_ADMIN_EMAIL]),
      'Evidence omitted from supplier email for ' + claim['Claim ID'],
      '<p>These files were omitted because of file type, access, or size controls:</p>' +
      '<p>' + escapeHtml_(omitted.join(', ')) + '</p>'
    );
  }
}

function runDailyMonitor() {
  const sheet = getClaimsSheet_();

  if (sheet.getLastRow() < 2) {
    return;
  }

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

  rows.forEach(function(values) {
    const claim = objectFromRow_(headers, values);
    const claimId = claim['Claim ID'];

    try {
      if (claim['Automation Status'] === 'Failed' &&
          Number(claim['Retry Count'] || 0) < SETTINGS.MAX_AUTOMATIC_RETRIES) {
        retryClaim(claimId);
        return;
      }

      if (claim['Status'] === 'Pending Approval') {
        const waitingDays = daysSince_(claim['Last Reminder Date'] || claim['Created Date']);

        if (waitingDays >= SETTINGS.APPROVAL_REMINDER_DAYS) {
          const escalated = daysSince_(claim['Created Date']) >=
            SETTINGS.APPROVAL_ESCALATION_DAYS;
          issueApprovalRequest_(claimId, true, escalated);
        }
        return;
      }

      if (claim['Status'] === 'Awaiting Supplier' ||
          claim['Status'] === 'Supplier Overdue') {
        monitorSupplierDeadline_(claim);
        return;
      }

      if (claim['Status'] === 'Supplier Responded' &&
          daysSince_(claim['Last Updated']) >= 2) {
        sendOwnerReminder_(claim, 'Supplier response requires a resolution decision.');
        updateClaim_(claimId, {'Last Reminder Date': new Date()});
        return;
      }

      if (claim['Status'] === 'Needs Information' &&
          daysSince_(claim['Last Reminder Date'] || claim['Last Updated']) >= 3) {
        notifyNeedsInformation_(claim);
        updateClaim_(claimId, {'Last Reminder Date': new Date()});
      }
    } catch (error) {
      markClaimFailure_(claimId, 'Daily monitor', error);
    }
  });
}

function monitorSupplierDeadline_(claim) {
  const deadline = toDate_(claim['Response Deadline']);

  if (!deadline) {
    throw new Error('Response deadline is missing or invalid.');
  }

  const daysUntilDeadline = daysUntil_(deadline);
  const daysFromLastReminder = daysSince_(
    claim['Last Reminder Date'] || claim['Supplier Notified Date']
  );

  if (daysUntilDeadline < 0) {
    updateClaim_(claim['Claim ID'], {
      'Status': 'Supplier Overdue',
      'Escalation Level': Math.max(1, Number(claim['Escalation Level'] || 0)),
      'Last Updated': new Date()
    });

    if (daysFromLastReminder >= 3) {
      sendInternal_(
        joinEmails_([
          claim['Owner Email'],
          SETTINGS.PROCUREMENT_MANAGER_EMAIL,
          SETTINGS.SHARED_CLAIMS_EMAIL
        ]),
        'Overdue supplier response: ' + claim['Claim ID'],
        buildInternalStatusMessage_(claim)
      );

      updateClaim_(claim['Claim ID'], {
        'Last Reminder Date': new Date(),
        'Escalation Level': Number(claim['Escalation Level'] || 0) + 1
      });

      logActivity_(claim['Claim ID'], 'Supplier deadline escalated',
        Session.getEffectiveUser().getEmail(), String(daysUntilDeadline), '');
    }
    return;
  }

  if (daysUntilDeadline <= 2 && daysFromLastReminder >= 1) {
    sendOwnerReminder_(claim,
      'The supplier response deadline is in ' + daysUntilDeadline + ' day(s).');
    updateClaim_(claim['Claim ID'], {'Last Reminder Date': new Date()});
  }
}

function retryClaim(claimId) {
  const found = findClaim_(claimId);

  if (!found) {
    throw new Error('Claim not found: ' + claimId);
  }

  updateClaim_(claimId, {
    'Retry Count': Number(found.object['Retry Count'] || 0) + 1,
    'Automation Status': 'Processing',
    'Last Automation Run': new Date(),
    'Error Message': ''
  });

  try {
    ensureClaimFolders_(claimId);
    moveIntakeEvidence_(claimId);

    let claim = findClaim_(claimId).object;

    if (!claim['Claim PDF File ID']) {
      createClaimDocument_(claimId, 'Intake');
      claim = findClaim_(claimId).object;
    }

    if (claim['Status'] === 'Approved - Send Pending' ||
        (claim['Approval Status'] === 'Approved' &&
         claim['Status'] !== 'Awaiting Supplier')) {
      finalizeApprovedClaim_(claimId);
    } else if (claim['Status'] === 'Pending Approval' &&
               !claim['Current Approver Email']) {
      routeInitialApproval_(claimId);
    }

    updateClaim_(claimId, {
      'Automation Status': 'Completed',
      'Last Automation Run': new Date(),
      'Error Message': ''
    });

    logActivity_(claimId, 'Claim retry completed',
      Session.getEffectiveUser().getEmail(), '', '');
  } catch (error) {
    markClaimFailure_(claimId, 'Claim retry', error);
    throw error;
  }
}

function resubmitClaim(claimId) {
  const found = findClaim_(claimId);

  if (!found) {
    throw new Error('Claim not found: ' + claimId);
  }

  const claim = found.object;
  const errors = validateClaimRecord_(claim);

  if (claim['Potential Duplicate Of']) {
    errors.push('Potential Duplicate Of must be reviewed and cleared before resubmission.');
  }

  if (errors.length > 0) {
    throw new Error(errors.join(' | '));
  }

  updateClaim_(claimId, {
    'Status': 'Pending Approval',
    'Approval Status': 'Not Started',
    'Approval Stage': '',
    'Current Approver Email': '',
    'Approval Token Hash': '',
    'Approval Token Expires': '',
    'Automation Status': 'Processing',
    'Error Message': '',
    'Last Updated': new Date()
  });

  routeInitialApproval_(claimId);
  updateClaim_(claimId, {'Automation Status': 'Completed'});
  logActivity_(claimId, 'Claim resubmitted',
    Session.getEffectiveUser().getEmail(), '', '');
}

function retrySelectedClaim() {
  retryClaim(getSelectedClaimId_());
}

function resubmitSelectedClaim() {
  resubmitClaim(getSelectedClaimId_());
}

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

  if (sheet.getName() !== 'Claims' || row < 2) {
    throw new Error('Select a claim row on the Claims sheet.');
  }

  return cleanString_(sheet.getRange(row, 1).getValue());
}

function ensureClaimFolders_(claimId) {
  const claim = findClaim_(claimId).object;
  const root = DriveApp.getFolderById(SETTINGS.ROOT_FOLDER_ID);
  const year = Utilities.formatDate(
    toDate_(claim['Created Date']) || new Date(),
    Session.getScriptTimeZone(),
    'yyyy'
  );

  const yearFolder = getOrCreateFolder_(root, year);
  const folderName = sanitizeFileName_(
    claimId + ' - ' + claim['Supplier Name'] + ' - ' + claim['PO Number']
  );

  let claimFolder = getFolderByStoredId_(claim['Claim Folder ID']);

  if (!claimFolder) {
    claimFolder = getOrCreateFolder_(yearFolder, folderName);
  }

  let evidenceFolder = getFolderByStoredId_(claim['Evidence Folder ID']);

  if (!evidenceFolder) {
    evidenceFolder = getOrCreateFolder_(claimFolder, 'Evidence');
  }

  let correspondenceFolder = getFolderByStoredId_(claim['Correspondence Folder ID']);

  if (!correspondenceFolder) {
    correspondenceFolder = getOrCreateFolder_(claimFolder, 'Correspondence');
  }

  updateClaim_(claimId, {
    'Claim Folder ID': claimFolder.getId(),
    'Claim Folder URL': claimFolder.getUrl(),
    'Evidence Folder ID': evidenceFolder.getId(),
    'Evidence Folder URL': evidenceFolder.getUrl(),
    'Correspondence Folder ID': correspondenceFolder.getId(),
    'Last Updated': new Date()
  });
}

function moveIntakeEvidence_(claimId) {
  const claim = findClaim_(claimId).object;
  const ids = cleanString_(claim['Evidence File IDs'])
    .split(',')
    .map(function(value) { return value.trim(); })
    .filter(String);

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

  const folder = DriveApp.getFolderById(claim['Evidence Folder ID']);
  moveFiles_(claimId, ids, folder);
}

function moveResolutionAttachments_(claimId, rawValue) {
  const ids = extractDriveFileIds_(rawValue);

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

  const claim = findClaim_(claimId).object;
  const folder = DriveApp.getFolderById(claim['Correspondence Folder ID']);
  moveFiles_(claimId, ids, folder);
}

function moveFiles_(claimId, ids, destination) {
  const failures = [];

  ids.forEach(function(fileId) {
    try {
      const file = DriveApp.getFileById(fileId);

      if (file.getName().indexOf(claimId + ' - ') !== 0) {
        file.setName(claimId + ' - ' + sanitizeFileName_(file.getName()));
      }

      file.moveTo(destination);
    } catch (error) {
      failures.push(fileId + ': ' + error.message);
    }
  });

  if (failures.length > 0) {
    throw new Error('Some evidence files could not be moved. ' + failures.join(' | '));
  }
}

function createClaimDocument_(claimId, versionLabel) {
  const claim = findClaim_(claimId).object;
  const folder = DriveApp.getFolderById(claim['Claim Folder ID']);
  const title = claimId + ' - ' + versionLabel;
  const doc = DocumentApp.create(title);
  const body = doc.getBody();

  body.appendParagraph('Supplier Claim')
    .setHeading(DocumentApp.ParagraphHeading.HEADING1);
  body.appendParagraph(versionLabel + ' record');

  const rows = [
    ['Claim ID', claim['Claim ID']],
    ['Status', claim['Status']],
    ['Supplier', claim['Supplier Name']],
    ['Supplier Contact', claim['Supplier Contact Email']],
    ['Purchase Order', claim['PO Number']],
    ['Receipt', claim['Receipt Number']],
    ['Receipt Date', formatValue_(claim['Receipt Date'])],
    ['Claim Type', claim['Claim Type']],
    ['Item', claim['SKU']],
    ['Quantity Ordered', formatValue_(claim['Quantity Ordered'])],
    ['Quantity Received', formatValue_(claim['Quantity Received'])],
    ['Quantity Affected', formatValue_(claim['Quantity Affected'])],
    ['Unit Value', claim['Currency'] + ' ' + formatValue_(claim['Unit Value'])],
    ['Claim Value', claim['Currency'] + ' ' + formatValue_(claim['Claim Value'])],
    ['Requested Resolution', claim['Requested Resolution']],
    ['Response Deadline', formatValue_(claim['Response Deadline'])],
    ['Owner', claim['Owner Email']],
    ['Approval Status', claim['Approval Status']],
    ['Approved By', claim['Approved By']],
    ['Supplier Reference', claim['Supplier Reference']],
    ['Resolution Type', claim['Resolution Type']],
    ['Credit Memo', claim['Credit Memo Number']],
    ['Replacement Tracking', claim['Replacement Tracking']],
    ['Resolved Value', formatValue_(claim['Resolved Value'])],
    ['Closed Date', formatValue_(claim['Closed Date'])]
  ];

  body.appendTable(rows);
  body.appendParagraph('Claim Details')
    .setHeading(DocumentApp.ParagraphHeading.HEADING2);
  body.appendParagraph(cleanString_(claim['Description']) || 'No description recorded.');

  if (claim['Supplier Response Summary']) {
    body.appendParagraph('Supplier Response and Resolution')
      .setHeading(DocumentApp.ParagraphHeading.HEADING2);
    body.appendParagraph(claim['Supplier Response Summary']);
  }

  if (claim['Notes']) {
    body.appendParagraph('Internal Notes')
      .setHeading(DocumentApp.ParagraphHeading.HEADING2);
    body.appendParagraph(claim['Notes']);
  }

  doc.saveAndClose();

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

  const pdfBlob = docFile
    .getAs(MimeType.PDF)
    .setName(title + '.pdf');

  const pdfFile = folder.createFile(pdfBlob);

  updateClaim_(claimId, {
    'Claim Document URL': pdfFile.getUrl(),
    'Claim PDF File ID': pdfFile.getId(),
    'Last Updated': new Date()
  });

  logActivity_(claimId, versionLabel + ' document created',
    Session.getEffectiveUser().getEmail(), pdfFile.getId(), '');

  return pdfFile;
}

function validateIntake_(submitted, evidenceIds) {
  const errors = [];
  const type = cleanString_(submitted['Claim Type']);
  const allowedTypes = [
    'Short Shipment',
    'Damaged Goods',
    'Price Discrepancy',
    'Other'
  ];

  if (allowedTypes.indexOf(type) === -1) {
    errors.push('Claim Type is invalid.');
  }

  if (!isEmail_(submitted[SETTINGS.COLLECTED_EMAIL_HEADER])) {
    errors.push('Submitter email is missing or invalid.');
  }

  if (!cleanString_(submitted['Supplier Name'])) {
    errors.push('Supplier Name is required.');
  }

  if (!isEmail_(submitted['Supplier Contact Email'])) {
    errors.push('Supplier Contact Email is invalid.');
  }

  if (!cleanString_(submitted['Purchase Order Number'])) {
    errors.push('Purchase Order Number is required.');
  }

  if (!cleanString_(submitted['Receipt Number'])) {
    errors.push('Receipt Number is required.');
  }

  if (!toDate_(submitted['Receipt Date'])) {
    errors.push('Receipt Date is invalid.');
  }

  if (!cleanString_(submitted['Item SKU or Description'])) {
    errors.push('Item SKU or Description is required.');
  }

  if (!(toNumber_(submitted['Quantity Affected']) > 0)) {
    errors.push('Quantity Affected must be greater than zero.');
  }

  if (!(toNumber_(submitted['Estimated Claim Value']) > 0)) {
    errors.push('Estimated Claim Value must be greater than zero.');
  }

  if (['Credit', 'Replacement', 'Either'].indexOf(
      cleanString_(submitted['Requested Resolution'])) === -1) {
    errors.push('Requested Resolution is invalid.');
  }

  const deadline = toDate_(submitted['Supplier Response Deadline']);

  if (!deadline || daysUntil_(deadline) < 0) {
    errors.push('Supplier Response Deadline must be today or later.');
  }

  if (!cleanString_(submitted['Claim Details'])) {
    errors.push('Claim Details are required.');
  }

  if (type === 'Damaged Goods' && evidenceIds.length === 0) {
    errors.push('At least one evidence file is required for damaged goods.');
  }

  if (type === 'Short Shipment') {
    const ordered = toNumberOrBlank_(submitted['Quantity Ordered']);
    const received = toNumberOrBlank_(submitted['Quantity Received']);

    if (ordered === '' || received === '') {
      errors.push('Quantity Ordered and Quantity Received are required for shortages.');
    } else if (received > ordered) {
      errors.push('Quantity Received cannot exceed Quantity Ordered for a shortage.');
    }
  }

  return errors;
}

function validateClaimRecord_(claim) {
  const submitted = {
    'Claim Type': claim['Claim Type'],
    'Supplier Name': claim['Supplier Name'],
    'Supplier Contact Email': claim['Supplier Contact Email'],
    'Purchase Order Number': claim['PO Number'],
    'Receipt Number': claim['Receipt Number'],
    'Receipt Date': claim['Receipt Date'],
    'Item SKU or Description': claim['SKU'],
    'Quantity Ordered': claim['Quantity Ordered'],
    'Quantity Received': claim['Quantity Received'],
    'Quantity Affected': claim['Quantity Affected'],
    'Unit Value': claim['Unit Value'],
    'Estimated Claim Value': claim['Claim Value'],
    'Currency': claim['Currency'],
    'Claim Details': claim['Description'],
    'Requested Resolution': claim['Requested Resolution'],
    'Supplier Response Deadline': claim['Response Deadline']
  };

  submitted[SETTINGS.COLLECTED_EMAIL_HEADER] = claim['Submitter Email'];

  const evidence = cleanString_(claim['Evidence File IDs'])
    .split(',')
    .filter(String);

  return validateIntake_(submitted, evidence);
}

function validateResolution_(submitted, action) {
  const errors = [];
  const summary = cleanString_(submitted['Resolution Summary']);

  if (!summary) {
    errors.push('Resolution Summary is required.');
  }

  if (action === 'Close with Credit') {
    if (!cleanString_(submitted['Credit Memo Number'])) {
      errors.push('Credit Memo Number is required.');
    }

    if (toNumberOrBlank_(submitted['Resolved Value']) === '') {
      errors.push('Resolved Value is required.');
    }
  }

  if (action === 'Close with Replacement') {
    if (!cleanString_(submitted['Replacement Tracking'])) {
      errors.push('Replacement Tracking is required.');
    }

    if (toNumberOrBlank_(submitted['Resolved Value']) === '') {
      errors.push('Resolved Value is required.');
    }
  }

  if (action === 'Extend Supplier Deadline') {
    const newDeadline = toDate_(submitted['New Response Deadline']);

    if (!newDeadline || daysUntil_(newDeadline) < 0) {
      errors.push('New Response Deadline must be today or later.');
    }
  }

  const allowedActions = [
    'Record Supplier Response',
    'Close with Credit',
    'Close with Replacement',
    'Extend Supplier Deadline',
    'Withdraw Claim'
  ];

  if (allowedActions.indexOf(action) === -1) {
    errors.push('Action is invalid.');
  }

  return errors;
}

function isAuthorizedUpdater_(claim, actor) {
  const allowed = [
    claim['Owner Email'],
    claim['Delegated Approver Email'],
    SETTINGS.PROCUREMENT_MANAGER_EMAIL,
    SETTINGS.QUALITY_COORDINATOR_EMAIL,
    SETTINGS.OPERATIONS_DIRECTOR_EMAIL,
    SETTINGS.AUTOMATION_ADMIN_EMAIL
  ].map(function(value) {
    return cleanString_(value).toLowerCase();
  }).filter(String);

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

function findOwner_(supplierName) {
  const ss = SpreadsheetApp.openById(SETTINGS.SPREADSHEET_ID);
  const sheet = ss.getSheetByName('SupplierRouting');

  if (!sheet || sheet.getLastRow() < 2) {
    return SETTINGS.PROCUREMENT_MANAGER_EMAIL;
  }

  const values = sheet.getDataRange().getValues();
  const headers = values.shift();
  const target = normalize_(supplierName);

  for (let i = 0; i < values.length; i += 1) {
    const row = objectFromRow_(headers, values[i]);

    if (normalize_(row['Supplier Name']) === target &&
        String(row['Active']).toLowerCase() !== 'false' &&
        isEmail_(row['Owner Email'])) {
      return cleanString_(row['Owner Email']);
    }
  }

  return SETTINGS.PROCUREMENT_MANAGER_EMAIL;
}

function buildDuplicateKey_(submitted) {
  const parts = [
    normalize_(submitted['Supplier Name']),
    normalize_(submitted['Purchase Order Number']),
    normalize_(submitted['Receipt Number']),
    normalize_(submitted['Claim Type']),
    normalize_(submitted['Item SKU or Description'])
  ];

  return hash_(parts.join('|'));
}

function findOpenDuplicate_(duplicateKey) {
  const sheet = getClaimsSheet_();

  if (sheet.getLastRow() < 2) {
    return '';
  }

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

  for (let i = values.length - 1; i >= 0; i -= 1) {
    const row = objectFromRow_(headers, values[i]);
    const status = cleanString_(row['Status']);

    if (row['Duplicate Key'] === duplicateKey &&
        status.indexOf('Closed - ') !== 0 &&
        status !== 'Rejected') {
      return row['Claim ID'];
    }
  }

  return '';
}

function appendClaim_(claim) {
  const sheet = getClaimsSheet_();
  const row = CLAIM_HEADERS.map(function(header) {
    return Object.prototype.hasOwnProperty.call(claim, header) ? claim[header] : '';
  });

  sheet.appendRow(row);
}

function updateClaim_(claimId, updates) {
  const found = findClaim_(claimId);

  if (!found) {
    throw new Error('Claim not found: ' + claimId);
  }

  const values = found.values.slice();

  Object.keys(updates).forEach(function(field) {
    if (!Object.prototype.hasOwnProperty.call(found.headerMap, field)) {
      throw new Error('Unknown Claims field: ' + field);
    }

    values[found.headerMap[field]] = updates[field];
  });

  if (Object.prototype.hasOwnProperty.call(found.headerMap, 'Last Updated') &&
      !Object.prototype.hasOwnProperty.call(updates, 'Last Updated')) {
    values[found.headerMap['Last Updated']] = new Date();
  }

  found.sheet.getRange(found.row, 1, 1, values.length).setValues([values]);
}

function findClaim_(claimId) {
  if (!claimId) {
    return null;
  }

  const sheet = getClaimsSheet_();

  if (sheet.getLastRow() < 2) {
    return null;
  }

  const headers = getHeaders_(sheet);
  const cell = sheet
    .getRange(2, 1, sheet.getLastRow() - 1, 1)
    .createTextFinder(String(claimId))
    .matchEntireCell(true)
    .findNext();

  if (!cell) {
    return null;
  }

  const row = cell.getRow();
  const values = sheet.getRange(row, 1, 1, headers.length).getValues()[0];

  return {
    sheet: sheet,
    row: row,
    values: values,
    object: objectFromRow_(headers, values),
    headerMap: headerMap_(headers)
  };
}

function findClaimByField_(fieldName, fieldValue) {
  const sheet = getClaimsSheet_();

  if (sheet.getLastRow() < 2) {
    return null;
  }

  const headers = getHeaders_(sheet);
  const map = headerMap_(headers);
  const column = map[fieldName];

  if (column === undefined) {
    throw new Error('Unknown field: ' + fieldName);
  }

  const cell = sheet
    .getRange(2, column + 1, sheet.getLastRow() - 1, 1)
    .createTextFinder(String(fieldValue))
    .matchEntireCell(true)
    .findNext();

  if (!cell) {
    return null;
  }

  const row = cell.getRow();
  const values = sheet.getRange(row, 1, 1, headers.length).getValues()[0];

  return {
    sheet: sheet,
    row: row,
    values: values,
    object: objectFromRow_(headers, values),
    headerMap: map
  };
}

function getClaimsSheet_() {
  const sheet = SpreadsheetApp
    .openById(SETTINGS.SPREADSHEET_ID)
    .getSheetByName('Claims');

  if (!sheet) {
    throw new Error('Claims sheet does not exist. Run setupWorkbook().');
  }

  return sheet;
}

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

  return objectFromRow_(headers, values);
}

function buildSourceEventKey_(e) {
  return [
    SETTINGS.SPREADSHEET_ID,
    e.range.getSheet().getName(),
    e.range.getRow()
  ].join(':');
}

function generateClaimId_() {
  const properties = PropertiesService.getScriptProperties();
  const year = Utilities.formatDate(
    new Date(),
    Session.getScriptTimeZone(),
    'yyyy'
  );

  const key = 'CLAIM_SEQUENCE_' + year;
  const next = Number(properties.getProperty(key) || 0) + 1;
  properties.setProperty(key, String(next));

  return 'CLM-' + year + '-' + String(next).padStart(5, '0');
}

function logActivity_(claimId, action, actor, details, sourceEventKey) {
  const sheet = SpreadsheetApp
    .openById(SETTINGS.SPREADSHEET_ID)
    .getSheetByName('ActivityLog');

  sheet.appendRow([
    new Date(),
    claimId,
    action,
    actor,
    String(details || '').substring(0, 5000),
    sourceEventKey || ''
  ]);
}

function logError_(claimId, source, error) {
  const ss = SpreadsheetApp.openById(SETTINGS.SPREADSHEET_ID);
  const sheet = ss.getSheetByName('ErrorQueue') ||
    ensureSheet_(ss, 'ErrorQueue', ERROR_HEADERS);

  sheet.appendRow([
    new Date(),
    claimId,
    source,
    String(error.message || error).substring(0, 5000),
    String(error.stack || '').substring(0, 10000),
    false,
    ''
  ]);
}

function markClaimFailure_(claimId, source, error) {
  if (claimId && findClaim_(claimId)) {
    const claim = findClaim_(claimId).object;

    updateClaim_(claimId, {
      'Automation Status': 'Failed',
      'Last Automation Run': new Date(),
      'Retry Count': Number(claim['Retry Count'] || 0) + 1,
      'Error Message': String(error.message || error).substring(0, 5000)
    });
  }

  logError_(claimId, source, error);
  notifyAdmin_('Supplier claim automation failure ' + claimId,
    source + ': ' + error.message);
}

function notifyNeedsInformation_(claim) {
  sendInternal_(
    joinEmails_([claim['Submitter Email'], claim['Owner Email']]),
    'Information required for supplier claim ' + claim['Claim ID'],
    '<p>The claim requires additional information before approval.</p>' +
    '<p>' + escapeHtml_(claim['Error Message'] || claim['Notes']) + '</p>'
  );
}

function notifyPotentialDuplicate_(claim) {
  sendInternal_(
    joinEmails_([
      claim['Owner Email'],
      SETTINGS.PROCUREMENT_MANAGER_EMAIL,
      SETTINGS.AUTOMATION_ADMIN_EMAIL
    ]),
    'Potential duplicate supplier claim ' + claim['Claim ID'],
    '<p>This claim may duplicate ' +
      escapeHtml_(claim['Potential Duplicate Of']) + '.</p>' +
    '<p>Review both records before resubmitting or closing the new claim.</p>'
  );
}

function notifyApprovalOutcome_(claimId, outcome, comment) {
  const claim = findClaim_(claimId).object;

  sendInternal_(
    joinEmails_([
      claim['Submitter Email'],
      claim['Owner Email'],
      SETTINGS.SHARED_CLAIMS_EMAIL
    ]),
    claimId + ': ' + outcome,
    '<p><b>Outcome:</b> ' + escapeHtml_(outcome) + '</p>' +
    '<p><b>Comment:</b> ' + escapeHtml_(comment) + '</p>'
  );
}

function sendOwnerReminder_(claim, message) {
  sendInternal_(
    joinEmails_([claim['Owner Email'], SETTINGS.SHARED_CLAIMS_EMAIL]),
    'Supplier claim reminder ' + claim['Claim ID'],
    '<p>' + escapeHtml_(message) + '</p>' +
    buildInternalStatusMessage_(claim)
  );

  logActivity_(claim['Claim ID'], 'Owner reminder sent',
    Session.getEffectiveUser().getEmail(), message, '');
}

function notifyAdmin_(subject, message) {
  try {
    sendInternal_(
      SETTINGS.AUTOMATION_ADMIN_EMAIL,
      subject,
      '<p>' + escapeHtml_(message) + '</p>'
    );
  } catch (error) {
    console.error('Admin notification failed: ' + error.message);
  }
}

function sendInternal_(to, subject, htmlBody) {
  if (!to) {
    throw new Error('Internal notification has no valid recipient.');
  }

  MailApp.sendEmail({
    to: to,
    subject: subject,
    htmlBody: htmlBody,
    body: stripHtml_(htmlBody),
    replyTo: SETTINGS.SHARED_CLAIMS_EMAIL
  });
}

function buildInternalStatusMessage_(claim) {
  return '<p><b>Claim:</b> ' + escapeHtml_(claim['Claim ID']) + '</p>' +
    '<p><b>Supplier:</b> ' + escapeHtml_(claim['Supplier Name']) + '</p>' +
    '<p><b>Status:</b> ' + escapeHtml_(claim['Status']) + '</p>' +
    '<p><b>Owner:</b> ' + escapeHtml_(claim['Owner Email']) + '</p>' +
    '<p><b>Deadline:</b> ' +
      escapeHtml_(formatValue_(claim['Response Deadline'])) + '</p>';
}

function renderApprovalPage_(claim, token, actor) {
  const html =
    '<!doctype html><html><head>' +
    '<meta name="viewport" content="width=device-width, initial-scale=1">' +
    '<title>Supplier Claim Approval</title></head><body>' +
    '<h1>Supplier Claim Approval</h1>' +
    '<p><b>Claim:</b> ' + escapeHtml_(claim['Claim ID']) + '</p>' +
    '<p><b>Signed in as:</b> ' + escapeHtml_(actor) + '</p>' +
    '<p><b>Supplier:</b> ' + escapeHtml_(claim['Supplier Name']) + '</p>' +
    '<p><b>Type:</b> ' + escapeHtml_(claim['Claim Type']) + '</p>' +
    '<p><b>PO and receipt:</b> ' +
      escapeHtml_(claim['PO Number'] + ' / ' + claim['Receipt Number']) + '</p>' +
    '<p><b>Value:</b> ' +
      escapeHtml_(claim['Currency'] + ' ' + claim['Claim Value']) + '</p>' +
    '<p><b>Details:</b> ' + escapeHtml_(claim['Description']) + '</p>' +
    '<form method="post" action="' + escapeHtml_(SETTINGS.WEB_APP_URL) + '">' +
    '<input type="hidden" name="claimId" value="' +
      escapeHtml_(claim['Claim ID']) + '">' +
    '<input type="hidden" name="token" value="' + escapeHtml_(token) + '">' +
    '<p><label>Decision ' +
    '<select name="decision" required>' +
    '<option value="">Select</option>' +
    '<option value="Approve">Approve</option>' +
    '<option value="Return for Information">Return for Information</option>' +
    '<option value="Reject">Reject</option>' +
    '</select></label></p>' +
    '<p><label>Comment</label></p>' +
    '<p><textarea name="comment" rows="6" cols="60"></textarea></p>' +
    '<p><button type="submit">Submit Decision</button></p>' +
    '</form></body></html>';

  return HtmlService.createHtmlOutput(html);
}

function renderMessagePage_(title, message) {
  const html =
    '<!doctype html><html><head>' +
    '<meta name="viewport" content="width=device-width, initial-scale=1">' +
    '<title>' + escapeHtml_(title) + '</title></head><body>' +
    '<h1>' + escapeHtml_(title) + '</h1>' +
    '<p>' + escapeHtml_(message) + '</p>' +
    '</body></html>';

  return HtmlService.createHtmlOutput(html);
}

function getActiveUserEmail_() {
  return cleanString_(Session.getActiveUser().getEmail()).toLowerCase();
}

function getFolderByStoredId_(folderId) {
  if (!folderId) {
    return null;
  }

  try {
    return DriveApp.getFolderById(folderId);
  } catch (error) {
    return null;
  }
}

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

function extractDriveFileIds_(value) {
  const text = cleanString_(value);
  const matches = text.match(/[A-Za-z0-9_-]{25,}/g) || [];

  return matches.filter(function(item, index) {
    return matches.indexOf(item) === index;
  });
}

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

function headerMap_(headers) {
  const map = {};

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

  return map;
}

function objectFromRow_(headers, values) {
  const object = {};

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

  return object;
}

function toNumber_(value) {
  const cleaned = String(value === null || value === undefined ? '' : value)
    .replace(/[^0-9.-]/g, '');
  const number = Number(cleaned);

  return Number.isFinite(number) ? number : 0;
}

function toNumberOrBlank_(value) {
  if (value === '' || value === null || value === undefined) {
    return '';
  }

  const cleaned = String(value).replace(/[^0-9.-]/g, '');
  const number = Number(cleaned);

  return Number.isFinite(number) ? number : '';
}

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

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

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

function daysUntil_(value) {
  const date = toDate_(value);

  if (!date) {
    return 999999;
  }

  return Math.floor((startOfDay_(date).getTime() -
    startOfDay_(new Date()).getTime()) / 86400000);
}

function daysSince_(value) {
  const date = toDate_(value);

  if (!date) {
    return 999999;
  }

  return Math.floor((startOfDay_(new Date()).getTime() -
    startOfDay_(date).getTime()) / 86400000);
}

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

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

function normalize_(value) {
  return cleanString_(value).toLowerCase().replace(/\s+/g, ' ');
}

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

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

  return Utilities.base64EncodeWebSafe(digest);
}

function formatValue_(value) {
  if (value instanceof Date && !isNaN(value.getTime())) {
    return Utilities.formatDate(
      value,
      Session.getScriptTimeZone(),
      'yyyy-MM-dd HH:mm'
    );
  }

  return cleanString_(value);
}

function appendNote_(existing, note) {
  const timestamp = Utilities.formatDate(
    new Date(),
    Session.getScriptTimeZone(),
    'yyyy-MM-dd HH:mm'
  );

  return [cleanString_(existing), timestamp + ' ' + cleanString_(note)]
    .filter(String)
    .join('\n');
}

function sanitizeFileName_(value) {
  return cleanString_(value)
    .replace(/[\\/:*?"<>|]/g, '-')
    .substring(0, 180);
}

function joinEmails_(values) {
  const unique = [];

  values.forEach(function(value) {
    cleanString_(value).split(',').forEach(function(email) {
      const cleaned = email.trim();

      if (isEmail_(cleaned) && unique.indexOf(cleaned) === -1) {
        unique.push(cleaned);
      }
    });
  });

  return unique.join(',');
}

function escapeHtml_(value) {
  return cleanString_(value)
    .replace(/&/g, '&amp;')
    .replace(/</g, '&lt;')
    .replace(/>/g, '&gt;')
    .replace(/"/g, '&quot;')
    .replace(/'/g, '&#39;');
}

function stripHtml_(value) {
  return String(value)
    .replace(/<[^>]*>/g, ' ')
    .replace(/\s+/g, ' ')
    .trim();
}

function validateConfiguration_(requireWebApp) {
  const required = [
    'SPREADSHEET_ID',
    'ROOT_FOLDER_ID',
    'PROCUREMENT_MANAGER_EMAIL',
    'QUALITY_COORDINATOR_EMAIL',
    'OPERATIONS_DIRECTOR_EMAIL',
    'AUTOMATION_ADMIN_EMAIL',
    'SHARED_CLAIMS_EMAIL'
  ];

  if (requireWebApp) {
    required.push('WEB_APP_URL');
  }

  required.forEach(function(key) {
    const value = SETTINGS[key];

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

Optional AI Extension

Paste the following into a second project file named AI.gs. The core script calls it only when AI_ENABLED is set to true. Store the API key as a Script Property named OPENAI_API_KEY. Do not place the key in the spreadsheet or source code.

const AI_SYSTEM_INSTRUCTION = [
  'You review supplier shortage, damage, and price discrepancy claim data.',
  'Return only the requested structured JSON.',
  'Summarize facts without deciding supplier liability.',
  'Do not approve, reject, close, or assign financial responsibility.',
  'Treat missing fields as missing rather than inventing values.',
  'Use normal when deterministic data does not justify high priority.',
  'Identify uncertainty explicitly.'
].join(' ');

function runAiReviewForSelectedClaim() {
  runAiReview_(getSelectedClaimId_());
}

function runAiReview_(claimId) {
  if (!SETTINGS.AI_ENABLED) {
    throw new Error('AI_ENABLED is false.');
  }

  if (!SETTINGS.AI_MODEL ||
      SETTINGS.AI_MODEL.indexOf('YOUR_') === 0) {
    throw new Error('Replace YOUR_MODEL_NAME with an approved model.');
  }

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

  if (!apiKey) {
    throw new Error('Missing OPENAI_API_KEY Script Property.');
  }

  const found = findClaim_(claimId);

  if (!found) {
    throw new Error('Claim not found: ' + claimId);
  }

  const claim = found.object;
  const userPrompt = buildAiUserPrompt_(claim);
  const payload = {
    model: SETTINGS.AI_MODEL,
    instructions: AI_SYSTEM_INSTRUCTION,
    input: userPrompt,
    text: {
      format: {
        type: 'json_schema',
        name: 'supplier_claim_review',
        strict: true,
        schema: {
          type: 'object',
          additionalProperties: false,
          properties: {
            claim_summary: {
              type: 'string'
            },
            suggested_category: {
              type: 'string',
              enum: [
                'Short Shipment',
                'Damaged Goods',
                'Price Discrepancy',
                'Other',
                'Unclear'
              ]
            },
            priority_suggestion: {
              type: 'string',
              enum: ['Normal', 'High', 'Manual Review']
            },
            missing_information: {
              type: 'array',
              items: {
                type: 'string',
                enum: [
                  'Supplier',
                  'Purchase Order',
                  'Receipt',
                  'Receipt Date',
                  'Item',
                  'Quantity',
                  'Value',
                  'Description',
                  'Evidence',
                  'Supplier Contact',
                  'Response Deadline'
                ]
              }
            },
            confidence: {
              type: 'number',
              minimum: 0,
              maximum: 1
            },
            rationale: {
              type: 'string'
            }
          },
          required: [
            'claim_summary',
            'suggested_category',
            'priority_suggestion',
            'missing_information',
            'confidence',
            'rationale'
          ]
        }
      }
    }
  };

  const responseJson = fetchAiWithRetry_(
    SETTINGS.AI_ENDPOINT,
    apiKey,
    payload
  );

  const outputText = extractAiOutputText_(responseJson);
  let result;

  try {
    result = JSON.parse(outputText);
  } catch (error) {
    throw new Error('AI output was not valid JSON: ' + outputText.substring(0, 500));
  }

  validateAiResult_(result);

  updateClaim_(claimId, {
    'AI Summary': result.claim_summary,
    'AI Suggested Category': result.suggested_category,
    'AI Missing Information': result.missing_information.join(', '),
    'AI Confidence': result.confidence,
    'AI Review Status': result.confidence < 0.7
      ? 'Low Confidence - Human Review'
      : 'Pending Human Review',
    'AI Last Run': new Date(),
    'AI Error': ''
  });

  logActivity_(
    claimId,
    'AI review completed',
    Session.getEffectiveUser().getEmail(),
    JSON.stringify({
      confidence: result.confidence,
      category: result.suggested_category,
      usage: responseJson.usage || {}
    }),
    ''
  );

  return result;
}

function buildAiUserPrompt_(claim) {
  const safeDescription = redactForAi_(claim['Description']).substring(0, 5000);

  return [
    'Review this supplier claim.',
    'Recorded category: ' + claim['Claim Type'],
    'Purchase order present: ' + Boolean(claim['PO Number']),
    'Receipt present: ' + Boolean(claim['Receipt Number']),
    'Receipt date present: ' + Boolean(claim['Receipt Date']),
    'Item present: ' + Boolean(claim['SKU']),
    'Quantity ordered: ' + formatValue_(claim['Quantity Ordered']),
    'Quantity received: ' + formatValue_(claim['Quantity Received']),
    'Quantity affected: ' + formatValue_(claim['Quantity Affected']),
    'Claim value: ' + formatValue_(claim['Claim Value']),
    'Requested resolution: ' + claim['Requested Resolution'],
    'Response deadline present: ' + Boolean(claim['Response Deadline']),
    'Evidence file count: ' + cleanString_(claim['Evidence File IDs'])
      .split(',')
      .filter(String).length,
    'Description: ' + safeDescription,
    'Identify missing information only from the supplied facts.'
  ].join('\n');
}

function fetchAiWithRetry_(url, apiKey, payload) {
  const retryableCodes = [429, 500, 502, 503, 504];
  let lastError = null;

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

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

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

      lastError = new Error('AI API returned HTTP ' + code + ': ' +
        text.substring(0, 1000));

      if (retryableCodes.indexOf(code) === -1) {
        throw lastError;
      }
    } catch (error) {
      lastError = error;
    }

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

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

function extractAiOutputText_(responseJson) {
  if (responseJson.output_text) {
    return responseJson.output_text;
  }

  const output = responseJson.output || [];

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

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

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

function validateAiResult_(result) {
  const categories = [
    'Short Shipment',
    'Damaged Goods',
    'Price Discrepancy',
    'Other',
    'Unclear'
  ];

  const priorities = ['Normal', 'High', 'Manual Review'];

  if (!result || typeof result !== 'object') {
    throw new Error('AI result must be an object.');
  }

  if (!cleanString_(result.claim_summary)) {
    throw new Error('AI result is missing claim_summary.');
  }

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

  if (priorities.indexOf(result.priority_suggestion) === -1) {
    throw new Error('AI priority_suggestion is invalid.');
  }

  if (!Array.isArray(result.missing_information)) {
    throw new Error('AI missing_information must be an array.');
  }

  if (typeof result.confidence !== 'number' ||
      result.confidence < 0 ||
      result.confidence > 1) {
    throw new Error('AI confidence must be between zero and one.');
  }
}

function redactForAi_(value) {
  return cleanString_(value)
    .replace(/[^\s@]+@[^\s@]+\.[^\s@]+/g, '[EMAIL REDACTED]')
    .replace(/\+?[0-9][0-9\s().-]{7,}[0-9]/g, '[PHONE REDACTED]');
}

Authorization and Deployment

1. Replace the spreadsheet, folder, web app, email, and model placeholders.
2. Save the project and run setupWorkbook() manually from the Apps Script editor.
3. Review and accept the requested spreadsheet, Drive, Docs, email, trigger, and external-request permissions.
4. Deploy the project as a web app. Use the automation owner as the executing identity and restrict access to the organization.
5. Copy the web app URL into SETTINGS.WEB_APP_URL.
6. Create a new deployment version after changing the URL.
7. Submit a test intake form and verify the trigger, claim row, folders, PDF, and approval email.
8. Open the approval page as the correct user and as an unauthorized user.
9. Run runDailyMonitor() manually with test deadlines before relying on the time-driven trigger.

Inspect Apps Script execution history for function name, duration, user, logs, and error stack. The Claims sheet shows business-level failures, while ErrorQueue stores technical details.

To recover a failed record, select its row and use Supplier Claims, Retry selected claim. To correct a returned or duplicate-reviewed claim, update only the permitted fields, clear the duplicate flag when justified, document the correction, and choose Resubmit selected claim.

Failure Handling and Operational Reliability

The source event key provides idempotency for intake events. Resolution events are recorded in Last Resolution Event Key before noncritical confirmation steps, so a repeated event cannot close the claim twice.

The approved status is committed before supplier-document delivery. If delivery fails, the system does not roll back the human decision. Instead, it creates an explicit send-pending state that can be retried without another approval.

The Claims filter for Automation Status equal to Failed acts as the manual-review queue. Records with Retry Count at or above three require human investigation. ErrorQueue functions as a lightweight dead-letter queue because it retains failures that automatic processing did not resolve.

Staff should reconcile Intake Responses against Claims, Approved claims against Supplier Notified Date, and closed claims against the required credit or replacement evidence. Reconciliation identifies partial failures that a trigger log alone may not reveal.

A Complete Example

A receiving employee at Larkspur Equipment Works opens the intake form after inspecting a pallet of motor assemblies.

• Claim Type: Damaged Goods
• Supplier: A fictional supplier recorded in the routing table
• Purchase Order: PO-10482
• Receipt: RCV-88714
• Quantity Ordered: 20
• Quantity Received: 20
• Quantity Affected: 3
• Unit Value: $410
• Estimated Claim Value: $1,230
• Requested Resolution: Replacement
• Evidence: Two damage photographs and one receiving document

1. Google Forms writes the response into row 42 of Intake Responses.
2. Apps Script creates the event key from the workbook ID, sheet name, and row 42. No prior record uses the key.
3. The script validates the email addresses, dates, claim value, quantities, requested resolution, and presence of damage evidence.
4. The sequence generator creates CLM-2026-00041.
5. The supplier routing table assigns the claim to Buyer 1. The duplicate key does not match an open claim.
6. Drive folders are created under Supplier Claims/2026. The three uploaded files are renamed with the claim ID and moved into Evidence.
7. Apps Script creates CLM-2026-00041 - Intake.pdf.
8. Because the type is Damaged Goods, the quality coordinator receives the Stage 1 approval request.
9. The value is below $2,500, so no