Executive KPI Dashboard Across Finance, Sales, and Operations

The Business Situation

Harborlight Equipment Services is a fictional 85-person industrial equipment service business. Its leadership team includes the managing director, finance manager, sales director, operations manager, and strategy analyst. Finance has four employees, sales has eight, and operations includes approximately 45 field and workshop employees.

The business tracks performance in an accounting platform, a customer relationship management system, an operations scheduling application, and several Google Sheets. Each system contains useful information, but none provides a complete executive view.

The strategy analyst prepares a weekly leadership report covering nine key performance indicators. The indicators include revenue, gross margin, overdue receivables, qualified sales pipeline, win rate, on-time job completion, resource utilization, open operational exceptions, and forecast variance.

Every Monday morning, the analyst exports reports, copies values into a summary workbook, reconciles differences, asks KPI owners for explanations, updates charts, and emails a PDF or spreadsheet link. The process takes several hours and depends heavily on one employee understanding the location and meaning of every source value.

Leadership wanted a limited, governed dashboard rather than a report containing every available metric. Each KPI needed a precise definition, an accountable owner, an approved source, threshold rules, a refresh cadence, exception notes, and a drill-down link.

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

The Existing Process

The original reporting process followed the same sequence each week:

1. The finance manager exported revenue, margin, and receivables figures from the accounting platform.
2. The sales director exported pipeline and opportunity data from the CRM.
3. The operations manager exported job completion and utilization figures from the scheduling system.
4. The strategy analyst opened a separate planning spreadsheet to obtain the current forecast variance.
5. The analyst copied nine values into an executive reporting spreadsheet.
6. Each value was compared with the prior week and the relevant target.
7. Unusual values were checked against source reports or clarified by email.
8. Charts and status colors were updated manually.
9. The analyst emailed the report link and followed up with KPI owners for missing explanations.

These problems did not imply that the source applications were inadequate. The issue was that leadership reporting crossed system boundaries. The business needed a controlled integration and reporting layer above its existing applications.

What the New System Needed to Do

The system was not intended to replace the accounting, CRM, or operations applications. Those systems remained authoritative for transaction-level data. The new layer standardized a small number of management indicators and made their preparation observable.

Implementation Approaches Considered

Why the Spreadsheet-Only Option Was Rejected

Improving formatting and formulas would have made the weekly workbook easier to read, but it would not have removed the source collection, validation, duplicate handling, or email follow-up work. It also would not have provided reliable execution logs.

Why Power BI Was Not Selected Initially

Power BI was technically capable, but Harborlight used Google Workspace as its primary collaboration environment. Adopting Power BI would have added identity, sharing, refresh, and support considerations that were not justified by the narrow initial scope. It remains a reasonable option if the company later standardizes on Microsoft reporting tools.

Why a Connected Warehouse Was Deferred

A warehouse would be appropriate for transaction-level analytics, larger datasets, more frequent refreshes, or complex relationships. For nine weekly indicators sourced from standardized exports, it would have increased implementation and maintenance without solving a proportionally larger problem.

Why the Google Reporting Stack Was Selected

The selected approach retained the company’s existing Google Workspace environment, introduced controlled automation, and provided a clear upgrade path. It was sufficiently technical to address validation and reliability without requiring a new database platform.

The Selected Solution

The solution used Google Sheets as the governed reporting store, Google Apps Script as the automation layer, Gmail for receiving exports and sending alerts, Google Drive for controlled storage, and Looker Studio for dashboard presentation.

The source applications were retained. The weekly planning spreadsheet was also retained, but it was standardized so Apps Script could read it safely. Manual copying, status-color decisions, chart updates, and routine email assembly were removed.

Human control remained over KPI definitions, threshold changes, exception explanations, performance interpretation, corrective action, and any decision affecting customers, employees, spending, or accounting records.

System Architecture and Data Flow

• Intake: Scheduled CSV attachments received under controlled Gmail labels, plus a standardized Google Sheets planning source.
• System of record: A central Google Sheets workbook containing definitions, raw imports, KPI facts, notes, current status, and logs.
• Automation layer: A bound Google Apps Script project with time-based triggers, validation logic, idempotent upserts, and email functions.
• Document storage: A restricted Google Drive or Shared Drive folder containing the workbook, source archives, and documentation.
• Notifications: Gmail messages sent to KPI owners, backup owners, the strategy analyst, and executive recipients.
• Reporting: Looker Studio data sources connected to the KPI_Current and KPI_Facts worksheets.
• AI layer: Optional structured exception-summary drafting after the deterministic workflow is operating reliably.

1. Source generation: Each operational system produces a CSV using an approved column structure. A scheduled export emails the file from an approved sender to the automation mailbox.
2. Email routing: Gmail filters apply labels such as KPI/Finance, KPI/Sales, and KPI/Operations. The label is the Apps Script trigger source during a scheduled run.
3. Attachment validation: Apps Script checks the sender, attachment filename, content hash, required headers, and prior import history. An invalid file is not loaded.
4. Evidence archiving: A valid attachment is copied into a restricted Drive archive folder using a deterministic filename containing the source, date, and content hash.
5. Raw loading: CSV rows are appended to the appropriate raw worksheet. The script adds an import ID and timestamp.
6. Planning-sheet refresh: Apps Script opens the approved planning spreadsheet by ID, reads the required worksheet, and copies new source records into Planning_Raw.
7. Validation: Required dates and numeric fields are checked. Values outside configured valid ranges are written to Data_Quality and do not overwrite a valid fact.
8. Transformation: Each source field is mapped to a KPI definition. Currency symbols, commas, percentages, and date values are normalized.
9. Fact upsert: The script creates a fact key such as FIN_REV_MTD|2026-07-12. A corrected source record for the same KPI and date replaces the earlier fact only when it has a later source-update timestamp.
10. Threshold evaluation: The actual value is compared with warning and critical limits. The result becomes On Track, Warning, Critical, Stale, or Data Missing.
11. Note merge: Any controlled owner note matching the fact key is added to the reporting output without allowing the source import to overwrite the note.
12. Current-view refresh: The newest valid fact for each KPI is written to KPI_Current. Missing or stale indicators are represented explicitly.
13. Dashboard update: Looker Studio queries the updated Google Sheets data according to its configured data-freshness behavior. Apps Script does not claim to force a Looker Studio refresh.
14. Notification: Daily source-health messages identify missing or stale inputs. A Monday executive email summarizes all current exceptions and links to the dashboard.
15. Failure path: Import, configuration, validation, Drive, or Gmail failures are written to logs. Unexpected failures generate an administrator alert when Gmail remains available.

Data Structure

The reporting workbook uses separate worksheets for configuration, raw data, validated facts, notes, current output, and operational logs. This separation prevents report users from treating an imported value as automatically trustworthy.

Supporting Worksheets

Finance_Raw, Sales_Raw, Operations_Raw, and Planning_Raw

Contain source rows plus import IDs and import timestamps.

Exception_Notes

Contains fact key, KPI ID, period, note, note owner, update timestamp, and review status.

Processed_Imports

Stores message ID, attachment name, content hash, archived file URL, row count, and processing status.

Data_Quality

Records missing fields, invalid values, stale sources, malformed files, and duplicate configuration.

Automation_Log

Stores run ID, start and finish times, status, imported row count, fact count, issue count, notification count, and error message.

Alert_History

Prevents the same KPI status from generating repeated messages more than once per day.

The main relationship is one KPI definition to many KPI facts. Each fact can have one active exception note. Raw source records can produce several KPI facts because one source row may contain multiple approved measurements.

Workflow Statuses and Ownership

A KPI is not rejected merely because performance is poor. Warning and Critical are valid performance results. Rejection applies only to invalid data, unapproved sources, malformed inputs, or failed validation.

Threshold changes require human authorization. The strategy director and relevant KPI owner agree on the change, document its effective date, and update the KPI definition. Historical targets are preserved in existing facts rather than recalculated retroactively unless a documented restatement is required.

Step-by-Step Implementation

Step 1: Prepare the Accounts and Permissions

1. Create or designate a Google Workspace automation account. It needs Gmail, Google Sheets, Apps Script, and Drive access. Avoid using an individual employee’s account if the process must continue through staffing changes.
2. Create a restricted Shared Drive or controlled Drive folder for executive reporting. Grant edit access to the strategy analyst and automation account. Grant report viewers only the access required for the published dashboard.
3. Create a subfolder named KPI Source Archive. Copy its folder ID from the URL and use it as YOUR_FOLDER_ID in the script.
4. Create a central Google Sheets workbook named Executive KPI Control Workbook. Store it in the restricted reporting folder.
5. Create a separate planning-source spreadsheet or identify the existing approved one. The automation account needs viewer access. The planning sheet must use the documented headers.
6. Configure source systems to send scheduled CSV exports to the automation mailbox. Where scheduled delivery is unavailable, a source owner can send the export manually using the same controlled filename and headers.
7. Create Gmail labels named KPI/Finance, KPI/Sales, and KPI/Operations.
8. Create Gmail filters based on the approved sender and attachment filename. A filter routes the message to the relevant label. Gmail interface labels can change, so configure the underlying sender, attachment, and label behavior rather than relying on a specific menu description.
9. Identify one primary and one backup owner for every KPI.
10. Create at least two test users: one KPI owner and one executive viewer. Do not test permission boundaries only with administrator accounts.

The automation account requires permission to read labeled Gmail messages, send Gmail messages, read and write the control workbook, read the planning spreadsheet, create archive files in the configured Drive folder, and create time-based triggers.

No API credential is required for the core implementation because Apps Script uses the executing Google account’s authorized services. The optional AI enhancement requires a separate API key stored in Script Properties.

Step 2: Build the Intake

The intake consists of three email-based CSV feeds and one Google Sheets feed.

Dates should use ISO format such as 2026-07-12. Date-time values should include a timezone when the source supports it. Percentages can be supplied as decimals such as 0.31 or formatted values such as 31%; the script normalizes both.

Every source record requires a stable source_record_id. If a source owner corrects a previously exported value, the source should issue a new record ID or a later source_updated_at value. The later timestamp determines which value is retained for the KPI period.

Duplicate prevention uses the Gmail message ID, attachment name, and SHA-256 content hash. Fact creation uses a second idempotency control based on KPI ID and period date.

Incomplete attachments are rejected before raw loading. Invalid individual values are logged and excluded from fact creation. This distinction prevents one malformed KPI from necessarily blocking every valid value in the same source file.

Step 3: Create the System of Record

Open the central workbook, select the Apps Script editor from the spreadsheet’s extension tools, and create a bound script project. Paste the complete core script from the Code and Configuration section into a file named Code.gs.

Run setupWorkbook once. It creates the following worksheets with fixed headers:

• KPI_Definitions
• Finance_Raw
• Sales_Raw
• Operations_Raw
• Planning_Raw
• KPI_Facts
• KPI_Current
• Exception_Notes
• Processed_Imports
• Data_Quality
• Automation_Log
• Alert_History

The setup function also inserts representative KPI definitions. Replace owner email addresses, backup addresses, thresholds, targets, source links, and valid ranges with approved business values.

Do not rename raw fields without updating both the export and the script configuration. The script intentionally treats a header change as a controlled integration change rather than guessing which column replaced it.

Protect generated sheets so ordinary users cannot edit them. KPI owners should edit only the permitted cells in Exception_Notes. Restrict KPI_Definitions to the strategy director, finance manager where appropriate, and the technical owner.

Create filter views for active KPIs, open data-quality issues, current exceptions, stale sources, and failed automation runs. Filter-view interface labels can vary, but the required outcome is a saved, non-destructive filter rather than a shared sort that disrupts other users.

Step 4: Connect the Tools

Looker Studio connects directly to the central workbook. Create one data source for KPI_Current and a second for KPI_Facts. Confirm that dates are interpreted as dates and that actual, target, and variance fields are numeric.

Set the Google Sheets data-freshness option to a cadence appropriate for the business and supported by the connector. The exact configuration label can vary. Updating a sheet does not guarantee that every open browser session redraws immediately, so display the source update time and dashboard query context to users.

Step 5: Build the Core Automation

The core automation runs in this exact order:

1. Acquire a script lock to prevent overlapping executions.
2. Create an automation run record.
3. Validate configuration placeholders.
4. Read each approved Gmail label.
5. Validate sender, filename, content hash, and CSV headers.
6. Archive valid attachments in Drive.
7. Append unprocessed rows to the relevant raw worksheet.
8. Read new records from the planning spreadsheet.
9. Validate dates and numeric values.
10. Map raw fields to active KPI definitions.
11. Calculate target variance and threshold status.
12. Upsert facts by KPI ID and period date.
13. Merge controlled exception notes.
14. Build the current KPI output.
15. Send source-health alerts or the weekly executive summary.
16. Update the run log and release the lock.

• Trigger: Daily time-based trigger, plus a Monday weekly-summary trigger.
• Conditions: Approved configuration, expected sender, matching filename, required headers, unprocessed attachment hash, valid dates, and valid numeric ranges.
• Actions: Archive, import, validate, transform, upsert, calculate status, refresh current output, and notify.
• Fields updated: Raw values, import metadata, facts, current status, logs, alert history, and note joins.
• Notification: Daily stale-data messages and weekly exception summaries.
• Exception: Invalid data is logged and isolated. Unexpected failures mark the run as Failed or Partial.

The script does not delete a historical fact merely because a source is unavailable. The prior value remains in history, while the current view marks it Stale when its permitted age is exceeded.

Users recover a failed record by correcting the source export and resending it, fixing the planning row, restoring permissions, or updating the controlled configuration. They then run the refresh manually or wait for the next trigger.

Step 6: Add Approvals, Reminders, and Escalations

This reporting process does not need a payment-style approval workflow. The source application remains authoritative, and poor KPI performance is not rejected. The relevant human control is acknowledgement and explanation.

Exception notes use review statuses such as DRAFT, READY, and ACKNOWLEDGED. The status does not change the underlying metric. It records whether the owner’s explanation has been reviewed.

A returned item is corrected at the source. A threshold dispute is escalated to the strategy director and KPI owner. The automation never changes a threshold based on an owner’s explanation.

Step 7: Add Documents and File Management

The reporting folder uses this structure:

• Executive KPI Reporting
  • Control Workbook
  • KPI Source Archive
  • KPI Dictionary and Definitions
  • Implementation and Recovery Procedures
  • Archived Reporting Packs, if retained

The script archives each processed CSV using a filename containing the source, import date, content-hash prefix, and original filename. Before creating a file, it searches for the deterministic archive filename. This reduces duplicate files if an execution is retried.

Archive permissions should be narrower than dashboard permissions because source files may contain supporting values that are not intended for every dashboard viewer. The dashboard should expose approved KPI aggregates and controlled drill-down links, not an unrestricted archive folder.

Google Drive version history applies to the control workbook and documentation. Source CSVs are treated as immutable evidence. A corrected export should be saved as a new file with a distinct hash rather than replacing an archived file.

Set retention according to accounting, contractual, privacy, and regulatory requirements. The script does not implement legal retention policy by itself.

Step 8: Add Reporting and Operational Views

Create the following Looker Studio pages and Google Sheets operational views:

The executive page should show no more than the approved KPI set. Use a compact table with actual, target, status, owner, source date, exception note, and drill-down link. Add trend charts only where history aids interpretation.

Use conditional formatting for presentation, but do not calculate business status only through dashboard color rules. The authoritative status must already exist in KPI_Current.

The dashboard owner reviews field types and row counts after changes. Alert thresholds are controlled in the workbook, not duplicated in Looker Studio.

Step 9: Add Security and Governance Controls

• Use a dedicated automation account or documented shared operational identity.
• Restrict raw worksheets, archive files, and financial drill-down links.
• Protect generated columns and worksheets against normal editing.
• Limit KPI-definition editing to approved administrators.
• Store API secrets only in Script Properties, never in cells or source code.
• Review Apps Script executions and Google Workspace activity logs according to available administrative capabilities.
• Remove former employees from source files, dashboard sharing, distribution lists, and backup-owner assignments.
• Disable public and unrestricted link sharing.
• Keep personally identifiable transaction data out of executive KPI exports unless it is necessary and approved.
• Back up configuration, code, and the KPI dictionary before structural changes.
• Document the source formula for every KPI, including inclusions, exclusions, timezone, currency, and reporting period.
• Require human approval for threshold changes and historical restatements.
• Do not send sensitive source records to an AI service without a separate data-protection review.

The reporting layer should receive only the fields needed for the defined KPIs. Exporting entire customer, employee, or invoice tables would create unnecessary privacy and security exposure.

Step 10: Deploy and Test

1. Build the workbook and script in a non-production copy.
2. Replace configuration placeholders with test mailbox addresses, folder IDs, spreadsheet IDs, and report URLs.
3. Run setupWorkbook and authorize the requested Google services.
4. Run loadSampleDataForTest. This function clears the raw test worksheets, so never run it against production data.
5. Run runLocalValidationTest and inspect KPI_Facts, KPI_Current, Data_Quality, and Automation_Log.
6. Send one test CSV to each Gmail label and run runDailyRefresh manually.
7. Confirm that each attachment is archived only once and that a repeated run does not create duplicate facts.
8. Connect a test Looker Studio report to the test workbook.
9. Complete user acceptance testing with the strategy analyst, one department owner, and one executive viewer.
10. Compare two reporting cycles with the manually prepared report.
11. Correct source mappings and definitions before activating triggers.
12. Run installTriggers from the production script project.
13. Communicate the dashboard purpose, refresh timing, exception-note process, and support owner.
14. Retain the prior manual workbook for a defined rollback period without continuing two uncontrolled reporting processes indefinitely.

Apps Script time-based triggers run within a scheduling window rather than at an exact second. Configure the project timezone and allow sufficient time between the daily refresh and the leadership meeting.

Code and Configuration

The core implementation requires Apps Script because the native Google Sheets and Looker Studio connections do not provide the required Gmail attachment processing, content hashing, validation, idempotent fact upserts, Drive archiving, and scheduled exception messages.

Place the following complete script in the Apps Script project bound to the central control workbook. Replace all values beginning with YOUR_. Review the representative KPI thresholds before using them.

const KPI_SETTINGS = Object.freeze({
  TIME_ZONE: 'Etc/UTC',
  DASHBOARD_URL: 'YOUR_LOOKER_STUDIO_REPORT_URL',
  SOURCE_SPREADSHEET_ID: 'YOUR_SOURCE_SPREADSHEET_ID',
  SOURCE_SPREADSHEET_TAB: 'Weekly_Planning',
  ARCHIVE_FOLDER_ID: 'YOUR_FOLDER_ID',
  EXECUTIVE_RECIPIENTS: [
    'YOUR_EXECUTIVE_EMAIL_ADDRESS'
  ],
  ADMIN_EMAIL: 'YOUR_ADMIN_EMAIL_ADDRESS',
  GMAIL_LOOKBACK_DAYS: 21,
  MAX_GMAIL_THREADS: 100
});

const EMAIL_SOURCES = [
  {
    name: 'Finance',
    labelName: 'KPI/Finance',
    sheetName: 'Finance_Raw',
    allowedSender: 'YOUR_FINANCE_EXPORT_EMAIL',
    filePattern: /^finance_kpi_.*\.csv$/i,
    headers: [
      'as_of_date',
      'revenue_mtd',
      'gross_margin_pct',
      'ar_over_30',
      'source_record_id',
      'source_updated_at'
    ]
  },
  {
    name: 'Sales',
    labelName: 'KPI/Sales',
    sheetName: 'Sales_Raw',
    allowedSender: 'YOUR_SALES_EXPORT_EMAIL',
    filePattern: /^sales_kpi_.*\.csv$/i,
    headers: [
      'as_of_date',
      'qualified_pipeline',
      'win_rate_pct',
      'source_record_id',
      'source_updated_at'
    ]
  },
  {
    name: 'Operations',
    labelName: 'KPI/Operations',
    sheetName: 'Operations_Raw',
    allowedSender: 'YOUR_OPERATIONS_EXPORT_EMAIL',
    filePattern: /^operations_kpi_.*\.csv$/i,
    headers: [
      'as_of_date',
      'on_time_completion_pct',
      'utilization_pct',
      'open_exceptions',
      'source_record_id',
      'source_updated_at'
    ]
  }
];

const PLANNING_HEADERS = [
  'as_of_date',
  'forecast_variance_pct',
  'source_record_id',
  'source_updated_at'
];

const KPI_DEFINITION_HEADERS = [
  'KPI_ID',
  'KPI_Name',
  'Function',
  'Owner_Name',
  'Owner_Email',
  'Owner_Backup_Email',
  'Source_Tab',
  'Source_Field',
  'Format',
  'Direction',
  'Target_Value',
  'Warning_Low',
  'Critical_Low',
  'Warning_High',
  'Critical_High',
  'Valid_Min',
  'Valid_Max',
  'Active',
  'Max_Age_Hours',
  'Drilldown_URL',
  'Notes'
];

const KPI_FACT_HEADERS = [
  'Fact_Key',
  'KPI_ID',
  'KPI_Name',
  'Function',
  'Format',
  'Period_End',
  'Actual_Value',
  'Target_Value',
  'Variance',
  'Threshold_Status',
  'Workflow_Status',
  'Source_Name',
  'Source_Record_ID',
  'Source_Updated_At',
  'Owner_Name',
  'Owner_Email',
  'Exception_Note',
  'Drilldown_URL',
  'Created_At',
  'Updated_At',
  'Automation_Run_ID'
];

const EXCEPTION_NOTE_HEADERS = [
  'Fact_Key',
  'KPI_ID',
  'Period_End',
  'Exception_Note',
  'Note_Owner_Email',
  'Note_Updated_At',
  'Review_Status'
];

const PROCESSED_IMPORT_HEADERS = [
  'Import_Key',
  'Source',
  'Gmail_Message_ID',
  'Attachment_Name',
  'Content_SHA256',
  'Archived_File_URL',
  'Imported_At',
  'Row_Count',
  'Status'
];

const DATA_QUALITY_HEADERS = [
  'Issue_ID',
  'Run_ID',
  'Source',
  'Source_Record_ID',
  'Field',
  'Severity',
  'Issue',
  'Detected_At',
  'Status',
  'Resolved_At'
];

const AUTOMATION_LOG_HEADERS = [
  'Run_ID',
  'Started_At',
  'Ended_At',
  'Status',
  'Imported_Rows',
  'Facts_Upserted',
  'Validation_Issues',
  'Emails_Sent',
  'Error_Message',
  'Execution_User'
];

const ALERT_HISTORY_HEADERS = [
  'Alert_Key',
  'KPI_ID',
  'Threshold_Status',
  'Sent_To',
  'Sent_At',
  'Run_ID'
];

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('KPI Automation')
    .addItem('Set up workbook', 'setupWorkbook')
    .addItem('Run daily refresh', 'runDailyRefresh')
    .addItem('Run weekly refresh and email', 'runWeeklyRefreshAndNotify')
    .addItem('Load sample test data', 'loadSampleDataForTest')
    .addItem('Run local validation test', 'runLocalValidationTest')
    .addSeparator()
    .addItem('Install triggers', 'installTriggers')
    .addItem('Remove triggers', 'removeTriggers')
    .addToUi();
}

function setupWorkbook() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  ensureSheet_(ss, 'KPI_Definitions', KPI_DEFINITION_HEADERS);

  EMAIL_SOURCES.forEach(function(source) {
    ensureSheet_(
      ss,
      source.sheetName,
      source.headers.concat(['import_id', 'imported_at'])
    );
  });

  ensureSheet_(
    ss,
    'Planning_Raw',
    PLANNING_HEADERS.concat(['import_id', 'imported_at'])
  );

  ensureSheet_(ss, 'KPI_Facts', KPI_FACT_HEADERS);
  ensureSheet_(ss, 'KPI_Current', KPI_FACT_HEADERS);
  ensureSheet_(ss, 'Exception_Notes', EXCEPTION_NOTE_HEADERS);
  ensureSheet_(ss, 'Processed_Imports', PROCESSED_IMPORT_HEADERS);
  ensureSheet_(ss, 'Data_Quality', DATA_QUALITY_HEADERS);
  ensureSheet_(ss, 'Automation_Log', AUTOMATION_LOG_HEADERS);
  ensureSheet_(ss, 'Alert_History', ALERT_HISTORY_HEADERS);

  insertDefaultDefinitions_();

  ss.getSheets().forEach(function(sheet) {
    sheet.setFrozenRows(1);
  });

  SpreadsheetApp.flush();
  console.log('Workbook setup completed.');
}

function insertDefaultDefinitions_() {
  const sheet = getSheet_('KPI_Definitions');

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

  const rows = [
    [
      'FIN_REV_MTD', 'Revenue MTD', 'Finance', 'Finance Manager',
      'YOUR_FINANCE_OWNER_EMAIL', 'YOUR_BACKUP_EMAIL',
      'Finance_Raw', 'revenue_mtd', 'CURRENCY', 'HIGHER_IS_BETTER',
      500000, 450000, 400000, '', '', 0, '', true, 36,
      'YOUR_FINANCE_DRILLDOWN_URL', 'Month-to-date revenue'
    ],
    [
      'FIN_GM_PCT', 'Gross Margin %', 'Finance', 'Finance Manager',
      'YOUR_FINANCE_OWNER_EMAIL', 'YOUR_BACKUP_EMAIL',
      'Finance_Raw', 'gross_margin_pct', 'PERCENT', 'HIGHER_IS_BETTER',
      0.32, 0.29, 0.27, '', '', 0, 1, true, 36,
      'YOUR_FINANCE_DRILLDOWN_URL', 'Gross margin as a decimal'
    ],
    [
      'FIN_AR30', 'Receivables Over 30 Days', 'Finance', 'Finance Manager',
      'YOUR_FINANCE_OWNER_EMAIL', 'YOUR_BACKUP_EMAIL',
      'Finance_Raw', 'ar_over_30', 'CURRENCY', 'LOWER_IS_BETTER',
      120000, '', '', 150000, 200000, 0, '', true, 36,
      'YOUR_FINANCE_DRILLDOWN_URL', 'Open receivables more than 30 days old'
    ],
    [
      'SAL_PIPELINE', 'Qualified Pipeline', 'Sales', 'Sales Director',
      'YOUR_SALES_OWNER_EMAIL', 'YOUR_BACKUP_EMAIL',
      'Sales_Raw', 'qualified_pipeline', 'CURRENCY', 'HIGHER_IS_BETTER',
      1800000, 1500000, 1200000, '', '', 0, '', true, 36,
      'YOUR_SALES_DRILLDOWN_URL', 'Qualified open pipeline'
    ],
    [
      'SAL_WIN_RATE', 'Win Rate', 'Sales', 'Sales Director',
      'YOUR_SALES_OWNER_EMAIL', 'YOUR_BACKUP_EMAIL',
      'Sales_Raw', 'win_rate_pct', 'PERCENT', 'HIGHER_IS_BETTER',
      0.30, 0.25, 0.20, '', '', 0, 1, true, 36,
      'YOUR_SALES_DRILLDOWN_URL', 'Rolling approved win-rate definition'
    ],
    [
      'OPS_ON_TIME', 'On-Time Completion', 'Operations', 'Operations Manager',
      'YOUR_OPERATIONS_OWNER_EMAIL', 'YOUR_BACKUP_EMAIL',
      'Operations_Raw', 'on_time_completion_pct', 'PERCENT',
      'HIGHER_IS_BETTER',
      0.94, 0.90, 0.84, '', '', 0, 1, true, 36,
      'YOUR_OPERATIONS_DRILLDOWN_URL', 'Completed jobs delivered on time'
    ],
    [
      'OPS_UTIL', 'Resource Utilization', 'Operations', 'Operations Manager',
      'YOUR_OPERATIONS_OWNER_EMAIL', 'YOUR_BACKUP_EMAIL',
      'Operations_Raw', 'utilization_pct', 'PERCENT', 'BETWEEN',
      0.82, 0.72, 0.65, 0.92, 0.97, 0, 1.25, true, 36,
      'YOUR_OPERATIONS_DRILLDOWN_URL', 'Productive capacity utilization'
    ],
    [
      'OPS_EXCEPTIONS', 'Open Operational Exceptions', 'Operations',
      'Operations Manager', 'YOUR_OPERATIONS_OWNER_EMAIL',
      'YOUR_BACKUP_EMAIL', 'Operations_Raw', 'open_exceptions', 'NUMBER',
      'LOWER_IS_BETTER', 5, '', '', 12, 20, 0, '', true, 36,
      'YOUR_OPERATIONS_DRILLDOWN_URL', 'Unresolved operational exceptions'
    ],
    [
      'STR_FORECAST_VAR', 'Forecast Variance', 'Strategy', 'Strategy Analyst',
      'YOUR_STRATEGY_OWNER_EMAIL', 'YOUR_BACKUP_EMAIL',
      'Planning_Raw', 'forecast_variance_pct', 'PERCENT',
      'LOWER_IS_BETTER', 0.05, '', '', 0.08, 0.12, 0, 1, true, 168,
      'YOUR_PLANNING_DRILLDOWN_URL', 'Absolute forecast variance'
    ]
  ];

  appendRows_(sheet, rows);
}

function runDailyRefresh() {
  runRefresh_(false);
}

function runWeeklyRefreshAndNotify() {
  runRefresh_(true);
}

function runRefresh_(sendExecutiveSummary) {
  const lock = LockService.getScriptLock();

  if (!lock.tryLock(30000)) {
    throw new Error('Another KPI automation run is already active.');
  }

  const runId = 'RUN-' + Utilities.getUuid();
  const stats = {
    importedRows: 0,
    factsUpserted: 0,
    validationIssues: 0,
    emailsSent: 0
  };
  const sourceErrors = [];

  try {
    validateConfiguration_();
    startRun_(runId);

    EMAIL_SOURCES.forEach(function(source) {
      try {
        stats.importedRows += importGmailSource_(source, runId);
      } catch (error) {
        sourceErrors.push(source.name + ': ' + error.message);
        recordIssue_(
          runId,
          source.name,
          '',
          'Source import',
          'ERROR',
          error.message
        );
      }
    });

    try {
      stats.importedRows += importPlanningSource_(runId);
    } catch (error) {
      sourceErrors.push('Planning: ' + error.message);
      recordIssue_(
        runId,
        'Planning',
        '',
        'Source import',
        'ERROR',
        error.message
      );
    }

    stats.factsUpserted = buildFacts_(runId);
    refreshCurrentView_(runId);
    stats.validationIssues = countRunIssues_(runId);

    if (sendExecutiveSummary) {
      stats.emailsSent += sendExceptionSummary_(runId);
    } else {
      stats.emailsSent += sendSourceHealthAlerts_(runId);
    }

    const status = sourceErrors.length ? 'PARTIAL' : 'SUCCESS';
    finishRun_(
      runId,
      status,
      stats,
      sourceErrors.join(' | ')
    );

    console.log(JSON.stringify({
      runId: runId,
      status: status,
      stats: stats,
      sourceErrors: sourceErrors
    }));

    return {
      runId: runId,
      status: status,
      stats: stats,
      sourceErrors: sourceErrors
    };
  } catch (error) {
    try {
      if (!runExists_(runId)) {
        startRun_(runId);
      }

      stats.validationIssues = countRunIssues_(runId);
      finishRun_(runId, 'FAILED', stats, error.stack || error.message);
    } catch (loggingError) {
      console.error('Unable to update failure log: ' + loggingError.message);
    }

    notifyAdministratorFailure_(runId, error);
    console.error(error.stack || error.message);
    throw error;
  } finally {
    lock.releaseLock();
  }
}

function importGmailSource_(source, runId) {
  const label = GmailApp.getUserLabelByName(source.labelName);

  if (!label) {
    throw new Error('Required Gmail label not found: ' + source.labelName);
  }

  const processed = new Set(
    getObjects_('Processed_Imports').map(function(row) {
      return String(row.Import_Key);
    })
  );

  const cutoff = new Date(
    Date.now() - KPI_SETTINGS.GMAIL_LOOKBACK_DAYS * 24 * 60 * 60 * 1000
  );

  const messages = [];

  label
    .getThreads(0, KPI_SETTINGS.MAX_GMAIL_THREADS)
    .forEach(function(thread) {
      thread.getMessages().forEach(function(message) {
        if (message.getDate() >= cutoff) {
          messages.push(message);
        }
      });
    });

  messages.sort(function(a, b) {
    return a.getDate().getTime() - b.getDate().getTime();
  });

  let importedRows = 0;

  messages.forEach(function(message) {
    const sender = extractEmailAddress_(message.getFrom());

    if (sender !== source.allowedSender.toLowerCase()) {
      return;
    }

    message.getAttachments({
      includeInlineImages: false,
      includeAttachments: true
    }).forEach(function(attachment) {
      const attachmentName = attachment.getName() || '';

      if (!source.filePattern.test(attachmentName)) {
        return;
      }

      const digestBytes = Utilities.computeDigest(
        Utilities.DigestAlgorithm.SHA_256,
        attachment.getBytes()
      );
      const digest = Utilities.base64EncodeWebSafe(digestBytes);
      const importKey = [
        source.name,
        message.getId(),
        attachmentName,
        digest
      ].join('|');

      if (processed.has(importKey)) {
        return;
      }

      const csvText = attachment.getDataAsString('UTF-8');
      const parsedRows = parseAndValidateCsv_(
        csvText,
        source.headers,
        source.name
      );

      const archivedUrl = archiveAttachment_(
        source.name,
        attachment,
        digest
      );

      const importId = 'IMP-' + Utilities.getUuid();
      const importedAt = new Date();

      const destinationRows = parsedRows.map(function(rowObject) {
        return source.headers.map(function(header) {
          return rowObject[header];
        }).concat([importId, importedAt]);
      });

      appendRows_(getSheet_(source.sheetName), destinationRows);

      appendRows_(getSheet_('Processed_Imports'), [[
        importKey,
        source.name,
        message.getId(),
        attachmentName,
        digest,
        archivedUrl,
        importedAt,
        destinationRows.length,
        'IMPORTED'
      ]]);

      processed.add(importKey);
      importedRows += destinationRows.length;
    });
  });

  return importedRows;
}

function importPlanningSource_(runId) {
  const sourceSpreadsheet = SpreadsheetApp.openById(
    KPI_SETTINGS.SOURCE_SPREADSHEET_ID
  );
  const sourceSheet = sourceSpreadsheet.getSheetByName(
    KPI_SETTINGS.SOURCE_SPREADSHEET_TAB
  );

  if (!sourceSheet) {
    throw new Error(
      'Planning source tab not found: ' +
      KPI_SETTINGS.SOURCE_SPREADSHEET_TAB
    );
  }

  const values = sourceSheet.getDataRange().getValues();

  if (values.length < 2) {
    return 0;
  }

  const sourceHeaderMap = createHeaderMap_(values[0]);

  PLANNING_HEADERS.forEach(function(header) {
    if (sourceHeaderMap[header] === undefined) {
      throw new Error('Missing planning header: ' + header);
    }
  });

  const destinationSheet = getSheet_('Planning_Raw');
  const existingIds = new Set(
    getObjects_('Planning_Raw').map(function(row) {
      return String(row.source_record_id);
    })
  );

  const importId = 'PLAN-' + Utilities.getUuid();
  const importedAt = new Date();
  const output = [];

  values.slice(1).forEach(function(row) {
    const sourceRecordId = String(
      row[sourceHeaderMap.source_record_id] || ''
    ).trim();

    if (!sourceRecordId || existingIds.has(sourceRecordId)) {
      return;
    }

    const outputRow = PLANNING_HEADERS.map(function(header) {
      return row[sourceHeaderMap[header]];
    }).concat([importId, importedAt]);

    output.push(outputRow);
    existingIds.add(sourceRecordId);
  });

  appendRows_(destinationSheet, output);
  return output.length;
}

function parseAndValidateCsv_(csvText, requiredHeaders, sourceName) {
  const parsed = Utilities.parseCsv(csvText);

  if (!parsed.length) {
    throw new Error(sourceName + ' CSV is empty.');
  }

  const headerMap = createHeaderMap_(parsed[0]);

  requiredHeaders.forEach(function(header) {
    if (headerMap[header] === undefined) {
      throw new Error(
        sourceName + ' CSV is missing required header: ' + header
      );
    }
  });

  const rows = [];

  parsed.slice(1).forEach(function(row) {
    const hasData = row.some(function(value) {
      return String(value).trim() !== '';
    });

    if (!hasData) {
      return;
    }

    const object = {};

    requiredHeaders.forEach(function(header) {
      object[header] = row[headerMap[header]];
    });

    rows.push(object);
  });

  if (!rows.length) {
    throw new Error(sourceName + ' CSV contains no data rows.');
  }

  return rows;
}

function archiveAttachment_(sourceName, attachment, digest) {
  const folder = DriveApp.getFolderById(KPI_SETTINGS.ARCHIVE_FOLDER_ID);
  const originalName = sanitizeFilename_(attachment.getName() || 'source.csv');
  const datePart = Utilities.formatDate(
    new Date(),
    KPI_SETTINGS.TIME_ZONE,
    'yyyyMMdd'
  );
  const archiveName = [
    sourceName,
    datePart,
    digest.substring(0, 12),
    originalName
  ].join('_');

  const existingFiles = folder.getFilesByName(archiveName);

  if (existingFiles.hasNext()) {
    return existingFiles.next().getUrl();
  }

  const file = folder
    .createFile(attachment.copyBlob())
    .setName(archiveName);

  return file.getUrl();
}

function buildFacts_(runId) {
  const definitions = getObjects_('KPI_Definitions').filter(function(def) {
    return isTrue_(def.Active);
  });

  validateDefinitionUniqueness_(definitions, runId);

  const existingFacts = getObjects_('KPI_Facts');
  const factMap = new Map();

  existingFacts.forEach(function(fact) {
    if (fact.Fact_Key) {
      factMap.set(String(fact.Fact_Key), fact);
    }
  });

  const noteMap = getExceptionNoteMap_(runId);
  const rawCache = {};
  let upserted = 0;

  definitions.forEach(function(def) {
    const sourceTab = String(def.Source_Tab);
    const sourceField = String(def.Source_Field);

    if (!rawCache[sourceTab]) {
      rawCache[sourceTab] = getObjects_(sourceTab);
    }

    const rawRows = rawCache[sourceTab];

    if (!rawRows.length) {
      recordIssue_(
        runId,
        sourceTab,
        '',
        sourceField,
        'ERROR',
        'No raw source rows are available for active KPI ' + def.KPI_ID
      );
      return;
    }

    rawRows.forEach(function(raw) {
      const periodEnd = parseDate_(raw.as_of_date);
      const sourceRecordId = String(
        raw.source_record_id || raw.import_id || ''
      ).trim();
      const sourceUpdatedAt =
        parseDate_(raw.source_updated_at) ||
        parseDate_(raw.imported_at) ||
        new Date();
      const actualValue = parseNumber_(raw[sourceField]);

      if (!periodEnd) {
        recordIssue_(
          runId,
          sourceTab,
          sourceRecordId,
          'as_of_date',
          'ERROR',
          'Invalid or missing period date for KPI ' + def.KPI_ID
        );
        return;
      }

      if (actualValue === null) {
        recordIssue_(
          runId,
          sourceTab,
          sourceRecordId,
          sourceField,
          'ERROR',
          'Missing or non-numeric value for KPI ' + def.KPI_ID
        );
        return;
      }

      const validMin = parseNumber_(def.Valid_Min);
      const validMax = parseNumber_(def.Valid_Max);

      if (validMin !== null && actualValue < validMin) {
        recordIssue_(
          runId,
          sourceTab,
          sourceRecordId,
          sourceField,
          'ERROR',
          'Value is below configured valid minimum for KPI ' + def.KPI_ID
        );
        return;
      }

      if (validMax !== null && actualValue > validMax) {
        recordIssue_(
          runId,
          sourceTab,
          sourceRecordId,
          sourceField,
          'ERROR',
          'Value is above configured valid maximum for KPI ' + def.KPI_ID
        );
        return;
      }

      const periodKey = Utilities.formatDate(
        periodEnd,
        KPI_SETTINGS.TIME_ZONE,
        'yyyy-MM-dd'
      );
      const factKey = String(def.KPI_ID) + '|' + periodKey;
      const existing = factMap.get(factKey);
      const existingUpdated = existing
        ? parseDate_(existing.Source_Updated_At)
        : null;

      if (
        existingUpdated &&
        sourceUpdatedAt.getTime() < existingUpdated.getTime()
      ) {
        return;
      }

      const targetValue = parseNumber_(def.Target_Value);
      const thresholdStatus = evaluateThreshold_(actualValue, def);
      const workflowStatus =
        thresholdStatus === 'On Track' ? 'READY' : 'REVIEW_REQUIRED';
      const now = new Date();
      const note = noteMap.get(factKey);

      const fact = {
        Fact_Key: factKey,
        KPI_ID: String(def.KPI_ID),
        KPI_Name: String(def.KPI_Name),
        Function: String(def.Function),
        Format: String(def.Format),
        Period_End: periodEnd,
        Actual_Value: actualValue,
        Target_Value: targetValue,
        Variance:
          targetValue === null ? '' : actualValue - targetValue,
        Threshold_Status: thresholdStatus,
        Workflow_Status: workflowStatus,
        Source_Name: sourceTab,
        Source_Record_ID: sourceRecordId,
        Source_Updated_At: sourceUpdatedAt,
        Owner_Name: String(def.Owner_Name),
        Owner_Email: String(def.Owner_Email),
        Exception_Note: note ? note.Exception_Note : '',
        Drilldown_URL: String(def.Drilldown_URL || ''),
        Created_At:
          existing && existing.Created_At
            ? existing.Created_At
            : now,
        Updated_At: now,
        Automation_Run_ID: runId
      };

      factMap.set(factKey, fact);
      upserted += 1;
    });
  });

  const facts = Array.from(factMap.values());

  facts.sort(function(a, b) {
    const dateDifference =
      parseDate_(a.Period_End).getTime() -
      parseDate_(b.Period_End).getTime();

    if (dateDifference !== 0) {
      return dateDifference;
    }

    return String(a.KPI_ID).localeCompare(String(b.KPI_ID));
  });

  rewriteObjects_('KPI_Facts', KPI_FACT_HEADERS, facts);
  return upserted;
}

function refreshCurrentView_(runId) {
  const definitions = getObjects_('KPI_Definitions').filter(function(def) {
    return isTrue_(def.Active);
  });
  const facts = getObjects_('KPI_Facts');
  const now = new Date();
  const currentRows = [];

  definitions.forEach(function(def) {
    const matchingFacts = facts
      .filter(function(fact) {
        return String(fact.KPI_ID) === String(def.KPI_ID);
      })
      .sort(function(a, b) {
        return (
          parseDate_(b.Period_End).getTime() -
          parseDate_(a.Period_End).getTime()
        );
      });

    if (!matchingFacts.length) {
      currentRows.push({
        Fact_Key: String(def.KPI_ID) + '|MISSING',
        KPI_ID: String(def.KPI_ID),
        KPI_Name: String(def.KPI_Name),
        Function: String(def.Function),
        Format: String(def.Format),
        Period_End: '',
        Actual_Value: '',
        Target_Value: parseNumber_(def.Target_Value),
        Variance: '',
        Threshold_Status: 'Data Missing',
        Workflow_Status: 'DATA_EXCEPTION',
        Source_Name: String(def.Source_Tab),
        Source_Record_ID: '',
        Source_Updated_At: '',
        Owner_Name: String(def.Owner_Name),
        Owner_Email: String(def.Owner_Email),
        Exception_Note: '',
        Drilldown_URL: String(def.Drilldown_URL || ''),
        Created_At: '',
        Updated_At: now,
        Automation_Run_ID: runId
      });
      return;
    }

    const latest = Object.assign({}, matchingFacts[0]);
    const sourceUpdatedAt = parseDate_(latest.Source_Updated_At);
    const maxAgeHours = parseNumber_(def.Max_Age_Hours);

    if (
      sourceUpdatedAt &&
      maxAgeHours !== null &&
      now.getTime() - sourceUpdatedAt.getTime() >
        maxAgeHours * 60 * 60 * 1000
    ) {
      latest.Threshold_Status = 'Stale';
      latest.Workflow_Status = 'DATA_EXCEPTION';
    }

    currentRows.push(latest);
  });

  currentRows.sort(function(a, b) {
    const functionCompare = String(a.Function).localeCompare(
      String(b.Function)
    );

    if (functionCompare !== 0) {
      return functionCompare;
    }

    return String(a.KPI_Name).localeCompare(String(b.KPI_Name));
  });

  rewriteObjects_('KPI_Current', KPI_FACT_HEADERS, currentRows);
}

function evaluateThreshold_(value, def) {
  const direction = String(def.Direction).trim().toUpperCase();
  const warningLow = parseNumber_(def.Warning_Low);
  const criticalLow = parseNumber_(def.Critical_Low);
  const warningHigh = parseNumber_(def.Warning_High);
  const criticalHigh = parseNumber_(def.Critical_High);

  if (direction === 'HIGHER_IS_BETTER') {
    if (criticalLow === null || warningLow === null) {
      return 'Data Error';
    }
    if (value < criticalLow) {
      return 'Critical';
    }
    if (value < warningLow) {
      return 'Warning';
    }
    return 'On Track';
  }

  if (direction === 'LOWER_IS_BETTER') {
    if (criticalHigh === null || warningHigh === null) {
      return 'Data Error';
    }
    if (value > criticalHigh) {
      return 'Critical';
    }
    if (value > warningHigh) {
      return 'Warning';
    }
    return 'On Track';
  }

  if (direction === 'BETWEEN') {
    if (
      criticalLow === null ||
      warningLow === null ||
      warningHigh === null ||
      criticalHigh === null
    ) {
      return 'Data Error';
    }

    if (value < criticalLow || value > criticalHigh) {
      return 'Critical';
    }

    if (value < warningLow || value > warningHigh) {
      return 'Warning';
    }

    return 'On Track';
  }

  return 'Data Error';
}

function getExceptionNoteMap_(runId) {
  const rows = getObjects_('Exception_Notes');
  const noteMap = new Map();
  const seen = new Set();

  rows.forEach(function(row) {
    const factKey = String(row.Fact_Key || '').trim();

    if (!factKey) {
      return;
    }

    if (seen.has(factKey)) {
      recordIssue_(
        runId,
        'Exception_Notes',
        factKey,
        'Fact_Key',
        'WARNING',
        'Multiple note rows exist for the same fact key. Latest timestamp wins.'
      );
    }

    seen.add(factKey);

    const existing = noteMap.get(factKey);
    const incomingDate =
      parseDate_(row.Note_Updated_At) || new Date(0);
    const existingDate =
      existing && parseDate_(existing.Note_Updated_At)
        ? parseDate_(existing.Note_Updated_At)
        : new Date(0);

    if (!existing || incomingDate.getTime() >= existingDate.getTime()) {
      noteMap.set(factKey, row);
    }
  });

  return noteMap;
}

function sendExceptionSummary_(runId) {
  const currentRows = getObjects_('KPI_Current');
  const issues = getObjects_('Data_Quality').filter(function(issue) {
    return String(issue.Run_ID) === runId;
  });
  const exceptions = currentRows.filter(function(row) {
    return String(row.Threshold_Status) !== 'On Track';
  });

  const executiveRecipients = KPI_SETTINGS.EXECUTIVE_RECIPIENTS.filter(
    validEmail_
  );

  if (!executiveRecipients.length) {
    throw new Error('No valid executive email recipients are configured.');
  }

  const dateLabel = Utilities.formatDate(
    new Date(),
    KPI_SETTINGS.TIME_ZONE,
    'yyyy-MM-dd'
  );
  const subject =
    'Executive KPI exception summary | ' + dateLabel;
  const plainText = buildPlainSummary_(exceptions, issues);
  const htmlBody = buildHtmlSummary_(exceptions, issues);

  GmailApp.sendEmail(
    executiveRecipients.join(','),
    subject,
    plainText,
    {
      htmlBody: htmlBody,
      name: 'Executive KPI Monitor'
    }
  );

  let sentCount = 1;
  const ownerGroups = {};

  exceptions.forEach(function(row) {
    const ownerEmail = String(row.Owner_Email || '').trim();

    if (!validEmail_(ownerEmail)) {
      return;
    }

    if (!ownerGroups[ownerEmail]) {
      ownerGroups[ownerEmail] = [];
    }

    ownerGroups[ownerEmail].push(row);
  });

  Object.keys(ownerGroups).forEach(function(ownerEmail) {
    const ownerRows = ownerGroups[ownerEmail];
    const ownerHtml = buildHtmlSummary_(ownerRows, []);
    const ownerPlain = buildPlainSummary_(ownerRows, []);

    GmailApp.sendEmail(
      ownerEmail,
      'KPI items requiring review | ' + dateLabel,
      ownerPlain,
      {
        htmlBody: ownerHtml,
        name: 'Executive KPI Monitor'
      }
    );

    sentCount += 1;
  });

  return sentCount;
}

function sendSourceHealthAlerts_(runId) {
  const currentRows = getObjects_('KPI_Current');
  const definitions = getObjects_('KPI_Definitions');
  const definitionMap = {};

  definitions.forEach(function(def) {
    definitionMap[String(def.KPI_ID)] = def;
  });

  const today = Utilities.formatDate(
    new Date(),
    KPI_SETTINGS.TIME_ZONE,
    'yyyy-MM-dd'
  );

  const sentKeys = new Set(
    getObjects_('Alert_History').map(function(row) {
      return String(row.Alert_Key);
    })
  );

  let sentCount = 0;

  currentRows.forEach(function(row) {
    const status = String(row.Threshold_Status);

    if (
      status !== 'Stale' &&
      status !== 'Data Missing' &&
      status !== 'Critical'
    ) {
      return;
    }

    const alertKey = [
      today,
      row.KPI_ID,
      status
    ].join('|');

    if (sentKeys.has(alertKey)) {
      return;
    }

    const def = definitionMap[String(row.KPI_ID)] || {};
    const ownerEmail = String(row.Owner_Email || '').trim();
    const backupEmail = String(def.Owner_Backup_Email || '').trim();
    const toEmail = validEmail_(ownerEmail)
      ? ownerEmail
      : KPI_SETTINGS.ADMIN_EMAIL;

    if (!validEmail_(toEmail)) {
      return;
    }

    const options = {
      htmlBody: buildHtmlSummary_([row], []),
      name: 'Executive KPI Monitor'
    };

    if (
      validEmail_(backupEmail) &&
      backupEmail !== toEmail
    ) {
      options.cc = backupEmail;
    }

    GmailApp.sendEmail(
      toEmail,
      'KPI source or critical-status alert | ' + row.KPI_Name,
      buildPlainSummary_([row], []),
      options
    );

    appendRows_(getSheet_('Alert_History'), [[
      alertKey,
      row.KPI_ID,
      status,
      toEmail,
      new Date(),
      runId
    ]]);

    sentKeys.add(alertKey);
    sentCount += 1;
  });

  return sentCount;
}

function buildPlainSummary_(exceptions, issues) {
  const lines = [];

  lines.push('Executive KPI exception summary');
  lines.push('');

  if (!exceptions.length) {
    lines.push('No current KPI threshold exceptions were found.');
  } else {
    exceptions.forEach(function(row) {
      lines.push(
        [
          row.KPI_Name,
          row.Threshold_Status,
          formatKpiValue_(row.Actual_Value, row.Format),
          'Owner: ' + row.Owner_Name,
          row.Exception_Note
            ? 'Note: ' + row.Exception_Note
            : 'Note: not provided'
        ].join(' | ')
      );
    });
  }

  if (issues.length) {
    lines.push('');
    lines.push('Validation issues in this run: ' + issues.length);
  }

  lines.push('');
  lines.push('Dashboard: ' + KPI_SETTINGS.DASHBOARD_URL);

  return lines.join('\n');
}

function buildHtmlSummary_(exceptions, issues) {
  let html = '<p><b>Executive KPI exception summary</b></p>';

  if (!exceptions.length) {
    html += '<p>No current KPI threshold exceptions were found.</p>';
  } else {
    html +=
      '<table border="1" cellpadding="6" cellspacing="0">' +
      '<thead><tr>' +
      '<th>KPI</th>' +
      '<th>Status</th>' +
      '<th>Actual</th>' +
      '<th>Owner</th>' +
      '<th>Exception note</th>' +
      '</tr></thead><tbody>';

    exceptions.forEach(function(row) {
      html +=
        '<tr>' +
        '<td>' + escapeHtml_(row.KPI_Name) + '</td>' +
        '<td>' + escapeHtml_(row.Threshold_Status) + '</td>' +
        '<td>' +
        escapeHtml_(formatKpiValue_(row.Actual_Value, row.Format)) +
        '</td>' +
        '<td>' + escapeHtml_(row.Owner_Name) + '</td>' +
        '<td>' +
        escapeHtml_(row.Exception_Note || 'Not provided') +
        '</td>' +
        '</tr>';
    });

    html += '</tbody></table>';
  }

  if (issues.length) {
    html +=
      '<p><b>Validation issues in this run:</b> ' +
      issues.length +
      '</p>';
  }

  html +=
    '<p><a href="' +
    escapeHtml_(KPI_SETTINGS.DASHBOARD_URL) +
    '">Open the executive KPI dashboard</a></p>';

  return html;
}

function loadSampleDataForTest() {
  setupWorkbook();

  const now = new Date();
  const period = Utilities.formatDate(
    now,
    KPI_SETTINGS.TIME_ZONE,
    'yyyy-MM-dd'
  );

  clearDataRows_('Finance_Raw');
  clearDataRows_('Sales_Raw');
  clearDataRows_('Operations_Raw');
  clearDataRows_('Planning_Raw');
  clearDataRows_('KPI_Facts');
  clearDataRows_('KPI_Current');
  clearDataRows_('Data_Quality');

  appendRows_(getSheet_('Finance_Raw'), [[
    period, 438000, 0.31, 162000,
    'TEST-FIN-' + period, now, 'TEST-IMPORT', now
  ]]);

  appendRows_(getSheet_('Sales_Raw'), [[
    period, 1720000, 0.24,
    'TEST-SAL-' + period, now, 'TEST-IMPORT', now
  ]]);

  appendRows_(getSheet_('Operations_Raw'), [[
    period, 0.88, 0.86, 8,
    'TEST-OPS-' + period, now, 'TEST-IMPORT', now
  ]]);

  appendRows_(getSheet_('Planning_Raw'), [[
    period, 0.06,
    'TEST-PLAN-' + period, now, 'TEST-IMPORT', now
  ]]);

  console.log('Sample data loaded. Do not use this function in production.');
}

function runLocalValidationTest() {
  const runId = 'TEST-' + Utilities.getUuid();
  const stats = {
    importedRows: 0,
    factsUpserted: 0,
    validationIssues: 0,
    emailsSent: 0
  };

  startRun_(runId);

  try {
    stats.factsUpserted = buildFacts_(runId);
    refreshCurrentView_(runId);
    stats.validationIssues = countRunIssues_(runId);
    finishRun_(runId, 'TEST_SUCCESS', stats, '');
    console.log(JSON.stringify({runId: runId, stats: stats}));
  } catch (error) {
    finishRun_(runId, 'TEST_FAILED', stats, error.stack || error.message);
    throw error;
  }
}

function installTriggers() {
  removeTriggers();

  ScriptApp.newTrigger('runDailyRefresh')
    .timeBased()
    .everyDays(1)
    .atHour(6)
    .create();

  ScriptApp.newTrigger('runWeeklyRefreshAndNotify')
    .timeBased()
    .everyWeeks(1)
    .onWeekDay(ScriptApp.WeekDay.MONDAY)
    .atHour(8)
    .create();

  console.log('Daily and weekly triggers installed.');
}

function removeTriggers() {
  const managedHandlers = [
    'runDailyRefresh',
    'runWeeklyRefreshAndNotify'
  ];

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

function validateConfiguration_() {
  const requiredValues = [
    KPI_SETTINGS.DASHBOARD_URL,
    KPI_SETTINGS.SOURCE_SPREADSHEET_ID,
    KPI_SETTINGS.ARCHIVE_FOLDER_ID,
    KPI_SETTINGS.ADMIN_EMAIL
  ].concat(KPI_SETTINGS.EXECUTIVE_RECIPIENTS);

  EMAIL_SOURCES.forEach(function(source) {
    requiredValues.push(source.allowedSender);
  });

  requiredValues.forEach(function(value) {
    if (!value || String(value).indexOf('YOUR_') === 0) {
      throw new Error(
        'A required configuration placeholder has not been replaced: ' +
        value
      );
    }
  });
}

function validateDefinitionUniqueness_(definitions, runId) {
  const seen = new Set();

  definitions.forEach(function(def) {
    const id = String(def.KPI_ID || '').trim();

    if (!id) {
      recordIssue_(
        runId,
        'KPI_Definitions',
        '',
        'KPI_ID',
        'ERROR',
        'An active KPI definition has no KPI ID.'
      );
      return;
    }

    if (seen.has(id)) {
      throw new Error('Duplicate active KPI ID: ' + id);
    }

    seen.add(id);
  });
}

function startRun_(runId) {
  appendRows_(getSheet_('Automation_Log'), [[
    runId,
    new Date(),
    '',
    'RUNNING',
    0,
    0,
    0,
    0,
    '',
    Session.getEffectiveUser().getEmail()
  ]]);
}

function finishRun_(runId, status, stats, errorMessage) {
  const sheet = getSheet_('Automation_Log');
  const values = sheet.getDataRange().getValues();
  let targetRow = -1;

  for (let index = values.length - 1; index >= 1; index -= 1) {
    if (String(values[index][0]) === runId) {
      targetRow = index + 1;
      break;
    }
  }

  if (targetRow === -1) {
    throw new Error('Automation log row not found for run ' + runId);
  }

  sheet.getRange(targetRow, 3, 1, 8).setValues([[
    new Date(),
    status,
    stats.importedRows,
    stats.factsUpserted,
    stats.validationIssues,
    stats.emailsSent,
    errorMessage || '',
    Session.getEffectiveUser().getEmail()
  ]]);
}

function runExists_(runId) {
  return getObjects_('Automation_Log').some(function(row) {
    return String(row.Run_ID) === runId;
  });
}

function recordIssue_(
  runId,
  source,
  sourceRecordId,
  field,
  severity,
  issue
) {
  appendRows_(getSheet_('Data_Quality'), [[
    'ISSUE-' + Utilities.getUuid(),
    runId,
    source,
    sourceRecordId,
    field,
    severity,
    issue,
    new Date(),
    'OPEN',
    ''
  ]]);
}

function countRunIssues_(runId) {
  return getObjects_('Data_Quality').filter(function(row) {
    return String(row.Run_ID) === runId;
  }).length;
}

function notifyAdministratorFailure_(runId, error) {
  if (!validEmail_(KPI_SETTINGS.ADMIN_EMAIL)) {
    return;
  }

  try {
    GmailApp.sendEmail(
      KPI_SETTINGS.ADMIN_EMAIL,
      'Executive KPI automation failed | ' + runId,
      [
        'The executive KPI automation failed.',
        '',
        'Run ID: ' + runId,
        'Error: ' + (error.stack || error.message),
        '',
        'Review Apps Script executions and the Automation_Log worksheet.'
      ].join('\n')
    );
  } catch (notificationError) {
    console.error(
      'Unable to send administrator failure email: ' +
      notificationError.message
    );
  }
}

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

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

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

  const existingHeaders = sheet
    .getRange(1, 1, 1, headers.length)
    .getValues()[0]
    .map(function(value) {
      return String(value);
    });

  if (existingHeaders.join('|') !== headers.join('|')) {
    throw new Error(
      'Header mismatch in sheet ' +
      sheetName +
      '. Correct the header before continuing.'
    );
  }

  return sheet;
}

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

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

  return sheet;
}

function getObjects_(sheetName) {
  const sheet = getSheet_(sheetName);
  const values = sheet.getDataRange().getValues();

  if (values.length < 2) {
    return [];
  }

  const headers = values[0].map(function(value) {
    return String(value);
  });

  return values.slice(1)
    .filter(function(row) {
      return row.some(function(value) {
        return value !== '';
      });
    })
    .map(function(row) {
      const object = {};

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

      return object;
    });
}

function rewriteObjects_(sheetName, headers, objects) {
  const sheet = getSheet_(sheetName);

  clearDataRows_(sheetName);

  if (!objects.length) {
    return;
  }

  const rows = objects.map(function(object) {
    return headers.map(function(header) {
      return object[header] === undefined ? '' : object[header];
    });
  });

  appendRows_(sheet, rows);
}

function appendRows_(sheet, rows) {
  if (!rows || !rows.length) {
    return;
  }

  sheet
    .getRange(
      sheet.getLastRow() + 1,
      1,
      rows.length,
      rows[0].length
    )
    .setValues(rows);
}

function clearDataRows_(sheetName) {
  const sheet = getSheet_(sheetName);

  if (sheet.getLastRow() > 1) {
    sheet
      .getRange(
        2,
        1,
        sheet.getLastRow() - 1,
        sheet.getLastColumn()
      )
      .clearContent();
  }
}

function createHeaderMap_(headerRow) {
  const map = {};

  headerRow.forEach(function(value, index) {
    const normalized = normalizeHeader_(value);

    if (normalized) {
      map[normalized] = index;
    }
  });

  return map;
}

function normalizeHeader_(value) {
  return String(value || '')
    .replace(/^\uFEFF/, '')
    .trim()
    .toLowerCase()
    .replace(/\s+/g, '_');
}

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

  if (typeof value === 'number') {
    return Number.isFinite(value) ? value : null;
  }

  let text = String(value).trim();

  if (!text) {
    return null;
  }

  const isPercent = text.indexOf('%') !== -1;
  const isNegativeParentheses =
    text.charAt(0) === '(' && text.charAt(text.length - 1) === ')';

  text = text
    .replace(/[$£€,%\s]/g, '')
    .replace(/[()]/g, '');

  let number = Number(text);

  if (!Number.isFinite(number)) {
    return null;
  }

  if (isNegativeParentheses) {
    number = -number;
  }

  if (isPercent) {
    number = number / 100;
  }

  return number;
}

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

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

  const parsed = new Date(value);

  return isNaN(parsed.getTime()) ? null : parsed;
}

function isTrue_(value) {
  if (value === true) {
    return true;
  }

  return ['TRUE', 'YES', '1'].indexOf(
    String(value).trim().toUpperCase()
  ) !== -1;
}

function extractEmailAddress_(fromValue) {
  const text = String(fromValue || '').trim().toLowerCase();
  const match = text.match(/<([^>]+)>/);

  return match ? match[1].trim() : text;
}

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

function sanitizeFilename_(value) {
  return String(value)
    .replace(/[^a-zA-Z0-9._-]/g, '_')
    .substring(0, 120);
}

function formatKpiValue_(value, format) {
  const number = parseNumber_(value);

  if (number === null) {
    return 'Not available';
  }

  if (String(format) === 'PERCENT') {
    return Utilities.formatString('%.1f%%', number * 100);
  }

  if (String(format) === 'CURRENCY') {
    return '$' + Utilities.formatString('%.2f', number);
  }

  return Utilities.formatString('%.2f', number);
}

function escapeHtml_(value) {
  return String(value === null || value === undefined ? '' : value)
    .replace(/&/g, '&amp;')
    .replace(/</g, '&lt;')
    .replace(/>/g, '&gt;')
    .replace(/"/g, '&quot;')
    .replace(/'/g, '&#39;');
}

Configuration and Deployment Instructions

1. Replace the dashboard URL, source spreadsheet ID, Drive folder ID, administrator email, executive recipients, and approved source senders.
2. Run setupWorkbook from the Apps Script editor.
3. Approve the requested Gmail, Sheets, Drive, and trigger permissions. Review the authorization screen against the code before approving.
4. Update the default KPI definitions. The values in the script are representative and are not business recommendations.
5. Run loadSampleDataForTest only in the test workbook.
6. Run runLocalValidationTest and inspect the Apps Script execution log and workbook logs.
7. Send test CSV files to the configured mailbox and apply the expected labels.
8. Run runDailyRefresh manually. Confirm that Drive archives, raw rows, facts, current records, and alert history are correct.
9. Create the Looker Studio report and replace YOUR_LOOKER_STUDIO_REPORT_URL.
10. Run installTriggers from the production automation account.

The script requests Gmail read and send access, Sheets access, Drive file access, and trigger management. If the optional AI code is installed, it also requests external HTTP access.

Use the Apps Script Executions view to inspect start time, duration, status, and logged errors. The workbook’s Automation_Log provides a business-facing record, while the Apps Script execution details provide technical diagnostics.

Looker Studio Native Configuration

1. Create a new report using the Google Sheets connector.
2. Select the central control workbook and the KPI_Current worksheet.
3. Enable the option that treats the first row as field names.
4. Verify that Period_End and Source_Updated_At are date or date-time fields.
5. Verify that Actual_Value, Target_Value, and Variance are numeric.
6. Add an executive table with KPI name, actual, target, variance, status, owner, period, note, and drill-down URL.
7. Add filters for function, status, and owner.
8. Create a second data source from KPI_Facts.
9. Add a trend chart using Period End as the time dimension, Actual Value as the metric, and KPI ID as a report filter.
10. Add visible source-update and period fields so viewers can assess freshness.
11. Configure data freshness according to the connector options available in the organization’s environment.
12. Test report access using an executive viewer account rather than the report owner’s account.

Failure Handling and Operational Reliability

The design uses two idempotency layers. Attachment idempotency prevents the same email file from being intentionally imported twice. Fact idempotency prevents repeated or corrected imports from creating duplicate KPI-period records.

The Data_Quality worksheet acts as a manual-review queue. Staff resolve an issue by correcting the source or configuration, rerunning the workflow, verifying the result, changing the issue status to Resolved, and recording the resolution timestamp.

Reconciliation should compare the newest source record ID, raw row, fact record, current KPI record, dashboard display, and automation run. This gives the technical owner a repeatable route through a partially completed process.

A Complete Example

For the week ending July 12, 2026, Harborlight’s finance system emails the following representative CSV row:

as_of_date,revenue_mtd,gross_margin_pct,ar_over_30,source_record_id,source_updated_at
2026-07-12,438000,0.31,162000,FIN-2026-07-12-R1,2026-07-13T05:30:00Z

1. Gmail applies the KPI/Finance label because the message comes from the approved sender and contains the expected attachment.
2. The daily Apps Script trigger reads the message and verifies that the filename matches the finance pattern.
3. The script calculates a SHA-256 content hash. No matching import key exists.
4. The attachment is archived in Drive with a name similar to Finance_20260713_HASH_finance_kpi_2026-07-12.csv.
5. The row is appended to Finance_Raw with an import ID and import timestamp.
6. The FIN_REV_MTD definition maps revenue_mtd to Revenue MTD.
7. The value 438000 passes the non-negative validation rule.
8. The generated fact key is FIN_REV_MTD|2026-07-12.
9. The target is 500,000, the warning threshold is 450,000, and the critical threshold is 400,000.
10. Because 438,000 is below the warning threshold but above the critical threshold, the result becomes Warning.
11. The variance is calculated as 438000 - 500000 = -62000.
12. The finance manager is assigned as owner.
13. The fact is written to KPI_Facts and becomes the latest Revenue MTD row in KPI_Current.
14. The finance manager adds an exception note to Exception_Notes using the same fact key. The next run merges the note into the reporting output.
15. Looker Studio reads the current row and displays the value, target, warning status, source date, owner, note, and finance drill-down link.
16. The Monday executive summary includes Revenue MTD as an exception.

The same finance row also produces Gross Margin and Receivables Over 30 Days facts. It does not create a single combined status because each KPI has its own definition, target, owner context, and thresholds.

If the finance system later sends record FIN-2026-07-12-R2 with a later source-update timestamp, the same fact keys are updated. The raw history and processed import evidence remain available.

Implementation Cost

All amounts below are representative planning assumptions, not quoted platform prices or verified client costs. Organizations should obtain current licensing information and substitute their own labour rates.

Tools with no separate incremental fee still require configuration, testing, monitoring, documentation, and ownership. Existing subscriptions should not be treated as having no organizational cost.

Estimated Time and Cost Savings

The representative calculation uses these assumptions:

• 9 KPI records per weekly cycle
• 4.33 weekly cycles per average month
• 39 KPI updates per month after rounding
• 26 minutes of current handling per KPI, including extraction, reconciliation, formatting, and follow-up
• 4 minutes of new routine handling per KPI
• 15% of KPI records require exception review
• 10 minutes of review for each exception
• 2 hours of monthly automation maintenance
• $65 loaded hourly labour cost
• $45 recurring monthly tool and administration allocation
• $9,840 representative one-time economic implementation cost

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

Actual calculation: 39 × 26 ÷ 60 = 16.90 hours

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

Routine handling: 39 × 4 ÷ 60 = 2.60 hours

Exception handling: 39 × 15% × 10 ÷ 60 = 0.98 hours

Maintenance: 2.00 hours

Total new monthly labour: 2.60 + 0.98 + 2.00 = 5.58 hours

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

Actual calculation: 16.90 – 5.58 = 11.32 hours

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

Actual calculation: 11.32 × $65 = $735.80

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

Actual calculation: $735.80 – $45 = $690.80

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

Actual calculation: $9,840 ÷ $690.80 = approximately 14.2 months

Recovered time does not automatically reduce payroll. It may provide additional analytical capacity, quicker reporting, reduced overtime, better preparation for leadership meetings, and less dependency on one analyst.

Non-financial benefits include clearer ownership, fewer follow-up emails, more consistent KPI definitions, faster detection of missing source data, improved auditability, easier historical comparison, and better visibility into automation failures.

Readers should replace the volume, minutes, exception rate, hourly cost, maintenance time, tool costs, and implementation effort with their own measurements.

Adding AI to the Automation

AI should be added only after the core import, validation, status calculation, notification, and dashboard workflow is reliable.

The main benefits of the core system do not require AI. Required fields, exact header checks, numeric validation, threshold evaluation, duplicate prevention, ownership, permissions, and refresh monitoring are better handled by deterministic rules.

Potential AI uses include summarizing owner notes, identifying missing explanation elements, grouping recurring exception themes, drafting a short leadership briefing, and helping users search historical notes semantically.

AI should not determine whether a KPI is Warning or Critical. It should not change targets, approve explanations, assign blame, recommend disciplinary action, alter accounting data, or make final operational decisions.

The Recommended AI Enhancement

The recommended enhancement is a structured draft of the weekly exception briefing. The AI receives only current exception data and approved owner notes. It returns a concise draft for human review.

• Trigger: Manual review action or a scheduled trigger after the deterministic weekly refresh.
• AI input: KPI ID, name, function, actual value, target, variance, status, source period, and owner note.
• System instruction: Summarize only supplied facts, do not infer causes, do not make final decisions, and identify missing context.
• Expected output: Structured JSON containing a headline, summary, and one item per exception.
• Validation: Confirm valid JSON, approved KPI IDs, expected statuses, required properties, and length limits.
• Record update: Save the draft to AI_Briefs with input hash, model, timestamp, and review status.
• Human review: The strategy analyst checks every draft before it is copied into an executive communication.
• Low-confidence handling: Missing notes become questions for the owner rather than invented explanations.
• Prohibited data: Customer names, employee details, invoice-level data, credentials, legal advice, health information, and unrelated source records.
• Failure behavior: Preserve the normal rule-based email and prepare the briefing manually.

The reusable instruction is:

You prepare a draft executive KPI exception briefing from structured records.

Use only facts supplied in the input. Do not infer a cause that is not stated in an owner note. Do not change KPI status, thresholds, targets, owners, or values. Do not make final financial, employment, legal, customer, safety, or operational decisions.

For each exception:
1. State the KPI, status, actual value, and target.
2. Summarize the owner note when one exists.
3. If context is missing, state that context is missing and provide questions for the owner.
4. Keep possible issues conditional and grounded in the supplied note.
5. Return only JSON matching the required schema.

A representative structured response is:

{
  "headline": "Three KPIs require leadership attention",
  "summary": "Revenue MTD, receivables over 30 days, and on-time completion are outside their warning thresholds. Owner review is required before conclusions are drawn.",
  "items": [
    {
      "kpi_id": "FIN_REV_MTD",
      "status": "Warning",
      "observed": "Revenue MTD is 438000 against a target of 500000.",
      "possible_issue": "The owner note states that two expected renewals moved to the following reporting week.",
      "questions_for_owner": [
        "Are the renewals still expected within the current month?",
        "Does the timing change affect the current forecast?"
      ]
    }
  ],
  "disclaimer": "Draft generated from supplied KPI records and subject to human review."
}

The following optional Apps Script file can be added as AI.gs in the same bound project. Store the API key in Script Properties under OPENAI_API_KEY. Replace YOUR_OPENAI_MODEL with an available model that supports structured JSON output.

const AI_SETTINGS = Object.freeze({
  MODEL: 'YOUR_OPENAI_MODEL',
  ENDPOINT: 'https://api.openai.com/v1/responses',
  MAX_RETRIES: 3
});

const AI_SYSTEM_INSTRUCTION = [
  'You prepare a draft executive KPI exception briefing from structured records.',
  'Use only facts supplied in the input.',
  'Do not infer a cause that is not stated in an owner note.',
  'Do not change KPI status, thresholds, targets, owners, or values.',
  'Do not make final financial, employment, legal, customer, safety, or operational decisions.',
  'For each exception, state the KPI, status, actual value, and target.',
  'Summarize the owner note when one exists.',
  'If context is missing, state that it is missing and provide questions for the owner.',
  'Return only JSON matching the required schema.'
].join(' ');

function generateAiExceptionBrief() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const currentSheet = spreadsheet.getSheetByName('KPI_Current');

  if (!currentSheet) {
    throw new Error('KPI_Current worksheet not found.');
  }

  const exceptions = aiReadObjects_(currentSheet)
    .filter(function(row) {
      return String(row.Threshold_Status) !== 'On Track';
    })
    .map(function(row) {
      return {
        kpi_id: String(row.KPI_ID),
        kpi_name: String(row.KPI_Name),
        function: String(row.Function),
        period_end: aiDateText_(row.Period_End),
        actual_value: row.Actual_Value,
        target_value: row.Target_Value,
        variance: row.Variance,
        status: String(row.Threshold_Status),
        owner_note: String(row.Exception_Note || '')
      };
    });

  if (!exceptions.length) {
    throw new Error('No current exceptions are available for an AI brief.');
  }

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

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

  if (
    !AI_SETTINGS.MODEL ||
    AI_SETTINGS.MODEL.indexOf('YOUR_') === 0
  ) {
    throw new Error('Replace YOUR_OPENAI_MODEL before running.');
  }

  const userInput = JSON.stringify({
    task: 'Draft the weekly executive KPI exception briefing.',
    exceptions: exceptions
  });

  const requestBody = {
    model: AI_SETTINGS.MODEL,
    instructions: AI_SYSTEM_INSTRUCTION,
    input: userInput,
    text: {
      format: {
        type: 'json_schema',
        name: 'kpi_exception_brief',
        strict: true,
        schema: {
          type: 'object',
          additionalProperties: false,
          required: [
            'headline',
            'summary',
            'items',
            'disclaimer'
          ],
          properties: {
            headline: {
              type: 'string'
            },
            summary: {
              type: 'string'
            },
            items: {
              type: 'array',
              items: {
                type: 'object',
                additionalProperties: false,
                required: [
                  'kpi_id',
                  'status',
                  'observed',
                  'possible_issue',
                  'questions_for_owner'
                ],
                properties: {
                  kpi_id: {
                    type: 'string'
                  },
                  status: {
                    type: 'string'
                  },
                  observed: {
                    type: 'string'
                  },
                  possible_issue: {
                    type: 'string'
                  },
                  questions_for_owner: {
                    type: 'array',
                    items: {
                      type: 'string'
                    }
                  }
                }
              }
            },
            disclaimer: {
              type: 'string'
            }
          }
        }
      }
    }
  };

  const generatedAt = new Date();
  const inputHash = aiHash_(userInput);
  let briefId = 'AI-' + Utilities.getUuid();

  try {
    const responseJson = aiCallWithRetry_(
      apiKey,
      requestBody
    );
    const outputText = aiExtractOutputText_(responseJson);
    const parsedBrief = JSON.parse(outputText);

    aiValidateBrief_(parsedBrief, exceptions);

    aiSaveBrief_({
      briefId: briefId,
      generatedAt: generatedAt,
      periodEnd: aiDateText_(exceptions[0].period_end),
      inputHash: inputHash,
      model: AI_SETTINGS.MODEL,
      status: 'DRAFT_REVIEW_REQUIRED',
      draftJson: JSON.stringify(parsedBrief),
      reviewedBy: '',
      reviewedAt: '',
      approvedText: '',
      errorMessage: ''
    });

    console.log(JSON.stringify({
      briefId: briefId,
      status: 'DRAFT_REVIEW_REQUIRED'
    }));

    return parsedBrief;
  } catch (error) {
    aiSaveBrief_({
      briefId: briefId,
      generatedAt: generatedAt,
      periodEnd: aiDateText_(exceptions[0].period_end),
      inputHash: inputHash,
      model: AI_SETTINGS.MODEL,
      status: 'FAILED',
      draftJson: '',
      reviewedBy: '',
      reviewedAt: '',
      approvedText: '',
      errorMessage: error.stack || error.message
    });

    throw error;
  }
}

function aiCallWithRetry_(apiKey, requestBody) {
  let lastError;

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

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

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

      if (
        statusCode === 429 ||
        statusCode >= 500
      ) {
        lastError = new Error(
          'AI service returned retryable status ' +
          statusCode +
          ': ' +
          responseText
        );

        Utilities.sleep(
          Math.pow(2, attempt) * 1000 +
          Math.floor(Math.random() * 500)
        );

        continue;
      }

      throw new Error(
        'AI service returned status ' +
        statusCode +
        ': ' +
        responseText
      );
    } catch (error) {
      lastError = error;

      if (attempt === AI_SETTINGS.MAX_RETRIES - 1) {
        break;
      }

      Utilities.sleep(
        Math.pow(2, attempt) * 1000 +
        Math.floor(Math.random() * 500)
      );
    }
  }

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

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

  const output = responseJson.output || [];

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

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

  throw new Error('AI response contained no output text.');
}

function aiValidateBrief_(brief, exceptions) {
  if (
    !brief ||
    typeof brief.headline !== 'string' ||
    typeof brief.summary !== 'string' ||
    !Array.isArray(brief.items) ||
    typeof brief.disclaimer !== 'string'
  ) {
    throw new Error('AI brief does not match the required structure.');
  }

  const allowed = {};

  exceptions.forEach(function(item) {
    allowed[item.kpi_id] = item.status;
  });

  brief.items.forEach(function(item) {
    if (!allowed[item.kpi_id]) {
      throw new Error(
        'AI output contains an unapproved KPI ID: ' + item.kpi_id
      );
    }

    if (String(item.status) !== String(allowed[item.kpi_id])) {
      throw new Error(
        'AI output changed the status for KPI ' + item.kpi_id
      );
    }

    if (!Array.isArray(item.questions_for_owner)) {
      throw new Error(
        'AI output has invalid owner questions for ' + item.kpi_id
      );
    }
  });
}

function aiSaveBrief_(brief) {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = spreadsheet.getSheetByName('AI_Briefs');

  const headers = [
    'Brief_ID',
    'Generated_At',
    'Period_End',
    'Input_Hash',
    'Model',
    'Status',
    'Draft_JSON',
    'Reviewed_By',
    'Reviewed_At',
    'Approved_Text',
    'Error_Message'
  ];

  if (!sheet) {
    sheet = spreadsheet.insertSheet('AI_Briefs');
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
    sheet.setFrozenRows(1);
  }

  sheet.appendRow([
    brief.briefId,
    brief.generatedAt,
    brief.periodEnd,
    brief.inputHash,
    brief.model,
    brief.status,
    brief.draftJson,
    brief.reviewedBy,
    brief.reviewedAt,
    brief.approvedText,
    brief.errorMessage
  ]);
}

function aiReadObjects_(sheet) {
  const values = sheet.getDataRange().getValues();

  if (values.length < 2) {
    return [];
  }

  const headers = values[0].map(function(value) {
    return String(value);
  });

  return values.slice(1)
    .filter(function(row) {
      return row.some(function(value) {
        return value !== '';
      });
    })
    .map(function(row) {
      const object = {};

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

      return object;
    });
}

function aiHash_(text) {
  const digest = Utilities.computeDigest(
    Utilities.DigestAlgorithm.SHA_256,
    text,
    Utilities.Charset.UTF_8
  );

  return Utilities.base64EncodeWebSafe(digest);
}

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

  const date = value instanceof Date ? value : new Date(value);

  if (isNaN(date.getTime())) {
    return String(value);
  }

  return Utilities.formatDate(date, 'Etc/UTC', 'yyyy-MM-dd');
}

Test the AI function with fabricated KPI records. Inspect the saved JSON, deliberately remove an expected field, test an unavailable model, and revoke the API key to confirm the fallback behavior. The function saves a draft but does not send it automatically.

Benefits of the AI Enhancement

• Reduces the time required to turn several exception notes into one concise draft.
• Uses a consistent briefing structure across finance, sales, and operations.
• Identifies exceptions that lack owner context.
• Produces questions for owners without changing the deterministic status.
• Supports later theme analysis across approved historical notes.

These are AI-specific benefits. Data collection, validation, duplicate prevention, dashboard refresh, threshold evaluation, ownership, reminders, and audit logging already come from the core automation.

What Remains Rule-Based or Human-Controlled

Estimating the Additional Value of AI

The representative AI estimate assumes:

• 39 KPI updates per month
• 15% exception rate, producing approximately 5.85 exception records
• 10 minutes of human briefing work per exception without AI
• 6 minutes of review and correction per exception with AI
• 4 additional minutes saved per exception
• 20% of drafts require material wording correction
• 5% service or malformed-output failure rate
• Human review of every draft
• $5 monthly AI usage allowance

Additional monthly time recovered: 5.85 exceptions × 4 minutes ÷ 60 = 0.39 hours

Representative labour value: 0.39 × $65 = $25.35

Net additional monthly value after AI allowance: $25.35 – $5 = $20.35

The additional financial value is modest at this volume. The stronger reason to add AI would be more consistent briefing preparation or a larger set of unstructured exception notes, not replacement of the core rules.

Testing Checklist

Use fabricated sample data before processing real financial, customer, employee, or operational information.

Ongoing Maintenance

Rotate API credentials according to organizational policy. Google account authorization should be reviewed when scopes, ownership, or administrative controls change.

Keep a current recovery document covering account ownership, script location, trigger installation, source labels, folder IDs, spreadsheet IDs, approved senders, dashboard ownership, and manual reporting fallback.

When to Move to Dedicated Software

The Google-based implementation can remain appropriate while the KPI set is small, refreshes are predictable, data is aggregated, and governance requirements are manageable.

Consider a database, cloud warehouse, dedicated business intelligence platform, or performance management application when several of these conditions appear:

• Hundreds of KPIs or high-frequency transaction data must be processed.
• Multiple legal entities, currencies, regions, or business units require complex consolidation.
• Source APIs replace a small number of standardized exports.
• Row-level and field-level permissions become difficult to administer in Sheets.
• Formal regulatory or external-audit controls require stronger data lineage and immutable processing logs.
• Apps Script execution limits, Gmail search limits, or spreadsheet performance affect reliability.
• Exceptions require multi-stage approvals, delegation, comments, and service-level tracking.
• Mobile or offline workflows become necessary.
• Executives or customers need a dedicated portal.
• Source changes consume excessive maintenance time.
• Advanced forecasting, planning, allocations, or scenario modeling become part of the same process.
• Vendor support and contractual service commitments become mandatory.
• Security risk increases because aggregated reporting files begin to contain transaction-level sensitive information.

Growth alone does not require immediate replacement. The decision should consider reliability, governance, user experience, maintenance effort, and total cost rather than transaction volume in isolation.

Implementation Checklist

• Define the executive decisions the dashboard must support.
• Limit and approve the KPI set.
• Document every KPI formula, owner, source, target, threshold, and refresh cadence.
• Confirm Google Workspace, Gmail, Drive, Apps Script, and Looker Studio access.
• Create the automation account and backup ownership process.
• Create the control workbook and restricted archive folder.
• Configure Gmail labels and source-sender filters.
• Standardize finance, sales, operations, and planning input fields.
• Create the raw, fact, current, note, import, quality, alert, and automation-log structures.
• Replace all script configuration placeholders.
• Review field mappings and valid ranges.
• Configure unique import keys and KPI-period fact keys.
• Build and test source validation.
• Build fact creation and current-view refresh.
• Configure threshold statuses and workflow ownership.
• Define reminder and escalation timing.
• Configure backup owners and administrator notifications.
• Archive source attachments with restricted permissions.
• Connect KPI_Current and KPI_Facts to Looker Studio.
• Create executive, exception, trend, owner, stale-data, and failure views.
• Protect generated sheets and restrict sensitive drill-down links.
• Run sample, duplicate, failure, stale-data, notification, and permission tests.
• Complete user acceptance testing with source owners and executive viewers.
• Install production triggers from the correct automation account.
• Document rollback and manual recovery steps.
• Validate implementation-cost assumptions.
• Replace savings assumptions with measured internal figures.
• Add AI only after the core automation is reliable.
• Require human review for every AI-generated briefing.
• Assign primary and backup maintenance owners.
• Review upgrade criteria quarterly.