Building a Practical Weekly Sales Forecast in Google Sheets

The Business Situation

Cedar Vale Packaging is a fictional 48-person B2B packaging distributor. Its eight sales representatives manage approximately 140 open opportunities. A sales manager, an operations analyst, and the business owner review the sales outlook every Monday.

The company uses Google Workspace for spreadsheets, forms, email, and shared files. Its order management system can export confirmed orders as a CSV file, but it is not connected to the sales forecast. Opportunity updates arrive through individual spreadsheets, email messages, and conversations with the sales manager.

The weekly forecast covers the prior week and the next eight weeks. It combines approximately 140 open opportunities with about 65 confirmed orders. The forecast is intended to estimate the sales value expected to close or be released for fulfilment during each week.

The owner needs three transparent forecast scenarios:

• Low: A conservative view of expected sales.
• Base: The working forecast used for planning.
• High: A reasonable upside view, capped at the full opportunity or order value.

The forecast must account for historical stage conversion, known risks, confirmed orders, manager-approved overrides, and changes since the prior forecast. It must remain understandable to managers who do not maintain the automation.

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 began on Thursday afternoon. The sales manager emailed the representatives and requested updated stages, expected close dates, and risk notes.

1. Each representative reviewed a personal opportunity spreadsheet or replied by email.
2. The sales manager copied updates into a consolidated workbook.
3. The operations analyst exported confirmed orders from the order management system.
4. The analyst pasted order totals into a separate forecast tab.
5. The sales manager applied judgement by changing totals or adding comments.
6. The owner received a PDF or spreadsheet attachment on Monday morning.
7. After the meeting, the analyst manually saved a copy to preserve the previous forecast.

The problem was not simply that the spreadsheet required manual work. The more important issue was that assumptions, ownership, approval evidence, and historical forecast changes were mixed together. A corrected number could not always be distinguished from an unsupported adjustment.

What the New System Needed to Do

The model also needed a clear definition of the forecast measure. Cedar Vale defined it as the expected sales value scheduled to close or be released for fulfilment during a specific Monday-to-Sunday week. That definition prevented different departments from interpreting the forecast as invoiced revenue, cash receipts, or total pipeline.

Implementation Approaches Considered

Why the simple workbook was not sufficient

A single workbook could calculate totals, but it would leave representatives editing operational columns directly. It would also make submission validation and reminder automation harder to govern.

Why CRM forecasting was deferred

A CRM would become a reasonable option if Cedar Vale standardized opportunity management there. At the time of this representative implementation, introducing a CRM solely to solve the weekly forecast would have expanded the project into data migration, workflow redesign, training, and order integration.

Why Power BI or Looker Studio was not the first step

Power BI and Looker Studio are useful reporting layers. They are not substitutes for a controlled system of record. Adding a dashboard before fixing identifiers, statuses, assumptions, and approvals would have produced a more attractive view of inconsistent source data.

Why the Google Workspace option was selected

The selected approach retained tools the company already used while adding structured forms, a protected system of record, automated calculations, snapshots, reminders, and email summaries. It was appropriate for 140 active opportunities and one weekly forecast cycle, provided the workbook remained governed.

The Selected Solution

Google Sheets became the operational system of record for the forecast, but not the source of every business transaction. The order management system remained authoritative for confirmed orders. Its weekly export was loaded into the controlled Orders sheet.

The following manual decisions remained:

• Representatives confirmed stages, close dates, and known risks.
• The sales manager approved or rejected forecast overrides.
• The sales manager approved changes to historical conversion assumptions.
• The owner decided how the forecast affected purchasing, staffing, and cash planning.
• The operations analyst reviewed invalid order imports and reconciliation differences.

The automation removed email consolidation, repetitive copying, scenario arithmetic, snapshot creation, stale-record identification, and scheduled report distribution.

System Architecture and Data Flow

• Intake: Google Forms for opportunity updates and override decisions, plus a controlled confirmed-order import.
• System of record: Protected tables in Google Sheets.
• Automation layer: A bound Google Apps Script project with form and time-based triggers.
• Document storage: A restricted Google Drive folder for the workbook, forms, exports, and documentation.
• Notifications: Gmail messages sent by the automation account.
• Reporting: Forecast, snapshot, exception, and operational views in Google Sheets.
• AI layer: Optional narrative generation using aggregated forecast data only.

1. Opportunity update: A representative submits an opportunity ID, stage, expected close date, risk code, and notes through Google Forms. The spreadsheet form-submit trigger receives the named fields.
2. Submission validation: Apps Script checks required values, allowed stages, dates, the opportunity ID, and whether the submitter owns the record or is an authorized manager.
3. Record update: Apps Script updates the matching row in Opportunities. It records the update time, automation status, and an audit entry.
4. Override routing: If the representative requests an override, the request is marked Pending. The forecast ignores it until a manager submits an approval decision.
5. Confirmed-order load: The operations analyst replaces the controlled order import with the current export. The order ID and linked opportunity ID prevent double counting.
6. Forecast calculation: Apps Script groups opportunities and orders by week. It applies approved historical probabilities, risk multipliers, scenario factors, and approved manual overrides.
7. Snapshot and variance: The current result is compared with the latest prior snapshot for each week. A new immutable snapshot is appended after every completed forecast run.
8. Distribution: Gmail sends a scheduled summary containing low, base, high, prior-base, and variance values.
9. Failure path: Invalid submissions or transient failures are recorded in Error_Log. Retryable events are retried up to three times. Persistent failures move to Manual Review.

The opportunity ID returned by the source process remains the stable key throughout the workflow. The automation does not create a second opportunity when an update arrives. It performs an update against the existing ID.

Data Structure

Opportunity records

Confirmed orders

Supporting tables

Conversion_Rates

Stores the historical stage, eligible opportunity count, won count, raw conversion rate, minimum sample, approved probability, approver, and approval date.

Outcome_History

Stores historical opportunity-stage observations and final outcomes used to calculate stage-to-win rates.

Assumptions

Stores opportunity risk multipliers, order risk multipliers, scenario factors, stale-update age, and forecast horizon.

Forecast

Contains the current weekly low, base, high, actual, prior-base, and variance values.

Forecast_Snapshots

Appends one row per forecast week and run. Existing snapshots are never overwritten by the calculation.

Processed_Events

Stores each form event key to prevent duplicate processing.

Error_Log

Stores process, source key, error message, serialized payload, retry count, and recovery status.

Audit_Log

Records material updates, approvals, forecast runs, and the responsible user or automation process.

Record relationships

• One opportunity can have zero or one current approved override.
• One opportunity can produce one or more order records if the source process supports split orders.
• Each form submission updates one opportunity.
• Each forecast run creates one snapshot row for every forecast week.
• Each failed event can produce one error record and up to three automated retries.

Workflow Statuses and Ownership

A record can move backward when a manager selects Return for Information, when an expected close date is moved to a later week, or when an order is placed on hold. A rejected override does not reject the opportunity. It removes the override from consideration and returns the opportunity to its calculated base value.

Step-by-Step Implementation

Step 1: Prepare the Accounts and Permissions

1. Create or identify a shared Google Drive location owned by the business rather than an individual representative.
2. Create the forecast workbook in that location.
3. Assign one automation owner and one backup owner. Both should be able to edit the workbook and Apps Script project.
4. Create two Google Forms: Weekly Opportunity Update and Forecast Override Decision.
5. Link both forms to the same workbook. Rename their generated response sheets to Rep Update Responses and Override Responses.
6. Confirm that the automation owner can send Gmail messages to internal recipients.
7. Confirm that the Google Workspace subscription and administrator policies allow Apps Script, installable triggers, and the required email activity. Feature availability and quotas can vary.
8. Create a test representative account and a test manager account. Do not test with production opportunity records.

The automation owner requires edit access to the operational sheets and permission to install triggers. Sales representatives need access to the update form, but they do not need edit access to protected forecast calculations.

Use a separate workbook copy for development. Apps Script does not provide a full transaction-safe staging environment for this design, so test forms must point to the test workbook until user acceptance testing is complete.

Step 2: Build the Intake

Weekly Opportunity Update form

Use form section branching so that respondents who select Yes for Request Override are sent to the amount and reason questions. Apps Script repeats the validation because browser-side form validation alone is not sufficient.

The confirmation message should state that the update has been submitted for processing and that a requested override does not affect the forecast until it is approved.

Forecast Override Decision form

• Opportunity ID: Required short answer.
• Manager Email: Required email address.
• Decision: Approve, Reject, or Return for Information.
• Approved Base Forecast Amount: Required when approving.
• Decision Note: Required for rejection or return.

Restrict the manager form to authorized managers where Google Workspace policies allow it. The script also validates the submitted manager email against its configuration.

Attachments are not required for this forecast. Excluding them reduces unnecessary access and retention issues. If evidence is later required, store a controlled Drive link rather than placing large files in the workbook.

Step 3: Create the System of Record

Create the following sheets with exact names:

• Opportunities
• Orders
• Outcome_History
• Conversion_Rates
• Assumptions
• Forecast
• Forecast_Snapshots
• Processed_Events
• Error_Log
• Audit_Log
• Rep Update Responses
• Override Responses

The initialization function in the code section creates the operational sheets and exact headers. The two response sheets are created by Google Forms and must be renamed to match the script configuration.

Use IDs such as OPP-2026-0142 and ORD-2026-0087. IDs should be assigned once and never recalculated from a row number. Row numbers change when records are sorted or archived.

The historical conversion table uses these formulas, assuming the stage is in column A:

Eligible Count:
=COUNTIF(Outcome_History!B:B,A2)

Won Count:
=COUNTIFS(Outcome_History!B:B,A2,Outcome_History!D:D,"Won")

Raw Conversion Rate:
=IFERROR(C2/B2,0)

The Applied Probability remains a manager-approved value. It does not automatically change every time the historical data changes. This prevents a small number of new outcomes from unexpectedly changing the working forecast.

Protect these columns in Opportunities so only the sales manager and automation owners can edit them:

• Manual Base Override
• Override Status
• Override Approved By
• Override Approved At
• Automation Status
• Last Automation Run
• Retry Count
• Error Message

Create filtered views for active opportunities, pending overrides, stale updates, won opportunities without linked orders, and manual-review records. Interface labels can vary between Google Sheets versions, but the underlying requirement is a saved filter that does not alter other users’ views.

Step 4: Connect the Tools

The forms use the Google account connection established by the form owner. The bound Apps Script uses the authorization of the user who installs each trigger. No API key is required for the core implementation.

For the confirmed-order import, save the raw CSV in a date-based Drive folder before replacing data in Orders. Map headers rather than relying on column positions. If the external export changes its headers, stop the forecast run and correct the mapping rather than silently shifting values.

Step 5: Build the Core Automation

Automation 1: Process a representative update

• Trigger: A row is submitted to Rep Update Responses.
• Conditions: The opportunity exists, the submitter is authorized, required fields are present, and values are allowed.
• Actions: Acquire a script lock, check the event key, update the opportunity row, append an audit entry, and record the processed event.
• Fields updated: Stage, close date, risk, notes, last update, status, automation status, and override-request fields where applicable.
• Notification: Managers receive an email when an override is requested.
• Exception: The serialized submission is written to Error_Log for retry or manual review.

Automation 2: Process an override decision

• Trigger: A row is submitted to Override Responses.
• Conditions: The submitter is an authorized manager and the decision is valid.
• Actions: Update approval status, amount, manager email, decision note, and approval timestamp.
• Fields updated: Override Status, Manual Base Override, Override Approved By, Override Approved At, Notes, and Last Updated.
• Notification: The opportunity owner receives the decision.
• Exception: An invalid approval amount or manager identity is logged without changing the forecast.

Automation 3: Calculate the weekly forecast

• Trigger: A manual menu action or the scheduled Monday trigger.
• Conditions: Required sheets and headers exist; probabilities and amounts are valid.
• Actions: Load assumptions, group records by week, calculate scenarios, compare prior snapshots, write the current forecast, and append new snapshots.
• Fields updated: Forecast totals, snapshot rows, automation timestamps, and error fields.
• Notification: Scheduled runs send the completed summary to configured recipients.
• Exception: Invalid records are excluded from totals and listed in the summary as exceptions.

The calculation for an open opportunity is:

Calculated base contribution =
Opportunity amount
× Approved stage probability
× Opportunity risk multiplier

Approved override base contribution =
Manager-approved base amount

Low contribution =
MIN(Opportunity amount, Base contribution × Opportunity low factor)

High contribution =
MIN(Opportunity amount, Base contribution × Opportunity high factor)

A confirmed order uses the same pattern, substituting its order-risk multiplier. The high scenario is capped at the order’s net amount.

Closed-won and closed-lost opportunities are excluded from open pipeline. Confirmed and released orders are counted from the Orders sheet. This prevents the same value from appearing as both an open opportunity and a confirmed order.

Step 6: Add Approvals, Reminders, and Escalations

Overrides are sequential. A representative first submits the requested amount and business reason. A sales manager then approves, rejects, or returns the request.

The Friday reminder identifies active opportunities inside the forecast horizon that have not been updated within seven days. Records are grouped by owner so each representative receives one message rather than one message per opportunity.

Pending overrides are summarized to the configured managers. If an approver is unavailable, another email address listed in MANAGER_EMAILS can act. Delegation changes must be recorded by the system owner.

A reminder failure does not change an opportunity’s status. It creates a notification error that can be retried. This keeps communication problems separate from forecast calculations.

Step 7: Add Documents and File Management

The core workflow does not generate a document for every opportunity. That avoids unnecessary file creation. Google Drive is used for the following controlled items:

• The production workbook and forms.
• A development workbook and test forms.
• Weekly raw order exports.
• Implementation documentation and field definitions.
• Archived management reports when a fixed report is required.

Use folders such as:

Sales Forecast/
  Production/
  Development/
  Order Exports/
    2026/
      2026-07-13_orders.csv
  Documentation/
  Archived Reports/

Use YYYY-MM-DD_description.ext for export names. Restrict shared links to the organization or named users. Do not use public links.

When replacing an order export, retain the prior raw file. Validate row count, total amount, import timestamp, and duplicate order IDs. If an upload fails, leave the previous controlled order data in place and mark the source as stale. Do not partially paste a file into production.

Step 8: Add Reporting and Operational Views

The current forecast refreshes whenever the forecast function runs. There is no separate data extract to refresh. If Looker Studio is added later, it should read a stable reporting range or a separate reporting sheet rather than the editable operational tables.

The dashboard owner should investigate any week where the absolute base forecast change exceeds a locally agreed threshold. The system should report the change without deciding whether it is acceptable.

Step 9: Add Security and Governance Controls

• Give representatives form access instead of broad workbook edit access.
• Protect assumptions, conversion rates, approved overrides, logs, and formulas.
• Keep account names, notes, and email addresses out of the optional AI payload.
• Store API secrets only in Apps Script properties, never in worksheet cells.
• Restrict Drive files to named users or the organization.
• Review Apps Script executions and trigger ownership.
• Remove former employees from forms, files, groups, and manager configuration promptly.
• Retain snapshots according to the company’s reporting and record-retention policy.
• Back up the production workbook before structural changes.
• Require human approval for overrides, rate changes, and operational decisions.

The forecast contains commercially sensitive information. Access should be based on job responsibility. If customer names or sales notes contain personal information, privacy and retention requirements should be assessed before implementation.

Step 10: Deploy and Test

1. Run the initialization function in the development workbook.
2. Load synthetic opportunities, orders, and historical outcomes.
3. Link development forms and verify their response-sheet names.
4. Authorize the script under the designated test owner.
5. Install the form and time-based triggers.
6. Complete every test in the testing checklist later in this case study.
7. Ask two representatives, the sales manager, and the analyst to complete user acceptance testing.
8. Run the old and new forecast processes in parallel for two weekly cycles.
9. Reconcile opportunity counts, order totals, probability assumptions, overrides, and snapshot variance.
10. Copy approved configuration to production and install production triggers under the long-term automation owner.
11. Keep the prior process available for one forecast cycle as a rollback option.
12. Document the owner, backup owner, form URLs, sheet names, trigger schedule, and recovery procedure.

Launch communication should define the submission deadline, explain that form submission does not automatically approve overrides, and identify the support contact. The production workbook should not be activated until the prior and new forecast results can be reconciled from the underlying records.

Code and Configuration

The implementation uses one bound Google Apps Script project. Open the production forecast workbook, open the Apps Script editor from the workbook’s extensions menu, create a file named ForecastAutomation.gs, and paste the complete code below.

Replace these values before running it:

• YOUR_SPREADSHEET_ID
• YOUR_EMAIL_ADDRESS
• YOUR_OPERATIONS_EMAIL_ADDRESS
• Etc/UTC with the workbook’s approved time zone if different

The project requests access to the configured spreadsheet, Gmail sending, script properties, locks, and trigger management. Installable triggers are documented in the official Google Apps Script trigger guide. Review current service limits in the official Apps Script quotas documentation.

var CONFIG = {
  SPREADSHEET_ID: 'YOUR_SPREADSHEET_ID',
  TIME_ZONE: 'Etc/UTC',
  FORECAST_WEEKS: 8,
  PRIOR_WEEKS: 1,
  STALE_DAYS: 7,
  MAX_RETRIES: 3,
  SUMMARY_RECIPIENTS: ['YOUR_EMAIL_ADDRESS'],
  MANAGER_EMAILS: ['YOUR_EMAIL_ADDRESS'],
  OPERATIONS_EMAIL: 'YOUR_OPERATIONS_EMAIL_ADDRESS',
  SHEETS: {
    OPPORTUNITIES: 'Opportunities',
    ORDERS: 'Orders',
    HISTORY: 'Outcome_History',
    RATES: 'Conversion_Rates',
    ASSUMPTIONS: 'Assumptions',
    FORECAST: 'Forecast',
    SNAPSHOTS: 'Forecast_Snapshots',
    PROCESSED: 'Processed_Events',
    ERRORS: 'Error_Log',
    AUDIT: 'Audit_Log',
    REP_RESPONSES: 'Rep Update Responses',
    OVERRIDE_RESPONSES: 'Override Responses'
  }
};

var HEADERS = {
  OPPORTUNITIES: [
    'Opportunity ID', 'Account', 'Owner Email', 'Stage', 'Amount',
    'Expected Close Date', 'Created Date', 'Last Updated',
    'Last Rep Update', 'Risk Code', 'Risk Notes',
    'Requested Override', 'Override Reason', 'Override Status',
    'Manual Base Override', 'Override Approved By',
    'Override Approved At', 'Status', 'Linked Order ID',
    'Automation Status', 'Last Automation Run', 'Retry Count',
    'Error Message', 'Notes'
  ],
  ORDERS: [
    'Order ID', 'Linked Opportunity ID', 'Customer', 'Net Amount',
    'Scheduled Revenue Date', 'Order Status', 'Risk Code',
    'Actual Revenue Date', 'Actual Amount', 'Last Imported At',
    'Source System ID', 'Automation Status', 'Error Message'
  ],
  HISTORY: [
    'Opportunity ID', 'Snapshot Stage', 'Snapshot Date',
    'Outcome', 'Outcome Date'
  ],
  RATES: [
    'Stage', 'Eligible Count', 'Won Count', 'Raw Conversion Rate',
    'Minimum Sample', 'Applied Probability', 'Approved By',
    'Approved Date'
  ],
  ASSUMPTIONS: ['Key', 'Value', 'Description'],
  FORECAST: [
    'Week Start', 'Week End', 'Confirmed Low', 'Confirmed Base',
    'Confirmed High', 'Pipeline Low', 'Pipeline Base',
    'Pipeline High', 'Total Low', 'Total Base', 'Total High',
    'Actual Sales', 'Prior Base', 'Variance Amount',
    'Variance Percent', 'Last Refreshed'
  ],
  SNAPSHOTS: [
    'Snapshot ID', 'Run At', 'Week Start', 'Low Forecast',
    'Base Forecast', 'High Forecast', 'Confirmed Base',
    'Pipeline Base', 'Actual Sales'
  ],
  PROCESSED: [
    'Processed At', 'Event Key', 'Source Sheet', 'Result'
  ],
  ERRORS: [
    'Timestamp', 'Error ID', 'Process', 'Source Key',
    'Error Message', 'Payload JSON', 'Status', 'Retry Count',
    'Last Retry At', 'Resolved At'
  ],
  AUDIT: [
    'Timestamp', 'Action', 'Record Type', 'Record ID',
    'Actor', 'Details'
  ]
};

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Forecast Automation')
    .addItem('Initialize workbook', 'initializeWorkbook')
    .addItem('Install triggers', 'installTriggers')
    .addItem('Run forecast now', 'runWeeklyForecast')
    .addItem('Send weekly forecast now', 'runWeeklyForecastAndEmail')
    .addItem('Send update reminders now', 'sendUpdateReminders')
    .addItem('Retry open errors', 'retryOpenErrors')
    .addSeparator()
    .addItem('Generate optional AI summary', 'generateAiForecastSummary')
    .addItem('Send approved AI summary', 'sendApprovedAiSummary')
    .addToUi();
}

function initializeWorkbook() {
  validateSpreadsheetConfiguration_();
  var ss = getSpreadsheet_();

  createSheetWithHeaders_(
    ss, CONFIG.SHEETS.OPPORTUNITIES, HEADERS.OPPORTUNITIES
  );
  createSheetWithHeaders_(ss, CONFIG.SHEETS.ORDERS, HEADERS.ORDERS);
  createSheetWithHeaders_(ss, CONFIG.SHEETS.HISTORY, HEADERS.HISTORY);
  var ratesSheet = createSheetWithHeaders_(
    ss, CONFIG.SHEETS.RATES, HEADERS.RATES
  );
  var assumptionsSheet = createSheetWithHeaders_(
    ss, CONFIG.SHEETS.ASSUMPTIONS, HEADERS.ASSUMPTIONS
  );
  createSheetWithHeaders_(ss, CONFIG.SHEETS.FORECAST, HEADERS.FORECAST);
  createSheetWithHeaders_(
    ss, CONFIG.SHEETS.SNAPSHOTS, HEADERS.SNAPSHOTS
  );
  createSheetWithHeaders_(
    ss, CONFIG.SHEETS.PROCESSED, HEADERS.PROCESSED
  );
  createSheetWithHeaders_(ss, CONFIG.SHEETS.ERRORS, HEADERS.ERRORS);
  createSheetWithHeaders_(ss, CONFIG.SHEETS.AUDIT, HEADERS.AUDIT);

  if (ratesSheet.getLastRow() === 1) {
    var rates = [
      ['Qualified', '', '', '', 30, 0.18, '', ''],
      ['Discovery', '', '', '', 30, 0.30, '', ''],
      ['Proposal', '', '', '', 30, 0.52, '', ''],
      ['Verbal Commit', '', '', '', 30, 0.78, '', '']
    ];
    ratesSheet.getRange(2, 1, rates.length, rates[0].length)
      .setValues(rates);

    for (var i = 2; i <= rates.length + 1; i++) {
      ratesSheet.getRange(i, 2)
        .setFormula('=COUNTIF(Outcome_History!B:B,A' + i + ')');
      ratesSheet.getRange(i, 3)
        .setFormula(
          '=COUNTIFS(Outcome_History!B:B,A' + i +
          ',Outcome_History!D:D,"Won")'
        );
      ratesSheet.getRange(i, 4)
        .setFormula('=IFERROR(C' + i + '/B' + i + ',0)');
    }
  }

  if (assumptionsSheet.getLastRow() === 1) {
    var assumptions = [
      ['OpportunityLowFactor', 0.75,
        'Low scenario factor for open opportunities'],
      ['OpportunityHighFactor', 1.25,
        'High scenario factor for open opportunities'],
      ['OpportunityRisk.None', 1.00, 'No known risk adjustment'],
      ['OpportunityRisk.Timing', 0.80, 'Timing risk adjustment'],
      ['OpportunityRisk.Budget', 0.65, 'Budget risk adjustment'],
      ['OpportunityRisk.Competition', 0.75,
        'Competitive risk adjustment'],
      ['OpportunityRisk.Decision Maker', 0.85,
        'Decision-maker risk adjustment'],
      ['OrderLowFactor', 0.90,
        'Low scenario factor for confirmed orders'],
      ['OrderHighFactor', 1.10,
        'High scenario factor for confirmed orders'],
      ['OrderRisk.None', 1.00, 'No known order risk'],
      ['OrderRisk.Supply', 0.80, 'Supply risk adjustment'],
      ['OrderRisk.Customer Hold', 0.50,
        'Customer hold adjustment'],
      ['OrderRisk.Credit Hold', 0.25, 'Credit hold adjustment']
    ];
    assumptionsSheet.getRange(
      2, 1, assumptions.length, assumptions[0].length
    ).setValues(assumptions);
  }

  formatWorkbook_(ss);
  appendAudit_(
    'INITIALIZE', 'Workbook', ss.getId(),
    Session.getEffectiveUser().getEmail(),
    'Operational sheets and default assumptions initialized.'
  );
}

function installTriggers() {
  validateSpreadsheetConfiguration_();
  var ss = getSpreadsheet_();
  var handlers = [
    'handleFormSubmit',
    'runWeeklyForecastAndEmail',
    'sendUpdateReminders',
    'retryOpenErrors'
  ];

  var existing = ScriptApp.getProjectTriggers();
  for (var i = 0; i < existing.length; i++) {
    if (handlers.indexOf(existing[i].getHandlerFunction()) !== -1) {
      ScriptApp.deleteTrigger(existing[i]);
    }
  }

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

  ScriptApp.newTrigger('runWeeklyForecastAndEmail')
    .timeBased()
    .onWeekDay(ScriptApp.WeekDay.MONDAY)
    .atHour(7)
    .create();

  ScriptApp.newTrigger('sendUpdateReminders')
    .timeBased()
    .onWeekDay(ScriptApp.WeekDay.FRIDAY)
    .atHour(14)
    .create();

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

  appendAudit_(
    'INSTALL_TRIGGERS', 'Automation', 'TRIGGERS',
    Session.getEffectiveUser().getEmail(),
    'Form, forecast, reminder, and retry triggers installed.'
  );
}

function handleFormSubmit(e) {
  if (!e || !e.range || !e.namedValues) {
    throw new Error(
      'This function must run from an installable spreadsheet form trigger.'
    );
  }

  var lock = LockService.getScriptLock();
  lock.waitLock(30000);

  var sourceSheet = e.range.getSheet().getName();
  var eventKey = [
    e.source.getId(),
    sourceSheet,
    e.range.getRow()
  ].join('|');
  var payload = namedValuesToObject_(e.namedValues);

  try {
    if (isProcessed_(eventKey)) {
      return;
    }

    var notification = withRetry_(function () {
      return dispatchForm_(sourceSheet, payload);
    }, CONFIG.MAX_RETRIES);

    markProcessed_(
      eventKey, sourceSheet,
      notification ? 'Processed with notification' : 'Processed'
    );

    if (notification) {
      sendNotificationSafely_(notification, eventKey);
    }
  } catch (error) {
    recordError_(
      'Form submit',
      eventKey,
      error,
      JSON.stringify({
        sourceSheet: sourceSheet,
        payload: payload
      })
    );
    throw error;
  } finally {
    lock.releaseLock();
  }
}

function dispatchForm_(sourceSheet, payload) {
  if (sourceSheet === CONFIG.SHEETS.REP_RESPONSES) {
    return processRepUpdate_(payload);
  }

  if (sourceSheet === CONFIG.SHEETS.OVERRIDE_RESPONSES) {
    return processOverrideDecision_(payload);
  }

  throw new Error('Unrecognized form response sheet: ' + sourceSheet);
}

function processRepUpdate_(payload) {
  var opportunityId = requiredText_(
    payload['Opportunity ID'], 'Opportunity ID'
  );
  var submitter = normalizeEmail_(
    requiredText_(payload['Work Email'], 'Work Email')
  );
  var stage = requiredText_(payload['Stage'], 'Stage');
  var closeDate = parseDate_(
    requiredText_(
      payload['Expected Close Date'], 'Expected Close Date'
    ),
    'Expected Close Date'
  );
  var riskCode = requiredText_(payload['Risk Code'], 'Risk Code');
  var riskNotes = optionalText_(payload['Risk Notes']);
  var requestOverride =
    optionalText_(payload['Request Override']).toLowerCase() === 'yes';
  var updateNotes = optionalText_(payload['Update Notes']);

  assertAllowed_(
    stage,
    [
      'Qualified', 'Discovery', 'Proposal', 'Verbal Commit',
      'Closed Won', 'Closed Lost'
    ],
    'Stage'
  );
  assertAllowed_(
    riskCode,
    ['None', 'Timing', 'Budget', 'Competition', 'Decision Maker'],
    'Risk Code'
  );

  if (riskCode !== 'None' && !riskNotes) {
    throw new Error('Risk Notes are required for a non-default risk.');
  }

  var record = findRecordById_(
    CONFIG.SHEETS.OPPORTUNITIES,
    'Opportunity ID',
    opportunityId
  );
  var owner = normalizeEmail_(
    getRowValue_(record.values, record.map, 'Owner Email')
  );

  if (submitter !== owner && !isManager_(submitter)) {
    throw new Error(
      'Submitter is not the opportunity owner or an authorized manager.'
    );
  }

  var now = new Date();
  setRowValue_(record.values, record.map, 'Stage', stage);
  setRowValue_(
    record.values, record.map, 'Expected Close Date', closeDate
  );
  setRowValue_(record.values, record.map, 'Risk Code', riskCode);
  setRowValue_(record.values, record.map, 'Risk Notes', riskNotes);
  setRowValue_(record.values, record.map, 'Last Rep Update', now);
  setRowValue_(record.values, record.map, 'Last Updated', now);
  setRowValue_(
    record.values, record.map, 'Status', statusFromStage_(stage)
  );
  setRowValue_(
    record.values, record.map, 'Automation Status', 'Successful'
  );
  setRowValue_(
    record.values, record.map, 'Last Automation Run', now
  );
  setRowValue_(record.values, record.map, 'Retry Count', 0);
  setRowValue_(record.values, record.map, 'Error Message', '');

  if (updateNotes) {
    setRowValue_(
      record.values,
      record.map,
      'Notes',
      appendNote_(
        getRowValue_(record.values, record.map, 'Notes'),
        updateNotes,
        submitter,
        now
      )
    );
  }

  var notification = null;

  if (requestOverride) {
    var requestedAmount = parseNumber_(
      payload['Requested Base Forecast Amount'],
      'Requested Base Forecast Amount'
    );
    var reason = requiredText_(
      payload['Override Reason'], 'Override Reason'
    );
    var opportunityAmount = parseNumber_(
      getRowValue_(record.values, record.map, 'Amount'),
      'Opportunity Amount'
    );

    if (requestedAmount < 0 || requestedAmount > opportunityAmount) {
      throw new Error(
        'Requested override must be between zero and opportunity amount.'
      );
    }

    setRowValue_(
      record.values, record.map, 'Requested Override', requestedAmount
    );
    setRowValue_(
      record.values, record.map, 'Override Reason', reason
    );
    setRowValue_(
      record.values, record.map, 'Override Status', 'Pending'
    );
    setRowValue_(
      record.values, record.map, 'Manual Base Override', ''
    );
    setRowValue_(
      record.values, record.map, 'Override Approved By', ''
    );
    setRowValue_(
      record.values, record.map, 'Override Approved At', ''
    );

    notification = {
      to: CONFIG.MANAGER_EMAILS.join(','),
      subject: 'Forecast override awaiting review: ' + opportunityId,
      body:
        'Opportunity ' + opportunityId +
        ' has a pending forecast override request.\n\n' +
        'Requested amount: ' + requestedAmount + '\n' +
        'Reason: ' + reason + '\n' +
        'Submitted by: ' + submitter
    };
  }

  saveRecord_(record);

  appendAudit_(
    requestOverride ? 'UPDATE_AND_REQUEST_OVERRIDE' : 'UPDATE',
    'Opportunity',
    opportunityId,
    submitter,
    'Stage=' + stage + '; Risk=' + riskCode
  );

  return notification;
}

function processOverrideDecision_(payload) {
  var opportunityId = requiredText_(
    payload['Opportunity ID'], 'Opportunity ID'
  );
  var manager = normalizeEmail_(
    requiredText_(payload['Manager Email'], 'Manager Email')
  );
  var decision = requiredText_(payload['Decision'], 'Decision');
  var decisionNote = optionalText_(payload['Decision Note']);

  if (!isManager_(manager)) {
    throw new Error('Submitted manager is not authorized.');
  }

  assertAllowed_(
    decision,
    ['Approve', 'Reject', 'Return for Information'],
    'Decision'
  );

  var record = findRecordById_(
    CONFIG.SHEETS.OPPORTUNITIES,
    'Opportunity ID',
    opportunityId
  );
  var now = new Date();
  var owner = normalizeEmail_(
    getRowValue_(record.values, record.map, 'Owner Email')
  );

  if (decision === 'Approve') {
    var approvedAmount = parseNumber_(
      payload['Approved Base Forecast Amount'],
      'Approved Base Forecast Amount'
    );
    var opportunityAmount = parseNumber_(
      getRowValue_(record.values, record.map, 'Amount'),
      'Opportunity Amount'
    );

    if (approvedAmount < 0 || approvedAmount > opportunityAmount) {
      throw new Error(
        'Approved amount must be between zero and opportunity amount.'
      );
    }

    setRowValue_(
      record.values, record.map, 'Override Status', 'Approved'
    );
    setRowValue_(
      record.values, record.map, 'Manual Base Override', approvedAmount
    );
    setRowValue_(
      record.values, record.map, 'Override Approved By', manager
    );
    setRowValue_(
      record.values, record.map, 'Override Approved At', now
    );
  } else if (decision === 'Reject') {
    setRowValue_(
      record.values, record.map, 'Override Status', 'Rejected'
    );
    setRowValue_(
      record.values, record.map, 'Manual Base Override', ''
    );
    setRowValue_(
      record.values, record.map, 'Override Approved By', manager
    );
    setRowValue_(
      record.values, record.map, 'Override Approved At', now
    );
  } else {
    setRowValue_(
      record.values, record.map, 'Override Status',
      'Needs Information'
    );
    setRowValue_(
      record.values, record.map, 'Manual Base Override', ''
    );
    setRowValue_(
      record.values, record.map, 'Override Approved By', manager
    );
    setRowValue_(
      record.values, record.map, 'Override Approved At', now
    );
  }

  if (decisionNote) {
    setRowValue_(
      record.values,
      record.map,
      'Notes',
      appendNote_(
        getRowValue_(record.values, record.map, 'Notes'),
        'Override decision: ' + decisionNote,
        manager,
        now
      )
    );
  }

  setRowValue_(record.values, record.map, 'Last Updated', now);
  setRowValue_(
    record.values, record.map, 'Automation Status', 'Successful'
  );
  setRowValue_(
    record.values, record.map, 'Last Automation Run', now
  );
  setRowValue_(record.values, record.map, 'Error Message', '');
  saveRecord_(record);

  appendAudit_(
    'OVERRIDE_' + decision.toUpperCase().replace(/ /g, '_'),
    'Opportunity',
    opportunityId,
    manager,
    decisionNote || 'No decision note supplied.'
  );

  return {
    to: owner,
    subject: 'Forecast override decision: ' + opportunityId,
    body:
      'The forecast override for ' + opportunityId +
      ' was updated to: ' + decision + '.\n\n' +
      'Decision note: ' + (decisionNote || 'None')
  };
}

function runWeeklyForecast() {
  var lock = LockService.getScriptLock();
  lock.waitLock(30000);

  try {
    var result = calculateForecast_();
    SpreadsheetApp.flush();
    return result;
  } catch (error) {
    recordError_(
      'Forecast run',
      Utilities.getUuid(),
      error,
      JSON.stringify({runAt: new Date().toISOString()})
    );
    throw error;
  } finally {
    lock.releaseLock();
  }
}

function runWeeklyForecastAndEmail() {
  var runKey = Utilities.formatDate(
    new Date(), CONFIG.TIME_ZONE, 'YYYY-ww'
  );
  var properties = PropertiesService.getScriptProperties();

  if (properties.getProperty('LAST_WEEKLY_EMAIL_KEY') === runKey) {
    return;
  }

  var result = runWeeklyForecast();
  sendForecastSummary_(result);
  properties.setProperty('LAST_WEEKLY_EMAIL_KEY', runKey);
}

function calculateForecast_() {
  var ss = getSpreadsheet_();
  var runAt = new Date();
  var assumptions = loadAssumptions_();
  var rates = loadConversionRates_();
  var priorSnapshots = loadLatestSnapshotByWeek_();

  var start = startOfWeek_(addDays_(runAt, -7 * CONFIG.PRIOR_WEEKS));
  var weekCount = CONFIG.PRIOR_WEEKS + CONFIG.FORECAST_WEEKS;
  var buckets = {};

  for (var i = 0; i < weekCount; i++) {
    var weekStart = addDays_(start, i * 7);
    var key = dateKey_(weekStart);
    buckets[key] = {
      weekStart: weekStart,
      weekEnd: addDays_(weekStart, 6),
      confirmedLow: 0,
      confirmedBase: 0,
      confirmedHigh: 0,
      pipelineLow: 0,
      pipelineBase: 0,
      pipelineHigh: 0,
      actual: 0
    };
  }

  var exceptions = [];
  var opportunities = getTable_(
    CONFIG.SHEETS.OPPORTUNITIES,
    HEADERS.OPPORTUNITIES
  );

  for (var o = 0; o < opportunities.data.length; o++) {
    var opportunity = opportunities.data[o];
    var opportunityId = optionalText_(
      getRowValue_(
        opportunity, opportunities.map, 'Opportunity ID'
      )
    );

    if (!opportunityId) {
      continue;
    }

    try {
      var status = optionalText_(
        getRowValue_(opportunity, opportunities.map, 'Status')
      );
      var stage = optionalText_(
        getRowValue_(opportunity, opportunities.map, 'Stage')
      );

      if (
        status !== 'Active' ||
        stage === 'Closed Won' ||
        stage === 'Closed Lost'
      ) {
        continue;
      }

      var closeDate = parseDate_(
        getRowValue_(
          opportunity, opportunities.map, 'Expected Close Date'
        ),
        'Expected Close Date'
      );
      var bucket = buckets[dateKey_(startOfWeek_(closeDate))];

      if (!bucket) {
        continue;
      }

      var amount = parseNumber_(
        getRowValue_(opportunity, opportunities.map, 'Amount'),
        'Opportunity Amount'
      );
      if (amount < 0) {
        throw new Error('Opportunity amount cannot be negative.');
      }

      if (typeof rates[stage] !== 'number') {
        throw new Error(
          'No approved conversion probability for stage: ' + stage
        );
      }

      var risk = optionalText_(
        getRowValue_(opportunity, opportunities.map, 'Risk Code')
      ) || 'None';
      var riskMultiplier = assumptionNumber_(
        assumptions, 'OpportunityRisk.' + risk
      );

      var overrideStatus = optionalText_(
        getRowValue_(
          opportunity, opportunities.map, 'Override Status'
        )
      );
      var base;

      if (overrideStatus === 'Approved') {
        base = parseNumber_(
          getRowValue_(
            opportunity, opportunities.map, 'Manual Base Override'
          ),
          'Manual Base Override'
        );
      } else {
        base = amount * rates[stage] * riskMultiplier;
      }

      base = clamp_(base, 0, amount);
      var low = clamp_(
        base * assumptionNumber_(
          assumptions, 'OpportunityLowFactor'
        ),
        0,
        amount
      );
      var high = clamp_(
        base * assumptionNumber_(
          assumptions, 'OpportunityHighFactor'
        ),
        0,
        amount
      );

      bucket.pipelineLow += low;
      bucket.pipelineBase += base;
      bucket.pipelineHigh += high;
    } catch (error) {
      exceptions.push(
        'Opportunity ' + opportunityId + ': ' + error.message
      );
      markSourceRecordError_(
        CONFIG.SHEETS.OPPORTUNITIES,
        opportunities.dataStartRow + o,
        opportunities.map,
        error.message
      );
    }
  }

  var orders = getTable_(CONFIG.SHEETS.ORDERS, HEADERS.ORDERS);

  for (var r = 0; r < orders.data.length; r++) {
    var order = orders.data[r];
    var orderId = optionalText_(
      getRowValue_(order, orders.map, 'Order ID')
    );

    if (!orderId) {
      continue;
    }

    try {
      var orderStatus = optionalText_(
        getRowValue_(order, orders.map, 'Order Status')
      );
      var actualAmountValue = getRowValue_(
        order, orders.map, 'Actual Amount'
      );
      var actualDateValue = getRowValue_(
        order, orders.map, 'Actual Revenue Date'
      );

      if (
        actualAmountValue !== '' &&
        actualDateValue !== ''
      ) {
        var actualDate = parseDate_(
          actualDateValue, 'Actual Revenue Date'
        );
        var actualBucket =
          buckets[dateKey_(startOfWeek_(actualDate))];

        if (actualBucket) {
          actualBucket.actual += parseNumber_(
            actualAmountValue, 'Actual Amount'
          );
        }
      }

      if (
        orderStatus !== 'Confirmed' &&
        orderStatus !== 'Released'
      ) {
        continue;
      }

      var scheduledDate = parseDate_(
        getRowValue_(
          order, orders.map, 'Scheduled Revenue Date'
        ),
        'Scheduled Revenue Date'
      );
      var orderBucket =
        buckets[dateKey_(startOfWeek_(scheduledDate))];

      if (!orderBucket) {
        continue;
      }

      var netAmount = parseNumber_(
        getRowValue_(order, orders.map, 'Net Amount'),
        'Net Amount'
      );
      if (netAmount < 0) {
        throw new Error('Net Amount cannot be negative.');
      }

      var orderRisk = optionalText_(
        getRowValue_(order, orders.map, 'Risk Code')
      ) || 'None';
      var orderBase = clamp_(
        netAmount * assumptionNumber_(
          assumptions, 'OrderRisk.' + orderRisk
        ),
        0,
        netAmount
      );
      var orderLow = clamp_(
        orderBase * assumptionNumber_(
          assumptions, 'OrderLowFactor'
        ),
        0,
        netAmount
      );
      var orderHigh = clamp_(
        orderBase * assumptionNumber_(
          assumptions, 'OrderHighFactor'
        ),
        0,
        netAmount
      );

      orderBucket.confirmedLow += orderLow;
      orderBucket.confirmedBase += orderBase;
      orderBucket.confirmedHigh += orderHigh;
    } catch (error) {
      exceptions.push('Order ' + orderId + ': ' + error.message);
      markSourceRecordError_(
        CONFIG.SHEETS.ORDERS,
        orders.dataStartRow + r,
        orders.map,
        error.message
      );
    }
  }

  var forecastRows = [];
  var snapshotRows = [];
  var resultRows = [];
  var orderedKeys = Object.keys(buckets).sort();

  for (var k = 0; k < orderedKeys.length; k++) {
    var weekKey = orderedKeys[k];
    var item = buckets[weekKey];
    var totalLow = item.confirmedLow + item.pipelineLow;
    var totalBase = item.confirmedBase + item.pipelineBase;
    var totalHigh = item.confirmedHigh + item.pipelineHigh;
    var priorBase =
      Object.prototype.hasOwnProperty.call(priorSnapshots, weekKey)
        ? priorSnapshots[weekKey]
        : '';
    var variance =
      priorBase === '' ? '' : totalBase - priorBase;
    var variancePercent =
      priorBase === '' || priorBase === 0
        ? ''
        : variance / priorBase;

    forecastRows.push([
      item.weekStart,
      item.weekEnd,
      roundCurrency_(item.confirmedLow),
      roundCurrency_(item.confirmedBase),
      roundCurrency_(item.confirmedHigh),
      roundCurrency_(item.pipelineLow),
      roundCurrency_(item.pipelineBase),
      roundCurrency_(item.pipelineHigh),
      roundCurrency_(totalLow),
      roundCurrency_(totalBase),
      roundCurrency_(totalHigh),
      roundCurrency_(item.actual),
      priorBase === '' ? '' : roundCurrency_(priorBase),
      variance === '' ? '' : roundCurrency_(variance),
      variancePercent,
      runAt
    ]);

    snapshotRows.push([
      Utilities.getUuid(),
      runAt,
      item.weekStart,
      roundCurrency_(totalLow),
      roundCurrency_(totalBase),
      roundCurrency_(totalHigh),
      roundCurrency_(item.confirmedBase),
      roundCurrency_(item.pipelineBase),
      roundCurrency_(item.actual)
    ]);

    resultRows.push({
      weekStart: item.weekStart,
      low: roundCurrency_(totalLow),
      base: roundCurrency_(totalBase),
      high: roundCurrency_(totalHigh),
      priorBase: priorBase === '' ? null : roundCurrency_(priorBase),
      variance: variance === '' ? null : roundCurrency_(variance)
    });
  }

  var forecastSheet = ss.getSheetByName(CONFIG.SHEETS.FORECAST);
  forecastSheet.clearContents();
  forecastSheet.getRange(
    1, 1, 1, HEADERS.FORECAST.length
  ).setValues([HEADERS.FORECAST]);
  if (forecastRows.length > 0) {
    forecastSheet.getRange(
      2, 1, forecastRows.length, HEADERS.FORECAST.length
    ).setValues(forecastRows);
  }

  var snapshotSheet = ss.getSheetByName(CONFIG.SHEETS.SNAPSHOTS);
  snapshotSheet.getRange(
    snapshotSheet.getLastRow() + 1,
    1,
    snapshotRows.length,
    HEADERS.SNAPSHOTS.length
  ).setValues(snapshotRows);

  appendAudit_(
    'FORECAST_RUN',
    'Forecast',
    Utilities.getUuid(),
    Session.getEffectiveUser().getEmail() || 'Scheduled automation',
    'Weeks=' + resultRows.length +
      '; Exceptions=' + exceptions.length
  );

  return {
    runAt: runAt,
    rows: resultRows,
    exceptions: exceptions
  };
}

function sendForecastSummary_(result) {
  validateEmailList_(CONFIG.SUMMARY_RECIPIENTS);

  var subject =
    'Weekly sales forecast: ' +
    Utilities.formatDate(
      result.runAt, CONFIG.TIME_ZONE, 'yyyy-MM-dd'
    );
  var html = '<p>The weekly forecast completed successfully.</p>';
  html +=
    '<table border="1" cellpadding="5" cellspacing="0">' +
    '<tr><th>Week</th><th>Low</th>' +
    '<th>Base</th><th>High</th>' +
    '<th>Change</th></tr>';

  var textLines = ['Weekly sales forecast', ''];

  for (var i = 0; i < result.rows.length; i++) {
    var row = result.rows[i];
    var week = dateKey_(row.weekStart);
    var change =
      row.variance === null ? 'No prior snapshot' :
      formatCurrency_(row.variance);

    html +=
      '<tr><td>' + htmlEscape_(week) + '</td>' +
      '<td>' + htmlEscape_(formatCurrency_(row.low)) +
      '</td><td>' +
      htmlEscape_(formatCurrency_(row.base)) +
      '</td><td>' +
      htmlEscape_(formatCurrency_(row.high)) +
      '</td><td>' +
      htmlEscape_(change) + '</td></tr>';

    textLines.push(
      week +
      ' | Low ' + formatCurrency_(row.low) +
      ' | Base ' + formatCurrency_(row.base) +
      ' | High ' + formatCurrency_(row.high) +
      ' | Change ' + change
    );
  }

  html += '</table>';

  if (result.exceptions.length > 0) {
    html +=
      '<p><b>Manual review required:</b> ' +
      result.exceptions.length + ' record(s).</p>';
    textLines.push(
      '',
      'Manual review required: ' +
      result.exceptions.length + ' record(s).'
    );
  }

  sendEmail_({
    to: CONFIG.SUMMARY_RECIPIENTS.join(','),
    subject: subject,
    body: textLines.join('\n'),
    htmlBody: html
  });
}

function sendUpdateReminders() {
  var properties = PropertiesService.getScriptProperties();
  var reminderKey = Utilities.formatDate(
    new Date(), CONFIG.TIME_ZONE, 'yyyy-MM-dd'
  );

  if (properties.getProperty('LAST_REMINDER_KEY') === reminderKey) {
    return;
  }

  var table = getTable_(
    CONFIG.SHEETS.OPPORTUNITIES,
    HEADERS.OPPORTUNITIES
  );
  var now = new Date();
  var horizonEnd = addDays_(now, CONFIG.FORECAST_WEEKS * 7);
  var staleCutoff = addDays_(now, -CONFIG.STALE_DAYS);
  var byOwner = {};
  var pendingOverrideIds = [];

  for (var i = 0; i < table.data.length; i++) {
    var row = table.data[i];
    var id = optionalText_(
      getRowValue_(row, table.map, 'Opportunity ID')
    );
    if (!id) {
      continue;
    }

    var status = optionalText_(
      getRowValue_(row, table.map, 'Status')
    );
    var owner = normalizeEmail_(
      getRowValue_(row, table.map, 'Owner Email')
    );
    var overrideStatus = optionalText_(
      getRowValue_(row, table.map, 'Override Status')
    );

    if (overrideStatus === 'Pending') {
      pendingOverrideIds.push(id);
    }

    if (status !== 'Active' || !owner) {
      continue;
    }

    try {
      var closeDate = parseDate_(
        getRowValue_(row, table.map, 'Expected Close Date'),
        'Expected Close Date'
      );
      var lastUpdateValue = getRowValue_(
        row, table.map, 'Last Rep Update'
      );
      var lastUpdate = lastUpdateValue
        ? parseDate_(lastUpdateValue, 'Last Rep Update')
        : new Date(0);

      if (
        closeDate <= horizonEnd &&
        lastUpdate < staleCutoff
      ) {
        if (!byOwner[owner]) {
          byOwner[owner] = [];
        }
        byOwner[owner].push(id);
      }
    } catch (error) {
      recordError_(
        'Reminder validation',
        'OPPORTUNITY|' + id,
        error,
        JSON.stringify({opportunityId: id})
      );
    }
  }

  var owners = Object.keys(byOwner);
  for (var o = 0; o < owners.length; o++) {
    var email = owners[o];
    sendNotificationSafely_({
      to: email,
      subject: 'Weekly forecast updates required',
      body:
        'Please update these forecast opportunities:\n\n' +
        byOwner[email].join('\n') +
        '\n\nUse the Weekly Opportunity Update form.'
    }, 'REMINDER|' + reminderKey + '|' + email);
  }

  if (pendingOverrideIds.length > 0) {
    sendNotificationSafely_({
      to: CONFIG.MANAGER_EMAILS.join(','),
      subject: 'Forecast overrides awaiting review',
      body:
        'These opportunities have pending override requests:\n\n' +
        pendingOverrideIds.join('\n')
    }, 'OVERRIDES|' + reminderKey);
  }

  checkOrderImportFreshness_(now);
  properties.setProperty('LAST_REMINDER_KEY', reminderKey);
}

function checkOrderImportFreshness_(now) {
  var table = getTable_(CONFIG.SHEETS.ORDERS, HEADERS.ORDERS);
  var latestImport = null;

  for (var i = 0; i < table.data.length; i++) {
    var value = getRowValue_(
      table.data[i], table.map, 'Last Imported At'
    );
    if (!value) {
      continue;
    }
    var importedAt = parseDate_(value, 'Last Imported At');
    if (!latestImport || importedAt > latestImport) {
      latestImport = importedAt;
    }
  }

  if (
    !latestImport ||
    latestImport < addDays_(now, -CONFIG.STALE_DAYS)
  ) {
    sendNotificationSafely_({
      to: CONFIG.OPERATIONS_EMAIL,
      subject: 'Confirmed-order forecast source may be stale',
      body:
        'The Orders sheet has no import timestamp within the last ' +
        CONFIG.STALE_DAYS +
        ' days. Validate the order export before the next forecast.'
    }, 'ORDER_FRESHNESS|' + dateKey_(now));
  }
}

function retryOpenErrors() {
  var sheet = getSpreadsheet_().getSheetByName(CONFIG.SHEETS.ERRORS);
  if (!sheet || sheet.getLastRow() < 2) {
    return;
  }

  var values = sheet.getDataRange().getValues();
  var map = headerMap_(values[0]);

  for (var i = 1; i < values.length; i++) {
    var status = values[i][map['Status']];
    var retryCount = Number(values[i][map['Retry Count']] || 0);

    if (status !== 'Open' || retryCount >= CONFIG.MAX_RETRIES) {
      continue;
    }

    try {
      var processName = values[i][map['Process']];
      var sourceKey = values[i][map['Source Key']];
      var payload = JSON.parse(values[i][map['Payload JSON']] || '{}');
      var notification = null;

      if (processName === 'Form submit') {
        notification = dispatchForm_(
          payload.sourceSheet, payload.payload
        );
        markProcessed_(
          sourceKey, payload.sourceSheet, 'Recovered by retry'
        );
      } else if (processName === 'Notification') {
        sendEmail_(payload);
      } else {
        continue;
      }

      if (notification) {
        sendNotificationSafely_(
          notification, 'RECOVERED|' + sourceKey
        );
      }

      sheet.getRange(i + 1, map['Status'] + 1)
        .setValue('Resolved');
      sheet.getRange(i + 1, map['Retry Count'] + 1)
        .setValue(retryCount + 1);
      sheet.getRange(i + 1, map['Last Retry At'] + 1)
        .setValue(new Date());
      sheet.getRange(i + 1, map['Resolved At'] + 1)
        .setValue(new Date());
    } catch (error) {
      retryCount++;
      sheet.getRange(i + 1, map['Retry Count'] + 1)
        .setValue(retryCount);
      sheet.getRange(i + 1, map['Last Retry At'] + 1)
        .setValue(new Date());
      sheet.getRange(i + 1, map['Error Message'] + 1)
        .setValue(error.message);
      sheet.getRange(i + 1, map['Status'] + 1)
        .setValue(
          retryCount >= CONFIG.MAX_RETRIES
            ? 'Manual Review'
            : 'Open'
        );
    }
  }
}

function sendNotificationSafely_(message, sourceKey) {
  try {
    sendEmail_(message);
  } catch (error) {
    recordError_(
      'Notification',
      sourceKey,
      error,
      JSON.stringify(message)
    );
  }
}

function sendEmail_(message) {
  if (!message || !message.to || !message.subject || !message.body) {
    throw new Error('Email requires to, subject, and body values.');
  }

  var addresses = message.to.split(',');
  validateEmailList_(addresses);

  withRetry_(function () {
    var options = {};
    if (message.htmlBody) {
      options.htmlBody = message.htmlBody;
    }
    GmailApp.sendEmail(
      message.to,
      message.subject,
      message.body,
      options
    );
  }, CONFIG.MAX_RETRIES);
}

function loadAssumptions_() {
  var table = getTable_(
    CONFIG.SHEETS.ASSUMPTIONS,
    HEADERS.ASSUMPTIONS
  );
  var result = {};

  for (var i = 0; i < table.data.length; i++) {
    var key = optionalText_(
      getRowValue_(table.data[i], table.map, 'Key')
    );
    if (!key) {
      continue;
    }
    result[key] = parseNumber_(
      getRowValue_(table.data[i], table.map, 'Value'),
      'Assumption ' + key
    );
  }
  return result;
}

function loadConversionRates_() {
  var table = getTable_(CONFIG.SHEETS.RATES, HEADERS.RATES);
  var result = {};

  for (var i = 0; i < table.data.length; i++) {
    var stage = optionalText_(
      getRowValue_(table.data[i], table.map, 'Stage')
    );
    if (!stage) {
      continue;
    }

    var probability = parseNumber_(
      getRowValue_(
        table.data[i], table.map, 'Applied Probability'
      ),
      'Applied Probability for ' + stage
    );

    if (probability < 0 || probability > 1) {
      throw new Error(
        'Applied probability must be between zero and one: ' + stage
      );
    }
    result[stage] = probability;
  }
  return result;
}

function loadLatestSnapshotByWeek_() {
  var sheet = getSpreadsheet_()
    .getSheetByName(CONFIG.SHEETS.SNAPSHOTS);
  var result = {};

  if (!sheet || sheet.getLastRow() < 2) {
    return result;
  }

  var values = sheet.getDataRange().getValues();
  var map = headerMap_(values[0]);

  for (var i = 1; i < values.length; i++) {
    var week = values[i][map['Week Start']];
    var base = values[i][map['Base Forecast']];
    if (week !== '' && base !== '') {
      result[dateKey_(parseDate_(week, 'Snapshot Week Start'))] =
        Number(base);
    }
  }
  return result;
}

function getTable_(sheetName, requiredHeaders) {
  var sheet = getSpreadsheet_().getSheetByName(sheetName);
  if (!sheet) {
    throw new Error('Required sheet not found: ' + sheetName);
  }

  var lastColumn = Math.max(sheet.getLastColumn(), requiredHeaders.length);
  var lastRow = Math.max(sheet.getLastRow(), 1);
  var values = sheet.getRange(1, 1, lastRow, lastColumn).getValues();
  var map = headerMap_(values[0]);
  requireHeaders_(map, requiredHeaders, sheetName);

  return {
    sheet: sheet,
    headers: values[0],
    map: map,
    data: values.slice(1),
    dataStartRow: 2
  };
}

function findRecordById_(sheetName, idHeader, idValue) {
  var requiredHeaders =
    sheetName === CONFIG.SHEETS.OPPORTUNITIES
      ? HEADERS.OPPORTUNITIES
      : HEADERS.ORDERS;
  var table = getTable_(sheetName, requiredHeaders);

  for (var i = 0; i < table.data.length; i++) {
    if (
      String(getRowValue_(table.data[i], table.map, idHeader)).trim() ===
      String(idValue).trim()
    ) {
      return {
        sheet: table.sheet,
        map: table.map,
        values: table.data[i],
        rowNumber: table.dataStartRow + i
      };
    }
  }

  throw new Error(
    idHeader + ' not found in ' + sheetName + ': ' + idValue
  );
}

function saveRecord_(record) {
  record.sheet.getRange(
    record.rowNumber,
    1,
    1,
    record.values.length
  ).setValues([record.values]);
}

function markSourceRecordError_(
  sheetName, rowNumber, map, errorMessage
) {
  var sheet = getSpreadsheet_().getSheetByName(sheetName);

  if (typeof map['Automation Status'] === 'number') {
    sheet.getRange(rowNumber, map['Automation Status'] + 1)
      .setValue('Manual Review');
  }
  if (typeof map['Error Message'] === 'number') {
    sheet.getRange(rowNumber, map['Error Message'] + 1)
      .setValue(errorMessage);
  }
}

function isProcessed_(eventKey) {
  var table = getTable_(
    CONFIG.SHEETS.PROCESSED,
    HEADERS.PROCESSED
  );

  for (var i = 0; i < table.data.length; i++) {
    if (
      String(
        getRowValue_(table.data[i], table.map, 'Event Key')
      ) === eventKey
    ) {
      return true;
    }
  }
  return false;
}

function markProcessed_(eventKey, sourceSheet, result) {
  var sheet = getSpreadsheet_()
    .getSheetByName(CONFIG.SHEETS.PROCESSED);
  sheet.appendRow([new Date(), eventKey, sourceSheet, result]);
}

function recordError_(processName, sourceKey, error, payloadJson) {
  var ss = getSpreadsheet_();
  var sheet = ss.getSheetByName(CONFIG.SHEETS.ERRORS);

  if (!sheet) {
    sheet = createSheetWithHeaders_(
      ss, CONFIG.SHEETS.ERRORS, HEADERS.ERRORS
    );
  }

  sheet.appendRow([
    new Date(),
    Utilities.getUuid(),
    processName,
    sourceKey,
    error && error.message ? error.message : String(error),
    payloadJson || '{}',
    'Open',
    0,
    '',
    ''
  ]);
}

function appendAudit_(
  action, recordType, recordId, actor, details
) {
  var ss = getSpreadsheet_();
  var sheet = ss.getSheetByName(CONFIG.SHEETS.AUDIT);

  if (!sheet) {
    sheet = createSheetWithHeaders_(
      ss, CONFIG.SHEETS.AUDIT, HEADERS.AUDIT
    );
  }

  sheet.appendRow([
    new Date(),
    action,
    recordType,
    recordId,
    actor || 'Unknown',
    details || ''
  ]);
}

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

  if (sheet.getLastRow() === 0) {
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  } else {
    var existing = sheet.getRange(
      1, 1, 1, Math.max(sheet.getLastColumn(), headers.length)
    ).getValues()[0];
    var map = headerMap_(existing);
    requireHeaders_(map, headers, name);
  }

  sheet.setFrozenRows(1);
  return sheet;
}

function formatWorkbook_(ss) {
  var forecast = ss.getSheetByName(CONFIG.SHEETS.FORECAST);
  forecast.setFrozenRows(1);
  forecast.getRange('A:B').setNumberFormat('yyyy-mm-dd');
  forecast.getRange('C:N').setNumberFormat('$#,##0.00');
  forecast.getRange('O:O').setNumberFormat('0.0%');
  forecast.getRange('P:P').setNumberFormat('yyyy-mm-dd hh:mm');

  var opportunities =
    ss.getSheetByName(CONFIG.SHEETS.OPPORTUNITIES);
  opportunities.setFrozenRows(1);
}

function getSpreadsheet_() {
  validateSpreadsheetConfiguration_();
  return SpreadsheetApp.openById(CONFIG.SPREADSHEET_ID);
}

function validateSpreadsheetConfiguration_() {
  if (
    !CONFIG.SPREADSHEET_ID ||
    CONFIG.SPREADSHEET_ID.indexOf('YOUR_') === 0
  ) {
    throw new Error(
      'Replace YOUR_SPREADSHEET_ID in CONFIG before running.'
    );
  }
}

function headerMap_(headers) {
  var map = {};
  for (var i = 0; i < headers.length; i++) {
    var name = String(headers[i] || '').trim();
    if (name) {
      map[name] = i;
    }
  }
  return map;
}

function requireHeaders_(map, required, sheetName) {
  var missing = [];
  for (var i = 0; i < required.length; i++) {
    if (typeof map[required[i]] !== 'number') {
      missing.push(required[i]);
    }
  }

  if (missing.length > 0) {
    throw new Error(
      'Missing headers in ' + sheetName + ': ' + missing.join(', ')
    );
  }
}

function getRowValue_(row, map, header) {
  if (typeof map[header] !== 'number') {
    throw new Error('Unknown field: ' + header);
  }
  return row[map[header]];
}

function setRowValue_(row, map, header, value) {
  if (typeof map[header] !== 'number') {
    throw new Error('Unknown field: ' + header);
  }
  row[map[header]] = value;
}

function namedValuesToObject_(namedValues) {
  var result = {};
  var keys = Object.keys(namedValues);

  for (var i = 0; i < keys.length; i++) {
    var value = namedValues[keys[i]];
    result[keys[i]] = Array.isArray(value)
      ? String(value[0] || '').trim()
      : String(value || '').trim();
  }
  return result;
}

function requiredText_(value, label) {
  var text = optionalText_(value);
  if (!text) {
    throw new Error(label + ' is required.');
  }
  return text;
}

function optionalText_(value) {
  if (value === null || typeof value === 'undefined') {
    return '';
  }
  return String(value).trim();
}

function parseNumber_(value, label) {
  if (value === '' || value === null || typeof value === 'undefined') {
    throw new Error(label + ' is required.');
  }

  var cleaned = String(value)
    .replace(/[$,]/g, '')
    .trim();
  var number = Number(cleaned);

  if (!isFinite(number)) {
    throw new Error(label + ' must be a valid number.');
  }
  return number;
}

function parseDate_(value, label) {
  var date =
    Object.prototype.toString.call(value) === '[object Date]'
      ? new Date(value.getTime())
      : new Date(value);

  if (isNaN(date.getTime())) {
    throw new Error(label + ' must be a valid date.');
  }
  return date;
}

function assertAllowed_(value, allowed, label) {
  if (allowed.indexOf(value) === -1) {
    throw new Error(
      label + ' must be one of: ' + allowed.join(', ')
    );
  }
}

function statusFromStage_(stage) {
  if (stage === 'Closed Won') {
    return 'Won';
  }
  if (stage === 'Closed Lost') {
    return 'Lost';
  }
  return 'Active';
}

function normalizeEmail_(email) {
  return optionalText_(email).toLowerCase();
}

function isManager_(email) {
  var normalized = normalizeEmail_(email);
  for (var i = 0; i < CONFIG.MANAGER_EMAILS.length; i++) {
    if (normalizeEmail_(CONFIG.MANAGER_EMAILS[i]) === normalized) {
      return true;
    }
  }
  return false;
}

function validateEmailList_(emails) {
  if (!emails || emails.length === 0) {
    throw new Error('At least one recipient is required.');
  }

  for (var i = 0; i < emails.length; i++) {
    var email = normalizeEmail_(emails[i]);
    if (
      !email ||
      email.indexOf('YOUR_') !== -1 ||
      !/^[^@\s]+@[^@\s]+\.[^@\s]+$/.test(email)
    ) {
      throw new Error('Invalid configured email address: ' + emails[i]);
    }
  }
}

function assumptionNumber_(assumptions, key) {
  if (!Object.prototype.hasOwnProperty.call(assumptions, key)) {
    throw new Error('Missing numeric assumption: ' + key);
  }
  return Number(assumptions[key]);
}

function startOfWeek_(date) {
  var result = new Date(date.getTime());
  result.setHours(12, 0, 0, 0);
  var day = result.getDay();
  var difference = day === 0 ? -6 : 1 - day;
  result.setDate(result.getDate() + difference);
  return result;
}

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

function dateKey_(date) {
  return Utilities.formatDate(
    date, CONFIG.TIME_ZONE, 'yyyy-MM-dd'
  );
}

function clamp_(value, minimum, maximum) {
  return Math.min(Math.max(value, minimum), maximum);
}

function roundCurrency_(value) {
  return Math.round((Number(value) + Number.EPSILON) * 100) / 100;
}

function formatCurrency_(value) {
  var number = Number(value || 0);
  return '$' + number.toFixed(2).replace(
    /\B(?=(\d{3})+(?!\d))/g, ','
  );
}

function appendNote_(existing, note, actor, timestamp) {
  var prefix = existing ? String(existing).trim() + '\n' : '';
  return prefix +
    '[' + Utilities.formatDate(
      timestamp, CONFIG.TIME_ZONE, 'yyyy-MM-dd HH:mm'
    ) + '] ' + actor + ': ' + note;
}

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

function withRetry_(operation, attempts) {
  var lastError;

  for (var i = 0; i < attempts; i++) {
    try {
      return operation();
    } catch (error) {
      lastError = error;
      if (i < attempts - 1) {
        Utilities.sleep(Math.pow(2, i) * 1000);
      }
    }
  }

  throw lastError;
}

Authorization and deployment

1. Set the Apps Script project time zone to the same time zone used by the spreadsheet.
2. Run initializeWorkbook manually from the editor.
3. Review the requested permissions and authorize the script using the automation-owner account.
4. Link the two forms and confirm their response tabs use the configured names.
5. Run installTriggers.
6. Open the Apps Script executions page and confirm that the initialization and trigger installation completed successfully.
7. Submit one test representative update and one test approval.
8. Run runWeeklyForecast and inspect Forecast, Forecast_Snapshots, Processed_Events, Error_Log, and Audit_Log.
9. Run runWeeklyForecastAndEmail only after replacing all placeholder email addresses.

Duplicate prevention and recovery

Each spreadsheet form event receives a key containing the spreadsheet ID, response sheet name, and response row. The script checks Processed_Events before applying an update. Reprocessing the same event therefore does not create a second opportunity or apply the same update twice.

Opportunity updates are implemented as an upsert-like update against an existing opportunity ID. Unknown IDs fail validation. A failed event stores its source payload in Error_Log. The daily retry trigger attempts supported failures up to three times before setting the error to Manual Review.

Execution details are visible in the Apps Script executions page. Business-level events remain available in the workbook logs even after platform execution history is no longer convenient for routine investigation.

Failure Handling and Operational Reliability

The Error_Log acts as a lightweight dead-letter queue. A dead-letter queue is a holding area for events that could not be processed normally. Staff identify failed records through the Status, Retry Count, and Error Message columns.

Reconciliation should compare these totals each week:

• Active opportunities in the source process versus Opportunities.
• Confirmed and released orders in the export versus Orders.
• Order export amount versus the imported amount.
• Won opportunities without a linked order.
• Open errors and manual-review records.
• Forecast snapshot count versus expected weeks per run.

A Complete Example

A representative submits an update for opportunity OPP-2026-0142. The opportunity has a value of $48,000 and an expected close date of July 24, 2026.

The form event key is built from the spreadsheet ID, response sheet name, and response row. The script confirms that the event has not been processed and verifies that the submitted work email matches the opportunity owner.

The approved Proposal probability is 52 percent. The Timing risk multiplier is 80 percent. Before override approval, the calculated base is:

$48,000 × 0.52 × 0.80 = $19,968

The requested $14,000 override is marked Pending. It does not affect the calculation yet. The manager receives a Gmail notification.

The manager reviews the phased-release explanation and approves $14,000 through the manager form. Apps Script records the manager email, approval date, approved amount, and audit entry.

The approved override becomes the base contribution:

Low:  $14,000 × 0.75 = $10,500
Base: $14,000
High: $14,000 × 1.25 = $17,500

A confirmed order, ORD-2026-0087, contributes another $22,000 to the same week. It has no known order risk. Its scenario values are $19,800 low, $22,000 base, and $22,000 high because the high result is capped at the order amount.

The combined contribution for this example is therefore:

• Low: $30,300
• Base: $36,000
• High: $39,500

If the prior snapshot for the week had a base value of $31,500, the variance is $4,500, or approximately 14.3 percent. The summary email reports the change but does not claim that the increase is guaranteed.

Implementation Cost

All amounts below are representative assumptions for this scenario. They are not verified client pricing or results. Actual software entitlements, implementation rates, and internal labor costs must be confirmed.

An organization without suitable Google Workspace access must include the required subscription cost. No-cost incremental licensing does not mean the solution has no operating cost. Ownership, monitoring, updates, testing, and support still require labor.

Estimated Time and Cost Savings

The savings model uses the following representative assumptions:

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

560 × 6 ÷ 60 = 56.00 hours

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

Standard handling:
560 × 2 ÷ 60 = 18.67 hours

Exception handling:
560 × 10% × 4 ÷ 60 = 3.73 hours

Maintenance:
3.00 hours

Total new monthly labor:
18.67 + 3.73 + 3.00 = 25.40 hours

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

56.00 - 25.40 = 30.60 hours

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

30.60 × $55 = $1,683

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

$1,683 - $0 incremental software = $1,683

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

$7,370 ÷ $1,683 = 4.38 months

Recovered time does not automatically reduce payroll. It may instead provide additional capacity, quicker forecast preparation, reduced overtime, fewer administrative tasks, or the ability to handle higher opportunity volume without adding the same amount of coordination work.

Non-financial benefits include:

• Clear ownership of stale opportunities and pending approvals.
• Fewer follow-up emails and spreadsheet reconciliations.
• Consistent stage, risk, and scenario definitions.
• Visible separation between calculated values and manual overrides.
• Better audit evidence for management adjustments.
• Reliable week-over-week variance tracking.
• Improved ability to explain forecast changes.
• Lower dependency on one analyst’s spreadsheet knowledge.

Readers should replace volume, handling time, exception rate, labor rate, implementation cost, maintenance time, and incremental licensing assumptions with their own figures.

Adding AI to the Automation

AI is optional and should be added only after the deterministic forecast works reliably. The core benefits in this case come from structured fields, exact identifiers, approved probabilities, risk multipliers, scenario rules, snapshots, and scheduled communication.

AI is not appropriate for calculating the forecast. Formulas and rule-based code are more reliable for multiplication, thresholds, date grouping, exact duplicate detection, permission checks, and approval status.

Potential AI uses include:

• Drafting a concise explanation of week-over-week changes.
• Summarizing aggregated risk-category movement.
• Identifying questions a manager may want to investigate.
• Condensing approved, non-sensitive notes after human review.
• Supporting semantic search across approved historical summaries.

AI must not change an opportunity amount, probability, risk multiplier, scenario result, override, or final forecast total.

The Recommended AI Enhancement

The recommended enhancement drafts a management narrative from aggregated forecast output. It runs after the forecast calculation and writes a proposed summary to AI_Summaries with status Pending Review.

• Trigger: Manual menu action after a completed forecast run.
• AI input: Week, low, base, high, prior base, variance, and counts by risk category.
• Excluded data: Account names, contact names, email addresses, free-text customer notes, credentials, and personal information.
• Expected output: Structured JSON containing an executive summary, material changes, risk observations, follow-up questions, and confidence label.
• Validation: Required keys, data types, confidence values, and output length.
• Record update: Append to AI_Summaries without changing Forecast.
• Human review: A manager changes status from Pending Review to Approved.
• Failure behavior: Log the failure and use the normal deterministic forecast email.

Reusable AI instruction

You summarize a deterministic weekly sales forecast.

Rules:
1. Never recalculate, alter, infer, or replace any supplied forecast value.
2. Treat supplied totals and variances as authoritative.
3. Explain only changes supported by the input.
4. Do not state that forecasted sales are guaranteed.
5. Do not recommend approving an override or making a final business decision.
6. If evidence is insufficient, state that management review is required.
7. Return only JSON matching the supplied schema.
8. Use concise, neutral business language.

Reusable user prompt

Create a weekly management summary from the JSON forecast data below.

Highlight:
- material changes in base forecast by week
- whether changes are increases or decreases
- weeks with a wide low-to-high range
- aggregated risk categories requiring review
- questions management should investigate

Do not change or recalculate any number.
Do not include customer names or speculate about individual customers.

FORECAST_DATA:
{{AGGREGATED_FORECAST_JSON}}

Expected JSON

{
  "executive_summary": "The base forecast increased in two weeks and decreased in one week. Management review is recommended for the week with the widest scenario range.",
  "material_changes": [
    {
      "week_start": "2026-07-20",
      "direction": "increase",
      "amount": 4500,
      "explanation": "The supplied current base exceeds the prior base by 4500."
    }
  ],
  "risk_observations": [
    {
      "risk_category": "Timing",
      "observation": "Timing is the most common non-default risk category in the aggregated input."
    }
  ],
  "follow_up_questions": [
    "What source changes caused the increase for the week beginning 2026-07-20?"
  ],
  "confidence": "medium"
}

The following optional file uses the OpenAI Responses API. Confirm the current API contract in the official Responses API documentation before deployment. Store OPENAI_API_KEY and OPENAI_MODEL in Apps Script properties. Do not place the key in the workbook.

Create a second Apps Script file named AI_Summary.gs and paste this code. It uses helper functions from ForecastAutomation.gs.

var AI_SYSTEM_INSTRUCTION =
  'You summarize a deterministic weekly sales forecast.\n\n' +
  'Rules:\n' +
  '1. Never recalculate, alter, infer, or replace any supplied ' +
  'forecast value.\n' +
  '2. Treat supplied totals and variances as authoritative.\n' +
  '3. Explain only changes supported by the input.\n' +
  '4. Do not state that forecasted sales are guaranteed.\n' +
  '5. Do not recommend approving an override or making a final ' +
  'business decision.\n' +
  '6. If evidence is insufficient, state that management review ' +
  'is required.\n' +
  '7. Return only JSON matching the supplied schema.\n' +
  '8. Use concise, neutral business language.';

function generateAiForecastSummary() {
  var properties = PropertiesService.getScriptProperties();
  var apiKey = properties.getProperty('OPENAI_API_KEY');
  var model = properties.getProperty('OPENAI_MODEL');

  if (!apiKey) {
    throw new Error(
      'Set OPENAI_API_KEY in Apps Script properties.'
    );
  }
  if (!model) {
    throw new Error(
      'Set OPENAI_MODEL to an approved model in Apps Script properties.'
    );
  }

  var input = buildAggregateAiInput_();
  var userPrompt =
    'Create a weekly management summary from the JSON forecast ' +
    'data below.\n\n' +
    'Highlight material changes, direction, wide scenario ranges, ' +
    'aggregated risk categories, and management questions.\n' +
    'Do not change or recalculate any number.\n' +
    'Do not include customer names or speculate about individual ' +
    'customers.\n\nFORECAST_DATA:\n' +
    JSON.stringify(input);

  var schema = {
    type: 'object',
    additionalProperties: false,
    properties: {
      executive_summary: {type: 'string'},
      material_changes: {
        type: 'array',
        items: {
          type: 'object',
          additionalProperties: false,
          properties: {
            week_start: {type: 'string'},
            direction: {
              type: 'string',
              enum: ['increase', 'decrease', 'unchanged']
            },
            amount: {type: 'number'},
            explanation: {type: 'string'}
          },
          required: [
            'week_start', 'direction', 'amount', 'explanation'
          ]
        }
      },
      risk_observations: {
        type: 'array',
        items: {
          type: 'object',
          additionalProperties: false,
          properties: {
            risk_category: {type: 'string'},
            observation: {type: 'string'}
          },
          required: ['risk_category', 'observation']
        }
      },
      follow_up_questions: {
        type: 'array',
        items: {type: 'string'}
      },
      confidence: {
        type: 'string',
        enum: ['low', 'medium', 'high']
      }
    },
    required: [
      'executive_summary',
      'material_changes',
      'risk_observations',
      'follow_up_questions',
      'confidence'
    ]
  };

  var requestBody = {
    model: model,
    instructions: AI_SYSTEM_INSTRUCTION,
    input: userPrompt,
    text: {
      format: {
        type: 'json_schema',
        name: 'weekly_forecast_summary',
        strict: true,
        schema: schema
      }
    }
  };

  var responseJson = callOpenAiResponsesApi_(apiKey, requestBody);
  var outputText = extractResponseText_(responseJson);
  var summary = JSON.parse(outputText);
  validateAiSummary_(summary);

  var sheet = getOrCreateAiSummarySheet_();
  sheet.appendRow([
    Utilities.getUuid(),
    new Date(),
    JSON.stringify(input),
    JSON.stringify(summary),
    summary.executive_summary,
    summary.confidence,
    'Pending Review',
    '',
    ''
  ]);

  appendAudit_(
    'AI_SUMMARY_CREATED',
    'AI Summary',
    sheet.getLastRow(),
    Session.getEffectiveUser().getEmail(),
    'AI output stored as Pending Review. Forecast values unchanged.'
  );

  return summary;
}

function buildAggregateAiInput_() {
  var forecast = getTable_(
    CONFIG.SHEETS.FORECAST,
    HEADERS.FORECAST
  );
  var opportunities = getTable_(
    CONFIG.SHEETS.OPPORTUNITIES,
    HEADERS.OPPORTUNITIES
  );
  var weeks = [];
  var riskCounts = {};

  for (var i = 0; i < forecast.data.length; i++) {
    var row = forecast.data[i];
    var weekStart = getRowValue_(
      row, forecast.map, 'Week Start'
    );

    if (!weekStart) {
      continue;
    }

    weeks.push({
      week_start: dateKey_(parseDate_(weekStart, 'Week Start')),
      low: Number(
        getRowValue_(row, forecast.map, 'Total Low') || 0
      ),
      base: Number(
        getRowValue_(row, forecast.map, 'Total Base') || 0
      ),
      high: Number(
        getRowValue_(row, forecast.map, 'Total High') || 0
      ),
      prior_base: nullableNumber_(
        getRowValue_(row, forecast.map, 'Prior Base')
      ),
      variance: nullableNumber_(
        getRowValue_(row, forecast.map, 'Variance Amount')
      )
    });
  }

  for (var r = 0; r < opportunities.data.length; r++) {
    var opportunity = opportunities.data[r];
    var status = optionalText_(
      getRowValue_(opportunity, opportunities.map, 'Status')
    );

    if (status !== 'Active') {
      continue;
    }

    var risk = optionalText_(
      getRowValue_(opportunity, opportunities.map, 'Risk Code')
    ) || 'None';
    riskCounts[risk] = Number(riskCounts[risk] || 0) + 1;
  }

  return {
    generated_at: new Date().toISOString(),
    currency: 'USD',
    authoritative_values: true,
    weeks: weeks,
    active_opportunity_risk_counts: riskCounts
  };
}

function callOpenAiResponsesApi_(apiKey, requestBody) {
  var url = 'https://api.openai.com/v1/responses';
  var lastError;

  for (var attempt = 0; attempt < 3; attempt++) {
    try {
      var response = UrlFetchApp.fetch(url, {
        method: 'post',
        contentType: 'application/json',
        headers: {
          Authorization: 'Bearer ' + apiKey
        },
        payload: JSON.stringify(requestBody),
        muteHttpExceptions: true
      });

      var status = response.getResponseCode();
      var content = response.getContentText();

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

      if (
        status === 429 ||
        status === 500 ||
        status === 502 ||
        status === 503 ||
        status === 504
      ) {
        throw new Error(
          'Retryable AI API status ' + status + ': ' +
          content.substring(0, 500)
        );
      }

      throw new Error(
        'AI API request failed with status ' + status + ': ' +
        content.substring(0, 500)
      );
    } catch (error) {
      lastError = error;
      if (attempt < 2) {
        Utilities.sleep(Math.pow(2, attempt) * 2000);
      }
    }
  }

  throw lastError;
}

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

  if (!responseJson.output) {
    throw new Error('AI response did not contain output.');
  }

  for (var i = 0; i < responseJson.output.length; i++) {
    var item = responseJson.output[i];
    if (!item.content) {
      continue;
    }

    for (var c = 0; c < item.content.length; c++) {
      if (
        item.content[c].type === 'output_text' &&
        item.content[c].text
      ) {
        return item.content[c].text;
      }
    }
  }

  throw new Error('AI response did not contain structured text.');
}

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

  if (
    typeof summary.executive_summary !== 'string' ||
    summary.executive_summary.length === 0 ||
    summary.executive_summary.length > 2000
  ) {
    throw new Error('Invalid executive_summary.');
  }

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

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

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

  if (
    ['low', 'medium', 'high'].indexOf(summary.confidence) === -1
  ) {
    throw new Error('Invalid AI confidence value.');
  }
}

function sendApprovedAiSummary() {
  var sheet = getOrCreateAiSummarySheet_();
  if (sheet.getLastRow() < 2) {
    throw new Error('No AI summary is available.');
  }

  var values = sheet.getDataRange().getValues();
  var map = headerMap_(values[0]);
  var selectedRow = -1;

  for (var i = values.length - 1; i >= 1; i--) {
    if (
      values[i][map['Review Status']] === 'Approved' &&
      !values[i][map['Sent At']]
    ) {
      selectedRow = i;
      break;
    }
  }

  if (selectedRow === -1) {
    throw new Error(
      'No approved, unsent AI summary was found.'
    );
  }

  var summary = JSON.parse(
    values[selectedRow][map['Output JSON']]
  );

  sendEmail_({
    to: CONFIG.SUMMARY_RECIPIENTS.join(','),
    subject: 'Approved weekly forecast narrative',
    body:
      summary.executive_summary +
      '\n\nThis narrative was AI-assisted and manager-approved. ' +
      'The deterministic forecast remains authoritative.'
  });

  sheet.getRange(selectedRow + 1, map['Sent At'] + 1)
    .setValue(new Date());
  sheet.getRange(selectedRow + 1, map['Review Status'] + 1)
    .setValue('Sent');

  appendAudit_(
    'AI_SUMMARY_SENT',
    'AI Summary',
    values[selectedRow][map['Summary ID']],
    Session.getEffectiveUser().getEmail(),
    'Approved narrative sent. Forecast values unchanged.'
  );
}

function getOrCreateAiSummarySheet_() {
  var ss = getSpreadsheet_();
  var name = 'AI_Summaries';
  var headers = [
    'Summary ID', 'Created At', 'Input JSON', 'Output JSON',
    'Executive Summary', 'Confidence', 'Review Status',
    'Reviewed By', 'Sent At'
  ];
  return createSheetWithHeaders_(ss, name, headers);
}

function nullableNumber_(value) {
  if (value === '' || value === null || typeof value === 'undefined') {
    return null;
  }
  var number = Number(value);
  return isFinite(number) ? number : null;
}

To configure the optional code, open Apps Script project settings and add script properties named OPENAI_API_KEY and OPENAI_MODEL. The model must support the configured structured-output format. Interface labels and supported models can change, so verify the provider documentation before deployment.

Test the function with synthetic aggregate data. Confirm that it creates only an AI_Summaries row and does not edit Forecast, Opportunities, Orders, or Assumptions.

Benefits of the AI Enhancement

• Less time drafting an executive narrative from weekly changes.
• More consistent language across weekly reports.
• Quicker identification of wide scenario ranges.
• Concise aggregation of risk-category counts.
• A reusable set of management follow-up questions.

These are AI-specific benefits. The reliable totals, scenarios, reminders, approvals, snapshots, and variance calculations already exist without AI.

What Remains Rule-Based or Human-Controlled

Estimating the Additional Value of AI

The following assumptions compare summary-writing effort only:

• Four forecast summaries per month.
• Manual narrative drafting after core automation: 20 minutes per summary.
• AI-assisted review: 6 minutes per summary.
• Correction rate: 15 percent, requiring 8 additional minutes.
• AI service failure rate: 5 percent, requiring a 20-minute manual fallback.
• Loaded manager cost: $55 per hour.
• Representative AI usage cost: $4 per month.

Core automation without AI:
4 × 20 minutes = 80 minutes

AI-assisted standard review:
4 × 6 minutes = 24 minutes

Expected correction effort:
4 × 15% × 8 minutes = 4.8 minutes

Expected failure fallback:
4 × 5% × 20 minutes = 4 minutes

Expected AI-assisted effort:
24 + 4.8 + 4 = 32.8 minutes

Additional time recovered:
80 - 32.8 = 47.2 minutes, or 0.79 hours

Additional labor value:
0.79 × $55 = $43.45

Net additional monthly value:
$43.45 - $4 assumed AI usage = $39.45

The AI enhancement offers modest additional capacity in this scenario. Its stronger benefit may be summary consistency rather than direct cost reduction. It does not eliminate review, corrections, service failures, or the deterministic forecast process.

Testing Checklist

Use synthetic sample data and test accounts before processing real commercial information.

Ongoing Maintenance

Credentials and authorizations should be rotated or renewed under company policy. Because installable triggers run as their creator, trigger ownership must be addressed before that employee leaves or changes responsibilities.

Template and header changes require regression testing. A renamed field can interrupt mappings even when the spreadsheet still appears readable to users. Maintain a simple change log that records the date, change, approver, test evidence, and rollback procedure.

When to Move to Dedicated Software

The spreadsheet implementation should not be replaced automatically. It remains useful while the data volume, workflow, and permission model are controlled. A dedicated CRM, sales forecasting platform, database-backed application, or business intelligence architecture becomes more appropriate when several of these conditions appear:

• Transaction volume causes slow recalculation or lengthy script runs.
• Multiple locations need separate security boundaries and approval chains.
• Representatives require a customer-facing or mobile portal.
• Offline data capture becomes a formal requirement.
• Complex product, territory, currency, or revenue-recognition rules are introduced.
• Row-level permissions cannot be managed safely in a shared workbook.
• Formal audit requirements demand stronger immutable records.
• CRM and order APIs must synchronize close to real time.
• Exception rates remain high despite process improvements.
• Maintenance consumes more time than a supported platform would require.
• Advanced probabilistic forecasting or capacity planning becomes necessary.
• API quotas, workbook size, or Apps Script execution limits constrain operations.
• The company requires vendor support commitments or formal service levels.
• Security risk increases because too many users need workbook access.
• Management needs consolidated forecasting across entities, currencies, or business units.

A practical next step may be to retain the Google Forms workflow while moving the system of record to a CRM or relational database. Another option is to retain the governed spreadsheet as an approved planning layer and add Looker Studio or Power BI for broader reporting. The appropriate change depends on which limitation is actually being reached.

Implementation Checklist

• Define the forecast measure, weekly boundary, and planning horizon.
• Confirm low, base, and high scenario assumptions.
• Assign a primary system owner and backup owner.
• Confirm Google Workspace, Apps Script, Gmail, and Drive access.
• Create development and production workbooks.
• Create representative and manager forms.
• Define opportunity, order, history, assumption, snapshot, error, and audit fields.
• Adopt stable opportunity and order identifiers.
• Load historical outcomes and approve conversion probabilities.
• Document risk multipliers and scenario factors.
• Map form questions to sheet fields.
• Map the confirmed-order export by header.
• Protect assumptions, approvals, calculations, and logs.
• Configure ownership and manager email lists.
• Install form-submit, forecast, reminder, and retry triggers.
• Validate duplicate-event prevention.
• Configure approval, rejection, return, and delegation rules.
• Configure reminders and escalation timing.
• Test Gmail recipients and notification failures.
• Create order-import storage and naming conventions.
• Create active, stale, pending, failed, and reconciliation views.
• Validate forecast snapshots and variance calculations.
• Test permissions with representative, manager, analyst, and owner accounts.
• Complete all normal, exception, retry, and recovery tests.
• Run the old and new processes in parallel before launch.
• Document one-time and recurring cost assumptions.
• Replace savings assumptions with actual volume, time, and labor figures.
• Add AI only after the deterministic automation is stable.
• Keep AI summaries separate from authoritative calculations.
• Require human review before distributing AI-generated narrative.
• Schedule permission, credential, conversion-rate, and documentation reviews.
• Define the volume, security, reporting, and maintenance thresholds that would justify dedicated software.