Validating Procurement Savings with Finance in Google Workspace

The Business Situation

Harborstone Industrial Supply is a fictional 92-person distributor of industrial maintenance components. Its four-person procurement team negotiates supplier pricing, rebates, freight concessions, contract credits, and one-time settlements. A six-person finance team is responsible for confirming whether reported savings were reflected in actual purchases and invoices.

The company uses Google Workspace for email, forms, spreadsheets, and document storage. Procurement maintains several Google Sheets, while finance relies on accounting exports and supplier invoices. The tools are familiar, but the savings-validation process is not connected or consistently controlled.

Procurement opens approximately 24 savings initiatives per month. Initiatives range from a unit-price reduction on recurring purchases to a one-time supplier credit. At any time, 30 to 45 initiatives may be waiting for baseline approval, accumulating actual spend, or awaiting final finance validation.

The procurement analyst, category managers, finance business partner, accounts payable analyst, analytics manager, and chief financial officer all need different views of the same information. Procurement needs credit for negotiated improvements. Finance needs evidence that the baseline is valid and that savings were realized. Leadership needs an approved number rather than a total assembled from unverified spreadsheet claims.

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 business decided to establish a controlled savings register with consistent definitions, evidence folders, two-stage finance approval, automated reminders, and a clear distinction between expected, calculated, and finance-approved savings.

The Existing Process

The original process worked chronologically as follows:

1. A category manager negotiated a new price or commercial concession with a supplier.
2. The manager added a row to a personal or category-specific spreadsheet.
3. The baseline price, expected volume, and claimed savings were entered using locally maintained formulas.
4. Supporting emails, quotations, contracts, and invoice extracts were stored in different Drive folders or email threads.
5. At month-end, the procurement analyst combined rows from several spreadsheets into a summary workbook.
6. Finance sampled larger claims and asked for invoices, purchase data, or an explanation of the baseline.
7. Procurement revised the summary manually, sometimes without preserving the original value or reason for the change.
8. Leadership received a total that mixed negotiated opportunities, forecast savings, completed one-time credits, and finance-validated results.

The main problem was not that procurement lacked data. The problem was that the data did not move through a controlled validation process. A negotiated reduction could be commercially valuable, but it was not treated as realized savings until finance confirmed the baseline, actual spend, eligible volume, timing, and supporting evidence.

What the New System Needed to Do

The team documented the requirements before selecting the implementation. This prevented the project from becoming a spreadsheet redesign without process controls.

The business also agreed on a conservative calculation policy. Recurring realized savings would be capped at committed volume, one-time savings would be capped at the claimed and evidenced amount, and finance could approve less than the calculated amount. Finance could not approve more than the calculated amount without changing the underlying evidence and recalculating the record.

Implementation Approaches Considered

Improving the spreadsheet

Adding protected ranges, dropdowns, and formulas would improve data quality, but it would not create a reliable approval process. Users could still bypass intake rules, overwrite earlier values, and separate evidence from the record.

Google Workspace with Apps Script

This approach retained tools already used by staff while adding controlled intake, unique identifiers, document folders, status transitions, approval routing, reminders, and an audit log. It was selected because the transaction volume was moderate and the workflow did not require a customer-facing portal or direct accounting-system posting.

No-code operational database

A no-code database would provide stronger relational views and user interfaces. It remained a reasonable future option, particularly if the workflow expanded to hundreds of initiatives per month. For the representative scenario, it introduced an additional system and recurring licensing requirement without resolving a current volume constraint.

Dedicated procurement software

A procurement or spend-analytics platform could support broader sourcing, contract, supplier, and spend-management requirements. The organization did not yet need that breadth. It first needed a controlled validation process and consistent business definitions.

Custom application

A custom application offered the most flexibility but required application hosting, identity management, testing, security patching, database administration, and support. That responsibility was not proportionate to the workflow.

The Selected Solution

Harborstone selected Google Forms, Google Sheets, Google Drive, and Google Apps Script. Email notifications are sent by Apps Script using the installing administrator’s Google account. Google Sheets also provides the operational register and dashboard source.

The selected implementation removed manual identifier creation, folder setup, calculation rework, approval-request emails, reminder tracking, and monthly consolidation of separate category files.

Human control remained in several places. Procurement still defined the commercial baseline and supplied evidence. Finance approved or rejected the baseline. Finance also made the final decision about the recognized savings amount. The automation performed deterministic calculations, but it did not release payments, post accounting entries, or approve its own results.

System Architecture and Data Flow

• Intake: A procurement request form, an owner update form, and a finance review form in Google Forms.
• System of record: A protected Google Sheets workbook containing Initiatives, Periods, Reviews, Audit Log, Failures, Settings, Dashboard, and Executive Summaries sheets.
• Automation layer: A spreadsheet-bound Google Apps Script project with installable form-submission and daily time-based triggers.
• Document storage: A restricted Google Drive root folder with one case folder and four evidence subfolders per initiative.
• Notifications: Approval, return, rejection, reminder, escalation, and failure messages sent by Apps Script.
• Reporting: Google Sheets formulas, filter views, pivot tables, and optional connected reporting tools.
• AI layer: An optional API call that drafts an executive summary only from finance-approved and data-minimized records.

1. Procurement submission: A category manager submits the request form. Google Forms records the raw response and triggers Apps Script. If required values are missing or invalid, the response remains in the raw form tab and a failure is added to the Failures sheet.
2. Record creation: Apps Script checks the form response identifier for duplicates, acquires a script lock, and generates a record ID. It writes an initiative row with an initial automation status of Processing.
3. Calculation: The script validates the savings type and calculates expected recurring and expected total savings. Currency values are rounded to two decimal places.
4. Folder creation: Apps Script creates a Drive folder named with the record ID and supplier name. It creates baseline, negotiation, actual-spend, and finance-approval subfolders. Accessible source files are copied into the controlled folder.
5. Baseline approval: Finance receives a prefilled review-form link containing the record ID and review stage. The initiative enters Awaiting Baseline Approval.
6. Finance decision: An authorized finance approver submits Approved, Return for Information, or Rejected. Apps Script writes a review row, creates an approval receipt, updates the initiative, and notifies the owner.
7. Measurement: After baseline approval, the owner submits interim or final actual volume, actual spend, period dates, and evidence through the update form.
8. Realized-savings calculation: Apps Script aggregates accepted periods, calculates the actual effective unit price, caps eligible volume at committed volume, and calculates proposed realized savings.
9. Final validation: A final measurement triggers a second finance review. Finance can approve the calculated amount or a lower amount with an exception reason. Approved savings cannot exceed the calculated amount.
10. Completion and reporting: The initiative enters Completed with approval status Final Approved. Dashboards use only the Finance Approved Savings field for validated totals.
11. Failure path: Exceptions are recorded against the initiative and in the Failures sheet. Transient failures are retried up to three times. Unresolved failures enter a manual-review queue.

Data Structure

The workbook uses related sheets rather than placing every event in one wide row. The Initiatives sheet contains the current state. Periods stores each owner update or measurement. Reviews stores every finance decision. Audit Log preserves important events, and Failures acts as a recoverable error queue.

Initiatives

Related records

Periods

Contains multiple updates for one Record ID. Each accepted actuals row records period dates, actual volume, actual spend, one-time evidence, calculated values, evidence links, and the source response identifier.

Reviews

Contains multiple finance decisions for one Record ID. The combination of Review ID and source response identifier is unique.

Audit Log

Contains append-only operational events. It records actor, timestamp, event, old value, new value, and a correlation identifier.

Failures

Contains one row per failed event, including the source form, response identifier, retry count, status, and error message.

The Initiatives sheet is therefore a current-state table, while Periods, Reviews, and Audit Log provide transaction history. Users report from Initiatives but investigate discrepancies through the related sheets and Drive folder.

Workflow Statuses and Ownership

A record can move backward only through an explicit finance return or an authorized correction. Procurement cannot mark its own initiative as approved or completed. Finance cannot change the baseline silently because baseline corrections must arrive through the owner update form or through a documented administrative recovery.

Step-by-Step Implementation

Step 1: Prepare the Accounts and Permissions

1. Create or select a shared Google Workspace account that will own the spreadsheet, forms, Apps Script project, and Drive root folder. Avoid making a departing employee the sole owner.
2. Create a restricted Drive root folder named Procurement Savings Validation. Give the automation owner edit access, procurement leads appropriate access, and finance approvers access required to inspect evidence.
3. Create a blank Google Sheets workbook named Procurement Savings Register inside a restricted administrative folder.
4. Open the workbook, open the Apps Script editor from the spreadsheet’s extension tools, and create a spreadsheet-bound script project.
5. Identify the finance approver email addresses, procurement process owner, automation owner, and escalation recipient. Use shared groups where the organization’s access policy permits them.
6. Create at least four test accounts or test personas: procurement requester, procurement owner, finance approver, and unauthorized user.
7. Confirm that the installing account can create Forms, create folders, copy Drive files, write to the workbook, create installable triggers, and send internal email.
8. Confirm that collected-email functionality is appropriate for the Google Workspace environment. The design assumes authenticated internal respondents.
9. Review Apps Script execution, email, and Drive quotas for the organization’s account type. Exact limits vary, so the administrator should verify current limits before deployment.
10. Create separate development and production workbooks. Test code and forms in development before copying the approved script to production.

The Apps Script runs as the user who installs the triggers, not as the person submitting each form. This service identity must be stable, monitored, and limited to the folders and files required by the process.

Step 2: Build the Intake

The supplied setup function creates three forms. The forms are connected to the system workbook so raw responses remain available even if a subsequent script action fails.

The update form collects a Record ID, update type, measurement dates, actual volume, actual spend, verified one-time amount, optional corrected baseline values, evidence links, notes, and a final-measurement indicator.

The finance review form collects a Record ID, review stage, decision, finance-approved amount, exception type, and notes. Respondent email is collected by Google Forms and checked against the configured finance-approver list.

Google Forms handles required-field and numeric validation. Apps Script performs cross-field validation that Forms cannot enforce reliably, such as checking that the negotiated price is lower than the baseline only when the savings type is recurring.

Direct file-upload questions were not used. File-upload behavior and storage ownership can vary by Workspace configuration. Instead, the automation creates a controlled folder immediately after submission and sends its link to the owner. Existing accessible Drive files can be supplied as links and copied into the controlled folder.

Duplicate submissions are detected using the Google Forms response identifier. Similar business records are also visible through supplier, category, baseline period, and date reporting, but the system does not automatically reject a potentially legitimate second negotiation with the same supplier.

Step 3: Create the System of Record

Run the setup function after replacing the configuration placeholders. It creates the required sheets and exact headers. It also applies status validation, number formats, frozen header rows, and a basic dashboard.

The record naming convention is:

PSV-YYYY-NNNN

Example:
PSV-2026-0042

Period record:
PSV-2026-0042-P001

Finance review:
PSV-2026-0042-R001

A script lock protects sequence generation from simultaneous submissions. The year-specific counter is stored in Apps Script Properties. Before issuing an ID, the script also checks the Initiatives sheet so a reset property does not create a duplicate.

The core recurring calculation is:

Expected recurring savings =
MAX(0, Baseline Unit Price - Negotiated Unit Price)
× Committed Volume

The final recurring calculation uses actual weighted price and a conservative volume cap:

Actual effective unit price =
Total accepted actual spend ÷ Total accepted actual volume

Eligible actual volume =
MIN(Total accepted actual volume, Committed Volume)

Calculated recurring savings =
MAX(0, Baseline Unit Price - Actual Effective Unit Price)
× Eligible actual volume

One-time savings are calculated as:

Calculated one-time savings =
MIN(Claimed One-Time Savings, Highest evidenced one-time amount)

For a mixed initiative, calculated realized savings equal calculated recurring savings plus calculated one-time savings.

The use of the highest evidenced one-time amount, rather than the sum of repeated submissions, prevents duplicate evidence updates from multiplying the same supplier credit. If multiple independent one-time credits are expected under one initiative, the data model should be extended with a separate one-time-benefit table.

Protect the Initiatives, Reviews, Audit Log, Failures, Settings, and Executive Summaries sheets. Most users should receive view access. Only the automation owner and designated backup should edit controlled rows directly. Raw form-response tabs should also be restricted because they may contain rejected or incomplete submissions.

Step 4: Connect the Tools

Authentication uses the Google authorization granted to the Apps Script project owner when the script is first run. No API key is required for the core Google Workspace integration. The script requests access to forms, spreadsheets, Drive, email, triggers, and script properties.

Each destination identifier is written back to the source record. The Drive folder URL is stored in Document Link, the form response ID is stored in External System ID, and each related period and review stores its own source response ID.

Step 5: Build the Core Automation

Automation 1: Create and route a savings initiative

• Trigger: Submission of the procurement request form.
• Conditions: Authenticated respondent, valid email, allowed category and savings type, valid dates, and valid cross-field amounts.
• Actions: Check duplicate response ID, generate Record ID, calculate expected savings, create the initiative row, create the folder structure, copy accessible evidence, and send the baseline-review request.
• Fields updated: Record ID, expected values, status, approval status, folder link, automation fields, due date, and notification timestamp.
• Notification: Finance receives the record summary and prefilled review link. The owner receives the Record ID and controlled folder link.
• Exception: The record is marked Failed or Manual Review, a failure row is created, and the automation owner is alerted.

Automation 2: Accept owner updates and calculate actual results

• Trigger: Submission of the owner update form.
• Conditions: Existing Record ID, authorized respondent, allowed status transition, valid measurement values, and accessible evidence.
• Actions: Create a period row, copy evidence, aggregate accepted periods, calculate actual effective price, calculate recurring and one-time savings, detect common exceptions, and update the initiative.
• Fields updated: Actual volume, actual spend, actual effective unit price, calculated realized savings, exception type, evidence links, final-measurement flag, status, and due date.
• Notification: A final measurement sends finance a prefilled final-review link. Interim measurements do not generate unnecessary finance requests.
• Exception: Invalid transitions, inaccessible evidence, or malformed amounts enter the failure queue without changing an approved value.

Automation 3: Record the finance decision

• Trigger: Submission of the finance review form.
• Conditions: Respondent is an authorized finance approver, stage matches the current workflow, and approved amount does not exceed calculated savings.
• Actions: Create a review record, create a text approval receipt in Drive, update the initiative, write an audit event, and notify the owner.
• Fields updated: Status, approval status, approver, decision date, finance-approved savings, exception type, notes, due date, and completion-notification timestamp.
• Notification: Procurement receives the decision and any return reason. Completed records include the validated amount.
• Exception: Unauthorized or contradictory decisions are rejected and logged for manual review.

Idempotency is based on each form response ID. If Google retries a trigger or an administrator replays a failed event, the script checks whether that response already created a record. It either exits safely or completes only the unfinished notification and folder actions.

Step 6: Add Approvals, Reminders, and Escalations

The workflow uses sequential approvals:

1. Finance approves the baseline before measurement is treated as valid.
2. Procurement records interim and final actuals.
3. Finance approves the final realized amount.

Baseline approval confirms that the baseline period, unit definition, negotiated comparison, committed volume, classification, and evidence are reasonable. It does not confirm that savings have been realized.

Final approval confirms that actual spend and volume support the calculation. Finance may approve a lower value when evidence is incomplete, a portion falls outside the period, the actual unit mix changed, or another documented exception applies.

The approval deadline is three business days. The daily monitor sends the first overdue reminder after the due date and another reminder no more frequently than every two calendar days. At five overdue days, the escalation recipient is copied.

If the assigned approver is unavailable, the configured finance-approver list acts as a controlled pool. An administrator can update the list in Script Properties. Delegation changes should be documented and time-limited.

A rejection closes the current claim. Return for Information sends the record backward without erasing the finance decision. Procurement uses the update form to provide a baseline correction or additional evidence, which then creates another finance review.

Approval evidence consists of the raw Google Forms response, the Reviews row, the audit event, and a plain-text receipt stored in the Finance Approval subfolder. No approval depends solely on an email reply.

Step 7: Add Documents and File Management

Each initiative receives the following folder structure:

PSV-2026-0042 - Supplier Name
01 Baseline Evidence
02 Negotiation Evidence
03 Actual Spend Evidence
04 Finance Approval
Evidence Instructions.txt

Evidence filenames use the Record ID and a response-specific suffix. An example is PSV-2026-0042_A1B2C3D4_Q1-Invoice-Export.xlsx. This supports traceability and avoids overwriting an earlier file with the same name.

The root folder must not permit public or unrestricted link sharing. Access should be inherited from the restricted root where possible. The script adds the requester, owner, and finance approvers as editors only when permitted by Workspace policy.

Original source files are copied rather than moved. This avoids breaking links used by another business process. The controlled copy becomes the evidence used for validation.

Google-native documents retain their version history. Replacing a file with a new upload creates a separate file unless users deliberately manage versions. The preferred process is to add revised evidence as a new file with a date or version suffix, preserving the earlier item.

The script rejects inaccessible or unrecognized file links for submissions that require evidence. Large files and unusual formats should be linked or handled under a documented exception if copying would exceed storage, runtime, or file-size constraints.

When a copy fails, the workflow does not pretend the evidence was stored. It records the error, leaves the event recoverable, and alerts the automation owner.

Step 8: Add Reporting and Operational Views

Create protected filter views or separate reporting tabs for:

• New requests awaiting baseline approval
• Initiatives currently measuring actual results
• Final submissions awaiting finance review
• Returned records awaiting procurement action
• Overdue records based on Next Action Due
• Missing or inaccessible evidence
• Rejected claims
• Records by category and owner
• Measurement periods ending within 30 days
• Recently completed initiatives
• Automation failures and unresolved retries
• Expected savings compared with calculated realized savings
• Calculated realized savings compared with finance-approved savings
• Average elapsed time by workflow stage
• Manual-review queue

The basic Dashboard sheet created by the script uses direct formulas against Initiatives. Analytics staff can add pivot tables by category, owner, savings type, and approval month.

Validated executive reporting must sum Finance Approved Savings only where Approval Status equals Final Approved. Expected Total Savings is a pipeline measure. Calculated Realized Savings is a proposed measure. Neither should be labeled as finance-validated savings.

Refresh is immediate for formulas and occurs when the workbook recalculates. If a separate reporting tool is connected later, set a documented refresh schedule and display the latest refresh timestamp.

The analytics manager owns dashboard definitions. Procurement owns pipeline interpretation, while finance owns the definition of validated savings. Alert thresholds should be documented rather than embedded only in chart formatting.

Step 9: Add Security and Governance Controls

• Limit form access to authenticated internal users where the Workspace configuration supports that control.
• Give most users view access to the register and edit access only through forms.
• Restrict the Settings, Failures, Reviews, Audit Log, and raw response tabs.
• Protect formula-driven and automation-managed columns.
• Store configuration in Apps Script Properties rather than visible cells.
• Never store passwords, API keys, or client secrets in spreadsheet cells.
• Use a dedicated root folder without public link sharing.
• Review owner and approver access quarterly and after role changes.
• Remove former employees from Forms, Sheets, Drive folders, groups, and the Apps Script project.
• Retain raw responses, approval records, and source evidence according to finance and procurement retention policies.
• Export or back up the workbook and verify that Drive retention controls cover supporting evidence.
• Keep accounting entries and payment release outside this workflow unless a separately controlled integration is implemented.
• Do not send supplier names, personal emails, contracts, invoices, or unrestricted notes to the optional AI service.
• Require human review before any AI-generated summary is distributed.

Regulatory requirements vary by industry and jurisdiction. The organization should assess tax, financial-record retention, privacy, contractual confidentiality, and audit requirements before using the workflow for formal financial reporting.

Step 10: Deploy and Test

1. Run the configuration and setup functions in the development workbook.
2. Authorize the requested Google services using the designated development owner.
3. Confirm that the three forms, table sheets, dashboard, settings, and triggers were created.
4. Submit at least one request for each savings type.
5. Use test Drive files rather than real supplier documents.
6. Complete baseline approval, return, rejection, correction, interim measurement, final measurement, and final approval scenarios.
7. Confirm that duplicate responses do not create duplicate initiatives, periods, folders, or reviews.
8. Force controlled failures by using an inaccessible file and an unauthorized finance account.
9. Review Apps Script execution history, the Failures sheet, and Audit Log.
10. Complete user acceptance testing with procurement, finance, analytics, and the backup administrator.
11. Copy the approved script and configuration into the production workbook.
12. Run setup once in production and inspect all generated permissions before releasing form links.
13. Pilot with one procurement category for two to four weeks.
14. Compare system calculations with manually verified examples.
15. Activate the daily monitor only after due dates, recipients, and escalation rules are accepted.
16. Publish short operating instructions defining expected, calculated, and approved savings.
17. Document rollback procedures. Forms can be closed, triggers disabled, and the prior reporting process temporarily restored without deleting captured records.

The automation owner monitors the launch. Procurement and finance each appoint a backup process owner. Users receive a support route for calculation questions, access problems, and failed evidence uploads.

Code and Configuration

The following complete Google Apps Script creates the workbook structure, forms, triggers, calculations, evidence folders, approval routing, reminders, audit events, and recoverable failure queue.

Place the script in the Apps Script project bound to the Procurement Savings Register spreadsheet. Replace the four placeholders in configureSystem, save the project, run configureSystem, and then run setupSystem.

const PSV = Object.freeze({
  SHEETS: {
    INITIATIVES: 'Initiatives',
    PERIODS: 'Periods',
    REVIEWS: 'Reviews',
    AUDIT: 'Audit Log',
    FAILURES: 'Failures',
    SETTINGS: 'Settings',
    DASHBOARD: 'Dashboard',
    EXECUTIVE: 'Executive Summaries'
  },
  REQUEST_FORM_KEY: 'REQUEST_FORM_ID',
  UPDATE_FORM_KEY: 'UPDATE_FORM_ID',
  REVIEW_FORM_KEY: 'REVIEW_FORM_ID',
  STATUSES: [
    'Processing',
    'Awaiting Baseline Approval',
    'Measuring',
    'Awaiting Finance Review',
    'Returned for Information',
    'Completed',
    'Rejected',
    'Manual Review'
  ],
  APPROVAL_STATUSES: [
    'Pending Baseline',
    'Baseline Approved',
    'Returned Baseline',
    'Rejected Baseline',
    'Pending Final',
    'Returned Final',
    'Final Approved',
    'Rejected Final'
  ],
  SAVINGS_TYPES: ['Recurring unit-price', 'One-time', 'Mixed'],
  CATEGORIES: [
    'Direct materials',
    'Indirect spend',
    'Logistics',
    'Technology',
    'Facilities',
    'Professional services',
    'Other'
  ],
  EXCEPTIONS: [
    'None',
    'Price variance',
    'Volume variance',
    'Currency mismatch',
    'Missing evidence',
    'Timing mismatch',
    'Other'
  ]
});

const PSV_HEADERS = Object.freeze({
  INITIATIVES: [
    'Record ID', 'Created Date', 'Last Updated', 'Requester Email',
    'Requester Name', 'Owner Email', 'Category', 'Supplier',
    'Description', 'Savings Type', 'Currency', 'Baseline Unit Price',
    'Negotiated Unit Price', 'Committed Volume',
    'Claimed One-Time Savings', 'Expected Recurring Savings',
    'Expected Total Savings', 'Baseline Period', 'Measurement Start',
    'Measurement End', 'Status', 'Approval Status', 'Finance Approver',
    'Finance Decision Date', 'Finance Approved Savings', 'Actual Volume',
    'Actual Spend', 'Actual Effective Unit Price',
    'Calculated Realized Savings', 'Exception Type', 'Evidence Links',
    'Document Link', 'External System ID', 'Automation Status',
    'Last Automation Run', 'Retry Count', 'Error Message',
    'Next Action Due', 'Last Reminder Date', 'Baseline Notice Sent',
    'Final Notice Sent', 'Completion Notice Sent',
    'Final Measurement Received', 'Notes'
  ],
  PERIODS: [
    'Period ID', 'Record ID', 'Submitted Date', 'Submitted By',
    'Update Type', 'Period Start', 'Period End', 'Actual Volume',
    'Actual Spend', 'Verified One-Time Amount',
    'Actual Effective Unit Price', 'Calculated Recurring Savings',
    'Proposed Realized Savings', 'Evidence Links', 'Final Measurement',
    'External System ID', 'Validation Status', 'Error Message'
  ],
  REVIEWS: [
    'Review ID', 'Record ID', 'Review Date', 'Review Stage', 'Decision',
    'Approver Email', 'Finance Approved Savings', 'Exception Type',
    'Notes', 'Approval Evidence Link', 'External System ID'
  ],
  AUDIT: [
    'Timestamp', 'Record ID', 'Event', 'Actor', 'Details',
    'Old Value', 'New Value', 'Correlation ID'
  ],
  FAILURES: [
    'Failure ID', 'Timestamp', 'Handler', 'Source Form ID',
    'Response ID', 'Record ID', 'Error', 'Retry Count', 'Status',
    'Last Retry', 'Owner'
  ],
  SETTINGS: ['Key', 'Value', 'Description'],
  EXECUTIVE: [
    'Summary ID', 'Period', 'Created Date', 'Model',
    'Input Record Count', 'Approved Savings', 'Headline', 'Summary',
    'Key Points JSON', 'Exceptions JSON', 'Actions JSON',
    'Confidence Note', 'Review Status', 'Reviewer', 'Review Date',
    'Raw JSON'
  ]
});

function configureSystem() {
  const configuration = {
    ROOT_FOLDER_ID: 'YOUR_FOLDER_ID',
    FINANCE_APPROVERS: 'YOUR_EMAIL_ADDRESS',
    AUTOMATION_OWNER_EMAIL: 'YOUR_EMAIL_ADDRESS',
    ESCALATION_EMAIL: 'YOUR_EMAIL_ADDRESS'
  };

  Object.keys(configuration).forEach(function(key) {
    const value = String(configuration[key] || '').trim();
    if (!value || value.indexOf('YOUR_') === 0) {
      throw new Error('Replace the placeholder for ' + key + '.');
    }
  });

  PropertiesService.getScriptProperties().setProperties(configuration);
  console.log('Configuration saved.');
}

function setupSystem() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  if (!ss) {
    throw new Error('Open the target spreadsheet before running setupSystem.');
  }

  const props = PropertiesService.getScriptProperties();
  props.setProperty('SPREADSHEET_ID', ss.getId());

  validateConfiguration_();
  DriveApp.getFolderById(requiredProperty_('ROOT_FOLDER_ID'));

  setupTable_(ss, PSV.SHEETS.INITIATIVES, PSV_HEADERS.INITIATIVES);
  setupTable_(ss, PSV.SHEETS.PERIODS, PSV_HEADERS.PERIODS);
  setupTable_(ss, PSV.SHEETS.REVIEWS, PSV_HEADERS.REVIEWS);
  setupTable_(ss, PSV.SHEETS.AUDIT, PSV_HEADERS.AUDIT);
  setupTable_(ss, PSV.SHEETS.FAILURES, PSV_HEADERS.FAILURES);
  setupTable_(ss, PSV.SHEETS.SETTINGS, PSV_HEADERS.SETTINGS);
  setupTable_(ss, PSV.SHEETS.EXECUTIVE, PSV_HEADERS.EXECUTIVE);
  setupDashboard_(ss);
  applySheetControls_(ss);

  const requestForm = getOrCreateForm_(
    PSV.REQUEST_FORM_KEY,
    createRequestForm_,
    ss.getId()
  );
  const updateForm = getOrCreateForm_(
    PSV.UPDATE_FORM_KEY,
    createUpdateForm_,
    ss.getId()
  );
  const reviewForm = getOrCreateForm_(
    PSV.REVIEW_FORM_KEY,
    createReviewForm_,
    ss.getId()
  );

  installTriggers_(requestForm, updateForm, reviewForm);
  writeSettings_(ss, requestForm, updateForm, reviewForm);

  console.log('Setup complete.');
  console.log('Request form: ' + requestForm.getPublishedUrl());
  console.log('Update form: ' + updateForm.getPublishedUrl());
  console.log('Review form: ' + reviewForm.getPublishedUrl());
}

function validateConfiguration_() {
  requiredProperty_('ROOT_FOLDER_ID');
  requiredProperty_('FINANCE_APPROVERS');
  requiredProperty_('AUTOMATION_OWNER_EMAIL');
  requiredProperty_('ESCALATION_EMAIL');

  financeApprovers_().forEach(function(email) {
    validateEmail_(email, 'Finance approver');
  });
  validateEmail_(
    requiredProperty_('AUTOMATION_OWNER_EMAIL'),
    'Automation owner'
  );
  validateEmail_(
    requiredProperty_('ESCALATION_EMAIL'),
    'Escalation email'
  );
}

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

  if (sheet.getLastRow() === 0) {
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  } else {
    const existing = sheet.getRange(1, 1, 1, headers.length).getValues()[0];
    headers.forEach(function(header, index) {
      if (existing[index] !== header) {
        throw new Error(
          'Header mismatch in ' + name + ' at column ' + (index + 1) +
          '. Expected "' + header + '".'
        );
      }
    });
  }

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

function setupDashboard_(ss) {
  let sheet = ss.getSheetByName(PSV.SHEETS.DASHBOARD);
  if (!sheet) {
    sheet = ss.insertSheet(PSV.SHEETS.DASHBOARD);
  }

  sheet.clear();
  sheet.getRange('A1:B1').setValues([['Metric', 'Value']]);
  sheet.getRange('A2:A9').setValues([
    ['Awaiting baseline approval'],
    ['Awaiting final review'],
    ['Returned for information'],
    ['Automation failures'],
    ['Completed initiatives'],
    ['Finance-approved savings'],
    ['Calculated realized savings'],
    ['Expected total savings']
  ]);

  sheet.getRange('B2').setFormula(
    '=COUNTIF(Initiatives!U2:U,"Awaiting Baseline Approval")'
  );
  sheet.getRange('B3').setFormula(
    '=COUNTIF(Initiatives!U2:U,"Awaiting Finance Review")'
  );
  sheet.getRange('B4').setFormula(
    '=COUNTIF(Initiatives!U2:U,"Returned for Information")'
  );
  sheet.getRange('B5').setFormula(
    '=COUNTIF(Initiatives!AH2:AH,"Failed")'
  );
  sheet.getRange('B6').setFormula(
    '=COUNTIF(Initiatives!V2:V,"Final Approved")'
  );
  sheet.getRange('B7').setFormula(
    '=SUMIF(Initiatives!V2:V,"Final Approved",Initiatives!Y2:Y)'
  );
  sheet.getRange('B8').setFormula(
    '=SUM(Initiatives!AC2:AC)'
  );
  sheet.getRange('B9').setFormula(
    '=SUM(Initiatives!Q2:Q)'
  );

  sheet.getRange('A1:B1').setFontWeight('bold');
  sheet.getRange('B7:B9').setNumberFormat('$#,##0.00');
  sheet.setFrozenRows(1);
}

function applySheetControls_(ss) {
  const initiativeSheet = ss.getSheetByName(PSV.SHEETS.INITIATIVES);
  const headers = PSV_HEADERS.INITIATIVES;

  const statusColumn = headers.indexOf('Status') + 1;
  const approvalColumn = headers.indexOf('Approval Status') + 1;

  const statusValidation = SpreadsheetApp.newDataValidation()
    .requireValueInList(PSV.STATUSES, true)
    .setAllowInvalid(false)
    .build();

  const approvalValidation = SpreadsheetApp.newDataValidation()
    .requireValueInList(PSV.APPROVAL_STATUSES, true)
    .setAllowInvalid(false)
    .build();

  initiativeSheet.getRange(2, statusColumn, initiativeSheet.getMaxRows() - 1, 1)
    .setDataValidation(statusValidation);
  initiativeSheet.getRange(
    2,
    approvalColumn,
    initiativeSheet.getMaxRows() - 1,
    1
  ).setDataValidation(approvalValidation);

  [
    'Baseline Unit Price',
    'Negotiated Unit Price',
    'Claimed One-Time Savings',
    'Expected Recurring Savings',
    'Expected Total Savings',
    'Finance Approved Savings',
    'Actual Spend',
    'Actual Effective Unit Price',
    'Calculated Realized Savings'
  ].forEach(function(header) {
    const column = headers.indexOf(header) + 1;
    initiativeSheet.getRange(
      2,
      column,
      initiativeSheet.getMaxRows() - 1,
      1
    ).setNumberFormat('$#,##0.00');
  });
}

function getOrCreateForm_(propertyKey, builder, spreadsheetId) {
  const props = PropertiesService.getScriptProperties();
  const existingId = props.getProperty(propertyKey);

  if (existingId) {
    return FormApp.openById(existingId);
  }

  const form = builder(spreadsheetId);
  props.setProperty(propertyKey, form.getId());
  return form;
}

function createRequestForm_(spreadsheetId) {
  const form = FormApp.create('Procurement Savings Request');
  form.setDescription(
    'Submit a procurement savings initiative for controlled finance validation.'
  );
  form.setCollectEmail(true);
  form.setConfirmationMessage(
    'The request was received. The Record ID and evidence folder will be sent by email after validation.'
  );

  addTextItem_(form, 'Requester Name', true);
  addEmailItem_(form, 'Owner Email', true);
  addListItem_(form, 'Category', PSV.CATEGORIES, true);
  addTextItem_(form, 'Supplier', true);
  addParagraphItem_(form, 'Description', true);
  addListItem_(form, 'Savings Type', PSV.SAVINGS_TYPES, true);
  addListItem_(form, 'Currency', ['USD'], true);
  addNumberItem_(form, 'Baseline Unit Price', true);
  addNumberItem_(form, 'Negotiated Unit Price', true);
  addNumberItem_(form, 'Committed Volume', true);
  addNumberItem_(form, 'Claimed One-Time Savings', true);
  addTextItem_(form, 'Baseline Period', true);
  addDateItem_(form, 'Measurement Start', true);
  addDateItem_(form, 'Measurement End', true);
  addParagraphItem_(form, 'Existing Evidence File Links', false);
  addParagraphItem_(form, 'Notes', false);

  form.setDestination(
    FormApp.DestinationType.SPREADSHEET,
    spreadsheetId
  );
  return form;
}

function createUpdateForm_(spreadsheetId) {
  const form = FormApp.create('Procurement Savings Owner Update');
  form.setDescription(
    'Submit baseline corrections, additional evidence, interim actuals, or final actuals.'
  );
  form.setCollectEmail(true);
  form.setConfirmationMessage(
    'The update was received and will be validated by the automation.'
  );

  addTextItem_(form, 'Record ID', true);
  addListItem_(form, 'Update Type', [
    'Baseline correction',
    'Additional evidence',
    'Interim actuals',
    'Final actuals'
  ], true);
  addDateItem_(form, 'Period Start', false);
  addDateItem_(form, 'Period End', false);
  addNumberItem_(form, 'Actual Volume', false);
  addNumberItem_(form, 'Actual Spend', false);
  addNumberItem_(form, 'Verified One-Time Amount', false);
  addNumberItem_(form, 'Revised Baseline Unit Price', false);
  addNumberItem_(form, 'Revised Negotiated Unit Price', false);
  addNumberItem_(form, 'Revised Committed Volume', false);
  addNumberItem_(form, 'Revised Claimed One-Time Savings', false);
  addParagraphItem_(form, 'Evidence File Links', false);
  addParagraphItem_(form, 'Notes', false);
  addListItem_(form, 'Final Measurement', ['Yes', 'No'], true);

  form.setDestination(
    FormApp.DestinationType.SPREADSHEET,
    spreadsheetId
  );
  return form;
}

function createReviewForm_(spreadsheetId) {
  const form = FormApp.create('Procurement Savings Finance Review');
  form.setDescription(
    'Restricted finance review for procurement savings baselines and final realized amounts.'
  );
  form.setCollectEmail(true);
  form.setConfirmationMessage(
    'The finance decision was received and will be applied after authorization and validation.'
  );

  addTextItem_(form, 'Record ID', true);
  addListItem_(form, 'Review Stage', ['Baseline', 'Final'], true);
  addListItem_(form, 'Decision', [
    'Approved',
    'Return for Information',
    'Rejected'
  ], true);
  addNumberItem_(form, 'Finance Approved Savings Amount', true);
  addListItem_(form, 'Exception Type', PSV.EXCEPTIONS, true);
  addParagraphItem_(form, 'Notes', false);

  form.setDestination(
    FormApp.DestinationType.SPREADSHEET,
    spreadsheetId
  );
  return form;
}

function addTextItem_(form, title, required) {
  return form.addTextItem().setTitle(title).setRequired(required);
}

function addEmailItem_(form, title, required) {
  const validation = FormApp.createTextValidation()
    .requireTextIsEmail()
    .setHelpText('Enter a valid email address.')
    .build();

  return form.addTextItem()
    .setTitle(title)
    .setRequired(required)
    .setValidation(validation);
}

function addParagraphItem_(form, title, required) {
  return form.addParagraphTextItem()
    .setTitle(title)
    .setRequired(required);
}

function addListItem_(form, title, values, required) {
  return form.addListItem()
    .setTitle(title)
    .setChoiceValues(values)
    .setRequired(required);
}

function addNumberItem_(form, title, required) {
  const validation = FormApp.createTextValidation()
    .requireNumberGreaterThanOrEqualTo(0)
    .setHelpText('Enter a number equal to or greater than zero.')
    .build();

  return form.addTextItem()
    .setTitle(title)
    .setRequired(required)
    .setValidation(validation);
}

function addDateItem_(form, title, required) {
  return form.addDateItem()
    .setTitle(title)
    .setRequired(required);
}

function installTriggers_(requestForm, updateForm, reviewForm) {
  const managedHandlers = [
    'handleRequestSubmit',
    'handleUpdateSubmit',
    'handleReviewSubmit',
    'dailyMonitor'
  ];

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

  ScriptApp.newTrigger('handleRequestSubmit')
    .forForm(requestForm)
    .onFormSubmit()
    .create();

  ScriptApp.newTrigger('handleUpdateSubmit')
    .forForm(updateForm)
    .onFormSubmit()
    .create();

  ScriptApp.newTrigger('handleReviewSubmit')
    .forForm(reviewForm)
    .onFormSubmit()
    .create();

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

function writeSettings_(ss, requestForm, updateForm, reviewForm) {
  const sheet = ss.getSheetByName(PSV.SHEETS.SETTINGS);
  if (sheet.getLastRow() > 1) {
    sheet.getRange(
      2,
      1,
      sheet.getLastRow() - 1,
      PSV_HEADERS.SETTINGS.length
    ).clearContent();
  }

  const values = [
    ['Request Form URL', requestForm.getPublishedUrl(), 'Procurement intake'],
    ['Update Form URL', updateForm.getPublishedUrl(), 'Owner updates'],
    ['Review Form URL', reviewForm.getPublishedUrl(), 'Finance decisions'],
    ['Root Folder ID', requiredProperty_('ROOT_FOLDER_ID'), 'Restricted Drive root'],
    ['Finance Approvers', requiredProperty_('FINANCE_APPROVERS'), 'Comma-separated emails'],
    ['Automation Owner', requiredProperty_('AUTOMATION_OWNER_EMAIL'), 'Failure owner'],
    ['Escalation Email', requiredProperty_('ESCALATION_EMAIL'), 'Overdue escalation']
  ];

  sheet.getRange(2, 1, values.length, values[0].length).setValues(values);
}

function handleRequestSubmit(e) {
  executeHandler_('request', e, processRequestResponse_);
}

function handleUpdateSubmit(e) {
  executeHandler_('update', e, processUpdateResponse_);
}

function handleReviewSubmit(e) {
  executeHandler_('review', e, processReviewResponse_);
}

function executeHandler_(handlerName, event, processor) {
  if (!event || !event.response) {
    throw new Error('A Google Forms submission event is required.');
  }

  try {
    processor(event.response);
  } catch (error) {
    const responseId = safeResponseId_(event.response);
    const formId = event.source && event.source.getId ?
      event.source.getId() : '';
    const values = responseMap_(event.response);
    let recordId = cleanText_(values['Record ID']);

    if (!recordId && responseId) {
      const existing = findRowByValue_(
        PSV.SHEETS.INITIATIVES,
        'External System ID',
        responseId
      );
      if (existing) {
        recordId = existing.data['Record ID'];
      }
    }

    if (recordId) {
      markInitiativeFailure_(recordId, error);
    }

    logFailure_(
      handlerName,
      formId,
      responseId,
      recordId,
      error
    );

    safeSendEmail_(
      requiredProperty_('AUTOMATION_OWNER_EMAIL'),
      'Procurement savings automation failure',
      'Handler: ' + handlerName + '\n' +
      'Record ID: ' + (recordId || 'Not created') + '\n' +
      'Response ID: ' + responseId + '\n' +
      'Error: ' + sanitizeError_(error)
    );

    console.error(error.stack || error);
    throw error;
  }
}

function processRequestResponse_(response) {
  withScriptLock_(function() {
    const values = responseMap_(response);
    const responseId = safeResponseId_(response);
    const existing = findRowByValue_(
      PSV.SHEETS.INITIATIVES,
      'External System ID',
      responseId
    );

    if (existing) {
      audit_(
        existing.data['Record ID'],
        'Duplicate request event ignored',
        respondentEmail_(response),
        'The source response ID already exists.',
        '',
        '',
        responseId
      );

      if (existing.data['Automation Status'] === 'Failed') {
        recoverCurrentRecord_(existing.data);
      }
      return;
    }

    const requesterEmail = respondentEmail_(response);
    const requesterName = requiredText_(values, 'Requester Name');
    const ownerEmail = requiredText_(values, 'Owner Email').toLowerCase();
    const category = requiredText_(values, 'Category');
    const supplier = requiredText_(values, 'Supplier');
    const description = requiredText_(values, 'Description');
    const savingsType = requiredText_(values, 'Savings Type');
    const currency = requiredText_(values, 'Currency');
    const baseline = requiredNumber_(values, 'Baseline Unit Price');
    const negotiated = requiredNumber_(values, 'Negotiated Unit Price');
    const committed = requiredNumber_(values, 'Committed Volume');
    const claimedOneTime = requiredNumber_(
      values,
      'Claimed One-Time Savings'
    );
    const baselinePeriod = requiredText_(values, 'Baseline Period');
    const measurementStart = requiredDate_(values, 'Measurement Start');
    const measurementEnd = requiredDate_(values, 'Measurement End');
    const sourceEvidence = cleanText_(
      values['Existing Evidence File Links']
    );
    const notes = cleanText_(values['Notes']);

    validateEmail_(requesterEmail, 'Requester');
    validateEmail_(ownerEmail, 'Owner');

    if (PSV.CATEGORIES.indexOf(category) === -1) {
      throw new Error('Invalid category.');
    }
    if (PSV.SAVINGS_TYPES.indexOf(savingsType) === -1) {
      throw new Error('Invalid savings type.');
    }
    if (currency !== 'USD') {
      throw new Error('This implementation accepts USD records only.');
    }
    if (measurementEnd.getTime() < measurementStart.getTime()) {
      throw new Error('Measurement End cannot precede Measurement Start.');
    }

    validateSavingsInputs_(
      savingsType,
      baseline,
      negotiated,
      committed,
      claimedOneTime
    );
    validateEvidenceLinks_(sourceEvidence);

    const expected = calculateExpected_(
      savingsType,
      baseline,
      negotiated,
      committed,
      claimedOneTime
    );

    const recordId = nextRecordId_();
    const now = new Date();

    appendObject_(PSV.SHEETS.INITIATIVES, {
      'Record ID': recordId,
      'Created Date': now,
      'Last Updated': now,
      'Requester Email': requesterEmail,
      'Requester Name': requesterName,
      'Owner Email': ownerEmail,
      'Category': category,
      'Supplier': supplier,
      'Description': description,
      'Savings Type': savingsType,
      'Currency': currency,
      'Baseline Unit Price': baseline,
      'Negotiated Unit Price': negotiated,
      'Committed Volume': committed,
      'Claimed One-Time Savings': claimedOneTime,
      'Expected Recurring Savings': expected.recurring,
      'Expected Total Savings': expected.total,
      'Baseline Period': baselinePeriod,
      'Measurement Start': measurementStart,
      'Measurement End': measurementEnd,
      'Status': 'Processing',
      'Approval Status': 'Pending Baseline',
      'Evidence Links': sourceEvidence,
      'External System ID': responseId,
      'Automation Status': 'Processing',
      'Last Automation Run': now,
      'Retry Count': 0,
      'Final Measurement Received': 'No',
      'Notes': notes
    });

    audit_(
      recordId,
      'Initiative created',
      requesterEmail,
      'Initial request accepted.',
      '',
      'Processing',
      responseId
    );

    completeInitialRequest_(recordId, responseId);
  });
}

function completeInitialRequest_(recordId, responseId) {
  const record = requireRecord_(recordId).data;
  const folder = ensureCaseFolder_(record);
  const copied = copyEvidenceFiles_(
    record['Evidence Links'],
    folder,
    '01 Baseline Evidence',
    recordId,
    responseId
  );

  const dueDate = addBusinessDays_(new Date(), 3);

  updateInitiative_(recordId, {
    'Document Link': folder.getUrl(),
    'Evidence Links': mergeLinks_('', copied.join('\n')),
    'Status': 'Awaiting Baseline Approval',
    'Approval Status': 'Pending Baseline',
    'Next Action Due': dueDate,
    'Automation Status': 'Processing',
    'Last Automation Run': new Date(),
    'Error Message': ''
  });

  const refreshed = requireRecord_(recordId).data;
  if (!refreshed['Baseline Notice Sent']) {
    sendBaselineRequest_(refreshed);
    updateInitiative_(recordId, {
      'Baseline Notice Sent': new Date()
    });
  }

  updateInitiative_(recordId, {
    'Automation Status': 'Complete',
    'Last Automation Run': new Date(),
    'Error Message': ''
  });

  audit_(
    recordId,
    'Baseline review requested',
    'Automation',
    'Finance review notification sent.',
    'Processing',
    'Awaiting Baseline Approval',
    responseId
  );
}

function processUpdateResponse_(response) {
  withScriptLock_(function() {
    const values = responseMap_(response);
    const responseId = safeResponseId_(response);
    const recordId = requiredText_(values, 'Record ID').toUpperCase();
    const updateType = requiredText_(values, 'Update Type');
    const submitter = respondentEmail_(response);

    const recordReference = requireRecord_(recordId);
    const record = recordReference.data;
    authorizeOwnerUpdate_(record, submitter);

    const duplicate = findRowByValue_(
      PSV.SHEETS.PERIODS,
      'External System ID',
      responseId
    );

    if (duplicate) {
      audit_(
        recordId,
        'Duplicate update event ignored',
        submitter,
        'The source response ID already exists.',
        '',
        '',
        responseId
      );

      if (record['Automation Status'] === 'Failed') {
        recoverCurrentRecord_(record);
      }
      return;
    }

    const allowedTypes = [
      'Baseline correction',
      'Additional evidence',
      'Interim actuals',
      'Final actuals'
    ];
    if (allowedTypes.indexOf(updateType) === -1) {
      throw new Error('Invalid update type.');
    }

    const evidence = cleanText_(values['Evidence File Links']);
    const notes = cleanText_(values['Notes']);
    const finalMeasurement = requiredText_(values, 'Final Measurement');
    validateEvidenceLinks_(evidence);

    const folder = ensureCaseFolder_(record);
    const evidenceFolderName =
      updateType === 'Baseline correction' ||
      updateType === 'Additional evidence'
        ? '01 Baseline Evidence'
        : '03 Actual Spend Evidence';

    const copiedLinks = copyEvidenceFiles_(
      evidence,
      folder,
      evidenceFolderName,
      recordId,
      responseId
    );

    const periodId = nextRelatedId_(
      PSV.SHEETS.PERIODS,
      'Record ID',
      recordId,
      'P'
    );

    if (updateType === 'Baseline correction') {
      processBaselineCorrection_(
        record,
        values,
        submitter,
        responseId,
        periodId,
        copiedLinks,
        notes
      );
      return;
    }

    if (updateType === 'Additional evidence') {
      processAdditionalEvidence_(
        record,
        submitter,
        responseId,
        periodId,
        copiedLinks,
        notes
      );
      return;
    }

    processActualsUpdate_(
      record,
      values,
      updateType,
      submitter,
      responseId,
      periodId,
      copiedLinks,
      finalMeasurement,
      notes
    );
  });
}

function processBaselineCorrection_(
  record,
  values,
  submitter,
  responseId,
  periodId,
  copiedLinks,
  notes
) {
  if (
    record['Status'] !== 'Returned for Information' ||
    record['Approval Status'] !== 'Returned Baseline'
  ) {
    throw new Error(
      'Baseline corrections are allowed only after a baseline return.'
    );
  }

  const baseline = optionalNumber_(
    values['Revised Baseline Unit Price'],
    record['Baseline Unit Price']
  );
  const negotiated = optionalNumber_(
    values['Revised Negotiated Unit Price'],
    record['Negotiated Unit Price']
  );
  const committed = optionalNumber_(
    values['Revised Committed Volume'],
    record['Committed Volume']
  );
  const claimedOneTime = optionalNumber_(
    values['Revised Claimed One-Time Savings'],
    record['Claimed One-Time Savings']
  );

  validateSavingsInputs_(
    record['Savings Type'],
    baseline,
    negotiated,
    committed,
    claimedOneTime
  );

  if (!copiedLinks.length) {
    throw new Error('A baseline correction requires supporting evidence.');
  }

  const expected = calculateExpected_(
    record['Savings Type'],
    baseline,
    negotiated,
    committed,
    claimedOneTime
  );

  appendObject_(PSV.SHEETS.PERIODS, {
    'Period ID': periodId,
    'Record ID': record['Record ID'],
    'Submitted Date': new Date(),
    'Submitted By': submitter,
    'Update Type': 'Baseline correction',
    'Evidence Links': copiedLinks.join('\n'),
    'Final Measurement': 'No',
    'External System ID': responseId,
    'Validation Status': 'Accepted'
  });

  updateInitiative_(record['Record ID'], {
    'Baseline Unit Price': baseline,
    'Negotiated Unit Price': negotiated,
    'Committed Volume': committed,
    'Claimed One-Time Savings': claimedOneTime,
    'Expected Recurring Savings': expected.recurring,
    'Expected Total Savings': expected.total,
    'Evidence Links': mergeLinks_(
      record['Evidence Links'],
      copiedLinks.join('\n')
    ),
    'Status': 'Awaiting Baseline Approval',
    'Approval Status': 'Pending Baseline',
    'Next Action Due': addBusinessDays_(new Date(), 3),
    'Baseline Notice Sent': '',
    'Completion Notice Sent': '',
    'Automation Status': 'Processing',
    'Last Automation Run': new Date(),
    'Error Message': '',
    'Notes': mergeNotes_(record['Notes'], notes)
  });

  const refreshed = requireRecord_(record['Record ID']).data;
  sendBaselineRequest_(refreshed);
  updateInitiative_(record['Record ID'], {
    'Baseline Notice Sent': new Date(),
    'Automation Status': 'Complete'
  });

  audit_(
    record['Record ID'],
    'Baseline corrected and resubmitted',
    submitter,
    'Corrected baseline returned to finance.',
    'Returned Baseline',
    'Pending Baseline',
    responseId
  );
}

function processAdditionalEvidence_(
  record,
  submitter,
  responseId,
  periodId,
  copiedLinks,
  notes
) {
  if (!copiedLinks.length) {
    throw new Error('An additional-evidence update requires a file link.');
  }

  if (record['Status'] !== 'Returned for Information') {
    throw new Error(
      'Additional evidence can reopen only a returned record.'
    );
  }

  appendObject_(PSV.SHEETS.PERIODS, {
    'Period ID': periodId,
    'Record ID': record['Record ID'],
    'Submitted Date': new Date(),
    'Submitted By': submitter,
    'Update Type': 'Additional evidence',
    'Evidence Links': copiedLinks.join('\n'),
    'Final Measurement': 'No',
    'External System ID': responseId,
    'Validation Status': 'Accepted'
  });

  const returningFinal = record['Approval Status'] === 'Returned Final';
  const newStatus = returningFinal
    ? 'Awaiting Finance Review'
    : 'Awaiting Baseline Approval';
  const newApproval = returningFinal
    ? 'Pending Final'
    : 'Pending Baseline';

  const patch = {
    'Evidence Links': mergeLinks_(
      record['Evidence Links'],
      copiedLinks.join('\n')
    ),
    'Status': newStatus,
    'Approval Status': newApproval,
    'Next Action Due': addBusinessDays_(new Date(), 3),
    'Completion Notice Sent': '',
    'Automation Status': 'Processing',
    'Last Automation Run': new Date(),
    'Error Message': '',
    'Notes': mergeNotes_(record['Notes'], notes)
  };

  if (returningFinal) {
    patch['Final Notice Sent'] = '';
  } else {
    patch['Baseline Notice Sent'] = '';
  }

  updateInitiative_(record['Record ID'], patch);
  const refreshed = requireRecord_(record['Record ID']).data;

  if (returningFinal) {
    sendFinalReviewRequest_(refreshed);
    updateInitiative_(record['Record ID'], {
      'Final Notice Sent': new Date()
    });
  } else {
    sendBaselineRequest_(refreshed);
    updateInitiative_(record['Record ID'], {
      'Baseline Notice Sent': new Date()
    });
  }

  updateInitiative_(record['Record ID'], {
    'Automation Status': 'Complete'
  });

  audit_(
    record['Record ID'],
    'Additional evidence submitted',
    submitter,
    'Returned record resubmitted to finance.',
    record['Approval Status'],
    newApproval,
    responseId
  );
}

function processActualsUpdate_(
  record,
  values,
  updateType,
  submitter,
  responseId,
  periodId,
  copiedLinks,
  finalMeasurement,
  notes
) {
  const validStatuses = ['Measuring', 'Returned for Information'];
  if (validStatuses.indexOf(record['Status']) === -1) {
    throw new Error('The record is not open for actuals submission.');
  }

  if (
    record['Status'] === 'Returned for Information' &&
    record['Approval Status'] !== 'Returned Final'
  ) {
    throw new Error('Actuals cannot resolve a returned baseline review.');
  }

  const periodStart = requiredDate_(values, 'Period Start');
  const periodEnd = requiredDate_(values, 'Period End');
  const actualVolume = optionalNumber_(values['Actual Volume'], 0);
  const actualSpend = optionalNumber_(values['Actual Spend'], 0);
  const verifiedOneTime = optionalNumber_(
    values['Verified One-Time Amount'],
    0
  );

  if (periodEnd.getTime() < periodStart.getTime()) {
    throw new Error('Period End cannot precede Period Start.');
  }

  if (
    record['Savings Type'] !== 'One-time' &&
    actualVolume <= 0
  ) {
    throw new Error(
      'Actual Volume must be greater than zero for recurring savings.'
    );
  }

  if (
    record['Savings Type'] !== 'Recurring unit-price' &&
    updateType === 'Final actuals' &&
    verifiedOneTime <= 0
  ) {
    throw new Error(
      'A final one-time or mixed submission requires an evidenced amount.'
    );
  }

  if (updateType === 'Final actuals' && !copiedLinks.length) {
    throw new Error('Final actuals require supporting evidence.');
  }

  const effectivePrice = actualVolume > 0
    ? roundCurrency_(actualSpend / actualVolume)
    : 0;

  const periodRecurring =
    record['Savings Type'] === 'One-time'
      ? 0
      : roundCurrency_(
          Math.max(
            0,
            Number(record['Baseline Unit Price']) - effectivePrice
          ) * Math.min(actualVolume, Number(record['Committed Volume']))
        );

  const proposedPeriod = roundCurrency_(
    periodRecurring +
    Math.min(
      verifiedOneTime,
      Number(record['Claimed One-Time Savings'])
    )
  );

  appendObject_(PSV.SHEETS.PERIODS, {
    'Period ID': periodId,
    'Record ID': record['Record ID'],
    'Submitted Date': new Date(),
    'Submitted By': submitter,
    'Update Type': updateType,
    'Period Start': periodStart,
    'Period End': periodEnd,
    'Actual Volume': actualVolume,
    'Actual Spend': actualSpend,
    'Verified One-Time Amount': verifiedOneTime,
    'Actual Effective Unit Price': effectivePrice,
    'Calculated Recurring Savings': periodRecurring,
    'Proposed Realized Savings': proposedPeriod,
    'Evidence Links': copiedLinks.join('\n'),
    'Final Measurement': finalMeasurement,
    'External System ID': responseId,
    'Validation Status': 'Accepted'
  });

  const aggregate = aggregateActuals_(record['Record ID'], record);
  const isFinal =
    updateType === 'Final actuals' || finalMeasurement === 'Yes';

  const patch = {
    'Actual Volume': aggregate.actualVolume,
    'Actual Spend': aggregate.actualSpend,
    'Actual Effective Unit Price': aggregate.effectivePrice,
    'Calculated Realized Savings': aggregate.realizedSavings,
    'Exception Type': aggregate.exceptionType,
    'Evidence Links': mergeLinks_(
      record['Evidence Links'],
      copiedLinks.join('\n')
    ),
    'Status': isFinal ? 'Awaiting Finance Review' : 'Measuring',
    'Approval Status': isFinal
      ? 'Pending Final'
      : 'Baseline Approved',
    'Final Measurement Received': isFinal ? 'Yes' : 'No',
    'Next Action Due': isFinal
      ? addBusinessDays_(new Date(), 3)
      : record['Measurement End'],
    'Final Notice Sent': isFinal ? '' : record['Final Notice Sent'],
    'Automation Status': 'Processing',
    'Last Automation Run': new Date(),
    'Error Message': '',
    'Notes': mergeNotes_(record['Notes'], notes)
  };

  updateInitiative_(record['Record ID'], patch);

  if (isFinal) {
    const refreshed = requireRecord_(record['Record ID']).data;
    sendFinalReviewRequest_(refreshed);
    updateInitiative_(record['Record ID'], {
      'Final Notice Sent': new Date()
    });
  }

  updateInitiative_(record['Record ID'], {
    'Automation Status': 'Complete'
  });

  audit_(
    record['Record ID'],
    isFinal ? 'Final actuals submitted' : 'Interim actuals submitted',
    submitter,
    'Actual spend and volume were recalculated.',
    record['Status'],
    isFinal ? 'Awaiting Finance Review' : 'Measuring',
    responseId
  );
}

function processReviewResponse_(response) {
  withScriptLock_(function() {
    const values = responseMap_(response);
    const responseId = safeResponseId_(response);
    const recordId = requiredText_(values, 'Record ID').toUpperCase();
    const reviewStage = requiredText_(values, 'Review Stage');
    const decision = requiredText_(values, 'Decision');
    const approver = respondentEmail_(response);
    const approvedAmount = requiredNumber_(
      values,
      'Finance Approved Savings Amount'
    );
    const exceptionType = requiredText_(values, 'Exception Type');
    const notes = cleanText_(values['Notes']);

    authorizeFinance_(approver);

    const duplicate = findRowByValue_(
      PSV.SHEETS.REVIEWS,
      'External System ID',
      responseId
    );

    const record = requireRecord_(recordId).data;

    if (duplicate) {
      audit_(
        recordId,
        'Duplicate review event ignored',
        approver,
        'The source response ID already exists.',
        '',
        '',
        responseId
      );

      if (record['Automation Status'] === 'Failed') {
        recoverCurrentRecord_(record);
      }
      return;
    }

    if (['Baseline', 'Final'].indexOf(reviewStage) === -1) {
      throw new Error('Invalid review stage.');
    }
    if (
      ['Approved', 'Return for Information', 'Rejected']
        .indexOf(decision) === -1
    ) {
      throw new Error('Invalid finance decision.');
    }
    if (PSV.EXCEPTIONS.indexOf(exceptionType) === -1) {
      throw new Error('Invalid exception type.');
    }

    validateReviewTransition_(record, reviewStage);

    if (reviewStage === 'Final' && decision === 'Approved') {
      const calculated = Number(record['Calculated Realized Savings']) || 0;
      if (approvedAmount > calculated + 0.01) {
        throw new Error(
          'Finance Approved Savings cannot exceed Calculated Realized Savings.'
        );
      }
      if (
        approvedAmount < calculated - 0.01 &&
        (exceptionType === 'None' || !notes)
      ) {
        throw new Error(
          'A lower final amount requires an exception type and notes.'
        );
      }
    }

    const reviewId = nextRelatedId_(
      PSV.SHEETS.REVIEWS,
      'Record ID',
      recordId,
      'R'
    );

    const receiptUrl = createApprovalReceipt_(
      record,
      reviewId,
      reviewStage,
      decision,
      approver,
      approvedAmount,
      exceptionType,
      notes
    );

    appendObject_(PSV.SHEETS.REVIEWS, {
      'Review ID': reviewId,
      'Record ID': recordId,
      'Review Date': new Date(),
      'Review Stage': reviewStage,
      'Decision': decision,
      'Approver Email': approver,
      'Finance Approved Savings':
        reviewStage === 'Final' && decision === 'Approved'
          ? approvedAmount
          : '',
      'Exception Type': exceptionType,
      'Notes': notes,
      'Approval Evidence Link': receiptUrl,
      'External System ID': responseId
    });

    const transition = financeTransition_(
      reviewStage,
      decision,
      approvedAmount
    );

    updateInitiative_(recordId, {
      'Status': transition.status,
      'Approval Status': transition.approvalStatus,
      'Finance Approver': approver,
      'Finance Decision Date': new Date(),
      'Finance Approved Savings': transition.approvedSavings,
      'Exception Type': exceptionType,
      'Next Action Due': transition.nextActionDue,
      'Completion Notice Sent': '',
      'Automation Status': 'Processing',
      'Last Automation Run': new Date(),
      'Error Message': '',
      'Notes': mergeNotes_(record['Notes'], notes)
    });

    const refreshed = requireRecord_(recordId).data;
    sendFinanceOutcome_(refreshed, reviewStage, decision, notes);

    updateInitiative_(recordId, {
      'Completion Notice Sent': new Date(),
      'Automation Status': 'Complete'
    });

    audit_(
      recordId,
      reviewStage + ' finance decision',
      approver,
      decision + (notes ? ': ' + notes : ''),
      record['Approval Status'],
      transition.approvalStatus,
      responseId
    );
  });
}

function validateReviewTransition_(record, reviewStage) {
  if (
    reviewStage === 'Baseline' &&
    (
      record['Status'] !== 'Awaiting Baseline Approval' ||
      record['Approval Status'] !== 'Pending Baseline'
    )
  ) {
    throw new Error(
      'The record is not awaiting a baseline decision.'
    );
  }

  if (
    reviewStage === 'Final' &&
    (
      record['Status'] !== 'Awaiting Finance Review' ||
      record['Approval Status'] !== 'Pending Final'
    )
  ) {
    throw new Error(
      'The record is not awaiting a final decision.'
    );
  }
}

function financeTransition_(stage, decision, approvedAmount) {
  if (stage === 'Baseline' && decision === 'Approved') {
    return {
      status: 'Measuring',
      approvalStatus: 'Baseline Approved',
      approvedSavings: '',
      nextActionDue: ''
    };
  }
  if (stage === 'Baseline' && decision === 'Return for Information') {
    return {
      status: 'Returned for Information',
      approvalStatus: 'Returned Baseline',
      approvedSavings: '',
      nextActionDue: addBusinessDays_(new Date(), 3)
    };
  }
  if (stage === 'Baseline' && decision === 'Rejected') {
    return {
      status: 'Rejected',
      approvalStatus: 'Rejected Baseline',
      approvedSavings: '',
      nextActionDue: ''
    };
  }
  if (stage === 'Final' && decision === 'Approved') {
    return {
      status: 'Completed',
      approvalStatus: 'Final Approved',
      approvedSavings: roundCurrency_(approvedAmount),
      nextActionDue: ''
    };
  }
  if (stage === 'Final' && decision === 'Return for Information') {
    return {
      status: 'Returned for Information',
      approvalStatus: 'Returned Final',
      approvedSavings: '',
      nextActionDue: addBusinessDays_(new Date(), 3)
    };
  }
  return {
    status: 'Rejected',
    approvalStatus: 'Rejected Final',
    approvedSavings: '',
    nextActionDue: ''
  };
}

function aggregateActuals_(recordId, record) {
  const sheet = getSpreadsheet_().getSheetByName(PSV.SHEETS.PERIODS);
  const data = getTableObjects_(sheet);

  let actualVolume = 0;
  let actualSpend = 0;
  let highestOneTime = 0;

  data.forEach(function(row) {
    if (
      row['Record ID'] === recordId &&
      row['Validation Status'] === 'Accepted' &&
      ['Interim actuals', 'Final actuals'].indexOf(row['Update Type']) !== -1
    ) {
      actualVolume += Number(row['Actual Volume']) || 0;
      actualSpend += Number(row['Actual Spend']) || 0;
      highestOneTime = Math.max(
        highestOneTime,
        Number(row['Verified One-Time Amount']) || 0
      );
    }
  });

  const effectivePrice = actualVolume > 0
    ? roundCurrency_(actualSpend / actualVolume)
    : 0;
  const eligibleVolume = Math.min(
    actualVolume,
    Number(record['Committed Volume']) || 0
  );

  const recurring =
    record['Savings Type'] === 'One-time'
      ? 0
      : roundCurrency_(
          Math.max(
            0,
            Number(record['Baseline Unit Price']) - effectivePrice
          ) * eligibleVolume
        );

  const oneTime =
    record['Savings Type'] === 'Recurring unit-price'
      ? 0
      : Math.min(
          highestOneTime,
          Number(record['Claimed One-Time Savings']) || 0
        );

  let exceptionType = 'None';
  if (
    actualVolume < Number(record['Committed Volume']) &&
    record['Savings Type'] !== 'One-time'
  ) {
    exceptionType = 'Volume variance';
  }
  if (
    effectivePrice > Number(record['Negotiated Unit Price']) + 0.01 &&
    record['Savings Type'] !== 'One-time'
  ) {
    exceptionType = 'Price variance';
  }

  return {
    actualVolume: roundQuantity_(actualVolume),
    actualSpend: roundCurrency_(actualSpend),
    effectivePrice: effectivePrice,
    recurringSavings: recurring,
    oneTimeSavings: roundCurrency_(oneTime),
    realizedSavings: roundCurrency_(recurring + oneTime),
    exceptionType: exceptionType
  };
}

function calculateExpected_(
  savingsType,
  baseline,
  negotiated,
  committed,
  claimedOneTime
) {
  const recurring =
    savingsType === 'One-time'
      ? 0
      : roundCurrency_(
          Math.max(0, baseline - negotiated) * committed
        );

  const oneTime =
    savingsType === 'Recurring unit-price'
      ? 0
      : roundCurrency_(claimedOneTime);

  return {
    recurring: recurring,
    total: roundCurrency_(recurring + oneTime)
  };
}

function validateSavingsInputs_(
  savingsType,
  baseline,
  negotiated,
  committed,
  claimedOneTime
) {
  if (PSV.SAVINGS_TYPES.indexOf(savingsType) === -1) {
    throw new Error('Invalid savings type.');
  }

  if (savingsType !== 'One-time') {
    if (baseline <= 0) {
      throw new Error(
        'Baseline Unit Price must be greater than zero.'
      );
    }
    if (committed <= 0) {
      throw new Error(
        'Committed Volume must be greater than zero.'
      );
    }
    if (negotiated >= baseline) {
      throw new Error(
        'Negotiated Unit Price must be below Baseline Unit Price.'
      );
    }
  }

  if (
    savingsType !== 'Recurring unit-price' &&
    claimedOneTime <= 0
  ) {
    throw new Error(
      'Claimed One-Time Savings must be greater than zero.'
    );
  }
}

function ensureCaseFolder_(record) {
  if (record['Document Link']) {
    const existingFolderId = extractFolderId_(record['Document Link']);
    if (existingFolderId) {
      return withRetry_('Open case folder', function() {
        return DriveApp.getFolderById(existingFolderId);
      }, 3);
    }
  }

  const root = DriveApp.getFolderById(
    requiredProperty_('ROOT_FOLDER_ID')
  );
  const folderName =
    record['Record ID'] + ' - ' + safeFileName_(record['Supplier']);

  const folder = withRetry_('Create case folder', function() {
    return root.createFolder(folderName);
  }, 3);

  folder.setDescription(
    'Controlled procurement savings evidence for ' +
    record['Record ID']
  );

  [
    '01 Baseline Evidence',
    '02 Negotiation Evidence',
    '03 Actual Spend Evidence',
    '04 Finance Approval'
  ].forEach(function(name) {
    folder.createFolder(name);
  });

  folder.createFile(
    'Evidence Instructions.txt',
    'Record ID: ' + record['Record ID'] + '\n' +
    'Store baseline, negotiation, actual-spend, and approval evidence ' +
    'in the corresponding subfolders. Do not enable public link sharing.',
    MimeType.PLAIN_TEXT
  );

  const editors = uniqueValues_(
    [
      record['Requester Email'],
      record['Owner Email']
    ].concat(financeApprovers_())
  );

  editors.forEach(function(email) {
    if (email) {
      folder.addEditor(email);
    }
  });

  updateInitiative_(record['Record ID'], {
    'Document Link': folder.getUrl()
  });

  return folder;
}

function copyEvidenceFiles_(
  linksText,
  caseFolder,
  subfolderName,
  recordId,
  correlationId
) {
  const links = splitLinks_(linksText);
  if (!links.length) {
    return [];
  }

  const target = getOrCreateChildFolder_(caseFolder, subfolderName);
  const result = [];

  links.forEach(function(link) {
    const fileId = extractFileId_(link);
    if (!fileId) {
      throw new Error(
        'An evidence URL does not identify a supported Drive file: ' + link
      );
    }

    const source = withRetry_('Open evidence file', function() {
      return DriveApp.getFileById(fileId);
    }, 3);

    const suffix = String(correlationId || '')
      .replace(/[^A-Za-z0-9]/g, '')
      .slice(-8) || Utilities.getUuid().slice(0, 8);

    const copyName =
      recordId + '_' + suffix + '_' + safeFileName_(source.getName());

    const existing = target.getFilesByName(copyName);
    if (existing.hasNext()) {
      result.push(existing.next().getUrl());
      return;
    }

    const copy = withRetry_('Copy evidence file', function() {
      return source.makeCopy(copyName, target);
    }, 3);

    result.push(copy.getUrl());
  });

  return result;
}

function createApprovalReceipt_(
  record,
  reviewId,
  stage,
  decision,
  approver,
  approvedAmount,
  exceptionType,
  notes
) {
  const folder = ensureCaseFolder_(record);
  const target = getOrCreateChildFolder_(
    folder,
    '04 Finance Approval'
  );

  const content =
    'Review ID: ' + reviewId + '\n' +
    'Record ID: ' + record['Record ID'] + '\n' +
    'Review Stage: ' + stage + '\n' +
    'Decision: ' + decision + '\n' +
    'Approver: ' + approver + '\n' +
    'Decision Date: ' + new Date().toISOString() + '\n' +
    'Calculated Realized Savings: ' +
      record['Calculated Realized Savings'] + '\n' +
    'Finance Approved Savings: ' +
      (
        stage === 'Final' && decision === 'Approved'
          ? approvedAmount
          : ''
      ) + '\n' +
    'Exception Type: ' + exceptionType + '\n' +
    'Notes: ' + notes + '\n';

  const file = target.createFile(
    reviewId + '.txt',
    content,
    MimeType.PLAIN_TEXT
  );
  return file.getUrl();
}

function sendBaselineRequest_(record) {
  const reviewUrl = prefilledFormUrl_(
    requiredProperty_(PSV.REVIEW_FORM_KEY),
    {
      'Record ID': record['Record ID'],
      'Review Stage': 'Baseline'
    }
  );

  const body =
    'A procurement savings baseline requires finance review.\n\n' +
    'Record ID: ' + record['Record ID'] + '\n' +
    'Category: ' + record['Category'] + '\n' +
    'Supplier: ' + record['Supplier'] + '\n' +
    'Savings type: ' + record['Savings Type'] + '\n' +
    'Expected total savings: ' + record['Expected Total Savings'] + '\n' +
    'Evidence folder: ' + record['Document Link'] + '\n' +
    'Review form: ' + reviewUrl + '\n';

  sendEmail_(
    financeApprovers_().join(','),
    'Baseline review required: ' + record['Record ID'],
    body
  );

  sendEmail_(
    record['Owner Email'],
    'Savings record created: ' + record['Record ID'],
    'Your savings record was created and sent for baseline review.\n\n' +
    'Evidence folder: ' + record['Document Link']
  );
}

function sendFinalReviewRequest_(record) {
  const reviewUrl = prefilledFormUrl_(
    requiredProperty_(PSV.REVIEW_FORM_KEY),
    {
      'Record ID': record['Record ID'],
      'Review Stage': 'Final'
    }
  );

  const body =
    'A procurement savings record requires final finance review.\n\n' +
    'Record ID: ' + record['Record ID'] + '\n' +
    'Actual volume: ' + record['Actual Volume'] + '\n' +
    'Actual spend: ' + record['Actual Spend'] + '\n' +
    'Actual effective unit price: ' +
      record['Actual Effective Unit Price'] + '\n' +
    'Calculated realized savings: ' +
      record['Calculated Realized Savings'] + '\n' +
    'Exception: ' + record['Exception Type'] + '\n' +
    'Evidence folder: ' + record['Document Link'] + '\n' +
    'Review form: ' + reviewUrl + '\n';

  sendEmail_(
    financeApprovers_().join(','),
    'Final savings review required: ' + record['Record ID'],
    body
  );
}

function sendFinanceOutcome_(record, stage, decision, notes) {
  const recipients = uniqueValues_([
    record['Requester Email'],
    record['Owner Email']
  ]).join(',');

  const body =
    'Finance recorded a decision for a procurement savings record.\n\n' +
    'Record ID: ' + record['Record ID'] + '\n' +
    'Review stage: ' + stage + '\n' +
    'Decision: ' + decision + '\n' +
    'Current status: ' + record['Status'] + '\n' +
    'Approval status: ' + record['Approval Status'] + '\n' +
    'Finance-approved savings: ' +
      (record['Finance Approved Savings'] || '') + '\n' +
    'Notes: ' + (notes || '') + '\n' +
    'Evidence folder: ' + record['Document Link'] + '\n';

  sendEmail_(
    recipients,
    'Finance decision: ' + record['Record ID'],
    body
  );
}

function prefilledFormUrl_(formId, values) {
  const form = FormApp.openById(formId);
  let draft = form.createResponse();

  form.getItems().forEach(function(item) {
    const title = item.getTitle();
    if (!Object.prototype.hasOwnProperty.call(values, title)) {
      return;
    }

    const value = String(values[title]);
    if (item.getType() === FormApp.ItemType.TEXT) {
      draft = draft.withItemResponse(
        item.asTextItem().createResponse(value)
      );
    } else if (item.getType() === FormApp.ItemType.LIST) {
      draft = draft.withItemResponse(
        item.asListItem().createResponse(value)
      );
    }
  });

  return draft.toPrefilledUrl();
}

function dailyMonitor() {
  withScriptLock_(function() {
    retryOpenFailures_();

    const sheet = getSpreadsheet_()
      .getSheetByName(PSV.SHEETS.INITIATIVES);
    const records = getTableObjects_(sheet);
    const today = startOfDay_(new Date());

    records.forEach(function(record) {
      const status = record['Status'];
      if (
        [
          'Awaiting Baseline Approval',
          'Awaiting Finance Review',
          'Returned for Information',
          'Measuring'
        ].indexOf(status) === -1
      ) {
        return;
      }

      let due = record['Next Action Due'];
      if (status === 'Measuring') {
        due = record['Measurement End'];
      }
      if (!(due instanceof Date) || isNaN(due.getTime())) {
        return;
      }

      const overdueDays = daysBetween_(
        startOfDay_(due),
        today
      );
      if (overdueDays < 1) {
        return;
      }

      const lastReminder = record['Last Reminder Date'];
      if (
        lastReminder instanceof Date &&
        daysBetween_(startOfDay_(lastReminder), today) < 2
      ) {
        return;
      }

      const financeOwned =
        status === 'Awaiting Baseline Approval' ||
        status === 'Awaiting Finance Review';

      const to = financeOwned
        ? financeApprovers_().join(',')
        : record['Owner Email'];

      const cc = overdueDays >= 5
        ? requiredProperty_('ESCALATION_EMAIL')
        : '';

      const subject =
        (overdueDays >= 5 ? 'Escalation: ' : 'Reminder: ') +
        record['Record ID'] + ' is overdue';

      const body =
        'Record ID: ' + record['Record ID'] + '\n' +
        'Status: ' + status + '\n' +
        'Due date: ' + due + '\n' +
        'Days overdue: ' + overdueDays + '\n' +
        'Evidence folder: ' + record['Document Link'] + '\n';

      const sent = safeSendEmail_(to, subject, body, cc);
      if (sent) {
        updateInitiative_(record['Record ID'], {
          'Last Reminder Date': new Date()
        });
        audit_(
          record['Record ID'],
          overdueDays >= 5 ? 'Escalation sent' : 'Reminder sent',
          'Automation',
          'Overdue by ' + overdueDays + ' day(s).',
          '',
          '',
          Utilities.getUuid()
        );
      }
    });
  });
}

function recoverCurrentRecord_(record) {
  ensureCaseFolder_(record);
  const status = record['Status'];

  if (
    status === 'Awaiting Baseline Approval' &&
    !record['Baseline Notice Sent']
  ) {
    sendBaselineRequest_(record);
    updateInitiative_(record['Record ID'], {
      'Baseline Notice Sent': new Date()
    });
  } else if (
    status === 'Awaiting Finance Review' &&
    !record['Final Notice Sent']
  ) {
    sendFinalReviewRequest_(record);
    updateInitiative_(record['Record ID'], {
      'Final Notice Sent': new Date()
    });
  } else if (
    [
      'Measuring',
      'Returned for Information',
      'Completed',
      'Rejected'
    ].indexOf(status) !== -1 &&
    !record['Completion Notice Sent']
  ) {
    sendFinanceOutcome_(
      record,
      record['Approval Status'].indexOf('Final') !== -1
        ? 'Final'
        : 'Baseline',
      status,
      record['Notes']
    );
    updateInitiative_(record['Record ID'], {
      'Completion Notice Sent': new Date()
    });
  }

  updateInitiative_(record['Record ID'], {
    'Automation Status': 'Complete',
    'Last Automation Run': new Date(),
    'Error Message': ''
  });
}

function retryOpenFailures_() {
  const sheet = getSpreadsheet_().getSheetByName(PSV.SHEETS.FAILURES);
  const failures = getTableObjects_(sheet);

  failures.forEach(function(failure) {
    if (
      failure['Status'] === 'Open' &&
      Number(failure['Retry Count']) < 3
    ) {
      retryFailure(failure['Failure ID']);
    }
  });
}

function retryFailure(failureId) {
  const failureReference = findRowByValue_(
    PSV.SHEETS.FAILURES,
    'Failure ID',
    failureId
  );
  if (!failureReference) {
    throw new Error('Failure ID not found: ' + failureId);
  }

  const failure = failureReference.data;
  const currentRetries = Number(failure['Retry Count']) || 0;

  if (failure['Status'] === 'Resolved') {
    return;
  }
  if (currentRetries >= 3) {
    updateObjectAtRow_(
      PSV.SHEETS.FAILURES,
      failureReference.row,
      {
        'Status': 'Manual Review',
        'Last Retry': new Date()
      }
    );
    return;
  }

  try {
    const form = FormApp.openById(failure['Source Form ID']);
    const responses = form.getResponses();
    let response = null;

    responses.some(function(candidate) {
      if (safeResponseId_(candidate) === failure['Response ID']) {
        response = candidate;
        return true;
      }
      return false;
    });

    if (!response) {
      throw new Error('Source form response could not be found.');
    }

    if (failure['Handler'] === 'request') {
      processRequestResponse_(response);
    } else if (failure['Handler'] === 'update') {
      processUpdateResponse_(response);
    } else if (failure['Handler'] === 'review') {
      processReviewResponse_(response);
    } else {
      throw new Error('Unknown failure handler.');
    }

    updateObjectAtRow_(
      PSV.SHEETS.FAILURES,
      failureReference.row,
      {
        'Retry Count': currentRetries + 1,
        'Status': 'Resolved',
        'Last Retry': new Date()
      }
    );
  } catch (error) {
    const nextRetries = currentRetries + 1;
    updateObjectAtRow_(
      PSV.SHEETS.FAILURES,
      failureReference.row,
      {
        'Error': sanitizeError_(error),
        'Retry Count': nextRetries,
        'Status': nextRetries >= 3 ? 'Manual Review' : 'Open',
        'Last Retry': new Date()
      }
    );
  }
}

function logFailure_(
  handler,
  sourceFormId,
  responseId,
  recordId,
  error
) {
  appendObject_(PSV.SHEETS.FAILURES, {
    'Failure ID': 'FAIL-' + Utilities.getUuid(),
    'Timestamp': new Date(),
    'Handler': handler,
    'Source Form ID': sourceFormId,
    'Response ID': responseId,
    'Record ID': recordId,
    'Error': sanitizeError_(error),
    'Retry Count': 0,
    'Status': 'Open',
    'Owner': requiredProperty_('AUTOMATION_OWNER_EMAIL')
  });
}

function markInitiativeFailure_(recordId, error) {
  try {
    const record = requireRecord_(recordId).data;
    updateInitiative_(recordId, {
      'Automation Status': 'Failed',
      'Status':
        record['Status'] === 'Processing'
          ? 'Manual Review'
          : record['Status'],
      'Last Automation Run': new Date(),
      'Retry Count': (Number(record['Retry Count']) || 0) + 1,
      'Error Message': sanitizeError_(error)
    });
  } catch (ignored) {
    console.error('Could not mark initiative failure: ' + ignored);
  }
}

function audit_(
  recordId,
  event,
  actor,
  details,
  oldValue,
  newValue,
  correlationId
) {
  appendObject_(PSV.SHEETS.AUDIT, {
    'Timestamp': new Date(),
    'Record ID': recordId,
    'Event': event,
    'Actor': actor,
    'Details': details,
    'Old Value': oldValue,
    'New Value': newValue,
    'Correlation ID': correlationId
  });
}

function authorizeOwnerUpdate_(record, submitter) {
  const allowed = uniqueValues_([
    String(record['Requester Email']).toLowerCase(),
    String(record['Owner Email']).toLowerCase(),
    requiredProperty_('AUTOMATION_OWNER_EMAIL').toLowerCase()
  ]);

  if (allowed.indexOf(submitter.toLowerCase()) === -1) {
    throw new Error('The respondent is not authorized to update this record.');
  }
}

function authorizeFinance_(email) {
  if (financeApprovers_().indexOf(email.toLowerCase()) === -1) {
    throw new Error('The respondent is not an authorized finance approver.');
  }
}

function financeApprovers_() {
  return requiredProperty_('FINANCE_APPROVERS')
    .split(',')
    .map(function(email) {
      return email.trim().toLowerCase();
    })
    .filter(String);
}

function nextRecordId_() {
  const year = new Date().getFullYear();
  const key = 'PSV_SEQUENCE_' + year;
  const props = PropertiesService.getScriptProperties();
  let sequence = Number(props.getProperty(key)) || 0;
  let candidate;

  do {
    sequence += 1;
    candidate =
      'PSV-' + year + '-' + String(sequence).padStart(4, '0');
  } while (
    findRowByValue_(
      PSV.SHEETS.INITIATIVES,
      'Record ID',
      candidate
    )
  );

  props.setProperty(key, String(sequence));
  return candidate;
}

function nextRelatedId_(sheetName, recordHeader, recordId, marker) {
  const sheet = getSpreadsheet_().getSheetByName(sheetName);
  const rows = getTableObjects_(sheet).filter(function(row) {
    return row[recordHeader] === recordId;
  });

  return recordId + '-' + marker +
    String(rows.length + 1).padStart(3, '0');
}

function appendObject_(sheetName, object) {
  const sheet = getSpreadsheet_().getSheetByName(sheetName);
  if (!sheet) {
    throw new Error('Sheet not found: ' + sheetName);
  }

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

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

function updateInitiative_(recordId, patch) {
  const reference = requireRecord_(recordId);
  const completePatch = Object.assign({}, patch, {
    'Last Updated': new Date()
  });

  updateObjectAtRow_(
    PSV.SHEETS.INITIATIVES,
    reference.row,
    completePatch
  );
}

function updateObjectAtRow_(sheetName, rowNumber, patch) {
  const sheet = getSpreadsheet_().getSheetByName(sheetName);
  const headers = getHeaders_(sheet);

  Object.keys(patch).forEach(function(header) {
    const column = headers.indexOf(header) + 1;
    if (column === 0) {
      throw new Error(
        'Unknown column "' + header + '" in ' + sheetName
      );
    }
    sheet.getRange(rowNumber, column).setValue(patch[header]);
  });
}

function requireRecord_(recordId) {
  const reference = findRowByValue_(
    PSV.SHEETS.INITIATIVES,
    'Record ID',
    recordId
  );
  if (!reference) {
    throw new Error('Record ID not found: ' + recordId);
  }
  return reference;
}

function findRowByValue_(sheetName, header, value) {
  const sheet = getSpreadsheet_().getSheetByName(sheetName);
  const headers = getHeaders_(sheet);
  const column = headers.indexOf(header) + 1;

  if (column === 0) {
    throw new Error('Header not found: ' + header);
  }
  if (sheet.getLastRow() < 2) {
    return null;
  }

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

  for (let index = 0; index < values.length; index += 1) {
    if (String(values[index][0]) === String(value)) {
      const row = index + 2;
      const rowValues = sheet.getRange(
        row,
        1,
        1,
        headers.length
      ).getValues()[0];

      const object = {};
      headers.forEach(function(item, itemIndex) {
        object[item] = rowValues[itemIndex];
      });

      return {
        row: row,
        data: object
      };
    }
  }

  return null;
}

function getTableObjects_(sheet) {
  if (sheet.getLastRow() < 2) {
    return [];
  }

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

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

function getHeaders_(sheet) {
  if (sheet.getLastColumn() === 0) {
    return [];
  }
  return sheet.getRange(
    1,
    1,
    1,
    sheet.getLastColumn()
  ).getValues()[0];
}

function responseMap_(response) {
  const map = {};
  response.getItemResponses().forEach(function(itemResponse) {
    const title = itemResponse.getItem().getTitle();
    const value = itemResponse.getResponse();
    map[title] = Array.isArray(value) ? value.join('\n') : value;
  });
  return map;
}

function respondentEmail_(response) {
  const email = String(response.getRespondentEmail() || '')
    .trim()
    .toLowerCase();

  validateEmail_(email, 'Respondent');
  return email;
}

function safeResponseId_(response) {
  return String(response.getId() || '').trim();
}

function requiredText_(values, field) {
  const value = cleanText_(values[field]);
  if (!value) {
    throw new Error(field + ' is required.');
  }
  return value;
}

function requiredNumber_(values, field) {
  const raw = values[field];
  if (raw === '' || raw === null || typeof raw === 'undefined') {
    throw new Error(field + ' is required.');
  }
  return parseNonNegativeNumber_(raw, field);
}

function optionalNumber_(raw, fallback) {
  if (raw === '' || raw === null || typeof raw === 'undefined') {
    return Number(fallback) || 0;
  }
  return parseNonNegativeNumber_(raw, 'Numeric field');
}

function parseNonNegativeNumber_(raw, field) {
  const number = Number(String(raw).replace(/,/g, '').trim());
  if (!isFinite(number) || number < 0) {
    throw new Error(field + ' must be a nonnegative number.');
  }
  return number;
}

function requiredDate_(values, field) {
  const raw = values[field];
  const date = raw instanceof Date ? raw : new Date(raw);
  if (!(date instanceof Date) || isNaN(date.getTime())) {
    throw new Error(field + ' must be a valid date.');
  }
  return date;
}

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

function validateEmail_(email, label) {
  const pattern = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
  if (!pattern.test(String(email || '').trim())) {
    throw new Error(label + ' email is invalid.');
  }
}

function validateEvidenceLinks_(text) {
  splitLinks_(text).forEach(function(link) {
    if (
      link.indexOf('https://drive.google.com/') !== 0 &&
      link.indexOf('https://docs.google.com/') !== 0
    ) {
      throw new Error(
        'Evidence links must be Google Drive or Google document URLs.'
      );
    }
  });
}

function splitLinks_(text) {
  return String(text || '')
    .split(/\r?\n/)
    .map(function(value) {
      return value.trim();
    })
    .filter(String);
}

function extractFileId_(url) {
  const value = String(url || '');
  const pathMatch = value.match(/\/d\/([A-Za-z0-9_-]+)/);
  if (pathMatch) {
    return pathMatch[1];
  }

  const queryMatch = value.match(/[?&]id=([A-Za-z0-9_-]+)/);
  return queryMatch ? queryMatch[1] : '';
}

function extractFolderId_(url) {
  const match = String(url || '').match(
    /\/folders\/([A-Za-z0-9_-]+)/
  );
  return match ? match[1] : '';
}

function getOrCreateChildFolder_(parent, name) {
  const folders = parent.getFoldersByName(name);
  return folders.hasNext() ? folders.next() : parent.createFolder(name);
}

function safeFileName_(name) {
  const cleaned = String(name || 'Unnamed')
    .replace(/[\\/:*?"<>|#%{}~]/g, '-')
    .replace(/\s+/g, ' ')
    .trim();

  return cleaned.slice(0, 120) || 'Unnamed';
}

function mergeLinks_(existing, additional) {
  return uniqueValues_(
    splitLinks_(existing).concat(splitLinks_(additional))
  ).join('\n');
}

function mergeNotes_(existing, additional) {
  const cleanAdditional = cleanText_(additional);
  if (!cleanAdditional) {
    return cleanText_(existing);
  }

  const stamped =
    Utilities.formatDate(
      new Date(),
      Session.getScriptTimeZone(),
      'yyyy-MM-dd HH:mm'
    ) + ': ' + cleanAdditional;

  return [cleanText_(existing), stamped]
    .filter(String)
    .join('\n');
}

function uniqueValues_(values) {
  const seen = {};
  return values.filter(function(value) {
    const normalized = String(value || '').trim();
    if (!normalized || seen[normalized]) {
      return false;
    }
    seen[normalized] = true;
    return true;
  });
}

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

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

function addBusinessDays_(date, count) {
  const result = new Date(date);
  let added = 0;

  while (added < count) {
    result.setDate(result.getDate() + 1);
    const day = result.getDay();
    if (day !== 0 && day !== 6) {
      added += 1;
    }
  }

  return result;
}

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

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

function withScriptLock_(callback) {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(30000)) {
    throw new Error('The automation is busy. Retry the event.');
  }

  try {
    return callback();
  } finally {
    lock.releaseLock();
  }
}

function withRetry_(label, callback, attempts) {
  let lastError;

  for (let attempt = 1; attempt <= attempts; attempt += 1) {
    try {
      return callback();
    } catch (error) {
      lastError = error;
      if (attempt < attempts) {
        Utilities.sleep(500 * Math.pow(2, attempt - 1));
      }
    }
  }

  throw new Error(label + ' failed: ' + sanitizeError_(lastError));
}

function sendEmail_(to, subject, body, cc) {
  if (!to) {
    throw new Error('Email recipient is required.');
  }

  withRetry_('Send email', function() {
    const message = {
      to: to,
      subject: subject,
      body: body,
      name: 'Procurement Savings Validation'
    };
    if (cc) {
      message.cc = cc;
    }
    MailApp.sendEmail(message);
  }, 3);
}

function safeSendEmail_(to, subject, body, cc) {
  try {
    sendEmail_(to, subject, body, cc);
    return true;
  } catch (error) {
    console.error('Email failed: ' + sanitizeError_(error));
    return false;
  }
}

function sanitizeError_(error) {
  return String(
    error && error.message ? error.message : error
  ).slice(0, 1000);
}

function requiredProperty_(key) {
  const value = PropertiesService
    .getScriptProperties()
    .getProperty(key);

  if (!value) {
    throw new Error('Missing Script Property: ' + key);
  }
  return value;
}

function getSpreadsheet_() {
  const id = requiredProperty_('SPREADSHEET_ID');
  return SpreadsheetApp.openById(id);
}

Authorization and deployment

1. Replace YOUR_FOLDER_ID with the ID from the restricted Drive root folder URL.
2. Replace the email placeholders. Multiple finance approvers can be entered as a comma-separated list.
3. Run configureSystem. This stores configuration in Script Properties.
4. Run setupSystem. Google displays an authorization request for Forms, Sheets, Drive, email, triggers, and script properties.
5. Review the requested permissions and authorize only from the designated owner account.
6. Open the Settings sheet and copy the generated request and update form URLs to the appropriate internal process pages.
7. Restrict the finance review form to authorized finance users. The script also enforces the approver list.
8. Inspect the Apps Script trigger list. There should be one trigger for each form handler and one daily monitor trigger.

Testing and logs

Submit a request with test values and inspect Initiatives, Audit Log, the generated Drive folder, and Apps Script execution history. Google Apps Script execution details show the function, duration, status, and logged errors. The Failures sheet provides a business-readable queue.

To retry a specific failed event manually, copy its Failure ID and run retryFailure('FAIL-ID-HERE') from a temporary wrapper function or the Apps Script debugger. Automatic retries run daily for open failures with fewer than three attempts.

Likely setup errors include an incorrect root folder ID, insufficient Drive access, unauthorized form respondents, a changed sheet header, or forms deleted after their IDs were stored. Correct the configuration or restore the expected structure before retrying.

Failure Handling and Operational Reliability

The workflow is idempotent at the event level. Every request, update, and review retains its external form response ID. Folder evidence copies use a response-derived filename suffix, preventing replayed events from creating additional copies with the same controlled name.

Google service operations can still fail after an external side effect occurs. For example, an email may be accepted by the mail service immediately before a later sheet update fails. Notification timestamps and audit records reduce repeated messages, but exactly-once delivery cannot be guaranteed across separate services.

The Failures sheet functions as a lightweight dead-letter queue. It does not contain full form payloads, reducing unnecessary duplication of sensitive data. Instead, it stores the source form and response identifiers so the authorized script can retrieve and replay the original response.

Staff reconcile the system monthly by comparing raw form responses with Initiatives, Periods, and Reviews. Any raw response without a corresponding external ID in a controlled table is investigated.

A Complete Example

A Harborstone category manager negotiates a reduction for a recurring component. The original baseline is a weighted average of invoices from January through March.

Google Forms assigns a response identifier. Apps Script confirms that the identifier is new and generates PSV-2026-0042.

The expected recurring savings calculation is:

($12.50 - $11.80) × 10,000
= $0.70 × 10,000
= $7,000 expected recurring savings

The script creates the initiative, creates PSV-2026-0042 - Supplier Name in Drive, copies the baseline invoice export, and sends finance a baseline-review link.

Finance inspects the invoices and confirms that $12.50 is a valid comparable baseline. The finance approver submits an Approved baseline decision. The initiative changes from Awaiting Baseline Approval to Measuring.

At the end of June, procurement submits final actuals:

• Actual volume: 9,200 units
• Actual spend: $109,020
• Final measurement: Yes
• Evidence: purchase export and invoice files

The actual effective unit price is:

$109,020 ÷ 9,200 units
= $11.85 per unit

Eligible volume is 9,200 because actual volume is below the 10,000-unit commitment. The calculated realized savings are:

($12.50 - $11.85) × 9,200
= $0.65 × 9,200
= $5,980 calculated realized savings

The system marks a volume variance because actual volume is below commitment and a price variance because the effective price is $0.05 above the negotiated price. The current implementation stores one primary exception, so the finance reviewer records additional detail in Notes.

Finance confirms that $300 of spend included expedited freight that should not be treated as part of the unit-price comparison. Rather than overriding the formula silently, finance returns the record for information. Procurement submits corrected evidence showing comparable product spend of $108,720.

The recalculated effective unit price is approximately $11.82, producing calculated savings of $6,256. Finance approves $6,256. The script creates review ID PSV-2026-0042-R003, stores an approval receipt, updates Finance Approved Savings, changes the status to Completed, and notifies procurement.

Executive reporting includes $6,256. It does not include the original $7,000 expected amount as realized savings.

Implementation Cost

All amounts below are representative planning assumptions, not verified client costs or current product pricing. The scenario assumes that suitable Google Workspace subscriptions and storage capacity already exist. Organizations must verify licensing, storage, email, and Apps Script requirements for their environment.

Even when incremental software cost is zero under an existing agreement, configuration, testing, security review, documentation, support, and maintenance still require labour.

Estimated Time and Cost Savings

The representative calculation uses these assumptions:

• Monthly workflow volume: 24 initiatives
• Current combined procurement and finance handling time: 55 minutes per initiative
• New routine handling time: 18 minutes per initiative
• Exception rate: 20 percent
• Additional exception handling: 25 minutes per exception
• Monthly maintenance: 3 hours
• Loaded labour cost: $60 per hour
• Recurring incremental software cost: $0 under the assumed existing subscriptions
• One-time internal implementation cost: $3,660

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

24 × 55 ÷ 60 = 22.0 hours

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

Routine handling:
24 × 18 ÷ 60 = 7.2 hours

Exception handling:
24 × 20% × 25 ÷ 60 = 2.0 hours

Maintenance:
3.0 hours

Total new monthly labour:
7.2 + 2.0 + 3.0 = 12.2 hours

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

22.0 - 12.2 = 9.8 hours

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

9.8 × $60 = $588 per month

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

$588 - $0 incremental software
= $588 per month

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

$3,660 ÷ $588
= approximately 6.2 months

The calculation already includes three monthly maintenance hours in the new labour total. If the organization must purchase additional software, storage, or support, those recurring costs should be deducted from the monthly value.

Recovered time does not automatically reduce payroll. It may instead create capacity for sourcing work, improve month-end turnaround, reduce overtime, support higher transaction volume, and reduce dependence on a single analyst.

Non-financial benefits include:

• Clear ownership and due dates
• Fewer finance follow-up emails
• Consistent savings definitions
• Fewer incomplete records
• Evidence linked to each initiative
• Separate expected, calculated, and approved values
• Better approval auditability
• More consistent procurement performance reporting
• Faster investigation of differences
• Less dependence on manually consolidated workbooks

Readers should replace volume, handling time, exception rate, maintenance effort, labour rate, implementation cost, software cost, and storage requirements with their own measured figures.

Adding AI to the Automation

AI is optional and should be added only after the deterministic workflow, calculation rules, permissions, and failure handling operate reliably.

Potential AI applications include summarizing approved initiatives, categorizing free-text descriptions, identifying themes in finance return notes, comparing narrative evidence, and suggesting missing-information questions.

AI should not calculate unit-price savings when formulas can do so exactly. It should not decide whether a baseline is acceptable, approve realized savings, reject a procurement claim, make accounting entries, or determine financial reporting treatment.

The core automation provides intake control, identifiers, calculations, folder creation, routing, reminders, audit history, and approved reporting. AI is not responsible for those benefits.

The Recommended AI Enhancement

The recommended enhancement is a monthly executive-summary draft based only on finance-approved initiatives. The AI receives Record ID, category, savings type, expected amount, approved amount, and controlled exception type. It does not receive supplier names, personal emails, invoices, contracts, Drive links, or unrestricted notes.

• Trigger: Analytics manager manually runs the summary function after month-end reconciliation.
• AI input: Data-minimized final-approved records for one month.
• System instruction: Use only supplied figures, distinguish expected from approved savings, and avoid unsupported causal claims.
• Expected output: Structured JSON containing a headline, summary, key points, exceptions, actions, and confidence note.
• Validation: JSON schema, required fields, allowed Record IDs, and reconciliation of total approved savings.
• Record update: A Pending Review row is added to Executive Summaries.
• Human review: Finance or analytics checks every figure before distribution.
• Low-confidence handling: The output remains pending and the reviewer prepares the summary manually.
• Prohibited data: Supplier identities, personal data, contracts, invoices, credentials, bank details, and unrestricted notes.
• Failure behavior: No summary is distributed. The normal spreadsheet report remains available.

Reusable AI instruction and prompt

System instruction:

You draft procurement savings executive summaries from structured,
finance-approved records. Use only the supplied data. Do not infer
supplier performance, cash impact, accounting treatment, or causation.
Clearly distinguish expected savings from finance-approved savings.
Do not describe calculated or expected amounts as realized unless they
are included in finance_approved_savings. Mention record IDs when
describing exceptions. Return only JSON that matches the supplied
schema.

User prompt:

Prepare the executive summary for the reporting period in the JSON
payload below.

Requirements:
1. Reconcile the summary to total_finance_approved_savings.
2. State the number of approved initiatives.
3. Identify material differences between expected and approved amounts.
4. Mention only exception types and Record IDs contained in the input.
5. Provide no more than five key points and five recommended actions.
6. Do not add external benchmarks, savings forecasts, or supplier claims.
7. If the input is insufficient, explain the limitation in confidence_note.

Input:
{{APPROVED_SAVINGS_JSON}}

The following optional Apps Script calls an approved OpenAI Responses API model. Store the API key and model identifier in Script Properties as OPENAI_API_KEY and OPENAI_MODEL. The model identifier must be selected and approved by the organization rather than hard-coded here.

function generateMonthlyExecutiveSummary(year, month) {
  if (
    !Number.isInteger(year) ||
    !Number.isInteger(month) ||
    month < 1 ||
    month > 12
  ) {
    throw new Error('Provide a valid year and month from 1 to 12.');
  }

  const props = PropertiesService.getScriptProperties();
  const spreadsheetId = props.getProperty('SPREADSHEET_ID');
  const apiKey = props.getProperty('OPENAI_API_KEY');
  const model = props.getProperty('OPENAI_MODEL');

  if (!spreadsheetId) {
    throw new Error('Missing SPREADSHEET_ID Script Property.');
  }
  if (!apiKey || apiKey.indexOf('YOUR_') === 0) {
    throw new Error('Set OPENAI_API_KEY in Script Properties.');
  }
  if (!model || model.indexOf('YOUR_') === 0) {
    throw new Error('Set OPENAI_MODEL to an approved model identifier.');
  }

  const ss = SpreadsheetApp.openById(spreadsheetId);
  const initiativeSheet = ss.getSheetByName('Initiatives');
  if (!initiativeSheet) {
    throw new Error('Initiatives sheet was not found.');
  }

  const records = executiveReadTable_(initiativeSheet);
  const start = new Date(year, month - 1, 1);
  const end = new Date(year, month, 1);

  const approved = records.filter(function(record) {
    const decisionDate = record['Finance Decision Date'];
    return (
      record['Approval Status'] === 'Final Approved' &&
      decisionDate instanceof Date &&
      decisionDate.getTime() >= start.getTime() &&
      decisionDate.getTime() < end.getTime()
    );
  });

  if (!approved.length) {
    throw new Error('No final-approved records exist for the period.');
  }

  const minimizedRecords = approved.map(function(record) {
    return {
      record_id: String(record['Record ID']),
      category: String(record['Category']),
      savings_type: String(record['Savings Type']),
      expected_total_savings:
        executiveRound_(record['Expected Total Savings']),
      finance_approved_savings:
        executiveRound_(record['Finance Approved Savings']),
      exception_type: String(record['Exception Type'] || 'None')
    };
  });

  const totalApproved = executiveRound_(
    minimizedRecords.reduce(function(total, record) {
      return total + record.finance_approved_savings;
    }, 0)
  );

  const totalExpected = executiveRound_(
    minimizedRecords.reduce(function(total, record) {
      return total + record.expected_total_savings;
    }, 0)
  );

  const period = year + '-' + String(month).padStart(2, '0');
  const inputPayload = {
    reporting_period: period,
    approved_initiative_count: minimizedRecords.length,
    total_expected_savings: totalExpected,
    total_finance_approved_savings: totalApproved,
    records: minimizedRecords
  };

  const instructions =
    'You draft procurement savings executive summaries from structured, ' +
    'finance-approved records. Use only the supplied data. Do not infer ' +
    'supplier performance, cash impact, accounting treatment, or causation. ' +
    'Clearly distinguish expected savings from finance-approved savings. ' +
    'Do not describe calculated or expected amounts as realized unless they ' +
    'are included in finance_approved_savings. Mention record IDs when ' +
    'describing exceptions. Return only JSON that matches the supplied schema.';

  const userPrompt =
    'Prepare the executive summary for the reporting period in the JSON ' +
    'payload below.\n\n' +
    'Requirements:\n' +
    '1. Reconcile the summary to total_finance_approved_savings.\n' +
    '2. State the number of approved initiatives.\n' +
    '3. Identify material differences between expected and approved amounts.\n' +
    '4. Mention only exception types and Record IDs contained in the input.\n' +
    '5. Provide no more than five key points and five recommended actions.\n' +
    '6. Do not add external benchmarks, savings forecasts, or supplier claims.\n' +
    '7. If the input is insufficient, explain the limitation in confidence_note.\n\n' +
    'Input:\n' + JSON.stringify(inputPayload);

  const schema = {
    type: 'object',
    additionalProperties: false,
    properties: {
      headline: {type: 'string'},
      executive_summary: {type: 'string'},
      key_points: {
        type: 'array',
        maxItems: 5,
        items: {type: 'string'}
      },
      exceptions: {
        type: 'array',
        maxItems: 5,
        items: {
          type: 'object',
          additionalProperties: false,
          properties: {
            record_id: {type: 'string'},
            reason: {type: 'string'}
          },
          required: ['record_id', 'reason']
        }
      },
      recommended_actions: {
        type: 'array',
        maxItems: 5,
        items: {type: 'string'}
      },
      confidence_note: {type: 'string'}
    },
    required: [
      'headline',
      'executive_summary',
      'key_points',
      'exceptions',
      'recommended_actions',
      'confidence_note'
    ]
  };

  const requestBody = {
    model: model,
    instructions: instructions,
    input: [
      {
        role: 'user',
        content: [
          {
            type: 'input_text',
            text: userPrompt
          }
        ]
      }
    ],
    text: {
      format: {
        type: 'json_schema',
        name: 'procurement_savings_summary',
        strict: true,
        schema: schema
      }
    }
  };

  const responseObject = executiveCallApi_(
    apiKey,
    requestBody,
    3
  );
  const outputText = executiveExtractOutputText_(responseObject);

  let summary;
  try {
    summary = JSON.parse(outputText);
  } catch (error) {
    throw new Error('AI output was not valid JSON: ' + error.message);
  }

  executiveValidateSummary_(
    summary,
    minimizedRecords.map(function(record) {
      return record.record_id;
    })
  );

  let summarySheet = ss.getSheetByName('Executive Summaries');
  if (!summarySheet) {
    summarySheet = ss.insertSheet('Executive Summaries');
    summarySheet.appendRow([
      'Summary ID', 'Period', 'Created Date', 'Model',
      'Input Record Count', 'Approved Savings', 'Headline', 'Summary',
      'Key Points JSON', 'Exceptions JSON', 'Actions JSON',
      'Confidence Note', 'Review Status', 'Reviewer', 'Review Date',
      'Raw JSON'
    ]);
  }

  const summaryId =
    'EXE-' + period + '-' + Utilities.getUuid().slice(0, 8);

  summarySheet.appendRow([
    summaryId,
    period,
    new Date(),
    model,
    minimizedRecords.length,
    totalApproved,
    summary.headline,
    summary.executive_summary,
    JSON.stringify(summary.key_points),
    JSON.stringify(summary.exceptions),
    JSON.stringify(summary.recommended_actions),
    summary.confidence_note,
    'Pending Review',
    '',
    '',
    JSON.stringify(summary)
  ]);

  console.log('Executive summary created: ' + summaryId);
  return {
    summary_id: summaryId,
    period: period,
    approved_savings: totalApproved,
    review_status: 'Pending Review'
  };
}

function executiveCallApi_(apiKey, requestBody, attempts) {
  const endpoint = 'https://api.openai.com/v1/responses';
  let lastError;

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

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

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

      if ((status === 429 || status >= 500) && attempt < attempts) {
        Utilities.sleep(1000 * Math.pow(2, attempt - 1));
        continue;
      }

      throw new Error(
        'AI API returned HTTP ' + status + ': ' + body.slice(0, 500)
      );
    } catch (error) {
      lastError = error;
      if (attempt < attempts) {
        Utilities.sleep(1000 * Math.pow(2, attempt - 1));
      }
    }
  }

  throw new Error('AI API request failed: ' + lastError.message);
}

function executiveExtractOutputText_(responseObject) {
  if (
    responseObject.output_text &&
    typeof responseObject.output_text === 'string'
  ) {
    return responseObject.output_text;
  }

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

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

function executiveValidateSummary_(summary, allowedRecordIds) {
  const requiredStrings = [
    'headline',
    'executive_summary',
    'confidence_note'
  ];

  requiredStrings.forEach(function(field) {
    if (!summary[field] || typeof summary[field] !== 'string') {
      throw new Error('AI output is missing ' + field + '.');
    }
  });

  [
    'key_points',
    'exceptions',
    'recommended_actions'
  ].forEach(function(field) {
    if (!Array.isArray(summary[field])) {
      throw new Error('AI output field ' + field + ' must be an array.');
    }
  });

  summary.exceptions.forEach(function(exception) {
    if (allowedRecordIds.indexOf(exception.record_id) === -1) {
      throw new Error(
        'AI output referenced an unknown Record ID: ' +
        exception.record_id
      );
    }
  });
}

function executiveReadTable_(sheet) {
  if (sheet.getLastRow() < 2) {
    return [];
  }

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

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

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

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

Run the AI function manually with a call such as generateMonthlyExecutiveSummary(2026, 6). Review the new row, compare every figure with the Dashboard and Initiatives sheets, and change Review Status only after a human reviewer accepts the draft.

Benefits of the AI Enhancement

The AI enhancement addresses unstructured executive writing rather than the calculation itself. Potential benefits include:

• Less time converting approved rows into a narrative
• More consistent distinction between expected and approved savings
• Faster identification of common exception themes
• A repeatable summary structure across months
• Record IDs attached to exception descriptions
• A controlled first draft for finance and analytics review

These are separate from the core automation benefits. Forms, calculations, approvals, evidence, reminders, and reporting work without AI.

What Remains Rule-Based or Human-Controlled

Estimating the Additional Value of AI

The representative scenario assumes one monthly executive summary covering approximately 24 initiatives.

Additional time recovered:
90 - 32.5 = 57.5 minutes per month

Equivalent time per included record:
57.5 ÷ 24 = approximately 2.4 minutes

Labour value:
57.5 ÷ 60 × $60 = $57.50 per month

Less assumed AI usage budget:
$57.50 - $5.00 = $52.50 net additional monthly value

The calculation assumes continued human review and an occasional manual fallback. AI does not eliminate errors, corrections, or review responsibility.

Testing Checklist

Use fictional sample data and non-sensitive test documents before processing real procurement or supplier information.

Ongoing Maintenance

The primary system owner should have a named backup. Both need access to the Apps Script project, workbook, root folder, forms, documentation, and recovery instructions.

Configuration changes should be tested in development. Changing a form question title without updating the script will break field mapping. Changing workbook headers can also cause controlled failures, which is preferable to writing values into the wrong columns.

If the optional AI summary is enabled, finance or analytics should sample every output initially. The organization can reduce sampling only after establishing an approved risk policy, and the final release should remain human-controlled.

When to Move to Dedicated Software

The Google Workspace implementation can remain appropriate while volume is moderate, permissions are manageable, and the workflow is primarily internal. Replacement is not automatic merely because the process is important.

Consider a no-code database, procurement platform, spend-analytics product, or custom application when several of the following conditions appear:

• Monthly volume increases enough to create Apps Script runtime or quota pressure.
• Hundreds of concurrent active initiatives make spreadsheet views difficult to manage.
• Multiple business units require row-level permissions that cannot be maintained safely in Sheets.
• Formal regulatory or external-audit controls require stronger immutable records.
• Actual spend must be imported automatically from an accounting, enterprise resource planning, or purchasing system.
• Supplier, contract, purchase-order, invoice, and payment data require a richer relational model.
• Multiple currencies require governed exchange-rate treatment.
• Increasing exception rates make the form workflow difficult to operate.
• Manual administration exceeds the value of the lightweight platform.
• Users require mobile workflows, offline operation, or a supplier-facing portal.
• Service-level commitments require formal vendor support.
• Advanced forecasting, attribution, and scenario modelling become necessary.
• Security risk increases because too many users need direct workbook or folder access.

Migration is easier because the implementation already defines identifiers, fields, statuses, approval rules, calculations, evidence requirements, and audit expectations. Those definitions can become requirements for a dedicated platform rather than being rediscovered during replacement.

Implementation Checklist

• Confirm the business definition of expected, calculated, and finance-approved savings.
• Define recurring, one-time, and mixed calculation rules.
• Confirm committed-volume caps and measurement periods.
• Select the production Google Workspace owner and backup owner.
• Create the restricted spreadsheet and Drive root folder.
• Configure procurement, finance, analytics, and administrator permissions.
• Replace all Apps Script configuration placeholders.
• Create and verify the Initiatives, Periods, Reviews, Audit Log, Failures, Settings, Dashboard, and Executive Summaries sheets.
• Create the request, owner update, and finance review forms.
• Verify field titles and mappings before changing any form question.
• Install the three submission triggers and daily monitor trigger.
• Test unique Record ID generation under simultaneous submissions.
• Test expected and realized savings formulas with known examples.
• Confirm duplicate-event protection.
• Confirm folder naming, subfolders, permissions, and evidence copying.
• Confirm baseline and final approval routing.
• Test returns, rejections, corrections, and reassignment.
• Confirm reminder and escalation timing.
• Confirm notification timestamps and retry behavior.
• Protect controlled sheets, columns, raw responses, and settings.
• Review credential storage and former-user removal procedures.
• Run normal, exception, security, file, email, and recovery tests.
• Complete user acceptance testing with procurement and finance.
• Document deployment, rollback, support, and manual-recovery procedures.
• Replace representative cost and savings assumptions with measured figures.
• Enable the optional AI summary only after the core workflow is stable.
• Restrict AI inputs and require human review before distribution.
• Assign daily, monthly, quarterly, and annual maintenance owners.
• Document the transaction, permission, security, integration, and reporting thresholds that would justify dedicated software.