Building a Daily Operations Dashboard in Looker Studio

The Business Situation

Cedar Vale Industrial Equipment is a fictional 68-person company that assembles industrial equipment, distributes replacement components, and provides field service. The business owner relies on six department leads for daily updates covering sales, service, production, staffing, inventory, and finance.

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.

Each department already maintained operational information in Google Sheets. The problem was that the sheets used different structures, update times, definitions, and threshold conventions. Department leads summarized their numbers in separate Gmail messages each morning.

An office administrator copied selected figures into a management spreadsheet. The owner then reviewed five or six emails, checked the summary, asked follow-up questions, and opened the underlying trackers when a number appeared unusual.

The process covered approximately 22 business days per month. Each day involved six departmental input records and ten management measures. That created about 132 departmental updates per month, excluding corrections and follow-up messages.

The operations manager became the system owner. Department leads remained responsible for the accuracy of their source figures. The office administrator became the backup owner, while the business owner and executive team remained the primary dashboard audience.

The business did not need a large enterprise data warehouse at this stage. It needed a controlled daily reporting process that could answer five questions quickly:

• Which measures require action today?
• Who owns each exception?
• When was each source updated?
• What explanation did the responsible department provide?
• Where can a manager inspect the underlying detail?

The Existing Process

The original process followed the same general sequence each weekday:

1. Department employees updated separate operational trackers during the day.
2. Each department lead calculated or copied the measures they believed management needed.
3. The lead emailed a short update to the owner and office administrator.
4. The administrator copied values into a management summary spreadsheet.
5. The administrator reconciled missing or conflicting numbers through email or chat.
6. The owner reviewed the summary and opened several departmental sheets for context.
7. Exceptions were assigned informally through replies, calls, or meetings.
8. The following morning, the process started again without a consistent record of the previous day’s actions.

The spreadsheet itself was not the main problem. The larger issue was the absence of common data definitions, validation, status rules, refresh expectations, and exception ownership.

For example, one department considered an item overdue at the end of its due date, while another waited until the following morning. Finance reported overdue receivables as a currency amount, while service reported unbilled work as both a count and an amount in different emails.

The company needed to preserve the useful departmental trackers while removing the manual consolidation layer.

What the New System Needed to Do

The team defined the reporting requirements before choosing a dashboard product. The goal was not to display every available number. It was to present a limited set of measures that could lead to a specific operational action.

The design also needed an explicit refresh expectation. Department updates were due by 6:30 a.m. The consolidation job would run at approximately 6:45 a.m., subject to the scheduling behavior of Google Apps Script time-driven triggers.

The dashboard would show its last automation time so a viewer could distinguish a genuine zero from missing or stale data.

Implementation Approaches Considered

Improving the spreadsheet alone

A single management spreadsheet could have added standardized columns, conditional formatting, and a manually maintained summary. This would have been the quickest option, but it would still depend on copying values and checking whether every department had submitted its update.

Google Sheets, Looker Studio, Apps Script, and Gmail

This option retained the company’s existing Google Workspace environment. Google Sheets provided structured inputs and the system of record. Apps Script handled validation and consolidation. Looker Studio presented the dashboard, and Gmail delivered deterministic exception summaries.

It offered sufficient control for ten daily metrics without introducing another identity platform or a separate database.

Power BI and the Microsoft ecosystem

Power BI was a valid reporting alternative. Combined with SharePoint, Excel, Power Automate, and Outlook, it could support a similar design. It was less suitable for Cedar Vale because the business already used Google Workspace and would have needed additional administration, licensing review, and duplicate user management.

Connected database and BI architecture

A PostgreSQL database or cloud warehouse would provide stronger constraints, larger-scale history, and more reliable joins. This becomes attractive when metrics are calculated from detailed transactions rather than daily summaries. At the current scale, it would add deployment, backup, monitoring, and access-management work without materially improving the daily decision process.

Dedicated software

An ERP, manufacturing operations platform, or field service platform could eventually become the source for many of these measures. Purchasing one solely to produce a morning dashboard would create a much larger implementation than the stated need.

The Selected Solution

Cedar Vale selected Google Sheets, Google Apps Script, Looker Studio, Gmail, and Google Drive.

The existing departmental trackers were retained. The daily input tabs contained only aggregate measures and links back to those detailed trackers. This reduced the amount of sensitive or transactional data copied into the management workbook.

Manual consolidation, status coloring, missing-update checks, and routine exception-summary emails were removed.

Human control remained in four places:

• Department leads remained accountable for source accuracy.
• Operations managers acknowledged and resolved exceptions.
• Management decided what operational action to take.
• A human reviewer approved any AI-generated briefing before distribution.

System Architecture and Data Flow

The architecture used a master Google Sheets workbook. Six input tabs accepted one row per department per business date. A configuration tab defined the ten measures. Apps Script read the inputs, validated them, created long-format metric records, synchronized exception records, and sent Gmail notifications.

Looker Studio connected to the consolidated output tabs rather than the editable departmental inputs.

1. Department update: A department lead enters the business date, measure values, exception note, drill-down URL, and update timestamp. Invalid data remains visible in the source row and is not silently corrected.
2. Scheduled trigger: An installable Apps Script trigger starts the consolidation function. The trigger executes as the account that created it.
3. Business-day check: The script checks weekends and the optional Holidays tab. Non-business days are logged as skipped.
4. Configuration load: The script reads active metric definitions, owner addresses, thresholds, refresh times, and source mappings.
5. Source validation: Each source tab is checked for exactly one row matching the business date. Missing or duplicate rows produce No Data records.
6. Transformation: Numeric values are standardized, ratios are calculated where required, and deterministic record IDs are generated.
7. Status evaluation: Each metric is classified using minimum, maximum, or ratio-based rules. A non-green result without an explanation is routed to manual review.
8. System-of-record update: Daily metric records are inserted or updated by record ID. The operation is idempotent, meaning a rerun updates the same logical record instead of creating another one.
9. Exception synchronization: Watch, Action Required, and No Data records create or reopen entries in the exception log. Corrected metrics can automatically resolve the corresponding exception.
10. Dashboard reporting: Looker Studio reads Daily_Metrics and Exception_Log. Connector freshness and report caching determine when updated sheet values appear to viewers.
11. Notification: Gmail receives a deterministic table of current measures and exceptions. Later reminder runs notify owners of open items.
12. Failure path: Processing failures are written to Automation_Log where possible. The operations leader receives an error message, and the same business date can be rerun without duplicating metric records.

• Intake: Six controlled input tabs in Google Sheets
• System of record: Daily_Metrics and Exception_Log tabs in the master workbook
• Automation layer: Bound Google Apps Script project with installable time-driven triggers
• Document storage: Restricted Google Drive folder containing the workbook, report, procedures, and approved exports
• Notifications: Gmail messages sent through Apps Script
• Reporting: Looker Studio connected to consolidated Google Sheets ranges
• AI layer: Optional approved API used only to draft a reviewed briefing

Data Structure

The solution used a long-format metric table. Each row represented one metric for one business date. Long-format data made it easier to filter by department, owner, status, and date without adding a new column every time a measure changed.

Related records

Metric_Config to Daily_Metrics

One metric configuration can produce one Daily_Metrics row per business date.

Department input to Daily_Metrics

One department row can supply several metrics. The source sheet and row number are stored on every resulting metric record.

Daily_Metrics to Exception_Log

A daily metric has zero or one matching exception record for the same date and metric ID.

Automation_Log to Daily_Metrics

One automation run can create or update several metric rows. Run_ID provides the relationship.

Email_Log to exception notifications

Message keys prevent routine reminders from being sent repeatedly on the same day.

Google Sheets does not enforce database-grade unique constraints. The script therefore checks duplicate source dates and uses deterministic output IDs when performing updates.

Workflow Statuses and Ownership

A value correction sends a record backward through the calculation. The department lead changes the source row, and an authorized user reruns the consolidation. The deterministic Record_ID causes the output record to be updated rather than duplicated.

Rejection is represented as a return for correction. If the operations manager believes a source value or explanation is unreliable, the exception remains Open and the department lead corrects the source.

The dashboard does not approve payments, accounting entries, staffing actions, or safety decisions. It provides evidence and ownership for managers who make those decisions in the appropriate operational system.

Step-by-Step Implementation

Step 1: Prepare the Accounts and Permissions

1. Create or identify a designated Google Workspace account that will own the master workbook, Apps Script project, and installable triggers.
2. Do not share that account’s password. Assign named users and use organizational recovery and multifactor authentication controls.
3. Create a restricted Google Drive folder named Daily Operations.
4. Place the master Google Sheets workbook in that folder.
5. Grant department leads edit access only where the organization’s sharing model permits it.
6. Grant the operations manager and backup owner edit access to configuration, exception, and log tabs.
7. Grant executives view access to the Looker Studio report rather than edit access to the workbook.
8. Create test users representing a department lead, operations manager, executive viewer, and unauthorized user.
9. Confirm that the account creating the trigger can send email to the required recipients and has sufficient Apps Script execution capacity for the expected volume.
10. Document who will reinstall the triggers if the automation owner leaves or the workbook changes ownership.

Worksheet protection prevents accidental edits but is not a confidentiality boundary. A user with edit access to a workbook may be able to inspect other tabs. Cedar Vale therefore stored only aggregate finance and staffing measures in the master workbook.

If detailed finance, employee, or customer information must remain department-restricted, use separate source workbooks or a database with row-level and field-level permissions. The consolidation script can then be extended to open approved source spreadsheet IDs.

The implementation requires features that support Google Sheets, Looker Studio connections, Apps Script execution, installable triggers, and outbound email. Exact subscription eligibility and quotas should be confirmed against the organization’s current Google agreement.

Step 2: Build the Intake

The intake consisted of six tabs in the master workbook. Each tab accepted one row per business date. The script supplied later in this case study creates the tabs and applies basic date and numeric validation.

The business date, source link, and update timestamp are required for every row. An exception note is conditionally required when any measure from the row produces Watch or Action Required status.

The input is internal and is not exposed as a public form. Restricting workbook access is the primary spam-prevention control. If a Google Form is added later, restrict it to authenticated organizational users and validate the resulting response rows before consolidation.

Attachments are not accepted in the summary workbook. Departments provide a drill-down URL to the detailed tracker or approved Drive location. This avoids copying documents and reduces file-access ambiguity.

The operating procedure should state that input tabs contain aggregate operational information only. Personal employee data, customer account details, bank information, and confidential case notes are prohibited.

Duplicate prevention uses two layers:

• Department leads are instructed to edit the existing row for a business date instead of adding another row.
• The script treats multiple rows for the same date as No Data until the duplicate is corrected.

Step 3: Create the System of Record

The Apps Script initialization function creates the following tabs:

• Metric_Config
• Daily_Metrics
• Exception_Log
• Automation_Log
• Email_Log
• Holidays
• The six department input tabs

The Metric_Config tab contains one row for each management measure. Configuration is data rather than code, so the operations manager can update an owner, threshold, or source field without rewriting the processing logic.

These thresholds are representative assumptions, not universal recommendations. Cedar Vale would confirm each measure’s definition, timing, exclusions, and owner before activation.

Record IDs follow this naming convention:

OPS-YYYYMMDD-METRIC_ID

Example:
OPS-20260714-INVENTORY_SHORTAGES_7D

Filtered views should be created for current exceptions, No Data records, unresolved exceptions, automation failures, and records owned by each department.

Protect calculated output tabs from routine editing. Leave only acknowledgment and resolution fields editable for authorized operations staff.

Step 4: Connect the Tools

The Google Sheets to Apps Script connection uses the built-in Spreadsheet service. No API key is required. The script runs with the authorization of the user who installs the trigger.

The Apps Script to Gmail connection uses MailApp. The user authorizes outbound email when the script first runs. The implementation does not read inbox contents.

The Google Sheets to Looker Studio connection uses the native Google Sheets data connector. Add Daily_Metrics and Exception_Log as separate data sources. Set field types explicitly, especially Business_Date, currency values, percentages, status, and Drilldown_URL.

Connector and report interface labels can change. The required underlying configuration is:

• Select the master workbook.
• Select the appropriate output worksheet.
• Use the first row as field names.
• Confirm date and number types.
• Set a suitable data-freshness interval.
• Display Last_Automation_Run prominently.
• Restrict report sharing to authorized viewers.

Step 5: Build the Core Automation

The core consolidation runs in this order:

1. Acquire a script lock to prevent overlapping executions.
2. Validate configuration placeholders and email addresses.
3. Check the business calendar.
4. Load active metric configurations.
5. Read each required department row.
6. Validate dates, duplicates, numeric values, timestamps, and URLs.
7. Calculate target, variance, and status.
8. Create a deterministic metric record ID.
9. Upsert Daily_Metrics by Record_ID.
10. Create, update, reopen, or resolve matching exception records.
11. Send the morning table if it has not already been sent.
12. Write the run result to Automation_Log.

• Trigger: Installable weekday time-driven trigger, with holiday checking inside the script
• Conditions: Active metric, valid configuration, one matching source row, valid numeric input, and valid drill-down URL
• Actions: Validate, evaluate, upsert, synchronize exceptions, email, and log
• Fields updated: All calculated Daily_Metrics fields and matching Exception_Log fields
• Notification: Morning status table to configured recipients
• Exception: No Data record and manual-review status when a reliable value cannot be produced

Unique records are created from the business date and metric ID. Reruns update the same ID. The first Created_Date is preserved.

Documents are linked rather than copied. Source row numbers are stored as external references, and the drill-down URL provides the operational detail.

If a write fails after some records have been updated, staff rerun the same business date. Idempotent upserts repair the partial run without creating another logical metric record.

Step 6: Add Approvals, Reminders, and Escalations

This dashboard uses exception acknowledgment rather than a formal transactional approval. The dashboard does not authorize a payment, accounting entry, purchase, staffing action, or safety decision.

The acknowledgment process is:

1. A Watch, Action Required, or No Data result creates an Open exception.
2. The metric owner reviews the source and drill-down detail.
3. The owner changes Acknowledgement_Status to ACKNOWLEDGED.
4. The owner enters Acknowledged_By, Acknowledged_At, and a resolution note.
5. Once the operational issue is addressed, the owner or operations manager changes the status to RESOLVED.
6. If management accepts the exception for that date, an authorized manager uses WAIVED and records the reason.

The reminder script runs later in the morning. An unacknowledged Watch item goes to its owner. An Action Required or No Data item also copies the configured escalation recipient. Older unresolved exceptions remain eligible for escalation.

For an unavailable owner, the operations manager updates the Metric_Config owner before the reminder run or records a temporary delegate in the operating procedure. Changes should use a named user rather than a shared password.

Parallel approval is not needed because each metric has one accountable owner. If a future measure requires finance and operations approval, add separate approval records rather than storing two people in one status field.

Step 7: Add Documents and File Management

The core workflow does not create a document for every metric. The workbook and Looker Studio report are the authoritative reporting artifacts.

The Drive structure is:

Daily Operations/
  01 System/
    Daily Operations Master
    Looker Studio report shortcut
  02 Procedures/
    Data dictionary
    Daily update procedure
    Exception response procedure
  03 Approved Exports/
  04 Archive/

Recommended file controls include:

• Restrict shared links to the organization or named users.
• Do not use public link sharing.
• Use Drive version history for procedures and supporting documents.
• Name approved exports DailyOps_YYYY-MM-DD_v1.
• Archive exports according to the organization’s retention policy.
• Replace outdated exports rather than presenting several files as current.
• Keep drill-down permissions aligned with dashboard permissions.
• Do not expose a link that gives executives access to restricted employee or customer-level data.

If optional PDF generation is added later, the automation must test file creation, folder permissions, duplicate naming, file size, and failed uploads. Those operations are not part of the core implementation described here.

Step 8: Add Reporting and Operational Views

The Looker Studio report used four pages.

Executive daily view

• Ten scorecards for the selected business date
• Status, actual, target, owner, and last update
• Visible Last_Automation_Run indicator
• Clickable drill-down links
• Filter for date and department

Exception queue

• Action Required first, then No Data, Watch, and On Track
• Owner, exception note, acknowledgment status, and drill-down URL
• Filters for owner, department, and resolution status
• Count of unresolved items

Trend view

• Actual and target by business date
• Status counts over time
• Production attainment, first-time fix rate, overdue jobs, and stockouts
• Thirty-day and ninety-day filters where sufficient history exists

Data-quality and automation view

• No Data metrics
• Missing exception notes
• Latest successful run
• Failed runs
• Metrics by last update time
• Open exceptions by age and owner

A useful calculated field for status sorting is:

CASE
  WHEN Status = "Action Required" THEN 1
  WHEN Status = "No Data" THEN 2
  WHEN Status = "Watch" THEN 3
  WHEN Status = "On Track" THEN 4
  ELSE 5
END

Threshold evaluation remains in Apps Script rather than Looker Studio. This prevents dashboard-specific formulas from becoming a second source of business logic.

Looker Studio may cache connected data. Configure an appropriate freshness setting and test the actual delay. The Last_Automation_Run field identifies when consolidation completed, but it does not guarantee that a viewer’s browser has requested the newest data.

Step 9: Add Security and Governance Controls

• Grant edit access only to users who must enter or manage data.
• Protect configuration, output, and log ranges from accidental changes.
• Store only aggregate staffing and finance measures in the dashboard workbook.
• Use named user accounts and multifactor authentication.
• Store optional API keys in Apps Script Properties, never in worksheet cells or source code.
• Review Drive, workbook, report, and source-link access together.
• Remove former employees promptly and reinstall triggers under the replacement owner.
• Retain automation and email logs according to the organization’s policy.
• Use Drive and organizational backup controls appropriate to the records.
• Review Apps Script executions and unexpected authorization changes.
• Do not send personal, financial-account, customer-level, legal, or safety-sensitive data to an AI service.
• Require human approval before distributing AI-generated commentary.

Spreadsheet protection is intended to prevent mistakes, not to enforce strict separation among editors. Businesses with formal segregation-of-duties requirements should use separate workbooks, a controlled database, or dedicated software.

Step 10: Deploy and Test

1. Create a development copy of the workbook and Looker Studio report.
2. Replace all placeholder IDs, URLs, time-zone values, and email addresses.
3. Run the initialization function in the development workbook.
4. Enter sample rows for at least five business dates.
5. Test every status boundary and all missing-data conditions.
6. Connect the development output tabs to a test Looker Studio report.
7. Run user acceptance testing with one department lead, the operations manager, and an executive viewer.
8. Operate the manual process and dashboard in parallel for at least one reporting cycle.
9. Correct metric definitions and thresholds before production activation.
10. Copy approved configuration to the production workbook.
11. Install production triggers under the designated automation owner.
12. Confirm the first production email and dashboard refresh manually.
13. Document rollback steps, including disabling triggers and returning to the previous management summary.
14. Assign a support owner and backup owner.
15. Communicate the update deadline, exception process, and source-correction procedure to all participants.

The rollback does not require deleting data. Disable the triggers, retain the output for investigation, and use the previous reporting process until the fault is corrected.

Code and Configuration

The implementation requires code because native spreadsheet and dashboard actions do not provide the required cross-tab validation, idempotent upserts, threshold rules, exception synchronization, reminders, and run logging.

Create the master Google Sheets workbook first. Open it, select the Apps Script option from the extensions menu, and paste the following code into the bound project. Replace all values beginning with YOUR_.

The script requests permission to read and write the workbook, manage installable triggers, and send email. It does not read Gmail messages.

const CORE_CONFIG = Object.freeze({
  SPREADSHEET_ID: 'YOUR_SPREADSHEET_ID',
  TIME_ZONE: 'YOUR_IANA_TIME_ZONE',
  DASHBOARD_URL: 'YOUR_LOOKER_STUDIO_REPORT_URL',
  MORNING_RECIPIENTS: ['YOUR_EMAIL_ADDRESS'],
  ERROR_RECIPIENTS: ['YOUR_OPERATIONS_LEADER_EMAIL'],
  SENDER_NAME: 'Daily Operations Monitor'
});

const TABS = Object.freeze({
  CONFIG: 'Metric_Config',
  DAILY: 'Daily_Metrics',
  EXCEPTIONS: 'Exception_Log',
  AUTOMATION_LOG: 'Automation_Log',
  EMAIL_LOG: 'Email_Log',
  HOLIDAYS: 'Holidays'
});

const INPUT_HEADERS = Object.freeze({
  Sales_Input: [
    'Business_Date', 'Bookings_MTD', 'Bookings_Pace_Target',
    'Quotes_Overdue', 'Exception_Note', 'Drilldown_URL',
    'Input_Updated_At'
  ],
  Service_Input: [
    'Business_Date', 'Overdue_Jobs', 'First_Time_Fix_Rate',
    'Unbilled_Completed_Value', 'Exception_Note', 'Drilldown_URL',
    'Input_Updated_At'
  ],
  Staffing_Input: [
    'Business_Date', 'Open_Critical_Shifts', 'Exception_Note',
    'Drilldown_URL', 'Input_Updated_At'
  ],
  Inventory_Input: [
    'Business_Date', 'Critical_Stockouts', 'Material_Shortages_7D',
    'Exception_Note', 'Drilldown_URL', 'Input_Updated_At'
  ],
  Finance_Input: [
    'Business_Date', 'Overdue_AR_Value', 'Exception_Note',
    'Drilldown_URL', 'Input_Updated_At'
  ],
  Production_Input: [
    'Business_Date', 'Units_Completed', 'Units_Planned',
    'Exception_Note', 'Drilldown_URL', 'Input_Updated_At'
  ]
});

const METRIC_HEADERS = [
  'Metric_ID', 'Department', 'Metric_Name', 'Source_Sheet',
  'Actual_Field', 'Target_Field', 'Fixed_Target', 'Evaluation_Type',
  'Warning_Threshold', 'Critical_Threshold', 'Display_Format',
  'Owner_Email', 'Escalation_Email', 'Refresh_By',
  'Acknowledge_By', 'Active'
];

const DAILY_HEADERS = [
  'Record_ID', 'Created_Date', 'Business_Date', 'Metric_ID',
  'Department', 'Metric_Name', 'Actual_Value', 'Target_Value',
  'Variance', 'Status', 'Owner_Email', 'Escalation_Email',
  'Refresh_By', 'Acknowledge_By', 'Last_Updated',
  'Exception_Type', 'Exception_Note', 'Drilldown_URL',
  'Source_Sheet', 'Source_Row', 'External_System_ID',
  'Automation_Status', 'Last_Automation_Run', 'Retry_Count',
  'Error_Message', 'Run_ID'
];

const EXCEPTION_HEADERS = [
  'Exception_ID', 'Record_ID', 'Business_Date', 'Metric_ID',
  'Department', 'Metric_Name', 'Metric_Status', 'Exception_Type',
  'Exception_Note', 'Owner_Email', 'Escalation_Email',
  'Acknowledge_By', 'Drilldown_URL', 'First_Seen', 'Last_Seen',
  'Acknowledgement_Status', 'Acknowledged_By', 'Acknowledged_At',
  'Resolution_Note', 'Resolved_At', 'Run_ID'
];

const AUTOMATION_LOG_HEADERS = [
  'Run_ID', 'Started_At', 'Finished_At', 'Business_Date',
  'Status', 'Records_Processed', 'Exceptions', 'Error_Message'
];

const EMAIL_LOG_HEADERS = [
  'Message_Key', 'Sent_At', 'Type', 'Recipients',
  'Subject', 'Business_Date', 'Run_ID'
];

const HOLIDAY_HEADERS = ['Date', 'Name', 'Active'];

const DEFAULT_METRICS = [
  {
    Metric_ID: 'SALES_BOOKINGS_PACE',
    Department: 'Sales',
    Metric_Name: 'Sales bookings versus pace',
    Source_Sheet: 'Sales_Input',
    Actual_Field: 'Bookings_MTD',
    Target_Field: 'Bookings_Pace_Target',
    Fixed_Target: '',
    Evaluation_Type: 'MIN_RATIO',
    Warning_Threshold: 0.95,
    Critical_Threshold: 0.85,
    Display_Format: 'CURRENCY',
    Owner_Email: 'YOUR_SALES_OWNER_EMAIL',
    Escalation_Email: 'YOUR_OPERATIONS_LEADER_EMAIL',
    Refresh_By: '06:30',
    Acknowledge_By: '09:00',
    Active: true
  },
  {
    Metric_ID: 'SALES_QUOTES_OVERDUE',
    Department: 'Sales',
    Metric_Name: 'Quotes overdue',
    Source_Sheet: 'Sales_Input',
    Actual_Field: 'Quotes_Overdue',
    Target_Field: '',
    Fixed_Target: 0,
    Evaluation_Type: 'MAXIMUM',
    Warning_Threshold: 4,
    Critical_Threshold: 8,
    Display_Format: 'INTEGER',
    Owner_Email: 'YOUR_SALES_OWNER_EMAIL',
    Escalation_Email: 'YOUR_OPERATIONS_LEADER_EMAIL',
    Refresh_By: '06:30',
    Acknowledge_By: '09:00',
    Active: true
  },
  {
    Metric_ID: 'SERVICE_JOBS_OVERDUE',
    Department: 'Service',
    Metric_Name: 'Service jobs overdue',
    Source_Sheet: 'Service_Input',
    Actual_Field: 'Overdue_Jobs',
    Target_Field: '',
    Fixed_Target: 0,
    Evaluation_Type: 'MAXIMUM',
    Warning_Threshold: 2,
    Critical_Threshold: 5,
    Display_Format: 'INTEGER',
    Owner_Email: 'YOUR_SERVICE_OWNER_EMAIL',
    Escalation_Email: 'YOUR_OPERATIONS_LEADER_EMAIL',
    Refresh_By: '06:30',
    Acknowledge_By: '09:00',
    Active: true
  },
  {
    Metric_ID: 'SERVICE_FIRST_TIME_FIX',
    Department: 'Service',
    Metric_Name: 'First-time fix rate',
    Source_Sheet: 'Service_Input',
    Actual_Field: 'First_Time_Fix_Rate',
    Target_Field: '',
    Fixed_Target: 0.92,
    Evaluation_Type: 'MINIMUM',
    Warning_Threshold: 0.90,
    Critical_Threshold: 0.85,
    Display_Format: 'PERCENT',
    Owner_Email: 'YOUR_SERVICE_OWNER_EMAIL',
    Escalation_Email: 'YOUR_OPERATIONS_LEADER_EMAIL',
    Refresh_By: '06:30',
    Acknowledge_By: '09:00',
    Active: true
  },
  {
    Metric_ID: 'SERVICE_UNBILLED_VALUE',
    Department: 'Service',
    Metric_Name: 'Unbilled completed work',
    Source_Sheet: 'Service_Input',
    Actual_Field: 'Unbilled_Completed_Value',
    Target_Field: '',
    Fixed_Target: 0,
    Evaluation_Type: 'MAXIMUM',
    Warning_Threshold: 10000,
    Critical_Threshold: 25000,
    Display_Format: 'CURRENCY',
    Owner_Email: 'YOUR_SERVICE_OWNER_EMAIL',
    Escalation_Email: 'YOUR_OPERATIONS_LEADER_EMAIL',
    Refresh_By: '06:30',
    Acknowledge_By: '09:00',
    Active: true
  },
  {
    Metric_ID: 'STAFFING_CRITICAL_SHIFTS',
    Department: 'Staffing',
    Metric_Name: 'Open critical shifts',
    Source_Sheet: 'Staffing_Input',
    Actual_Field: 'Open_Critical_Shifts',
    Target_Field: '',
    Fixed_Target: 0,
    Evaluation_Type: 'MAXIMUM',
    Warning_Threshold: 0,
    Critical_Threshold: 2,
    Display_Format: 'INTEGER',
    Owner_Email: 'YOUR_STAFFING_OWNER_EMAIL',
    Escalation_Email: 'YOUR_OPERATIONS_LEADER_EMAIL',
    Refresh_By: '06:30',
    Acknowledge_By: '09:00',
    Active: true
  },
  {
    Metric_ID: 'INVENTORY_CRITICAL_STOCKOUTS',
    Department: 'Inventory',
    Metric_Name: 'Critical stockouts',
    Source_Sheet: 'Inventory_Input',
    Actual_Field: 'Critical_Stockouts',
    Target_Field: '',
    Fixed_Target: 0,
    Evaluation_Type: 'MAXIMUM',
    Warning_Threshold: 0,
    Critical_Threshold: 2,
    Display_Format: 'INTEGER',
    Owner_Email: 'YOUR_INVENTORY_OWNER_EMAIL',
    Escalation_Email: 'YOUR_OPERATIONS_LEADER_EMAIL',
    Refresh_By: '06:30',
    Acknowledge_By: '09:00',
    Active: true
  },
  {
    Metric_ID: 'INVENTORY_SHORTAGES_7D',
    Department: 'Inventory',
    Metric_Name: 'Seven-day material shortages',
    Source_Sheet: 'Inventory_Input',
    Actual_Field: 'Material_Shortages_7D',
    Target_Field: '',
    Fixed_Target: 0,
    Evaluation_Type: 'MAXIMUM',
    Warning_Threshold: 0,
    Critical_Threshold: 3,
    Display_Format: 'INTEGER',
    Owner_Email: 'YOUR_INVENTORY_OWNER_EMAIL',
    Escalation_Email: 'YOUR_OPERATIONS_LEADER_EMAIL',
    Refresh_By: '06:30',
    Acknowledge_By: '09:00',
    Active: true
  },
  {
    Metric_ID: 'FINANCE_OVERDUE_AR',
    Department: 'Finance',
    Metric_Name: 'Overdue accounts receivable',
    Source_Sheet: 'Finance_Input',
    Actual_Field: 'Overdue_AR_Value',
    Target_Field: '',
    Fixed_Target: 0,
    Evaluation_Type: 'MAXIMUM',
    Warning_Threshold: 25000,
    Critical_Threshold: 50000,
    Display_Format: 'CURRENCY',
    Owner_Email: 'YOUR_FINANCE_OWNER_EMAIL',
    Escalation_Email: 'YOUR_OPERATIONS_LEADER_EMAIL',
    Refresh_By: '06:30',
    Acknowledge_By: '09:00',
    Active: true
  },
  {
    Metric_ID: 'PRODUCTION_ATTAINMENT',
    Department: 'Production',
    Metric_Name: 'Production attainment',
    Source_Sheet: 'Production_Input',
    Actual_Field: 'Units_Completed',
    Target_Field: 'Units_Planned',
    Fixed_Target: '',
    Evaluation_Type: 'MIN_RATIO',
    Warning_Threshold: 0.95,
    Critical_Threshold: 0.85,
    Display_Format: 'PERCENT',
    Owner_Email: 'YOUR_PRODUCTION_OWNER_EMAIL',
    Escalation_Email: 'YOUR_OPERATIONS_LEADER_EMAIL',
    Refresh_By: '06:30',
    Acknowledge_By: '09:00',
    Active: true
  }
];

function initializeSystem() {
  const ss = getSpreadsheet_();

  Object.keys(INPUT_HEADERS).forEach(function (sheetName) {
    const sheet = ensureSheet_(ss, sheetName);
    ensureHeaders_(sheet, INPUT_HEADERS[sheetName]);
    applyInputValidation_(sheet, INPUT_HEADERS[sheetName]);
  });

  const configSheet = ensureSheet_(ss, TABS.CONFIG);
  ensureHeaders_(configSheet, METRIC_HEADERS);

  if (configSheet.getLastRow() < 2) {
    const rows = DEFAULT_METRICS.map(function (metric) {
      return objectToRow_(metric, METRIC_HEADERS);
    });
    configSheet.getRange(2, 1, rows.length, METRIC_HEADERS.length)
      .setValues(rows);
  }

  const activeColumn = METRIC_HEADERS.indexOf('Active') + 1;
  const activeRule = SpreadsheetApp.newDataValidation()
    .requireCheckbox()
    .setAllowInvalid(false)
    .build();
  configSheet.getRange(
    2,
    activeColumn,
    Math.max(configSheet.getMaxRows() - 1, 1),
    1
  ).setDataValidation(activeRule);

  const dailySheet = ensureSheet_(ss, TABS.DAILY);
  ensureHeaders_(dailySheet, DAILY_HEADERS);
  dailySheet.getRange('B:B').setNumberFormat('yyyy-mm-dd hh:mm:ss');
  dailySheet.getRange('C:C').setNumberFormat('yyyy-mm-dd');
  dailySheet.getRange('O:O').setNumberFormat('yyyy-mm-dd hh:mm:ss');
  dailySheet.getRange('W:W').setNumberFormat('yyyy-mm-dd hh:mm:ss');

  const exceptionSheet = ensureSheet_(ss, TABS.EXCEPTIONS);
  ensureHeaders_(exceptionSheet, EXCEPTION_HEADERS);
  exceptionSheet.getRange('C:C').setNumberFormat('yyyy-mm-dd');
  exceptionSheet.getRange('N:O').setNumberFormat('yyyy-mm-dd hh:mm:ss');
  exceptionSheet.getRange('R:R').setNumberFormat('yyyy-mm-dd hh:mm:ss');
  exceptionSheet.getRange('T:T').setNumberFormat('yyyy-mm-dd hh:mm:ss');

  const acknowledgementColumn =
    EXCEPTION_HEADERS.indexOf('Acknowledgement_Status') + 1;
  const acknowledgementRule = SpreadsheetApp.newDataValidation()
    .requireValueInList(
      ['OPEN', 'ACKNOWLEDGED', 'RESOLVED', 'WAIVED'],
      true
    )
    .setAllowInvalid(false)
    .build();
  exceptionSheet.getRange(
    2,
    acknowledgementColumn,
    Math.max(exceptionSheet.getMaxRows() - 1, 1),
    1
  ).setDataValidation(acknowledgementRule);

  ensureHeaders_(
    ensureSheet_(ss, TABS.AUTOMATION_LOG),
    AUTOMATION_LOG_HEADERS
  );
  ensureHeaders_(
    ensureSheet_(ss, TABS.EMAIL_LOG),
    EMAIL_LOG_HEADERS
  );
  ensureHeaders_(
    ensureSheet_(ss, TABS.HOLIDAYS),
    HOLIDAY_HEADERS
  );

  console.log('Daily operations system initialized in workbook: ' + ss.getId());
}

function installAutomationTriggers() {
  assertCoreConfiguration_();

  const handlers = ['runDailyOperations', 'sendExceptionReminders'];

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

  ScriptApp.newTrigger('runDailyOperations')
    .timeBased()
    .everyDays(1)
    .atHour(6)
    .nearMinute(45)
    .create();

  ScriptApp.newTrigger('sendExceptionReminders')
    .timeBased()
    .everyDays(1)
    .atHour(10)
    .nearMinute(15)
    .create();

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

function runDailyOperations() {
  return runPipeline_(true);
}

function refreshDashboardOnly() {
  return runPipeline_(false);
}

function forceMorningBriefing() {
  const result = runPipeline_(false);

  if (result && result.records && result.records.length) {
    sendMorningBriefing_(
      result.records,
      result.businessDate,
      result.runId,
      true
    );
  }
}

function runPipeline_(sendMorningEmail) {
  const lock = LockService.getScriptLock();

  if (!lock.tryLock(5000)) {
    console.log('Another automation run is already active.');
    return null;
  }

  const runId = Utilities.getUuid();
  const startedAt = new Date();
  const businessDate = todayKey_();
  let ss = null;

  try {
    assertCoreConfiguration_();
    ss = getSpreadsheet_();

    if (!isBusinessDay_(ss, startedAt)) {
      writeAutomationLog_(
        ss,
        runId,
        startedAt,
        new Date(),
        businessDate,
        'SKIPPED_NON_BUSINESS_DAY',
        0,
        0,
        ''
      );
      return {
        runId: runId,
        businessDate: businessDate,
        records: []
      };
    }

    const metrics = getActiveMetricConfigurations_(ss);

    if (!metrics.length) {
      throw new Error('No active metrics were found in Metric_Config.');
    }

    const sourceCache = {};
    const records = metrics.map(function (metric) {
      try {
        return processMetric_(
          ss,
          metric,
          businessDate,
          runId,
          startedAt,
          sourceCache
        );
      } catch (error) {
        console.error(
          'Metric processing failed for ' +
          metric.Metric_ID +
          ': ' +
          error.message
        );
        return buildNoDataRecord_(
          metric,
          businessDate,
          runId,
          startedAt,
          'METRIC_PROCESSING_ERROR',
          error.message
        );
      }
    });

    withRetry_(
      function () {
        upsertDailyRecords_(ss, records);
      },
      'Daily metric upsert',
      3
    );

    withRetry_(
      function () {
        synchronizeExceptions_(ss, records, runId);
      },
      'Exception synchronization',
      3
    );

    if (sendMorningEmail) {
      sendMorningBriefing_(
        records,
        businessDate,
        runId,
        false
      );
    }

    const exceptionCount = records.filter(function (record) {
      return record.Status !== 'On Track';
    }).length;

    writeAutomationLog_(
      ss,
      runId,
      startedAt,
      new Date(),
      businessDate,
      'SUCCESS',
      records.length,
      exceptionCount,
      ''
    );

    return {
      runId: runId,
      businessDate: businessDate,
      records: records
    };
  } catch (error) {
    console.error(error.stack || error.message);

    if (ss) {
      try {
        writeAutomationLog_(
          ss,
          runId,
          startedAt,
          new Date(),
          businessDate,
          'FAILED',
          0,
          0,
          error.stack || error.message
        );
      } catch (logError) {
        console.error('Unable to write failure log: ' + logError.message);
      }
    }

    notifyAutomationFailure_(runId, businessDate, error);
    throw error;
  } finally {
    lock.releaseLock();
  }
}

function processMetric_(
  ss,
  metric,
  businessDate,
  runId,
  runTime,
  sourceCache
) {
  validateMetricConfiguration_(metric);

  if (!sourceCache[metric.Source_Sheet]) {
    sourceCache[metric.Source_Sheet] = loadSourceRow_(
      ss,
      metric.Source_Sheet,
      businessDate
    );
  }

  const sourceResult = sourceCache[metric.Source_Sheet];

  if (sourceResult.error) {
    return buildNoDataRecord_(
      metric,
      businessDate,
      runId,
      runTime,
      sourceResult.errorType,
      sourceResult.error
    );
  }

  const source = sourceResult.data;
  const actual = finiteNonnegativeNumber_(
    source[metric.Actual_Field],
    metric.Actual_Field
  );

  let target = null;

  if (String(metric.Target_Field || '').trim()) {
    target = finiteNonnegativeNumber_(
      source[metric.Target_Field],
      metric.Target_Field
    );
  } else if (
    metric.Fixed_Target !== '' &&
    metric.Fixed_Target !== null
  ) {
    target = finiteNonnegativeNumber_(
      metric.Fixed_Target,
      'Fixed_Target'
    );
  }

  if (metric.Evaluation_Type === 'MIN_RATIO' && (!target || target <= 0)) {
    throw new Error('A positive target is required for MIN_RATIO.');
  }

  const updatedAt = parseRequiredDate_(
    source.Input_Updated_At,
    'Input_Updated_At'
  );

  const drilldownUrl = String(source.Drilldown_URL || '').trim();

  if (!/^https?:\/\/\S+$/i.test(drilldownUrl)) {
    throw new Error('Drilldown_URL must be a valid HTTP or HTTPS URL.');
  }

  const evaluation = evaluateMetric_(metric, actual, target);
  let exceptionNote = String(source.Exception_Note || '').trim();
  let automationStatus = 'COMPLETE';
  let exceptionType = evaluation.exceptionType;
  let errorMessage = '';

  if (
    evaluation.status !== 'On Track' &&
    !exceptionNote
  ) {
    exceptionNote = 'Required exception note was not supplied.';
    exceptionType = 'MISSING_EXCEPTION_NOTE';
    automationStatus = 'MANUAL_REVIEW';
    errorMessage = 'A non-green metric requires an exception note.';
  }

  const recordId =
    'OPS-' +
    businessDate.replace(/-/g, '') +
    '-' +
    metric.Metric_ID;

  return {
    Record_ID: recordId,
    Created_Date: runTime,
    Business_Date: dateFromKey_(businessDate),
    Metric_ID: metric.Metric_ID,
    Department: metric.Department,
    Metric_Name: metric.Metric_Name,
    Actual_Value: actual,
    Target_Value: target === null ? '' : target,
    Variance: target === null ? '' : actual - target,
    Status: evaluation.status,
    Owner_Email: metric.Owner_Email,
    Escalation_Email: metric.Escalation_Email,
    Refresh_By: metric.Refresh_By,
    Acknowledge_By: metric.Acknowledge_By,
    Last_Updated: updatedAt,
    Exception_Type: exceptionType,
    Exception_Note: exceptionNote,
    Drilldown_URL: drilldownUrl,
    Source_Sheet: metric.Source_Sheet,
    Source_Row: sourceResult.rowNumber,
    External_System_ID:
      metric.Source_Sheet + '#' + sourceResult.rowNumber,
    Automation_Status: automationStatus,
    Last_Automation_Run: runTime,
    Retry_Count: 0,
    Error_Message: errorMessage,
    Run_ID: runId
  };
}

function evaluateMetric_(metric, actual, target) {
  const warning = Number(metric.Warning_Threshold);
  const critical = Number(metric.Critical_Threshold);
  let comparisonValue = actual;

  if (metric.Evaluation_Type === 'MIN_RATIO') {
    comparisonValue = actual / target;
  }

  if (
    metric.Evaluation_Type === 'MINIMUM' ||
    metric.Evaluation_Type === 'MIN_RATIO'
  ) {
    if (comparisonValue >= warning) {
      return {status: 'On Track', exceptionType: ''};
    }
    if (comparisonValue >= critical) {
      return {
        status: 'Watch',
        exceptionType: 'THRESHOLD_WARNING'
      };
    }
    return {
      status: 'Action Required',
      exceptionType: 'THRESHOLD_CRITICAL'
    };
  }

  if (metric.Evaluation_Type === 'MAXIMUM') {
    if (comparisonValue <= warning) {
      return {status: 'On Track', exceptionType: ''};
    }
    if (comparisonValue <= critical) {
      return {
        status: 'Watch',
        exceptionType: 'THRESHOLD_WARNING'
      };
    }
    return {
      status: 'Action Required',
      exceptionType: 'THRESHOLD_CRITICAL'
    };
  }

  throw new Error(
    'Unsupported Evaluation_Type: ' + metric.Evaluation_Type
  );
}

function buildNoDataRecord_(
  metric,
  businessDate,
  runId,
  runTime,
  exceptionType,
  message
) {
  return {
    Record_ID:
      'OPS-' +
      businessDate.replace(/-/g, '') +
      '-' +
      metric.Metric_ID,
    Created_Date: runTime,
    Business_Date: dateFromKey_(businessDate),
    Metric_ID: metric.Metric_ID,
    Department: metric.Department,
    Metric_Name: metric.Metric_Name,
    Actual_Value: '',
    Target_Value: '',
    Variance: '',
    Status: 'No Data',
    Owner_Email: metric.Owner_Email,
    Escalation_Email: metric.Escalation_Email,
    Refresh_By: metric.Refresh_By,
    Acknowledge_By: metric.Acknowledge_By,
    Last_Updated: '',
    Exception_Type: exceptionType,
    Exception_Note: message,
    Drilldown_URL: '',
    Source_Sheet: metric.Source_Sheet,
    Source_Row: '',
    External_System_ID: '',
    Automation_Status: 'MANUAL_REVIEW',
    Last_Automation_Run: runTime,
    Retry_Count: 0,
    Error_Message: message,
    Run_ID: runId
  };
}

function loadSourceRow_(ss, sheetName, businessDate) {
  const sheet = ss.getSheetByName(sheetName);

  if (!sheet) {
    return {
      errorType: 'SOURCE_SHEET_MISSING',
      error: 'Source sheet was not found: ' + sheetName
    };
  }

  const rows = readObjects_(sheet);
  const matches = rows.filter(function (row) {
    return dateKey_(row.Business_Date) === businessDate;
  });

  if (!matches.length) {
    return {
      errorType: 'MISSING_INPUT',
      error:
        'No ' +
        sheetName +
        ' row was found for ' +
        businessDate +
        '.'
    };
  }

  if (matches.length > 1) {
    return {
      errorType: 'DUPLICATE_INPUT',
      error:
        'Multiple ' +
        sheetName +
        ' rows were found for ' +
        businessDate +
        '.'
    };
  }

  return {
    data: matches[0],
    rowNumber: matches[0]._rowNumber,
    error: ''
  };
}

function getActiveMetricConfigurations_(ss) {
  const sheet = ss.getSheetByName(TABS.CONFIG);

  if (!sheet) {
    throw new Error('Metric_Config sheet was not found.');
  }

  return readObjects_(sheet).filter(function (row) {
    const value = row.Active;
    return value === true || String(value).toUpperCase() === 'TRUE';
  });
}

function validateMetricConfiguration_(metric) {
  const required = [
    'Metric_ID', 'Department', 'Metric_Name', 'Source_Sheet',
    'Actual_Field', 'Evaluation_Type', 'Warning_Threshold',
    'Critical_Threshold', 'Owner_Email', 'Escalation_Email'
  ];

  required.forEach(function (field) {
    if (
      metric[field] === '' ||
      metric[field] === null ||
      typeof metric[field] === 'undefined'
    ) {
      throw new Error(
        'Metric ' + metric.Metric_ID + ' is missing ' + field + '.'
      );
    }
  });

  if (!isValidEmail_(metric.Owner_Email)) {
    throw new Error('Invalid metric owner email.');
  }

  if (!isValidEmail_(metric.Escalation_Email)) {
    throw new Error('Invalid escalation email.');
  }

  const warning = Number(metric.Warning_Threshold);
  const critical = Number(metric.Critical_Threshold);

  if (!Number.isFinite(warning) || !Number.isFinite(critical)) {
    throw new Error('Metric thresholds must be numeric.');
  }

  if (
    (metric.Evaluation_Type === 'MINIMUM' ||
      metric.Evaluation_Type === 'MIN_RATIO') &&
    warning <= critical
  ) {
    throw new Error(
      'Minimum metrics require Warning_Threshold greater than Critical_Threshold.'
    );
  }

  if (
    metric.Evaluation_Type === 'MAXIMUM' &&
    warning >= critical
  ) {
    throw new Error(
      'Maximum metrics require Warning_Threshold less than Critical_Threshold.'
    );
  }
}

function upsertDailyRecords_(ss, records) {
  const sheet = ss.getSheetByName(TABS.DAILY);
  const existingRows = readObjects_(sheet);
  const rowById = {};

  existingRows.forEach(function (row) {
    if (rowById[row.Record_ID]) {
      throw new Error(
        'Duplicate Record_ID already exists: ' + row.Record_ID
      );
    }
    rowById[row.Record_ID] = row;
  });

  const rowsToAppend = [];

  records.forEach(function (record) {
    const existing = rowById[record.Record_ID];

    if (existing) {
      record.Created_Date =
        existing.Created_Date || record.Created_Date;
      sheet.getRange(
        existing._rowNumber,
        1,
        1,
        DAILY_HEADERS.length
      ).setValues([objectToRow_(record, DAILY_HEADERS)]);
    } else {
      rowsToAppend.push(objectToRow_(record, DAILY_HEADERS));
    }
  });

  if (rowsToAppend.length) {
    sheet.getRange(
      sheet.getLastRow() + 1,
      1,
      rowsToAppend.length,
      DAILY_HEADERS.length
    ).setValues(rowsToAppend);
  }
}

function synchronizeExceptions_(ss, records, runId) {
  const sheet = ss.getSheetByName(TABS.EXCEPTIONS);
  const existingRows = readObjects_(sheet);
  const existingById = {};

  existingRows.forEach(function (row) {
    if (existingById[row.Exception_ID]) {
      throw new Error(
        'Duplicate Exception_ID already exists: ' +
        row.Exception_ID
      );
    }
    existingById[row.Exception_ID] = row;
  });

  const now = new Date();
  const updates = [];

  records.forEach(function (record) {
    const exceptionId = 'EXC-' + record.Record_ID.substring(4);
    const existing = existingById[exceptionId];

    if (record.Status !== 'On Track') {
      let acknowledgementStatus = 'OPEN';
      let acknowledgedBy = '';
      let acknowledgedAt = '';
      let resolutionNote = '';
      let resolvedAt = '';
      let firstSeen = now;

      if (existing) {
        firstSeen = existing.First_Seen || now;
        acknowledgementStatus =
          existing.Acknowledgement_Status || 'OPEN';
        acknowledgedBy = existing.Acknowledged_By || '';
        acknowledgedAt = existing.Acknowledged_At || '';
        resolutionNote = existing.Resolution_Note || '';
        resolvedAt = existing.Resolved_At || '';

        if (
          acknowledgementStatus === 'RESOLVED' ||
          acknowledgementStatus === 'WAIVED'
        ) {
          acknowledgementStatus = 'OPEN';
          acknowledgedBy = '';
          acknowledgedAt = '';
          resolutionNote = '';
          resolvedAt = '';
        }
      }

      updates.push({
        Exception_ID: exceptionId,
        Record_ID: record.Record_ID,
        Business_Date: record.Business_Date,
        Metric_ID: record.Metric_ID,
        Department: record.Department,
        Metric_Name: record.Metric_Name,
        Metric_Status: record.Status,
        Exception_Type: record.Exception_Type,
        Exception_Note: record.Exception_Note,
        Owner_Email: record.Owner_Email,
        Escalation_Email: record.Escalation_Email,
        Acknowledge_By: record.Acknowledge_By,
        Drilldown_URL: record.Drilldown_URL,
        First_Seen: firstSeen,
        Last_Seen: now,
        Acknowledgement_Status: acknowledgementStatus,
        Acknowledged_By: acknowledgedBy,
        Acknowledged_At: acknowledgedAt,
        Resolution_Note: resolutionNote,
        Resolved_At: resolvedAt,
        Run_ID: runId
      });
    } else if (
      existing &&
      existing.Acknowledgement_Status !== 'RESOLVED' &&
      existing.Acknowledgement_Status !== 'WAIVED'
    ) {
      existing.Metric_Status = 'On Track';
      existing.Last_Seen = now;
      existing.Acknowledgement_Status = 'RESOLVED';
      existing.Resolution_Note =
        existing.Resolution_Note ||
        'Automatically resolved after a corrected run returned the metric to On Track.';
      existing.Resolved_At = now;
      existing.Run_ID = runId;
      updates.push(existing);
    }
  });

  const newRows = [];

  updates.forEach(function (item) {
    const existing = existingById[item.Exception_ID];

    if (existing) {
      sheet.getRange(
        existing._rowNumber,
        1,
        1,
        EXCEPTION_HEADERS.length
      ).setValues([objectToRow_(item, EXCEPTION_HEADERS)]);
    } else {
      newRows.push(objectToRow_(item, EXCEPTION_HEADERS));
    }
  });

  if (newRows.length) {
    sheet.getRange(
      sheet.getLastRow() + 1,
      1,
      newRows.length,
      EXCEPTION_HEADERS.length
    ).setValues(newRows);
  }
}

function sendMorningBriefing_(records, businessDate, runId, force) {
  const messageKey = force
    ? 'MORNING-' + businessDate + '-FORCE-' + Utilities.getUuid()
    : 'MORNING-' + businessDate;

  const ss = getSpreadsheet_();

  if (!force && wasEmailSent_(ss, messageKey)) {
    console.log('Morning briefing already sent for ' + businessDate);
    return;
  }

  const sorted = records.slice().sort(function (a, b) {
    const rank = {
      'Action Required': 1,
      'No Data': 2,
      'Watch': 3,
      'On Track': 4
    };
    return (
      (rank[a.Status] || 9) - (rank[b.Status] || 9) ||
      String(a.Department).localeCompare(String(b.Department))
    );
  });

  let html = '<p><b>Daily operations briefing for ' +
    escapeHtml_(businessDate) +
    '</b></p>';

  html += '<p>Dashboard: <a href="' +
    escapeHtml_(CORE_CONFIG.DASHBOARD_URL) +
    '">Open daily operations dashboard</a></p>';

  html += '<table border="1" cellpadding="6" cellspacing="0">';
  html += '<tr><th>Department</th><th>Metric</th>';
  html += '<th>Value</th><th>Status</th>';
  html += '<th>Owner</th><th>Note</th></tr>';

  sorted.forEach(function (record) {
    const link = record.Drilldown_URL
      ? '<a href="' +
        escapeHtml_(record.Drilldown_URL) +
        '">' +
        escapeHtml_(record.Metric_Name) +
        '</a>'
      : escapeHtml_(record.Metric_Name);

    html += '<tr>';
    html += '<td>' + escapeHtml_(record.Department) + '</td>';
    html += '<td>' + link + '</td>';
    html += '<td>' +
      escapeHtml_(formatMetricValue_(record)) +
      '</td>';
    html += '<td>' + escapeHtml_(record.Status) + '</td>';
    html += '<td>' + escapeHtml_(record.Owner_Email) + '</td>';
    html += '<td>' +
      escapeHtml_(record.Exception_Note || '') +
      '</td>';
    html += '</tr>';
  });

  html += '</table>';
  html += '<p>Run ID: ' + escapeHtml_(runId) + '</p>';

  const exceptionCount = records.filter(function (record) {
    return record.Status !== 'On Track';
  }).length;

  const subject =
    'Daily operations: ' +
    exceptionCount +
    ' exception(s) for ' +
    businessDate;

  const textBody =
    'Daily operations briefing for ' +
    businessDate +
    '. Exceptions: ' +
    exceptionCount +
    '. Dashboard: ' +
    CORE_CONFIG.DASHBOARD_URL +
    '. Run ID: ' +
    runId;

  withRetry_(
    function () {
      MailApp.sendEmail({
        to: CORE_CONFIG.MORNING_RECIPIENTS.join(','),
        subject: subject,
        body: textBody,
        htmlBody: html,
        name: CORE_CONFIG.SENDER_NAME
      });
    },
    'Morning email',
    3
  );

  recordEmail_(
    ss,
    messageKey,
    'MORNING',
    CORE_CONFIG.MORNING_RECIPIENTS.join(','),
    subject,
    businessDate,
    runId
  );
}

function sendExceptionReminders() {
  const lock = LockService.getScriptLock();

  if (!lock.tryLock(5000)) {
    console.log('Another automation run is already active.');
    return;
  }

  try {
    assertCoreConfiguration_();
    const ss = getSpreadsheet_();
    const now = new Date();

    if (!isBusinessDay_(ss, now)) {
      return;
    }

    const today = todayKey_();
    const sheet = ss.getSheetByName(TABS.EXCEPTIONS);
    const exceptions = readObjects_(sheet).filter(function (item) {
      const status = String(
        item.Acknowledgement_Status || 'OPEN'
      ).toUpperCase();
      const itemDate = dateKey_(item.Business_Date);

      return (
        itemDate &&
        itemDate <= today &&
        status !== 'RESOLVED' &&
        status !== 'WAIVED'
      );
    });

    const groups = {};

    exceptions.forEach(function (item) {
      const owner = String(item.Owner_Email || '').trim().toLowerCase();

      if (!isValidEmail_(owner)) {
        console.error(
          'Skipping exception with invalid owner: ' +
          item.Exception_ID
        );
        return;
      }

      if (!groups[owner]) {
        groups[owner] = [];
      }

      groups[owner].push(item);
    });

    Object.keys(groups).forEach(function (owner) {
      const messageKey =
        'REMINDER-' + today + '-' + owner.toLowerCase();

      if (wasEmailSent_(ss, messageKey)) {
        return;
      }

      const items = groups[owner];
      const escalationRecipients = [];

      items.forEach(function (item) {
        if (
          item.Metric_Status === 'Action Required' ||
          item.Metric_Status === 'No Data' ||
          dateKey_(item.Business_Date) < today
        ) {
          const escalation = String(
            item.Escalation_Email || ''
          ).trim();

          if (
            isValidEmail_(escalation) &&
            escalationRecipients.indexOf(escalation) === -1
          ) {
            escalationRecipients.push(escalation);
          }
        }
      });

      let html = '<p>The following daily operations exceptions remain open.</p>';
      html += '<ul>';

      items.forEach(function (item) {
        html += '<li><b>' +
          escapeHtml_(item.Metric_Name) +
          ':</b> ' +
          escapeHtml_(item.Metric_Status) +
          ' for ' +
          escapeHtml_(dateKey_(item.Business_Date)) +
          '. ' +
          escapeHtml_(item.Exception_Note || '') +
          '</li>';
      });

      html += '</ul>';
      html += '<p><a href="' +
        escapeHtml_(CORE_CONFIG.DASHBOARD_URL) +
        '">Open the daily operations dashboard</a></p>';

      const subject =
        'Action requested: ' +
        items.length +
        ' open operations exception(s)';

      const options = {
        to: owner,
        subject: subject,
        body:
          'You have ' +
          items.length +
          ' open daily operations exception(s). Dashboard: ' +
          CORE_CONFIG.DASHBOARD_URL,
        htmlBody: html,
        name: CORE_CONFIG.SENDER_NAME
      };

      if (escalationRecipients.length) {
        options.cc = escalationRecipients.join(',');
      }

      withRetry_(
        function () {
          MailApp.sendEmail(options);
        },
        'Exception reminder for ' + owner,
        3
      );

      recordEmail_(
        ss,
        messageKey,
        'EXCEPTION_REMINDER',
        owner +
          (escalationRecipients.length
            ? ', cc: ' + escalationRecipients.join(',')
            : ''),
        subject,
        today,
        Utilities.getUuid()
      );
    });
  } finally {
    lock.releaseLock();
  }
}

function testWithSampleData() {
  assertCoreConfiguration_();

  const ss = getSpreadsheet_();
  const businessDate = todayKey_();

  if (!isBusinessDay_(ss, new Date())) {
    throw new Error(
      'Run the sample test on a configured business day.'
    );
  }

  const dateValue = dateFromKey_(businessDate);
  const now = new Date();
  const sampleLink = CORE_CONFIG.DASHBOARD_URL;

  addInputIfMissing_(
    ss.getSheetByName('Sales_Input'),
    businessDate,
    [
      dateValue, 184000, 200000, 3,
      'Bookings are below pace while two orders await confirmation.',
      sampleLink, now
    ]
  );

  addInputIfMissing_(
    ss.getSheetByName('Service_Input'),
    businessDate,
    [
      dateValue, 6, 0.88, 12400,
      'Two jobs require parts and completed work is awaiting billing review.',
      sampleLink, now
    ]
  );

  addInputIfMissing_(
    ss.getSheetByName('Staffing_Input'),
    businessDate,
    [
      dateValue, 0, '', sampleLink, now
    ]
  );

  addInputIfMissing_(
    ss.getSheetByName('Inventory_Input'),
    businessDate,
    [
      dateValue, 1, 4,
      'Four planned assemblies have components due after the required date.',
      sampleLink, now
    ]
  );

  addInputIfMissing_(
    ss.getSheetByName('Finance_Input'),
    businessDate,
    [
      dateValue, 37000,
      'Two invoices are under documented customer review.',
      sampleLink, now
    ]
  );

  addInputIfMissing_(
    ss.getSheetByName('Production_Input'),
    businessDate,
    [
      dateValue, 92, 100,
      'Material shortages reduced planned output.',
      sampleLink, now
    ]
  );

  return refreshDashboardOnly();
}

function addInputIfMissing_(sheet, businessDate, values) {
  const existing = readObjects_(sheet).some(function (row) {
    return dateKey_(row.Business_Date) === businessDate;
  });

  if (!existing) {
    sheet.appendRow(values);
  }
}

function applyInputValidation_(sheet, headers) {
  const rowCount = Math.max(sheet.getMaxRows() - 1, 1);
  const dateRule = SpreadsheetApp.newDataValidation()
    .requireDate()
    .setAllowInvalid(false)
    .build();
  const numberRule = SpreadsheetApp.newDataValidation()
    .requireNumberGreaterThanOrEqualTo(0)
    .setAllowInvalid(false)
    .build();

  headers.forEach(function (header, index) {
    const range = sheet.getRange(2, index + 1, rowCount, 1);

    if (
      header === 'Business_Date' ||
      header === 'Input_Updated_At'
    ) {
      range.setDataValidation(dateRule);
    } else if (
      header !== 'Exception_Note' &&
      header !== 'Drilldown_URL'
    ) {
      range.setDataValidation(numberRule);
    }

    if (header === 'Business_Date') {
      range.setNumberFormat('yyyy-mm-dd');
    }

    if (header === 'Input_Updated_At') {
      range.setNumberFormat('yyyy-mm-dd hh:mm:ss');
    }

    if (header === 'First_Time_Fix_Rate') {
      range.setNumberFormat('0.0%');
    }
  });
}

function isBusinessDay_(ss, date) {
  const dayName = Utilities.formatDate(
    date,
    CORE_CONFIG.TIME_ZONE,
    'EEE'
  );

  if (dayName === 'Sat' || dayName === 'Sun') {
    return false;
  }

  const holidaySheet = ss.getSheetByName(TABS.HOLIDAYS);

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

  const targetDate = Utilities.formatDate(
    date,
    CORE_CONFIG.TIME_ZONE,
    'yyyy-MM-dd'
  );

  const isHoliday = readObjects_(holidaySheet).some(function (row) {
    const active =
      row.Active === true ||
      String(row.Active).toUpperCase() === 'TRUE';

    return active && dateKey_(row.Date) === targetDate;
  });

  return !isHoliday;
}

function writeAutomationLog_(
  ss,
  runId,
  startedAt,
  finishedAt,
  businessDate,
  status,
  recordsProcessed,
  exceptions,
  errorMessage
) {
  const sheet = ss.getSheetByName(TABS.AUTOMATION_LOG);

  sheet.appendRow([
    runId,
    startedAt,
    finishedAt,
    dateFromKey_(businessDate),
    status,
    recordsProcessed,
    exceptions,
    String(errorMessage || '').substring(0, 5000)
  ]);
}

function recordEmail_(
  ss,
  messageKey,
  type,
  recipients,
  subject,
  businessDate,
  runId
) {
  const sheet = ss.getSheetByName(TABS.EMAIL_LOG);

  sheet.appendRow([
    messageKey,
    new Date(),
    type,
    recipients,
    subject,
    dateFromKey_(businessDate),
    runId
  ]);
}

function wasEmailSent_(ss, messageKey) {
  const sheet = ss.getSheetByName(TABS.EMAIL_LOG);

  return readObjects_(sheet).some(function (row) {
    return String(row.Message_Key) === String(messageKey);
  });
}

function notifyAutomationFailure_(runId, businessDate, error) {
  const validRecipients = CORE_CONFIG.ERROR_RECIPIENTS.filter(
    function (email) {
      return isValidEmail_(email) &&
        String(email).indexOf('YOUR_') === -1;
    }
  );

  if (!validRecipients.length) {
    return;
  }

  try {
    MailApp.sendEmail({
      to: validRecipients.join(','),
      subject:
        'Daily operations automation failed for ' + businessDate,
      body:
        'Run ID: ' +
        runId +
        '\n\nError:\n' +
        (error.stack || error.message),
      name: CORE_CONFIG.SENDER_NAME
    });
  } catch (mailError) {
    console.error(
      'Unable to send failure notification: ' +
      mailError.message
    );
  }
}

function withRetry_(operation, label, maximumAttempts) {
  let lastError = null;

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

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

  throw new Error(
    label +
    ' failed after ' +
    maximumAttempts +
    ' attempts. Last error: ' +
    lastError.message
  );
}

function getSpreadsheet_() {
  if (
    !CORE_CONFIG.SPREADSHEET_ID ||
    CORE_CONFIG.SPREADSHEET_ID.indexOf('YOUR_') === 0
  ) {
    throw new Error(
      'Replace CORE_CONFIG.SPREADSHEET_ID before running the script.'
    );
  }

  return withRetry_(
    function () {
      return SpreadsheetApp.openById(
        CORE_CONFIG.SPREADSHEET_ID
      );
    },
    'Open spreadsheet',
    3
  );
}

function ensureSheet_(ss, sheetName) {
  return ss.getSheetByName(sheetName) || ss.insertSheet(sheetName);
}

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

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

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

  const mismatch = headers.some(function (header, index) {
    return String(existing[index]) !== String(header);
  });

  if (mismatch) {
    throw new Error(
      'Header mismatch in sheet ' +
      sheet.getName() +
      '. Correct the header row before continuing.'
    );
  }

  sheet.setFrozenRows(1);
}

function readObjects_(sheet) {
  if (!sheet || sheet.getLastRow() < 2) {
    return [];
  }

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

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

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

    return object;
  }).filter(function (object) {
    return headers.some(function (header) {
      return object[header] !== '';
    });
  });
}

function objectToRow_(object, headers) {
  return headers.map(function (header) {
    return typeof object[header] === 'undefined'
      ? ''
      : object[header];
  });
}

function finiteNonnegativeNumber_(value, fieldName) {
  if (value === '' || value === null) {
    throw new Error(fieldName + ' is required.');
  }

  const number = Number(value);

  if (!Number.isFinite(number) || number < 0) {
    throw new Error(
      fieldName + ' must be a finite nonnegative number.'
    );
  }

  return number;
}

function parseRequiredDate_(value, fieldName) {
  const date = value instanceof Date ? value : new Date(value);

  if (!(date instanceof Date) || Number.isNaN(date.getTime())) {
    throw new Error(fieldName + ' must be a valid date and time.');
  }

  return date;
}

function todayKey_() {
  return Utilities.formatDate(
    new Date(),
    CORE_CONFIG.TIME_ZONE,
    'yyyy-MM-dd'
  );
}

function dateKey_(value) {
  if (value instanceof Date && !Number.isNaN(value.getTime())) {
    return Utilities.formatDate(
      value,
      CORE_CONFIG.TIME_ZONE,
      'yyyy-MM-dd'
    );
  }

  const text = String(value || '').trim();

  if (/^\d{4}-\d{2}-\d{2}$/.test(text)) {
    return text;
  }

  const parsed = new Date(text);

  if (Number.isNaN(parsed.getTime())) {
    return '';
  }

  return Utilities.formatDate(
    parsed,
    CORE_CONFIG.TIME_ZONE,
    'yyyy-MM-dd'
  );
}

function dateFromKey_(key) {
  const parts = String(key).split('-');

  if (parts.length !== 3) {
    throw new Error('Invalid date key: ' + key);
  }

  return new Date(
    Number(parts[0]),
    Number(parts[1]) - 1,
    Number(parts[2]),
    12,
    0,
    0
  );
}

function formatMetricValue_(record) {
  if (record.Actual_Value === '') {
    return 'No data';
  }

  const metric = DEFAULT_METRICS.filter(function (item) {
    return item.Metric_ID === record.Metric_ID;
  })[0];

  const format = metric ? metric.Display_Format : '';

  if (format === 'PERCENT') {
    if (record.Metric_ID === 'PRODUCTION_ATTAINMENT') {
      return record.Target_Value
        ? ((record.Actual_Value / record.Target_Value) * 100)
          .toFixed(1) + '%'
        : 'No data';
    }

    return (Number(record.Actual_Value) * 100).toFixed(1) + '%';
  }

  if (format === 'CURRENCY') {
    return Number(record.Actual_Value).toLocaleString(
      'en-US',
      {style: 'currency', currency: 'USD'}
    );
  }

  return Number(record.Actual_Value).toLocaleString('en-US');
}

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

function isValidEmail_(value) {
  const email = String(value || '').trim();

  return (
    /^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(email) &&
    email.indexOf('YOUR_') === -1
  );
}

function assertCoreConfiguration_() {
  const requiredTextValues = [
    CORE_CONFIG.SPREADSHEET_ID,
    CORE_CONFIG.TIME_ZONE,
    CORE_CONFIG.DASHBOARD_URL
  ];

  requiredTextValues.forEach(function (value) {
    if (!value || String(value).indexOf('YOUR_') !== -1) {
      throw new Error(
        'Replace all required CORE_CONFIG placeholders.'
      );
    }
  });

  if (!CORE_CONFIG.MORNING_RECIPIENTS.length) {
    throw new Error('At least one morning recipient is required.');
  }

  CORE_CONFIG.MORNING_RECIPIENTS
    .concat(CORE_CONFIG.ERROR_RECIPIENTS)
    .forEach(function (email) {
      if (!isValidEmail_(email)) {
        throw new Error(
          'Replace all recipient placeholders with valid emails.'
        );
      }
    });
}

Deployment instructions

1. Replace YOUR_SPREADSHEET_ID with the ID between /d/ and the next slash in the workbook URL.
2. Replace YOUR_IANA_TIME_ZONE with the organization’s approved IANA time-zone identifier.
3. Replace the dashboard URL and every email placeholder.
4. Save the Apps Script project.
5. Set the Apps Script project time zone to match the configured time zone.
6. Run initializeSystem manually.
7. Review the requested permissions and authorize the script.
8. Inspect the generated Metric_Config tab and adjust thresholds, owners, and deadlines.
9. Run testWithSampleData in the development workbook.
10. Inspect Daily_Metrics, Exception_Log, and Automation_Log.
11. Use the Apps Script Executions view to inspect console output and failures.
12. Run installAutomationTriggers only after testing is complete.

Installable triggers run as their creator. Time-based schedules are approximate rather than exact. The script’s business-day and holiday checks prevent routine weekend and configured holiday processing.

Expected output

A successful run produces ten Daily_Metrics records for the business date, zero or more Exception_Log records, one Automation_Log row, and one Email_Log row when the briefing is sent.

Common troubleshooting points

• Authorization error: Reauthorize the project under the designated automation owner.
• Spreadsheet not found: Confirm the spreadsheet ID and the trigger owner’s access.
• Header mismatch: Restore the exact header names rather than renaming generated columns.
• No Data result: Check for a missing row, duplicate date, invalid source value, missing timestamp, or invalid URL.
• Email not sent: Review Apps Script executions, email authorization, recipient addresses, and account quotas.
• Dashboard appears stale: Check Last_Automation_Run first, then the Looker Studio connector freshness and report cache.
• Duplicate email concern: Review Email_Log. Forced briefings intentionally use a new message key.

The script retries transient workbook writes and email sends three times with increasing delays. It does not repeatedly retry indefinitely because persistent authorization, quota, or data problems require human intervention.

Failure Handling and Operational Reliability

The exception log acts as the manual-review queue. Automation_Log is the technical run ledger, while Email_Log records notification attempts that completed successfully.

There is no separate dead-letter service in this small implementation. No Data and Manual Review records serve as the operational dead-letter queue. If volume or integration complexity increases, failed payloads should be stored in a dedicated queue or database table with replay controls.

Idempotency covers metric and exception writes. Email delivery cannot be fully transactional with a spreadsheet update because Gmail sending and sheet logging are separate operations. A narrow possibility remains that an email succeeds immediately before the email-log write fails. Staff should inspect Gmail and Email_Log before forcing another message.

A weekly reconciliation should compare:

• Expected department rows versus submitted rows
• Expected metric count versus Daily_Metrics count
• Successful automation runs versus business days
• Open exceptions versus resolved or waived exceptions
• Email_Log entries versus expected notification days

A Complete Example

On July 14, 2026, Cedar Vale’s inventory lead entered the following row in Inventory_Input:

Business_Date: 2026-07-14
Critical_Stockouts: 1
Material_Shortages_7D: 4
Exception_Note: Four planned assemblies have components due after the required date.
Drilldown_URL: Approved inventory shortage tracker URL
Input_Updated_At: 2026-07-14 06:18

The scheduled script found exactly one Inventory_Input row for the date. It read two active metric configurations from that row.

Critical stockouts

• Metric ID: INVENTORY_CRITICAL_STOCKOUTS
• Actual value: 1
• Warning threshold: 0
• Critical threshold: 2
• Condition evaluated: Maximum
• Status: Watch
• Generated record ID: OPS-20260714-INVENTORY_CRITICAL_STOCKOUTS

Seven-day material shortages

• Metric ID: INVENTORY_SHORTAGES_7D
• Actual value: 4
• Warning threshold: 0
• Critical threshold: 3
• Condition evaluated: Maximum
• Status: Action Required
• Generated record ID: OPS-20260714-INVENTORY_SHORTAGES_7D
• External system reference: Inventory_Input#15

The script assigned both records to the inventory owner from Metric_Config. It copied the exception explanation and drill-down URL into Daily_Metrics.

The Action Required result created exception ID EXC-20260714-INVENTORY_SHORTAGES_7D. The acknowledgment status started as Open.

The morning Gmail summary placed the Action Required metric above Watch and On Track measures. It displayed the inventory owner and included a link to the approved shortage tracker.

The inventory owner opened the tracker, confirmed the four shortages, and recorded that two purchase orders would be expedited while production sequencing would be changed for the other two assemblies. The owner changed the exception to Acknowledged and entered a resolution note.

The operations manager reviewed the plan and later marked the exception Resolved. The historical Daily_Metrics record remained Action Required because that accurately represented the July 14 condition. The exception record documented how the condition was handled.

If the input had contained two rows for July 14, the script would not have selected one arbitrarily. Both inventory metrics would have been No Data until the duplicate was corrected and the process rerun.

Implementation Cost

The following amounts are representative planning assumptions, not verified client pricing or outcomes. Platform licensing depends on the organization’s subscriptions, region, contract, user count, and required governance features.

An incremental software assumption of zero does not mean the system has no cost. Existing subscriptions have an organizational cost, and the dashboard requires setup, testing, ownership, monitoring, and maintenance.

A technically capable internal team could perform the build without the optional professional implementation cost. Its internal labour should still be estimated and compared with competing priorities.

Estimated Time and Cost Savings

The estimate uses the following representative assumptions:

• 22 daily reporting cycles per month
• 110 minutes of combined handling per cycle in the current process
• 25 minutes of routine handling per cycle after automation
• 30% of daily cycles requiring additional exception review
• 20 minutes of review for each exception cycle
• 3 hours of monthly automation maintenance
• $55 loaded hourly labour cost
• $0 incremental recurring core software cost under existing subscriptions
• $9,070 one-time implementation cost when professional implementation is included

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

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

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

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

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

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

Recovered time does not automatically reduce payroll. It may instead provide additional operating capacity, quicker exception handling, less overtime, fewer administrative tasks, and less dependency on one employee.

Non-financial benefits include:

• Clearer metric ownership
• Fewer update-chasing emails
• More consistent threshold application
• Visible missing and stale data
• Faster access to underlying detail
• Better evidence of exception acknowledgment
• More consistent management discussions
• Improved historical trend reporting

Readers should replace the workflow volume, handling times, exception rate, labour rate, maintenance estimate, software allocation, implementation cost, and threshold assumptions with their own figures.

Adding AI to the Automation

AI should be added only after source validation, metric calculations, exception rules, and deterministic notifications work reliably.

The core automation already provides the following benefits without AI:

• Validated inputs
• Common metric definitions
• Threshold calculation
• Unique record creation
• Dashboard refresh
• Exception ownership
• Reminder and escalation routing
• Audit and run logs

AI is useful for converting the small set of aggregate exceptions into a concise narrative draft. It is not needed to determine whether four shortages exceed a threshold of three. That comparison is more reliable, explainable, and economical as a rule.

Other possible AI applications include summarizing longer exception notes, grouping recurring themes, suggesting likely duplicate explanations, and identifying missing context. These should remain advisory.

The Recommended AI Enhancement

The recommended enhancement generates a structured morning briefing draft from the current day’s aggregate metrics. It sends the draft to a reviewer, not directly to the executive distribution list.

• Trigger: Successful completion of the deterministic dashboard refresh
• AI input: Metric ID, department, status, actual value, target, owner, and redacted exception note
• System instruction: Summarize only supplied evidence and do not make operational decisions
• Expected output: Structured JSON containing a headline, summary, priorities, caveats, and confidence value
• Validation: JSON structure, known metric IDs, maximum priority count, and confidence range
• Record update: Store the draft, model identifier, token usage, review status, and errors in Briefing_Drafts
• Human review: Reviewer edits the text and sets Review_Status to APPROVED
• Low confidence: Reviewer receives the draft, but it is not sent automatically
• Prohibited data: Personal data, customer details, account numbers, bank details, legal advice, safety decisions, and confidential case information
• Failure behavior: Retain the deterministic exception email and record the AI failure

Reusable AI prompt

System instruction:

You are drafting a daily operations briefing from validated aggregate metrics.
Use only the supplied facts. Do not invent causes, commitments, deadlines, or
recommended decisions. Prioritize Action Required and No Data items, followed
by Watch items. Keep the language neutral and concise. Do not make payment,
staffing, safety, legal, accounting, or customer commitments. Return only JSON
that matches the required schema.

User prompt:

Business date: {{BUSINESS_DATE}}
Dashboard URL: {{DASHBOARD_URL}}

Validated metrics:
{{METRICS_JSON}}

Create:
1. A short factual headline.
2. A summary of no more than 120 words.
3. Up to five priority items grounded in the supplied metrics.
4. Caveats for missing, uncertain, or incomplete information.
5. A confidence value from 0 to 1 based only on data completeness.

Every priority must reference a supplied metric_id. If no exception requires
action, state that no threshold exception requires immediate action. Do not
infer a root cause unless it appears in the supplied exception note.

Expected structured output

{
  "headline": "Four operating measures need review",
  "summary": "Inventory shortages and overdue service work require attention. Sales bookings remain below pace, while staffing has no current exception. Review the linked source records before assigning operational actions.",
  "priorities": [
    {
      "metric_id": "INVENTORY_SHORTAGES_7D",
      "status": "Action Required",
      "owner_email": "[email protected]",
      "action": "Review the four reported shortages and the linked tracker.",
      "evidence": "Actual value 4 exceeded the critical threshold of 3."
    }
  ],
  "caveats": [
    "The briefing uses aggregate measures and does not establish root cause."
  ],
  "confidence": 0.91
}

The following optional Apps Script file uses the OpenAI Responses API. Store the API key in Apps Script Properties with the name OPENAI_API_KEY. Replace the model placeholder with an approved model available to the organization. Review the current official Responses API documentation before deployment because available models and API details can change.

This code is added to the same Apps Script project as the core code. It relies on the tested helper functions and constants already provided.

const AI_CONFIG = Object.freeze({
  API_URL: 'https://api.openai.com/v1/responses',
  MODEL: 'YOUR_APPROVED_OPENAI_MODEL',
  REVIEWER_EMAIL: 'YOUR_BRIEFING_REVIEWER_EMAIL',
  SHEET_NAME: 'Briefing_Drafts',
  MAX_METRICS: 20
});

const AI_DRAFT_HEADERS = [
  'Draft_ID', 'Business_Date', 'Created_At', 'Model',
  'Input_Record_Count', 'Input_Tokens', 'Output_Tokens',
  'Draft_JSON', 'Draft_Text', 'Review_Status',
  'Reviewer_Email', 'Reviewed_At', 'Reviewer_Notes',
  'Sent_At', 'Error_Message'
];

function runDailyOperationsWithAiDraft() {
  const result = runPipeline_(true);

  if (result && result.records && result.records.length) {
    generateReviewedBriefingDraft();
  }
}

function installAiEnabledTrigger() {
  assertAiConfiguration_();

  const handlerNames = [
    'runDailyOperations',
    'runDailyOperationsWithAiDraft'
  ];

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

  ScriptApp.newTrigger('runDailyOperationsWithAiDraft')
    .timeBased()
    .everyDays(1)
    .atHour(6)
    .nearMinute(45)
    .create();

  console.log('AI-enabled daily trigger installed.');
}

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Daily Operations')
    .addItem(
      'Generate AI briefing draft',
      'generateReviewedBriefingDraft'
    )
    .addItem(
      'Send approved briefing',
      'sendApprovedBriefing'
    )
    .addToUi();
}

function generateReviewedBriefingDraft() {
  assertCoreConfiguration_();
  assertAiConfiguration_();

  const ss = getSpreadsheet_();
  const businessDate = todayKey_();
  const dailySheet = ss.getSheetByName(TABS.DAILY);

  const records = readObjects_(dailySheet).filter(function (row) {
    return dateKey_(row.Business_Date) === businessDate;
  });

  if (!records.length) {
    throw new Error(
      'No consolidated metrics were found for ' + businessDate + '.'
    );
  }

  if (records.length > AI_CONFIG.MAX_METRICS) {
    throw new Error(
      'Metric count exceeds the configured AI input limit.'
    );
  }

  const safeMetrics = records.map(function (record) {
    return {
      metric_id: String(record.Metric_ID),
      department: String(record.Department),
      metric_name: String(record.Metric_Name),
      status: String(record.Status),
      actual_value:
        record.Actual_Value === '' ? null : Number(record.Actual_Value),
      target_value:
        record.Target_Value === '' ? null : Number(record.Target_Value),
      owner_email: String(record.Owner_Email),
      exception_note: redactAggregateNote_(
        String(record.Exception_Note || '')
      )
    };
  });

  const systemInstruction =
    'You are drafting a daily operations briefing from validated ' +
    'aggregate metrics. Use only the supplied facts. Do not invent ' +
    'causes, commitments, deadlines, or recommended decisions. ' +
    'Prioritize Action Required and No Data items, followed by Watch ' +
    'items. Keep the language neutral and concise. Do not make payment, ' +
    'staffing, safety, legal, accounting, or customer commitments. ' +
    'Return only JSON that matches the required schema.';

  const userPrompt =
    'Business date: ' +
    businessDate +
    '\nDashboard URL: ' +
    CORE_CONFIG.DASHBOARD_URL +
    '\n\nValidated metrics:\n' +
    JSON.stringify(safeMetrics, null, 2) +
    '\n\nCreate a short factual headline, a summary of no more than ' +
    '120 words, up to five grounded priority items, caveats for ' +
    'incomplete information, and a confidence value from 0 to 1. ' +
    'Every priority must reference a supplied metric_id. Do not infer ' +
    'a root cause unless it appears in the exception note.';

  const schema = {
    type: 'object',
    additionalProperties: false,
    properties: {
      headline: {type: 'string'},
      summary: {type: 'string'},
      priorities: {
        type: 'array',
        maxItems: 5,
        items: {
          type: 'object',
          additionalProperties: false,
          properties: {
            metric_id: {type: 'string'},
            status: {type: 'string'},
            owner_email: {type: 'string'},
            action: {type: 'string'},
            evidence: {type: 'string'}
          },
          required: [
            'metric_id',
            'status',
            'owner_email',
            'action',
            'evidence'
          ]
        }
      },
      caveats: {
        type: 'array',
        items: {type: 'string'}
      },
      confidence: {
        type: 'number',
        minimum: 0,
        maximum: 1
      }
    },
    required: [
      'headline',
      'summary',
      'priorities',
      'caveats',
      'confidence'
    ]
  };

  const requestBody = {
    model: AI_CONFIG.MODEL,
    input: [
      {
        role: 'system',
        content: [
          {
            type: 'input_text',
            text: systemInstruction
          }
        ]
      },
      {
        role: 'user',
        content: [
          {
            type: 'input_text',
            text: userPrompt
          }
        ]
      }
    ],
    text: {
      format: {
        type: 'json_schema',
        name: 'daily_operations_briefing',
        strict: true,
        schema: schema
      }
    }
  };

  try {
    const apiResult = callOpenAiResponsesApi_(requestBody);
    const outputText = extractResponseText_(apiResult);
    const draftObject = JSON.parse(outputText);

    validateAiDraft_(draftObject, safeMetrics);

    const draftText = renderAiDraft_(draftObject);
    const draftId =
      'AI-' + businessDate.replace(/-/g, '');

    upsertAiDraft_(ss, {
      Draft_ID: draftId,
      Business_Date: dateFromKey_(businessDate),
      Created_At: new Date(),
      Model: AI_CONFIG.MODEL,
      Input_Record_Count: safeMetrics.length,
      Input_Tokens:
        apiResult.usage && apiResult.usage.input_tokens
          ? apiResult.usage.input_tokens
          : '',
      Output_Tokens:
        apiResult.usage && apiResult.usage.output_tokens
          ? apiResult.usage.output_tokens
          : '',
      Draft_JSON: JSON.stringify(draftObject),
      Draft_Text: draftText,
      Review_Status: 'PENDING_REVIEW',
      Reviewer_Email: AI_CONFIG.REVIEWER_EMAIL,
      Reviewed_At: '',
      Reviewer_Notes: '',
      Sent_At: '',
      Error_Message: ''
    });

    sendDraftToReviewer_(
      ss,
      businessDate,
      draftId,
      draftText,
      draftObject.confidence
    );

    return draftObject;
  } catch (error) {
    recordAiFailure_(ss, businessDate, error);
    throw error;
  }
}

function callOpenAiResponsesApi_(requestBody) {
  const apiKey = PropertiesService.getScriptProperties()
    .getProperty('OPENAI_API_KEY');

  if (!apiKey) {
    throw new Error(
      'OPENAI_API_KEY was not found in Apps Script Properties.'
    );
  }

  let lastError = null;

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

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

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

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

    const retryable = status === 429 || status >= 500;

    if (!retryable || attempt === 3) {
      throw lastError;
    }

    const headers = response.getHeaders();
    const retryAfter = Number(
      headers['Retry-After'] || headers['retry-after'] || 0
    );
    const delayMilliseconds = retryAfter > 0
      ? retryAfter * 1000
      : Math.pow(2, attempt - 1) * 2000;

    Utilities.sleep(delayMilliseconds);
  }

  throw lastError;
}

function extractResponseText_(apiResult) {
  if (
    apiResult.output_text &&
    typeof apiResult.output_text === 'string'
  ) {
    return apiResult.output_text;
  }

  const textParts = [];

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

  if (!textParts.length) {
    throw new Error('The AI response did not contain output text.');
  }

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

function validateAiDraft_(draft, metrics) {
  if (
    !draft ||
    typeof draft.headline !== 'string' ||
    typeof draft.summary !== 'string' ||
    !Array.isArray(draft.priorities) ||
    !Array.isArray(draft.caveats) ||
    typeof draft.confidence !== 'number'
  ) {
    throw new Error('AI output failed structural validation.');
  }

  if (draft.confidence < 0 || draft.confidence > 1) {
    throw new Error('AI confidence must be between 0 and 1.');
  }

  if (draft.priorities.length > 5) {
    throw new Error('AI output contains too many priorities.');
  }

  const knownIds = {};
  metrics.forEach(function (metric) {
    knownIds[metric.metric_id] = true;
  });

  draft.priorities.forEach(function (priority) {
    if (!knownIds[priority.metric_id]) {
      throw new Error(
        'AI output referenced an unknown metric ID: ' +
        priority.metric_id
      );
    }
  });
}

function renderAiDraft_(draft) {
  const lines = [
    draft.headline,
    '',
    draft.summary,
    '',
    'Priorities:'
  ];

  if (!draft.priorities.length) {
    lines.push('No priority exception was identified.');
  }

  draft.priorities.forEach(function (priority, index) {
    lines.push(
      (index + 1) +
      '. ' +
      priority.metric_id +
      ' | ' +
      priority.status +
      ' | ' +
      priority.action +
      ' Evidence: ' +
      priority.evidence
    );
  });

  if (draft.caveats.length) {
    lines.push('', 'Caveats:');
    draft.caveats.forEach(function (caveat) {
      lines.push('- ' + caveat);
    });
  }

  lines.push('', 'AI confidence: ' + draft.confidence);

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

function upsertAiDraft_(ss, draft) {
  const sheet = ensureSheet_(ss, AI_CONFIG.SHEET_NAME);
  ensureHeaders_(sheet, AI_DRAFT_HEADERS);

  const rows = readObjects_(sheet);
  const existing = rows.filter(function (row) {
    return row.Draft_ID === draft.Draft_ID;
  })[0];

  if (existing && existing.Sent_At) {
    throw new Error(
      'An approved briefing has already been sent for this date.'
    );
  }

  if (existing) {
    draft.Reviewer_Notes = existing.Reviewer_Notes || '';
    draft.Reviewed_At = existing.Reviewed_At || '';
    sheet.getRange(
      existing._rowNumber,
      1,
      1,
      AI_DRAFT_HEADERS.length
    ).setValues([objectToRow_(draft, AI_DRAFT_HEADERS)]);
  } else {
    sheet.appendRow(objectToRow_(draft, AI_DRAFT_HEADERS));
  }
}

function sendDraftToReviewer_(
  ss,
  businessDate,
  draftId,
  draftText,
  confidence
) {
  const messageKey = 'AI-REVIEW-' + businessDate;

  if (wasEmailSent_(ss, messageKey)) {
    return;
  }

  const subject =
    'Review required: operations briefing draft for ' +
    businessDate;

  MailApp.sendEmail({
    to: AI_CONFIG.REVIEWER_EMAIL,
    subject: subject,
    body:
      draftText +
      '\n\nReview the Briefing_Drafts sheet. Set Review_Status to ' +
      'APPROVED only after checking every statement. Confidence: ' +
      confidence,
    name: CORE_CONFIG.SENDER_NAME
  });

  recordEmail_(
    ss,
    messageKey,
    'AI_DRAFT_REVIEW',
    AI_CONFIG.REVIEWER_EMAIL,
    subject,
    businessDate,
    draftId
  );
}

function sendApprovedBriefing() {
  assertCoreConfiguration_();
  assertAiConfiguration_();

  const ss = getSpreadsheet_();
  const sheet = ss.getSheetByName(AI_CONFIG.SHEET_NAME);

  if (!sheet) {
    throw new Error('Briefing_Drafts sheet was not found.');
  }

  const approved = readObjects_(sheet).filter(function (row) {
    return (
      String(row.Review_Status).toUpperCase() === 'APPROVED' &&
      !row.Sent_At
    );
  }).sort(function (a, b) {
    return b._rowNumber - a._rowNumber;
  });

  if (!approved.length) {
    throw new Error('No unsent approved briefing was found.');
  }

  const draft = approved[0];
  const businessDate = dateKey_(draft.Business_Date);
  const messageKey = 'AI-APPROVED-' + businessDate;

  if (wasEmailSent_(ss, messageKey)) {
    throw new Error(
      'An approved briefing email is already logged for this date.'
    );
  }

  const subject =
    'Reviewed daily operations briefing for ' + businessDate;

  MailApp.sendEmail({
    to: CORE_CONFIG.MORNING_RECIPIENTS.join(','),
    subject: subject,
    body:
      String(draft.Draft_Text) +
      '\n\nDashboard: ' +
      CORE_CONFIG.DASHBOARD_URL,
    name: CORE_CONFIG.SENDER_NAME
  });

  sheet.getRange(
    draft._rowNumber,
    AI_DRAFT_HEADERS.indexOf('Sent_At') + 1
  ).setValue(new Date());

  recordEmail_(
    ss,
    messageKey,
    'AI_APPROVED_BRIEFING',
    CORE_CONFIG.MORNING_RECIPIENTS.join(','),
    subject,
    businessDate,
    draft.Draft_ID
  );
}

function recordAiFailure_(ss, businessDate, error) {
  const sheet = ensureSheet_(ss, AI_CONFIG.SHEET_NAME);
  ensureHeaders_(sheet, AI_DRAFT_HEADERS);

  const failure = {
    Draft_ID:
      'AI-' +
      businessDate.replace(/-/g, '') +
      '-FAILED-' +
      Utilities.getUuid(),
    Business_Date: dateFromKey_(businessDate),
    Created_At: new Date(),
    Model: AI_CONFIG.MODEL,
    Input_Record_Count: '',
    Input_Tokens: '',
    Output_Tokens: '',
    Draft_JSON: '',
    Draft_Text: '',
    Review_Status: 'FAILED',
    Reviewer_Email: AI_CONFIG.REVIEWER_EMAIL,
    Reviewed_At: '',
    Reviewer_Notes: '',
    Sent_At: '',
    Error_Message: String(
      error.stack || error.message
    ).substring(0, 5000)
  };

  sheet.appendRow(objectToRow_(failure, AI_DRAFT_HEADERS));
}

function redactAggregateNote_(text) {
  return String(text || '')
    .replace(
      /[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}/gi,
      '[REDACTED_EMAIL]'
    )
    .replace(/\b\d{9,}\b/g, '[REDACTED_NUMBER]')
    .substring(0, 500);
}

function assertAiConfiguration_() {
  if (
    !AI_CONFIG.MODEL ||
    AI_CONFIG.MODEL.indexOf('YOUR_') !== -1
  ) {
    throw new Error('Replace AI_CONFIG.MODEL.');
  }

  if (!isValidEmail_(AI_CONFIG.REVIEWER_EMAIL)) {
    throw new Error(
      'Replace AI_CONFIG.REVIEWER_EMAIL with a valid address.'
    );
  }
}

After adding the optional code:

1. Add OPENAI_API_KEY
2. Replace the model and reviewer placeholders.
3. Run generateReviewedBriefingDraft
4. Review the Briefing_Drafts row and token-use fields.
5. Test malformed responses by temporarily changing the validation input in a development copy.
6. Confirm that no executive message is sent while Review_Status is PENDING_REVIEW.
7. Edit Draft_Text, set Review_Status to APPROVED, and run sendApprovedBriefing.
8. Only after acceptance testing, run installAiEnabledTrigger

If the AI service fails, the deterministic dashboard and exception summary remain available. The failure is logged, and no unreviewed narrative is sent.

Benefits of the AI Enhancement

The AI enhancement can reduce the time required to turn a structured exception table into readable commentary. It can also impose a consistent briefing format across different reviewers.

AI-specific benefits include:

• Less manual drafting time
• Consistent ordering of critical, missing, and warning items
• Shorter summaries of several exception notes
• Faster identification of caveats and incomplete context
• Structured priority lists for review

AI does not create the dashboard, validate the source, calculate thresholds, assign deterministic statuses, prevent duplicates, or maintain the audit log. Those benefits come from the core automation.

What Remains Rule-Based or Human-Controlled

Estimating the Additional Value of AI

The optional estimate assumes the core process includes eight minutes of manual briefing drafting per daily cycle.

• 22 daily cycles per month
• 8 minutes of briefing work without AI
• 3 minutes of human review for every AI draft
• 15% of drafts requiring 3 additional correction minutes
• 5% AI failure rate requiring 6 minutes of fallback work
• $55 loaded hourly labour cost
• $6 monthly AI usage allowance