Tracking Strategic Initiatives, Risks, and Benefits

The Business Situation

Northvale Technical Services is a fictional 95-person business that installs and maintains specialized industrial equipment. Its leadership team manages a portfolio of operational improvement, customer service, technology, workforce, and commercial initiatives.

The strategy function consists of a strategy director and one portfolio analyst. Six executives act as initiative sponsors, while approximately 14 department managers own delivery. The leadership team typically has 24 active initiatives, 70 open or recently completed milestones, 30 open risks, and approximately 104 progress updates due each month.

Northvale already uses Google Workspace. Initiative proposals were submitted through email, tracked in separate Google Sheets, discussed in leadership meetings, and summarized manually in presentation slides. Supporting documents were stored in different Google Drive folders.

Leadership could approve an initiative, but the information generated afterward was difficult to govern. Progress updates, dependencies, risks, expected benefits, milestone changes, and decisions became scattered across emails, meeting notes, and departmental spreadsheets.

The strategy director needed one governed tracker that could answer practical questions:

• Which initiatives support each strategic objective?
• Who is accountable for the initiative and who is the executive sponsor?
• Which milestones are late or blocked?
• Which risks require leadership attention?
• Which initiatives depend on work owned by another department?
• What decisions are pending, and who must make them?
• Are expected benefits supported by measurable KPIs?
• Which owners have not provided a current update?
• Has the automation failed to process any submission?

The selected design standardizes inputs through Google Forms, records the portfolio in Google Sheets, processes workflow through Google Apps Script, stores evidence in Google Drive, sends notifications through Google Workspace email, and presents executive reporting in Looker Studio.

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

The Existing Process

The original process followed this sequence:

1. A department manager emailed an initiative proposal to an executive sponsor.
2. The sponsor requested revisions through email or comments in a document.
3. Once leadership approved the proposal, the strategy analyst added a row to a portfolio spreadsheet.
4. The initiative owner maintained a separate project plan or departmental spreadsheet.
5. Before each leadership meeting, the analyst emailed owners and requested progress updates.
6. Owners replied in different formats, including email paragraphs, slide pages, spreadsheet links, and meeting notes.
7. The analyst interpreted the updates, copied information into the portfolio sheet, and manually rebuilt executive slides.
8. Risks, dependencies, and decisions were discussed in meetings but were not always added to a persistent log.
9. Benefit information was reviewed inconsistently because baseline values, targets, units, and calculation directions were not standardized.

The spreadsheet was not inherently the problem. The main weaknesses were the absence of controlled intake, relational records, consistent validation, workflow automation, and an operational exception process.

The business needed to change the process because portfolio volume had reached a point where manual follow-up was unreliable. Leadership also wanted to compare strategic investment, delivery health, risk exposure, and benefit realization without commissioning a new presentation for every review.

What the New System Needed to Do

The system also needed to stay proportionate to a 95-person company. The strategy team did not need a large project portfolio management platform at this stage, but it did need stronger controls than an unstructured spreadsheet could provide.

Implementation Approaches Considered

Improving the existing spreadsheet

Adding dropdowns, conditional formatting, and protected ranges would improve consistency. It would not solve fragmented intake, reminders, relational child records, duplicate submissions, or failure recovery. This option was insufficient because the central problem was workflow governance, not sheet appearance.

Using Google Workspace with Apps Script

This approach retained tools already understood by staff. Google Forms provided controlled intake, Sheets provided transparent records, Apps Script handled identifiers and workflow, Drive stored evidence, and Looker Studio provided reporting. It required disciplined implementation but matched the company’s volume and internal support capacity.

Using a no-code relational database

A no-code database could provide better native relationships and interfaces. Northvale did not select it because the immediate requirements could be met inside its existing productivity environment, and leadership wanted to avoid introducing another data platform before the governance process was stable.

Purchasing dedicated software

A dedicated strategy execution or project portfolio management product would become attractive if Northvale later required complex resource planning, scenario modeling, formal stage gates, extensive integrations, or vendor-supported regulatory controls. Those requirements were not yet strong enough to justify a larger implementation.

Building a custom application

A custom application offered the greatest interface flexibility, but the development and support burden was disproportionate to 24 active initiatives. It was not selected.

The Selected Solution

Northvale selected a Google Workspace implementation centered on Google Sheets and Apps Script.

The implementation retained Google Workspace rather than replacing it. The process removed manual ID creation, copying form submissions into portfolio tabs, preparing routine reminder emails, calculating health indicators, and rebuilding common status views.

Several decisions remained human-controlled:

• An executive sponsor or authorized strategy approver approves or rejects an initiative.
• A sponsor confirms final initiative closure.
• Leadership accepts high-impact risks.
• Authorized leaders approve budget changes.
• Business owners confirm whether a benefit has actually been realized.
• The strategy director resolves suspected duplicate proposals and disputed status information.

System Architecture and Data Flow

• Intake: Google Forms for initiative proposals and conditional portfolio updates.
• System of record: A governed Google Sheets workbook with one tab per entity.
• Automation layer: Spreadsheet-bound Google Apps Script using installable form and time-based triggers.
• Document storage: A restricted Google Drive portfolio folder with one subfolder per initiative.
• Notifications: Email sent by the account that owns the Apps Script triggers.
• Reporting: Looker Studio connected to controlled workbook tabs.
• AI layer: Optional API call from Apps Script after the rule-based workflow is operating reliably.

1. Proposal submission: An employee submits the Initiative Intake Form. Google Forms validates required questions and writes the original response to its linked response tab.
2. Event trigger: An installable spreadsheet form-submit trigger passes the response row and named values to Apps Script.
3. Event registration: The script creates an event key from the response sheet ID and row number. It checks the Processed_Events tab before doing any work.
4. Validation: The script validates email addresses, dates, numeric values, KPI direction, and possible duplicates. Invalid submissions enter a failed state for review.
5. Record creation: The script generates an initiative ID such as SI-2026-0007, creates the main record, optionally creates an initial benefit record, and stores the source event key.
6. Document setup: Apps Script creates an initiative folder under the configured Drive root. The returned folder URL is saved in the initiative record.
7. Approval request: The sponsor and strategy function receive the initiative ID, proposal summary, and a link to the Portfolio Update Form. Approval remains a human decision.
8. Portfolio update: An authorized user selects an update type such as Progress, Milestone, Risk, Dependency, Decision, or Benefit. Conditional form sections collect the relevant fields.
9. Transformation and scoring: Apps Script normalizes values, updates the appropriate child tab, recalculates risk exposure and benefit attainment, and updates the initiative health score.
10. Notification: Material exceptions such as an off-track status, high risk, overdue decision, or request for help generate a targeted email.
11. Scheduled monitoring: A daily trigger checks update due dates. It sends reminders one day before or on the due date, then escalates at two and five overdue days.
12. Reporting: Looker Studio reads governed tabs from Sheets. Dashboard viewers see the latest completed automation output when the data source refreshes.
13. Failure path: Failed events are marked in Processed_Events and Automation_Log. The strategy owner receives an error notification and can retry the stored response after correcting the cause.

Data Structure

The workbook uses a parent-child structure. One initiative can have many milestones, risks, dependencies, decisions, benefits, and progress updates. Every child record contains an Initiative ID that references the Initiatives tab.

Google Sheets does not enforce database foreign keys. Apps Script therefore performs the equivalent validation before creating or updating a child record. It confirms that the Initiative ID exists and that an existing child ID belongs to the stated initiative.

Workflow Statuses and Ownership

A record moves backward when an approver returns it for more information, when a closure proposal is rejected, or when evidence contradicts the reported status. These changes require a decision record or an authorized correction with explanatory notes.

Risk acceptance, initiative approval, budget change, and final closure cannot be inferred from a score. They require a named human approver and a dated decision record.

Step-by-Step Implementation

Step 1: Prepare the Accounts and Permissions

1. Create or identify a Google Workspace account that will own the Apps Script project and install the triggers. Use a role-based automation account where licensing and organizational policy permit it.
2. Confirm that the account can create and edit Google Forms, Google Sheets, Google Drive folders, Apps Script projects, and Looker Studio data sources.
3. Create a restricted Drive folder named Strategic Portfolio. Grant edit access only to the strategy team and approved document managers.
4. Create a Google Sheets workbook named Strategic Initiative Register inside the restricted portfolio folder.
5. Create an intake form owner group consisting of the strategy director and backup analyst.
6. Create a dashboard viewer group for executives and a smaller dashboard editor group for the strategy and analytics team.
7. Identify the automation notification address, strategy mailbox, authorized strategy approvers, active portfolio editors, and test users.
8. Confirm Apps Script execution, email-recipient, URL Fetch, and runtime quotas against the organization’s current Google documentation and expected volume.
9. Create a separate test workbook and test forms. Do not test destructive changes against the live portfolio.

The Apps Script project requests authorization to read and write the workbook, access the configured Drive folder, send email, manage installable triggers, and call an external API if the optional AI feature is enabled.

Step 2: Build the Intake

Create two Google Forms and link both forms to the same Strategic Initiative Register workbook. Rename the generated response tabs to Initiative Intake Responses and Portfolio Update Responses.

Initiative Intake Form

Enable email collection using the organization’s authenticated option. Restrict the form to authenticated organizational users where appropriate. Add a privacy notice explaining that the form captures the submitter’s identity and is used for portfolio governance.

The confirmation message should state that the proposal has been received but is not approved until an authorized decision is recorded. The generated initiative ID and folder link are sent by email after processing.

Portfolio Update Form

Add these common required questions:

• Initiative ID
• Update Type

Configure Update Type as a multiple-choice question with section branching. The choices are Progress, Milestone, Risk, Dependency, Decision, and Benefit.

Use dropdowns or multiple-choice questions for all controlled values. Google Forms should reject missing required fields before submission, while Apps Script performs authoritative cross-field and authorization checks.

File upload questions were not enabled in this design. Form-uploaded files can create ownership and relocation complications. Instead, the automation creates a controlled initiative folder and accepts an Evidence URL. Staff place evidence in the assigned folder and submit its link.

Duplicate prevention occurs at two levels. The event key prevents the same response row from being processed twice, while the intake validation checks for an active initiative with the same normalized name, owner, and target date.

Step 3: Create the System of Record

Open the Strategic Initiative Register workbook. Select the Apps Script option from the Extensions menu, create a spreadsheet-bound project, and paste the complete script provided in the Code and Configuration section.

Replace the configuration placeholders, run configureProject, and then run setupWorkbook. The setup function creates these governed tabs:

• Initiatives
• Milestones
• Risks
• Dependencies
• Decisions
• Benefits
• Update_Log
• Owners
• Config
• Processed_Events
• Automation_Log

The two Google Forms create and maintain their own response tabs. Treat those response tabs as immutable source evidence. Do not reorder or rename form-generated columns after the script has been tested.

Use the naming convention entity-year-sequence:

• SI-2026-0001 for initiatives
• MS-2026-0001 for milestones
• RK-2026-0001 for risks
• DP-2026-0001 for dependencies
• DC-2026-0001 for decisions
• BN-2026-0001 for benefits
• UP-2026-0001 for progress updates

Protect header rows, formula or automation-controlled columns, event logs, and configuration tabs. Give initiative owners form access instead of general spreadsheet edit access.

Create filtered Sheet views for active initiatives, missing updates, high risks, overdue milestones, pending decisions, incomplete benefit records, and failed automation events. Filter views are operational aids and do not replace the Looker Studio dashboard.

Step 4: Connect the Tools

Google Forms authenticates users through Google Workspace when email collection and domain restriction are enabled. Apps Script runs under the authorization granted by the trigger owner. Drive access uses that account’s Google authorization. Looker Studio uses a governed data-source credential choice appropriate to the intended viewer access.

The source response row is never deleted after processing. The generated record ID is stored in Processed_Events, and the source event key is written to the destination record. This creates traceability in both directions.

Step 5: Build the Core Automation

Automation A: Process a new initiative

• Trigger: A new row appears in Initiative Intake Responses through a form submission.
• Conditions: Required values are present, dates and numbers are valid, emails are valid, KPI target differs from baseline, and no likely active duplicate is found.
• Actions: Register event, generate initiative ID, create Drive folder, append initiative, create initial benefit, request approval, recalculate score, and complete event log.
• Fields updated: Approval Status, Overall Status, Next Update Due, Document Folder URL, Automation Status, timestamps, and source event key.
• Notification: Sponsor and strategy address receive the proposal summary and update-form link.
• Exception: Invalid or duplicate submissions are marked Failed and sent to the strategy owner for review.

Automation B: Process a portfolio update

• Trigger: A new row appears in Portfolio Update Responses.
• Conditions: Initiative exists, submitter is authorized, update type is recognized, controlled values are valid, and referenced child IDs belong to the initiative.
• Actions: Create or update the appropriate record, preserve the source event key, recalculate initiative metrics, and complete the event log.
• Fields updated: Child status and dates, initiative summary fields, health score, automation status, and last automation run.
• Notification: Material risks, off-track status, help requests, and governance decisions generate targeted messages.
• Exception: The event remains recoverable from the original response row.

Automation C: Recalculate initiative health

The score is deterministic. It does not use AI.

Starting score by reported status:
On Track = 100
At Risk = 70
Off Track = 40
On Hold = 55
Completion Proposed = 80
Pending Approval = 60
Needs Information = 50
Completed = 100

Deductions:
Overdue milestone penalty = minimum of 20 or 5 points per overdue milestone
Risk penalty = minimum of 25 or total open risk exposure divided by 4
Stale update penalty = minimum of 15 or 3 points per day beyond cadence
Benefit penalty = up to 15 points when measured attainment is below 80 percent

Final health score = maximum of 0 or starting score minus deductions

The score helps sort the portfolio. It does not approve, reject, close, fund, or cancel an initiative.

Automation D: Maintain idempotency

The event key combines the numeric response sheet ID and source row. Before processing, Apps Script acquires a script lock and checks Processed_Events. A completed event is ignored. A failed event can be retried. Newly created records also carry the source event key, preventing a retry from generating another initiative or child record after partial completion.

Step 6: Add Approvals, Reminders, and Escalations

Approval is recorded through the Decision section of the Portfolio Update Form.

The workflow supports sequential approval by requiring sponsor approval before work begins. If a second approval is required, such as finance approval for a budget change, record a separate decision with its own owner and due date. This is clearer than merging several approvals into one status.

Parallel approvals can be represented by two Requested decision records. The initiative does not progress until both are Approved. Apps Script can be extended with a decision-group field if parallel approval becomes frequent.

The daily monitor applies these update rules:

• One day before the due date: remind the initiative owner.
• On the due date: remind the owner if no current update has been received.
• Two days overdue: notify the owner and sponsor.
• Five days overdue: notify the owner, sponsor, and strategy address.
• After a valid progress update: set the next due date, clear previous reminder state, and restart the cadence.

If an approver is unavailable, the strategy director adds an authorized delegate to the strategy approver property or records a formal reassignment decision. The former approver is not silently replaced.

A rejection or return records the submitter, decision date, notes, and source event. Final confirmation is sent to the initiative owner and sponsor.

Step 7: Add Documents and File Management

The root folder structure is:

Strategic Portfolio
  SI-2026-0001 - Initiative Name
  SI-2026-0002 - Initiative Name
  SI-2026-0003 - Initiative Name

Within each initiative folder, the strategy team may create standard subfolders such as Charter, Decisions, Delivery Evidence, Risks, and Benefits Evidence. The core script creates only the initiative folder to avoid unnecessary empty structures.

Recommended file naming follows this format:

INITIATIVE-ID_DOCUMENT-TYPE_YYYY-MM-DD_VERSION
SI-2026-0007_BENEFIT-EVIDENCE_2026-06-30_v01
SI-2026-0007_DECISION_2026-07-15_Approved

• Access is inherited from the governed root unless an initiative requires a narrower group.
• Google Drive version history is used instead of overwriting evidence with unrelated files.
• Evidence URL fields connect tracker records to documents.
• Replacing a file does not require changing the tracker link when a Google file retains the same URL.
• Missing evidence leaves the URL blank and keeps benefit or closure review open.
• Duplicate documents are resolved by the initiative owner, not automatically deleted.
• Large files should follow current Workspace storage limits and organizational policy.
• If folder creation fails, the initiative remains in the tracker with Automation Status set to WARNING. The strategy analyst can create the folder manually and add the URL.
• Retention and archival periods follow Northvale’s records policy rather than an arbitrary script rule.

Step 8: Add Reporting and Operational Views

Connect Looker Studio to the governed workbook tabs. Use Initiatives as the primary portfolio source and add Risks, Milestones, Decisions, Benefits, Update_Log, and Processed_Events as separate sources where needed.

Create five report pages:

1. Portfolio overview: Active initiatives, budget baseline, status distribution, strategic theme, owner, sponsor, average health score, and completion forecast.
2. Delivery: Overdue milestones, stale updates, percent complete, upcoming target dates, and initiatives requesting help.
3. Risk and dependency: Open risk count, exposure, high-scoring risks, blocked dependencies, dependency owner, and need-by date.
4. Benefits: KPI baseline, target, actual, attainment, benefit status, realization date, and missing evidence.
5. Governance and automation: Pending decisions, overdue decisions, rejected items, failed events, retry counts, and processing exceptions.

Looker Studio reads the Sheet data according to its configured data-source freshness and report refresh behavior. Apps Script does not push data directly into the report. Dashboard owners should document expected freshness and display a Last Automation Run field so viewers can judge currency.

Step 9: Add Security and Governance Controls

• Use least-privilege access and Google Groups rather than individually maintained sharing where practical.
• Restrict tracker editing to the strategy function and automation account.
• Collect updates through authenticated forms instead of granting broad sheet-edit permissions.
• Protect identifier, score, event, timestamp, and automation-control columns.
• Keep API keys in Apps Script Properties, not cells, code, form descriptions, or email templates.
• Restrict Drive shared links to the organization or named groups.
• Review Apps Script executions, trigger ownership, Drive activity, and Sheet version history.
• Remove former employees from groups, forms, folders, dashboards, and approver lists promptly.
• Back up the workbook according to the organization’s recovery requirements.
• Do not send personal employee information, confidential contracts, credentials, regulated records, or sensitive customer data to the optional AI service.
• Require human review of every AI-generated summary before it is used in an executive decision.
• Retain original form responses and decision records as audit evidence.

The tracker supports governance but is not an accounting ledger, legal records system, safety-management system, or regulated project-control platform. Applicable legal, privacy, financial, employment, and sector-specific requirements must be reviewed before deployment.

Step 10: Deploy and Test

1. Build the forms, workbook, script, folders, and dashboard in a test environment.
2. Create test users representing a submitter, initiative owner, sponsor, strategy approver, unauthorized employee, and automation owner.
3. Load representative sample initiatives without real confidential information.
4. Run the testing checklist later in this article and retain test evidence.
5. Conduct user acceptance testing with the strategy director, portfolio analyst, two initiative owners, and one sponsor.
6. Correct field names, permissions, email text, score thresholds, and report filters.
7. Export or copy the tested configuration into the production workbook.
8. Run configureProject, setupWorkbook, testConfiguration, and installTriggers using the production automation owner.
9. Pilot the process with three initiatives for two update cycles.
10. Reconcile every form response against Processed_Events and the destination tabs.
11. Activate the process for all new initiatives, then migrate current initiatives using controlled intake or a validated migration file.
12. Keep the old tracker read-only during the pilot and rollback period.
13. Publish a short operating guide covering submissions, approvals, evidence, errors, and support contacts.
14. Monitor executions daily during the first two weeks.

Code and Configuration

The following complete Google Apps Script is placed in the spreadsheet-bound Apps Script project attached to the Strategic Initiative Register workbook. It creates the governed tabs, processes both forms, generates IDs, manages idempotency, recalculates scores, sends reminders, logs errors, and includes an optional AI review function.

Replace every placeholder in configureProject. Keep AI disabled until the core workflow has passed testing.

const APP = Object.freeze({
  intakeResponseSheet: 'Initiative Intake Responses',
  updateResponseSheet: 'Portfolio Update Responses',
  sheets: {
    initiatives: 'Initiatives',
    milestones: 'Milestones',
    risks: 'Risks',
    dependencies: 'Dependencies',
    decisions: 'Decisions',
    benefits: 'Benefits',
    updates: 'Update_Log',
    owners: 'Owners',
    config: 'Config',
    events: 'Processed_Events',
    logs: 'Automation_Log'
  }
});

const HEADERS = Object.freeze({
  Initiatives: [
    'Initiative ID', 'Initiative Name', 'Objective', 'Strategic Theme',
    'Sponsor Email', 'Owner Email', 'Start Date', 'Target End Date',
    'Budget Baseline', 'Primary KPI', 'KPI Unit', 'KPI Baseline',
    'KPI Target', 'KPI Direction', 'KPI Actual', 'Benefit Attainment %',
    'Expected Benefit', 'Benefit Type', 'Overall Status', 'Approval Status',
    'Percent Complete', 'Health Score', 'Open Risk Count', 'Risk Exposure',
    'Overdue Milestones', 'Last Update', 'Next Update Due',
    'Update Cadence State', 'Current Summary', 'Help Required',
    'Dependencies Summary', 'Document Folder URL', 'Created Date',
    'Created By', 'Last Updated', 'Last Reminder Date',
    'Last Escalation Level', 'Automation Status', 'Last Automation Run',
    'Retry Count', 'Error Message', 'Notes', 'Source Event Key'
  ],
  Milestones: [
    'Milestone ID', 'Initiative ID', 'Milestone Title', 'Owner Email',
    'Due Date', 'Status', 'Completion Date', 'Evidence URL',
    'Created Date', 'Created By', 'Last Updated', 'Source Event Key'
  ],
  Risks: [
    'Risk ID', 'Initiative ID', 'Risk Title', 'Risk Description',
    'Probability', 'Impact', 'Risk Score', 'Owner Email', 'Risk Response',
    'Target Date', 'Status', 'Created Date', 'Created By', 'Last Updated',
    'Source Event Key'
  ],
  Dependencies: [
    'Dependency ID', 'Initiative ID', 'Dependency Description',
    'Dependent Initiative ID', 'Owner Email', 'Need By Date', 'Status',
    'Created Date', 'Created By', 'Last Updated', 'Source Event Key'
  ],
  Decisions: [
    'Decision ID', 'Initiative ID', 'Decision Type', 'Decision Text',
    'Related Record ID', 'Decision Owner Email', 'Due Date', 'Status',
    'Approver Email', 'Decided Date', 'Decision Notes',
    'Last Reminder Date', 'Escalation Level', 'Created Date', 'Created By',
    'Last Updated', 'Source Event Key'
  ],
  Benefits: [
    'Benefit ID', 'Initiative ID', 'Benefit KPI', 'Benefit Unit',
    'Baseline', 'Target', 'Actual', 'Direction', 'Attainment %',
    'Owner Email', 'Realization Date', 'Status', 'Evidence URL',
    'Created Date', 'Created By', 'Last Updated', 'Source Event Key'
  ],
  Update_Log: [
    'Update ID', 'Initiative ID', 'Reporting Period End', 'Overall Status',
    'Percent Complete', 'Update Summary', 'KPI Actual', 'Confidence',
    'Help Required', 'AI Summary', 'AI Category',
    'AI Missing Information', 'AI Risk Cues', 'AI Confidence',
    'AI Review Status', 'AI Request ID', 'AI Input Tokens',
    'AI Output Tokens', 'Submitted By', 'Submitted Date', 'Processed Date',
    'Source Event Key'
  ],
  Owners: [
    'Email', 'Display Name', 'Role', 'Active', 'Backup Email', 'Last Updated'
  ],
  Config: [
    'Key', 'Value', 'Description'
  ],
  Processed_Events: [
    'Event Key', 'Source Sheet', 'Source Row', 'Status', 'Attempts',
    'First Seen', 'Last Attempt', 'Completed Date', 'Last Error', 'Record ID'
  ],
  Automation_Log: [
    'Timestamp', 'Level', 'Function', 'Event Key', 'Record ID',
    'Message', 'Details'
  ]
});

function configureProject() {
  PropertiesService.getScriptProperties().setProperties({
    SPREADSHEET_ID: 'YOUR_SPREADSHEET_ID',
    ROOT_FOLDER_ID: 'YOUR_FOLDER_ID',
    INTAKE_FORM_URL: 'YOUR_INTAKE_FORM_URL',
    UPDATE_FORM_URL: 'YOUR_UPDATE_FORM_URL',
    DASHBOARD_URL: 'YOUR_LOOKER_STUDIO_URL',
    STRATEGY_EMAIL: 'YOUR_EMAIL_ADDRESS',
    STRATEGY_APPROVER_EMAILS: '[email protected],[email protected]',
    PORTFOLIO_EDITOR_EMAILS: '[email protected],[email protected]',
    COLLECTED_EMAIL_HEADER: 'Email Address',
    AI_ENABLED: 'false',
    OPENAI_API_KEY: 'YOUR_API_KEY',
    OPENAI_MODEL: 'gpt-4o-mini'
  }, true);
}

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

  Object.keys(HEADERS).forEach(function(sheetName) {
    let sheet = ss.getSheetByName(sheetName);
    if (!sheet) {
      sheet = ss.insertSheet(sheetName);
    }

    const headers = HEADERS[sheetName];
    const current = sheet.getLastColumn() > 0
      ? sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
      : [];

    if (sheet.getLastRow() === 0 || current.join('|') !== headers.join('|')) {
      if (sheet.getLastRow() > 1) {
        throw new Error(
          'Sheet ' + sheetName +
          ' contains data but its headers do not match the required schema.'
        );
      }
      sheet.clear();
      sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
    }

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

  seedConfig_();
  applyDataValidation_();
  log_('INFO', 'setupWorkbook', '', '', 'Workbook setup completed', {});
}

function seedConfig_() {
  const defaults = [
    ['UPDATE_CADENCE_DAYS', '7', 'Days between owner progress updates'],
    ['REMINDER_LEAD_DAYS', '1', 'Days before due date for first reminder'],
    ['ESCALATION_LEVEL_1_DAYS', '2', 'Days overdue before sponsor escalation'],
    ['ESCALATION_LEVEL_2_DAYS', '5', 'Days overdue before strategy escalation'],
    ['AI_CONFIDENCE_THRESHOLD', '0.70', 'Minimum confidence before normal AI status']
  ];

  defaults.forEach(function(row) {
    if (!findRecord_(APP.sheets.config, 'Key', row[0])) {
      appendObject_(APP.sheets.config, {
        'Key': row[0],
        'Value': row[1],
        'Description': row[2]
      });
    }
  });
}

function applyDataValidation_() {
  applyListValidation_(
    APP.sheets.initiatives,
    'Overall Status',
    [
      'Pending Approval', 'Needs Information', 'On Track', 'At Risk',
      'Off Track', 'On Hold', 'Completion Proposed', 'Completed',
      'Rejected', 'Cancelled'
    ]
  );

  applyListValidation_(
    APP.sheets.initiatives,
    'Approval Status',
    ['Pending', 'Approved', 'Rejected', 'Needs Information']
  );

  applyListValidation_(
    APP.sheets.milestones,
    'Status',
    ['Not Started', 'In Progress', 'Blocked', 'Complete', 'Cancelled']
  );

  applyListValidation_(
    APP.sheets.risks,
    'Status',
    ['Open', 'Monitoring', 'Mitigated', 'Accepted', 'Closed']
  );

  applyListValidation_(
    APP.sheets.decisions,
    'Status',
    ['Requested', 'Approved', 'Rejected', 'Returned']
  );

  applyListValidation_(
    APP.sheets.benefits,
    'Status',
    ['Planned', 'Measuring', 'Realized', 'Not Realized', 'Cancelled']
  );
}

function applyListValidation_(sheetName, fieldName, values) {
  const sheet = getSheet_(sheetName);
  const map = headerMap_(sheet);
  const column = map[fieldName];

  if (!column) {
    throw new Error('Missing field ' + fieldName + ' on ' + sheetName);
  }

  const rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(values, true)
    .setAllowInvalid(false)
    .build();

  sheet.getRange(2, column, Math.max(sheet.getMaxRows() - 1, 1), 1)
    .setDataValidation(rule);
}

function testConfiguration() {
  const required = [
    'SPREADSHEET_ID', 'ROOT_FOLDER_ID', 'INTAKE_FORM_URL',
    'UPDATE_FORM_URL', 'STRATEGY_EMAIL'
  ];

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

  const ss = getSpreadsheet_();
  if (!ss.getSheetByName(APP.intakeResponseSheet)) {
    throw new Error('Missing response sheet: ' + APP.intakeResponseSheet);
  }
  if (!ss.getSheetByName(APP.updateResponseSheet)) {
    throw new Error('Missing response sheet: ' + APP.updateResponseSheet);
  }

  DriveApp.getFolderById(getProperty_('ROOT_FOLDER_ID'));

  if (String(getProperty_('AI_ENABLED')).toLowerCase() === 'true') {
    const key = getProperty_('OPENAI_API_KEY');
    if (!key || key === 'YOUR_API_KEY') {
      throw new Error('AI is enabled but OPENAI_API_KEY is not configured.');
    }
  }

  const result = {
    spreadsheet: ss.getName(),
    timezone: ss.getSpreadsheetTimeZone(),
    intakeSheet: APP.intakeResponseSheet,
    updateSheet: APP.updateResponseSheet,
    aiEnabled: getProperty_('AI_ENABLED')
  };

  Logger.log(JSON.stringify(result));
  return result;
}

function installTriggers() {
  testConfiguration();

  ScriptApp.getProjectTriggers().forEach(function(trigger) {
    const handler = trigger.getHandlerFunction();
    if (
      handler === 'onPortfolioFormSubmit' ||
      handler === 'dailyPortfolioSweep'
    ) {
      ScriptApp.deleteTrigger(trigger);
    }
  });

  ScriptApp.newTrigger('onPortfolioFormSubmit')
    .forSpreadsheet(getSpreadsheet_())
    .onFormSubmit()
    .create();

  ScriptApp.newTrigger('dailyPortfolioSweep')
    .timeBased()
    .everyDays(1)
    .atHour(7)
    .create();

  log_('INFO', 'installTriggers', '', '', 'Triggers installed', {});
}

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

  const sheet = e.range.getSheet();
  const row = e.range.getRow();
  const namedValues = e.namedValues || buildNamedValues_(sheet, row);

  handleStoredEvent_(sheet, row, namedValues);
}

function handleStoredEvent_(sheet, row, namedValues) {
  const eventKey = sheet.getSheetId() + ':' + row;

  if (!beginEvent_(eventKey, sheet.getName(), row)) {
    log_(
      'INFO',
      'handleStoredEvent_',
      eventKey,
      '',
      'Duplicate completed event ignored',
      {}
    );
    return;
  }

  let recordId = '';

  try {
    if (sheet.getName() === APP.intakeResponseSheet) {
      recordId = processIntake_(namedValues, eventKey);
    } else if (sheet.getName() === APP.updateResponseSheet) {
      recordId = processUpdate_(namedValues, eventKey);
    } else {
      throw new Error('Unrecognized form response sheet: ' + sheet.getName());
    }

    completeEvent_(eventKey, recordId);
    log_(
      'INFO',
      'handleStoredEvent_',
      eventKey,
      recordId,
      'Event processed successfully',
      {}
    );
  } catch (error) {
    const message = String(error && error.message ? error.message : error);
    failEvent_(eventKey, message);
    markRelatedInitiativeError_(namedValues, eventKey, message);
    log_(
      'ERROR',
      'handleStoredEvent_',
      eventKey,
      recordId,
      'Event processing failed',
      { error: message }
    );
    notifyStrategyError_(eventKey, sheet.getName(), row, message);
    throw error;
  }
}

function retryFailedEvent(eventKey) {
  const eventRecord = findRecord_(APP.sheets.events, 'Event Key', eventKey);

  if (!eventRecord) {
    throw new Error('Event key was not found: ' + eventKey);
  }
  if (eventRecord.object['Status'] !== 'FAILED') {
    throw new Error('Only FAILED events can be retried manually.');
  }

  const sourceSheet = getSpreadsheet_()
    .getSheetByName(eventRecord.object['Source Sheet']);
  const sourceRow = Number(eventRecord.object['Source Row']);

  if (!sourceSheet || sourceRow < 2) {
    throw new Error('The original source response could not be found.');
  }

  const namedValues = buildNamedValues_(sourceSheet, sourceRow);
  handleStoredEvent_(sourceSheet, sourceRow, namedValues);
}

function beginEvent_(eventKey, sourceSheet, sourceRow) {
  const lock = LockService.getScriptLock();
  lock.waitLock(30000);

  try {
    const existing = findRecord_(APP.sheets.events, 'Event Key', eventKey);
    const now = new Date();

    if (existing) {
      const status = existing.object['Status'];
      const lastAttempt = toDateOrNull_(existing.object['Last Attempt']);

      if (status === 'COMPLETED') {
        return false;
      }

      if (
        status === 'IN_PROGRESS' &&
        lastAttempt &&
        now.getTime() - lastAttempt.getTime() < 30 * 60 * 1000
      ) {
        return false;
      }

      updateObject_(APP.sheets.events, existing.row, {
        'Status': 'IN_PROGRESS',
        'Attempts': Number(existing.object['Attempts'] || 0) + 1,
        'Last Attempt': now,
        'Last Error': ''
      });
      return true;
    }

    appendObject_(APP.sheets.events, {
      'Event Key': eventKey,
      'Source Sheet': sourceSheet,
      'Source Row': sourceRow,
      'Status': 'IN_PROGRESS',
      'Attempts': 1,
      'First Seen': now,
      'Last Attempt': now,
      'Completed Date': '',
      'Last Error': '',
      'Record ID': ''
    });

    return true;
  } finally {
    lock.releaseLock();
  }
}

function completeEvent_(eventKey, recordId) {
  const record = findRecord_(APP.sheets.events, 'Event Key', eventKey);
  if (!record) {
    throw new Error('Cannot complete missing event ' + eventKey);
  }

  updateObject_(APP.sheets.events, record.row, {
    'Status': 'COMPLETED',
    'Completed Date': new Date(),
    'Last Error': '',
    'Record ID': recordId
  });
}

function failEvent_(eventKey, message) {
  const record = findRecord_(APP.sheets.events, 'Event Key', eventKey);
  if (!record) {
    return;
  }

  updateObject_(APP.sheets.events, record.row, {
    'Status': 'FAILED',
    'Last Error': String(message).substring(0, 2000)
  });
}

function processIntake_(namedValues, eventKey) {
  const existingByEvent = findRecord_(
    APP.sheets.initiatives,
    'Source Event Key',
    eventKey
  );

  if (existingByEvent) {
    return existingByEvent.object['Initiative ID'];
  }

  const submitter = collectedEmail_(namedValues);
  const name = requiredText_(namedValues, 'Initiative Name');
  const objective = requiredText_(namedValues, 'Strategic Objective');
  const theme = controlledValue_(
    namedValues,
    'Strategic Theme',
    ['Customer', 'Operational Excellence', 'People', 'Growth', 'Technology']
  );
  const sponsor = validEmail_(
    requiredText_(namedValues, 'Sponsor Email'),
    'Sponsor Email'
  );
  const owner = validEmail_(
    requiredText_(namedValues, 'Owner Email'),
    'Owner Email'
  );
  const startDate = requiredDate_(namedValues, 'Start Date');
  const targetDate = requiredDate_(namedValues, 'Target End Date');
  const budget = requiredNumber_(
    namedValues,
    'Budget Baseline',
    0,
    Number.MAX_SAFE_INTEGER
  );
  const primaryKpi = requiredText_(namedValues, 'Primary KPI');
  const kpiUnit = requiredText_(namedValues, 'KPI Unit');
  const baseline = requiredNumber_(
    namedValues,
    'KPI Baseline',
    -Number.MAX_SAFE_INTEGER,
    Number.MAX_SAFE_INTEGER
  );
  const target = requiredNumber_(
    namedValues,
    'KPI Target',
    -Number.MAX_SAFE_INTEGER,
    Number.MAX_SAFE_INTEGER
  );
  const direction = controlledValue_(
    namedValues,
    'KPI Direction',
    ['Increase', 'Decrease']
  );
  const expectedBenefit = requiredText_(namedValues, 'Expected Benefit');
  const benefitType = controlledValue_(
    namedValues,
    'Benefit Type',
    [
      'Financial', 'Capacity', 'Customer', 'Risk Reduction',
      'Compliance', 'Employee'
    ]
  );
  const dependencies = optionalText_(namedValues, 'Dependencies Summary');
  const charterUrl = optionalUrl_(namedValues, 'Existing Charter URL');

  if (startDate.getTime() > targetDate.getTime()) {
    throw new Error('Start Date cannot be after Target End Date.');
  }
  if (baseline === target) {
    throw new Error('KPI Target must differ from KPI Baseline.');
  }

  assertNoLikelyDuplicate_(name, owner, targetDate);

  const initiativeId = nextId_('SI');
  const now = new Date();
  let folderUrl = '';
  let automationStatus = 'OK';
  let errorMessage = '';

  try {
    folderUrl = createInitiativeFolder_(initiativeId, name);
  } catch (folderError) {
    automationStatus = 'WARNING';
    errorMessage = 'Folder creation failed: ' + folderError.message;
    log_(
      'WARN',
      'processIntake_',
      eventKey,
      initiativeId,
      errorMessage,
      {}
    );
  }

  appendObject_(APP.sheets.initiatives, {
    'Initiative ID': initiativeId,
    'Initiative Name': name,
    'Objective': objective,
    'Strategic Theme': theme,
    'Sponsor Email': sponsor,
    'Owner Email': owner,
    'Start Date': startDate,
    'Target End Date': targetDate,
    'Budget Baseline': budget,
    'Primary KPI': primaryKpi,
    'KPI Unit': kpiUnit,
    'KPI Baseline': baseline,
    'KPI Target': target,
    'KPI Direction': direction,
    'KPI Actual': '',
    'Benefit Attainment %': '',
    'Expected Benefit': expectedBenefit,
    'Benefit Type': benefitType,
    'Overall Status': 'Pending Approval',
    'Approval Status': 'Pending',
    'Percent Complete': 0,
    'Health Score': 60,
    'Open Risk Count': 0,
    'Risk Exposure': 0,
    'Overdue Milestones': 0,
    'Last Update': '',
    'Next Update Due': addDays_(now, configNumber_('UPDATE_CADENCE_DAYS', 7)),
    'Update Cadence State': 'Not Started',
    'Current Summary': 'Awaiting initiative approval.',
    'Help Required': '',
    'Dependencies Summary': dependencies,
    'Document Folder URL': folderUrl || charterUrl,
    'Created Date': now,
    'Created By': submitter,
    'Last Updated': now,
    'Last Reminder Date': '',
    'Last Escalation Level': 0,
    'Automation Status': automationStatus,
    'Last Automation Run': now,
    'Retry Count': 0,
    'Error Message': errorMessage,
    'Notes': charterUrl ? 'Existing charter: ' + charterUrl : '',
    'Source Event Key': eventKey
  });

  const benefitId = nextId_('BN');
  appendObject_(APP.sheets.benefits, {
    'Benefit ID': benefitId,
    'Initiative ID': initiativeId,
    'Benefit KPI': primaryKpi,
    'Benefit Unit': kpiUnit,
    'Baseline': baseline,
    'Target': target,
    'Actual': '',
    'Direction': direction,
    'Attainment %': '',
    'Owner Email': owner,
    'Realization Date': targetDate,
    'Status': 'Planned',
    'Evidence URL': '',
    'Created Date': now,
    'Created By': submitter,
    'Last Updated': now,
    'Source Event Key': eventKey + ':benefit'
  });

  sendApprovalRequest_(
    initiativeId,
    name,
    objective,
    sponsor,
    owner,
    targetDate,
    budget,
    folderUrl
  );

  return initiativeId;
}

function processUpdate_(namedValues, eventKey) {
  const submitter = collectedEmail_(namedValues);
  const initiativeId = requiredText_(namedValues, 'Initiative ID').toUpperCase();
  const initiative = findRecord_(
    APP.sheets.initiatives,
    'Initiative ID',
    initiativeId
  );

  if (!initiative) {
    throw new Error('Unknown Initiative ID: ' + initiativeId);
  }

  assertCanEditInitiative_(submitter, initiative.object);

  const updateType = controlledValue_(
    namedValues,
    'Update Type',
    ['Progress', 'Milestone', 'Risk', 'Dependency', 'Decision', 'Benefit']
  );

  let recordId = '';

  switch (updateType) {
    case 'Progress':
      recordId = processProgress_(
        namedValues,
        eventKey,
        submitter,
        initiative
      );
      break;
    case 'Milestone':
      recordId = processMilestone_(
        namedValues,
        eventKey,
        submitter,
        initiative
      );
      break;
    case 'Risk':
      recordId = processRisk_(
        namedValues,
        eventKey,
        submitter,
        initiative
      );
      break;
    case 'Dependency':
      recordId = processDependency_(
        namedValues,
        eventKey,
        submitter,
        initiative
      );
      break;
    case 'Decision':
      recordId = processDecision_(
        namedValues,
        eventKey,
        submitter,
        initiative
      );
      break;
    case 'Benefit':
      recordId = processBenefit_(
        namedValues,
        eventKey,
        submitter,
        initiative
      );
      break;
    default:
      throw new Error('Unsupported update type: ' + updateType);
  }

  recalculateInitiative_(initiativeId);
  return recordId;
}

function processProgress_(
  namedValues,
  eventKey,
  submitter,
  initiative
) {
  const existing = findRecord_(APP.sheets.updates, 'Source Event Key', eventKey);
  if (existing) {
    return existing.object['Update ID'];
  }

  const initiativeId = initiative.object['Initiative ID'];
  const periodEnd = requiredDate_(namedValues, 'Reporting Period End');
  const status = controlledValue_(
    namedValues,
    'Overall Status',
    ['On Track', 'At Risk', 'Off Track', 'On Hold', 'Completion Proposed']
  );
  const percent = requiredNumber_(
    namedValues,
    'Percent Complete',
    0,
    100
  );
  const summary = requiredText_(namedValues, 'Update Summary');
  const kpiActual = optionalNumber_(namedValues, 'KPI Actual');
  const confidence = controlledValue_(
    namedValues,
    'Confidence',
    ['High', 'Medium', 'Low']
  );
  const helpRequired = optionalText_(namedValues, 'Help Required');
  const now = new Date();

  const ai = maybeReviewUpdateWithAI_({
    initiativeId: initiativeId,
    objective: initiative.object['Objective'],
    reportedStatus: status,
    percentComplete: percent,
    updateSummary: summary,
    helpRequired: helpRequired
  });

  const updateId = nextId_('UP');

  appendObject_(APP.sheets.updates, {
    'Update ID': updateId,
    'Initiative ID': initiativeId,
    'Reporting Period End': periodEnd,
    'Overall Status': status,
    'Percent Complete': percent,
    'Update Summary': summary,
    'KPI Actual': kpiActual === null ? '' : kpiActual,
    'Confidence': confidence,
    'Help Required': helpRequired,
    'AI Summary': ai.summary,
    'AI Category': ai.category,
    'AI Missing Information': ai.missingInformation.join('; '),
    'AI Risk Cues': ai.riskCues.join('; '),
    'AI Confidence': ai.confidence,
    'AI Review Status': ai.reviewStatus,
    'AI Request ID': ai.requestId,
    'AI Input Tokens': ai.inputTokens,
    'AI Output Tokens': ai.outputTokens,
    'Submitted By': submitter,
    'Submitted Date': now,
    'Processed Date': now,
    'Source Event Key': eventKey
  });

  const changes = {
    'Overall Status': status,
    'Percent Complete': percent,
    'KPI Actual': kpiActual === null
      ? initiative.object['KPI Actual']
      : kpiActual,
    'Last Update': now,
    'Next Update Due': addDays_(
      now,
      configNumber_('UPDATE_CADENCE_DAYS', 7)
    ),
    'Update Cadence State': 'Current',
    'Current Summary': summary,
    'Help Required': helpRequired,
    'Last Updated': now,
    'Last Reminder Date': '',
    'Last Escalation Level': 0,
    'Automation Status': ai.reviewStatus === 'FAILED' ? 'WARNING' : 'OK',
    'Last Automation Run': now,
    'Error Message': ai.reviewStatus === 'FAILED'
      ? 'Optional AI review failed; core update was processed.'
      : ''
  };

  updateObject_(APP.sheets.initiatives, initiative.row, changes);

  if (
    status === 'Off Track' ||
    status === 'At Risk' ||
    helpRequired
  ) {
    sendExceptionNotice_(
      initiative.object,
      status,
      summary,
      helpRequired,
      updateId
    );
  }

  return updateId;
}

function processMilestone_(
  namedValues,
  eventKey,
  submitter,
  initiative
) {
  const sourceExisting = findRecord_(
    APP.sheets.milestones,
    'Source Event Key',
    eventKey
  );
  if (sourceExisting) {
    return sourceExisting.object['Milestone ID'];
  }

  const initiativeId = initiative.object['Initiative ID'];
  const suppliedId = optionalText_(namedValues, 'Milestone ID').toUpperCase();
  const title = requiredText_(namedValues, 'Milestone Title');
  const owner = validEmail_(
    requiredText_(namedValues, 'Milestone Owner Email'),
    'Milestone Owner Email'
  );
  const dueDate = requiredDate_(namedValues, 'Milestone Due Date');
  const status = controlledValue_(
    namedValues,
    'Milestone Status',
    ['Not Started', 'In Progress', 'Blocked', 'Complete', 'Cancelled']
  );
  let completionDate = optionalDate_(namedValues, 'Completion Date');
  const evidenceUrl = optionalUrl_(namedValues, 'Evidence URL');
  const now = new Date();

  if (status === 'Complete' && !completionDate) {
    completionDate = now;
  }

  if (suppliedId) {
    const existing = assertChildBelongs_(
      APP.sheets.milestones,
      'Milestone ID',
      suppliedId,
      initiativeId
    );

    updateObject_(APP.sheets.milestones, existing.row, {
      'Milestone Title': title,
      'Owner Email': owner,
      'Due Date': dueDate,
      'Status': status,
      'Completion Date': completionDate || '',
      'Evidence URL': evidenceUrl,
      'Last Updated': now,
      'Source Event Key': eventKey
    });
    return suppliedId;
  }

  const milestoneId = nextId_('MS');
  appendObject_(APP.sheets.milestones, {
    'Milestone ID': milestoneId,
    'Initiative ID': initiativeId,
    'Milestone Title': title,
    'Owner Email': owner,
    'Due Date': dueDate,
    'Status': status,
    'Completion Date': completionDate || '',
    'Evidence URL': evidenceUrl,
    'Created Date': now,
    'Created By': submitter,
    'Last Updated': now,
    'Source Event Key': eventKey
  });

  return milestoneId;
}

function processRisk_(
  namedValues,
  eventKey,
  submitter,
  initiative
) {
  const sourceExisting = findRecord_(
    APP.sheets.risks,
    'Source Event Key',
    eventKey
  );
  if (sourceExisting) {
    return sourceExisting.object['Risk ID'];
  }

  const initiativeId = initiative.object['Initiative ID'];
  const suppliedId = optionalText_(namedValues, 'Risk ID').toUpperCase();
  const title = requiredText_(namedValues, 'Risk Title');
  const description = requiredText_(namedValues, 'Risk Description');
  const probability = requiredNumber_(namedValues, 'Probability', 1, 5);
  const impact = requiredNumber_(namedValues, 'Impact', 1, 5);
  const owner = validEmail_(
    requiredText_(namedValues, 'Risk Owner Email'),
    'Risk Owner Email'
  );
  const response = requiredText_(namedValues, 'Risk Response');
  const targetDate = requiredDate_(namedValues, 'Risk Target Date');
  const status = controlledValue_(
    namedValues,
    'Risk Status',
    ['Open', 'Monitoring', 'Mitigated', 'Accepted', 'Closed']
  );
  const now = new Date();

  if (status === 'Accepted' && !isAuthorizedApprover_(
    submitter,
    initiative.object
  )) {
    throw new Error(
      'Risk acceptance requires the sponsor or an authorized strategy approver.'
    );
  }

  const values = {
    'Initiative ID': initiativeId,
    'Risk Title': title,
    'Risk Description': description,
    'Probability': probability,
    'Impact': impact,
    'Risk Score': probability * impact,
    'Owner Email': owner,
    'Risk Response': response,
    'Target Date': targetDate,
    'Status': status,
    'Last Updated': now,
    'Source Event Key': eventKey
  };

  if (suppliedId) {
    const existing = assertChildBelongs_(
      APP.sheets.risks,
      'Risk ID',
      suppliedId,
      initiativeId
    );
    updateObject_(APP.sheets.risks, existing.row, values);
    return suppliedId;
  }

  const riskId = nextId_('RK');
  values['Risk ID'] = riskId;
  values['Created Date'] = now;
  values['Created By'] = submitter;
  appendObject_(APP.sheets.risks, values);

  if (probability * impact >= 15) {
    safeSendEmail_({
      to: initiative.object['Sponsor Email'],
      cc: getProperty_('STRATEGY_EMAIL'),
      subject: 'High portfolio risk: ' + riskId,
      body: 'A high-scoring risk was recorded for ' + initiativeId + '.',
      htmlBody:
        '<p>A high-scoring risk was recorded.</p>' +
        '<p><b>Initiative:</b> ' +
        escapeHtml_(initiativeId) + '</p>' +
        '<p><b>Risk:</b> ' +
        escapeHtml_(title) + '</p>' +
        '<p><b>Score:</b> ' +
        String(probability * impact) + '</p>'
    }, 'processRisk_', eventKey, riskId);
  }

  return riskId;
}

function processDependency_(
  namedValues,
  eventKey,
  submitter,
  initiative
) {
  const sourceExisting = findRecord_(
    APP.sheets.dependencies,
    'Source Event Key',
    eventKey
  );
  if (sourceExisting) {
    return sourceExisting.object['Dependency ID'];
  }

  const initiativeId = initiative.object['Initiative ID'];
  const suppliedId = optionalText_(
    namedValues,
    'Dependency ID'
  ).toUpperCase();
  const description = requiredText_(
    namedValues,
    'Dependency Description'
  );
  const dependentId = optionalText_(
    namedValues,
    'Dependent Initiative ID'
  ).toUpperCase();
  const owner = validEmail_(
    requiredText_(namedValues, 'Dependency Owner Email'),
    'Dependency Owner Email'
  );
  const needByDate = requiredDate_(namedValues, 'Need By Date');
  const status = controlledValue_(
    namedValues,
    'Dependency Status',
    ['Open', 'Confirmed', 'Blocked', 'Satisfied', 'Cancelled']
  );
  const now = new Date();

  if (
    dependentId &&
    !findRecord_(APP.sheets.initiatives, 'Initiative ID', dependentId)
  ) {
    throw new Error(
      'Dependent Initiative ID does not exist: ' + dependentId
    );
  }

  const values = {
    'Initiative ID': initiativeId,
    'Dependency Description': description,
    'Dependent Initiative ID': dependentId,
    'Owner Email': owner,
    'Need By Date': needByDate,
    'Status': status,
    'Last Updated': now,
    'Source Event Key': eventKey
  };

  if (suppliedId) {
    const existing = assertChildBelongs_(
      APP.sheets.dependencies,
      'Dependency ID',
      suppliedId,
      initiativeId
    );
    updateObject_(APP.sheets.dependencies, existing.row, values);
    return suppliedId;
  }

  const dependencyId = nextId_('DP');
  values['Dependency ID'] = dependencyId;
  values['Created Date'] = now;
  values['Created By'] = submitter;
  appendObject_(APP.sheets.dependencies, values);
  return dependencyId;
}

function processDecision_(
  namedValues,
  eventKey,
  submitter,
  initiative
) {
  const sourceExisting = findRecord_(
    APP.sheets.decisions,
    'Source Event Key',
    eventKey
  );
  if (sourceExisting) {
    return sourceExisting.object['Decision ID'];
  }

  const initiativeId = initiative.object['Initiative ID'];
  const suppliedId = optionalText_(namedValues, 'Decision ID').toUpperCase();
  const decisionType = controlledValue_(
    namedValues,
    'Decision Type',
    [
      'Initiative Approval', 'General Governance', 'Closure Approval',
      'Risk Acceptance', 'Budget Change'
    ]
  );
  const decisionText = requiredText_(namedValues, 'Decision Text');
  const relatedRecordId = optionalText_(
    namedValues,
    'Related Record ID'
  ).toUpperCase();
  const decisionOwner = validEmail_(
    requiredText_(namedValues, 'Decision Owner Email'),
    'Decision Owner Email'
  );
  const dueDate = requiredDate_(namedValues, 'Decision Due Date');
  const status = controlledValue_(
    namedValues,
    'Decision Status',
    ['Requested', 'Approved', 'Rejected', 'Returned']
  );
  const notes = optionalText_(namedValues, 'Decision Notes');
  const now = new Date();

  if (
    ['Approved', 'Rejected', 'Returned'].indexOf(status) !== -1 &&
    !isAuthorizedApprover_(submitter, initiative.object)
  ) {
    throw new Error(
      'This final decision requires the sponsor or an authorized approver.'
    );
  }

  const values = {
    'Initiative ID': initiativeId,
    'Decision Type': decisionType,
    'Decision Text': decisionText,
    'Related Record ID': relatedRecordId,
    'Decision Owner Email': decisionOwner,
    'Due Date': dueDate,
    'Status': status,
    'Approver Email': status === 'Requested' ? '' : submitter,
    'Decided Date': status === 'Requested' ? '' : now,
    'Decision Notes': notes,
    'Last Reminder Date': '',
    'Escalation Level': 0,
    'Last Updated': now,
    'Source Event Key': eventKey
  };

  let decisionId = suppliedId;

  if (suppliedId) {
    const existing = assertChildBelongs_(
      APP.sheets.decisions,
      'Decision ID',
      suppliedId,
      initiativeId
    );
    updateObject_(APP.sheets.decisions, existing.row, values);
  } else {
    decisionId = nextId_('DC');
    values['Decision ID'] = decisionId;
    values['Created Date'] = now;
    values['Created By'] = submitter;
    appendObject_(APP.sheets.decisions, values);
  }

  applyDecisionOutcome_(
    decisionType,
    status,
    relatedRecordId,
    initiative,
    now
  );

  safeSendEmail_({
    to: initiative.object['Owner Email'],
    cc: initiative.object['Sponsor Email'],
    subject: 'Portfolio decision recorded: ' + decisionId,
    body:
      'Decision ' + decisionId + ' was recorded with status ' + status + '.',
    htmlBody:
      '<p>A portfolio decision was recorded.</p>' +
      '<p><b>Initiative:</b> ' +
      escapeHtml_(initiativeId) + '</p>' +
      '<p><b>Decision:</b> ' +
      escapeHtml_(decisionId) + '</p>' +
      '<p><b>Status:</b> ' +
      escapeHtml_(status) + '</p>'
  }, 'processDecision_', eventKey, decisionId);

  return decisionId;
}

function applyDecisionOutcome_(
  decisionType,
  status,
  relatedRecordId,
  initiative,
  now
) {
  const changes = {
    'Last Updated': now,
    'Last Automation Run': now,
    'Automation Status': 'OK',
    'Error Message': ''
  };

  if (decisionType === 'Initiative Approval') {
    if (status === 'Approved') {
      changes['Approval Status'] = 'Approved';
      changes['Overall Status'] = 'On Track';
      changes['Current Summary'] = 'Initiative approved for delivery.';
    } else if (status === 'Rejected') {
      changes['Approval Status'] = 'Rejected';
      changes['Overall Status'] = 'Rejected';
      changes['Current Summary'] = 'Initiative proposal rejected.';
    } else if (status === 'Returned') {
      changes['Approval Status'] = 'Needs Information';
      changes['Overall Status'] = 'Needs Information';
      changes['Current Summary'] = 'Approval returned for more information.';
    }
  }

  if (
    decisionType === 'Closure Approval' &&
    status === 'Approved'
  ) {
    changes['Overall Status'] = 'Completed';
    changes['Percent Complete'] = 100;
    changes['Current Summary'] = 'Initiative closure approved.';
  }

  if (
    decisionType === 'Risk Acceptance' &&
    status === 'Approved'
  ) {
    if (!relatedRecordId) {
      throw new Error('Risk Acceptance requires a Related Record ID.');
    }

    const risk = assertChildBelongs_(
      APP.sheets.risks,
      'Risk ID',
      relatedRecordId,
      initiative.object['Initiative ID']
    );

    updateObject_(APP.sheets.risks, risk.row, {
      'Status': 'Accepted',
      'Last Updated': now
    });
  }

  updateObject_(APP.sheets.initiatives, initiative.row, changes);
}

function processBenefit_(
  namedValues,
  eventKey,
  submitter,
  initiative
) {
  const sourceExisting = findRecord_(
    APP.sheets.benefits,
    'Source Event Key',
    eventKey
  );
  if (sourceExisting) {
    return sourceExisting.object['Benefit ID'];
  }

  const initiativeId = initiative.object['Initiative ID'];
  const suppliedId = optionalText_(namedValues, 'Benefit ID').toUpperCase();
  const kpi = requiredText_(namedValues, 'Benefit KPI');
  const unit = requiredText_(namedValues, 'Benefit Unit');
  const baseline = requiredNumber_(
    namedValues,
    'Benefit Baseline',
    -Number.MAX_SAFE_INTEGER,
    Number.MAX_SAFE_INTEGER
  );
  const target = requiredNumber_(
    namedValues,
    'Benefit Target',
    -Number.MAX_SAFE_INTEGER,
    Number.MAX_SAFE_INTEGER
  );
  const actual = optionalNumber_(namedValues, 'Benefit Actual');
  const direction = controlledValue_(
    namedValues,
    'Benefit Direction',
    ['Increase', 'Decrease']
  );
  const owner = validEmail_(
    requiredText_(namedValues, 'Benefit Owner Email'),
    'Benefit Owner Email'
  );
  const realizationDate = requiredDate_(namedValues, 'Realization Date');
  const status = controlledValue_(
    namedValues,
    'Benefit Status',
    ['Planned', 'Measuring', 'Realized', 'Not Realized', 'Cancelled']
  );
  const evidenceUrl = optionalUrl_(namedValues, 'Evidence URL');
  const now = new Date();

  if (baseline === target) {
    throw new Error('Benefit target must differ from benefit baseline.');
  }

  const attainment = actual === null
    ? ''
    : calculateAttainment_(baseline, target, actual, direction);

  const values = {
    'Initiative ID': initiativeId,
    'Benefit KPI': kpi,
    'Benefit Unit': unit,
    'Baseline': baseline,
    'Target': target,
    'Actual': actual === null ? '' : actual,
    'Direction': direction,
    'Attainment %': attainment,
    'Owner Email': owner,
    'Realization Date': realizationDate,
    'Status': status,
    'Evidence URL': evidenceUrl,
    'Last Updated': now,
    'Source Event Key': eventKey
  };

  if (suppliedId) {
    const existing = assertChildBelongs_(
      APP.sheets.benefits,
      'Benefit ID',
      suppliedId,
      initiativeId
    );
    updateObject_(APP.sheets.benefits, existing.row, values);
    return suppliedId;
  }

  const benefitId = nextId_('BN');
  values['Benefit ID'] = benefitId;
  values['Created Date'] = now;
  values['Created By'] = submitter;
  appendObject_(APP.sheets.benefits, values);
  return benefitId;
}

function dailyPortfolioSweep() {
  const initiatives = rowsAsObjects_(APP.sheets.initiatives);
  const today = startOfDay_(new Date());

  initiatives.forEach(function(record) {
    const item = record.object;
    const initiativeId = item['Initiative ID'];
    const terminal = [
      'Completed', 'Rejected', 'Cancelled'
    ].indexOf(item['Overall Status']) !== -1;

    if (terminal) {
      return;
    }

    recalculateInitiative_(initiativeId);

    const dueDate = toDateOrNull_(item['Next Update Due']);
    if (!dueDate) {
      return;
    }

    const due = startOfDay_(dueDate);
    const daysUntil = daysBetween_(due, today);
    const overdueDays = daysBetween_(today, due);
    const currentLevel = Number(item['Last Escalation Level'] || 0);
    const lastReminder = toDateOrNull_(item['Last Reminder Date']);
    const remindedToday = lastReminder
      ? sameDay_(lastReminder, today)
      : false;

    let level = 0;
    let shouldSend = false;

    if (daysUntil === 1 || daysUntil === 0) {
      shouldSend = !remindedToday;
    }

    if (
      overdueDays >= configNumber_('ESCALATION_LEVEL_1_DAYS', 2) &&
      currentLevel < 1
    ) {
      level = 1;
      shouldSend = true;
    }

    if (
      overdueDays >= configNumber_('ESCALATION_LEVEL_2_DAYS', 5) &&
      currentLevel < 2
    ) {
      level = 2;
      shouldSend = true;
    }

    const cadenceState = overdueDays > 0
      ? 'Overdue'
      : daysUntil <= 1
        ? 'Due'
        : 'Current';

    updateObject_(APP.sheets.initiatives, record.row, {
      'Update Cadence State': cadenceState,
      'Last Automation Run': new Date()
    });

    if (shouldSend) {
      sendUpdateReminder_(item, due, overdueDays, level);
      updateObject_(APP.sheets.initiatives, record.row, {
        'Last Reminder Date': new Date(),
        'Last Escalation Level': Math.max(currentLevel, level)
      });
    }
  });

  sweepOverdueDecisions_();
  log_('INFO', 'dailyPortfolioSweep', '', '', 'Daily sweep completed', {
    initiativesChecked: initiatives.length
  });
}

function sweepOverdueDecisions_() {
  const decisions = rowsAsObjects_(APP.sheets.decisions);
  const today = startOfDay_(new Date());

  decisions.forEach(function(record) {
    const item = record.object;
    if (item['Status'] !== 'Requested') {
      return;
    }

    const dueDate = toDateOrNull_(item['Due Date']);
    if (!dueDate) {
      return;
    }

    const overdueDays = daysBetween_(today, startOfDay_(dueDate));
    const currentLevel = Number(item['Escalation Level'] || 0);
    let nextLevel = 0;

    if (overdueDays >= 3 && currentLevel < 2) {
      nextLevel = 2;
    } else if (overdueDays >= 1 && currentLevel < 1) {
      nextLevel = 1;
    }

    if (nextLevel === 0) {
      return;
    }

    const initiative = findRecord_(
      APP.sheets.initiatives,
      'Initiative ID',
      item['Initiative ID']
    );

    const cc = nextLevel === 2
      ? [
          initiative ? initiative.object['Sponsor Email'] : '',
          getProperty_('STRATEGY_EMAIL')
        ].filter(String).join(',')
      : initiative
        ? initiative.object['Sponsor Email']
        : getProperty_('STRATEGY_EMAIL');

    safeSendEmail_({
      to: item['Decision Owner Email'],
      cc: cc,
      subject: 'Overdue portfolio decision: ' + item['Decision ID'],
      body: 'A portfolio decision is overdue.',
      htmlBody:
        '<p>A portfolio decision is overdue.</p>' +
        '<p><b>Decision:</b> ' +
        escapeHtml_(item['Decision ID']) + '</p>' +
        '<p><b>Initiative:</b> ' +
        escapeHtml_(item['Initiative ID']) + '</p>'
    }, 'sweepOverdueDecisions_', '', item['Decision ID']);

    updateObject_(APP.sheets.decisions, record.row, {
      'Last Reminder Date': new Date(),
      'Escalation Level': nextLevel,
      'Last Updated': new Date()
    });
  });
}

function recalculateInitiative_(initiativeId) {
  const initiative = findRecord_(
    APP.sheets.initiatives,
    'Initiative ID',
    initiativeId
  );
  if (!initiative) {
    throw new Error('Cannot recalculate missing initiative ' + initiativeId);
  }

  const milestones = rowsAsObjects_(APP.sheets.milestones).filter(
    function(row) {
      return row.object['Initiative ID'] === initiativeId;
    }
  );
  const risks = rowsAsObjects_(APP.sheets.risks).filter(function(row) {
    return row.object['Initiative ID'] === initiativeId;
  });
  const benefits = rowsAsObjects_(APP.sheets.benefits).filter(function(row) {
    return row.object['Initiative ID'] === initiativeId;
  });

  const today = startOfDay_(new Date());

  const overdueMilestones = milestones.filter(function(row) {
    const item = row.object;
    const due = toDateOrNull_(item['Due Date']);
    return (
      due &&
      startOfDay_(due).getTime() < today.getTime() &&
      ['Complete', 'Cancelled'].indexOf(item['Status']) === -1
    );
  }).length;

  const openRisks = risks.filter(function(row) {
    return ['Mitigated', 'Closed'].indexOf(row.object['Status']) === -1;
  });

  const riskExposure = openRisks.reduce(function(total, row) {
    return total + Number(row.object['Risk Score'] || 0);
  }, 0);

  const measuredAttainment = benefits
    .map(function(row) {
      const value = Number(row.object['Attainment %']);
      return Number.isFinite(value) ? value : null;
    })
    .filter(function(value) {
      return value !== null;
    });

  const averageAttainment = measuredAttainment.length
    ? measuredAttainment.reduce(function(total, value) {
        return total + value;
      }, 0) / measuredAttainment.length
    : null;

  const statusScores = {
    'Pending Approval': 60,
    'Needs Information': 50,
    'On Track': 100,
    'At Risk': 70,
    'Off Track': 40,
    'On Hold': 55,
    'Completion Proposed': 80,
    'Completed': 100,
    'Rejected': 0,
    'Cancelled': 0
  };

  const status = initiative.object['Overall Status'];
  const base = statusScores[status] !== undefined
    ? statusScores[status]
    : 50;

  const milestonePenalty = Math.min(20, overdueMilestones * 5);
  const riskPenalty = Math.min(25, riskExposure / 4);

  const lastUpdate = toDateOrNull_(
    initiative.object['Last Update'] ||
    initiative.object['Created Date']
  );
  const cadenceDays = configNumber_('UPDATE_CADENCE_DAYS', 7);
  const ageDays = lastUpdate
    ? Math.max(0, daysBetween_(today, startOfDay_(lastUpdate)))
    : cadenceDays + 5;
  const stalePenalty = ageDays > cadenceDays
    ? Math.min(15, (ageDays - cadenceDays) * 3)
    : 0;

  const benefitPenalty = averageAttainment !== null &&
    averageAttainment < 80
      ? Math.min(15, (80 - averageAttainment) / 4)
      : 0;

  const health = Math.max(
    0,
    Math.round(
      base -
      milestonePenalty -
      riskPenalty -
      stalePenalty -
      benefitPenalty
    )
  );

  updateObject_(APP.sheets.initiatives, initiative.row, {
    'Health Score': health,
    'Open Risk Count': openRisks.length,
    'Risk Exposure': riskExposure,
    'Overdue Milestones': overdueMilestones,
    'Benefit Attainment %': averageAttainment === null
      ? ''
      : Math.round(averageAttainment * 10) / 10,
    'Last Updated': new Date(),
    'Last Automation Run': new Date()
  });
}

function maybeReviewUpdateWithAI_(input) {
  const disabled = {
    summary: '',
    category: '',
    missingInformation: [],
    riskCues: [],
    confidence: '',
    reviewStatus: 'DISABLED',
    requestId: '',
    inputTokens: '',
    outputTokens: ''
  };

  if (String(getProperty_('AI_ENABLED')).toLowerCase() !== 'true') {
    return disabled;
  }

  try {
    return reviewUpdateWithAI_(input);
  } catch (error) {
    log_(
      'WARN',
      'maybeReviewUpdateWithAI_',
      '',
      input.initiativeId,
      'Optional AI review failed',
      { error: error.message }
    );

    return {
      summary: '',
      category: '',
      missingInformation: [],
      riskCues: [],
      confidence: '',
      reviewStatus: 'FAILED',
      requestId: '',
      inputTokens: '',
      outputTokens: ''
    };
  }
}

function reviewUpdateWithAI_(input) {
  const apiKey = getProperty_('OPENAI_API_KEY');
  const model = getProperty_('OPENAI_MODEL') || 'gpt-4o-mini';

  if (!apiKey || apiKey === 'YOUR_API_KEY') {
    throw new Error('OPENAI_API_KEY is not configured.');
  }

  const safeInput = {
    initiativeId: String(input.initiativeId).substring(0, 50),
    objective: redactForAI_(input.objective).substring(0, 2000),
    reportedStatus: String(input.reportedStatus).substring(0, 50),
    percentComplete: Number(input.percentComplete),
    updateSummary: redactForAI_(input.updateSummary).substring(0, 4000),
    helpRequired: redactForAI_(input.helpRequired).substring(0, 1500)
  };

  const schema = {
    type: 'object',
    additionalProperties: false,
    properties: {
      summary: { type: 'string', maxLength: 700 },
      category: {
        type: 'string',
        enum: [
          'routine_progress', 'delivery_risk', 'dependency_issue',
          'decision_required', 'benefit_issue', 'insufficient_information'
        ]
      },
      missing_information: {
        type: 'array',
        maxItems: 6,
        items: { type: 'string', maxLength: 180 }
      },
      risk_cues: {
        type: 'array',
        maxItems: 6,
        items: { type: 'string', maxLength: 180 }
      },
      confidence: {
        type: 'number',
        minimum: 0,
        maximum: 1
      }
    },
    required: [
      'summary', 'category', 'missing_information',
      'risk_cues', 'confidence'
    ]
  };

  const systemInstruction =
    'You review strategic initiative progress updates. ' +
    'Summarize only facts present in the supplied input. ' +
    'Identify missing information and possible risk cues, but do not make ' +
    'approval, funding, closure, employment, legal, safety, or accounting ' +
    'decisions. Do not infer names, dates, costs, or commitments. ' +
    'Return JSON that conforms exactly to the supplied schema.';

  const userPrompt =
    'Review this strategic initiative update for a human portfolio analyst:\n' +
    JSON.stringify(safeInput);

  const payload = {
    model: model,
    messages: [
      { role: 'system', content: systemInstruction },
      { role: 'user', content: userPrompt }
    ],
    response_format: {
      type: 'json_schema',
      json_schema: {
        name: 'initiative_update_review',
        strict: true,
        schema: schema
      }
    }
  };

  const response = fetchWithRetry_(
    'https://api.openai.com/v1/chat/completions',
    {
      method: 'post',
      contentType: 'application/json',
      headers: {
        Authorization: 'Bearer ' + apiKey
      },
      payload: JSON.stringify(payload),
      muteHttpExceptions: true
    },
    3
  );

  const body = JSON.parse(response.getContentText());
  if (
    !body.choices ||
    !body.choices[0] ||
    !body.choices[0].message ||
    !body.choices[0].message.content
  ) {
    throw new Error('AI response did not contain structured content.');
  }

  const result = JSON.parse(body.choices[0].message.content);
  validateAIResult_(result);

  const threshold = configNumber_('AI_CONFIDENCE_THRESHOLD', 0.70);

  return {
    summary: result.summary,
    category: result.category,
    missingInformation: result.missing_information,
    riskCues: result.risk_cues,
    confidence: result.confidence,
    reviewStatus: result.confidence < threshold ? 'REVIEW' : 'READY',
    requestId: body.id || '',
    inputTokens: body.usage ? body.usage.prompt_tokens || '' : '',
    outputTokens: body.usage ? body.usage.completion_tokens || '' : ''
  };
}

function validateAIResult_(result) {
  const categories = [
    'routine_progress', 'delivery_risk', 'dependency_issue',
    'decision_required', 'benefit_issue', 'insufficient_information'
  ];

  if (!result || typeof result.summary !== 'string') {
    throw new Error('AI summary is missing or invalid.');
  }
  if (categories.indexOf(result.category) === -1) {
    throw new Error('AI category is invalid.');
  }
  if (!Array.isArray(result.missing_information)) {
    throw new Error('AI missing_information must be an array.');
  }
  if (!Array.isArray(result.risk_cues)) {
    throw new Error('AI risk_cues must be an array.');
  }
  if (
    typeof result.confidence !== 'number' ||
    result.confidence < 0 ||
    result.confidence > 1
  ) {
    throw new Error('AI confidence is invalid.');
  }
}

function fetchWithRetry_(url, options, maxAttempts) {
  let lastError = null;

  for (let attempt = 1; attempt <= maxAttempts; attempt++) {
    try {
      const response = UrlFetchApp.fetch(url, options);
      const code = response.getResponseCode();

      if (code >= 200 && code < 300) {
        return response;
      }

      const retryable = code === 429 || code >= 500;
      const message =
        'HTTP ' + code + ': ' +
        response.getContentText().substring(0, 1000);

      if (!retryable || attempt === maxAttempts) {
        throw new Error(message);
      }

      lastError = new Error(message);
    } catch (error) {
      lastError = error;
      if (attempt === maxAttempts) {
        break;
      }
    }

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

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

function redactForAI_(value) {
  return String(value || '')
    .replace(/[^\s@]+@[^\s@]+\.[^\s@]+/g, '[EMAIL REDACTED]')
    .replace(/\b\d{12,19}\b/g, '[LONG NUMBER REDACTED]');
}

function sendApprovalRequest_(
  initiativeId,
  name,
  objective,
  sponsor,
  owner,
  targetDate,
  budget,
  folderUrl
) {
  const updateUrl = getProperty_('UPDATE_FORM_URL');
  const strategyEmail = getProperty_('STRATEGY_EMAIL');

  safeSendEmail_({
    to: sponsor,
    cc: strategyEmail,
    subject: 'Initiative approval requested: ' + initiativeId,
    body:
      'Approval is requested for ' + initiativeId +
      '. Use the Portfolio Update Form to record the decision: ' + updateUrl,
    htmlBody:
      '<p>An initiative proposal is ready for human review.</p>' +
      '<p><b>ID:</b> ' + escapeHtml_(initiativeId) + '</p>' +
      '<p><b>Name:</b> ' + escapeHtml_(name) + '</p>' +
      '<p><b>Objective:</b> ' +
      escapeHtml_(objective) + '</p>' +
      '<p><b>Owner:</b> ' + escapeHtml_(owner) + '</p>' +
      '<p><b>Target date:</b> ' +
      escapeHtml_(formatDate_(targetDate)) + '</p>' +
      '<p><b>Budget baseline:</b> ' +
      escapeHtml_(String(budget)) + '</p>' +
      (folderUrl
        ? '<p><a href="' + escapeHtml_(folderUrl) +
          '">Open initiative folder</a></p>'
        : '') +
      '<p><a href="' + escapeHtml_(updateUrl) +
      '">Record the decision</a></p>'
  }, 'sendApprovalRequest_', '', initiativeId);
}

function sendExceptionNotice_(
  initiative,
  status,
  summary,
  helpRequired,
  updateId
) {
  safeSendEmail_({
    to: initiative['Sponsor Email'],
    cc: getProperty_('STRATEGY_EMAIL'),
    subject:
      'Portfolio exception: ' +
      initiative['Initiative ID'] +
      ' is ' + status,
    body:
      'A material portfolio update was received for ' +
      initiative['Initiative ID'] + '.',
    htmlBody:
      '<p>A material portfolio update was received.</p>' +
      '<p><b>Initiative:</b> ' +
      escapeHtml_(initiative['Initiative ID']) + '</p>' +
      '<p><b>Update:</b> ' +
      escapeHtml_(updateId) + '</p>' +
      '<p><b>Status:</b> ' +
      escapeHtml_(status) + '</p>' +
      '<p><b>Summary:</b> ' +
      escapeHtml_(summary) + '</p>' +
      (helpRequired
        ? '<p><b>Help required:</b> ' +
          escapeHtml_(helpRequired) + '</p>'
        : '')
  }, 'sendExceptionNotice_', '', updateId);
}

function sendUpdateReminder_(initiative, dueDate, overdueDays, level) {
  const strategyEmail = getProperty_('STRATEGY_EMAIL');
  const updateUrl = getProperty_('UPDATE_FORM_URL');

  let cc = '';
  if (level === 1) {
    cc = initiative['Sponsor Email'];
  } else if (level >= 2) {
    cc = [
      initiative['Sponsor Email'],
      strategyEmail
    ].filter(String).join(',');
  }

  const timing = overdueDays > 0
    ? overdueDays + ' day(s) overdue'
    : 'due ' + formatDate_(dueDate);

  safeSendEmail_({
    to: initiative['Owner Email'],
    cc: cc,
    subject:
      'Strategic initiative update ' + timing + ': ' +
      initiative['Initiative ID'],
    body:
      'Submit an update for ' +
      initiative['Initiative ID'] +
      ' using ' + updateUrl,
    htmlBody:
      '<p>A progress update is ' + escapeHtml_(timing) + '.</p>' +
      '<p><b>Initiative:</b> ' +
      escapeHtml_(initiative['Initiative ID']) + '</p>' +
      '<p><b>Name:</b> ' +
      escapeHtml_(initiative['Initiative Name']) + '</p>' +
      '<p><a href="' + escapeHtml_(updateUrl) +
      '">Submit portfolio update</a></p>'
  }, 'sendUpdateReminder_', '', initiative['Initiative ID']);
}

function notifyStrategyError_(eventKey, sheetName, row, message) {
  safeSendEmail_({
    to: getProperty_('STRATEGY_EMAIL'),
    subject: 'Strategic portfolio automation failure',
    body:
      'Event ' + eventKey + ' failed. ' + message,
    htmlBody:
      '<p>A strategic portfolio event failed.</p>' +
      '<p><b>Event:</b> ' + escapeHtml_(eventKey) + '</p>' +
      '<p><b>Source:</b> ' +
      escapeHtml_(sheetName + ' row ' + row) + '</p>' +
      '<p><b>Error:</b> ' +
      escapeHtml_(message) + '</p>'
  }, 'notifyStrategyError_', eventKey, '');
}

function safeSendEmail_(message, functionName, eventKey, recordId) {
  try {
    const recipients = [message.to, message.cc || '']
      .join(',')
      .split(',')
      .map(function(value) {
        return value.trim();
      })
      .filter(String);

    if (recipients.length === 0) {
      throw new Error('No email recipient was supplied.');
    }

    if (MailApp.getRemainingDailyQuota() < recipients.length) {
      throw new Error('Insufficient remaining daily email-recipient quota.');
    }

    MailApp.sendEmail({
      to: message.to,
      cc: message.cc || '',
      subject: message.subject,
      body: message.body,
      htmlBody: message.htmlBody,
      name: 'Strategy Portfolio Automation',
      replyTo: getProperty_('STRATEGY_EMAIL')
    });

    return true;
  } catch (error) {
    log_(
      'WARN',
      functionName,
      eventKey,
      recordId,
      'Email notification failed',
      { error: error.message, to: message.to, cc: message.cc || '' }
    );
    return false;
  }
}

function assertCanEditInitiative_(email, initiative) {
  const normalized = normalizeEmail_(email);
  const direct = [
    initiative['Owner Email'],
    initiative['Sponsor Email']
  ].map(normalizeEmail_);

  if (direct.indexOf(normalized) !== -1) {
    return;
  }

  if (propertyEmailList_('PORTFOLIO_EDITOR_EMAILS').indexOf(normalized) !== -1) {
    return;
  }

  const activeOwner = rowsAsObjects_(APP.sheets.owners).some(function(row) {
    return (
      normalizeEmail_(row.object['Email']) === normalized &&
      String(row.object['Active']).toLowerCase() === 'true'
    );
  });

  if (!activeOwner) {
    throw new Error('The submitter is not authorized to update this initiative.');
  }
}

function isAuthorizedApprover_(email, initiative) {
  const normalized = normalizeEmail_(email);
  if (normalized === normalizeEmail_(initiative['Sponsor Email'])) {
    return true;
  }
  return propertyEmailList_('STRATEGY_APPROVER_EMAILS')
    .indexOf(normalized) !== -1;
}

function assertChildBelongs_(
  sheetName,
  idField,
  recordId,
  initiativeId
) {
  const record = findRecord_(sheetName, idField, recordId);
  if (!record) {
    throw new Error(idField + ' was not found: ' + recordId);
  }
  if (record.object['Initiative ID'] !== initiativeId) {
    throw new Error(recordId + ' does not belong to ' + initiativeId);
  }
  return record;
}

function assertNoLikelyDuplicate_(name, owner, targetDate) {
  const normalizedName = normalizeText_(name);
  const normalizedOwner = normalizeEmail_(owner);
  const target = formatDate_(targetDate);

  const duplicate = rowsAsObjects_(APP.sheets.initiatives).some(function(row) {
    const item = row.object;
    return (
      normalizeText_(item['Initiative Name']) === normalizedName &&
      normalizeEmail_(item['Owner Email']) === normalizedOwner &&
      formatDate_(toDateOrNull_(item['Target End Date'])) === target &&
      ['Rejected', 'Cancelled'].indexOf(item['Overall Status']) === -1
    );
  });

  if (duplicate) {
    throw new Error(
      'POSSIBLE_DUPLICATE: An active initiative has the same name, owner, ' +
      'and target date. Strategy review is required.'
    );
  }
}

function markRelatedInitiativeError_(namedValues, eventKey, message) {
  let record = findRecord_(
    APP.sheets.initiatives,
    'Source Event Key',
    eventKey
  );

  if (!record) {
    const initiativeId = optionalText_(namedValues, 'Initiative ID').toUpperCase();
    if (initiativeId) {
      record = findRecord_(
        APP.sheets.initiatives,
        'Initiative ID',
        initiativeId
      );
    }
  }

  if (!record) {
    return;
  }

  updateObject_(APP.sheets.initiatives, record.row, {
    'Automation Status': 'ERROR',
    'Last Automation Run': new Date(),
    'Retry Count': Number(record.object['Retry Count'] || 0) + 1,
    'Error Message': String(message).substring(0, 2000)
  });
}

function createInitiativeFolder_(initiativeId, name) {
  const root = DriveApp.getFolderById(getProperty_('ROOT_FOLDER_ID'));
  const safeName = String(name)
    .replace(/[\\/:*?"<>|#%{}\[\]]/g, ' ')
    .replace(/\s+/g, ' ')
    .trim()
    .substring(0, 70);

  const folder = root.createFolder(initiativeId + ' - ' + safeName);
  return folder.getUrl();
}

function calculateAttainment_(baseline, target, actual, direction) {
  let result;

  if (direction === 'Increase') {
    result = ((actual - baseline) / (target - baseline)) * 100;
  } else {
    result = ((baseline - actual) / (baseline - target)) * 100;
  }

  return Math.round(Math.max(-100, Math.min(200, result)) * 10) / 10;
}

function nextId_(prefix) {
  const lock = LockService.getScriptLock();
  lock.waitLock(30000);

  try {
    const year = new Date().getFullYear();
    const key = 'SEQ_' + prefix + '_' + year;
    const propertyStore = PropertiesService.getScriptProperties();
    const next = Number(propertyStore.getProperty(key) || 0) + 1;
    propertyStore.setProperty(key, String(next));
    return prefix + '-' + year + '-' + String(next).padStart(4, '0');
  } finally {
    lock.releaseLock();
  }
}

function getSpreadsheet_() {
  const id = getProperty_('SPREADSHEET_ID');
  if (!id || id === 'YOUR_SPREADSHEET_ID') {
    throw new Error('SPREADSHEET_ID is not configured.');
  }
  return SpreadsheetApp.openById(id);
}

function getSheet_(sheetName) {
  const sheet = getSpreadsheet_().getSheetByName(sheetName);
  if (!sheet) {
    throw new Error('Missing sheet: ' + sheetName);
  }
  return sheet;
}

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

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

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

function updateObject_(sheetName, rowNumber, changes) {
  const sheet = getSheet_(sheetName);
  const map = headerMap_(sheet);

  Object.keys(changes).forEach(function(field) {
    if (!map[field]) {
      throw new Error('Unknown field ' + field + ' on ' + sheetName);
    }
    sheet.getRange(rowNumber, map[field]).setValue(changes[field]);
  });
}

function findRecord_(sheetName, fieldName, value) {
  const sheet = getSheet_(sheetName);
  const map = headerMap_(sheet);
  const column = map[fieldName];

  if (!column) {
    throw new Error('Missing field ' + fieldName + ' on ' + sheetName);
  }
  if (sheet.getLastRow() < 2) {
    return null;
  }

  const values = sheet
    .getRange(2, column, sheet.getLastRow() - 1, 1)
    .getValues();

  for (let index = 0; index < values.length; index++) {
    if (String(values[index][0]) === String(value)) {
      const row = index + 2;
      return {
        row: row,
        object: rowObject_(sheet, row)
      };
    }
  }

  return null;
}

function rowsAsObjects_(sheetName) {
  const sheet = getSheet_(sheetName);
  if (sheet.getLastRow() < 2) {
    return [];
  }

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

  return values.slice(1).map(function(row, index) {
    const object = {};
    headers.forEach(function(header, column) {
      object[header] = row[column];
    });
    return { row: index + 2, object: object };
  }).filter(function(record) {
    return Object.values(record.object).some(function(value) {
      return value !== '';
    });
  });
}

function rowObject_(sheet, rowNumber) {
  const headers = sheet
    .getRange(1, 1, 1, sheet.getLastColumn())
    .getValues()[0];
  const values = sheet
    .getRange(rowNumber, 1, 1, headers.length)
    .getValues()[0];
  const object = {};

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

  return object;
}

function headerMap_(sheet) {
  const headers = sheet
    .getRange(1, 1, 1, sheet.getLastColumn())
    .getValues()[0];
  const map = {};

  headers.forEach(function(header, index) {
    map[header] = index + 1;
  });

  return map;
}

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

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

  return result;
}

function collectedEmail_(namedValues) {
  const header = getProperty_('COLLECTED_EMAIL_HEADER') || 'Email Address';
  const email = requiredText_(namedValues, header);
  return validEmail_(email, header);
}

function requiredText_(namedValues, field) {
  const value = optionalText_(namedValues, field);
  if (!value) {
    throw new Error('Missing required field: ' + field);
  }
  return value;
}

function optionalText_(namedValues, field) {
  const raw = namedValues[field];
  if (Array.isArray(raw)) {
    return String(raw[0] || '').trim();
  }
  return String(raw || '').trim();
}

function controlledValue_(namedValues, field, allowed) {
  const value = requiredText_(namedValues, field);
  if (allowed.indexOf(value) === -1) {
    throw new Error(
      'Invalid value for ' + field + ': ' + value +
      '. Allowed values: ' + allowed.join(', ')
    );
  }
  return value;
}

function requiredNumber_(namedValues, field, minimum, maximum) {
  const value = optionalNumber_(namedValues, field);
  if (value === null) {
    throw new Error('Missing or invalid number: ' + field);
  }
  if (value < minimum || value > maximum) {
    throw new Error(
      field + ' must be between ' + minimum + ' and ' + maximum + '.'
    );
  }
  return value;
}

function optionalNumber_(namedValues, field) {
  const text = optionalText_(namedValues, field);
  if (!text) {
    return null;
  }

  const normalized = text.replace(/[$,%\s]/g, '').replace(/,/g, '');
  const value = Number(normalized);
  return Number.isFinite(value) ? value : null;
}

function requiredDate_(namedValues, field) {
  const date = optionalDate_(namedValues, field);
  if (!date) {
    throw new Error('Missing or invalid date: ' + field);
  }
  return date;
}

function optionalDate_(namedValues, field) {
  return toDateOrNull_(optionalText_(namedValues, field));
}

function toDateOrNull_(value) {
  if (!value) {
    return null;
  }
  if (Object.prototype.toString.call(value) === '[object Date]') {
    return isNaN(value.getTime()) ? null : value;
  }

  const text = String(value).trim();
  const isoMatch = text.match(/^(\d{4})-(\d{2})-(\d{2})$/);
  if (isoMatch) {
    const isoDate = new Date(
      Number(isoMatch[1]),
      Number(isoMatch[2]) - 1,
      Number(isoMatch[3])
    );
    return isNaN(isoDate.getTime()) ? null : isoDate;
  }

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

function validEmail_(email, fieldName) {
  const normalized = normalizeEmail_(email);
  if (!/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(normalized)) {
    throw new Error('Invalid email in ' + fieldName + '.');
  }
  return normalized;
}

function optionalUrl_(namedValues, field) {
  const value = optionalText_(namedValues, field);
  if (!value) {
    return '';
  }
  if (!/^https:\/\//i.test(value)) {
    throw new Error(field + ' must use an HTTPS URL.');
  }
  return value;
}

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

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

function propertyEmailList_(key) {
  return String(getProperty_(key) || '')
    .split(',')
    .map(normalizeEmail_)
    .filter(String);
}

function getProperty_(key) {
  return PropertiesService.getScriptProperties().getProperty(key) || '';
}

function configNumber_(key, fallback) {
  const record = findRecord_(APP.sheets.config, 'Key', key);
  if (!record) {
    return fallback;
  }
  const value = Number(record.object['Value']);
  return Number.isFinite(value) ? value : fallback;
}

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

function startOfDay_(date) {
  return new Date(date.getFullYear(), date.getMonth(), date.getDate());
}

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

function sameDay_(first, second) {
  return startOfDay_(first).getTime() === startOfDay_(second).getTime();
}

function formatDate_(date) {
  if (!date) {
    return '';
  }
  return Utilities.formatDate(
    date,
    getSpreadsheet_().getSpreadsheetTimeZone(),
    'yyyy-MM-dd'
  );
}

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

function log_(
  level,
  functionName,
  eventKey,
  recordId,
  message,
  details
) {
  try {
    appendObject_(APP.sheets.logs, {
      'Timestamp': new Date(),
      'Level': level,
      'Function': functionName,
      'Event Key': eventKey,
      'Record ID': recordId,
      'Message': String(message).substring(0, 2000),
      'Details': JSON.stringify(details || {}).substring(0, 5000)
    });
  } catch (loggingError) {
    console.error('Logging failed: ' + loggingError.message);
  }
}

Deployment instructions

1. Open the Strategic Initiative Register workbook.
2. Open its spreadsheet-bound Apps Script editor.
3. Paste the complete script into a script file and save it.
4. Replace the configuration placeholders in configureProject.
5. Run configureProject once.
6. Link both Forms to the workbook and confirm their response-tab names match the script.
7. Run setupWorkbook.
8. Add active users to the Owners tab and use true in the Active column.
9. Run testConfiguration and inspect the execution result.
10. Run installTriggers and approve the requested permissions.
11. Submit one test intake form and confirm the initiative, benefit, folder, event, log, and email records.
12. Submit one test progress update and confirm the Update_Log and initiative score.
13. Open Apps Script Executions to inspect completed and failed runs.

The form-submit trigger is installable, not a simple trigger. It runs when a linked form writes a response to the spreadsheet. The daily trigger runs near the configured hour according to Apps Script scheduling behavior and the project timezone.

If a form question or collected-email heading differs because of language or configuration, update the question title or COLLECTED_EMAIL_HEADER property. Exact titles must match the script.

To retry a failed event, obtain its Event Key from Processed_Events and run retryFailedEvent from the editor with the key supplied in a temporary wrapper or through an administrator-controlled invocation. Correct the underlying data or configuration before retrying.

Likely errors include mismatched form labels, missing authorization, an inaccessible root folder, invalid dates caused by locale differences, exhausted email quota, and an expired or revoked AI credential. Execution logs and Automation_Log provide the first troubleshooting evidence.

Looker Studio configuration

No custom reporting code is required. Add each governed Sheet tab as a Google Sheets data source, verify date and numeric types, and build charts using the fields documented earlier.

A useful dashboard health-band calculated field is:

CASE
  WHEN Health Score >= 80 THEN "Healthy"
  WHEN Health Score >= 60 THEN "Watch"
  ELSE "Intervention"
END

Keep row-level security and credential choices aligned with organizational policy. If different viewers must see different initiatives, a single broadly shared Sheet data source may be insufficient and a more controlled data architecture should be considered.

Failure Handling and Operational Reliability

The Processed_Events tab acts as a lightweight dead-letter queue for failed form events. It records the original sheet, row, attempts, error, and returned record ID. Automation_Log provides additional diagnostics.

Idempotency is essential because form triggers, administrator retries, and partial failures can otherwise create duplicate initiatives or child records. Event-level checking and Source Event Key fields address this risk.

The strategy analyst performs a weekly reconciliation:

1. Count new rows in each response tab.
2. Confirm each row has one Processed_Events entry.
3. Review FAILED and long-running IN_PROGRESS events.
4. Confirm completed events have a returned record ID.
5. Compare recent destination records with their source event keys.
6. Resolve errors before the next executive reporting cycle.

A Complete Example

Northvale’s service operations manager proposes an initiative named Reduce Emergency Dispatch Rework.

Original input

• Strategic theme: Operational Excellence
• Objective: Reduce repeat technician visits caused by incomplete first-visit diagnosis.
• Sponsor: [email protected]
• Owner: [email protected]
• Start date: 2026-07-20
• Target end date: 2026-12-18
• Budget baseline: $48,000
• Primary KPI: Repeat visit rate within 14 days
• Unit: Percent
• Baseline: 8.2
• Target: 5.5
• Direction: Decrease
• Expected benefit: Additional technician capacity and fewer customer disruptions.

The Intake Form writes the submission to row 11 of its response tab. Apps Script generates an event key using the sheet ID and row, checks Processed_Events, validates the proposal, and assigns SI-2026-0007.

The script creates the folder SI-2026-0007 - Reduce Emergency Dispatch Rework, stores its URL, and creates benefit record BN-2026-0007. The initiative starts with Approval Status Pending and Overall Status Pending Approval.

The sponsor receives an email and submits a Decision update:

• Decision type: Initiative Approval
• Decision status: Approved
• Decision owner: sponsor
• Decision text: Approve the diagnostic checklist and training pilot within the stated budget baseline.

The script confirms that the authenticated submitter is the sponsor, creates decision DC-2026-0012, and changes the initiative to Approved and On Track.

The owner then adds milestone MS-2026-0028 for completion of the diagnostic checklist. A later Risk submission creates RK-2026-0019 with probability 3, impact 4, and score 12. Its response states that the checklist pilot depends on data from the scheduling team.

A Dependency submission creates DP-2026-0011 and references another active initiative owned by the analytics team. The dependency is visible in the dashboard and has its own owner and need-by date.

At the end of the first reporting period, the initiative owner submits:

• Overall status: At Risk
• Percent complete: 35
• KPI actual: 7.6
• Confidence: Medium
• Summary: Pilot checklist is drafted, but scheduling data access is five days late and may delay technician testing.
• Help required: Sponsor support is needed to confirm analytics capacity.

Apps Script creates update UP-2026-0039, updates the KPI actual, sets the next update due date seven days later, and sends an exception notice to the sponsor and strategy address.

For a decrease KPI, attainment is calculated as:

Attainment = (Baseline - Actual) / (Baseline - Target) × 100
Attainment = (8.2 - 7.6) / (8.2 - 5.5) × 100
Attainment = 22.2 percent

The deterministic health score starts at 70 because the initiative is At Risk. It then deducts points for open risk exposure and any overdue milestones or stale updates. The dashboard displays the resulting score, but leadership uses the underlying details rather than treating the score as a final decision.

If the owner submits the same form event twice because a trigger is replayed, Processed_Events prevents a duplicate update. If an email notification fails, the update remains valid and the notification failure appears in Automation_Log.

When delivery is finished, the owner submits Completion Proposed. The initiative does not become Completed until the sponsor records an approved Closure Approval decision with supporting evidence.

Implementation Cost

All amounts below are representative planning assumptions, not verified client results or vendor quotations. Northvale is assumed to have an existing Google Workspace environment that already supports the required Forms, Sheets, Drive, Apps Script, email, and Looker Studio capabilities. Current vendor terms and organizational licensing must be confirmed.

Existing tools still carry subscription, administration, security, and support costs even when the project does not add an incremental license.

Estimated Time and Cost Savings

The estimate uses these representative assumptions:

• 104 progress updates and related portfolio transactions per month
• 22 minutes of current handling time per record
• 7 minutes of handling time after core automation
• 10 percent exception rate
• 12 minutes of additional manual review per exception
• 3 hours of monthly maintenance
• $55 loaded hourly labour cost
• $0 incremental recurring core software cost
• $6,940 total economic implementation cost

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

104 × 22 ÷ 60 = 38.13 hours

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

Routine handling: 104 × 7 ÷ 60 = 12.13 hours
Exception handling: 104 × 10% × 12 ÷ 60 = 2.08 hours
Maintenance: 3.00 hours
Total new monthly labour: 17.21 hours

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

38.13 - 17.21 = 20.92 hours

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

20.92 × $55 = $1,150.60

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

$1,150.60 - $0 incremental core software = $1,150.60

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

$6,940 ÷ $1,150.60 = 6.03 months

Recovered time does not automatically reduce payroll. It can represent additional analytical capacity, quicker preparation for leadership meetings, reduced overtime, fewer administrative follow-ups, lower dependency on one analyst, or the ability to handle more initiatives without proportional administrative growth.

Non-financial benefits include clearer ownership, more consistent status definitions, fewer incomplete updates, persistent decision history, faster exception identification, stronger benefit evidence, and improved reporting confidence.

Readers should replace the following assumptions with their own figures:

• Number of active initiatives
• Update frequency
• Current chasing and consolidation time
• New form-completion and review time
• Exception rate
• Loaded labour cost
• Monthly support effort
• Incremental software or license cost
• Implementation labour and internal participation

Adding AI to the Automation

AI is an optional enhancement introduced only after the forms, validation, approvals, reminders, scoring, and failure controls are operating reliably.

The core automation already provides standardized fields, deterministic validation, identifiers, status workflow, reminders, escalation, folder creation, calculations, and reporting. None of those functions require AI.

Useful AI applications for this process include:

• Summarizing narrative progress updates
• Classifying an update as routine progress, risk, dependency, benefit issue, or decision request
• Identifying possibly missing dates, owners, impacts, or next actions
• Highlighting risk cues in unstructured text
• Creating a searchable summary of update history
• Comparing a new update with the previous reporting period

AI should not replace required fields, dropdowns, date validation, KPI formulas, authorization checks, exact duplicate matching, reminder thresholds, or human approvals.

The Recommended AI Enhancement

The recommended enhancement reviews a Progress submission after rule-based validation. It produces a concise summary, category, missing-information list, risk-cue list, and confidence score.

• Trigger: A valid Progress update reaches Apps Script.
• AI input: Initiative objective, reported status, percent complete, narrative summary, and help request.
• System instruction: Summarize only supplied facts, identify missing information and risk cues, and avoid high-impact decisions.
• Expected output: Strict JSON conforming to the defined schema.
• Validation: Apps Script checks required properties, category values, arrays, and confidence range.
• Record update: Valid output is stored in dedicated Update_Log fields.
• Human review: A portfolio analyst reviews the output before using it in executive material.
• Low confidence: Confidence below 0.70 produces AI Review Status REVIEW.
• Prohibited data: Credentials, sensitive personal information, confidential contracts, regulated customer records, legal advice, and detailed financial account data.
• Logging: Request ID and token usage are stored for support and cost monitoring.
• Failure behavior: The core update remains valid and AI Review Status becomes FAILED.
• Fallback: The analyst reads the original owner-submitted update.

The reusable system instruction is:

You review strategic initiative progress updates. Summarize only facts present in the supplied input. Identify missing information and possible risk cues, but do not make approval, funding, closure, employment, legal, safety, or accounting decisions. Do not infer names, dates, costs, or commitments. Return JSON that conforms exactly to the supplied schema.

The reusable user prompt is:

Review this strategic initiative update for a human portfolio analyst:

{
  "initiativeId": "{{Initiative ID}}",
  "objective": "{{Objective}}",
  "reportedStatus": "{{Overall Status}}",
  "percentComplete": {{Percent Complete}},
  "updateSummary": "{{Update Summary}}",
  "helpRequired": "{{Help Required}}"
}

The structured response must follow this shape:

{
  "summary": "Checklist pilot is drafted, but delayed data access may affect technician testing.",
  "category": "dependency_issue",
  "missing_information": [
    "Revised testing date",
    "Named owner for resolving data access"
  ],
  "risk_cues": [
    "Scheduling data access is late",
    "Testing may be delayed"
  ],
  "confidence": 0.91
}

The complete API configuration and retry logic are included in the Apps Script earlier. Authentication uses an API key stored in Apps Script Properties and sent in the HTTP Authorization header. The request uses HTTPS POST with a JSON body. Retry behavior covers HTTP 429 and server errors, while malformed or nonconforming output is rejected.

Benefits of the AI Enhancement

• Less analyst reading time for repetitive narrative updates
• More consistent first-pass categorization
• Faster identification of missing owners, dates, impacts, or next actions
• Better handling of unstructured update text
• More concise executive summaries for human editing
• Structured risk cues that can support trend analysis
• Token and request logging that supports cost and quality monitoring

These benefits are specific to narrative interpretation. The improved audit trail, reminders, identifiers, status governance, and reporting already come from the core automation.

What Remains Rule-Based or Human-Controlled

Estimating the Additional Value of AI

The following assumptions are illustrative:

• 104 narrative progress updates per month
• Core automated handling time of 7 minutes per update
• AI-assisted handling time of 4.5 minutes before corrections
• 12 percent of AI outputs require 3 additional correction minutes
• 2 percent AI service failure rate requiring 2.5 additional manual minutes
• $4 monthly AI usage allowance
• $55 loaded hourly labour cost

Core automation:
104 × 7 minutes ÷ 60 = 12.13 hours

AI-assisted base review:
104 × 4.5 minutes ÷ 60 = 7.80 hours

AI correction time:
104 × 12% × 3 minutes ÷ 60 = 0.62 hours

AI failure fallback:
104 × 2% × 2.5 minutes ÷ 60 = 0.09 hours

Total AI-assisted handling:
7.80 + 0.62 + 0.09 = 8.51 hours

Additional capacity:
12.13 - 8.51 = 3.62 hours

Additional monthly labour value:
3.62 × $55 = $199.10

Net additional monthly value after AI usage:
$199.10 - $4 = $195.10

AI does not eliminate errors or human review. Actual value depends on narrative length, output quality, correction rates, provider cost, and whether analysts can use the recovered time productively.

Testing Checklist

Use fictional sample data before processing real portfolio information.

Ongoing Maintenance

The strategy director is the primary business owner. The portfolio analyst is the day-to-day system owner. A Workspace administrator is the technical backup for identity, authorization, and account recovery.

Documentation should include the data dictionary, form field names, trigger owner, script properties, approver rules, scoring logic, error recovery steps, dashboard definitions, retention policy, and current support contacts.

When to Move to Dedicated Software

The Google Workspace implementation remains appropriate while volume is moderate, workflow rules are understandable, and the strategy team can support the script responsibly.

Northvale should evaluate dedicated strategy execution, project portfolio management, governance-risk-compliance, or work-management software if several of these conditions emerge:

• Active initiative volume rises enough to affect Sheet or script performance.
• Several business units require different permission models in the same portfolio.
• Formal resource capacity planning and allocation become necessary.
• Financial actuals must integrate directly with accounting or enterprise resource planning systems.
• Stage gates require complex parallel approvals and digital signatures.
• Regulatory obligations require certified controls, immutable records, or formal audit support.
• Multiple locations require local ownership with central roll-up and data segregation.
• Exception rates become high enough that the form-driven process is difficult to administer.
• Apps Script maintenance becomes dependent on one employee.
• Executives require scenario modeling, portfolio optimization, or advanced forecasting.
• Mobile or offline workflows become necessary.
• External partners need a secure customer-facing portal.
• Integration volume requires managed APIs, queues, service-level monitoring, or centralized secrets.
• Security risk exceeds what can be managed through the current sharing and data-source model.

Dedicated software should not be adopted merely because the tracker uses Sheets. The decision should be based on governance, security, integration, scale, support, and total operating cost.

Implementation Checklist

• Confirm portfolio objectives, governance rules, volume, and update cadence.
• Select and document the Google Forms, Sheets, Apps Script, Drive, email, and Looker Studio responsibilities.
• Create production and test accounts, files, folders, and forms.
• Assign the primary system owner, technical backup, sponsors, and approvers.
• Configure least-privilege permissions and Google Groups.
• Build the Initiative Intake Form with exact field names and validation.
• Build the conditional Portfolio Update Form.
• Link both forms to the governed workbook.
• Create the entity tabs and approved data structure.
• Define unique ID formats and sequence ownership.
• Document every source-to-destination field mapping.
• Configure the form-submit trigger.
• Configure the daily reminder and escalation trigger.
• Implement event idempotency and source-event keys.
• Configure initiative approval, closure, risk acceptance, and budget decision rules.
• Set reminder, escalation, and unavailable-approver procedures.
• Create the controlled Drive root and initiative folder convention.
• Build portfolio, delivery, risk, benefit, governance, and failure views.
• Protect automation-controlled fields and sensitive tabs.
• Store credentials in Apps Script Properties.
• Authorize and test the complete script.
• Test normal, invalid, duplicate, unauthorized, failed, overdue, and recovery paths.
• Reconcile dashboard totals with Sheet records.
• Pilot with a limited group before full deployment.
• Document implementation, software, maintenance, and internal labour assumptions.
• Replace savings assumptions with actual measured handling time.
• Enable AI only after the core workflow is stable.
• Require human review and monitor AI cost, confidence, correction rate, and failures.
• Assign daily, weekly, monthly, and quarterly maintenance responsibilities.
• Define the volume, security, integration, audit, and support criteria that would trigger evaluation of dedicated software.