Secure Employee Document Collection with Google Workspace

The Business Situation

This representative scenario involves an 85-person distributed technical consulting firm. Its three-person People Operations team coordinates onboarding and employee document maintenance, while a compliance manager reviews professional certifications and non-standard employment documents.

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 firm uses Google Workspace for email, shared files, spreadsheets, and collaboration. Employees work remotely, so employment agreements, tax forms, identification, professional certifications, and policy acknowledgments are exchanged electronically.

People Operations processes approximately 110 document submissions per month. This includes documents from new employees, expiring certifications, revised tax forms, policy updates, and replacements for incomplete or unreadable files.

The original process relied on a shared Gmail inbox, individual employee folders in Google Drive, and a tracking spreadsheet. Employees emailed attachments to People Operations. A coordinator downloaded each file, looked up the employee, renamed the document, moved it into Drive, updated the spreadsheet, and sent a confirmation.

That arrangement was manageable at lower volumes, but several operational and privacy weaknesses had emerged:

• Attachments remained in inboxes after being copied to Drive.
• File names varied by employee and coordinator.
• Required documents were tracked separately from submitted documents.
• Follow-up dates depended on manual spreadsheet review.
• Managers received inconsistent escalation messages.
• Employees sometimes resent files because they could not see whether an earlier attachment had been processed.
• Identification and tax records could be stored in folders with broader access than intended.
• There was no reliable automation history showing when reminders, escalations, and processing attempts occurred.

The company wanted to retain Google Workspace rather than introduce a new employee platform immediately. The objective was to create a controlled collection process using Google Forms, Google Sheets, Google Drive, Google Apps Script, and Gmail.

The Existing Process

The original workflow followed this sequence:

1. People Operations emailed an employee with a list of required documents.
2. The employee replied with one or more attachments.
3. A coordinator opened the email and checked whether the attachments were readable and complete.
4. The coordinator searched a spreadsheet for the employee’s row.
5. Each attachment was downloaded and renamed manually.
6. The coordinator located or created the employee’s Drive folder.
7. The file was uploaded to Drive and the email attachment was left in the mailbox.
8. The spreadsheet was updated with a received date, status, and occasional notes.
9. If documents were missing, the coordinator created a calendar reminder or left the email unread.
10. Overdue items were escalated to a manager when someone remembered to review the tracker.
11. Compliance-sensitive documents were forwarded or shared with a second reviewer.

The process also depended heavily on one experienced coordinator who understood the folder structure and spreadsheet conventions. During absences, another team member could see the inbox but could not always determine which attachments had already been stored or reviewed.

What the New System Needed to Do

The project team defined the workflow requirements before selecting the final design.

Several decisions were intentionally left outside the automation. People Operations remained responsible for validating new employee records, reviewers remained responsible for document acceptance, and Legal or Compliance remained responsible for policy exceptions and retention decisions.

Implementation Approaches Considered

Improving email alone

The team could have standardized subject lines, created Gmail labels, and added spreadsheet templates. That would have reduced some inconsistency but would not have removed attachments from the inbox workflow or provided reliable event-driven task updates.

Microsoft 365

A Microsoft 365 design using Microsoft Forms, SharePoint, Power Automate, and Outlook could meet similar requirements. It was not selected because this representative firm already managed identities, mail, documents, and employee collaboration through Google Workspace.

Airtable

Airtable could provide a more database-like interface than a spreadsheet. It was considered more appropriate for operational workflows where attachment sensitivity, employee authentication, regional data handling, and access inheritance had been separately reviewed.

Dedicated HR software

A dedicated HR platform could reduce custom maintenance and provide broader employee lifecycle functions. The firm did not yet require payroll integration, employee self-service, complex regional onboarding, or vendor-supported compliance workflows, so replacing the current environment was not justified at this stage.

The Selected Solution

The selected implementation retained the company’s Google Workspace environment and added a controlled automation layer.

Google Forms became the intake tool. Google Sheets became the operational system of record, while Google Drive remained the authoritative file repository. Apps Script connected the systems through an installable form-submission trigger and scheduled monitoring triggers.

The design retained manual control over employee validation, document acceptance, exceptions, retention disposition, and access changes. Automation removed repetitive copying, renaming, task generation, reminders, and routine status updates.

The solution assumed that employees had managed Google accounts. A Google Forms file-upload question requires the respondent to sign in. If pre-hires, contractors, or other external users cannot use an approved Google account, the business should use a secure external portal rather than ask them to send sensitive files through ordinary email.

System Architecture and Data Flow

• Intake: Google Forms with collected email addresses and a file-upload question
• System of record: A restricted Google Sheets workbook
• Automation layer: Spreadsheet-bound Google Apps Script
• Document storage: Restricted Google Drive roots organized by sensitivity
• Notifications: Gmail messages sent by a controlled automation account
• Reporting: Filtered Sheets views and a summary dashboard
• AI layer: Optional extraction for approved certification documents only

1. Employee submits a document. Google Forms records the timestamp, authenticated account email, employee ID, document type, metadata, acknowledgment, and uploaded file reference. An incomplete form cannot be submitted when a required question is missing.
2. The response reaches Google Sheets. The linked response sheet creates a new row. The installable spreadsheet form-submission trigger receives the row number as the event identifier.
3. Apps Script validates the response. The script checks the employee ID format, email format, acknowledgment, department value, document type, file count, MIME type, and file size. Known employee records must match the collected account email.
4. The employee record is found or provisionally created. Known employees use roster-controlled department, manager, and start-date values. An unknown employee is created with an unverified status and routed to People Operations for manual validation.
5. Requirement tasks are generated. The script reads the Requirements sheet and creates missing task records for document types applicable to the employee’s department.
6. The file is routed. The document type determines whether the file enters the general, compliance, or highly restricted Drive root. The script creates an employee subfolder when necessary.
7. The file is renamed. The stored name follows the pattern EMP-0042_CERT_20260715_v01.pdf. The original name remains in the document register.
8. The document register is updated. The script stores the document ID, Drive file ID, folder ID, version, uploader, MIME type, size, expiry date, retention rule, and link.
9. The task status changes. The applicable task moves from Open or Overdue to Submitted and receives the document ID and review reminder date.
10. Notifications are sent. The employee receives a confirmation without an attachment. The assigned reviewer receives a task notification, subject to the configured access rules.
11. A reviewer verifies or returns the task. An authorized user selects the task row and uses a custom Sheets menu. The script records the reviewer’s account and timestamp.
12. Scheduled monitoring runs. A daily trigger sends reminders and escalations. An hourly trigger retries transient submission failures up to the configured limit.
13. Failures remain visible. Validation problems move to Manual Review. Technical failures remain marked Failed with retry count, error message, and log entries.

Data Structure

The workbook contains six operational sheets plus the linked form-response sheet.

Employees

One row per employee. It supplies trusted roster values used by the automation.

Requirements

One row per required document rule.

Tasks

One row per employee and requirement combination.

Documents

One row per uploaded document version.

AutomationLog

Append-only processing, warning, notification, approval, and failure events.

Dashboard

Formula-based operational counts and filtered views.

Employee record

Task record

Document record

The principal relationship is Employees to Tasks as one-to-many. Requirements to Tasks is also one-to-many. A Task can reference several historical Documents over time, but its Document ID points to the current submitted version.

Workflow Statuses and Ownership

Automation can move a task into Open, Submitted, or Overdue. Only an authorized human can set Verified, Returned, Waived, or Not Applicable. A reviewer cannot verify a document for an employee whose employment record remains Unverified.

Step-by-Step Implementation

Step 1: Prepare the Accounts and Permissions

1. Create or identify a controlled Google Workspace account that will own the form, response workbook, Apps Script project, and Gmail automation. Avoid assigning ownership to an employee’s personal account.
2. Confirm that the Workspace edition and administrative policies support the required Forms, Drive, Apps Script, and shared-storage features. Licensing and administrative controls vary, so verify them in the organization’s own tenant.
3. Create named Google Groups or equivalent controlled access groups for People Operations administrators and compliance reviewers.
4. Limit form editor access to the automation account and designated form administrators. Form editors can have access to form responses and uploaded content, so this role must not be assigned casually.
5. Create three Drive roots:
   • General employee records: policy acknowledgments and other lower-sensitivity employment documents
   • Compliance records: professional certifications accessible to approved compliance reviewers
   • Restricted employee records: identity, tax, and other high-sensitivity files limited to People Operations
6. Record each folder ID from its Drive URL. These values replace YOUR_GENERAL_FOLDER_ID, YOUR_COMPLIANCE_FOLDER_ID, and YOUR_RESTRICTED_FOLDER_ID in the script.
7. Create the Google Form using the automation account and link it to a new Google Sheets response workbook.
8. Restrict the response workbook to People Operations and approved technical administrators. Do not share it with all employees or managers.
9. Create two test employee accounts and one reviewer account. Use fabricated test documents that contain no real personal information.
10. Document who can change form questions, requirement rules, script configuration, workbook data, and Drive permissions.

The automation account requests authorization to read and write the configured spreadsheet and Drive folders, manage installable triggers, and send Gmail messages. The optional AI extension also requires permission to make external HTTP requests.

Step 2: Build the Intake

Create one Google Form submission per document. This keeps each task, file, version, and review decision independently traceable.

Enable collection of email addresses. Where policy permits, restrict responses to managed organizational accounts. Disable respondent access to summary charts and do not include sensitive submitted values in the confirmation message.

Use a confirmation message such as: Your document was received for automated processing. People Operations will contact you if it cannot be matched, stored, or verified.

Add a concise privacy notice explaining the collection purpose, intended reviewers, storage environment, retention basis, and internal contact for corrections. The notice should be approved by the organization’s privacy or legal owner.

Google Forms cannot securely hide trusted authorization data in a prefilled URL. Employee ID, department, and similar submitted values must therefore be validated against the roster rather than accepted as proof of identity.

Step 3: Create the System of Record

Open the linked response spreadsheet, select the Apps Script editor from the spreadsheet’s extension tools, and paste the complete script provided later in this article.

The setup function creates the supporting sheets and headers. The response sheet remains controlled by Google Forms, while the script adds only these tracking columns to its right:

• Automation Status
• Document Record ID
• Last Automation Run
• Retry Count
• Automation Error

Seed the Employees sheet from the authoritative People Operations roster. Employee ID must be unique. Work Email, Manager Email, and Department should be protected from general editing because they control identity matching, escalation, and requirement assignment.

The script generates a deterministic Task Key using:

EMPLOYEE_ID|REQUIREMENT_CODE

For example:

EMP-0042|CERT

This key prevents a repeated form event from creating a second active requirement task. Document versions are separate records because an employee can submit a replacement without losing prior review history.

Create protected ranges for the following:

• Employee ID and roster-controlled email fields
• Requirement codes and destination folder keys
• Document file IDs and Drive links
• Automation status and error columns
• Reviewer identity and timestamps
• AutomationLog sheet

Step 4: Connect the Tools

The form question labels must match the script configuration exactly. If the form is localized or a question is renamed, update the corresponding value in FORM_FIELDS.

Authentication is handled through the Google account that authorizes the Apps Script project. No Google password or OAuth token is stored in the spreadsheet. Folder IDs are identifiers rather than credentials, but access to the script should still be restricted.

Step 5: Build the Core Automation

The form-submission automation follows this exact order:

1. Acquire a script lock to prevent concurrent updates from creating duplicate records.
2. Build an idempotency key from spreadsheet ID, sheet ID, and row number.
3. Stop if the response row is already marked Processed.
4. Mark the row Processing and record the current run time.
5. Validate required values and extract one Drive file ID.
6. Find or create the employee record.
7. For known employees, compare the submitted email and collected account email with the roster email.
8. Generate any missing requirement tasks.
9. Find the requirement matching the submitted document type.
10. Check whether the file ID already exists in Documents.
11. Validate MIME type and size.
12. Create or find the destination employee folder.
13. Calculate the next document version.
14. Move and rename the file.
15. Mark any prior current version as Superseded when appropriate.
16. Create the document record.
17. Update the requirement task to Submitted.
18. Recalculate the employee collection status.
19. Mark the response row Processed.
20. Send the employee confirmation and reviewer notice.
21. Append an AutomationLog event.

• Trigger: New Google Forms response written to the linked spreadsheet
• Conditions: Valid employee ID, email, acknowledgment, document type, file count, MIME type, and file size
• Actions: Create or update records, generate tasks, move file, rename file, link document, and send notifications
• Fields updated: Response automation status, employee collection status, task status, document metadata, and log data
• Notification: Employee confirmation and reviewer notice without sensitive attachment content
• Exception: Validation failures move to Manual Review; transient technical failures move to Failed and become eligible for retry

The Drive file ID is the principal document idempotency key. If a trigger is delivered twice or a partial run is retried after the document record was created, the automation finds the existing File ID and reconciles the task rather than creating another document version.

Step 6: Add Approvals, Reminders, and Escalations

Document review is implemented through a custom menu in the restricted spreadsheet. Authorized reviewers select a task row and choose either Verify selected task or Return selected task.

The approval rules are:

• Identity and tax documents are reviewed only by designated People Operations users.
• Professional certifications are reviewed by the compliance reviewer.
• Employment agreements follow normal People Operations review. Non-standard wording is referred to Legal outside the automated approval.
• Policy acknowledgments require confirmation that the correct policy and employee identity are represented.
• An Unverified employee record cannot be approved.
• Waivers and Not Applicable statuses require a documented human decision and are not set by the script.

The reviewer action records the signed-in reviewer email, verification date, task status, document status, and log event. Returning a document requires a reason and sends that reason to the employee.

The scheduled reminder rules use calendar days:

Reviewer assignments should use named, authorized users in the script allowlist. Group addresses may be used for notifications, but the actual approving account must still be identifiable.

If a manager is missing or has an invalid email address, the escalation goes to the People Operations administrator. If a reviewer is unavailable, an administrator updates the task’s Reviewer Email to an approved delegate. The history remains in the log.

Step 7: Add Documents and File Management

The Drive structure is based on sensitivity rather than placing every document under one broadly shared employee folder.

Restricted Employee Records/
  EMP-0042 - Taylor Morgan/
    EMP-0042_ID_20260715_v01.pdf
    EMP-0042_TAX_20260715_v01.pdf

Compliance Records/
  EMP-0042 - Taylor Morgan/
    EMP-0042_CERT_20260715_v01.pdf

General Employee Records/
  EMP-0042 - Taylor Morgan/
    EMP-0042_POLICY_20260715_v01.pdf

Only the automation account and approved roles receive access to the corresponding roots. Managers receive overdue status notifications, not file links. Employees do not automatically receive access to the internal storage folder merely because they uploaded the source document.

When a replacement is submitted, the script creates the next version and marks the preceding current version Superseded. It does not immediately delete the older file. The organization’s approved retention schedule determines whether the prior version must be retained, archived, or destroyed.

Retention Review Date is a review trigger rather than an automatic deletion instruction. People Operations or Legal confirms that litigation holds, investigations, statutory requirements, contractual duties, and other restrictions have been considered before disposal.

The script accepts one file per submission. This avoids ambiguity about which requirement and version apply to a particular attachment. Multiple-document packages should be split by the employee or routed to Manual Review.

Step 8: Add Reporting and Operational Views

Create restricted filter views or separate formula-driven dashboard sections for:

• New and open requirements
• Submitted documents awaiting review
• Overdue employee tasks
• Returned documents awaiting replacement
• Tasks by employee and reviewer
• Certifications expiring within 30, 60, or 90 days
• Employee records with Unverified status
• Manual-review form submissions
• Failed automation runs
• Documents due for retention review
• Recently verified documents
• Average submission-to-verification time

Example Google Sheets formulas include:

=FILTER(Tasks!A:X,Tasks!F:F="Overdue")

=FILTER(Tasks!A:X,Tasks!F:F="Submitted")

=FILTER(Documents!A:AD,Documents!V:V="Due")

=COUNTIF(Tasks!F:F,"Open")

=COUNTIF(Tasks!F:F,"Submitted")

=COUNTIF(Tasks!F:F,"Overdue")

Formula separators and column letters may vary if the workbook structure or locale is changed. The dashboard should be owned by People Operations, refreshed from the source sheets, and checked after any field insertion.

An alert threshold was set for any Failed response older than two hours, any Manual Review response older than one business day, or more than ten Submitted tasks awaiting reviewer action.

Step 9: Add Security and Governance Controls

• Use least-privilege access for the form, workbook, script, and each Drive root.
• Keep uploaded identity and tax records outside folders available to managers or compliance users who do not need them.
• Restrict link sharing to the organization and disable public or anonymous links.
• Store any optional API key in Apps Script Properties, never in a worksheet or source-code constant.
• Review form editors because editors may have access to responses and uploaded files.
• Protect roster-controlled fields and automation-generated columns.
• Use a functional automation account governed by the organization, not a personal account.
• Remove former employees from groups, shared folders, script projects, and reviewer allowlists promptly.
• Retain AutomationLog entries according to the approved audit schedule.
• Export or otherwise back up configuration and non-file record data according to the organization’s continuity process.
• Do not send identity numbers, tax data, or raw sensitive files in reminder messages.
• Do not submit restricted documents to an AI service unless privacy, security, contractual, residency, and retention requirements have been approved.
• Keep final document acceptance, policy exceptions, and destruction authorization under human control.

The organization must determine which employment, privacy, tax, identity, professional licensing, and record-retention requirements apply in each operating jurisdiction. The workflow implements approved rules but does not determine the law.

Step 10: Deploy and Test

1. Build the form and workbook in a restricted test location.
2. Populate Employees with fabricated records and use non-sensitive sample files.
3. Replace every script placeholder and run setupAutomation.
4. Authorize the requested Google permissions using the controlled automation account.
5. Confirm that one form-submission trigger, one hourly retry trigger, and one daily reminder trigger exist.
6. Submit normal, duplicate, invalid, replacement, and oversized test cases.
7. Verify folder routing and confirm that users without permission cannot open copied document links.
8. Test approval and return actions with an authorized reviewer and an unauthorized test user.
9. Temporarily configure short due dates to test reminders and escalations.
10. Run user acceptance testing with one People Operations coordinator and one compliance reviewer.
11. Pilot the workflow with a small internal group before sending it to all employees.
12. Keep the existing tracker available in read-only mode during the pilot.
13. Document rollback steps, including disabling triggers, closing the form, and directing employees to an approved contingency channel.
14. After acceptance, activate the form, communicate the new submission process, and monitor every run during the initial rollout.

Code and Configuration

The following complete Google Apps Script belongs in the Apps Script project bound to the Google Sheets response workbook. Open the linked spreadsheet, open its Apps Script editor, replace the default editor contents, and paste this code into a file named employee_document_workflow.gs.

Replace the spreadsheet ID, folder IDs, email addresses, and approved reviewer list. Update the form labels if they differ from the values in FORM_FIELDS.

const CONFIG = Object.freeze({
  SPREADSHEET_ID: 'YOUR_SPREADSHEET_ID',
  RESPONSE_SHEET_NAME: 'Form Responses 1',
  ADMIN_EMAIL: 'YOUR_EMAIL_ADDRESS',
  HR_OWNER_EMAIL: 'YOUR_EMAIL_ADDRESS',
  GENERAL_FOLDER_ID: 'YOUR_GENERAL_FOLDER_ID',
  COMPLIANCE_FOLDER_ID: 'YOUR_COMPLIANCE_FOLDER_ID',
  RESTRICTED_FOLDER_ID: 'YOUR_RESTRICTED_FOLDER_ID',
  APPROVER_EMAILS: [
    'YOUR_EMAIL_ADDRESS'
  ],
  TIME_ZONE: 'Etc/UTC',
  MAX_FILE_BYTES: 10 * 1024 * 1024,
  MAX_RETRIES: 3,
  REVIEW_REMINDER_DAYS: 2,
  REVIEW_ESCALATION_DAYS: 4
});

const FORM_FIELDS = Object.freeze({
  TIMESTAMP: 'Timestamp',
  COLLECTED_EMAIL: 'Email Address',
  EMPLOYEE_ID: 'Employee ID',
  LEGAL_NAME: 'Legal Name',
  WORK_EMAIL: 'Work Email',
  DEPARTMENT: 'Department',
  START_DATE: 'Start Date',
  DOCUMENT_TYPE: 'Document Type',
  EXPIRY_DATE: 'Document Expiry Date',
  FILE_UPLOAD: 'Document File',
  CONFIRMATION: 'Submission Confirmation'
});

const SHEETS = Object.freeze({
  EMPLOYEES: 'Employees',
  REQUIREMENTS: 'Requirements',
  TASKS: 'Tasks',
  DOCUMENTS: 'Documents',
  LOG: 'AutomationLog'
});

const HEADERS = Object.freeze({
  EMPLOYEES: [
    'Employee ID', 'Legal Name', 'Work Email', 'Manager Email',
    'Department', 'Start Date', 'Employment Status',
    'Collection Status', 'Owner Email', 'Created At',
    'Updated At', 'Last Reminder At', 'Escalation Level', 'Notes'
  ],
  REQUIREMENTS: [
    'Requirement Code', 'Document Type', 'Applies To Department',
    'Required', 'Due Offset Days', 'Reminder Lead Days',
    'Reminder Interval Days', 'Escalation After Days',
    'Destination Folder Key', 'Allowed MIME Types',
    'Retention Rule', 'Retention Days', 'Active', 'Reviewer Email'
  ],
  TASKS: [
    'Task ID', 'Task Key', 'Employee ID', 'Requirement Code',
    'Document Type', 'Status', 'Owner Email', 'Reviewer Email',
    'Due Date', 'Next Reminder Date', 'Reminder Count',
    'Last Reminder At', 'Escalation Level', 'Last Escalated At',
    'Document ID', 'Submitted At', 'Verified At', 'Exception Type',
    'Automation Status', 'Last Automation Run', 'Error Message',
    'Created At', 'Updated At', 'Notes'
  ],
  DOCUMENTS: [
    'Document ID', 'Employee ID', 'Requirement Code', 'Document Type',
    'Version', 'Status', 'Original File Name', 'Stored File Name',
    'File ID', 'Document Link', 'Folder ID', 'MIME Type', 'Size Bytes',
    'Submitted At', 'Submitted By', 'Expiry Date', 'Verified By',
    'Verified At', 'Rejection Reason', 'Retention Rule',
    'Retention Review Date', 'Retention Status', 'External System ID',
    'AI Status', 'AI Confidence', 'AI Extraction JSON', 'Last AI Run',
    'Error Message', 'Created At', 'Updated At'
  ],
  LOG: [
    'Timestamp', 'Severity', 'Event Key', 'Record Type', 'Record ID',
    'Action', 'Status', 'Message', 'Retry Count'
  ],
  RESPONSE_TRACKING: [
    'Automation Status', 'Document Record ID',
    'Last Automation Run', 'Retry Count', 'Automation Error'
  ]
});

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Employee Documents')
    .addItem('Set up automation', 'setupAutomation')
    .addItem('Generate tasks for all employees', 'generateTasksForAllEmployees')
    .addSeparator()
    .addItem('Verify selected task', 'verifySelectedTask')
    .addItem('Return selected task', 'returnSelectedTask')
    .addSeparator()
    .addItem('Run reminders now', 'dailyReminderAndEscalation')
    .addItem('Retry failed submissions', 'retryFailedSubmissions')
    .addToUi();
}

function setupAutomation() {
  validateConfiguration_();

  const spreadsheet = getSpreadsheet_();
  ensureSheet_(spreadsheet, SHEETS.EMPLOYEES, HEADERS.EMPLOYEES);
  ensureSheet_(spreadsheet, SHEETS.REQUIREMENTS, HEADERS.REQUIREMENTS);
  ensureSheet_(spreadsheet, SHEETS.TASKS, HEADERS.TASKS);
  ensureSheet_(spreadsheet, SHEETS.DOCUMENTS, HEADERS.DOCUMENTS);
  ensureSheet_(spreadsheet, SHEETS.LOG, HEADERS.LOG);

  const responseSheet = spreadsheet.getSheetByName(CONFIG.RESPONSE_SHEET_NAME);
  if (!responseSheet) {
    throw new Error(
      'Response sheet not found. Link the Google Form to this workbook ' +
      'or update CONFIG.RESPONSE_SHEET_NAME.'
    );
  }

  ensureHeaders_(responseSheet, HEADERS.RESPONSE_TRACKING);
  seedRequirements_();
  installTriggers_();

  logEvent_(
    'INFO',
    'SETUP',
    'Configuration',
    CONFIG.SPREADSHEET_ID,
    'Setup',
    'SUCCESS',
    'Sheets, headers, requirements, and triggers were checked.',
    0
  );

  SpreadsheetApp.getUi().alert(
    'Setup complete. Review the Requirements sheet before using real data.'
  );
}

function installTriggers_() {
  const spreadsheet = getSpreadsheet_();
  const triggers = ScriptApp.getProjectTriggers();
  const handlers = {};

  triggers.forEach(function(trigger) {
    handlers[trigger.getHandlerFunction()] = true;
  });

  if (!handlers.onFormSubmitInstalled) {
    ScriptApp.newTrigger('onFormSubmitInstalled')
      .forSpreadsheet(spreadsheet)
      .onFormSubmit()
      .create();
  }

  if (!handlers.retryFailedSubmissions) {
    ScriptApp.newTrigger('retryFailedSubmissions')
      .timeBased()
      .everyHours(1)
      .create();
  }

  if (!handlers.dailyReminderAndEscalation) {
    ScriptApp.newTrigger('dailyReminderAndEscalation')
      .timeBased()
      .atHour(8)
      .everyDays(1)
      .create();
  }
}

function onFormSubmitInstalled(event) {
  if (!event || !event.range) {
    throw new Error('This function must run from an installable form trigger.');
  }

  if (event.range.getSheet().getName() !== CONFIG.RESPONSE_SHEET_NAME) {
    return;
  }

  processSubmissionRow_(event.range.getSheet(), event.range.getRow());
}

function processSubmissionRow_(responseSheet, rowNumber) {
  const lock = LockService.getScriptLock();

  if (!lock.tryLock(30000)) {
    throw new Error('Could not obtain the automation lock.');
  }

  try {
    ensureHeaders_(responseSheet, HEADERS.RESPONSE_TRACKING);

    let response = getRowObject_(responseSheet, rowNumber);
    const currentStatus = String(response['Automation Status'] || '').toUpperCase();

    if (currentStatus === 'PROCESSED') {
      return {
        status: 'PROCESSED',
        documentId: response['Document Record ID']
      };
    }

    const eventKey = [
      CONFIG.SPREADSHEET_ID,
      responseSheet.getSheetId(),
      rowNumber
    ].join(':');

    setFieldsAtRow_(responseSheet, rowNumber, {
      'Automation Status': 'PROCESSING',
      'Last Automation Run': new Date(),
      'Automation Error': ''
    });

    response = getRowObject_(responseSheet, rowNumber);

    try {
      const input = validateSubmission_(response);
      const employeeResult = getOrCreateEmployee_(input);
      const employee = employeeResult.employee;

      ensureTasksForEmployee_(employee);

      const requirement = getRequirementByDocumentType_(input.documentType);
      if (!requirement) {
        throw validationError_(
          'No active requirement matches document type: ' + input.documentType
        );
      }

      const existingDocument = findObjectByValue_(
        getSheet_(SHEETS.DOCUMENTS),
        'File ID',
        input.fileId
      );

      if (existingDocument) {
        markTaskSubmitted_(
          employee['Employee ID'],
          requirement,
          existingDocument['Document ID'],
          existingDocument['Submitted At'] || new Date()
        );

        recalculateEmployeeStatus_(employee['Employee ID']);

        setFieldsAtRow_(responseSheet, rowNumber, {
          'Automation Status': 'PROCESSED',
          'Document Record ID': existingDocument['Document ID'],
          'Last Automation Run': new Date(),
          'Automation Error': ''
        });

        logEvent_(
          'INFO',
          eventKey,
          'Document',
          existingDocument['Document ID'],
          'Duplicate event reconciliation',
          'SUCCESS',
          'Existing Drive file ID was reused without creating a duplicate.',
          Number(response['Retry Count'] || 0)
        );

        return {
          status: 'PROCESSED',
          documentId: existingDocument['Document ID']
        };
      }

      const file = DriveApp.getFileById(input.fileId);
      validateFile_(file, requirement);

      const destinationFolder = getOrCreateEmployeeFolder_(
        requirement['Destination Folder Key'],
        employee['Employee ID'],
        employee['Legal Name']
      );

      const version = getNextDocumentVersion_(
        employee['Employee ID'],
        requirement['Requirement Code']
      );

      const originalFileName = file.getName();
      const storedFileName = buildStoredFileName_(
        employee['Employee ID'],
        requirement['Requirement Code'],
        version,
        originalFileName,
        file.getMimeType()
      );

      file.moveTo(destinationFolder);
      file.setName(storedFileName);

      supersedePriorDocuments_(
        employee['Employee ID'],
        requirement['Requirement Code']
      );

      const now = new Date();
      const documentId = makeId_('DOC');
      const retentionDays = Number(requirement['Retention Days'] || 0);
      const retentionReviewDate = retentionDays > 0
        ? addDays_(now, retentionDays)
        : '';

      appendObject_(getSheet_(SHEETS.DOCUMENTS), {
        'Document ID': documentId,
        'Employee ID': employee['Employee ID'],
        'Requirement Code': requirement['Requirement Code'],
        'Document Type': requirement['Document Type'],
        'Version': version,
        'Status': 'Pending Review',
        'Original File Name': originalFileName,
        'Stored File Name': storedFileName,
        'File ID': file.getId(),
        'Document Link': file.getUrl(),
        'Folder ID': destinationFolder.getId(),
        'MIME Type': file.getMimeType(),
        'Size Bytes': file.getSize(),
        'Submitted At': now,
        'Submitted By': input.collectedEmail,
        'Expiry Date': input.expiryDate || '',
        'Verified By': '',
        'Verified At': '',
        'Rejection Reason': '',
        'Retention Rule': requirement['Retention Rule'],
        'Retention Review Date': retentionReviewDate,
        'Retention Status': retentionReviewDate ? 'Scheduled' : 'Pending Date',
        'External System ID': '',
        'AI Status': '',
        'AI Confidence': '',
        'AI Extraction JSON': '',
        'Last AI Run': '',
        'Error Message': '',
        'Created At': now,
        'Updated At': now
      });

      markTaskSubmitted_(
        employee['Employee ID'],
        requirement,
        documentId,
        now
      );

      recalculateEmployeeStatus_(employee['Employee ID']);

      setFieldsAtRow_(responseSheet, rowNumber, {
        'Automation Status': 'PROCESSED',
        'Document Record ID': documentId,
        'Last Automation Run': now,
        'Automation Error': ''
      });

      sendSubmissionNotifications_(
        employee,
        requirement,
        documentId,
        employeeResult.isNew
      );

      logEvent_(
        'INFO',
        eventKey,
        'Document',
        documentId,
        'Form submission processing',
        'SUCCESS',
        'Document stored, registered, and linked to its task.',
        Number(response['Retry Count'] || 0)
      );

      return {
        status: 'PROCESSED',
        documentId: documentId
      };
    } catch (error) {
      const isValidation = Boolean(error.isValidationError);
      const oldRetryCount = Number(response['Retry Count'] || 0);
      const newRetryCount = isValidation
        ? oldRetryCount
        : oldRetryCount + 1;
      const status = isValidation ? 'MANUAL_REVIEW' : 'FAILED';
      const message = safeErrorMessage_(error);

      setFieldsAtRow_(responseSheet, rowNumber, {
        'Automation Status': status,
        'Last Automation Run': new Date(),
        'Retry Count': newRetryCount,
        'Automation Error': message
      });

      logEvent_(
        isValidation ? 'WARNING' : 'ERROR',
        eventKey,
        'Form Response',
        String(rowNumber),
        'Form submission processing',
        status,
        message,
        newRetryCount
      );

      sendMailSafe_(
        CONFIG.ADMIN_EMAIL,
        'Employee document automation requires attention',
        [
          'Response row: ' + rowNumber,
          'Status: ' + status,
          'Error: ' + message,
          'Open the restricted response workbook to review the record.'
        ].join('\n'),
        ''
      );

      return {
        status: status,
        error: message
      };
    }
  } finally {
    lock.releaseLock();
  }
}

function validateSubmission_(response) {
  const employeeId = cleanText_(response[FORM_FIELDS.EMPLOYEE_ID]);
  const legalName = cleanText_(response[FORM_FIELDS.LEGAL_NAME]);
  const workEmail = normalizeEmail_(response[FORM_FIELDS.WORK_EMAIL]);
  const collectedEmail = normalizeEmail_(
    response[FORM_FIELDS.COLLECTED_EMAIL]
  );
  const department = cleanText_(response[FORM_FIELDS.DEPARTMENT]);
  const documentType = cleanText_(response[FORM_FIELDS.DOCUMENT_TYPE]);
  const confirmation = cleanText_(response[FORM_FIELDS.CONFIRMATION]);
  const fileIds = extractDriveFileIds_(response[FORM_FIELDS.FILE_UPLOAD]);

  if (!/^EMP-[0-9]{4}$/.test(employeeId)) {
    throw validationError_('Employee ID must match EMP-0000.');
  }

  if (!legalName) {
    throw validationError_('Legal Name is required.');
  }

  if (!isValidEmail_(workEmail) || !isValidEmail_(collectedEmail)) {
    throw validationError_('A valid work and collected email are required.');
  }

  if (!department || !documentType) {
    throw validationError_('Department and Document Type are required.');
  }

  if (confirmation.toLowerCase().indexOf('i confirm') === -1) {
    throw validationError_('Submission confirmation was not accepted.');
  }

  if (fileIds.length !== 1) {
    throw validationError_('Exactly one uploaded file is required.');
  }

  return {
    employeeId: employeeId,
    legalName: legalName,
    workEmail: workEmail,
    collectedEmail: collectedEmail,
    department: department,
    startDate: toDateOrBlank_(response[FORM_FIELDS.START_DATE]),
    documentType: documentType,
    expiryDate: toDateOrBlank_(response[FORM_FIELDS.EXPIRY_DATE]),
    fileId: fileIds[0]
  };
}

function getOrCreateEmployee_(input) {
  const sheet = getSheet_(SHEETS.EMPLOYEES);
  const existing = findObjectByValue_(
    sheet,
    'Employee ID',
    input.employeeId
  );

  if (existing) {
    const rosterEmail = normalizeEmail_(existing['Work Email']);

    if (input.workEmail !== rosterEmail ||
        input.collectedEmail !== rosterEmail) {
      throw validationError_(
        'Submitted or collected email does not match the employee roster.'
      );
    }

    setFieldsAtRow_(sheet, existing._row, {
      'Updated At': new Date()
    });

    existing['Updated At'] = new Date();

    return {
      employee: existing,
      isNew: false
    };
  }

  const now = new Date();
  const employee = {
    'Employee ID': input.employeeId,
    'Legal Name': input.legalName,
    'Work Email': input.workEmail,
    'Manager Email': '',
    'Department': input.department,
    'Start Date': input.startDate || '',
    'Employment Status': 'Unverified',
    'Collection Status': 'Exception',
    'Owner Email': CONFIG.HR_OWNER_EMAIL,
    'Created At': now,
    'Updated At': now,
    'Last Reminder At': '',
    'Escalation Level': 0,
    'Notes': 'Created from form submission. People Operations validation required.'
  };

  appendObject_(sheet, employee);
  employee._row = sheet.getLastRow();

  return {
    employee: employee,
    isNew: true
  };
}

function ensureTasksForEmployee_(employee) {
  const taskSheet = getSheet_(SHEETS.TASKS);
  const requirements = getActiveRequirements_();
  const startDate = toDateOrBlank_(employee['Start Date']) || new Date();

  requirements.forEach(function(requirement) {
    const appliesTo = cleanText_(
      requirement['Applies To Department']
    ).toUpperCase();

    const department = cleanText_(
      employee['Department']
    ).toUpperCase();

    if (appliesTo !== 'ALL' && appliesTo !== department) {
      return;
    }

    if (!isTrue_(requirement['Required'])) {
      return;
    }

    const taskKey = [
      employee['Employee ID'],
      requirement['Requirement Code']
    ].join('|');

    const existingTask = findObjectByValue_(
      taskSheet,
      'Task Key',
      taskKey
    );

    if (existingTask) {
      return;
    }

    const dueDate = addDays_(
      startDate,
      Number(requirement['Due Offset Days'] || 0)
    );

    const reminderDate = addDays_(
      dueDate,
      -Number(requirement['Reminder Lead Days'] || 0)
    );

    const now = new Date();

    appendObject_(taskSheet, {
      'Task ID': makeId_('TSK'),
      'Task Key': taskKey,
      'Employee ID': employee['Employee ID'],
      'Requirement Code': requirement['Requirement Code'],
      'Document Type': requirement['Document Type'],
      'Status': 'Open',
      'Owner Email': employee['Work Email'],
      'Reviewer Email': requirement['Reviewer Email'],
      'Due Date': dueDate,
      'Next Reminder Date': reminderDate,
      'Reminder Count': 0,
      'Last Reminder At': '',
      'Escalation Level': 0,
      'Last Escalated At': '',
      'Document ID': '',
      'Submitted At': '',
      'Verified At': '',
      'Exception Type': '',
      'Automation Status': 'OK',
      'Last Automation Run': now,
      'Error Message': '',
      'Created At': now,
      'Updated At': now,
      'Notes': ''
    });
  });
}

function generateTasksForAllEmployees() {
  const employees = getObjects_(getSheet_(SHEETS.EMPLOYEES));

  employees.forEach(function(employee) {
    if (cleanText_(employee['Employment Status']).toUpperCase() !== 'SEPARATED') {
      ensureTasksForEmployee_(employee);
      recalculateEmployeeStatus_(employee['Employee ID']);
    }
  });

  SpreadsheetApp.getUi().alert(
    'Requirement task generation completed.'
  );
}

function markTaskSubmitted_(employeeId, requirement, documentId, submittedAt) {
  const taskSheet = getSheet_(SHEETS.TASKS);
  const taskKey = [
    employeeId,
    requirement['Requirement Code']
  ].join('|');

  let task = findObjectByValue_(taskSheet, 'Task Key', taskKey);

  if (!task) {
    const employee = findObjectByValue_(
      getSheet_(SHEETS.EMPLOYEES),
      'Employee ID',
      employeeId
    );

    ensureTasksForEmployee_(employee);
    task = findObjectByValue_(taskSheet, 'Task Key', taskKey);
  }

  if (!task) {
    throw new Error('The requirement task could not be created.');
  }

  setFieldsAtRow_(taskSheet, task._row, {
    'Status': 'Submitted',
    'Document ID': documentId,
    'Submitted At': submittedAt,
    'Next Reminder Date': addDays_(
      submittedAt,
      CONFIG.REVIEW_REMINDER_DAYS
    ),
    'Escalation Level': 0,
    'Last Escalated At': '',
    'Exception Type': '',
    'Automation Status': 'OK',
    'Last Automation Run': new Date(),
    'Error Message': '',
    'Updated At': new Date()
  });
}

function validateFile_(file, requirement) {
  const allowed = cleanText_(
    requirement['Allowed MIME Types']
  ).split(';').map(function(value) {
    return cleanText_(value).toLowerCase();
  }).filter(String);

  const mimeType = cleanText_(file.getMimeType()).toLowerCase();

  if (allowed.length > 0 && allowed.indexOf(mimeType) === -1) {
    throw validationError_(
      'File type ' + mimeType + ' is not allowed for this requirement.'
    );
  }

  if (file.getSize() > CONFIG.MAX_FILE_BYTES) {
    throw validationError_(
      'The uploaded file exceeds the configured maximum size.'
    );
  }
}

function getOrCreateEmployeeFolder_(folderKey, employeeId, legalName) {
  const rootFolder = getRootFolder_(folderKey);
  const folderName = sanitizeFileName_(
    employeeId + ' - ' + legalName
  );
  const existingFolders = rootFolder.getFoldersByName(folderName);

  if (existingFolders.hasNext()) {
    return existingFolders.next();
  }

  return rootFolder.createFolder(folderName);
}

function getRootFolder_(folderKey) {
  const key = cleanText_(folderKey).toUpperCase();
  let folderId = '';

  if (key === 'GENERAL') {
    folderId = CONFIG.GENERAL_FOLDER_ID;
  } else if (key === 'COMPLIANCE') {
    folderId = CONFIG.COMPLIANCE_FOLDER_ID;
  } else if (key === 'RESTRICTED') {
    folderId = CONFIG.RESTRICTED_FOLDER_ID;
  } else {
    throw new Error('Unknown destination folder key: ' + folderKey);
  }

  return DriveApp.getFolderById(folderId);
}

function getNextDocumentVersion_(employeeId, requirementCode) {
  const documents = getObjects_(getSheet_(SHEETS.DOCUMENTS));
  let highestVersion = 0;

  documents.forEach(function(document) {
    if (cleanText_(document['Employee ID']) === employeeId &&
        cleanText_(document['Requirement Code']) === requirementCode) {
      highestVersion = Math.max(
        highestVersion,
        Number(document['Version'] || 0)
      );
    }
  });

  return highestVersion + 1;
}

function supersedePriorDocuments_(employeeId, requirementCode) {
  const sheet = getSheet_(SHEETS.DOCUMENTS);
  const documents = getObjects_(sheet);

  documents.forEach(function(document) {
    const status = cleanText_(document['Status']);

    if (cleanText_(document['Employee ID']) === employeeId &&
        cleanText_(document['Requirement Code']) === requirementCode &&
        ['Pending Review', 'Verified'].indexOf(status) !== -1) {
      setFieldsAtRow_(sheet, document._row, {
        'Status': 'Superseded',
        'Updated At': new Date()
      });
    }
  });
}

function buildStoredFileName_(
  employeeId,
  requirementCode,
  version,
  originalFileName,
  mimeType
) {
  const datePart = Utilities.formatDate(
    new Date(),
    CONFIG.TIME_ZONE,
    'yyyyMMdd'
  );

  const versionPart = ('0' + version).slice(-2);
  const extension = getFileExtension_(originalFileName, mimeType);

  return sanitizeFileName_(
    [
      employeeId,
      requirementCode,
      datePart,
      'v' + versionPart
    ].join('_') + extension
  );
}

function getFileExtension_(fileName, mimeType) {
  const match = cleanText_(fileName).match(/(\.[A-Za-z0-9]{1,8})$/);

  if (match) {
    return match[1].toLowerCase();
  }

  const extensions = {
    'application/pdf': '.pdf',
    'image/jpeg': '.jpg',
    'image/png': '.png'
  };

  return extensions[mimeType] || '';
}

function sendSubmissionNotifications_(
  employee,
  requirement,
  documentId,
  isNewEmployee
) {
  sendMailSafe_(
    employee['Work Email'],
    'Employee document received',
    [
      'Your ' + requirement['Document Type'] + ' was received.',
      'Reference: ' + documentId,
      'People Operations will contact you if a correction is required.',
      'For privacy, this message does not include the uploaded file.'
    ].join('\n'),
    ''
  );

  const reviewer = normalizeEmail_(requirement['Reviewer Email']);

  if (isValidEmail_(reviewer)) {
    sendMailSafe_(
      reviewer,
      'Employee document awaiting review',
      [
        'A ' + requirement['Document Type'] + ' is awaiting review.',
        'Employee ID: ' + employee['Employee ID'],
        'Document reference: ' + documentId,
        'Open the restricted task register to complete the review.'
      ].join('\n'),
      ''
    );
  }

  if (isNewEmployee) {
    sendMailSafe_(
      CONFIG.ADMIN_EMAIL,
      'Unverified employee record created',
      [
        'Employee ID: ' + employee['Employee ID'],
        'The form submission was stored, but the employee must be',
        'validated against the authoritative roster before approval.'
      ].join('\n'),
      ''
    );
  }
}

function dailyReminderAndEscalation() {
  const taskSheet = getSheet_(SHEETS.TASKS);
  const tasks = getObjects_(taskSheet);
  const employees = getObjects_(getSheet_(SHEETS.EMPLOYEES));
  const employeeMap = indexBy_(employees, 'Employee ID');
  const requirementMap = indexBy_(
    getActiveRequirements_(),
    'Requirement Code'
  );
  const now = new Date();

  tasks.forEach(function(task) {
    const status = cleanText_(task['Status']);
    const employee = employeeMap[cleanText_(task['Employee ID'])];
    const requirement = requirementMap[
      cleanText_(task['Requirement Code'])
    ];

    if (!employee || !requirement) {
      return;
    }

    try {
      if (['Open', 'Returned', 'Overdue'].indexOf(status) !== -1) {
        processEmployeeReminder_(
          taskSheet,
          task,
          employee,
          requirement,
          now
        );
      } else if (status === 'Submitted') {
        processReviewerReminder_(
          taskSheet,
          task,
          employee,
          now
        );
      }
    } catch (error) {
      setFieldsAtRow_(taskSheet, task._row, {
        'Automation Status': 'Error',
        'Last Automation Run': now,
        'Error Message': safeErrorMessage_(error),
        'Updated At': now
      });

      logEvent_(
        'ERROR',
        'TASK:' + task['Task ID'],
        'Task',
        task['Task ID'],
        'Reminder and escalation',
        'FAILED',
        safeErrorMessage_(error),
        0
      );
    }
  });

  markRetentionReviews_();
}

function processEmployeeReminder_(
  taskSheet,
  task,
  employee,
  requirement,
  now
) {
  const dueDate = toDateOrBlank_(task['Due Date']);
  const nextReminder = toDateOrBlank_(task['Next Reminder Date']);
  let currentStatus = cleanText_(task['Status']);

  if (dueDate && startOfDay_(now) > startOfDay_(dueDate)) {
    currentStatus = 'Overdue';

    setFieldsAtRow_(taskSheet, task._row, {
      'Status': 'Overdue',
      'Updated At': now
    });
  }

  if (nextReminder && startOfDay_(now) >= startOfDay_(nextReminder)) {
    const sent = sendMailSafe_(
      employee['Work Email'],
      'Employee document action required',
      [
        'Document requirement: ' + task['Document Type'],
        'Current status: ' + currentStatus,
        'Due date: ' + formatDate_(dueDate),
        'Submit the document through the approved employee document form.',
        'Do not reply to this message with an attachment.'
      ].join('\n'),
      ''
    );

    if (sent) {
      const interval = Number(
        requirement['Reminder Interval Days'] || 3
      );

      setFieldsAtRow_(taskSheet, task._row, {
        'Reminder Count': Number(task['Reminder Count'] || 0) + 1,
        'Last Reminder At': now,
        'Next Reminder Date': addDays_(now, interval),
        'Automation Status': 'OK',
        'Last Automation Run': now,
        'Error Message': '',
        'Updated At': now
      });
    }
  }

  if (!dueDate) {
    return;
  }

  const daysOverdue = daysBetween_(dueDate, now);
  const escalationAfter = Number(
    requirement['Escalation After Days'] || 0
  );
  const escalationLevel = Number(task['Escalation Level'] || 0);

  if (daysOverdue >= escalationAfter &&
      escalationAfter >= 0 &&
      escalationLevel === 0) {
    const managerEmail = normalizeEmail_(employee['Manager Email']);
    const recipient = isValidEmail_(managerEmail)
      ? managerEmail
      : CONFIG.ADMIN_EMAIL;
    const cc = recipient === CONFIG.ADMIN_EMAIL
      ? ''
      : CONFIG.ADMIN_EMAIL;

    const sent = sendMailSafe_(
      recipient,
      'Overdue employee document requirement',
      [
        'Employee ID: ' + employee['Employee ID'],
        'Requirement: ' + task['Document Type'],
        'Due date: ' + formatDate_(dueDate),
        'This notice contains status only and no document attachment.'
      ].join('\n'),
      cc
    );

    if (sent) {
      setFieldsAtRow_(taskSheet, task._row, {
        'Escalation Level': 1,
        'Last Escalated At': now,
        'Updated At': now
      });
    }
  }
}

function processReviewerReminder_(taskSheet, task, employee, now) {
  const nextReminder = toDateOrBlank_(task['Next Reminder Date']);
  const submittedAt = toDateOrBlank_(task['Submitted At']);
  const reviewer = normalizeEmail_(task['Reviewer Email']);

  if (nextReminder &&
      startOfDay_(now) >= startOfDay_(nextReminder) &&
      isValidEmail_(reviewer)) {
    const sent = sendMailSafe_(
      reviewer,
      'Employee document review reminder',
      [
        'Employee ID: ' + employee['Employee ID'],
        'Requirement: ' + task['Document Type'],
        'Task ID: ' + task['Task ID'],
        'Open the restricted task register to review the document.'
      ].join('\n'),
      ''
    );

    if (sent) {
      setFieldsAtRow_(taskSheet, task._row, {
        'Reminder Count': Number(task['Reminder Count'] || 0) + 1,
        'Last Reminder At': now,
        'Next Reminder Date': addDays_(
          now,
          CONFIG.REVIEW_REMINDER_DAYS
        ),
        'Updated At': now
      });
    }
  }

  if (submittedAt &&
      daysBetween_(submittedAt, now) >=
        CONFIG.REVIEW_ESCALATION_DAYS &&
      Number(task['Escalation Level'] || 0) === 0) {
    const sent = sendMailSafe_(
      CONFIG.ADMIN_EMAIL,
      'Employee document review is overdue',
      [
        'Employee ID: ' + employee['Employee ID'],
        'Requirement: ' + task['Document Type'],
        'Task ID: ' + task['Task ID'],
        'Assigned reviewer: ' + reviewer
      ].join('\n'),
      ''
    );

    if (sent) {
      setFieldsAtRow_(taskSheet, task._row, {
        'Escalation Level': 1,
        'Last Escalated At': now,
        'Updated At': now
      });
    }
  }
}

function verifySelectedTask() {
  const task = getSelectedTask_();
  assertAuthorizedApprover_();

  const employee = findObjectByValue_(
    getSheet_(SHEETS.EMPLOYEES),
    'Employee ID',
    task['Employee ID']
  );

  if (!employee) {
    throw new Error('Employee record not found.');
  }

  if (cleanText_(employee['Employment Status']) === 'Unverified') {
    throw new Error(
      'Validate the employee roster record before approving documents.'
    );
  }

  if (cleanText_(task['Status']) !== 'Submitted') {
    throw new Error('Only a Submitted task can be verified.');
  }

  const reviewer = normalizeEmail_(
    Session.getActiveUser().getEmail()
  );
  const now = new Date();

  setFieldsAtRow_(getSheet_(SHEETS.TASKS), task._row, {
    'Status': 'Verified',
    'Verified At': now,
    'Next Reminder Date': '',
    'Automation Status': 'OK',
    'Last Automation Run': now,
    'Error Message': '',
    'Updated At': now
  });

  const document = findObjectByValue_(
    getSheet_(SHEETS.DOCUMENTS),
    'Document ID',
    task['Document ID']
  );

  if (!document) {
    throw new Error('Linked document record not found.');
  }

  setFieldsAtRow_(getSheet_(SHEETS.DOCUMENTS), document._row, {
    'Status': 'Verified',
    'Verified By': reviewer,
    'Verified At': now,
    'Rejection Reason': '',
    'Updated At': now
  });

  recalculateEmployeeStatus_(task['Employee ID']);

  sendMailSafe_(
    employee['Work Email'],
    'Employee document verified',
    [
      'Your ' + task['Document Type'] + ' was verified.',
      'Reference: ' + task['Document ID'],
      'No action is required unless People Operations contacts you.'
    ].join('\n'),
    ''
  );

  logEvent_(
    'INFO',
    'APPROVAL:' + task['Task ID'],
    'Task',
    task['Task ID'],
    'Human verification',
    'SUCCESS',
    'Verified by ' + reviewer,
    0
  );

  SpreadsheetApp.getUi().alert('The selected task was verified.');
}

function returnSelectedTask() {
  const task = getSelectedTask_();
  assertAuthorizedApprover_();

  if (cleanText_(task['Status']) !== 'Submitted') {
    throw new Error('Only a Submitted task can be returned.');
  }

  const ui = SpreadsheetApp.getUi();
  const prompt = ui.prompt(
    'Return document',
    'Enter the correction reason. Do not include unnecessary sensitive data.',
    ui.ButtonSet.OK_CANCEL
  );

  if (prompt.getSelectedButton() !== ui.Button.OK) {
    return;
  }

  const reason = cleanText_(prompt.getResponseText());

  if (reason.length < 5) {
    throw new Error('A meaningful return reason is required.');
  }

  const reviewer = normalizeEmail_(
    Session.getActiveUser().getEmail()
  );
  const now = new Date();

  setFieldsAtRow_(getSheet_(SHEETS.TASKS), task._row, {
    'Status': 'Returned',
    'Next Reminder Date': now,
    'Escalation Level': 0,
    'Exception Type': 'Correction Required',
    'Automation Status': 'OK',
    'Last Automation Run': now,
    'Error Message': '',
    'Updated At': now,
    'Notes': reason
  });

  const document = findObjectByValue_(
    getSheet_(SHEETS.DOCUMENTS),
    'Document ID',
    task['Document ID']
  );

  if (document) {
    setFieldsAtRow_(getSheet_(SHEETS.DOCUMENTS), document._row, {
      'Status': 'Rejected',
      'Rejection Reason': reason,
      'Verified By': reviewer,
      'Verified At': now,
      'Updated At': now
    });
  }

  const employee = findObjectByValue_(
    getSheet_(SHEETS.EMPLOYEES),
    'Employee ID',
    task['Employee ID']
  );

  recalculateEmployeeStatus_(task['Employee ID']);

  if (employee) {
    sendMailSafe_(
      employee['Work Email'],
      'Employee document correction required',
      [
        'Document requirement: ' + task['Document Type'],
        'Correction requested: ' + reason,
        'Submit a replacement through the approved document form.',
        'Do not reply to this message with an attachment.'
      ].join('\n'),
      ''
    );
  }

  logEvent_(
    'INFO',
    'RETURN:' + task['Task ID'],
    'Task',
    task['Task ID'],
    'Human return decision',
    'SUCCESS',
    'Returned by ' + reviewer + '. Reason: ' + reason,
    0
  );

  ui.alert('The selected task was returned.');
}

function retryFailedSubmissions() {
  const sheet = getSpreadsheet_().getSheetByName(
    CONFIG.RESPONSE_SHEET_NAME
  );

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

  ensureHeaders_(sheet, HEADERS.RESPONSE_TRACKING);
  const rows = getObjects_(sheet);

  rows.forEach(function(row) {
    const status = cleanText_(
      row['Automation Status']
    ).toUpperCase();
    const retryCount = Number(row['Retry Count'] || 0);

    if (status === 'FAILED' && retryCount < CONFIG.MAX_RETRIES) {
      processSubmissionRow_(sheet, row._row);
    }
  });
}

function markRetentionReviews_() {
  const sheet = getSheet_(SHEETS.DOCUMENTS);
  const documents = getObjects_(sheet);
  const now = new Date();
  const dueReferences = [];

  documents.forEach(function(document) {
    const reviewDate = toDateOrBlank_(
      document['Retention Review Date']
    );
    const status = cleanText_(document['Retention Status']);

    if (reviewDate &&
        startOfDay_(reviewDate) <= startOfDay_(now) &&
        status !== 'Due' &&
        cleanText_(document['Status']) !== 'Disposed') {
      setFieldsAtRow_(sheet, document._row, {
        'Retention Status': 'Due',
        'Status': 'Retention Review',
        'Updated At': now
      });

      dueReferences.push(document['Document ID']);
    }
  });

  if (dueReferences.length > 0) {
    sendMailSafe_(
      CONFIG.ADMIN_EMAIL,
      'Employee documents require retention review',
      [
        'The following document records reached their review date:',
        dueReferences.join(', '),
        'Do not delete records until an authorized reviewer confirms disposition.'
      ].join('\n'),
      ''
    );
  }
}

function recalculateEmployeeStatus_(employeeId) {
  const employeeSheet = getSheet_(SHEETS.EMPLOYEES);
  const employee = findObjectByValue_(
    employeeSheet,
    'Employee ID',
    employeeId
  );

  if (!employee) {
    return;
  }

  if (cleanText_(employee['Employment Status']) === 'Unverified') {
    setFieldsAtRow_(employeeSheet, employee._row, {
      'Collection Status': 'Exception',
      'Updated At': new Date()
    });
    return;
  }

  const tasks = getObjects_(getSheet_(SHEETS.TASKS)).filter(
    function(task) {
      return cleanText_(task['Employee ID']) === employeeId;
    }
  );

  let collectionStatus = 'Not Started';

  if (tasks.length > 0) {
    const statuses = tasks.map(function(task) {
      return cleanText_(task['Status']);
    });

    const completeStatuses = ['Verified', 'Waived', 'Not Applicable'];
    const allComplete = statuses.every(function(status) {
      return completeStatuses.indexOf(status) !== -1;
    });

    const anySubmitted = statuses.indexOf('Submitted') !== -1;
    const anyOpen = statuses.some(function(status) {
      return ['Open', 'Returned', 'Overdue'].indexOf(status) !== -1;
    });

    if (allComplete) {
      collectionStatus = 'Complete';
    } else if (anySubmitted && !anyOpen) {
      collectionStatus = 'Ready for Review';
    } else {
      collectionStatus = 'In Progress';
    }
  }

  setFieldsAtRow_(employeeSheet, employee._row, {
    'Collection Status': collectionStatus,
    'Updated At': new Date()
  });
}

function seedRequirements_() {
  const sheet = getSheet_(SHEETS.REQUIREMENTS);

  if (sheet.getLastRow() > 1) {
    return;
  }

  const rows = [
    {
      'Requirement Code': 'AGREEMENT',
      'Document Type': 'Employment Agreement',
      'Applies To Department': 'ALL',
      'Required': true,
      'Due Offset Days': 3,
      'Reminder Lead Days': 2,
      'Reminder Interval Days': 3,
      'Escalation After Days': 2,
      'Destination Folder Key': 'RESTRICTED',
      'Allowed MIME Types': 'application/pdf',
      'Retention Rule': 'Apply approved employment record schedule',
      'Retention Days': 0,
      'Active': true,
      'Reviewer Email': CONFIG.HR_OWNER_EMAIL
    },
    {
      'Requirement Code': 'TAX',
      'Document Type': 'Tax Form',
      'Applies To Department': 'ALL',
      'Required': true,
      'Due Offset Days': 2,
      'Reminder Lead Days': 1,
      'Reminder Interval Days': 3,
      'Escalation After Days': 2,
      'Destination Folder Key': 'RESTRICTED',
      'Allowed MIME Types': 'application/pdf;image/jpeg;image/png',
      'Retention Rule': 'Apply approved tax record schedule',
      'Retention Days': 0,
      'Active': true,
      'Reviewer Email': CONFIG.HR_OWNER_EMAIL
    },
    {
      'Requirement Code': 'ID',
      'Document Type': 'Identity Document',
      'Applies To Department': 'ALL',
      'Required': true,
      'Due Offset Days': 2,
      'Reminder Lead Days': 1,
      'Reminder Interval Days': 3,
      'Escalation After Days': 2,
      'Destination Folder Key': 'RESTRICTED',
      'Allowed MIME Types': 'application/pdf;image/jpeg;image/png',
      'Retention Rule': 'Apply approved identity record schedule',
      'Retention Days': 0,
      'Active': true,
      'Reviewer Email': CONFIG.HR_OWNER_EMAIL
    },
    {
      'Requirement Code': 'CERT',
      'Document Type': 'Professional Certification',
      'Applies To Department': 'ALL',
      'Required': true,
      'Due Offset Days': 7,
      'Reminder Lead Days': 5,
      'Reminder Interval Days': 5,
      'Escalation After Days': 3,
      'Destination Folder Key': 'COMPLIANCE',
      'Allowed MIME Types': 'application/pdf;image/jpeg;image/png',
      'Retention Rule': 'Review at expiry and under approved schedule',
      'Retention Days': 0,
      'Active': true,
      'Reviewer Email': CONFIG.HR_OWNER_EMAIL
    },
    {
      'Requirement Code': 'POLICY',
      'Document Type': 'Policy Acknowledgment',
      'Applies To Department': 'ALL',
      'Required': true,
      'Due Offset Days': 5,
      'Reminder Lead Days': 3,
      'Reminder Interval Days': 4,
      'Escalation After Days': 3,
      'Destination Folder Key': 'GENERAL',
      'Allowed MIME Types': 'application/pdf;image/jpeg;image/png',
      'Retention Rule': 'Apply approved policy acknowledgment schedule',
      'Retention Days': 0,
      'Active': true,
      'Reviewer Email': CONFIG.HR_OWNER_EMAIL
    }
  ];

  rows.forEach(function(row) {
    appendObject_(sheet, row);
  });
}

function getActiveRequirements_() {
  return getObjects_(getSheet_(SHEETS.REQUIREMENTS)).filter(
    function(requirement) {
      return isTrue_(requirement['Active']);
    }
  );
}

function getRequirementByDocumentType_(documentType) {
  const target = cleanText_(documentType).toLowerCase();

  const matches = getActiveRequirements_().filter(
    function(requirement) {
      return cleanText_(
        requirement['Document Type']
      ).toLowerCase() === target;
    }
  );

  return matches.length ? matches[0] : null;
}

function getSelectedTask_() {
  const sheet = SpreadsheetApp.getActiveSheet();

  if (sheet.getName() !== SHEETS.TASKS) {
    throw new Error('Select a data row on the Tasks sheet.');
  }

  const row = sheet.getActiveRange().getRow();

  if (row < 2) {
    throw new Error('Select a task data row.');
  }

  return getRowObject_(sheet, row);
}

function assertAuthorizedApprover_() {
  const email = normalizeEmail_(
    Session.getActiveUser().getEmail()
  );

  const allowed = CONFIG.APPROVER_EMAILS.map(function(value) {
    return normalizeEmail_(value);
  });

  if (!email || allowed.indexOf(email) === -1) {
    throw new Error(
      'The signed-in account is not an authorized approver.'
    );
  }
}

function sendMailSafe_(recipient, subject, body, cc) {
  const to = normalizeEmail_(recipient);

  if (!isValidEmail_(to)) {
    logEvent_(
      'ERROR',
      'EMAIL',
      'Notification',
      to,
      'Send email',
      'FAILED',
      'Recipient email is invalid.',
      0
    );
    return false;
  }

  try {
    const options = {
      name: 'People Operations'
    };

    if (cc && isValidEmail_(normalizeEmail_(cc))) {
      options.cc = normalizeEmail_(cc);
    }

    GmailApp.sendEmail(to, subject, body, options);
    return true;
  } catch (error) {
    logEvent_(
      'ERROR',
      'EMAIL',
      'Notification',
      to,
      'Send email',
      'FAILED',
      safeErrorMessage_(error),
      0
    );
    return false;
  }
}

function logEvent_(
  severity,
  eventKey,
  recordType,
  recordId,
  action,
  status,
  message,
  retryCount
) {
  const sheet = getSheet_(SHEETS.LOG);

  appendObject_(sheet, {
    'Timestamp': new Date(),
    'Severity': severity,
    'Event Key': eventKey,
    'Record Type': recordType,
    'Record ID': recordId,
    'Action': action,
    'Status': status,
    'Message': cleanText_(message).slice(0, 1000),
    'Retry Count': Number(retryCount || 0)
  });
}

function validateConfiguration_() {
  const requiredValues = [
    CONFIG.SPREADSHEET_ID,
    CONFIG.ADMIN_EMAIL,
    CONFIG.HR_OWNER_EMAIL,
    CONFIG.GENERAL_FOLDER_ID,
    CONFIG.COMPLIANCE_FOLDER_ID,
    CONFIG.RESTRICTED_FOLDER_ID
  ];

  requiredValues.forEach(function(value) {
    if (!value || String(value).indexOf('YOUR_') === 0) {
      throw new Error(
        'Replace all required CONFIG placeholders before setup.'
      );
    }
  });

  if (!isValidEmail_(CONFIG.ADMIN_EMAIL) ||
      !isValidEmail_(CONFIG.HR_OWNER_EMAIL)) {
    throw new Error('Administrator and owner emails must be valid.');
  }

  if (!CONFIG.APPROVER_EMAILS.length ||
      CONFIG.APPROVER_EMAILS.some(function(email) {
        return !isValidEmail_(normalizeEmail_(email)) ||
          String(email).indexOf('YOUR_') === 0;
      })) {
    throw new Error('Configure at least one approved reviewer email.');
  }

  DriveApp.getFolderById(CONFIG.GENERAL_FOLDER_ID).getName();
  DriveApp.getFolderById(CONFIG.COMPLIANCE_FOLDER_ID).getName();
  DriveApp.getFolderById(CONFIG.RESTRICTED_FOLDER_ID).getName();
}

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

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

  if (!sheet) {
    throw new Error('Required sheet not found: ' + sheetName);
  }

  return sheet;
}

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

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

  ensureHeaders_(sheet, headers);
  return sheet;
}

function ensureHeaders_(sheet, requiredHeaders) {
  if (sheet.getLastRow() === 0) {
    sheet.getRange(1, 1, 1, requiredHeaders.length)
      .setValues([requiredHeaders]);
    sheet.setFrozenRows(1);
    return;
  }

  const lastColumn = Math.max(sheet.getLastColumn(), 1);
  const existingHeaders = sheet.getRange(1, 1, 1, lastColumn)
    .getValues()[0]
    .map(cleanText_);

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

  if (missing.length > 0) {
    sheet.getRange(
      1,
      existingHeaders.length + 1,
      1,
      missing.length
    ).setValues([missing]);
  }

  sheet.setFrozenRows(1);
}

function getObjects_(sheet) {
  if (sheet.getLastRow() < 2) {
    return [];
  }

  const values = sheet.getDataRange().getValues();
  const headers = values[0].map(cleanText_);

  return values.slice(1).map(function(row, index) {
    const object = {
      _row: index + 2
    };

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

    return object;
  });
}

function getRowObject_(sheet, rowNumber) {
  const lastColumn = sheet.getLastColumn();
  const headers = sheet.getRange(1, 1, 1, lastColumn)
    .getValues()[0]
    .map(cleanText_);
  const values = sheet.getRange(rowNumber, 1, 1, lastColumn)
    .getValues()[0];
  const object = {
    _row: rowNumber
  };

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

  return object;
}

function appendObject_(sheet, object) {
  const lastColumn = sheet.getLastColumn();
  const headers = sheet.getRange(1, 1, 1, lastColumn)
    .getValues()[0]
    .map(cleanText_);

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

  sheet.appendRow(row);
}

function setFieldsAtRow_(sheet, rowNumber, fields) {
  const lastColumn = sheet.getLastColumn();
  const headers = sheet.getRange(1, 1, 1, lastColumn)
    .getValues()[0]
    .map(cleanText_);

  Object.keys(fields).forEach(function(fieldName) {
    const index = headers.indexOf(fieldName);

    if (index === -1) {
      throw new Error(
        'Column not found on ' + sheet.getName() + ': ' + fieldName
      );
    }

    sheet.getRange(rowNumber, index + 1).setValue(fields[fieldName]);
  });
}

function findObjectByValue_(sheet, fieldName, targetValue) {
  const target = cleanText_(targetValue);
  const objects = getObjects_(sheet);

  for (let index = 0; index < objects.length; index += 1) {
    if (cleanText_(objects[index][fieldName]) === target) {
      return objects[index];
    }
  }

  return null;
}

function indexBy_(objects, fieldName) {
  const result = {};

  objects.forEach(function(object) {
    result[cleanText_(object[fieldName])] = object;
  });

  return result;
}

function extractDriveFileIds_(value) {
  const matches = cleanText_(value).match(/[-\w]{25,}/g);
  return matches ? Array.from(new Set(matches)) : [];
}

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

function sanitizeFileName_(value) {
  return cleanText_(value)
    .replace(/[\\/:*?"<>|#%{}]/g, '_')
    .replace(/\s+/g, ' ')
    .slice(0, 180);
}

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

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

  return String(value).trim();
}

function normalizeEmail_(value) {
  return cleanText_(value).toLowerCase();
}

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

function isTrue_(value) {
  return value === true ||
    ['TRUE', 'YES', '1'].indexOf(
      cleanText_(value).toUpperCase()
    ) !== -1;
}

function toDateOrBlank_(value) {
  if (!value) {
    return '';
  }

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

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

function addDays_(date, days) {
  const result = new Date(date);
  result.setDate(result.getDate() + Number(days || 0));
  return result;
}

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

function daysBetween_(earlier, later) {
  const milliseconds = startOfDay_(later).getTime() -
    startOfDay_(earlier).getTime();
  return Math.floor(milliseconds / 86400000);
}

function formatDate_(date) {
  if (!date) {
    return 'Not set';
  }

  return Utilities.formatDate(
    new Date(date),
    CONFIG.TIME_ZONE,
    'yyyy-MM-dd'
  );
}

function safeErrorMessage_(error) {
  const message = error && error.message
    ? error.message
    : String(error);

  return cleanText_(message).slice(0, 1000);
}

After pasting the code:

1. Replace every placeholder in CONFIG.
2. Set the Apps Script project time zone to the organization’s operational time zone and make CONFIG.TIME_ZONE consistent with it.
3. Save the project.
4. Select setupAutomation from the function list and run it manually.
5. Review the requested permissions and authorize them using the automation account.
6. Return to the workbook and inspect the generated sheets.
7. Review every seeded requirement. The retention text is a placeholder for an approved schedule, not a legal recommendation.
8. Submit one fabricated test response through the form.
9. Open Apps Script executions to inspect duration, status, and logged errors.
10. Confirm that duplicate triggers were not installed. The setup function checks handler names before creating them.

Duplicate execution is prevented at three levels: a script lock serializes concurrent updates, the response row records Processed status, and the Drive File ID is checked before a new document record is created.

Transient failures are marked Failed and retried hourly up to three times. Validation failures are marked Manual Review and are not retried until a person corrects the source or reference data. To recover a corrected record, change its Automation Status to Failed and keep Retry Count below the configured maximum, or use a controlled administrative function after reviewing the cause.

Likely setup errors include a renamed response sheet, mismatched form labels, inaccessible folder IDs, an unauthorized automation account, and an email header that differs from Email Address. Inspect the failed execution and the Automation Error column before retrying.

Failure Handling and Operational Reliability

Manual Review acts as the exception queue for invalid business data. Failed acts as the technical retry queue. The AutomationLog serves as a lightweight dead-letter record for events that require investigation after automated retries are exhausted.

People Operations should reconcile the following at least weekly:

• Processed form responses without a matching Document ID
• Document records without a matching Drive file
• Submitted tasks without a current document record
• Verified tasks whose document status is not Verified
• Failed responses at or above the retry limit
• Files remaining in the form upload staging folder longer than expected

A Complete Example

Taylor Morgan is represented in the employee register as EMP-0042. The trusted roster contains Taylor’s work email, Engineering department, start date, manager email, and Active employment status.

1. Taylor signs in to the managed Google account and opens the employee document form.
2. Taylor enters EMP-0042, selects Professional Certification, adds an expiry date of 2027-06-30, uploads a PDF, and accepts the confirmation statement.
3. Google Forms creates response row 187 in Form Responses 1.
4. The Apps Script trigger builds the event key using spreadsheet ID, sheet ID, and row 187.
5. The script confirms that the entered work email and collected account email match the roster email.
6. The script finds the active CERT requirement and confirms that a PDF is permitted.
7. The employee’s existing Task Key EMP-0042|CERT is found with status Open.
8. The uploaded Drive file ID is not present in Documents, so the event is not a duplicate.
9. The script finds the compliance storage root and creates EMP-0042 - Taylor Morgan because no folder exists.
10. The next certification version is 1. The file becomes EMP-0042_CERT_20260715_v01.pdf.
11. A document record such as DOC-7A42F19C58B3 is created with Pending Review status, Drive identifiers, expiry date, MIME type, size, uploader, and retention rule.
12. The task changes to Submitted and points to DOC-7A42F19C58B3.
13. Taylor receives a confirmation containing the document reference but not the file.
14. The compliance reviewer receives a status notification and opens the restricted Tasks sheet.
15. The reviewer opens the Drive link, confirms Taylor’s name, issuing body, credential number, and expiry date, and selects Verify selected task.
16. The script records the reviewer’s signed-in email and verification timestamp on both the Task and Document records.
17. The task becomes Verified. Taylor’s aggregate collection status is recalculated based on all applicable requirements.

If Taylor had submitted the same form event twice, the existing Drive File ID would have prevented a second document record. If the PDF had been unreadable, the reviewer would have selected Return selected task, entered a correction reason, and the task would have moved to Returned.

Implementation Cost

The following amounts are representative planning assumptions, not verified client pricing or results. Actual software, storage, licensing, security, and implementation costs depend on the organization’s Workspace agreement, region, volume, and controls.

Monthly maintenance labor is estimated separately in the savings model. A tool with no separate automation fee still requires monitoring, permission reviews, testing, documentation, and support.

Estimated Time and Cost Savings

The model uses these representative assumptions:

• 110 document submissions per month
• 14 minutes of current handling time per document
• 4 minutes of routine handling and human verification after automation
• 12 percent exception rate
• 10 minutes of additional handling per exception
• 3 hours of monthly maintenance
• $42 loaded hourly labor cost
• $35 incremental recurring monthly tool cost
• $6,500 modeled one-time implementation cost

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

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

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

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

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

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

Recovered time does not automatically reduce payroll. It may instead provide additional onboarding capacity, quicker review, lower overtime, less administrative follow-up, and reduced dependency on one coordinator.

Non-financial benefits include clearer ownership, more consistent naming, fewer incomplete records, better audit evidence, less attachment handling, controlled reviewer access, and more predictable communication with employees and managers.

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

Adding AI to the Automation

AI should be introduced only after the core intake, permissions, task generation, storage, reminders, and approval controls operate reliably.

Most of the value in this case comes from normal automation:

• Required form fields reduce incomplete intake.
• Exact employee matching prevents untrusted identity changes.
• Lookup rules assign requirements and storage locations.
• Drive file IDs prevent duplicate document records.
• Date rules create reminders and escalations.
• Human approval determines whether a document is accepted.

These tasks do not require AI. They are more reliable as required fields, exact matches, formulas, permissions, and deterministic workflow rules.

AI can be useful for unstructured documents where staff repeatedly read the same fields. Potential uses include extracting certification names, issuers, credential numbers, issue dates, expiry dates, and employee names for reviewer confirmation.

Identity documents, tax forms, disciplinary records, medical information, and other highly sensitive documents should be excluded from the optional AI workflow unless a separately approved architecture and service agreement permits their processing.

The Recommended AI Enhancement

The recommended enhancement extracts fields from professional certification files and places the result in the Documents sheet for human verification. It does not verify the certification, determine authenticity, change the task status, or make an employment decision.

• Trigger: Scheduled batch finds a CERT document with blank AI Status
• AI input: One approved certification PDF, JPEG, or PNG plus extraction instructions
• System instruction: Extract only visible information, use null when uncertain, and never infer validity
• Expected output: Strict JSON containing document fields, evidence, warnings, and confidence indicators
• Validation: JSON structure, date format, expected document type, confidence range, and prohibited document checks
• Record update: AI Status, AI Confidence, AI Extraction JSON, Last AI Run, and Error Message
• Human review: Compliance compares every extracted field with the original file
• Low confidence: Record is labeled Low Confidence and remains in the manual-review queue
• Failure behavior: The original document workflow continues without AI extraction

The reusable system instruction is:

You extract fields from professional certification documents for human review.

Rules:
1. Extract only information visibly present in the supplied document.
2. Do not infer whether the credential is genuine, valid, current, or acceptable.
3. Use null for a value that is absent, ambiguous, or unreadable.
4. Preserve credential numbers exactly as displayed.
5. Return dates as YYYY-MM-DD only when a complete date is visible.
6. Identify conflicting values in warnings.
7. Do not make employment, compliance, legal, or access decisions.
8. Return only data that matches the required JSON schema.

The reusable user prompt is:

Extract the employee name, issuing organization, credential name, credential number, issue date, and expiry date from this professional certification.

For every extracted field, include:
- value
- confidence from 0 to 1
- a short evidence phrase copied or closely transcribed from the document

List missing critical fields and any ambiguity in warnings. This output will be checked by an authorized human reviewer.

Expected structured output:

{
  "document_type": "professional_certification",
  "employee_name": {
    "value": "Taylor Morgan",
    "confidence": 0.97,
    "evidence": "This certifies that Taylor Morgan"
  },
  "issuer": {
    "value": "Example Professional Board",
    "confidence": 0.96,
    "evidence": "Example Professional Board"
  },
  "credential_name": {
    "value": "Professional Safety Certificate",
    "confidence": 0.94,
    "evidence": "Professional Safety Certificate"
  },
  "credential_id": {
    "value": "PSC-48291",
    "confidence": 0.91,
    "evidence": "Certificate No. PSC-48291"
  },
  "issue_date": {
    "value": "2025-07-01",
    "confidence": 0.9,
    "evidence": "Issued 1 July 2025"
  },
  "expiry_date": {
    "value": "2027-06-30",
    "confidence": 0.92,
    "evidence": "Expires 30 June 2027"
  },
  "missing_fields": [],
  "warnings": [],
  "overall_confidence": 0.93
}

The following optional Apps Script file uses an approved API compatible with structured output through the Responses API pattern. Confirm the endpoint, approved model, file-input support, contractual controls, and current API documentation before deployment. Store the API key in Script Properties under AI_API_KEY.

const AI_CONFIG = Object.freeze({
  ENDPOINT: 'https://api.openai.com/v1/responses',
  MODEL: 'YOUR_APPROVED_MODEL_NAME',
  MAX_FILE_BYTES: 10 * 1024 * 1024,
  LOW_CONFIDENCE_THRESHOLD: 0.80,
  MAX_BATCH_SIZE: 10,
  MAX_ATTEMPTS: 3
});

function processPendingCertificationExtractions() {
  const documentSheet = getSheet_(SHEETS.DOCUMENTS);
  const documents = getObjects_(documentSheet);
  let processed = 0;

  documents.forEach(function(document) {
    if (processed >= AI_CONFIG.MAX_BATCH_SIZE) {
      return;
    }

    const requirementCode = cleanText_(
      document['Requirement Code']
    );
    const aiStatus = cleanText_(document['AI Status']);
    const documentStatus = cleanText_(document['Status']);

    if (requirementCode === 'CERT' &&
        !aiStatus &&
        documentStatus === 'Pending Review') {
      extractCertificationForDocument(document['Document ID']);
      processed += 1;
    }
  });
}

function extractCertificationForDocument(documentId) {
  const apiKey = PropertiesService.getScriptProperties()
    .getProperty('AI_API_KEY');

  if (!apiKey) {
    throw new Error(
      'Set AI_API_KEY in Apps Script Properties before using AI extraction.'
    );
  }

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

  const documentSheet = getSheet_(SHEETS.DOCUMENTS);
  const document = findObjectByValue_(
    documentSheet,
    'Document ID',
    documentId
  );

  if (!document) {
    throw new Error('Document record not found: ' + documentId);
  }

  if (cleanText_(document['Requirement Code']) !== 'CERT') {
    throw new Error(
      'AI extraction is permitted only for approved certification records.'
    );
  }

  const file = DriveApp.getFileById(document['File ID']);
  const mimeType = cleanText_(file.getMimeType()).toLowerCase();
  const allowedTypes = [
    'application/pdf',
    'image/jpeg',
    'image/png'
  ];

  if (allowedTypes.indexOf(mimeType) === -1) {
    throw new Error('The certification file type is not approved for AI.');
  }

  if (file.getSize() > AI_CONFIG.MAX_FILE_BYTES) {
    throw new Error('The file exceeds the configured AI size limit.');
  }

  setFieldsAtRow_(documentSheet, document._row, {
    'AI Status': 'Processing',
    'Last AI Run': new Date(),
    'Error Message': ''
  });

  try {
    const blob = file.getBlob();
    const base64 = Utilities.base64Encode(blob.getBytes());
    const fileData = 'data:' + mimeType + ';base64,' + base64;

    const systemInstruction = [
      'You extract fields from professional certification documents',
      'for human review.',
      'Extract only information visibly present.',
      'Do not infer authenticity, validity, acceptability, or compliance.',
      'Use null when a value is absent, ambiguous, or unreadable.',
      'Preserve credential numbers exactly.',
      'Use YYYY-MM-DD only for complete visible dates.',
      'Return only data matching the required JSON schema.'
    ].join(' ');

    const userPrompt = [
      'Extract the employee name, issuing organization, credential name,',
      'credential number, issue date, and expiry date.',
      'For every field include value, confidence from 0 to 1, and evidence.',
      'List missing critical fields and warnings.',
      'An authorized human will verify every result.'
    ].join(' ');

    const fieldSchema = {
      type: 'object',
      additionalProperties: false,
      properties: {
        value: {
          type: ['string', 'null']
        },
        confidence: {
          type: 'number',
          minimum: 0,
          maximum: 1
        },
        evidence: {
          type: ['string', 'null']
        }
      },
      required: ['value', 'confidence', 'evidence']
    };

    const responseSchema = {
      type: 'object',
      additionalProperties: false,
      properties: {
        document_type: {
          type: 'string',
          enum: ['professional_certification', 'unknown']
        },
        employee_name: fieldSchema,
        issuer: fieldSchema,
        credential_name: fieldSchema,
        credential_id: fieldSchema,
        issue_date: fieldSchema,
        expiry_date: fieldSchema,
        missing_fields: {
          type: 'array',
          items: {
            type: 'string'
          }
        },
        warnings: {
          type: 'array',
          items: {
            type: 'string'
          }
        },
        overall_confidence: {
          type: 'number',
          minimum: 0,
          maximum: 1
        }
      },
      required: [
        'document_type',
        'employee_name',
        'issuer',
        'credential_name',
        'credential_id',
        'issue_date',
        'expiry_date',
        'missing_fields',
        'warnings',
        'overall_confidence'
      ]
    };

    const payload = {
      model: AI_CONFIG.MODEL,
      input: [
        {
          role: 'system',
          content: [
            {
              type: 'input_text',
              text: systemInstruction
            }
          ]
        },
        {
          role: 'user',
          content: [
            {
              type: 'input_text',
              text: userPrompt
            },
            {
              type: 'input_file',
              filename: file.getName(),
              file_data: fileData
            }
          ]
        }
      ],
      text: {
        format: {
          type: 'json_schema',
          name: 'employee_certification_extraction',
          strict: true,
          schema: responseSchema
        }
      }
    };

    const apiResponse = fetchAiWithRetry_(
      AI_CONFIG.ENDPOINT,
      apiKey,
      payload
    );

    const responseObject = JSON.parse(apiResponse);
    const outputText = getResponseOutputText_(responseObject);
    const extraction = JSON.parse(outputText);

    validateAiExtraction_(extraction);

    const confidence = Number(extraction.overall_confidence);
    const lowConfidence = confidence <
      AI_CONFIG.LOW_CONFIDENCE_THRESHOLD ||
      extraction.document_type !== 'professional_certification' ||
      extraction.missing_fields.length > 0;

    setFieldsAtRow_(documentSheet, document._row, {
      'AI Status': lowConfidence
        ? 'Low Confidence - Human Review'
        : 'Needs Human Review',
      'AI Confidence': confidence,
      'AI Extraction JSON': JSON.stringify(extraction),
      'Last AI Run': new Date(),
      'Error Message': '',
      'Updated At': new Date()
    });

    logEvent_(
      'INFO',
      'AI:' + documentId,
      'Document',
      documentId,
      'Certification field extraction',
      'SUCCESS',
      lowConfidence
        ? 'Extraction completed with low-confidence indicators.'
        : 'Extraction completed and requires human verification.',
      0
    );

    return extraction;
  } catch (error) {
    setFieldsAtRow_(documentSheet, document._row, {
      'AI Status': 'Failed - Manual Review',
      'Last AI Run': new Date(),
      'Error Message': safeErrorMessage_(error),
      'Updated At': new Date()
    });

    logEvent_(
      'ERROR',
      'AI:' + documentId,
      'Document',
      documentId,
      'Certification field extraction',
      'FAILED',
      safeErrorMessage_(error),
      0
    );

    return null;
  }
}

function fetchAiWithRetry_(endpoint, apiKey, payload) {
  let lastError = null;

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

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

      if (statusCode >= 200 && statusCode < 300) {
        return body;
      }

      if (statusCode === 429 || statusCode >= 500) {
        lastError = new Error(
          'AI service returned HTTP ' + statusCode + ': ' +
          body.slice(0, 500)
        );

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

      throw new Error(
        'AI request rejected with HTTP ' + statusCode + ': ' +
        body.slice(0, 500)
      );
    } catch (error) {
      lastError = error;

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

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

function getResponseOutputText_(responseObject) {
  if (responseObject.output_text) {
    return responseObject.output_text;
  }

  const output = responseObject.output || [];

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

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

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

function validateAiExtraction_(extraction) {
  if (!extraction ||
      typeof extraction !== 'object' ||
      Array.isArray(extraction)) {
    throw new Error('AI output is not a JSON object.');
  }

  if (['professional_certification', 'unknown']
      .indexOf(extraction.document_type) === -1) {
    throw new Error('AI output has an invalid document type.');
  }

  const confidence = Number(extraction.overall_confidence);

  if (!isFinite(confidence) ||
      confidence < 0 ||
      confidence > 1) {
    throw new Error('AI output has an invalid confidence value.');
  }

  ['issue_date', 'expiry_date'].forEach(function(fieldName) {
    const field = extraction[fieldName];

    if (field &&
        field.value !== null &&
        !/^\d{4}-\d{2}-\d{2}$/.test(field.value)) {
      throw new Error(
        'AI output contains an invalid date in ' + fieldName + '.'
      );
    }
  });

  if (!Array.isArray(extraction.missing_fields) ||
      !Array.isArray(extraction.warnings)) {
    throw new Error('AI output arrays are missing or invalid.');
  }
}

Add this code as a second file in the same Apps Script project. Set AI_API_KEY under project script properties and replace YOUR_APPROVED_MODEL_NAME. Run extractCertificationForDocument manually with a fabricated document ID before installing any schedule.

If scheduled processing is approved, create a time-based trigger for processPendingCertificationExtractions. Keep the batch size conservative, monitor API rate limits, and confirm that the selected service accepts the file format and size. Interface and model availability can change, so test against current official API documentation before production use.

Benefits of the AI Enhancement

The AI enhancement can reduce the time required to locate and transcribe fields from varied certification layouts. It can also produce more consistent structured values for expiry reporting and identify documents where expected fields appear to be missing.

These are AI-specific benefits. Secure intake, file naming, folder routing, duplicate prevention, task creation, reminders, escalation, and audit logging are already delivered by the rule-based automation.

The AI result remains an aid to the reviewer. A high confidence value does not establish authenticity, legal validity, current standing, or suitability for an employee’s role.

What Remains Rule-Based or Human-Controlled

• Employee identity matching: Uses the managed account email and trusted roster, not an AI judgment.
• Required document assignment: Uses department and requirement lookup rules.
• Storage classification: Uses an approved mapping from requirement code to Drive root.
• Access permission: Remains controlled by Workspace administrators and approved groups.
• Document verification: An authorized reviewer compares the file with policy requirements.
• Credential authenticity: A human follows the organization’s approved validation procedure.
• Employment or disciplinary decisions: Never made by the extraction model.
• Legal conclusions: Remain with qualified personnel.
• Retention disposition: Requires an authorized human decision after applicable holds and schedules are checked.
• Policy exceptions and waivers: Require documented human approval.

Estimating the Additional Value of AI

The AI estimate applies only to 25 monthly professional certification documents, not all 110 submissions.

The assumed 20 percent correction rate includes extracted fields that a reviewer edits. The 8 percent failure rate represents unreadable files, unsupported layouts, service errors, or invalid output. These are planning assumptions that must be replaced with pilot measurements.

Testing Checklist

Use fabricated sample data before processing real employee information.

Ongoing Maintenance

The primary system owner should be a People Operations manager. A technically capable backup owner should understand trigger monitoring, Apps Script executions, folder permissions, and manual recovery.

API keys and other optional secrets should be rotated according to organizational policy. AI output should be sampled regularly for inaccurate extraction, unexpected content, cost growth, and inappropriate document inclusion.

When to Move to Dedicated Software

This implementation may remain appropriate while volume, permissions, and workflows are moderate. It should be reassessed when one or more of the following becomes material:

• Document volume approaches Apps Script, Gmail, spreadsheet, or operational support limits.
• Employees need a full self-service portal with status visibility.
• External candidates or contractors need secure access without managed Google accounts.
• Multiple legal entities or jurisdictions require substantially different document packages.
• Field-level permissions become necessary within the employee register.
• Formal audit requirements exceed a spreadsheet-based event log.
• Retention must be enforced through legal holds and centrally governed disposition.
• Payroll, benefits, identity management, or background-check systems require deep integration.
• Mobile capture, offline operation, or multilingual workflows become essential.
• Exception rates or custom script maintenance consume excessive staff time.
• Vendor support, service-level commitments, or validated compliance controls become mandatory.
• Advanced electronic signatures, identity verification, or credential validation are required.

Relevant alternatives may include a human resources information system, employee onboarding platform, document management system, governance platform, or secure employee portal. Migration is not automatically necessary merely because the workflow is customized. The decision should compare risk, support effort, control requirements, user needs, and total cost.

Implementation Checklist

• Confirm the employee document types, owners, due dates, and review rules.
• Approve privacy notices, access boundaries, and retention references.
• Confirm Google Forms, Sheets, Drive, Apps Script, and Gmail are permitted.
• Create the controlled automation account.
• Create People Operations and compliance access groups.
• Create general, compliance, and restricted Drive roots.
• Build the authenticated Google Form with exact field labels.
• Link the form to the restricted response workbook.
• Seed the authoritative employee register.
• Review the Requirements sheet and department mappings.
• Configure unique task and document identifiers.
• Replace script spreadsheet, folder, email, and reviewer placeholders.
• Authorize the script and install one trigger of each required type.
• Verify Forms-to-Sheets, Sheets-to-Drive, and Apps-Script-to-Gmail mappings.
• Test task generation and duplicate prevention.
• Test file validation, routing, naming, and version handling.
• Test reviewer verification, rejection, reassignment, and audit evidence.
• Test reminders, overdue statuses, and escalation fallback.
• Create operational views for open, submitted, overdue, returned, failed, and retention-review records.
• Protect roster, configuration, automation, and audit fields.
• Confirm that managers and unauthorized users cannot open sensitive files.
• Test failure, retry, reconciliation, and rollback procedures.
• Document representative software, labor, maintenance, and savings assumptions.
• Pilot with fabricated data and then a limited internal group.
• Add AI only after the core workflow is stable and privacy approval is complete.
• Require human review of every AI extraction.
• Assign primary and backup maintenance owners.
• Schedule permission, trigger, retention, cost, and integration reviews.
• Define the volume, compliance, portal, integration, and support criteria that would justify dedicated software.