Document Review and Version Control with Google Workspace

The Business Situation

This representative case study follows an 85-person industrial equipment manufacturer. The business creates operating procedures, compliance policies, customer-facing technical documents, and marketing materials. Its Operations and Manufacturing team owns document control, while Legal and Compliance, department managers, and Marketing participate in reviews.

The company uses Google Workspace for email, file storage, and spreadsheets. Approximately 45 documents enter review each month. A typical document has one owner, two required reviewers, a due date, and one or two revision cycles before approval.

The original process depended on email attachments. An owner emailed a document to reviewers, collected comments through separate reply chains, renamed the next attachment manually, and updated a shared spreadsheet. Approved files were then copied into department folders.

This approach became difficult to control as the volume and variety of documents increased. Staff could not always determine which attachment was current, whether every required reviewer had responded, or why a published document had been approved.

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 implementation described below is a representative design rather than a report of verified client results. Volumes, costs, time assumptions, and expected benefits should be replaced with figures from the organization implementing the workflow.

The Existing Process

The original workflow followed this sequence:

1. The document owner created a file in Google Docs, Microsoft Word, or another authoring application.
2. The owner attached the document to an email and manually selected reviewers.
3. Reviewers downloaded or opened their own copies and returned comments by email.
4. The owner reconciled conflicting comments and created another attachment with a filename such as Procedure_Final_v2_updated.docx.
5. The new attachment was circulated again, often in a different email thread.
6. The owner recorded an approval date in a shared Google Sheet.
7. An operations coordinator copied the approved attachment into a department folder.
8. Old attachments remained in mailboxes, personal Drive folders, and download directories.

The spreadsheet provided a basic document index, but it was not a reliable system of record. Rows were updated inconsistently, versions were overwritten, and there was no event history showing who submitted, reviewed, rejected, approved, or published each version.

Operational procedures created an additional risk. A superseded procedure could remain accessible in a department folder while a new version was circulating. Marketing faced a similar issue when claims or disclosures changed after a file had already been shared externally.

The business therefore needed more than a better naming convention. It needed structured intake, deterministic routing, controlled version storage, reviewer accountability, approval evidence, and a recoverable automation process.

What the New System Needed to Do

The design also required a manual override. A document-control administrator needed to be able to reassign an unavailable reviewer, retry a failed record, archive a withdrawn document, or pause publication while preserving the event history.

Implementation Approaches Considered

Improving the manual process

A standardized filename, review checklist, and spreadsheet would reduce some confusion. It would not reliably stop duplicate versions, enforce reviewer routing, create reminders, or connect approval evidence to the published file. This option was not sufficient.

Using Google Workspace with Apps Script

The company already used Google Workspace, so Forms, Drive, and Sheets were familiar to staff. Apps Script could connect submissions, folders, review decisions, email notifications, publication, and reporting without introducing another primary system.

Using a no-code database

A no-code database could provide stronger relational views and interfaces. It was less suitable for this scenario because most users already worked in Google Workspace, document storage would still remain in Drive, and another permission boundary would have to be administered.

Purchasing dedicated software

A formal document management or quality management platform would be appropriate if the company needed validated electronic signatures, controlled training acknowledgements, complex retention schedules, formal regulatory validation, or extensive supplier and customer portals. Those requirements were not assumed at the representative volume.

The Selected Solution

The selected implementation uses two Google Forms linked to one control spreadsheet. The first form accepts new documents and revisions. The second captures review decisions. Google Apps Script processes both response sheets, manages Drive folders, sends email, updates structured records, publishes approved versions, and creates approval evidence.

Existing authoring tools were retained. Owners could continue drafting in Google Docs or upload Microsoft Office files and PDFs. The workflow controlled the submitted review copy rather than replacing authoring applications.

Manual filename creation, folder creation, reviewer request emails, reminder emails, version log updates, and evidence preparation were removed. Review comments, approval decisions, reviewer reassignment, policy exceptions, and final acceptance remained human-controlled.

System Architecture and Data Flow

• Intake: A Google Form for new documents and new versions.
• System of record: A Google Sheets workbook with separate Documents, Versions, Reviews, Events, Errors, Config, and AI Reviews sheets.
• Automation layer: A spreadsheet-bound Google Apps Script project with form-submission and scheduled triggers.
• Document storage: A controlled Google Drive root folder containing version, publication, archive, markup, and evidence folders.
• Notifications: Email sent through the account that owns the Apps Script triggers.
• Reporting: Filtered Sheets views, pivot tables, and an optional Looker Studio report.
• AI layer: An optional API call for section checking and summarization after the core workflow is stable.

1. Submission: An owner submits the intake form. The spreadsheet form-submission trigger receives the response row, including file links and collected email address.
2. Validation: Apps Script validates the submission type, document type, owner email, due date, confidentiality, destination, acknowledgement, and attached files. An invalid submission is marked failed and written to the Errors sheet.
3. Identification: For a new document, the script creates a locked monthly sequence and assigns a permanent ID. For a revision, it verifies the existing ID and confirms that the submitter is the owner or an administrator.
4. Storage: The script creates the department and document folder structure, moves uploaded files into the current version folder, and applies controlled filenames.
5. Record creation: The Documents sheet receives the current state. The Versions sheet receives an immutable version record and source event key.
6. Reviewer routing: Required reviewers are read from the Config sheet according to document type. Additional reviewers are validated and deduplicated. The owner is excluded from the reviewer list.
7. Review request: A Reviews row is created before each email is sent. The email contains a prefilled link to the review decision form and identifies the document ID, version, owner, and due date.
8. Decision: The review form submission is matched to the expected authenticated reviewer, document ID, and version. The decision and comments update the corresponding Reviews row.
9. Branching: A change request returns the document to its owner. A rejection closes the version. An approval waits until all reviewers for the current version have approved.
10. Publication: After unanimous human approval, the script archives the previous published copy, copies the approved version into the Published folder, and records the publication version.
11. Evidence: The script creates a PDF containing version information, reviewer decisions, timestamps, and comments. The evidence URL is stored against the version.
12. Monitoring: A daily trigger sends reminders and overdue escalations. Failures are logged with the function, row, record ID, error message, stack, and recovery status.

Data Structure

The spreadsheet uses one row per document, one row per submitted version, and one row per assigned reviewer per version. This separation prevents a new version from overwriting earlier review evidence.

Documents sheet

Versions, reviews, and events

The combination of Record_ID and Version_Number is unique in the Versions sheet. The combination of Record_ID, Version_Number, and Reviewer_Email is logically unique in Reviews. The script checks these relationships before appending rows.

Workflow Statuses and Ownership

A new version supersedes pending review requests for the prior version. It does not delete their records. A reviewer can send a document backward through Changes Requested or Rejected, but only a new owner submission can move it back into review.

Closure requires either successful publication, explicit rejection, or administrative archival. An AI result cannot move a record into Approved, Rejected, Published, or Archived.

Step-by-Step Implementation

Step 1: Prepare the Accounts and Permissions

1. Create or identify a shared Google Workspace account that will own the control spreadsheet, both Forms, the Drive root folder, and the installable Apps Script triggers. Avoid tying production ownership to an employee’s personal account.
2. Confirm that the organization’s Workspace configuration permits Forms file uploads. File-upload questions normally require respondents to sign in, and organizational policies may restrict who can upload.
3. Create a restricted shared Drive or controlled My Drive folder named Document Control. If a shared drive is used, test file moving and copying because shared-drive behavior and permissions can differ from My Drive.
4. Create a Google Sheet named Document Review Control. This file contains the Apps Script project and operational tables.
5. Create a Google Form named Document Intake and link its responses to the control spreadsheet. Rename the response tab Intake Responses.
6. Create a second Google Form named Document Review Decision, link it to the same spreadsheet, and rename its response tab Review Responses.
7. Enable collection of authenticated email addresses on both forms. Limit respondents to the organization when the documents are not intended for external access.
8. Identify the document-control administrator, backup administrator, escalation recipient, and route reviewers for each document type.
9. Create test accounts representing an owner, a legal reviewer, an operations reviewer, a marketing reviewer, and an unauthorized user.
10. Use a separate test root folder and copied spreadsheet until user acceptance testing is complete.

The script requests permission to read and write the spreadsheet, manage Drive files, create Google Docs, send email, make external requests if the optional AI feature is enabled, and create installable triggers. Only the production automation owner should authorize the production project.

Step 2: Build the Intake

Create the following questions in the Document Intake form. The question titles must match the script configuration exactly unless the corresponding constants or field references are changed.

Use form sections so that Existing Document ID appears for a new version and owner/title questions appear for a new document. The script still performs server-side validation because form branching alone is not an adequate control.

The confirmation message should explain that the submission is not approved merely because it was accepted. It should direct the owner to expect an email containing the generated document ID.

Duplicate prevention uses the form response sheet name and row number as the source event key. The script also writes Automation Record ID, Automation Status, and Automation Error back to the response row. A repeated trigger therefore repairs or ignores the existing record rather than generating another ID.

Step 3: Create the System of Record

The setup function creates these sheets:

• Documents: One row per controlled document.
• Versions: One row per submitted version.
• Reviews: One row per required reviewer per version.
• Events: Append-only business and automation events.
• Errors: Failed runs and recovery status.
• Config: Reviewer routes, administrators, and escalation recipients.
• AI Reviews: Optional structured AI outputs and usage counts.

Run the setup function only after both Forms are linked and their response tabs are renamed. It adds automation result columns to the right side of each response sheet without changing the Form-managed question columns.

Protect configuration, event, error, and identifier columns. Owners and reviewers should interact through the Forms and read-only operational views rather than editing control rows directly.

The generated identifier uses the pattern DOC-YYYYMM-NNNN. Apps Script’s Lock Service prevents two simultaneous submissions from receiving the same sequence. Script Properties store the current monthly sequence.

Use filtered views instead of deleting rows. For example, create views for current records, archived records, open errors, pending reviews, and reviews due within seven days.

Step 4: Connect the Tools

Apps Script uses Google’s authorization context rather than separately issued API credentials for Sheets, Drive, Docs, and email. The trigger owner must have sufficient access to every form response file and destination folder.

Review emails contain prefilled form links. Create one prefilled response manually in the review form to identify the URL parameters for Document ID and Version Number. Store those entry.NNNNN parameter names in the script settings.

Interface labels can vary by Google Workspace release. The underlying configuration is an installable spreadsheet form-submission trigger and a daily time-based trigger.

Step 5: Build the Core Automation

Automation 1: Process document intake

• Trigger: A new row submitted to Intake Responses.
• Conditions: Valid submission type, due date, owner, document type, confidentiality, destination, acknowledgement, route, and attachment.
• Actions: Assign or validate the record ID, create folders, move files, create document and version rows, create review rows, and send requests.
• Fields updated: Status, current version, folder URLs, required reviewers, approval status, timestamps, and automation status.
• Notification: Each reviewer receives a request. The owner receives the generated ID.
• Exception: The response row is marked Failed and an Errors record is created.

The exact action order matters:

1. Acquire a script lock.
2. Read the source row and construct its source event key.
3. Check whether the event has already created a version.
4. Validate all input before moving files.
5. Write the generated record ID back to the source response row.
6. Create or locate the deterministic Drive folder structure.
7. Move and rename uploaded files.
8. Create or update the Documents row.
9. Append the Versions row with its unique source event key.
10. Supersede pending reviews from an older version.
11. Create each Reviews row before sending its email.
12. Record the event and mark the response completed.
13. Release the lock.

Automation 2: Process review decisions

• Trigger: A new row submitted to Review Responses.
• Conditions: The respondent email must match a pending review for the specified document and version.
• Actions: Store the decision and comments, move optional markup files, evaluate all required reviews, and follow the appropriate branch.
• Fields updated: Review status, decision timestamp, comments, markup URL, document status, version status, and approval count.
• Notification: Owners receive change requests, rejections, or final publication confirmation.
• Exception: Duplicate or unauthorized responses are ignored or logged for review.

Automation 3: Publish an approved version

• Trigger: Every required review for the current version is Approved.
• Conditions: The version must still be current and contain at least one controlled file.
• Actions: Archive the previous published files, copy the approved files, create the evidence PDF, and update publication fields.
• Fields updated: Status, Approval_Status, Publication_Version, Last_Approved_At, Published_Document_URL, and Evidence_URL.
• Notification: Owner and administrators receive publication confirmation.
• Exception: A partial publication is marked Automation Error and can be retried idempotently.

Step 6: Add Approvals, Reminders, and Escalations

Approval is unanimous for the current version. Every row in Reviews for the active Record_ID and Version_Number must have the status Approved. There is no approval based solely on elapsed time.

Representative routing rules are:

• Policy: Legal and Compliance plus the responsible department approver.
• Procedure: Operations document control plus the responsible technical reviewer.
• Customer Document: Legal and Compliance plus the accountable customer-facing manager.
• Marketing Material: Marketing approver plus Legal and Compliance when claims or disclosures are present.
• Other: Document-control administrator selects the route before processing.

For a workflow requiring sequential approval, add a Review_Order field and send only the lowest pending order. This representative implementation uses parallel review because both reviewers can work at the same time. Publication still waits for all approvals.

The daily trigger applies these rules:

1. Send a reminder when a pending review is due within two calendar days.
2. Send no more than one reminder per reviewer per day.
3. Continue daily reminders while overdue.
4. After two overdue days, send one escalation to the configured escalation address and document owner.
5. If a reviewer is unavailable, an administrator updates the reviewer email, records a reassignment event, and resends the request.

A Changes Requested decision cancels remaining pending reviews for that version and returns comments to the owner. A Reject decision also stops publication. A new version creates a new set of review rows, preserving all earlier decisions.

Approval evidence consists of the review rows, timestamps, comments, source event references, publication timestamp, and generated evidence PDF. Email alone is not treated as the approval record.

Step 7: Add Documents and File Management

The script creates this structure under the controlled root folder:

Document Control/
  Operations & Manufacturing/
    DOC-202607-0017 - Hydraulic Lockout Procedure/
      01_Versions/
        v01/
          DOC-202607-0017_v01_01_Hydraulic_Lockout_Procedure.docx
          Review_Markups/
        v02/
          DOC-202607-0017_v02_01_Hydraulic_Lockout_Procedure.docx
          Review_Markups/
      02_Published/
      03_Archive/
        v01_superseded_20260715/
      04_Evidence/
        DOC-202607-0017_v02_approval_evidence.pdf

Folder names are sanitized to remove characters that cause confusing paths. The permanent record ID remains at the beginning of every controlled filename.

Version files are moved rather than copied from the Form upload location so there is one controlled submitted file. Published files are copies because the approved version folder must remain unchanged as historical evidence.

Folder inheritance should provide access by role. Restricted documents require a separately restricted root or explicit folder permissions. The automation must not create public links.

When a new version is approved, files in Published are moved to an Archive subfolder before the new approved copies are created. The Versions folder is never reused. A replacement file therefore becomes a new version rather than silently replacing prior evidence.

Set upload limits based on expected document sizes and available storage. If an upload cannot be moved, the workflow remains in Automation Error and does not send reviewers a link to an incomplete version.

Step 8: Add Reporting and Operational Views

Create filtered views or pivot tables for:

• New records created in the last seven days.
• Documents currently In Review.
• Pending reviews grouped by reviewer.
• Reviews due within two days.
• Overdue reviews and escalation status.
• Documents in Changes Required.
• Rejected versions.
• Published documents by department and document type.
• Automation failures with open Errors rows.
• Records with missing publication or evidence links.
• Average elapsed time from submission to publication.
• Version count by document.
• AI outputs waiting for human review.

A useful elapsed-time formula in a reporting sheet is:

=(Last_Approved_At - Created_At) * 24

The result is hours when both fields contain valid spreadsheet date-time values. Use business-day calculations only if the organization has defined working calendars and holiday rules.

Sheets views update as the control tables change. If Looker Studio is connected, configure its refresh behavior according to the reporting requirement. The document-control manager owns dashboard definitions and investigates any threshold breach, such as more than five overdue reviews or any publication failure.

Step 9: Add Security and Governance Controls

• Give owners and reviewers access only to folders and views needed for their roles.
• Restrict editing of Documents, Versions, Reviews, Events, Errors, and Config to administrators and the automation owner.
• Disable public and unrestricted link sharing at the organizational or shared-drive level where appropriate.
• Keep the production Apps Script project editable only by approved administrators.
• Store external API secrets in Script Properties, not in spreadsheet cells or source code.
• Review trigger ownership before disabling or deleting any user account.
• Remove former employees from groups, shared drives, Forms access, and Apps Script project access promptly.
• Retain event and evidence records according to the organization’s legal, contractual, quality, and privacy requirements.
• Back up the control spreadsheet and verify that Drive retention controls cover deleted files.
• Exclude Restricted, privileged, personal, export-controlled, or otherwise prohibited content from optional AI processing unless an approved policy and vendor configuration permit it.
• Require human approval for publication, legal acceptance, external commitments, safety instructions, and policy exceptions.

Google Sheets is an operational system of record in this design, not an immutable compliance ledger. Organizations requiring validated electronic signatures, tamper-evident records, or formal records-management certification should evaluate dedicated platforms.

Step 10: Deploy and Test

1. Copy the control spreadsheet, Forms, and root folder into a test environment.
2. Replace all placeholder IDs, reviewer addresses, form entry parameters, and administrator addresses.
3. Run setupControlSheets and inspect every generated header.
4. Complete each Config route with valid test email addresses.
5. Run installTriggers from the intended trigger-owner account and authorize the requested scopes.
6. Submit at least one new document for every document type.
7. Test approvals, changes requested, rejection, late reminders, escalation, publication failure, and retry.
8. Verify that a duplicate event does not create another document or version.
9. Conduct user acceptance testing with a small pilot group from Operations, Compliance, and Marketing.
10. Freeze structural changes during the pilot. Header names are part of the integration contract.
11. Document support ownership, recovery steps, route changes, and escalation contacts.
12. Schedule production activation outside a period with active high-priority reviews.
13. Retain the prior spreadsheet and email process in read-only form during the agreed transition period.
14. Monitor every trigger execution during the first two weeks and reconcile Forms responses to Versions and Reviews daily.

A rollback pauses or removes the installable triggers, prevents new Form submissions, and returns owners to the documented contingency process. Existing controlled records and files remain intact for reconciliation.

Code and Configuration

The code below belongs in the Apps Script project bound to the Document Review Control spreadsheet. Open the spreadsheet, open the Apps Script editor from the Extensions area, replace the default code in Code.gs, and paste the complete script.

Replace YOUR_SPREADSHEET_ID, YOUR_FOLDER_ID, YOUR_REVIEW_FORM_VIEW_URL, both review form entry parameters, and YOUR_EMAIL_ADDRESS. The response sheet names and Form question titles must match the implementation.

const SETTINGS = Object.freeze({
  SPREADSHEET_ID: 'YOUR_SPREADSHEET_ID',
  ROOT_FOLDER_ID: 'YOUR_FOLDER_ID',
  INTAKE_SHEET_NAME: 'Intake Responses',
  REVIEW_SHEET_NAME: 'Review Responses',
  REVIEW_FORM_URL: 'YOUR_REVIEW_FORM_VIEW_URL',
  REVIEW_FORM_DOCUMENT_ENTRY: 'entry.111111111',
  REVIEW_FORM_VERSION_ENTRY: 'entry.222222222',
  ADMIN_EMAIL: 'YOUR_EMAIL_ADDRESS'
});

const SHEETS = Object.freeze({
  DOCUMENTS: 'Documents',
  VERSIONS: 'Versions',
  REVIEWS: 'Reviews',
  EVENTS: 'Events',
  ERRORS: 'Errors',
  CONFIG: 'Config',
  AI_REVIEWS: 'AI Reviews'
});

const DOCUMENT_HEADERS = [
  'Record_ID', 'Title', 'Document_Type', 'Department', 'Owner_Email',
  'Requester_Email', 'Status', 'Current_Version', 'Due_Date',
  'Required_Reviewers', 'Confidentiality', 'Publication_Destination',
  'Record_Folder_URL', 'Versions_Folder_URL', 'Current_Version_Folder_URL',
  'Published_Folder_URL', 'Archive_Folder_URL', 'Evidence_Folder_URL',
  'Published_Document_URL', 'Approval_Status', 'Publication_Version',
  'Created_At', 'Last_Updated', 'Last_Automation_Run', 'Automation_Status',
  'Retry_Count', 'Error_Message', 'Last_Approved_At', 'Source_Row',
  'AI_Review_Status', 'AI_Summary', 'AI_Missing_Sections',
  'AI_Confidence', 'AI_Last_Run'
];

const VERSION_HEADERS = [
  'Version_Key', 'Record_ID', 'Version_Number', 'Submitted_At',
  'Submitted_By', 'Change_Summary', 'Version_Folder_URL', 'File_IDs',
  'File_URLs', 'File_Names', 'Version_Status', 'Source_Event_Key',
  'Approved_At', 'Evidence_URL'
];

const REVIEW_HEADERS = [
  'Review_ID', 'Record_ID', 'Version_Number', 'Reviewer_Email', 'Status',
  'Due_Date', 'Requested_At', 'Request_Sent_At', 'Decision_At', 'Comments',
  'Markup_File_URL', 'Last_Reminder_Date', 'Escalated_At', 'Review_Form_URL'
];

const EVENT_HEADERS = [
  'Event_ID', 'Timestamp', 'Record_ID', 'Version_Number', 'Event_Type',
  'Actor', 'Details', 'Source_Event_Key'
];

const ERROR_HEADERS = [
  'Error_ID', 'Timestamp', 'Source', 'Row_Number', 'Record_ID',
  'Function_Name', 'Error_Message', 'Stack', 'Status', 'Retry_Count',
  'Resolved_At'
];

const CONFIG_HEADERS = ['Key', 'Value', 'Description'];

const AI_REVIEW_HEADERS = [
  'AI_Review_ID', 'Timestamp', 'Record_ID', 'Version_Number', 'Model',
  'Summary', 'Missing_Sections', 'Potential_Issues', 'Confidence',
  'Requires_Human_Review', 'Raw_JSON', 'Input_Tokens', 'Output_Tokens',
  'Status'
];

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Document Control')
    .addItem('Set up control sheets', 'setupControlSheets')
    .addItem('Install triggers', 'installTriggers')
    .addSeparator()
    .addItem('Retry an intake row', 'retryIntakeRowPrompt')
    .addItem('Retry a record', 'retryRecordPrompt')
    .addToUi();
}

function setupControlSheets() {
  const ss = getSpreadsheet();

  ensureSheet(ss, SHEETS.DOCUMENTS, DOCUMENT_HEADERS);
  ensureSheet(ss, SHEETS.VERSIONS, VERSION_HEADERS);
  ensureSheet(ss, SHEETS.REVIEWS, REVIEW_HEADERS);
  ensureSheet(ss, SHEETS.EVENTS, EVENT_HEADERS);
  ensureSheet(ss, SHEETS.ERRORS, ERROR_HEADERS);
  ensureSheet(ss, SHEETS.CONFIG, CONFIG_HEADERS);
  ensureSheet(ss, SHEETS.AI_REVIEWS, AI_REVIEW_HEADERS);

  const intake = ss.getSheetByName(SETTINGS.INTAKE_SHEET_NAME);
  const review = ss.getSheetByName(SETTINGS.REVIEW_SHEET_NAME);

  if (!intake || !review) {
    throw new Error(
      'Link both Forms and rename their tabs Intake Responses and Review Responses.'
    );
  }

  ensureResponseColumns(intake);
  ensureResponseColumns(review);
  seedConfig();
  SpreadsheetApp.flush();
  console.log('Control sheets and response status columns are ready.');
}

function installTriggers() {
  validateSettings();

  const ss = getSpreadsheet();
  const handlers = ['onFormSubmit', 'sendReviewReminders'];

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

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

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

  console.log('Form submission and daily reminder triggers installed.');
}

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

  const sheetName = e.range.getSheet().getName();
  const rowNumber = e.range.getRow();

  if (sheetName === SETTINGS.INTAKE_SHEET_NAME) {
    processIntakeRow(rowNumber);
    return;
  }

  if (sheetName === SETTINGS.REVIEW_SHEET_NAME) {
    processReviewRow(rowNumber);
    return;
  }

  console.log('Ignored form submission from sheet: ' + sheetName);
}

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

  let recordId = '';
  const source = SETTINGS.INTAKE_SHEET_NAME;

  try {
    validateSettings();

    const sheet = getSpreadsheet().getSheetByName(source);
    ensureResponseColumns(sheet);

    const input = readRowObject(sheet, rowNumber);
    const eventKey = source + ':' + rowNumber;
    const existingVersion = findObject(
      SHEETS.VERSIONS,
      'Source_Event_Key',
      eventKey
    );

    if (existingVersion) {
      recordId = String(existingVersion.values.Record_ID);
      retryRecordInternal(recordId);
      setResponseResult(sheet, rowNumber, recordId, 'Completed', '');
      console.log('Existing intake event repaired or confirmed: ' + eventKey);
      return;
    }

    const submissionType = requireChoice(
      input['Submission Type'],
      ['New Document', 'New Version'],
      'Submission Type'
    );

    const requesterEmail = normalizeEmail(
      firstValue(input, ['Email Address', 'Requester Email'])
    );
    requireEmail(requesterEmail, 'Requester email');

    let documentRecord = null;
    let title = '';
    let documentType = '';
    let department = '';
    let ownerEmail = '';
    let confidentiality = '';
    let publicationDestination = '';
    let versionNumber = 1;

    if (submissionType === 'New Document') {
      title = requireText(input['Document Title'], 'Document Title', 150);
      documentType = requireChoice(
        input['Document Type'],
        ['Policy', 'Procedure', 'Customer Document', 'Marketing Material', 'Other'],
        'Document Type'
      );
      department = requireChoice(
        input['Department'],
        [
          'Legal & Compliance',
          'Operations & Manufacturing',
          'Marketing & Customer Engagement',
          'Corporate'
        ],
        'Department'
      );
      ownerEmail = normalizeEmail(input['Document Owner Email']);
      requireEmail(ownerEmail, 'Document Owner Email');
      confidentiality = requireChoice(
        input['Confidentiality'],
        ['Public', 'Internal', 'Confidential', 'Restricted'],
        'Confidentiality'
      );
      publicationDestination = requireChoice(
        input['Publication Destination'],
        [
          'Internal Controlled Library',
          'Customer Release',
          'Marketing Library',
          'No Publication'
        ],
        'Publication Destination'
      );

      recordId = String(input['Automation Record ID'] || '').trim();
      if (!recordId) {
        recordId = generateRecordId();
      }
    } else {
      recordId = requireText(
        input['Existing Document ID'],
        'Existing Document ID',
        40
      ).toUpperCase();

      documentRecord = findObject(SHEETS.DOCUMENTS, 'Record_ID', recordId);
      if (!documentRecord) {
        throw new Error('Existing document was not found: ' + recordId);
      }

      const admins = getAdminEmails();
      const existingOwner = normalizeEmail(documentRecord.values.Owner_Email);
      if (
        requesterEmail !== existingOwner &&
        admins.indexOf(requesterEmail) === -1
      ) {
        throw new Error(
          'Only the document owner or a configured administrator may submit a new version.'
        );
      }

      title = String(documentRecord.values.Title);
      documentType = String(documentRecord.values.Document_Type);
      department = String(documentRecord.values.Department);
      ownerEmail = existingOwner;
      confidentiality = String(
        input['Confidentiality'] || documentRecord.values.Confidentiality
      );
      publicationDestination = String(
        input['Publication Destination'] ||
        documentRecord.values.Publication_Destination
      );
      versionNumber = Number(documentRecord.values.Current_Version) + 1;
    }

    const acknowledgement = String(
      input['Privacy Acknowledgement'] || ''
    ).trim();
    if (acknowledgement !== 'Acknowledged') {
      throw new Error('Privacy Acknowledgement must be Acknowledged.');
    }

    const dueDate = parseRequiredDate(
      input['Review Due Date'],
      'Review Due Date'
    );
    if (dateOnly(dueDate).getTime() < dateOnly(new Date()).getTime()) {
      throw new Error('Review Due Date cannot be in the past.');
    }

    const changeSummary = requireText(
      input['Change Summary'],
      'Change Summary',
      2000
    );

    const additionalReviewers = parseEmailList(
      input['Additional Reviewer Emails']
    );
    const routedReviewers = getRouteReviewers(documentType);
    const reviewers = uniqueValues(
      routedReviewers.concat(additionalReviewers)
    ).filter(function(email) {
      return normalizeEmail(email) !== ownerEmail;
    });

    reviewers.forEach(function(email) {
      requireEmail(email, 'Reviewer email');
    });

    if (reviewers.length === 0) {
      throw new Error(
        'At least one valid reviewer other than the document owner is required.'
      );
    }

    const fileIds = getFileIdsFromCell(
      sheet,
      rowNumber,
      'Document Files'
    );
    if (fileIds.length === 0) {
      throw new Error(
        'No Drive file ID was found in the Document Files response.'
      );
    }

    setResponseResult(sheet, rowNumber, recordId, 'Processing', '');

    const folders = documentRecord
      ? foldersFromDocument(documentRecord.values)
      : ensureDocumentFolders(recordId, title, department);

    const versionLabel = formatVersion(versionNumber);
    const versionFolder = getOrCreateChildFolder(
      folders.versions,
      versionLabel
    );
    getOrCreateChildFolder(versionFolder, 'Review_Markups');

    const movedFiles = moveSubmissionFiles(
      fileIds,
      versionFolder,
      recordId,
      versionLabel
    );

    const now = new Date();

    if (!documentRecord) {
      appendObject(SHEETS.DOCUMENTS, DOCUMENT_HEADERS, {
        Record_ID: recordId,
        Title: title,
        Document_Type: documentType,
        Department: department,
        Owner_Email: ownerEmail,
        Requester_Email: requesterEmail,
        Status: 'Processing',
        Current_Version: versionNumber,
        Due_Date: dueDate,
        Required_Reviewers: reviewers.join(','),
        Confidentiality: confidentiality,
        Publication_Destination: publicationDestination,
        Record_Folder_URL: folders.record.getUrl(),
        Versions_Folder_URL: folders.versions.getUrl(),
        Current_Version_Folder_URL: versionFolder.getUrl(),
        Published_Folder_URL: folders.published.getUrl(),
        Archive_Folder_URL: folders.archive.getUrl(),
        Evidence_Folder_URL: folders.evidence.getUrl(),
        Published_Document_URL: '',
        Approval_Status: 'Pending 0/' + reviewers.length,
        Publication_Version: '',
        Created_At: now,
        Last_Updated: now,
        Last_Automation_Run: now,
        Automation_Status: 'Processing',
        Retry_Count: 0,
        Error_Message: '',
        Last_Approved_At: '',
        Source_Row: rowNumber,
        AI_Review_Status: '',
        AI_Summary: '',
        AI_Missing_Sections: '',
        AI_Confidence: '',
        AI_Last_Run: ''
      });

      documentRecord = findObject(SHEETS.DOCUMENTS, 'Record_ID', recordId);
    } else {
      cancelPendingReviews(
        recordId,
        Number(documentRecord.values.Current_Version),
        'Superseded by ' + versionLabel
      );

      updateObject(SHEETS.DOCUMENTS, documentRecord.rowNumber, {
        Status: 'Processing',
        Current_Version: versionNumber,
        Due_Date: dueDate,
        Required_Reviewers: reviewers.join(','),
        Confidentiality: confidentiality,
        Publication_Destination: publicationDestination,
        Current_Version_Folder_URL: versionFolder.getUrl(),
        Approval_Status: 'Pending 0/' + reviewers.length,
        Last_Updated: now,
        Last_Automation_Run: now,
        Automation_Status: 'Processing',
        Error_Message: '',
        AI_Review_Status: '',
        AI_Summary: '',
        AI_Missing_Sections: '',
        AI_Confidence: '',
        AI_Last_Run: ''
      });
    }

    appendObject(SHEETS.VERSIONS, VERSION_HEADERS, {
      Version_Key: recordId + '-' + versionLabel,
      Record_ID: recordId,
      Version_Number: versionNumber,
      Submitted_At: now,
      Submitted_By: requesterEmail,
      Change_Summary: changeSummary,
      Version_Folder_URL: versionFolder.getUrl(),
      File_IDs: JSON.stringify(movedFiles.ids),
      File_URLs: movedFiles.urls.join(','),
      File_Names: movedFiles.names.join(','),
      Version_Status: 'In Review',
      Source_Event_Key: eventKey,
      Approved_At: '',
      Evidence_URL: ''
    });

    const refreshedDocument = findObject(
      SHEETS.DOCUMENTS,
      'Record_ID',
      recordId
    );

    ensureReviewRowsAndRequests(
      refreshedDocument.values,
      versionNumber,
      reviewers,
      dueDate
    );

    updateObject(SHEETS.DOCUMENTS, refreshedDocument.rowNumber, {
      Status: 'In Review',
      Automation_Status: 'Completed',
      Last_Automation_Run: new Date(),
      Last_Updated: new Date(),
      Error_Message: ''
    });

    appendEvent(
      recordId,
      versionNumber,
      'VERSION_SUBMITTED',
      requesterEmail,
      JSON.stringify({
        title: title,
        reviewers: reviewers,
        files: movedFiles.names
      }),
      eventKey
    );

    sendOwnerReceipt(
      ownerEmail,
      recordId,
      versionLabel,
      title,
      dueDate,
      folders.record.getUrl()
    );

    setResponseResult(sheet, rowNumber, recordId, 'Completed', '');
    console.log('Processed intake for ' + recordId + ' ' + versionLabel);
  } catch (error) {
    setFailureSafely(source, rowNumber, recordId, error);
    markRecordError(recordId, error);
    recordError(
      source,
      rowNumber,
      recordId,
      'processIntakeRow',
      error
    );
    throw error;
  } finally {
    lock.releaseLock();
  }
}

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

  let recordId = '';
  const source = SETTINGS.REVIEW_SHEET_NAME;

  try {
    const sheet = getSpreadsheet().getSheetByName(source);
    ensureResponseColumns(sheet);

    const input = readRowObject(sheet, rowNumber);
    const eventKey = source + ':' + rowNumber;

    if (eventExists(eventKey, 'REVIEW_RESPONSE_PROCESSED')) {
      setResponseResult(
        sheet,
        rowNumber,
        String(input['Document ID'] || ''),
        'Completed',
        ''
      );
      return;
    }

    recordId = requireText(
      input['Document ID'],
      'Document ID',
      40
    ).toUpperCase();

    const versionNumber = parsePositiveInteger(
      input['Version Number'],
      'Version Number'
    );

    const reviewerEmail = normalizeEmail(
      firstValue(input, ['Email Address', 'Reviewer Email'])
    );
    requireEmail(reviewerEmail, 'Reviewer email');

    const decision = requireChoice(
      input['Decision'],
      ['Approve', 'Changes Requested', 'Reject'],
      'Decision'
    );

    const comments = String(input['Review Comments'] || '').trim();
    if (
      (decision === 'Changes Requested' || decision === 'Reject') &&
      !comments
    ) {
      throw new Error('Comments are required for this decision.');
    }

    const review = findReview(
      recordId,
      versionNumber,
      reviewerEmail
    );

    if (!review) {
      throw new Error(
        'No review assignment matches this document, version, and reviewer.'
      );
    }

    if (String(review.values.Status) !== 'Pending') {
      appendEvent(
        recordId,
        versionNumber,
        'DUPLICATE_REVIEW_IGNORED',
        reviewerEmail,
        'Existing review status: ' + review.values.Status,
        eventKey
      );
      setResponseResult(
        sheet,
        rowNumber,
        recordId,
        'Ignored',
        'The review was already completed or superseded.'
      );
      return;
    }

    const documentRecord = findObject(
      SHEETS.DOCUMENTS,
      'Record_ID',
      recordId
    );
    if (!documentRecord) {
      throw new Error('Document record was not found.');
    }

    if (
      Number(documentRecord.values.Current_Version) !== versionNumber
    ) {
      updateObject(SHEETS.REVIEWS, review.rowNumber, {
        Status: 'Superseded',
        Decision_At: new Date(),
        Comments: 'Response arrived after a newer version was submitted.'
      });

      appendEvent(
        recordId,
        versionNumber,
        'LATE_REVIEW_SUPERSEDED',
        reviewerEmail,
        comments,
        eventKey
      );

      setResponseResult(
        sheet,
        rowNumber,
        recordId,
        'Ignored',
        'A newer version is already current.'
      );
      return;
    }

    const markupIds = getFileIdsFromCell(
      sheet,
      rowNumber,
      'Marked-up File'
    );
    const markupUrl = moveMarkupFiles(
      markupIds,
      documentRecord.values,
      versionNumber,
      reviewerEmail
    );

    const reviewStatus =
      decision === 'Approve'
        ? 'Approved'
        : decision === 'Changes Requested'
          ? 'Changes Requested'
          : 'Rejected';

    updateObject(SHEETS.REVIEWS, review.rowNumber, {
      Status: reviewStatus,
      Decision_At: new Date(),
      Comments: comments,
      Markup_File_URL: markupUrl
    });

    appendEvent(
      recordId,
      versionNumber,
      'REVIEW_RESPONSE_PROCESSED',
      reviewerEmail,
      JSON.stringify({
        decision: decision,
        comments: comments,
        markupUrl: markupUrl
      }),
      eventKey
    );

    if (decision === 'Changes Requested') {
      cancelPendingReviews(
        recordId,
        versionNumber,
        'Cancelled after changes were requested.'
      );
      updateDocumentAndVersionStatus(
        recordId,
        versionNumber,
        'Changes Required',
        'Changes Required'
      );
      sendDecisionNotice(
        documentRecord.values,
        versionNumber,
        decision,
        reviewerEmail,
        comments,
        markupUrl
      );
    } else if (decision === 'Reject') {
      cancelPendingReviews(
        recordId,
        versionNumber,
        'Cancelled after rejection.'
      );
      updateDocumentAndVersionStatus(
        recordId,
        versionNumber,
        'Rejected',
        'Rejected'
      );
      sendDecisionNotice(
        documentRecord.values,
        versionNumber,
        decision,
        reviewerEmail,
        comments,
        markupUrl
      );
    } else {
      const summary = getReviewSummary(recordId, versionNumber);

      if (summary.total > 0 && summary.approved === summary.total) {
        finalizeApproval(recordId, versionNumber);
      } else {
        updateObject(
          SHEETS.DOCUMENTS,
          documentRecord.rowNumber,
          {
            Approval_Status:
              'Pending ' + summary.approved + '/' + summary.total,
            Last_Updated: new Date(),
            Last_Automation_Run: new Date()
          }
        );
      }
    }

    setResponseResult(sheet, rowNumber, recordId, 'Completed', '');
    console.log(
      'Processed review for ' + recordId + ' v' + versionNumber
    );
  } catch (error) {
    setFailureSafely(source, rowNumber, recordId, error);
    markRecordError(recordId, error);
    recordError(
      source,
      rowNumber,
      recordId,
      'processReviewRow',
      error
    );
    throw error;
  } finally {
    lock.releaseLock();
  }
}

function ensureReviewRowsAndRequests(
  documentValues,
  versionNumber,
  reviewers,
  dueDate
) {
  reviewers.forEach(function(reviewerEmail) {
    let review = findReview(
      documentValues.Record_ID,
      versionNumber,
      reviewerEmail
    );

    if (!review) {
      const reviewUrl = buildReviewUrl(
        documentValues.Record_ID,
        versionNumber
      );

      const rowNumber = appendObject(
        SHEETS.REVIEWS,
        REVIEW_HEADERS,
        {
          Review_ID: Utilities.getUuid(),
          Record_ID: documentValues.Record_ID,
          Version_Number: versionNumber,
          Reviewer_Email: reviewerEmail,
          Status: 'Pending',
          Due_Date: dueDate,
          Requested_At: new Date(),
          Request_Sent_At: '',
          Decision_At: '',
          Comments: '',
          Markup_File_URL: '',
          Last_Reminder_Date: '',
          Escalated_At: '',
          Review_Form_URL: reviewUrl
        }
      );

      review = {
        rowNumber: rowNumber,
        values: readRowObject(
          getSpreadsheet().getSheetByName(SHEETS.REVIEWS),
          rowNumber
        )
      };
    }

    if (
      String(review.values.Status) === 'Pending' &&
      !review.values.Request_Sent_At
    ) {
      sendReviewRequest(
        documentValues,
        versionNumber,
        reviewerEmail,
        dueDate,
        String(review.values.Review_Form_URL)
      );

      updateObject(SHEETS.REVIEWS, review.rowNumber, {
        Request_Sent_At: new Date()
      });
    }
  });
}

function sendReviewRequest(
  documentValues,
  versionNumber,
  reviewerEmail,
  dueDate,
  reviewUrl
) {
  const versionLabel = formatVersion(versionNumber);
  const subject =
    'Review requested: ' +
    documentValues.Record_ID +
    ' ' +
    versionLabel;

  const body =
    'A document requires your review.\n\n' +
    'Document: ' + documentValues.Title + '\n' +
    'ID: ' + documentValues.Record_ID + '\n' +
    'Version: ' + versionLabel + '\n' +
    'Due: ' + formatDate(dueDate) + '\n' +
    'Files: ' + documentValues.Current_Version_Folder_URL + '\n' +
    'Decision form: ' + reviewUrl + '\n\n' +
    'Use the decision form so the response is recorded against the correct version.';

  const html =
    '<p>A document requires your review.</p>' +
    '<p><b>Document:</b> ' +
    escapeHtml(documentValues.Title) +
    '<br><b>ID:</b> ' +
    escapeHtml(documentValues.Record_ID) +
    '<br><b>Version:</b> ' +
    escapeHtml(versionLabel) +
    '<br><b>Due:</b> ' +
    escapeHtml(formatDate(dueDate)) +
    '</p>' +
    '<p><a href="' +
    escapeHtml(documentValues.Current_Version_Folder_URL) +
    '">Open the controlled version</a></p>' +
    '<p><a href="' +
    escapeHtml(reviewUrl) +
    '">Submit your review decision</a></p>';

  sendMailWithRetry({
    to: reviewerEmail,
    subject: subject,
    body: body,
    htmlBody: html,
    name: 'Document Control'
  });
}

function sendOwnerReceipt(
  ownerEmail,
  recordId,
  versionLabel,
  title,
  dueDate,
  recordFolderUrl
) {
  sendMailWithRetry({
    to: ownerEmail,
    subject: 'Document review started: ' + recordId + ' ' + versionLabel,
    body:
      'The document review has started.\n\n' +
      'Document: ' + title + '\n' +
      'ID: ' + recordId + '\n' +
      'Version: ' + versionLabel + '\n' +
      'Review due date: ' + formatDate(dueDate) + '\n' +
      'Controlled folder: ' + recordFolderUrl,
    name: 'Document Control'
  });
}

function sendDecisionNotice(
  documentValues,
  versionNumber,
  decision,
  reviewerEmail,
  comments,
  markupUrl
) {
  const body =
    'A reviewer submitted a decision.\n\n' +
    'Document: ' + documentValues.Title + '\n' +
    'ID: ' + documentValues.Record_ID + '\n' +
    'Version: ' + formatVersion(versionNumber) + '\n' +
    'Reviewer: ' + reviewerEmail + '\n' +
    'Decision: ' + decision + '\n' +
    'Comments: ' + comments + '\n' +
    (markupUrl ? 'Markup: ' + markupUrl + '\n' : '') +
    '\nSubmit a new version through the intake form if further review is required.';

  sendMailWithRetry({
    to: documentValues.Owner_Email,
    subject:
      decision + ': ' +
      documentValues.Record_ID + ' ' +
      formatVersion(versionNumber),
    body: body,
    name: 'Document Control'
  });
}

function finalizeApproval(recordId, versionNumber) {
  const documentRecord = findObject(
    SHEETS.DOCUMENTS,
    'Record_ID',
    recordId
  );
  const versionRecord = findVersion(recordId, versionNumber);

  if (!documentRecord || !versionRecord) {
    throw new Error('Document or version record was not found.');
  }

  if (Number(documentRecord.values.Current_Version) !== versionNumber) {
    throw new Error('Only the current version can be published.');
  }

  const versionLabel = formatVersion(versionNumber);
  const folders = foldersFromDocument(documentRecord.values);
  const sourceFolder = folderFromUrl(
    versionRecord.values.Version_Folder_URL
  );

  const currentPublicationState = String(
    documentRecord.values.Publication_Version || ''
  );

  if (
    currentPublicationState !== versionLabel &&
    currentPublicationState !== 'Publishing ' + versionLabel
  ) {
    archivePublishedFiles(
      folders.published,
      folders.archive,
      currentPublicationState || 'previous'
    );

    updateObject(SHEETS.DOCUMENTS, documentRecord.rowNumber, {
      Status: 'Approved',
      Approval_Status: 'Approved',
      Publication_Version: 'Publishing ' + versionLabel,
      Last_Automation_Run: new Date()
    });
  }

  copyVersionFilesIdempotently(sourceFolder, folders.published);

  let evidenceUrl = String(versionRecord.values.Evidence_URL || '');
  if (!evidenceUrl) {
    evidenceUrl = createApprovalEvidence(
      documentRecord.values,
      versionRecord.values,
      getReviews(recordId, versionNumber),
      folders.evidence
    );

    updateObject(SHEETS.VERSIONS, versionRecord.rowNumber, {
      Evidence_URL: evidenceUrl,
      Approved_At: new Date(),
      Version_Status: 'Approved'
    });
  }

  updateObject(SHEETS.DOCUMENTS, documentRecord.rowNumber, {
    Status: 'Published',
    Approval_Status: 'Approved',
    Publication_Version: versionLabel,
    Published_Document_URL: folders.published.getUrl(),
    Last_Approved_At: new Date(),
    Last_Updated: new Date(),
    Last_Automation_Run: new Date(),
    Automation_Status: 'Completed',
    Error_Message: ''
  });

  appendEvent(
    recordId,
    versionNumber,
    'VERSION_PUBLISHED',
    'Apps Script',
    JSON.stringify({
      publishedFolder: folders.published.getUrl(),
      evidenceUrl: evidenceUrl
    }),
    ''
  );

  sendMailWithRetry({
    to: documentRecord.values.Owner_Email,
    cc: SETTINGS.ADMIN_EMAIL,
    subject: 'Published: ' + recordId + ' ' + versionLabel,
    body:
      'All required reviewers approved the document.\n\n' +
      'Published folder: ' + folders.published.getUrl() + '\n' +
      'Approval evidence: ' + evidenceUrl,
    name: 'Document Control'
  });
}

function createApprovalEvidence(
  documentValues,
  versionValues,
  reviews,
  evidenceFolder
) {
  const title =
    documentValues.Record_ID + ' ' +
    formatVersion(versionValues.Version_Number) +
    ' Approval Evidence';

  const evidenceDocument = DocumentApp.create(title);
  const body = evidenceDocument.getBody();

  body.appendParagraph(title)
    .setHeading(DocumentApp.ParagraphHeading.HEADING1);
  body.appendParagraph('Document title: ' + documentValues.Title);
  body.appendParagraph('Document type: ' + documentValues.Document_Type);
  body.appendParagraph('Owner: ' + documentValues.Owner_Email);
  body.appendParagraph(
    'Submitted: ' + formatDateTime(versionValues.Submitted_At)
  );
  body.appendParagraph(
    'Version: ' + formatVersion(versionValues.Version_Number)
  );
  body.appendParagraph('Change summary: ' + versionValues.Change_Summary);
  body.appendParagraph('Review decisions')
    .setHeading(DocumentApp.ParagraphHeading.HEADING2);

  const tableRows = [
    ['Reviewer', 'Status', 'Decision time', 'Comments']
  ];

  reviews.forEach(function(review) {
    tableRows.push([
      String(review.Reviewer_Email),
      String(review.Status),
      formatDateTime(review.Decision_At),
      String(review.Comments || '')
    ]);
  });

  body.appendTable(tableRows);
  body.appendParagraph(
    'Evidence generated: ' + formatDateTime(new Date())
  );
  body.appendParagraph(
    'This file records workflow evidence. It does not replace any legally required signature method.'
  );

  evidenceDocument.saveAndClose();

  const temporaryFile = DriveApp.getFileById(evidenceDocument.getId());
  const pdfBlob = temporaryFile
    .getAs(MimeType.PDF)
    .setName(
      documentValues.Record_ID + '_' +
      formatVersion(versionValues.Version_Number) +
      '_approval_evidence.pdf'
    );

  const pdfFile = withRetry(function() {
    return evidenceFolder.createFile(pdfBlob);
  }, 'Create approval evidence PDF', 3);

  temporaryFile.setTrashed(true);
  return pdfFile.getUrl();
}

function sendReviewReminders() {
  const reviews = getAllObjects(SHEETS.REVIEWS);
  const today = dateOnly(new Date());

  reviews.forEach(function(review) {
    if (String(review.values.Status) !== 'Pending') {
      return;
    }

    try {
      const dueDate = dateOnly(new Date(review.values.Due_Date));
      const daysUntilDue = Math.floor(
        (dueDate.getTime() - today.getTime()) / 86400000
      );

      if (daysUntilDue <= 2) {
        const lastReminder = review.values.Last_Reminder_Date
          ? formatDate(review.values.Last_Reminder_Date)
          : '';

        if (lastReminder !== formatDate(today)) {
          const documentRecord = findObject(
            SHEETS.DOCUMENTS,
            'Record_ID',
            review.values.Record_ID
          );

          if (!documentRecord) {
            throw new Error('Document was not found for pending review.');
          }

          sendMailWithRetry({
            to: review.values.Reviewer_Email,
            subject:
              (daysUntilDue < 0 ? 'Overdue review: ' : 'Review reminder: ') +
              review.values.Record_ID + ' ' +
              formatVersion(review.values.Version_Number),
            body:
              'The review is ' +
              (daysUntilDue < 0 ? 'overdue.' : 'due soon.') + '\n\n' +
              'Document: ' + documentRecord.values.Title + '\n' +
              'Due: ' + formatDate(dueDate) + '\n' +
              'Decision form: ' + review.values.Review_Form_URL,
            name: 'Document Control'
          });

          updateObject(SHEETS.REVIEWS, review.rowNumber, {
            Last_Reminder_Date: new Date()
          });
        }
      }

      const overdueDays = -daysUntilDue;
      if (overdueDays >= 2 && !review.values.Escalated_At) {
        const documentRecord = findObject(
          SHEETS.DOCUMENTS,
          'Record_ID',
          review.values.Record_ID
        );
        const escalationEmail = getConfigValue('ESCALATION_EMAIL');

        sendMailWithRetry({
          to: escalationEmail,
          cc: documentRecord.values.Owner_Email,
          subject: 'Escalation: overdue document review',
          body:
            'The following review is at least two days overdue.\n\n' +
            'Document ID: ' + review.values.Record_ID + '\n' +
            'Version: ' + formatVersion(review.values.Version_Number) + '\n' +
            'Reviewer: ' + review.values.Reviewer_Email + '\n' +
            'Due: ' + formatDate(dueDate),
          name: 'Document Control'
        });

        updateObject(SHEETS.REVIEWS, review.rowNumber, {
          Escalated_At: new Date()
        });

        appendEvent(
          review.values.Record_ID,
          review.values.Version_Number,
          'REVIEW_ESCALATED',
          'Apps Script',
          'Reviewer: ' + review.values.Reviewer_Email,
          ''
        );
      }
    } catch (error) {
      recordError(
        'Scheduled Reminder',
        review.rowNumber,
        String(review.values.Record_ID || ''),
        'sendReviewReminders',
        error
      );
    }
  });
}

function retryIntakeRowPrompt() {
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt(
    'Retry intake row',
    'Enter the Intake Responses row number.',
    ui.ButtonSet.OK_CANCEL
  );

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

  const rowNumber = Number(response.getResponseText());
  if (!Number.isInteger(rowNumber) || rowNumber < 2) {
    throw new Error('Enter a valid response row number of 2 or greater.');
  }

  processIntakeRow(rowNumber);
}

function retryRecordPrompt() {
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt(
    'Retry record',
    'Enter the document Record ID.',
    ui.ButtonSet.OK_CANCEL
  );

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

  const recordId = response.getResponseText().trim().toUpperCase();
  retryRecordInternal(recordId);
}

function retryRecordInternal(recordId) {
  const documentRecord = findObject(
    SHEETS.DOCUMENTS,
    'Record_ID',
    recordId
  );
  if (!documentRecord) {
    throw new Error('Record was not found: ' + recordId);
  }

  const versionNumber = Number(documentRecord.values.Current_Version);
  const summary = getReviewSummary(recordId, versionNumber);

  if (summary.total > 0 && summary.approved === summary.total) {
    finalizeApproval(recordId, versionNumber);
    return;
  }

  const reviewers = parseEmailList(
    documentRecord.values.Required_Reviewers
  );

  ensureReviewRowsAndRequests(
    documentRecord.values,
    versionNumber,
    reviewers,
    new Date(documentRecord.values.Due_Date)
  );

  updateObject(SHEETS.DOCUMENTS, documentRecord.rowNumber, {
    Status: 'In Review',
    Automation_Status: 'Completed',
    Error_Message: '',
    Retry_Count: Number(documentRecord.values.Retry_Count || 0) + 1,
    Last_Automation_Run: new Date(),
    Last_Updated: new Date()
  });

  appendEvent(
    recordId,
    versionNumber,
    'RECORD_RETRIED',
    Session.getActiveUser().getEmail() || 'Administrator',
    'Pending review requests and publication state were reconciled.',
    ''
  );
}

function updateDocumentAndVersionStatus(
  recordId,
  versionNumber,
  documentStatus,
  versionStatus
) {
  const documentRecord = findObject(
    SHEETS.DOCUMENTS,
    'Record_ID',
    recordId
  );
  const versionRecord = findVersion(recordId, versionNumber);

  updateObject(SHEETS.DOCUMENTS, documentRecord.rowNumber, {
    Status: documentStatus,
    Approval_Status: documentStatus,
    Last_Updated: new Date(),
    Last_Automation_Run: new Date(),
    Automation_Status: 'Completed'
  });

  updateObject(SHEETS.VERSIONS, versionRecord.rowNumber, {
    Version_Status: versionStatus
  });
}

function cancelPendingReviews(recordId, versionNumber, reason) {
  getAllObjects(SHEETS.REVIEWS).forEach(function(review) {
    if (
      String(review.values.Record_ID) === String(recordId) &&
      Number(review.values.Version_Number) === Number(versionNumber) &&
      String(review.values.Status) === 'Pending'
    ) {
      updateObject(SHEETS.REVIEWS, review.rowNumber, {
        Status: 'Superseded',
        Comments: reason
      });
    }
  });
}

function getReviewSummary(recordId, versionNumber) {
  const reviews = getReviews(recordId, versionNumber);
  return {
    total: reviews.length,
    approved: reviews.filter(function(review) {
      return String(review.Status) === 'Approved';
    }).length
  };
}

function getReviews(recordId, versionNumber) {
  return getAllObjects(SHEETS.REVIEWS)
    .filter(function(review) {
      return (
        String(review.values.Record_ID) === String(recordId) &&
        Number(review.values.Version_Number) === Number(versionNumber)
      );
    })
    .map(function(review) {
      return review.values;
    });
}

function findReview(recordId, versionNumber, reviewerEmail) {
  const matches = getAllObjects(SHEETS.REVIEWS).filter(function(review) {
    return (
      String(review.values.Record_ID) === String(recordId) &&
      Number(review.values.Version_Number) === Number(versionNumber) &&
      normalizeEmail(review.values.Reviewer_Email) ===
        normalizeEmail(reviewerEmail)
    );
  });

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

function findVersion(recordId, versionNumber) {
  const matches = getAllObjects(SHEETS.VERSIONS).filter(function(version) {
    return (
      String(version.values.Record_ID) === String(recordId) &&
      Number(version.values.Version_Number) === Number(versionNumber)
    );
  });

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

function eventExists(sourceEventKey, eventType) {
  return getAllObjects(SHEETS.EVENTS).some(function(event) {
    return (
      String(event.values.Source_Event_Key) === String(sourceEventKey) &&
      String(event.values.Event_Type) === String(eventType)
    );
  });
}

function appendEvent(
  recordId,
  versionNumber,
  eventType,
  actor,
  details,
  sourceEventKey
) {
  appendObject(SHEETS.EVENTS, EVENT_HEADERS, {
    Event_ID: Utilities.getUuid(),
    Timestamp: new Date(),
    Record_ID: recordId,
    Version_Number: versionNumber || '',
    Event_Type: eventType,
    Actor: actor || '',
    Details: details || '',
    Source_Event_Key: sourceEventKey || ''
  });
}

function recordError(
  source,
  rowNumber,
  recordId,
  functionName,
  error
) {
  appendObject(SHEETS.ERRORS, ERROR_HEADERS, {
    Error_ID: Utilities.getUuid(),
    Timestamp: new Date(),
    Source: source,
    Row_Number: rowNumber || '',
    Record_ID: recordId || '',
    Function_Name: functionName,
    Error_Message: error && error.message
      ? error.message
      : String(error),
    Stack: error && error.stack ? error.stack : '',
    Status: 'Open',
    Retry_Count: 0,
    Resolved_At: ''
  });

  console.error(functionName + ': ' + String(error));
}

function markRecordError(recordId, error) {
  if (!recordId) {
    return;
  }

  const record = findObject(SHEETS.DOCUMENTS, 'Record_ID', recordId);
  if (!record) {
    return;
  }

  updateObject(SHEETS.DOCUMENTS, record.rowNumber, {
    Status: 'Automation Error',
    Automation_Status: 'Failed',
    Error_Message: error.message || String(error),
    Retry_Count: Number(record.values.Retry_Count || 0) + 1,
    Last_Automation_Run: new Date(),
    Last_Updated: new Date()
  });
}

function setFailureSafely(sheetName, rowNumber, recordId, error) {
  try {
    const sheet = getSpreadsheet().getSheetByName(sheetName);
    if (sheet) {
      ensureResponseColumns(sheet);
      setResponseResult(
        sheet,
        rowNumber,
        recordId,
        'Failed',
        error.message || String(error)
      );
    }
  } catch (secondaryError) {
    console.error('Could not update response failure status: ' + secondaryError);
  }
}

function ensureDocumentFolders(recordId, title, department) {
  const root = DriveApp.getFolderById(SETTINGS.ROOT_FOLDER_ID);
  const departmentFolder = getOrCreateChildFolder(
    root,
    sanitizeName(department)
  );
  const recordFolder = getOrCreateChildFolder(
    departmentFolder,
    recordId + ' - ' + sanitizeName(title)
  );

  return {
    record: recordFolder,
    versions: getOrCreateChildFolder(recordFolder, '01_Versions'),
    published: getOrCreateChildFolder(recordFolder, '02_Published'),
    archive: getOrCreateChildFolder(recordFolder, '03_Archive'),
    evidence: getOrCreateChildFolder(recordFolder, '04_Evidence')
  };
}

function foldersFromDocument(documentValues) {
  return {
    record: folderFromUrl(documentValues.Record_Folder_URL),
    versions: folderFromUrl(documentValues.Versions_Folder_URL),
    published: folderFromUrl(documentValues.Published_Folder_URL),
    archive: folderFromUrl(documentValues.Archive_Folder_URL),
    evidence: folderFromUrl(documentValues.Evidence_Folder_URL)
  };
}

function getOrCreateChildFolder(parent, name) {
  const folders = parent.getFoldersByName(name);
  if (folders.hasNext()) {
    return folders.next();
  }

  return withRetry(function() {
    return parent.createFolder(name);
  }, 'Create folder ' + name, 3);
}

function moveSubmissionFiles(
  fileIds,
  versionFolder,
  recordId,
  versionLabel
) {
  const result = { ids: [], urls: [], names: [] };

  fileIds.forEach(function(fileId, index) {
    const file = DriveApp.getFileById(fileId);
    const originalName = file.getName();
    const extensionIndex = originalName.lastIndexOf('.');
    const extension =
      extensionIndex > 0 ? originalName.substring(extensionIndex) : '';
    const baseName =
      extensionIndex > 0
        ? originalName.substring(0, extensionIndex)
        : originalName;

    const newName =
      recordId + '_' +
      versionLabel + '_' +
      String(index + 1).padStart(2, '0') + '_' +
      sanitizeName(baseName) +
      extension;

    withRetry(function() {
      file.setName(newName);
      file.moveTo(versionFolder);
      return true;
    }, 'Move uploaded file ' + fileId, 3);

    result.ids.push(file.getId());
    result.urls.push(file.getUrl());
    result.names.push(newName);
  });

  return result;
}

function moveMarkupFiles(
  fileIds,
  documentValues,
  versionNumber,
  reviewerEmail
) {
  if (!fileIds.length) {
    return '';
  }

  const versionFolder = folderFromUrl(
    documentValues.Current_Version_Folder_URL
  );
  const markupRoot = getOrCreateChildFolder(
    versionFolder,
    'Review_Markups'
  );
  const reviewerFolder = getOrCreateChildFolder(
    markupRoot,
    sanitizeName(reviewerEmail)
  );

  const urls = [];

  fileIds.forEach(function(fileId, index) {
    const file = DriveApp.getFileById(fileId);
    file.setName(
      documentValues.Record_ID + '_' +
      formatVersion(versionNumber) + '_markup_' +
      String(index + 1).padStart(2, '0') + '_' +
      sanitizeName(file.getName())
    );
    file.moveTo(reviewerFolder);
    urls.push(file.getUrl());
  });

  return urls.join(',');
}

function archivePublishedFiles(
  publishedFolder,
  archiveFolder,
  previousVersion
) {
  const files = publishedFolder.getFiles();
  if (!files.hasNext()) {
    return;
  }

  const archiveName =
    sanitizeName(previousVersion) +
    '_superseded_' +
    Utilities.formatDate(
      new Date(),
      getSpreadsheet().getSpreadsheetTimeZone(),
      'yyyyMMdd_HHmmss'
    );

  const destination = getOrCreateChildFolder(
    archiveFolder,
    archiveName
  );

  while (files.hasNext()) {
    files.next().moveTo(destination);
  }
}

function copyVersionFilesIdempotently(sourceFolder, publishedFolder) {
  const files = sourceFolder.getFiles();
  let copiedCount = 0;

  while (files.hasNext()) {
    const file = files.next();
    const existing = publishedFolder.getFilesByName(file.getName());

    if (!existing.hasNext()) {
      withRetry(function() {
        file.makeCopy(file.getName(), publishedFolder);
        return true;
      }, 'Publish file ' + file.getId(), 3);
    }

    copiedCount++;
  }

  if (copiedCount === 0) {
    throw new Error('The version folder contains no publishable files.');
  }
}

function getFileIdsFromCell(sheet, rowNumber, headerName) {
  const headers = getHeaders(sheet);
  const columnIndex = headers.indexOf(headerName) + 1;

  if (columnIndex < 1) {
    return [];
  }

  const range = sheet.getRange(rowNumber, columnIndex);
  const candidates = [];

  const richText = range.getRichTextValue();
  if (richText) {
    richText.getRuns().forEach(function(run) {
      if (run.getLinkUrl()) {
        candidates.push(run.getLinkUrl());
      }
    });
  }

  candidates.push(String(range.getDisplayValue() || ''));
  candidates.push(String(range.getFormula() || ''));

  return uniqueValues(
    candidates.reduce(function(ids, value) {
      return ids.concat(extractDriveIds(value));
    }, [])
  );
}

function extractDriveIds(value) {
  const text = String(value || '');
  const ids = [];
  const patterns = [
    /\/d\/([A-Za-z0-9_-]{20,})/g,
    /[?&]id=([A-Za-z0-9_-]{20,})/g,
    /([A-Za-z0-9_-]{25,})/g
  ];

  patterns.forEach(function(pattern) {
    let match;
    while ((match = pattern.exec(text)) !== null) {
      ids.push(match[1]);
    }
  });

  return uniqueValues(ids);
}

function buildReviewUrl(recordId, versionNumber) {
  const separator =
    SETTINGS.REVIEW_FORM_URL.indexOf('?') === -1 ? '?' : '&';

  return (
    SETTINGS.REVIEW_FORM_URL +
    separator +
    'usp=pp_url&' +
    SETTINGS.REVIEW_FORM_DOCUMENT_ENTRY +
    '=' +
    encodeURIComponent(recordId) +
    '&' +
    SETTINGS.REVIEW_FORM_VERSION_ENTRY +
    '=' +
    encodeURIComponent(String(versionNumber))
  );
}

function generateRecordId() {
  const properties = PropertiesService.getScriptProperties();
  const period = Utilities.formatDate(
    new Date(),
    getSpreadsheet().getSpreadsheetTimeZone(),
    'yyyyMM'
  );
  const key = 'DOCUMENT_SEQUENCE_' + period;
  const next = Number(properties.getProperty(key) || '0') + 1;
  properties.setProperty(key, String(next));

  return 'DOC-' + period + '-' + String(next).padStart(4, '0');
}

function getRouteReviewers(documentType) {
  const keyMap = {
    'Policy': 'ROUTE_POLICY',
    'Procedure': 'ROUTE_PROCEDURE',
    'Customer Document': 'ROUTE_CUSTOMER_DOCUMENT',
    'Marketing Material': 'ROUTE_MARKETING_MATERIAL',
    'Other': 'ROUTE_OTHER'
  };

  const key = keyMap[documentType];
  if (!key) {
    throw new Error('No route key exists for document type: ' + documentType);
  }

  const reviewers = parseEmailList(getConfigValue(key));
  if (!reviewers.length) {
    throw new Error('No reviewers are configured for ' + documentType);
  }

  return reviewers;
}

function getAdminEmails() {
  return uniqueValues(
    parseEmailList(getConfigValue('ADMIN_EMAILS')).concat([
      normalizeEmail(SETTINGS.ADMIN_EMAIL)
    ])
  );
}

function seedConfig() {
  const defaults = [
    ['ADMIN_EMAILS', 'YOUR_EMAIL_ADDRESS', 'Comma-separated workflow administrators.'],
    ['ESCALATION_EMAIL', 'YOUR_EMAIL_ADDRESS', 'Recipient for overdue review escalations.'],
    ['ROUTE_POLICY', 'YOUR_EMAIL_ADDRESS', 'Required reviewers for policies.'],
    ['ROUTE_PROCEDURE', 'YOUR_EMAIL_ADDRESS', 'Required reviewers for procedures.'],
    ['ROUTE_CUSTOMER_DOCUMENT', 'YOUR_EMAIL_ADDRESS', 'Required reviewers for customer documents.'],
    ['ROUTE_MARKETING_MATERIAL', 'YOUR_EMAIL_ADDRESS', 'Required reviewers for marketing materials.'],
    ['ROUTE_OTHER', 'YOUR_EMAIL_ADDRESS', 'Required reviewers for other documents.']
  ];

  defaults.forEach(function(row) {
    if (!findObject(SHEETS.CONFIG, 'Key', row[0])) {
      appendObject(SHEETS.CONFIG, CONFIG_HEADERS, {
        Key: row[0],
        Value: row[1],
        Description: row[2]
      });
    }
  });
}

function getConfigValue(key) {
  const item = findObject(SHEETS.CONFIG, 'Key', key);
  if (!item) {
    throw new Error('Missing Config key: ' + key);
  }

  const value = String(item.values.Value || '').trim();
  if (!value) {
    throw new Error('Config value is empty: ' + key);
  }

  return value;
}

function validateSettings() {
  const values = [
    SETTINGS.SPREADSHEET_ID,
    SETTINGS.ROOT_FOLDER_ID,
    SETTINGS.REVIEW_FORM_URL,
    SETTINGS.REVIEW_FORM_DOCUMENT_ENTRY,
    SETTINGS.REVIEW_FORM_VERSION_ENTRY,
    SETTINGS.ADMIN_EMAIL
  ];

  if (values.some(function(value) {
    return String(value).indexOf('YOUR_') !== -1;
  })) {
    throw new Error('Replace all YOUR_ placeholder settings before deployment.');
  }
}

function getSpreadsheet() {
  return SpreadsheetApp.openById(SETTINGS.SPREADSHEET_ID);
}

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

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

  const currentHeaders = getHeaders(sheet);
  headers.forEach(function(header) {
    if (currentHeaders.indexOf(header) === -1) {
      sheet.getRange(1, sheet.getLastColumn() + 1).setValue(header);
      currentHeaders.push(header);
    }
  });

  sheet.setFrozenRows(1);
  return sheet;
}

function ensureResponseColumns(sheet) {
  const required = [
    'Automation Record ID',
    'Automation Status',
    'Automation Error'
  ];
  const headers = getHeaders(sheet);

  required.forEach(function(header) {
    if (headers.indexOf(header) === -1) {
      sheet.getRange(1, sheet.getLastColumn() + 1).setValue(header);
      headers.push(header);
    }
  });
}

function getHeaders(sheet) {
  if (sheet.getLastColumn() === 0) {
    return [];
  }

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

function readRowObject(sheet, rowNumber) {
  const headers = getHeaders(sheet);
  const values = sheet
    .getRange(rowNumber, 1, 1, headers.length)
    .getValues()[0];
  const result = {};

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

  return result;
}

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

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

function updateObject(sheetName, rowNumber, updates) {
  const sheet = getSpreadsheet().getSheetByName(sheetName);
  const headers = getHeaders(sheet);
  const row = sheet
    .getRange(rowNumber, 1, 1, headers.length)
    .getValues()[0];

  Object.keys(updates).forEach(function(key) {
    const index = headers.indexOf(key);
    if (index === -1) {
      throw new Error(
        'Header ' + key + ' was not found in sheet ' + sheetName
      );
    }
    row[index] = updates[key];
  });

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

function findObject(sheetName, fieldName, value) {
  const objects = getAllObjects(sheetName);
  for (let index = 0; index < objects.length; index++) {
    if (
      String(objects[index].values[fieldName]) === String(value)
    ) {
      return objects[index];
    }
  }
  return null;
}

function getAllObjects(sheetName) {
  const sheet = getSpreadsheet().getSheetByName(sheetName);
  if (!sheet || sheet.getLastRow() < 2) {
    return [];
  }

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

  return values.map(function(row, index) {
    const object = {};
    headers.forEach(function(header, columnIndex) {
      object[header] = row[columnIndex];
    });

    return {
      rowNumber: index + 2,
      values: object
    };
  });
}

function setResponseResult(
  sheet,
  rowNumber,
  recordId,
  status,
  errorMessage
) {
  const headers = getHeaders(sheet);
  const updates = {
    'Automation Record ID': recordId || '',
    'Automation Status': status || '',
    'Automation Error': errorMessage || ''
  };

  Object.keys(updates).forEach(function(header) {
    const column = headers.indexOf(header) + 1;
    sheet.getRange(rowNumber, column).setValue(updates[header]);
  });
}

function firstValue(object, keys) {
  for (let index = 0; index < keys.length; index++) {
    if (object[keys[index]] !== '' && object[keys[index]] != null) {
      return object[keys[index]];
    }
  }
  return '';
}

function requireText(value, label, maximumLength) {
  const text = String(value || '').trim();
  if (!text) {
    throw new Error(label + ' is required.');
  }
  if (maximumLength && text.length > maximumLength) {
    throw new Error(
      label + ' exceeds the maximum length of ' + maximumLength + '.'
    );
  }
  return text;
}

function requireChoice(value, allowed, label) {
  const text = String(value || '').trim();
  if (allowed.indexOf(text) === -1) {
    throw new Error(
      label + ' must be one of: ' + allowed.join(', ')
    );
  }
  return text;
}

function parsePositiveInteger(value, label) {
  const number = Number(value);
  if (!Number.isInteger(number) || number < 1) {
    throw new Error(label + ' must be a positive integer.');
  }
  return number;
}

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

function requireEmail(value, label) {
  const email = normalizeEmail(value);
  const valid = /^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(email);
  if (!valid) {
    throw new Error(label + ' is not a valid email address.');
  }
  return email;
}

function parseEmailList(value) {
  return uniqueValues(
    String(value || '')
      .split(/[,;\n]+/)
      .map(normalizeEmail)
      .filter(function(email) {
        return email.length > 0;
      })
  );
}

function uniqueValues(values) {
  return Array.from(new Set(values));
}

function parseRequiredDate(value, label) {
  const date = value instanceof Date ? new Date(value) : new Date(value);
  if (isNaN(date.getTime())) {
    throw new Error(label + ' is not a valid date.');
  }
  return date;
}

function dateOnly(value) {
  const date = new Date(value);
  return new Date(date.getFullYear(), date.getMonth(), date.getDate());
}

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

  const date = new Date(value);
  if (isNaN(date.getTime())) {
    return '';
  }

  return Utilities.formatDate(
    date,
    getSpreadsheet().getSpreadsheetTimeZone(),
    'yyyy-MM-dd'
  );
}

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

  const date = new Date(value);
  if (isNaN(date.getTime())) {
    return '';
  }

  return Utilities.formatDate(
    date,
    getSpreadsheet().getSpreadsheetTimeZone(),
    'yyyy-MM-dd HH:mm:ss'
  );
}

function formatVersion(versionNumber) {
  return 'v' + String(versionNumber).padStart(2, '0');
}

function sanitizeName(value) {
  return String(value || '')
    .replace(/[\\/:*?"<>|#%{}[\]]/g, '_')
    .replace(/\s+/g, ' ')
    .trim()
    .substring(0, 120);
}

function folderFromUrl(url) {
  const ids = extractDriveIds(url);
  if (!ids.length) {
    throw new Error('A folder ID could not be extracted from: ' + url);
  }
  return DriveApp.getFolderById(ids[0]);
}

function sendMailWithRetry(message) {
  return withRetry(function() {
    MailApp.sendEmail(message);
    return true;
  }, 'Send email to ' + message.to, 3);
}

function withRetry(operation, label, attempts) {
  let lastError;

  for (let attempt = 1; attempt <= attempts; attempt++) {
    try {
      return operation();
    } catch (error) {
      lastError = error;
      console.warn(
        label + ' failed on attempt ' + attempt + ': ' + error
      );

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

  throw new Error(label + ' failed after retries: ' + lastError);
}

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

After pasting the code, complete these deployment actions:

1. Replace the configuration placeholders.
2. Save the project.
3. Run setupControlSheets manually and approve the requested Google permissions.
4. Replace every placeholder route in the Config sheet with valid organizational email addresses.
5. Submit a test response to each Form and confirm that the file-upload cells contain accessible Drive links.
6. Run installTriggers while signed in as the intended automation owner.
7. Open the Apps Script Executions area to inspect trigger history, duration, logs, and errors.

The trigger is form-based for intake and review decisions. The reminder trigger is time-based and normally executes during the selected hour rather than at an exact minute. No edit-based trigger is required.

Duplicate execution is prevented through source event keys, response-row identifiers, unique version keys, and existing review checks. Publication uses a transitional Publishing vNN value and checks destination filenames before copying again.

Likely setup errors include incorrect sheet names, inaccessible uploaded files, a trigger owner without Drive permission, invalid prefilled form entry parameters, and placeholder reviewer addresses left in Config. Correct the configuration and use the retry menu rather than deleting partial records.

Failure Handling and Operational Reliability

The Errors sheet functions as a manual-review queue. Staff should not mark an error resolved until they confirm the response row, document row, version row, review rows, files, and event history agree.

The implementation does not have a separate message broker, so the Errors sheet serves as the practical dead-letter queue. Each open error contains the source, row number, record ID, function, message, stack, and retry count.

Idempotency reduces duplicate records but cannot guarantee that an email is never delivered twice. Email delivery and the subsequent timestamp update are separate actions. A rare failure between them can cause a repeated request. The review form still accepts only the pending assignment, so duplicate emails do not create duplicate approvals.

Perform a daily reconciliation during initial operation:

1. Count successful intake responses and compare them with Versions source event keys.
2. Confirm every In Review version has the expected number of review rows.
3. Confirm every Published document has a publication version and evidence URL.
4. Confirm every Failed response has an open Errors row.
5. Investigate any record whose Last Automation Run is older than its latest response.

A Complete Example

The following is a simulated record using the same representative manufacturer.

1. An operations engineer submits a new Procedure titled Hydraulic Lockout Procedure on July 8, 2026.
2. The intake contains Operations & Manufacturing as the department, Internal as the confidentiality level, Internal Controlled Library as the destination, and July 15, 2026 as the review due date.
3. The uploaded source file is Hydraulic Lockout Procedure.docx. The owner provides one additional technical reviewer.
4. The form submission reaches row 42 of Intake Responses. The source event key is Intake Responses:42.
5. The script acquires a lock and generates DOC-202607-0017.
6. The Procedure route adds the configured operations document-control reviewer. The additional technical reviewer is deduplicated, and the owner is excluded from approval.
7. The script creates the document folder and v01 version folder. The file becomes DOC-202607-0017_v01_01_Hydraulic_Lockout_Procedure.docx.
8. A Documents row is created with status Processing. A Versions row is created with Version Key DOC-202607-0017-v01.
9. Two Reviews rows are created. Each reviewer receives a prefilled decision-form URL containing document ID DOC-202607-0017 and version 1.
10. The technical reviewer selects Changes Requested and comments that the stored-energy verification step is missing. The response is matched using the authenticated email address.
11. The document moves to Changes Required. The other pending review is marked Superseded, and the owner receives the comment.
12. The owner updates the procedure and submits New Version with the existing document ID. The script creates v02 without altering v01.
13. Both required reviewers approve v02. The script evaluates two approved reviews out of two required reviews.
14. The approved v02 file is copied to Published. There was no prior publication, so no archive move is required.
15. An evidence PDF records the owner, change summary, both reviewers, approval timestamps, and comments.
16. The Documents row changes to Published, Approval Status becomes Approved, Publication Version becomes v02, and the evidence URL is stored in the Versions row.

If evidence creation had failed after the approved file was copied, the document would have entered Automation Error. A retry would find the existing published filename, avoid another copy, recreate the missing evidence, and complete the status update.

Implementation Cost

All amounts below are representative assumptions, not verified client costs. Google Workspace licensing, storage, sending limits, and feature availability must be confirmed for the organization’s existing agreement.

The maintenance labour is included in the savings calculation as time consumed by the new process. It is not treated as free merely because an internal employee performs it.

Estimated Time and Cost Savings

The estimate considers administrative handling, version coordination, reminders, filing, and evidence preparation. It excludes the substantive professional time required to read and evaluate a document because human review still occurs.

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

Actual calculation: 45 × 55 ÷ 60 = 41.25 hours

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

Actual calculation: 45 × 18 ÷ 60 + 45 × 12% × 20 ÷ 60 + 2 = 17.30 hours

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

Actual calculation: 41.25 − 17.30 = 23.95 hours

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

Actual calculation: 23.95 × $48 = $1,149.60

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

Actual calculation: $1,149.60 − $0 incremental core software = $1,149.60

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

Actual calculation: $10,364 ÷ $1,149.60 = approximately 9.0 months

Recovered time does not automatically reduce payroll. It can represent additional document-processing capacity, quicker turnaround, reduced overtime, less dependence on one coordinator, and more time for substantive compliance or operational work.

Non-financial benefits include clearer ownership, fewer follow-up emails, consistent reviewer routing, retained comments, more reliable current versions, improved auditability, better workload reporting, and a more predictable experience for owners and reviewers.

Readers should replace the document volume, current handling time, exception rate, maintenance requirement, labour rate, software cost, and implementation cost with their own measurements.

Adding AI to the Automation

AI is an optional enhancement added only after intake, version control, routing, approvals, publication, and failure recovery work reliably.

Useful AI applications include drafting a document summary, identifying potentially missing standard sections, extracting named responsibilities, comparing two versions, and suggesting a category for an unstructured submission.

AI is not needed to assign the permanent ID, increment versions, validate required form fields, compare exact reviewer email addresses, calculate due dates, apply routing tables, count approvals, or decide whether all required reviewers approved. Those tasks are more reliable as deterministic rules.

The core automation creates the folders, records, reminders, approval trail, publication control, and reporting. AI contributes only to interpreting document content.

The Recommended AI Enhancement

The recommended enhancement checks whether a standard document appears to contain required sections and drafts a short summary for the human reviewer. It does not determine legal sufficiency, safety, policy compliance, publication eligibility, or final approval.

• Trigger: An administrator manually runs the AI review for the current version after successful intake.
• AI input: Text from one or more eligible Google Docs in the controlled version folder, plus document type and required section names.
• System instruction: Inspect structure conservatively, cite short evidence, and never approve or reject.
• Expected output: Strict JSON containing section status, missing sections, summary, potential issues, confidence, and a human-review flag.
• Validation: Apps Script validates required properties, allowed status values, arrays, and confidence range.
• Record update: The output is written to AI Reviews and summarized in the Documents row.
• Human review: A reviewer confirms or corrects every AI observation.
• Low confidence: Confidence below 0.75 is flagged without changing workflow status.
• Prohibited data: Restricted, privileged, personal, export-controlled, or contractually prohibited content is excluded.
• Failure behavior: The workflow continues without AI and the human uses the normal checklist.

The reusable system instruction is:

You assist a human document reviewer. Examine only the supplied document text and required-section list. Identify whether each required section is present, partial, missing, or not applicable. Provide brief evidence from the text and a concise neutral summary. Do not approve, reject, publish, make legal conclusions, determine safety, or infer facts not supported by the document. Set requires_human_review to true in every response. When uncertain, lower confidence and explain the uncertainty.

The reusable user prompt is:

Document ID: {{RECORD_ID}}
Version: {{VERSION}}
Document type: {{DOCUMENT_TYPE}}
Required sections: {{REQUIRED_SECTIONS}}

Review the document text below. Return only JSON matching the supplied schema.

DOCUMENT TEXT
{{DOCUMENT_TEXT}}

The expected structured output is:

{
  "document_type": "Procedure",
  "sections": [
    {
      "name": "Purpose",
      "status": "present",
      "evidence": "The opening paragraph states the procedure objective.",
      "comment": "No structural issue identified."
    },
    {
      "name": "Records",
      "status": "missing",
      "evidence": "",
      "comment": "No records-retention or completed-form section was located."
    }
  ],
  "missing_required_sections": [
    "Records"
  ],
  "summary": "The procedure describes lockout preparation, isolation, verification, and restart responsibilities.",
  "potential_issues": [
    "A records section was not located."
  ],
  "confidence": 0.82,
  "requires_human_review": true
}

The optional code below is added after the core script in the same Apps Script project. It calls the OpenAI Responses API using a bearer token stored in Script Properties. Replace YOUR_MODEL_NAME with a model approved by the organization.

const AI_SETTINGS = Object.freeze({
  ENDPOINT: 'https://api.openai.com/v1/responses',
  MODEL: 'YOUR_MODEL_NAME',
  MAX_DOCUMENT_CHARACTERS: 60000,
  MIN_CONFIDENCE: 0.75
});

const AI_SYSTEM_INSTRUCTION =
  'You assist a human document reviewer. Examine only the supplied ' +
  'document text and required-section list. Identify whether each required ' +
  'section is present, partial, missing, or not applicable. Provide brief ' +
  'evidence from the text and a concise neutral summary. Do not approve, ' +
  'reject, publish, make legal conclusions, determine safety, or infer facts ' +
  'not supported by the document. Set requires_human_review to true in every ' +
  'response. When uncertain, lower confidence and explain the uncertainty.';

function setOpenAiApiKey() {
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt(
    'Store OpenAI API key',
    'Enter the approved API key. It will be stored in Script Properties.',
    ui.ButtonSet.OK_CANCEL
  );

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

  const key = response.getResponseText().trim();
  if (!key) {
    throw new Error('An API key is required.');
  }

  PropertiesService.getScriptProperties()
    .setProperty('OPENAI_API_KEY', key);

  ui.alert('The API key was stored in Script Properties.');
}

function runAiReviewPrompt() {
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt(
    'Run AI document check',
    'Enter the document Record ID.',
    ui.ButtonSet.OK_CANCEL
  );

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

  const result = runAiReviewForRecord(
    response.getResponseText().trim().toUpperCase()
  );

  ui.alert(
    'AI output saved for human review. Confidence: ' +
    result.confidence
  );
}

function runAiReviewForRecord(recordId) {
  if (AI_SETTINGS.MODEL.indexOf('YOUR_') !== -1) {
    throw new Error('Replace YOUR_MODEL_NAME before using AI.');
  }

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

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

  const documentRecord = findObject(
    SHEETS.DOCUMENTS,
    'Record_ID',
    recordId
  );
  if (!documentRecord) {
    throw new Error('Document was not found: ' + recordId);
  }

  if (String(documentRecord.values.Confidentiality) === 'Restricted') {
    throw new Error('Restricted documents are prohibited from AI processing.');
  }

  const versionNumber = Number(documentRecord.values.Current_Version);
  const versionRecord = findVersion(recordId, versionNumber);
  if (!versionRecord) {
    throw new Error('Current version record was not found.');
  }

  let fileIds;
  try {
    fileIds = JSON.parse(String(versionRecord.values.File_IDs || '[]'));
  } catch (error) {
    throw new Error('Version File_IDs does not contain valid JSON.');
  }

  const textParts = [];

  fileIds.forEach(function(fileId) {
    const file = DriveApp.getFileById(fileId);
    if (file.getMimeType() === MimeType.GOOGLE_DOCS) {
      const text = DocumentApp.openById(fileId).getBody().getText();
      textParts.push(
        'FILE: ' + file.getName() + '\n' + text
      );
    }
  });

  if (textParts.length === 0) {
    throw new Error(
      'No eligible Google Docs file was found. Convert an approved working copy or use the normal human checklist.'
    );
  }

  const documentText = textParts.join('\n\n');
  if (documentText.length > AI_SETTINGS.MAX_DOCUMENT_CHARACTERS) {
    throw new Error(
      'Document exceeds the configured AI character limit. Human review is required.'
    );
  }

  const requiredSections = getRequiredSections(
    documentRecord.values.Document_Type
  );

  const userPrompt =
    'Document ID: ' + recordId + '\n' +
    'Version: ' + formatVersion(versionNumber) + '\n' +
    'Document type: ' + documentRecord.values.Document_Type + '\n' +
    'Required sections: ' + requiredSections.join(', ') + '\n\n' +
    'Review the document text below. Return only JSON matching the supplied schema.\n\n' +
    'DOCUMENT TEXT\n' +
    documentText;

  const schema = {
    type: 'object',
    additionalProperties: false,
    required: [
      'document_type',
      'sections',
      'missing_required_sections',
      'summary',
      'potential_issues',
      'confidence',
      'requires_human_review'
    ],
    properties: {
      document_type: { type: 'string' },
      sections: {
        type: 'array',
        items: {
          type: 'object',
          additionalProperties: false,
          required: ['name', 'status', 'evidence', 'comment'],
          properties: {
            name: { type: 'string' },
            status: {
              type: 'string',
              enum: ['present', 'partial', 'missing', 'not_applicable']
            },
            evidence: { type: 'string' },
            comment: { type: 'string' }
          }
        }
      },
      missing_required_sections: {
        type: 'array',
        items: { type: 'string' }
      },
      summary: { type: 'string' },
      potential_issues: {
        type: 'array',
        items: { type: 'string' }
      },
      confidence: {
        type: 'number',
        minimum: 0,
        maximum: 1
      },
      requires_human_review: { type: 'boolean' }
    }
  };

  const payload = {
    model: AI_SETTINGS.MODEL,
    instructions: AI_SYSTEM_INSTRUCTION,
    input: userPrompt,
    text: {
      format: {
        type: 'json_schema',
        name: 'document_review_assist',
        strict: true,
        schema: schema
      }
    }
  };

  const response = fetchAiResponse(apiKey, payload);
  const outputText = extractAiOutputText(response);

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

  validateAiResult(result);

  const usage = response.usage || {};
  const reviewStatus =
    result.confidence < AI_SETTINGS.MIN_CONFIDENCE
      ? 'Low Confidence Human Review'
      : 'Human Review Required';

  appendObject(SHEETS.AI_REVIEWS, AI_REVIEW_HEADERS, {
    AI_Review_ID: Utilities.getUuid(),
    Timestamp: new Date(),
    Record_ID: recordId,
    Version_Number: versionNumber,
    Model: AI_SETTINGS.MODEL,
    Summary: result.summary,
    Missing_Sections: result.missing_required_sections.join(','),
    Potential_Issues: result.potential_issues.join(' | '),
    Confidence: result.confidence,
    Requires_Human_Review: result.requires_human_review,
    Raw_JSON: JSON.stringify(result),
    Input_Tokens: usage.input_tokens || '',
    Output_Tokens: usage.output_tokens || '',
    Status: reviewStatus
  });

  updateObject(SHEETS.DOCUMENTS, documentRecord.rowNumber, {
    AI_Review_Status: reviewStatus,
    AI_Summary: result.summary,
    AI_Missing_Sections: result.missing_required_sections.join(','),
    AI_Confidence: result.confidence,
    AI_Last_Run: new Date()
  });

  appendEvent(
    recordId,
    versionNumber,
    'AI_REVIEW_CREATED',
    'Approved AI service',
    JSON.stringify({
      confidence: result.confidence,
      missingSections: result.missing_required_sections
    }),
    ''
  );

  return result;
}

function fetchAiResponse(apiKey, payload) {
  let lastError;

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

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

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

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

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

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

  throw lastError;
}

function extractAiOutputText(response) {
  if (response.output_text) {
    return response.output_text;
  }

  const textParts = [];

  (response.output || []).forEach(function(item) {
    (item.content || []).forEach(function(content) {
      if (
        content.type === 'output_text' &&
        typeof content.text === 'string'
      ) {
        textParts.push(content.text);
      }
    });
  });

  if (!textParts.length) {
    throw new Error('The AI response contained no output text.');
  }

  return textParts.join('');
}

function validateAiResult(result) {
  const allowedStatuses = [
    'present',
    'partial',
    'missing',
    'not_applicable'
  ];

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

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

  result.sections.forEach(function(section) {
    if (
      !section.name ||
      allowedStatuses.indexOf(section.status) === -1
    ) {
      throw new Error('AI section output contains an invalid value.');
    }
  });

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

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

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

  if (result.requires_human_review !== true) {
    throw new Error('AI output must require human review.');
  }
}

function getRequiredSections(documentType) {
  const sectionMap = {
    'Policy': [
      'Purpose',
      'Scope',
      'Responsibilities',
      'Policy',
      'Exceptions',
      'Review Cycle'
    ],
    'Procedure': [
      'Purpose',
      'Scope',
      'Prerequisites',
      'Steps',
      'Responsibilities',
      'Records'
    ],
    'Customer Document': [
      'Audience',
      'Scope',
      'Commitments',
      'Responsibilities',
      'Effective Date'
    ],
    'Marketing Material': [
      'Audience',
      'Product or Service Description',
      'Claims Support',
      'Call to Action',
      'Disclosures'
    ],
    'Other': [
      'Objective',
      'Audience',
      'Main Content',
      'Owner',
      'Effective Date'
    ]
  };

  return sectionMap[documentType] || sectionMap.Other;
}

The AI code requires the external-request permission in addition to the core permissions. The API request uses bearer-token authentication, a POST request, JSON content, and a strict JSON schema. The response is one result, so pagination does not apply.

HTTP 429 and server errors are retried with exponential delay. Other client errors are not retried because they usually indicate invalid credentials, model access, request structure, or policy configuration. The organization should monitor provider rate limits and token usage rather than assuming fixed limits.

Test with non-sensitive sample Google Docs. Inspect the AI Reviews row, Documents summary fields, Apps Script execution log, and provider usage record. A malformed output, unavailable service, oversized document, unsupported file type, or prohibited confidentiality level must fall back to the standard human checklist.

Benefits of the AI Enhancement

The AI enhancement can reduce the time reviewers spend locating standard headings, drafting a first-pass summary, and identifying obvious structural omissions in long documents.

Its specific benefits are:

• Faster first-pass review of unstructured document text.
• More consistent section-level prompts for human reviewers.
• Short summaries that help reviewers understand document purpose before detailed reading.
• Structured missing-section data that can support reporting by document type.
• Earlier identification of documents that need clarification before formal approval.

These benefits are separate from version control, reminders, routing, publication, and audit evidence. Those benefits exist without AI.

What Remains Rule-Based or Human-Controlled

• Required fields: Forms and Apps Script enforce them because exact validation is deterministic.
• Version numbering: Apps Script increments versions under a lock.
• Reviewer assignment: Config routing and approved additional reviewers determine assignments.
• Approval counting: Publication requires exact Approved statuses for every current review row.
• Legal conclusions: Legal and Compliance personnel decide whether terms, claims, and disclosures are acceptable.
• Safety instructions: Qualified human reviewers determine technical and operational safety.
• Customer commitments: Authorized employees approve obligations and external representations.
• Marketing claims: Humans verify substantiation and contextual accuracy.
• Policy exceptions: Authorized managers approve exceptions and document their rationale.
• Final publication: It occurs only after recorded human approvals.

AI output is advisory because section detection can be wrong, context can be misunderstood, and a document can contain all expected headings while still being inaccurate or unacceptable.

Estimating the Additional Value of AI

Gross additional time recovered: 30 × 5 ÷ 60 = 2.50 hours per month

Correction time: 30 × 15% × 3 ÷ 60 = 0.225 hours per month

Failure fallback time: 30 × 2% × 8 ÷ 60 = 0.08 hours per month

Net additional capacity: 2.50 − 0.225 − 0.08 = 2.195 hours per month

Additional labour value: 2.195 × $48 = $105.36 per month

Net after representative AI usage: $105.36 − $8 = $97.36 per month

The comparison is therefore approximately 55 administrative minutes in the original process, 18 minutes with core automation, and an effective average reduction of about 2.9 more minutes across all 45 monthly records when only 30 are AI-eligible.

This estimate does not assume that AI eliminates review, corrections, or service failures. The main value is modest additional capacity and earlier structural feedback.

Testing Checklist

Use fictional or non-sensitive sample data before processing real documents.

Ongoing Maintenance

The primary owner should understand the business process and basic Apps Script operations. A backup owner needs enough access and documentation to restore triggers, update configuration, and recover failed records.

Credential rotation applies to optional API keys and any future service credentials. Google authorization should also be reviewed when scopes, trigger owners, or organizational security policies change.

Archive old response data only after confirming that document, version, review, event, and evidence retention requirements are met. Test backups by restoring a sample copy rather than relying only on the presence of a backup job.

When to Move to Dedicated Software

This implementation can remain appropriate while document volume, workflow complexity, and compliance obligations are moderate. It should not be replaced solely because the organization has grown.

Evaluate dedicated document management, quality management, contract lifecycle management, marketing approval, or records-management software when several of these conditions appear:

• Transaction volume causes Apps Script quotas, long execution times, or slow spreadsheet operations.
• Multiple sites require distinct permission models, local administrators, or offline access.
• Formal electronic signatures or validated approval controls are required.
• Regulations require tamper-evident records, formal system validation, or certified retention controls.
• Documents require complex effective dates, training acknowledgements, controlled distribution, or periodic recertification.
• Exception rates and manual recovery consume significant administrative time.
• Customer, supplier, or regulator portals are required.
• Advanced mobile workflows, offline review, or large-file annotation are needed.
• Integrations expand to enterprise resource planning, product lifecycle management, customer relationship management, or identity governance platforms.
• Spreadsheet performance, concurrent editing, or reporting limitations interfere with operations.
• The organization requires contractual vendor support and defined service commitments.
• Security risk exceeds what can be controlled through the current Workspace and script governance model.

A migration can retain the existing IDs, version history, review evidence, and folder links as source data. Upgrade planning should begin before quotas or controls become operational blockers.

Implementation Checklist

• Confirm document types, volumes, owners, reviewers, due dates, and publication rules.
• Select the Google Workspace accounts that will own Forms, Sheets, Drive files, and triggers.
• Create a test environment and production root folder.
• Configure role-based permissions and shared-link restrictions.
• Create the intake and review decision Forms.
• Link both Forms to the control spreadsheet and use the expected tab names.
• Create the Documents, Versions, Reviews, Events, Errors, Config, and AI Reviews structures.
• Define the permanent ID and version-number conventions.
• Configure reviewer routes for every document type.
• Map every Form field to the destination sheet and Drive action.
• Replace script placeholders and validate prefilled review-form parameters.
• Install the form-submission and daily reminder triggers.
• Configure approval, change-request, rejection, reminder, and escalation rules.
• Create the version, publication, archive, markup, and evidence folder structure.
• Build operational views for pending, overdue, rejected, published, and failed records.
• Protect control sheets, configuration, credentials, sensitive fields, and restricted documents.
• Test normal submissions, invalid data, duplicates, unavailable reviewers, failed files, failed notifications, and retries.
• Verify publication evidence and audit events for each test path.
• Complete user acceptance testing and a phased production rollout.
• Document representative software, labour, testing, training, and maintenance costs.
• Replace time-saving assumptions with measured handling times and exception rates.
• Enable AI only after the rule-based workflow is stable and approved.
• Define prohibited AI data, human-review requirements, output sampling, and cost monitoring.
• Assign primary and backup maintenance owners.
• Document the volume, security, compliance, integration, and support conditions that would justify dedicated software.