Building a Data Quality Issue Register with Google Workspace

The Business Situation

This case study uses a representative 65-person specialty food distributor operating three warehouses. Its four-person reporting team maintains inventory, order fulfilment, procurement, and executive performance reports. A three-person IT and data team supports source-system extracts, spreadsheet models, and scheduled reporting pipelines.

The reporting team identifies approximately 45 data-quality issues each month. These range from incorrect product classifications and duplicated shipment records to missing delivery dates and inconsistent warehouse identifiers. Some errors affect one report, while others originate in shared source data and appear across multiple reports.

Reporting analysts currently use Gmail, shared Google Sheets, and an informal chat channel to describe problems. IT personnel investigate the source, but the affected field, workaround, root cause, corrective action, and validation evidence are not consistently recorded.

As a result, the same issue may be investigated more than once. Analysts cannot reliably distinguish a new problem from the recurrence of an earlier defect. Executive leadership receives corrected reports, but the organization lacks a consistent view of recurring causes, overdue issues, and preventive work.

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 proposed system is a controlled data-quality issue register. It does not replace the company’s reporting tools or source systems. It creates a structured operational process around identifying, assigning, investigating, resolving, and validating reporting defects.

The Existing Process

The original process proceeds chronologically as follows:

1. An analyst notices an unexpected value while refreshing or reviewing a report.
2. The analyst emails an IT contact or adds a note to a general reporting spreadsheet.
3. IT asks for the report name, source, affected row, and an example.
4. The analyst supplies additional details in one or more email replies.
5. An IT team member investigates and communicates a workaround.
6. The source query, transformation, or reference data is corrected.
7. The analyst reruns the report and confirms that the immediate example is fixed.
8. The email thread is archived, but the root cause and preventive action are not transferred into a searchable register.

The largest problem is not the absence of technical fixes. The team is already capable of correcting most defects. The problem is weak operational memory. The organization cannot consistently answer which field failed, where the problem originated, who owned it, how often it recurred, what corrected it, and whether the correction was independently validated.

What the New System Needed to Do

The team defined requirements before selecting the implementation tools.

The system also needed to fit the company’s existing Google Workspace environment. At 45 new issues per month, a custom database application was not justified. The team still needed stronger controls than a basic shared spreadsheet could provide on its own.

Implementation Approaches Considered

Improving the existing spreadsheet

The company could have added dropdowns and manually maintained owner and status columns. This would improve consistency, but analysts would still need to create records, generate identifiers, send notifications, and maintain history manually. It would not adequately address repeat processing or missed follow-ups.

Google Workspace automation

This approach retained familiar tools while adding structured intake, deterministic assignment, status controls, automated email, evidence folders, and event logging. The current transaction volume was comfortably small enough for a spreadsheet-based register, subject to the organization’s applicable Apps Script and Gmail quotas.

No-code database and automation platform

A no-code database would provide stronger record interfaces and linked tables. It remained a credible alternative, especially if external collaborators required access. The company did not select it because the core users already worked in Google Workspace and the additional subscription and vendor administration were not necessary for the initial scope.

Dedicated data-quality software

A dedicated platform becomes more appropriate when the organization requires automated data profiling, lineage, schema monitoring, formal control certification, and monitoring across many pipelines. The present requirement was an issue-management process rather than an enterprise data observability program.

Custom application

A custom application offered no immediate operational advantage sufficient to offset its hosting, security, deployment, and maintenance requirements. It remained an upgrade path if volume, permissions, or workflow complexity increased substantially.

The Selected Solution

The selected implementation connected Google Forms, Google Sheets, Google Apps Script, Gmail, and Google Drive. Google Sheets remained the system of record and reporting source. Apps Script provided the automation layer.

The existing reporting applications and source systems were retained. The implementation did not attempt to modify warehouse, order, procurement, or accounting data automatically. It created a controlled process for managing issues affecting those data sources.

Manual ticket numbering, email assignment, reminder tracking, folder creation, and basic history logging were removed. Human owners still determined root cause and resolution. An independent analyst or data lead still validated the result. AI was not permitted to merge records, close issues, approve a fix, or decide that a report was safe to publish.

System Architecture and Data Flow

1. Submission: An analyst submits the Google Form. Google Forms writes the response to its linked response sheet. If required values are absent, the form blocks submission before the automation runs.
2. Trigger: An installable spreadsheet form-submit trigger passes the response row to Apps Script. The script creates a source event key from the response sheet ID and row number.
3. Idempotency check: Apps Script searches the register for the source event key. If the event has already been processed, it returns the existing ticket instead of creating another one.
4. Validation: The script verifies required values, allowed severity and frequency values, email syntax, and evidence-link format.
5. Ticket creation: Under a script lock, Apps Script generates a sequential identifier, calculates a fingerprint, checks for a recent similar ticket, selects an owner and validator, calculates a due date, and writes the register row.
6. Recurrence handling: A matching fingerprint does not suppress the submission. The new occurrence is retained and linked to the earlier issue for analyst review.
7. Document storage: Apps Script creates an evidence folder beneath a restricted Google Drive root and writes the folder URL to the register.
8. Notification: Gmail sends the identifier, severity, owner, due date, description, and folder link to the owner and reporter.
9. Investigation: Authorized staff update the status, root cause, workaround, resolution, and preventive action in the register.
10. Validation gate: When the owner moves the ticket to Pending Validation, Apps Script checks that required resolution fields exist and emails the designated validator.
11. Resolution: The validator records evidence and selects Passed or Failed. A pass sets the ticket to Resolved. A failure returns it to Investigating.
12. Monitoring: A daily time-based trigger sends due-date reminders and overdue escalations. Operational views read directly from the register.
13. Failure path: Processing errors are written to an Error Log. Partially completed records are marked Action Required, retain retry counts, and can be recovered using administrative retry functions.

• Intake: Google Forms and its linked response sheet
• System of record: Google Sheets Issue Register
• Automation layer: Google Apps Script installable triggers
• Document storage: Restricted Google Drive evidence folders
• Notifications: Gmail sent by the automation account
• Reporting: Google Sheets filters, formulas, pivots, and charts
• AI layer: Optional similarity suggestions, disabled during the initial rollout

Data Structure

The workbook contains six functional sheets:

• Form Responses 1: Immutable intake responses written by Google Forms.
• Issue Register: Current state of every issue.
• Assignments: Source-system and data-domain routing rules.
• History: Append-only business event history.
• Error Log: Automation failures and recovery status.
• Config: Folder, email, threshold, and sheet configuration.

Each issue has one current Issue Register row and many History rows. History records use Issue ID as the logical foreign key. Errors can reference both an Issue ID and a Source Event Key. Assignment rules are evaluated at creation time and are not retroactively applied to existing records.

Workflow Statuses and Ownership

A failed validation sends the issue back to Investigating. A recurrence of a previously resolved issue creates a new ticket and links it to the earlier record. This preserves occurrence volume instead of hiding recurring failures inside an old ticket.

Step-by-Step Implementation

Step 1: Prepare the Accounts and Permissions

1. Create or identify a Google Workspace automation account. The installable triggers execute as the account that creates them, and Gmail messages are sent by that account.
2. Create a Google Group or controlled distribution address for the reporting lead. Use an address such as YOUR_EMAIL_ADDRESS in configuration until the real internal address is approved.
3. Create a shared Google Drive folder named Data Quality Evidence. Restrict it to reporting, IT, and approved leadership users. Do not enable public link access.
4. Create a Google Form and link it to a new Google Sheets workbook.
5. Grant the automation account editor access to the workbook and evidence root folder.
6. Grant reporters permission to submit the form, but not necessarily to edit the register.
7. Grant owners and validators edit access only where required. Protect system-controlled columns such as Issue ID, Source Event Key, Fingerprint, Automation Status, and Retry Count.
8. Create at least four test identities or groups: reporter, data owner, validator, and reporting lead.
9. Use a separate test workbook and test evidence folder until user acceptance testing is complete.

The implementation requires Google Workspace features that permit Forms, Sheets, Drive, Apps Script, installable triggers, and Gmail sending. Exact account entitlements and quotas vary. Verify the organization’s current subscription, Apps Script execution quotas, and Gmail recipient limits before deployment.

The script requests access to the spreadsheet, Drive folder operations, Gmail sending, and trigger management. The automation account should not be a personal employee account that may be removed without a transition plan.

Step 2: Build the Intake

Create a Google Form named Report a Data Quality Issue. Interface labels may vary as Google Workspace changes, but the required question titles should match the following table because the script maps responses by heading.

If Google Forms email collection is enabled, the response sheet may include an Email Address column. The supplied script accepts either Reporter Email or Email Address. Using the explicit Reporter Email question provides more predictable field naming.

Configure the confirmation message to state that the issue has been received and that the ticket identifier will arrive by email. Do not promise that submission means the reported error has been confirmed.

Where organizational settings permit, restrict responses to authenticated employees. Do not use the one-response-per-user option because an analyst must be able to report multiple issues. Incomplete submissions remain in the form and do not trigger the workflow.

Technical duplicate events are prevented with the Source Event Key. Similar business issues are retained as separate occurrences and linked for review because recurrence is itself useful operational information.

Step 3: Create the System of Record

Open the linked response workbook. The Apps Script setup function creates the Issue Register, Assignments, History, Error Log, and Config sheets with exact headers and validation rules.

Use the following naming conventions:

• Workbook: Data Quality Issue Register - Production
• Test workbook: Data Quality Issue Register - Test
• Issue IDs: DQI-YYYY-00000
• Evidence folders: DQI-YYYY-00000 - Report Name
• Source event keys: responseSheetId:rowNumber
• History event IDs: generated UUID values

The ID counter is stored in Apps Script Properties and protected by a script lock. Before returning a new identifier, the script also checks the register to protect against property loss or manual resets.

Populate the Assignments sheet with specific rules first and wildcard fallbacks last.

Replace all example-invalid addresses before testing. The script scores an exact source-system match above a domain-only match and uses the configured default if no active rule matches.

Severity determines the initial due date:

• Critical: one calendar day
• High: three calendar days
• Medium: seven calendar days
• Low: fourteen calendar days

Authorized owners may override a due date where operational calendars require it. The reason should be entered in Notes, and the History sheet records the due-date edit.

Step 4: Connect the Tools

The form response tab is treated as immutable source evidence. The script does not write the Issue ID back into that tab because adding operational columns to a live linked response destination can complicate form maintenance. Instead, the Source Event Key creates a permanent link from the register to the exact source row, and the reporter receives the Issue ID by email.

Step 5: Build the Core Automation

Automation 1: Create the issue

• Trigger: A new row is added by Google Forms.
• Conditions: The response sheet name matches configuration, required values exist, severity and frequency are valid, and the source event has not been processed.
• Actions: Lock processing, generate the ID, calculate the fingerprint, identify a related issue, assign owner and validator, calculate due date, and append the register row.
• Fields updated: All intake, assignment, workflow, source, and automation fields.
• Notification: Owner and reporter receive the ticket details.
• Exception: The Error Log receives failures. Partially created tickets are marked Action Required.

Automation 2: Control status and validation

• Trigger: An authorized user edits a monitored Issue Register cell.
• Conditions: The new value is allowed, the status transition is valid, and required fields exist.
• Actions: Update Last Updated, write History, calculate a revised due date when severity changes, and enforce the validation gate.
• Fields updated: Status, Validation Status, Resolution Date, Due Date, Last Updated, and automation-error fields where needed.
• Notification: The owner, reporter, or validator receives the relevant change.
• Exception: Invalid status changes are reverted and recorded.

Automation 3: Recover incomplete processing

• Trigger: An administrator manually runs a retry function after reviewing the Error Log or Action Required view.
• Conditions: The source event or issue has not exceeded the configured retry count.
• Actions: Reprocess the source row, create a missing folder, resend only pending notifications where known, and update retry fields.
• Fields updated: Evidence Folder URL, Automation Status, Last Automation Run, Retry Count, Error Message, and Pending Notification Recipients.
• Notification: Recovery messages are sent when previously missing information becomes available.
• Exception: Records exceeding the retry limit stay in the manual-review queue.

Idempotency protects against duplicate trigger delivery. A business recurrence is different from a duplicate event. It becomes a separate ticket, preserving the number and timing of occurrences.

Step 6: Add Approvals, Reminders, and Escalations

The approval mechanism is a resolution-validation gate rather than a financial approval.

1. The owner investigates the issue and records Root Cause, Resolution, and Preventive Action.
2. The owner changes Status to Pending Validation.
3. Apps Script checks the three required resolution fields. If any are absent, the status change is reverted.
4. The script sets Validation Status to Pending and emails the designated validator.
5. The validator reproduces the relevant report or control using suitable test data.
6. The validator enters Validation Evidence and selects Passed or Failed.
7. A pass changes the ticket to Resolved and records Resolution Date.
8. A failure changes the ticket to Investigating and notifies the owner.
9. The reporting lead may move a Resolved ticket to Closed after administrative review.

The validator should be different from the owner for Critical and High issues where staffing permits. If the normal validator is unavailable, the reporting lead changes Validator Email before validation and records the delegation in Notes.

The daily trigger compares active-ticket due dates with the current date:

• One day before the due date, the owner receives a reminder.
• On and after the due date, the owner receives no more than one reminder per day.
• After the configured overdue threshold, the reporting lead receives the first escalation.
• Last Reminder Sent and Escalated At prevent duplicate same-day reminders and repeated first escalations.

Rejected tickets require a reason in Notes. Reassignment changes Owner Email, logs the old and new owners, and notifies the new owner.

Step 7: Add Documents and File Management

Create one Drive root for test and another for production. The automation creates a child folder using the Issue ID and a sanitized report name.

• Root: Data Quality Evidence
• Year: The base implementation keeps all folders beneath the root. A year level can be added later if volume warrants it.
• Issue folder: DQI-2026-00041 – Warehouse Fill Rate
• File naming: DQI-2026-00041_before.png, DQI-2026-00041_validation.xlsx, or DQI-2026-00041_query.sql

Permissions are inherited from the root folder. Users should upload evidence into the generated folder rather than creating public links. The submitted evidence URL remains in the register as a reference, but it does not replace controlled storage.

Google Drive provides file version history for supported file types. When replacing a file, users should either upload a new version or retain a date in the file name. The register should not store raw confidential data when a row identifier, redacted screenshot, or controlled query result is sufficient.

If folder creation fails, the ticket is still created. Evidence Folder URL remains blank, Automation Status becomes Action Required, and an administrator can run the operational retry. File-size and storage limits depend on the organization’s current Google Workspace configuration and must be monitored separately.

Step 8: Add Reporting and Operational Views

Create filter views or separate dashboard tabs for the following queues:

• New and Needs Review
• Assigned to the current owner
• Due within three days
• Overdue
• Missing owner or validator
• Pending Validation
• Failed Validation
• Workaround Active
• Related or recurring issues
• Rejected issues
• Resolved in the last 30 days
• Automation Status equals Action Required
• AI Review Status equals Review Required

A status summary can be created with this Google Sheets formula, based on the supplied column order:

=QUERY('Issue Register'!A:AP,"select U, count(A) where A is not null group by U label count(A) 'Issues'",1)

An overdue count can use:

=COUNTIFS('Issue Register'!V:V,"<"&TODAY(),'Issue Register'!U:U,"<>Resolved",'Issue Register'!U:U,"<>Closed",'Issue Register'!U:U,"<>Rejected")

Useful calculated metrics include issue age, time to triage, time to resolution, recurrence count by source and field, percentage requiring a workaround, and open issues by owner. Dashboard refresh is immediate because the views read from the register. The reporting lead owns definitions, while the automation owner maintains formulas and references.

Alert thresholds should begin conservatively. For example, leadership review may be appropriate when any Critical issue is overdue, an owner has more than ten active issues, or more than five records have Automation Status set to Action Required. These are representative thresholds and should be replaced with operationally appropriate values.

Step 9: Add Security and Governance Controls

• Use least-privilege workbook and Drive permissions.
• Protect identifier, fingerprint, source-event, automation, and retry columns from routine editing.
• Limit Config and Assignments edits to the reporting lead and automation administrators.
• Restrict the History and Error Log sheets from deletion or bulk editing.
• Do not store passwords, API keys, customer records, payment information, or sensitive personnel data in issue descriptions.
• Store configuration values in the Config sheet only when they are not secrets. Future AI API credentials should use Apps Script Properties or an approved secret-management service.
• Disable public Drive links and review inherited folder access.
• Remove former employees from the form, workbook, Drive root, groups, and trigger ownership arrangements.
• Retain records according to the organization’s reporting, legal, privacy, and security requirements.
• Export or back up the register and configuration on a scheduled basis appropriate to business risk.
• Review Apps Script executions and Gmail failures.
• Do not send prohibited data to an AI service. Keep final issue classification, resolution, and closure human-controlled.

Installable triggers execute with the trigger owner’s permissions. Changing workbook ownership does not automatically transfer trigger ownership. A documented handover procedure is therefore required.

Step 10: Deploy and Test

1. Build the form, workbook, configuration, and evidence root in a test environment.
2. Paste the complete script into the Apps Script project bound to the test workbook.
3. Run setupSystem and inspect the created sheets.
4. Replace placeholder configuration values and populate test assignment rules.
5. Run installAutomationTriggers and authorize the requested services.
6. Submit sample issues covering every severity, domain, and frequency value.
7. Test valid and invalid status changes with reporter, owner, validator, and lead accounts.
8. Disconnect or invalidate one test dependency at a time to verify failure recording and recovery.
9. Run user acceptance testing with two analysts, one data owner, and one validator.
10. Correct configuration and script defects in the test workbook.
11. Copy the approved form, workbook, and script into production. Use a new production evidence folder ID.
12. Install production triggers while signed in as the designated automation account.
13. Start with one reporting domain for a two-week pilot.
14. Review daily failures and weekly user feedback during the pilot.
15. Expand to the remaining domains after the pilot acceptance criteria are met.

The launch communication should explain how to submit an issue, what severity means, who owns triage, how validation works, and where staff can see status. Retain a rollback copy of the prior tracking spreadsheet, but direct all new reports to the form once production is activated.

Code and Configuration

The core implementation requires code because native Forms and Sheets actions do not provide sequential IDs, deterministic idempotency, assignment scoring, controlled status transitions, evidence-folder creation, or custom retry handling.

The following complete Google Apps Script is designed for the spreadsheet linked to the Google Form. Open that spreadsheet, open the Apps Script editor from the spreadsheet’s extension menu, replace the default editor contents, and paste this script into a single Code.gs file.

const SHEET_NAMES = {
  REGISTER: 'Issue Register',
  ASSIGNMENTS: 'Assignments',
  HISTORY: 'History',
  ERRORS: 'Error Log',
  CONFIG: 'Config'
};

const REGISTER_HEADERS = [
  'Issue ID',
  'Submitted At',
  'Created Date',
  'Last Updated',
  'Reporter Email',
  'Report or Dashboard',
  'Source System',
  'Data Domain',
  'Dataset or Table',
  'Affected Field',
  'Example Reference',
  'Submitted Evidence Link',
  'Issue Description',
  'Business Impact',
  'Severity',
  'Frequency',
  'Workaround',
  'Suspected Root Cause',
  'Owner Email',
  'Validator Email',
  'Status',
  'Due Date',
  'Related Issue ID',
  'Root Cause',
  'Resolution',
  'Preventive Action',
  'Validation Status',
  'Validation Evidence',
  'Resolution Date',
  'Evidence Folder URL',
  'Source Event Key',
  'Fingerprint',
  'AI Suggested Related IDs',
  'AI Confidence',
  'AI Review Status',
  'Automation Status',
  'Last Automation Run',
  'Retry Count',
  'Error Message',
  'Pending Notification Recipients',
  'Last Reminder Sent',
  'Escalated At',
  'Notes'
];

const ASSIGNMENT_HEADERS = [
  'Data Domain',
  'Source System',
  'Owner Email',
  'Validator Email',
  'Active'
];

const HISTORY_HEADERS = [
  'Event ID',
  'Issue ID',
  'Event Timestamp',
  'Event Type',
  'Actor Email',
  'From Value',
  'To Value',
  'Details'
];

const ERROR_HEADERS = [
  'Error ID',
  'Occurred At',
  'Handler',
  'Source Event Key',
  'Issue ID',
  'Message',
  'Stack',
  'Retry Count',
  'Resolved At'
];

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

const STATUS_VALUES = [
  'New',
  'Needs Review',
  'Triaged',
  'Investigating',
  'Workaround Active',
  'Pending Validation',
  'Resolved',
  'Closed',
  'Rejected'
];

const SEVERITY_VALUES = ['Critical', 'High', 'Medium', 'Low'];

const FREQUENCY_VALUES = [
  'First observed',
  'Intermittent',
  'Recurring',
  'Unknown'
];

const VALIDATION_VALUES = [
  'Not Started',
  'Pending',
  'Passed',
  'Failed',
  'Recheck Required'
];

const AI_REVIEW_VALUES = [
  'Not Run',
  'Review Required',
  'Confirmed',
  'Dismissed'
];

const TERMINAL_STATUSES = ['Resolved', 'Closed', 'Rejected'];

/**
 * Creates the controlled sheets, headers, configuration rows, and validations.
 * Run once before entering production configuration.
 */
function setupSystem() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  const register = ensureSheet_(
    spreadsheet,
    SHEET_NAMES.REGISTER,
    REGISTER_HEADERS
  );

  ensureSheet_(
    spreadsheet,
    SHEET_NAMES.ASSIGNMENTS,
    ASSIGNMENT_HEADERS
  );

  ensureSheet_(
    spreadsheet,
    SHEET_NAMES.HISTORY,
    HISTORY_HEADERS
  );

  ensureSheet_(
    spreadsheet,
    SHEET_NAMES.ERRORS,
    ERROR_HEADERS
  );

  const configSheet = ensureSheet_(
    spreadsheet,
    SHEET_NAMES.CONFIG,
    CONFIG_HEADERS
  );

  seedConfig_(configSheet);
  applyRegisterValidations_(register);

  spreadsheet.setActiveSheet(register);
  SpreadsheetApp.flush();

  Logger.log(
    'Setup complete. Replace placeholders in Config, populate Assignments, ' +
    'then run installAutomationTriggers.'
  );
}

/**
 * Installs one form-submit trigger, one edit trigger, and one daily trigger.
 * Run this while signed in as the designated automation account.
 */
function installAutomationTriggers() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const managedHandlers = [
    'handleFormSubmit',
    'handleRegisterEdit',
    'sendDailyReminders'
  ];

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

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

  ScriptApp.newTrigger('handleRegisterEdit')
    .forSpreadsheet(spreadsheet)
    .onEdit()
    .create();

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

  Logger.log('Automation triggers installed successfully.');
}

/**
 * Installable spreadsheet form-submit trigger.
 */
function handleFormSubmit(event) {
  let sourceEventKey = '';

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

    sourceEventKey =
      event.range.getSheet().getSheetId() + ':' + event.range.getRow();

    processResponseRow_(
      event.range.getSheet(),
      event.range.getRow()
    );
  } catch (error) {
    logError_(
      'handleFormSubmit',
      sourceEventKey,
      '',
      error
    );
    throw error;
  }
}

/**
 * Converts one response-sheet row into an idempotent issue ticket.
 */
function processResponseRow_(sourceSheet, sourceRow) {
  const config = getConfig_();

  requireConfig_(config, [
    'FORM_RESPONSE_SHEET_NAME',
    'EVIDENCE_ROOT_FOLDER_ID',
    'DEFAULT_OWNER_EMAIL',
    'DEFAULT_VALIDATOR_EMAIL',
    'REPORTING_LEAD_EMAIL'
  ]);

  if (sourceSheet.getName() !== config.FORM_RESPONSE_SHEET_NAME) {
    throw new Error(
      'Unexpected form response sheet: ' + sourceSheet.getName()
    );
  }

  if (sourceRow < 2) {
    throw new Error('The response row must be greater than one.');
  }

  const sourceData = getRowObject_(sourceSheet, sourceRow);
  const sourceEventKey = sourceSheet.getSheetId() + ':' + sourceRow;

  const reporterEmail = firstNonBlank_([
    sourceData['Reporter Email'],
    sourceData['Email Address']
  ]);

  const formData = {
    submittedAt: sourceData.Timestamp || new Date(),
    reporterEmail: cleanText_(reporterEmail),
    reportName: cleanText_(sourceData['Report or Dashboard']),
    sourceSystem: cleanText_(sourceData['Source System']),
    dataDomain: cleanText_(sourceData['Data Domain']),
    dataset: cleanText_(sourceData['Dataset or Table']),
    affectedField: cleanText_(sourceData['Affected Field']),
    exampleReference: cleanText_(sourceData['Example Reference']),
    submittedEvidenceLink: cleanText_(
      sourceData['Submitted Evidence Link']
    ),
    description: cleanText_(sourceData['Issue Description']),
    businessImpact: cleanText_(sourceData['Business Impact']),
    severity: cleanText_(sourceData.Severity),
    frequency: cleanText_(sourceData.Frequency),
    workaround: cleanText_(sourceData.Workaround),
    suspectedRootCause: cleanText_(
      sourceData['Suspected Root Cause']
    )
  };

  validateFormData_(formData);

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const register = spreadsheet.getSheetByName(SHEET_NAMES.REGISTER);
  const lock = LockService.getScriptLock();

  let issueId = '';
  let registerRow = 0;

  lock.waitLock(30000);

  try {
    const existingRow = findRowByExactValue_(
      register,
      'Source Event Key',
      sourceEventKey
    );

    if (existingRow) {
      const existingRecord = getRowObject_(register, existingRow);
      Logger.log(
        'Source event already processed as ' +
        existingRecord['Issue ID']
      );
      return existingRecord['Issue ID'];
    }

    const submittedAt = toDate_(formData.submittedAt) || new Date();
    issueId = generateIssueId_(register, submittedAt);

    const fingerprint = createFingerprint_([
      formData.sourceSystem,
      formData.dataDomain,
      formData.dataset,
      formData.affectedField,
      formData.description
    ]);

    const duplicateWindow = positiveInteger_(
      config.DUPLICATE_WINDOW_DAYS,
      30
    );

    const relatedIssueId = findRelatedIssue_(
      register,
      fingerprint,
      submittedAt,
      duplicateWindow
    );

    const assignment = getAssignment_(
      formData.dataDomain,
      formData.sourceSystem,
      config
    );

    const now = new Date();
    const initialStatus = relatedIssueId ? 'Needs Review' : 'New';

    const record = {
      'Issue ID': issueId,
      'Submitted At': submittedAt,
      'Created Date': now,
      'Last Updated': now,
      'Reporter Email': formData.reporterEmail,
      'Report or Dashboard': formData.reportName,
      'Source System': formData.sourceSystem,
      'Data Domain': formData.dataDomain,
      'Dataset or Table': formData.dataset,
      'Affected Field': formData.affectedField,
      'Example Reference': formData.exampleReference,
      'Submitted Evidence Link': formData.submittedEvidenceLink,
      'Issue Description': formData.description,
      'Business Impact': formData.businessImpact,
      'Severity': formData.severity,
      'Frequency': formData.frequency,
      'Workaround': formData.workaround,
      'Suspected Root Cause': formData.suspectedRootCause,
      'Owner Email': assignment.ownerEmail,
      'Validator Email': assignment.validatorEmail,
      'Status': initialStatus,
      'Due Date': calculateDueDate_(submittedAt, formData.severity),
      'Related Issue ID': relatedIssueId,
      'Root Cause': '',
      'Resolution': '',
      'Preventive Action': '',
      'Validation Status': 'Not Started',
      'Validation Evidence': '',
      'Resolution Date': '',
      'Evidence Folder URL': '',
      'Source Event Key': sourceEventKey,
      'Fingerprint': fingerprint,
      'AI Suggested Related IDs': '',
      'AI Confidence': '',
      'AI Review Status': 'Not Run',
      'Automation Status': 'Processing',
      'Last Automation Run': now,
      'Retry Count': 0,
      'Error Message': '',
      'Pending Notification Recipients': '',
      'Last Reminder Sent': '',
      'Escalated At': '',
      'Notes': relatedIssueId
        ? 'Possible recurrence of ' + relatedIssueId
        : ''
    };

    registerRow = appendObject_(register, record);

    logHistory_(
      issueId,
      'CREATED',
      formData.reporterEmail,
      '',
      initialStatus,
      relatedIssueId
        ? 'Created and linked to possible recurrence ' + relatedIssueId
        : 'Created from Google Forms submission'
    );
  } finally {
    lock.releaseLock();
  }

  completeInitialActions_(registerRow, config);
  return issueId;
}

/**
 * Creates the evidence folder and sends initial notifications.
 * A failure does not remove the ticket.
 */
function completeInitialActions_(registerRow, config) {
  const register = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName(SHEET_NAMES.REGISTER);

  let record = getRowObject_(register, registerRow);
  const errors = [];
  const failedRecipients = [];

  if (!record['Evidence Folder URL']) {
    try {
      const folderUrl = createEvidenceFolder_(record, config);
      setFields_(register, registerRow, {
        'Evidence Folder URL': folderUrl
      });
      record['Evidence Folder URL'] = folderUrl;
    } catch (error) {
      errors.push('folder: ' + error.message);
      logError_(
        'completeInitialActions',
        record['Source Event Key'],
        record['Issue ID'],
        error
      );
    }
  }

  uniqueEmails_([
    record['Owner Email'],
    record['Reporter Email']
  ]).forEach(function(recipient) {
    try {
      sendTicketEmail_(
        recipient,
        record,
        'Data quality issue created'
      );
    } catch (error) {
      failedRecipients.push(recipient);
      errors.push(
        'email ' + recipient + ': ' + error.message
      );
      logError_(
        'completeInitialActions',
        record['Source Event Key'],
        record['Issue ID'],
        error
      );
    }
  });

  setFields_(register, registerRow, {
    'Automation Status':
      errors.length === 0 ? 'Complete' : 'Action Required',
    'Last Automation Run': new Date(),
    'Error Message': errors.join(' | '),
    'Pending Notification Recipients':
      failedRecipients.join(',')
  });
}

/**
 * Installable edit trigger for controlled register changes.
 */
function handleRegisterEdit(event) {
  if (!event || !event.range) {
    throw new Error(
      'This function requires an installable spreadsheet edit event.'
    );
  }

  const range = event.range;
  const sheet = range.getSheet();

  if (
    sheet.getName() !== SHEET_NAMES.REGISTER ||
    range.getRow() < 2
  ) {
    return;
  }

  const issueId = getCellByHeader_(
    sheet,
    range.getRow(),
    'Issue ID'
  );

  try {
    if (
      range.getNumRows() !== 1 ||
      range.getNumColumns() !== 1
    ) {
      throw new Error(
        'Bulk edits are not supported for controlled workflow fields.'
      );
    }

    const headers = getHeaders_(sheet);
    const editedHeader = headers[range.getColumn() - 1];
    const monitoredHeaders = [
      'Owner Email',
      'Validator Email',
      'Status',
      'Due Date',
      'Severity',
      'Root Cause',
      'Resolution',
      'Preventive Action',
      'Validation Status',
      'Validation Evidence',
      'Related Issue ID',
      'Notes'
    ];

    if (monitoredHeaders.indexOf(editedHeader) === -1) {
      return;
    }

    const actor = getActorEmail_(event);
    const oldValue = event.oldValue === undefined
      ? ''
      : event.oldValue;
    const newValue = range.getValue();
    let record = getRowObject_(sheet, range.getRow());

    if (editedHeader === 'Status') {
      handleStatusEdit_(
        sheet,
        range,
        record,
        cleanText_(oldValue),
        cleanText_(newValue),
        actor
      );
    } else if (editedHeader === 'Validation Status') {
      handleValidationEdit_(
        sheet,
        range,
        record,
        cleanText_(oldValue),
        cleanText_(newValue),
        actor
      );
    } else if (editedHeader === 'Owner Email') {
      if (!isValidEmail_(cleanText_(newValue))) {
        revertCell_(
          sheet,
          range,
          oldValue,
          record,
          'Owner Email must be a valid email address.'
        );
        return;
      }

      logHistory_(
        record['Issue ID'],
        'OWNER_CHANGED',
        actor,
        oldValue,
        newValue,
        'Issue ownership changed'
      );

      notifyWithFailureTracking_(
        sheet,
        range.getRow(),
        [cleanText_(newValue)],
        'Data quality issue assigned'
      );
    } else if (editedHeader === 'Validator Email') {
      if (!isValidEmail_(cleanText_(newValue))) {
        revertCell_(
          sheet,
          range,
          oldValue,
          record,
          'Validator Email must be a valid email address.'
        );
        return;
      }

      logHistory_(
        record['Issue ID'],
        'VALIDATOR_CHANGED',
        actor,
        oldValue,
        newValue,
        'Validator changed'
      );
    } else if (editedHeader === 'Severity') {
      if (SEVERITY_VALUES.indexOf(cleanText_(newValue)) === -1) {
        revertCell_(
          sheet,
          range,
          oldValue,
          record,
          'Severity is not an allowed value.'
        );
        return;
      }

      const revisedDueDate = calculateDueDate_(
        new Date(),
        cleanText_(newValue)
      );

      setFields_(sheet, range.getRow(), {
        'Due Date': revisedDueDate
      });

      logHistory_(
        record['Issue ID'],
        'SEVERITY_CHANGED',
        actor,
        oldValue,
        newValue,
        'Due date recalculated from the new severity'
      );
    } else {
      logHistory_(
        record['Issue ID'],
        'FIELD_CHANGED',
        actor,
        oldValue,
        newValue,
        editedHeader + ' changed'
      );
    }

    setFields_(sheet, range.getRow(), {
      'Last Updated': new Date()
    });
  } catch (error) {
    logError_(
      'handleRegisterEdit',
      '',
      issueId,
      error
    );

    if (range.getNumRows() === 1) {
      setFields_(sheet, range.getRow(), {
        'Automation Status': 'Action Required',
        'Last Automation Run': new Date(),
        'Error Message': error.message
      });
    }
  }
}

/**
 * Enforces allowed status transitions and validation prerequisites.
 */
function handleStatusEdit_(
  sheet,
  range,
  record,
  oldStatus,
  newStatus,
  actor
) {
  if (STATUS_VALUES.indexOf(newStatus) === -1) {
    revertCell_(
      sheet,
      range,
      oldStatus,
      record,
      'Status is not an allowed value.'
    );
    return;
  }

  if (!oldStatus) {
    revertCell_(
      sheet,
      range,
      'Needs Review',
      record,
      'Status changes must be made one cell at a time.'
    );
    return;
  }

  if (!isAllowedTransition_(oldStatus, newStatus)) {
    revertCell_(
      sheet,
      range,
      oldStatus,
      record,
      'Transition from ' + oldStatus + ' to ' + newStatus +
      ' is not allowed.'
    );
    return;
  }

  if (newStatus === 'Pending Validation') {
    const missing = requiredResolutionFields_(record);

    if (missing.length > 0) {
      revertCell_(
        sheet,
        range,
        oldStatus,
        record,
        'Complete before validation: ' + missing.join(', ')
      );
      return;
    }

    setFields_(sheet, range.getRow(), {
      'Validation Status': 'Pending'
    });

    notifyWithFailureTracking_(
      sheet,
      range.getRow(),
      [record['Validator Email']],
      'Data quality issue awaiting validation'
    );
  }

  if (
    newStatus === 'Resolved' &&
    record['Validation Status'] !== 'Passed'
  ) {
    revertCell_(
      sheet,
      range,
      oldStatus,
      record,
      'A ticket cannot be resolved until validation has passed.'
    );
    return;
  }

  if (
    newStatus === 'Closed' &&
    (
      oldStatus !== 'Resolved' ||
      record['Validation Status'] !== 'Passed'
    )
  ) {
    revertCell_(
      sheet,
      range,
      oldStatus,
      record,
      'Only a validated Resolved ticket can be closed.'
    );
    return;
  }

  if (newStatus === 'Rejected' && !record.Notes) {
    revertCell_(
      sheet,
      range,
      oldStatus,
      record,
      'Enter a rejection reason in Notes before rejecting.'
    );
    return;
  }

  if (
    newStatus === 'Investigating' &&
    (oldStatus === 'Resolved' || oldStatus === 'Closed')
  ) {
    setFields_(sheet, range.getRow(), {
      'Validation Status': 'Recheck Required',
      'Resolution Date': ''
    });
  }

  logHistory_(
    record['Issue ID'],
    'STATUS_CHANGED',
    actor,
    oldStatus,
    newStatus,
    'Workflow status changed'
  );

  notifyWithFailureTracking_(
    sheet,
    range.getRow(),
    uniqueEmails_([
      record['Owner Email'],
      record['Reporter Email']
    ]),
    'Data quality issue status changed'
  );
}

/**
 * Converts a validator decision into deterministic workflow updates.
 */
function handleValidationEdit_(
  sheet,
  range,
  record,
  oldValue,
  newValue,
  actor
) {
  if (VALIDATION_VALUES.indexOf(newValue) === -1) {
    revertCell_(
      sheet,
      range,
      oldValue,
      record,
      'Validation Status is not an allowed value.'
    );
    return;
  }

  if (newValue === 'Passed') {
    if (record.Status !== 'Pending Validation') {
      revertCell_(
        sheet,
        range,
        oldValue,
        record,
        'Validation can pass only while Status is Pending Validation.'
      );
      return;
    }

    if (!cleanText_(record['Validation Evidence'])) {
      revertCell_(
        sheet,
        range,
        oldValue,
        record,
        'Validation Evidence is required before passing validation.'
      );
      return;
    }

    if (
      actor &&
      record['Validator Email'] &&
      actor.toLowerCase() !==
        cleanText_(record['Validator Email']).toLowerCase()
    ) {
      revertCell_(
        sheet,
        range,
        oldValue,
        record,
        'Only the assigned validator may pass this issue.'
      );
      return;
    }

    setFields_(sheet, range.getRow(), {
      'Status': 'Resolved',
      'Resolution Date': new Date()
    });

    logHistory_(
      record['Issue ID'],
      'VALIDATION_PASSED',
      actor,
      oldValue,
      newValue,
      'Ticket moved to Resolved'
    );

    notifyWithFailureTracking_(
      sheet,
      range.getRow(),
      uniqueEmails_([
        record['Owner Email'],
        record['Reporter Email']
      ]),
      'Data quality issue resolved'
    );
  } else if (newValue === 'Failed') {
    if (record.Status !== 'Pending Validation') {
      revertCell_(
        sheet,
        range,
        oldValue,
        record,
        'Validation can fail only while Status is Pending Validation.'
      );
      return;
    }

    setFields_(sheet, range.getRow(), {
      'Status': 'Investigating',
      'Resolution Date': ''
    });

    logHistory_(
      record['Issue ID'],
      'VALIDATION_FAILED',
      actor,
      oldValue,
      newValue,
      'Ticket returned to Investigating'
    );

    notifyWithFailureTracking_(
      sheet,
      range.getRow(),
      [record['Owner Email']],
      'Data quality issue failed validation'
    );
  } else {
    logHistory_(
      record['Issue ID'],
      'VALIDATION_CHANGED',
      actor,
      oldValue,
      newValue,
      'Validation status changed'
    );
  }
}

/**
 * Sends due-date reminders and the first overdue escalation.
 */
function sendDailyReminders() {
  const config = getConfig_();

  requireConfig_(config, [
    'REPORTING_LEAD_EMAIL'
  ]);

  const register = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName(SHEET_NAMES.REGISTER);

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

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

  const today = startOfDay_(new Date());
  const reminderDays = positiveInteger_(
    config.REMINDER_DAYS_BEFORE_DUE,
    1
  );
  const escalationDays = positiveInteger_(
    config.ESCALATION_DAYS_AFTER_DUE,
    1
  );

  values.forEach(function(rowValues, index) {
    const rowNumber = index + 2;
    const record = objectFromValues_(headers, rowValues);
    const status = cleanText_(record.Status);

    if (
      !record['Issue ID'] ||
      TERMINAL_STATUSES.indexOf(status) !== -1
    ) {
      return;
    }

    const dueDate = toDate_(record['Due Date']);

    if (!dueDate) {
      return;
    }

    const daysUntilDue = Math.floor(
      (
        startOfDay_(dueDate).getTime() -
        today.getTime()
      ) / 86400000
    );

    if (
      daysUntilDue <= reminderDays &&
      dateKey_(record['Last Reminder Sent']) !== dateKey_(today)
    ) {
      try {
        sendReminderEmail_(record, daysUntilDue);
        setFields_(register, rowNumber, {
          'Last Reminder Sent': new Date()
        });

        logHistory_(
          record['Issue ID'],
          'REMINDER_SENT',
          'automation',
          '',
          record['Owner Email'],
          'Due-date reminder sent'
        );
      } catch (error) {
        markOperationalFailure_(
          register,
          rowNumber,
          record,
          error,
          record['Owner Email']
        );
      }
    }

    const overdueDays = Math.max(0, -daysUntilDue);

    if (
      overdueDays >= escalationDays &&
      !record['Escalated At']
    ) {
      try {
        sendEscalationEmail_(
          record,
          config.REPORTING_LEAD_EMAIL,
          overdueDays
        );

        setFields_(register, rowNumber, {
          'Escalated At': new Date()
        });

        logHistory_(
          record['Issue ID'],
          'ESCALATED',
          'automation',
          '',
          config.REPORTING_LEAD_EMAIL,
          overdueDays + ' day(s) overdue'
        );
      } catch (error) {
        markOperationalFailure_(
          register,
          rowNumber,
          record,
          error,
          config.REPORTING_LEAD_EMAIL
        );
      }
    }
  });
}

/**
 * Retries failed source submissions that do not yet have an Issue ID.
 */
function retryFailedSubmissions() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const errorSheet = spreadsheet.getSheetByName(SHEET_NAMES.ERRORS);

  if (errorSheet.getLastRow() < 2) {
    Logger.log('No error records are available.');
    return;
  }

  const config = getConfig_();
  const maxRetries = positiveInteger_(config.MAX_RETRIES, 3);
  const headers = getHeaders_(errorSheet);
  const values = errorSheet.getRange(
    2,
    1,
    errorSheet.getLastRow() - 1,
    headers.length
  ).getValues();

  let processed = 0;

  values.forEach(function(rowValues, index) {
    if (processed >= 20) {
      return;
    }

    const rowNumber = index + 2;
    const errorRecord = objectFromValues_(headers, rowValues);
    const sourceKey = cleanText_(
      errorRecord['Source Event Key']
    );
    const retryCount = Number(errorRecord['Retry Count'] || 0);

    if (
      errorRecord['Resolved At'] ||
      errorRecord['Issue ID'] ||
      !sourceKey ||
      retryCount >= maxRetries
    ) {
      return;
    }

    const parts = sourceKey.split(':');

    if (parts.length !== 2) {
      return;
    }

    const sourceSheetId = Number(parts[0]);
    const sourceRow = Number(parts[1]);
    const sourceSheet = spreadsheet.getSheets().filter(
      function(sheet) {
        return sheet.getSheetId() === sourceSheetId;
      }
    )[0];

    if (!sourceSheet || !sourceRow) {
      errorSheet.getRange(
        rowNumber,
        headers.indexOf('Retry Count') + 1
      ).setValue(retryCount + 1);
      return;
    }

    try {
      const issueId = processResponseRow_(
        sourceSheet,
        sourceRow
      );

      setFields_(errorSheet, rowNumber, {
        'Issue ID': issueId,
        'Retry Count': retryCount + 1,
        'Resolved At': new Date()
      });
    } catch (error) {
      setFields_(errorSheet, rowNumber, {
        'Retry Count': retryCount + 1,
        'Message': error.message,
        'Stack': error.stack || ''
      });
    }

    processed += 1;
  });

  Logger.log('Retried ' + processed + ' failed submission(s).');
}

/**
 * Retries missing folders and pending ticket notifications.
 */
function retryOperationalFailures() {
  const register = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName(SHEET_NAMES.REGISTER);
  const config = getConfig_();
  const maxRetries = positiveInteger_(config.MAX_RETRIES, 3);

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

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

  let processed = 0;

  values.forEach(function(rowValues, index) {
    if (processed >= 20) {
      return;
    }

    const rowNumber = index + 2;
    const record = objectFromValues_(headers, rowValues);
    const retryCount = Number(record['Retry Count'] || 0);

    if (
      record['Automation Status'] !== 'Action Required' &&
      record['Automation Status'] !== 'Processing'
    ) {
      return;
    }

    if (retryCount >= maxRetries) {
      return;
    }

    const errors = [];
    let folderWasCreated = false;

    if (!record['Evidence Folder URL']) {
      try {
        const folderUrl = createEvidenceFolder_(record, config);
        setFields_(register, rowNumber, {
          'Evidence Folder URL': folderUrl
        });
        record['Evidence Folder URL'] = folderUrl;
        folderWasCreated = true;
      } catch (error) {
        errors.push('folder: ' + error.message);
        logError_(
          'retryOperationalFailures',
          record['Source Event Key'],
          record['Issue ID'],
          error
        );
      }
    }

    let recipients = cleanText_(
      record['Pending Notification Recipients']
    )
      .split(',')
      .map(cleanText_)
      .filter(Boolean);

    if (
      folderWasCreated ||
      (
        record['Automation Status'] === 'Processing' &&
        recipients.length === 0
      )
    ) {
      recipients = uniqueEmails_(
        recipients.concat([
          record['Owner Email'],
          record['Reporter Email']
        ])
      );
    }

    const stillPending = [];

    recipients.forEach(function(recipient) {
      try {
        sendTicketEmail_(
          recipient,
          record,
          'Data quality issue recovery update'
        );
      } catch (error) {
        stillPending.push(recipient);
        errors.push(
          'email ' + recipient + ': ' + error.message
        );
        logError_(
          'retryOperationalFailures',
          record['Source Event Key'],
          record['Issue ID'],
          error
        );
      }
    });

    setFields_(register, rowNumber, {
      'Automation Status':
        errors.length === 0 ? 'Complete' : 'Action Required',
      'Last Automation Run': new Date(),
      'Retry Count': retryCount + 1,
      'Error Message': errors.join(' | '),
      'Pending Notification Recipients':
        stillPending.join(',')
    });

    processed += 1;
  });

  Logger.log('Retried ' + processed + ' operational failure(s).');
}

/**
 * Performs a non-destructive configuration and trigger check.
 */
function runHealthCheck() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const config = getConfig_();
  const requiredSheets = [
    SHEET_NAMES.REGISTER,
    SHEET_NAMES.ASSIGNMENTS,
    SHEET_NAMES.HISTORY,
    SHEET_NAMES.ERRORS,
    SHEET_NAMES.CONFIG,
    config.FORM_RESPONSE_SHEET_NAME
  ];

  const missingSheets = requiredSheets.filter(function(name) {
    return !spreadsheet.getSheetByName(name);
  });

  const triggerHandlers = ScriptApp.getProjectTriggers().map(
    function(trigger) {
      return trigger.getHandlerFunction();
    }
  );

  const result = {
    spreadsheetId: spreadsheet.getId(),
    missingSheets: missingSheets,
    triggerHandlers: triggerHandlers,
    hasFormTrigger:
      triggerHandlers.indexOf('handleFormSubmit') !== -1,
    hasEditTrigger:
      triggerHandlers.indexOf('handleRegisterEdit') !== -1,
    hasReminderTrigger:
      triggerHandlers.indexOf('sendDailyReminders') !== -1,
    checkedAt: new Date().toISOString()
  };

  Logger.log(JSON.stringify(result, null, 2));
  return result;
}

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

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

  if (sheet.getMaxColumns() < headers.length) {
    sheet.insertColumnsAfter(
      sheet.getMaxColumns(),
      headers.length - sheet.getMaxColumns()
    );
  }

  const existing = sheet.getRange(
    1,
    1,
    1,
    headers.length
  ).getDisplayValues()[0];

  const hasHeaderValues = existing.some(function(value) {
    return value !== '';
  });

  if (!hasHeaderValues) {
    sheet.getRange(1, 1, 1, headers.length)
      .setValues([headers]);
  } else {
    const mismatch = headers.filter(function(header, index) {
      return existing[index] !== header;
    });

    if (mismatch.length > 0) {
      throw new Error(
        'Header mismatch in sheet ' + name +
        '. Expected the supplied exact header order.'
      );
    }
  }

  sheet.setFrozenRows(1);
  sheet.getRange(1, 1, 1, headers.length)
    .setFontWeight('bold');

  return sheet;
}

function seedConfig_(sheet) {
  const defaults = [
    ['FORM_RESPONSE_SHEET_NAME', 'Form Responses 1'],
    ['EVIDENCE_ROOT_FOLDER_ID', 'YOUR_FOLDER_ID'],
    ['DEFAULT_OWNER_EMAIL', 'YOUR_EMAIL_ADDRESS'],
    ['DEFAULT_VALIDATOR_EMAIL', 'YOUR_EMAIL_ADDRESS'],
    ['REPORTING_LEAD_EMAIL', 'YOUR_EMAIL_ADDRESS'],
    ['DUPLICATE_WINDOW_DAYS', '30'],
    ['REMINDER_DAYS_BEFORE_DUE', '1'],
    ['ESCALATION_DAYS_AFTER_DUE', '1'],
    ['MAX_RETRIES', '3']
  ];

  const existingKeys = {};

  if (sheet.getLastRow() > 1) {
    sheet.getRange(
      2,
      1,
      sheet.getLastRow() - 1,
      2
    ).getValues().forEach(function(row) {
      existingKeys[cleanText_(row[0])] = true;
    });
  }

  defaults.forEach(function(row) {
    if (!existingKeys[row[0]]) {
      sheet.appendRow(row);
    }
  });
}

function applyRegisterValidations_(sheet) {
  const headerMap = getHeaderMap_(sheet);
  const rowCount = Math.max(sheet.getMaxRows() - 1, 1);

  setListValidation_(
    sheet,
    headerMap.Status,
    rowCount,
    STATUS_VALUES
  );

  setListValidation_(
    sheet,
    headerMap.Severity,
    rowCount,
    SEVERITY_VALUES
  );

  setListValidation_(
    sheet,
    headerMap.Frequency,
    rowCount,
    FREQUENCY_VALUES
  );

  setListValidation_(
    sheet,
    headerMap['Validation Status'],
    rowCount,
    VALIDATION_VALUES
  );

  setListValidation_(
    sheet,
    headerMap['AI Review Status'],
    rowCount,
    AI_REVIEW_VALUES
  );

  sheet.getRange(
    2,
    headerMap['Due Date'],
    rowCount,
    1
  ).setNumberFormat('yyyy-mm-dd');

  [
    'Submitted At',
    'Created Date',
    'Last Updated',
    'Resolution Date',
    'Last Automation Run',
    'Last Reminder Sent',
    'Escalated At'
  ].forEach(function(header) {
    sheet.getRange(
      2,
      headerMap[header],
      rowCount,
      1
    ).setNumberFormat('yyyy-mm-dd hh:mm');
  });
}

function setListValidation_(
  sheet,
  column,
  rowCount,
  values
) {
  const rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(values, true)
    .setAllowInvalid(false)
    .build();

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

function getConfig_() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName(SHEET_NAMES.CONFIG);

  if (!sheet || sheet.getLastRow() < 2) {
    throw new Error(
      'Config sheet is missing or has no configuration rows.'
    );
  }

  const config = {};

  sheet.getRange(
    2,
    1,
    sheet.getLastRow() - 1,
    2
  ).getValues().forEach(function(row) {
    const key = cleanText_(row[0]);

    if (key) {
      config[key] = cleanText_(row[1]);
    }
  });

  return config;
}

function requireConfig_(config, keys) {
  keys.forEach(function(key) {
    const value = cleanText_(config[key]);

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

  [
    'DEFAULT_OWNER_EMAIL',
    'DEFAULT_VALIDATOR_EMAIL',
    'REPORTING_LEAD_EMAIL'
  ].forEach(function(key) {
    if (config[key] && !isValidEmail_(config[key])) {
      throw new Error(
        'Configuration value ' + key +
        ' is not a valid email address.'
      );
    }
  });
}

function validateFormData_(data) {
  const required = {
    'Reporter Email': data.reporterEmail,
    'Report or Dashboard': data.reportName,
    'Source System': data.sourceSystem,
    'Data Domain': data.dataDomain,
    'Dataset or Table': data.dataset,
    'Affected Field': data.affectedField,
    'Example Reference': data.exampleReference,
    'Issue Description': data.description,
    'Business Impact': data.businessImpact,
    'Severity': data.severity,
    'Frequency': data.frequency
  };

  const missing = Object.keys(required).filter(function(key) {
    return !cleanText_(required[key]);
  });

  if (missing.length > 0) {
    throw new Error(
      'Missing required form fields: ' + missing.join(', ')
    );
  }

  if (!isValidEmail_(data.reporterEmail)) {
    throw new Error('Reporter Email is invalid.');
  }

  if (SEVERITY_VALUES.indexOf(data.severity) === -1) {
    throw new Error('Severity is not an allowed value.');
  }

  if (FREQUENCY_VALUES.indexOf(data.frequency) === -1) {
    throw new Error('Frequency is not an allowed value.');
  }

  if (
    data.submittedEvidenceLink &&
    data.submittedEvidenceLink.indexOf('https://') !== 0
  ) {
    throw new Error(
      'Submitted Evidence Link must begin with https://.'
    );
  }
}

function generateIssueId_(register, dateValue) {
  const timezone = SpreadsheetApp.getActiveSpreadsheet()
    .getSpreadsheetTimeZone();
  const year = Utilities.formatDate(
    dateValue,
    timezone,
    'yyyy'
  );

  const propertyKey = 'DQI_COUNTER_' + year;
  const properties = PropertiesService.getScriptProperties();
  let counter = Number(properties.getProperty(propertyKey) || 0);
  let issueId = '';

  do {
    counter += 1;
    issueId =
      'DQI-' + year + '-' +
      Utilities.formatString('%05d', counter);
  } while (
    findRowByExactValue_(register, 'Issue ID', issueId)
  );

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

function createFingerprint_(parts) {
  const normalized = parts.map(function(value) {
    return cleanText_(value)
      .toLowerCase()
      .replace(/\s+/g, ' ')
      .trim();
  }).join('|');

  const digest = Utilities.computeDigest(
    Utilities.DigestAlgorithm.SHA_256,
    normalized,
    Utilities.Charset.UTF_8
  );

  return digest.map(function(byte) {
    const normalizedByte = (byte + 256) % 256;
    return ('0' + normalizedByte.toString(16)).slice(-2);
  }).join('');
}

function findRelatedIssue_(
  register,
  fingerprint,
  submittedAt,
  windowDays
) {
  if (register.getLastRow() < 2) {
    return '';
  }

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

  for (let index = values.length - 1; index >= 0; index -= 1) {
    const record = objectFromValues_(headers, values[index]);
    const created = toDate_(record['Created Date']);

    if (
      record.Fingerprint === fingerprint &&
      created &&
      Math.abs(
        submittedAt.getTime() - created.getTime()
      ) <= windowDays * 86400000
    ) {
      return cleanText_(record['Issue ID']);
    }
  }

  return '';
}

function getAssignment_(domain, sourceSystem, config) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName(SHEET_NAMES.ASSIGNMENTS);

  let selected = null;
  let selectedScore = -1;

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

    values.forEach(function(rowValues) {
      const rule = objectFromValues_(headers, rowValues);

      if (
        cleanText_(rule.Active).toLowerCase() !== 'yes'
      ) {
        return;
      }

      const ruleDomain = cleanText_(rule['Data Domain']);
      const ruleSource = cleanText_(rule['Source System']);
      const domainMatches =
        ruleDomain === '*' ||
        ruleDomain.toLowerCase() === domain.toLowerCase();
      const sourceMatches =
        ruleSource === '*' ||
        ruleSource.toLowerCase() === sourceSystem.toLowerCase();

      if (!domainMatches || !sourceMatches) {
        return;
      }

      let score = 0;

      if (ruleDomain !== '*') {
        score += 1;
      }

      if (ruleSource !== '*') {
        score += 2;
      }

      if (score > selectedScore) {
        selected = rule;
        selectedScore = score;
      }
    });
  }

  const ownerEmail = cleanText_(
    selected ? selected['Owner Email'] : config.DEFAULT_OWNER_EMAIL
  );

  const validatorEmail = cleanText_(
    selected
      ? selected['Validator Email']
      : config.DEFAULT_VALIDATOR_EMAIL
  );

  if (!isValidEmail_(ownerEmail)) {
    throw new Error(
      'No valid owner email was found for the assignment.'
    );
  }

  if (!isValidEmail_(validatorEmail)) {
    throw new Error(
      'No valid validator email was found for the assignment.'
    );
  }

  return {
    ownerEmail: ownerEmail,
    validatorEmail: validatorEmail
  };
}

function calculateDueDate_(startDate, severity) {
  const daysBySeverity = {
    Critical: 1,
    High: 3,
    Medium: 7,
    Low: 14
  };

  const dueDate = new Date(startDate.getTime());
  dueDate.setDate(
    dueDate.getDate() + (daysBySeverity[severity] || 7)
  );
  return dueDate;
}

function createEvidenceFolder_(record, config) {
  requireConfig_(config, ['EVIDENCE_ROOT_FOLDER_ID']);

  const root = DriveApp.getFolderById(
    config.EVIDENCE_ROOT_FOLDER_ID
  );

  const safeReportName = cleanText_(
    record['Report or Dashboard']
  )
    .replace(/[\\/:*?"<>|#%{}]/g, '_')
    .substring(0, 70);

  const folder = root.createFolder(
    record['Issue ID'] + ' - ' +
    (safeReportName || 'Data Quality Evidence')
  );

  return folder.getUrl();
}

function sendTicketEmail_(recipient, record, subjectPrefix) {
  if (!isValidEmail_(recipient)) {
    throw new Error(
      'Notification recipient is not a valid email address.'
    );
  }

  const subject =
    subjectPrefix + ': ' + record['Issue ID'];

  const body = [
    subjectPrefix,
    '',
    'Issue ID: ' + record['Issue ID'],
    'Status: ' + record.Status,
    'Severity: ' + record.Severity,
    'Report: ' + record['Report or Dashboard'],
    'Source system: ' + record['Source System'],
    'Dataset or table: ' + record['Dataset or Table'],
    'Affected field: ' + record['Affected Field'],
    'Owner: ' + record['Owner Email'],
    'Due date: ' + formatDate_(record['Due Date']),
    'Related issue: ' + (
      record['Related Issue ID'] || 'None'
    ),
    'Evidence folder: ' + (
      record['Evidence Folder URL'] ||
      'Folder creation is pending'
    ),
    '',
    'Description:',
    record['Issue Description'],
    '',
    'Business impact:',
    record['Business Impact'],
    '',
    'Open the controlled Issue Register for current status.'
  ].join('\n');

  GmailApp.sendEmail(
    recipient,
    subject,
    body,
    {name: 'Data Quality Issue Register'}
  );
}

function sendReminderEmail_(record, daysUntilDue) {
  const timing = daysUntilDue < 0
    ? Math.abs(daysUntilDue) + ' day(s) overdue'
    : daysUntilDue + ' day(s) until due';

  GmailApp.sendEmail(
    record['Owner Email'],
    'Data quality issue reminder: ' + record['Issue ID'],
    [
      'Issue ID: ' + record['Issue ID'],
      'Status: ' + record.Status,
      'Timing: ' + timing,
      'Due date: ' + formatDate_(record['Due Date']),
      'Report: ' + record['Report or Dashboard'],
      'Affected field: ' + record['Affected Field'],
      '',
      'Review and update the controlled Issue Register.'
    ].join('\n'),
    {name: 'Data Quality Issue Register'}
  );
}

function sendEscalationEmail_(
  record,
  reportingLeadEmail,
  overdueDays
) {
  GmailApp.sendEmail(
    reportingLeadEmail,
    'Overdue data quality issue: ' + record['Issue ID'],
    [
      'Issue ID: ' + record['Issue ID'],
      'Owner: ' + record['Owner Email'],
      'Status: ' + record.Status,
      'Severity: ' + record.Severity,
      'Overdue by: ' + overdueDays + ' day(s)',
      'Report: ' + record['Report or Dashboard'],
      'Affected field: ' + record['Affected Field'],
      '',
      'Review ownership, due date, and required escalation.'
    ].join('\n'),
    {name: 'Data Quality Issue Register'}
  );
}

function notifyWithFailureTracking_(
  sheet,
  rowNumber,
  recipients,
  subjectPrefix
) {
  const record = getRowObject_(sheet, rowNumber);
  const failedRecipients = [];

  uniqueEmails_(recipients).forEach(function(recipient) {
    try {
      sendTicketEmail_(recipient, record, subjectPrefix);
    } catch (error) {
      failedRecipients.push(recipient);
      logError_(
        'notifyWithFailureTracking',
        record['Source Event Key'],
        record['Issue ID'],
        error
      );
    }
  });

  if (failedRecipients.length > 0) {
    const existingPending = cleanText_(
      record['Pending Notification Recipients']
    )
      .split(',')
      .map(cleanText_)
      .filter(Boolean);

    setFields_(sheet, rowNumber, {
      'Automation Status': 'Action Required',
      'Last Automation Run': new Date(),
      'Error Message':
        'Notification failed for ' +
        failedRecipients.join(', '),
      'Pending Notification Recipients':
        uniqueEmails_(
          existingPending.concat(failedRecipients)
        ).join(',')
    });
  }
}

function markOperationalFailure_(
  sheet,
  rowNumber,
  record,
  error,
  recipient
) {
  logError_(
    'sendDailyReminders',
    record['Source Event Key'],
    record['Issue ID'],
    error
  );

  const pending = cleanText_(
    record['Pending Notification Recipients']
  )
    .split(',')
    .map(cleanText_)
    .filter(Boolean);

  if (recipient) {
    pending.push(recipient);
  }

  setFields_(sheet, rowNumber, {
    'Automation Status': 'Action Required',
    'Last Automation Run': new Date(),
    'Error Message': error.message,
    'Pending Notification Recipients':
      uniqueEmails_(pending).join(',')
  });
}

function requiredResolutionFields_(record) {
  const required = [
    'Root Cause',
    'Resolution',
    'Preventive Action'
  ];

  return required.filter(function(field) {
    return !cleanText_(record[field]);
  });
}

function isAllowedTransition_(fromStatus, toStatus) {
  if (fromStatus === toStatus) {
    return true;
  }

  const transitions = {
    'New': ['Triaged', 'Needs Review', 'Rejected'],
    'Needs Review': ['Triaged', 'Rejected'],
    'Triaged': [
      'Investigating',
      'Workaround Active',
      'Rejected'
    ],
    'Investigating': [
      'Workaround Active',
      'Pending Validation',
      'Rejected'
    ],
    'Workaround Active': [
      'Investigating',
      'Pending Validation'
    ],
    'Pending Validation': [
      'Investigating',
      'Resolved'
    ],
    'Resolved': ['Closed', 'Investigating'],
    'Closed': ['Investigating'],
    'Rejected': ['New']
  };

  return (
    transitions[fromStatus] || []
  ).indexOf(toStatus) !== -1;
}

function revertCell_(
  sheet,
  range,
  oldValue,
  record,
  message
) {
  range.setValue(oldValue);

  setFields_(sheet, range.getRow(), {
    'Automation Status': 'Action Required',
    'Last Automation Run': new Date(),
    'Error Message': message
  });

  logHistory_(
    record['Issue ID'],
    'EDIT_REJECTED',
    'automation',
    range.getValue(),
    oldValue,
    message
  );
}

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

  const targetRow = sheet.getLastRow() + 1;
  sheet.getRange(
    targetRow,
    1,
    1,
    headers.length
  ).setValues([row]);

  return targetRow;
}

function setFields_(sheet, rowNumber, updates) {
  const headerMap = getHeaderMap_(sheet);

  Object.keys(updates).forEach(function(header) {
    if (!headerMap[header]) {
      throw new Error(
        'Header not found in ' + sheet.getName() + ': ' + header
      );
    }

    sheet.getRange(
      rowNumber,
      headerMap[header]
    ).setValue(updates[header]);
  });
}

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

  return objectFromValues_(headers, values);
}

function objectFromValues_(headers, values) {
  const result = {};

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

  return result;
}

function getHeaders_(sheet) {
  if (sheet.getLastColumn() < 1) {
    return [];
  }

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

function getHeaderMap_(sheet) {
  const result = {};

  getHeaders_(sheet).forEach(function(header, index) {
    if (header) {
      result[header] = index + 1;
    }
  });

  return result;
}

function getCellByHeader_(sheet, rowNumber, header) {
  const map = getHeaderMap_(sheet);

  if (!map[header]) {
    return '';
  }

  return sheet.getRange(
    rowNumber,
    map[header]
  ).getValue();
}

function findRowByExactValue_(sheet, header, value) {
  if (!sheet || sheet.getLastRow() < 2) {
    return 0;
  }

  const map = getHeaderMap_(sheet);

  if (!map[header]) {
    throw new Error(
      'Header not found in ' + sheet.getName() + ': ' + header
    );
  }

  const range = sheet.getRange(
    2,
    map[header],
    sheet.getLastRow() - 1,
    1
  );

  const match = range.createTextFinder(String(value))
    .matchEntireCell(true)
    .useRegularExpression(false)
    .findNext();

  return match ? match.getRow() : 0;
}

function logHistory_(
  issueId,
  eventType,
  actor,
  fromValue,
  toValue,
  details
) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName(SHEET_NAMES.HISTORY);

  appendObject_(sheet, {
    'Event ID': Utilities.getUuid(),
    'Issue ID': issueId,
    'Event Timestamp': new Date(),
    'Event Type': eventType,
    'Actor Email': actor || 'automation',
    'From Value': fromValue,
    'To Value': toValue,
    'Details': details
  });
}

function logError_(
  handler,
  sourceEventKey,
  issueId,
  error
) {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = spreadsheet.getSheetByName(SHEET_NAMES.ERRORS);

  if (!sheet) {
    sheet = ensureSheet_(
      spreadsheet,
      SHEET_NAMES.ERRORS,
      ERROR_HEADERS
    );
  }

  appendObject_(sheet, {
    'Error ID': Utilities.getUuid(),
    'Occurred At': new Date(),
    'Handler': handler,
    'Source Event Key': sourceEventKey,
    'Issue ID': issueId,
    'Message': error && error.message
      ? error.message
      : String(error),
    'Stack': error && error.stack
      ? error.stack
      : '',
    'Retry Count': 0,
    'Resolved At': ''
  });
}

function getActorEmail_(event) {
  try {
    if (event.user && event.user.getEmail()) {
      return event.user.getEmail();
    }
  } catch (error) {
    Logger.log(
      'Editor identity was not available: ' + error.message
    );
  }

  try {
    return Session.getActiveUser().getEmail() || 'unknown';
  } catch (error) {
    return 'unknown';
  }
}

function uniqueEmails_(values) {
  const seen = {};

  return values
    .map(cleanText_)
    .filter(function(value) {
      if (!value || !isValidEmail_(value)) {
        return false;
      }

      const normalized = value.toLowerCase();

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

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

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

function firstNonBlank_(values) {
  for (let index = 0; index < values.length; index += 1) {
    if (cleanText_(values[index])) {
      return values[index];
    }
  }

  return '';
}

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

  return String(value).trim();
}

function positiveInteger_(value, fallback) {
  const parsed = parseInt(value, 10);

  return Number.isFinite(parsed) && parsed >= 0
    ? parsed
    : fallback;
}

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

  if (!value) {
    return null;
  }

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

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

function dateKey_(dateValue) {
  const date = toDate_(dateValue);

  if (!date) {
    return '';
  }

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

function formatDate_(dateValue) {
  const date = toDate_(dateValue);

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

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

Configuration and deployment instructions

1. Run setupSystem manually from the Apps Script editor.
2. Review the authorization request. The script needs spreadsheet, Drive, Gmail, and trigger access.
3. Open the Config sheet and replace YOUR_FOLDER_ID with the ID from the production evidence root folder URL.
4. Replace every YOUR_EMAIL_ADDRESS value with valid internal addresses.
5. Confirm that FORM_RESPONSE_SHEET_NAME exactly matches the linked response tab.
6. Populate Assignments with valid owner and validator addresses.
7. Run runHealthCheck. Open the execution log and confirm that required sheets exist.
8. Run installAutomationTriggers while signed in as the automation account.
9. Submit a test response through the form. Do not run handleFormSubmit directly because it requires an event object.
10. Confirm the register row, Issue ID, folder, emails, and History event.
11. Inspect Apps Script Executions for trigger errors. Review the workbook Error Log for business-recovery details.

The form trigger is event-based, the edit trigger is event-based, and the reminder trigger is time-based. The script removes its own prior managed triggers before reinstalling them, preventing duplicate trigger configuration.

Duplicate execution is prevented with Source Event Key and a script lock. Failed source events can be retried with retryFailedSubmissions. Missing folders and notifications can be retried with retryOperationalFailures. Both retry functions process no more than 20 records in one run to reduce execution-time risk.

Common setup errors include a mismatched response-sheet name, placeholder email addresses, an incorrect Drive folder ID, missing folder permissions, or headers that were manually rearranged. Correct the configuration rather than changing header names in the script.

Failure Handling and Operational Reliability

The Issue Register acts as the operational dead-letter queue for partially completed tickets through Automation Status. The Error Log acts as the technical dead-letter queue for source events and automation exceptions.

Staff identify failures using the Action Required filter and unresolved Error Log rows. Recovery is explicit rather than silent. The automation does not repeatedly retry indefinitely because an invalid address or revoked permission could otherwise generate continuing failures.

A weekly reconciliation compares the number of new form-response rows with unique Source Event Keys in the register. Any response row without a matching source key is investigated. This catches events that failed before a register record could be created.

A Complete Example

A reporting analyst notices that the weekly warehouse fill-rate report shows several orders with a delivered quantity twice the expected value.

1. The analyst opens the Google Form and enters Warehouse Fill Rate as the affected report.
2. The analyst selects Warehouse Export as Source System and Inventory as Data Domain.
3. The affected dataset is shipment_fact, and the field is delivered_qty.
4. The example reference identifies one test shipment and the affected reporting week without including customer details.
5. The description states that split shipments are producing two final-delivery rows, while the report expects one consolidated delivered quantity.
6. The analyst selects High severity, Recurring frequency, and records a temporary workaround that excludes duplicate final-delivery events.
7. Google Forms writes the response to row 52 of the linked sheet. The Source Event Key becomes the numeric response-sheet ID followed by :52.
8. Apps Script validates the values and generates DQI-2026-00041.
9. The fingerprint matches DQI-2026-00017, a previously resolved issue involving the same source, table, field, and normalized description. The new ticket is retained and its Related Issue ID becomes DQI-2026-00017.
10. The Inventory and Warehouse Export assignment rule selects the inventory data owner and reporting validator.
11. Because the ticket is High severity, the due date is set three calendar days after submission.
12. The status starts as Needs Review because a possible recurrence exists.
13. Apps Script creates the folder DQI-2026-00041 - Warehouse Fill Rate and stores its URL.
14. Gmail sends the ticket details to the owner and reporter.
15. The reporting lead confirms that the issue is a recurrence and moves it to Triaged.
16. The owner moves it to Investigating and determines that an upstream export changed its shipment-event ordering after a software configuration update.
17. The owner corrects the transformation to select the latest final-delivery event by shipment identifier and timestamp.
18. Root Cause, Resolution, and Preventive Action are completed. Preventive Action adds a duplicate final-event count to the reporting pipeline’s daily control output.
19. The owner changes Status to Pending Validation. Apps Script sets Validation Status to Pending and emails the validator.
20. The validator tests the original example, two split shipments, and one normal shipment. The validator records the evidence folder file and result in Validation Evidence.
21. The validator selects Passed. The automation changes Status to Resolved, records Resolution Date, logs the event, and notifies the reporter and owner.
22. The reporting lead later changes the status to Closed after confirming that the preventive check is active.

If folder creation had failed, the issue investigation could still proceed. The record would appear in the Action Required view, and an administrator could restore Drive access and run the operational retry without creating another ticket.

Implementation Cost

All amounts below are representative planning assumptions, not verified client results or vendor quotations. Actual subscription, consulting, tax, and internal labour costs should be confirmed for the organization.

The $35 recurring tool assumption is not a published Google price. It is a representative internal allocation used for the savings model. Apps Script still requires maintenance even when no separate automation-platform invoice is incurred.

Estimated Time and Cost Savings

The savings model covers issue administration, triage preparation, follow-up, status maintenance, and historical searching. It does not assume that the automation eliminates the technical work required to correct source data or reporting logic.

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

Current calculation: 45 × 24 ÷ 60 = 18.00 hours

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

Routine processing: 45 × 7 ÷ 60 = 5.25 hours

Exception handling: 45 × 15% × 12 ÷ 60 = 1.35 hours

Maintenance: 3.00 hours

Total new monthly labour: 5.25 + 1.35 + 3.00 = 9.60 hours

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

Hours recovered: 18.00 – 9.60 = 8.40 hours

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

Labour value: 8.40 × $48 = $403.20

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

Net monthly value: $403.20 – $35 = $368.20

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

Payback calculation: $10,332 ÷ $368.20 = approximately 28.1 months

Recovered time does not automatically reduce payroll. It may create additional reporting capacity, reduce overtime, improve response time, support higher issue volume, and reduce dependence on employees who remember historical fixes.

Non-financial benefits include:

• Fewer requests for missing diagnostic information
• Clearer owner and validator responsibilities
• Visible due dates and overdue work
• Searchable root causes and preventive actions
• Consistent resolution evidence
• Reliable recurrence tracking
• Better executive reporting on backlog and causes
• Reduced reliance on email archives
• A more predictable experience for reporting analysts

Readers should replace volume, handling time, exception rate, maintenance, hourly cost, recurring allocation, implementation hours, and consulting rates with their own figures.

Adding AI to the Automation

AI should be added only after the deterministic workflow is reliable. Forms, required fields, exact status rules, owner lookups, due-date calculations, unique IDs, and validation gates do not require AI.

Potential AI applications include:

• Suggesting similar historical issue descriptions
• Summarizing long issue and resolution notes
• Extracting candidate source systems or affected fields from unstructured email
• Identifying descriptions that appear to lack an expected result
• Suggesting issue categories for analyst confirmation
• Producing a draft recurrence summary for a weekly review

The core automation provides structured records, assignments, notifications, deadlines, audit history, and deterministic duplicate-event prevention. AI adds value only when comparing unstructured descriptions whose wording may differ even when the underlying problem is related.

The Recommended AI Enhancement

The recommended enhancement compares each new issue with up to 50 recent issues from the same data domain. It suggests likely duplicates, recurrences, or related problems for analyst review.

• Trigger: A ticket is successfully created and Automation Status is Complete.
• AI input: New issue ID, sanitized description, source system, domain, dataset, field, and candidate historical issues.
• System instruction: Compare only the supplied records and return structured suggestions without making final decisions.
• Expected output: Valid JSON containing no more than three candidate matches.
• Validation: Suggested IDs must exist in the supplied candidate list, confidence must be between zero and one, and relationship must use an allowed value.
• Record update: Store suggested IDs, highest confidence, and Review Required status.
• Human review: An analyst confirms or dismisses every suggestion.
• Low confidence: Results below 0.75 are retained in the AI log but do not create a review flag.
• Prohibited data: Customer details, employee details, payment data, credentials, secrets, and unrestricted source records.
• Failure behavior: Leave AI Review Status as Not Run and continue the core workflow.

Use the following exact system instruction:

You are assisting a data-quality reporting team. Compare one new issue with a supplied list of existing issues.

Use only the supplied information. Do not invent issue IDs, fields, systems, causes, or facts. Do not decide that records should be merged, closed, rejected, or resolved.

Classify a candidate only as:
same_problem
possible_recurrence
related_not_duplicate

Return no more than three candidates. Prefer no candidate over a weak match. Confidence must be a number from 0 to 1. Explain each suggestion in one concise sentence using the overlapping symptoms, source, dataset, field, or failure pattern.

Return valid JSON matching the required schema and no additional text.

Use this reusable user prompt:

NEW ISSUE
Issue ID: {{NEW_ISSUE_ID}}
Source system: {{SOURCE_SYSTEM}}
Data domain: {{DATA_DOMAIN}}
Dataset or table: {{DATASET}}
Affected field: {{AFFECTED_FIELD}}
Description: {{SANITIZED_DESCRIPTION}}
Business impact: {{SANITIZED_BUSINESS_IMPACT}}

CANDIDATE ISSUES
{{CANDIDATE_ISSUES_JSON}}

Compare the new issue with the candidates. Return only candidate IDs present in CANDIDATE ISSUES. If the information is insufficient or no meaningful relationship exists, return an empty candidate_matches array.

The required structured response is:

{
  "new_issue_id": "DQI-2026-00041",
  "candidate_matches": [
    {
      "issue_id": "DQI-2026-00017",
      "relationship": "possible_recurrence",
      "confidence": 0.86,
      "reason": "Both issues concern duplicate final-delivery rows in shipment_fact affecting delivered_qty."
    }
  ],
  "review_summary": "One possible recurrence requires analyst confirmation.",
  "insufficient_information": false
}

The automation configuration should follow this sequence:

1. Select only recent candidate records from the same domain, excluding descriptions containing prohibited data.
2. Limit the candidate set to 50 records and the description length to a controlled maximum.
3. Send the system instruction, user prompt, and required JSON schema to an organization-approved AI service.
4. Parse the response as JSON.
5. Reject output containing unknown Issue IDs, additional relationship values, confidence outside zero through one, or more than three candidates.
6. If the highest validated confidence is at least 0.75, update AI Suggested Related IDs, AI Confidence, and AI Review Status to Review Required.
7. If no candidate meets the threshold, leave AI Review Status as Not Run or record a separate No Suggestion result if the schema is extended.
8. Require an analyst to select Confirmed or Dismissed.
9. Log the service name, model or deployment version, timestamp, token or usage data, and validation outcome without retaining prohibited prompt content.

No provider-specific API code is included in the core deployment because the AI service, endpoint, authentication method, approved model, and data-processing terms must be selected by the organization. Guessing an endpoint or model would make the implementation less reliable. The prompt and validation sequence provide the complete provider-neutral automation configuration.

Benefits of the AI Enhancement

• Less time searching older descriptions with different wording
• More consistent suggestions for recurring issues
• Quicker identification of related source, table, and field combinations
• Better preparation for weekly recurrence reviews
• Improved handling of long, unstructured descriptions
• More complete recurrence reporting after analyst confirmation

These benefits are specific to semantic comparison. They do not create the ticket, assign the owner, send the reminder, enforce validation, or prove that a correction works. Those controls remain part of normal automation and human review.

What Remains Rule-Based or Human-Controlled

• Unique IDs: Generated deterministically to prevent collisions and preserve auditability.
• Required fields: Enforced by form and script validation rather than inferred by AI.
• Severity deadlines: Calculated from explicit rules.
• Owner assignment: Determined by controlled routing tables.
• Duplicate event handling: Based on Source Event Key, not semantic similarity.
• Final recurrence decision: Confirmed by an analyst because similar wording does not prove a shared cause.
• Root-cause conclusion: Determined by a technically responsible owner using source evidence.
• Resolution approval: Performed by the assigned validator.
• Report publication: Remains a human business decision.
• Issue rejection or closure: Requires an authorized employee and supporting notes or validation.
• Policy exceptions: Remain with the reporting lead or appropriate executive owner.

These controls remain human or deterministic because errors could affect executive reporting and operational decisions. AI can support review but cannot accept the resulting business risk.

Estimating the Additional Value of AI

The following figures are representative assumptions rather than measured outcomes:

Gross AI time recovered: 45 × 2 ÷ 60 = 1.50 hours

Human review time: 45 × 0.5 ÷ 60 = 0.375 hours

Correction time: 45 × 10% × 2 ÷ 60 = 0.150 hours

Failure fallback time: 45 × 3% × 1.5 ÷ 60 = 0.034 hours

Net additional capacity: 1.50 – 0.375 – 0.150 – 0.034 = approximately 0.94 hours per month

Additional labour value: 0.94 × $48 = approximately $45.12 per month

Net value after AI usage budget: $45.12 – $10 = approximately $35.12 per month

The financial value is modest at this volume. The stronger justification is improved recurrence visibility and reduced search time. AI should not be added unless those benefits justify its governance and monitoring requirements.

Testing Checklist

Use synthetic sample data in the test environment before processing real reporting information.

Ongoing Maintenance

The reporting operations lead should own the business process. A designated Workspace or automation administrator should own the script, triggers, permissions, and recovery procedures. Each role needs a named backup.

Configuration changes, header changes, form-question changes, assignment updates, and script releases should be documented. Former users must be removed promptly from the form, workbook, Drive root, notification groups, and automation ownership arrangements.

If an AI service is added, monitor usage cost, model or deployment changes, malformed-output rate, analyst confirmation rate, and prohibited-data incidents. Periodically sample both confirmed and dismissed suggestions.

When to Move to Dedicated Software

The Google Workspace implementation should not be replaced solely because it is spreadsheet-based. It remains appropriate while volume, access, workflow, and control requirements remain manageable.

Consider a dedicated data-quality, service-management, governance, or workflow platform when several of the following conditions appear:

• Thousands of active issues create spreadsheet performance or usability problems.
• Multiple business units require row-level or field-level permission models.
• External customers, suppliers, or partners need a secure portal.
• Formal regulatory controls require certified audit trails and segregation of duties.
• Many source systems require automated profiling, lineage, schema detection, or rule monitoring.
• Issue relationships become many-to-many and difficult to represent in one register row.
• Workflow branches require several sequential and parallel approvals.
• Exception rates or retry activity consume excessive administrative time.
• Mobile or offline workflows become operational requirements.
• Service-level agreements require more sophisticated calendars and escalation policies.
• Leadership requires enterprise analytics across incidents, controls, tests, and remediation projects.
• Vendor support, uptime commitments, or formal release management become mandatory.
• Apps Script quotas, Gmail limits, or Drive operations become material constraints.
• Maintenance depends too heavily on one script owner.
• Security risk exceeds what workbook and Drive permissions can reasonably control.

A future platform should preserve the established field definitions, workflow statuses, assignment rules, evidence requirements, and History records. The operational design remains useful even if the technology changes.

Implementation Checklist

• Confirm issue volume, users, severity definitions, and validation requirements.
• Approve Google Forms, Sheets, Apps Script, Gmail, and Drive as the selected tools.
• Create test and production automation accounts.
• Create restricted test and production evidence folders.
• Create the intake form with exact field titles and validation.
• Link the form to the correct response workbook.
• Paste the complete Apps Script into the bound spreadsheet project.
• Run the setup function and verify all headers.
• Replace Config placeholders.
• Populate and test assignment rules.
• Protect system-controlled columns and sheets.
• Install triggers as the designated automation account.
• Validate field mappings from form response to register.
• Test sequential ID generation and duplicate-event handling.
• Test recurrence linking without suppressing new occurrences.
• Test owner assignment, reassignment, and notifications.
• Test status transitions and rejected edits.
• Test the resolution-validation gate.
• Test reminders and escalations.
• Test evidence-folder creation and failed-folder recovery.
• Create operational views and verify reporting formulas.
• Test Error Log, retry count, manual recovery, and reconciliation.
• Complete security, privacy, retention, and former-user controls.
• Complete user acceptance testing with sample data.
• Document deployment, rollback, support, and trigger ownership.
• Replace representative cost assumptions with actual figures.
• Replace representative savings assumptions with measured handling times.
• Enable AI only after the core process is stable and approved.
• Validate AI JSON, confidence, prohibited-data controls, and human review.
• Assign primary and backup maintenance owners.
• Review dedicated-software upgrade criteria at least annually.