Building a 13-Week Cash Flow Forecast Across Four Tools

The Business Situation

Alder Creek Equipment Services is a fictional 48-person commercial equipment maintenance and rental business. Its finance function consists of a controller and an accounting specialist. The owner reviews major purchases, financing requirements, and short-term liquidity decisions. Operations managers provide expected purchase and project-spending information.

The company uses QuickBooks Online for accounting, Google Workspace for working documents, and manually prepared spreadsheets for management reporting. The accounting system contains open customer invoices, supplier bills, and general ledger bank balances, but it does not contain every forward-looking cash commitment.

The controller prepares a weekly 13-week cash flow forecast using the following information:

• Accounting balances for operating and payroll bank accounts
• Approximately 90 open customer invoices
• Approximately 55 open supplier bills
• Biweekly payroll for 48 employees
• Payroll remittances and sales tax payments
• Approximately 20 recurring operating expenses
• Between five and ten planned equipment purchases
• Customer payment commitments communicated by email
• Management assumptions about collections, spending, and minimum cash reserves

At roughly 160 forecast line items per weekly update, the model was becoming difficult to refresh, review, and explain. The business needed a repeatable forecast that could be updated without rebuilding formulas each week or depending entirely on the controller’s memory.

The implementation described below uses a single operating currency and treats QuickBooks Online register balances as the accounting cash position. Available bank funds may differ when deposits, uncleared payments, restricted cash, or bank-feed timing have not been reconciled. The controller therefore retains an opening-cash adjustment and reconciliation step.

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

The Existing Process

The previous forecasting process was performed in the following order:

1. The accounting specialist exported open invoices, open bills, and bank-account information from QuickBooks Online.
2. The controller copied the exports into an existing spreadsheet.
3. Old rows were removed and new rows were inserted into weekly columns.
4. Expected collection dates were adjusted using customer emails and the controller’s knowledge of payment behavior.
5. Payroll, taxes, recurring expenses, and planned purchases were entered manually.
6. Spreadsheet formulas allocated transactions to weekly columns.
7. The controller changed assumptions to create an informal downside view.
8. Potential shortfalls were identified by scanning ending-cash rows.
9. Selected figures were copied into an email for the owner.
10. At the next update, the model was rolled forward and parts of the previous forecast were overwritten.

The model also depended heavily on one person. If the controller was unavailable, another employee could retrieve accounting exports but could not easily distinguish accounting facts, forecasting assumptions, and management decisions.

The practical problem was not simply spreadsheet formatting. The forecast combined data with different meanings and ownership:

• Accounting facts: Open invoices, open bills, and ledger bank balances
• Forecast assumptions: Expected collection dates, payment timing, and scenario adjustments
• Operating commitments: Payroll, taxes, recurring expenses, and purchases
• Human decisions: Purchase approval, spending deferral, borrowing, and liquidity actions

Without separating these elements, it was difficult to automate the refresh safely or establish a reliable audit trail.

What the New System Needed to Do

The requirements were defined before selecting the implementation approach.

The system also needed to remain understandable. A future finance employee should be able to trace a weekly total back to the QuickBooks transaction or manually entered commitment that created it.

Implementation Approaches Considered

Improved manual spreadsheet

The lowest-effort option was to standardize the existing workbook, protect formulas, and document the export process. This would reduce some spreadsheet risk, but it would not remove recurring extraction, copy-and-paste work, or transaction matching. It would also leave forecast history dependent on manual file copies.

Connected Google Workspace model

This option retained familiar tools while using the QuickBooks Online API for accounting data, Apps Script for orchestration, Google Sheets as the controlled forecast model, Google Drive for snapshots, and Looker Studio for reporting. It required more initial configuration but directly addressed the recurring refresh and auditability problems.

Commercial connector

A commercial connector could reduce the amount of authentication and extraction code. It remained a valid alternative when a business preferred vendor-managed connectivity. However, the forecast still required custom rules for collection timing, approvals, scenarios, history, and shortfall notifications. The connector would solve data movement, not the complete forecasting process.

Dedicated forecasting software

A treasury or cash forecasting platform could provide bank connectivity, advanced permissions, entity consolidation, and vendor support. Alder Creek’s volume, single-entity structure, and existing Google Workspace skills did not yet justify replacing the accounting-adjacent process with a larger platform.

The connected Google Workspace model was selected because it balanced control, maintainability, and implementation effort. The spreadsheet remained visible to finance, while extraction, aggregation, history, and distribution became repeatable.

The Selected Solution

The selected implementation connected QuickBooks Online, Google Sheets, Apps Script, Google Drive, and Looker Studio.

QuickBooks Online remained the source of accounting transactions. Forecast users did not edit invoice or bill values in Google Sheets. Instead, they added separate overrides keyed by the permanent QuickBooks transaction ID.

Google Sheets was retained because the finance team already understood tabular models and needed to inspect detailed inputs. Apps Script was selected over a separate automation platform because the logic involved spreadsheet validation, weekly aggregation, file creation, and report distribution within Google Workspace.

The following manual steps were removed:

• Downloading and copying recurring accounting exports
• Matching forecast assumptions by changing row position
• Creating weekly buckets with copied formulas
• Saving a separate manual forecast-history file
• Scanning every ending-cash cell for minimum-buffer breaches
• Copying weekly figures into a recurring distribution email

The following decisions remained human-controlled:

• Changing a customer’s expected payment date
• Approving or deferring planned purchases
• Setting the minimum cash buffer
• Selecting the scenario used for management action
• Drawing on financing or changing payment commitments
• Accepting or explaining an actual-versus-forecast variance

System Architecture and Data Flow

• Intake: QuickBooks Online API data plus controlled Google Sheets input and override tabs
• System of record: QuickBooks Online for accounting facts and Google Sheets for forecast assumptions and results
• Automation layer: Time-based and edit-based Google Apps Script triggers
• Document storage: Google Drive folders and weekly CSV snapshots
• Notifications: Apps Script email messages containing summary figures, exceptions, and the report link
• Reporting: Looker Studio connected to structured Google Sheets tabs
• AI layer: Optional summary drafting from aggregated forecast data after the core process is stable

End-to-end data flow

1. The weekly trigger starts. A Monday time-based Apps Script trigger requests a document lock, creates a run ID, and checks whether the scheduled run already completed that day. A duplicate scheduled event exits without changing the model.
2. The previous forecast is archived. Current weekly rows are copied to the Forecast_History tab using a unique snapshot key. Existing keys are skipped, preventing duplicate history rows after a retry.
3. QuickBooks access is verified. Apps Script reads OAuth credentials from protected script properties. If the access token is close to expiry, the refresh token is exchanged for a new token. Authentication failure stops the run and creates an error record.
4. Accounting data is retrieved. Paginated QuickBooks queries retrieve bank accounts, invoices with open balances, and bills with open balances. Responses are validated before the source tabs are replaced.
5. Accounting records are normalized. QuickBooks IDs, document numbers, counterparties, dates, open balances, and currency values are mapped to stable spreadsheet columns. Returned transaction IDs become the matching keys for overrides.
6. Forecast assumptions are applied. Invoice collection overrides and bill payment overrides are matched by transaction ID. Missing overrides use documented default timing rules. Invalid override amounts fall back to the open accounting balance and are logged for review.
7. Manual commitments are expanded. Payroll, tax, recurring expense, purchase, and other cash rows are validated. Weekly, biweekly, and monthly recurrences are expanded into dated forecast lines.
8. Approval rules are evaluated. Planned purchases require controller approval. Purchases at or above the configured threshold also require owner approval. Rejected, deferred, or incomplete purchases remain visible in the input queue but are not included in the base forecast.
9. Scenarios are generated. Base forecast lines are copied into defined scenarios. Collection amounts, collection delays, and discretionary-purchase multipliers are applied without changing source records.
10. Weekly totals are calculated. Each dated line is assigned to a Monday-to-Sunday bucket. Opening cash, inflows, outflows, net cash flow, ending cash, minimum buffer, and headroom are calculated sequentially for 13 weeks.
11. Variance is recorded. The current accounting cash balance is compared with the ending cash previously forecast for the completed week. The comparison is appended to the Variance_Log tab rather than overwriting history.
12. Outputs are distributed. Apps Script writes the current result, creates a CSV snapshot in Google Drive, updates the run log, and emails a summary. A shortfall message identifies the earliest projected buffer breach.
13. Looker Studio refreshes its view. The report reads the current forecast, history, variance, and run-log tabs. If a refresh or notification fails, finance can still inspect the latest successful result and the error log.

Data Structure

The workbook uses separate tabs for source accounting data, human assumptions, generated detail, weekly output, history, and operational logs. Separating these concerns prevents a source refresh from overwriting a forecast decision.

Record relationships

AR_Open to AR_Overrides

One current QuickBooks invoice can have zero or one active override, matched by Transaction_ID.

AP_Open to AP_Overrides

One current QuickBooks bill can have zero or one active override, matched by Transaction_ID.

Manual_Inputs to Forecast_Detail

One manual input can generate multiple dated detail rows when it recurs weekly, biweekly, or monthly.

Forecast_Detail to Weekly_Forecast

Many detail rows are aggregated into one weekly row for each scenario and week.

Weekly_Forecast to Forecast_History

Each current weekly row is copied to history before the next successful forecast replaces it.

Forecast_History to Variance_Log

The completed week’s historical base forecast is compared with the accounting cash balance observed during the next refresh.

Workflow Statuses and Ownership

An item moves backward when an approver requests more information, when a source balance changes materially, or when an expected date becomes invalid. Automation never converts a rejection into an approval. Closure is determined by the accounting source or by deactivating a manual input.

Step-by-Step Implementation

Step 1: Prepare the Accounts and Permissions

1. Create a finance-owned Google Shared Drive or restricted Drive folder for the model. Do not place the forecast in an individual employee’s personal folder.
2. Create one Google Sheets workbook named according to a stable convention, such as Finance - 13 Week Cash Forecast.
3. Confirm that the controller and accounting specialist can edit input tabs. Give the owner report access and approval access without allowing changes to generated tabs.
4. Create a QuickBooks developer application for the company-controlled integration. Use separate sandbox and production credentials when both environments are available.
5. Register the deployed Apps Script web-app URL as an authorized OAuth redirect URI. The redirect URI in the QuickBooks application and Apps Script property must match exactly.
6. Store the client ID, client secret, redirect URI, tokens, and realm ID in Apps Script properties. Never place real credentials in worksheet cells.
7. Create a Google Drive folder for timestamped forecast snapshots and copy its folder ID into the Config tab.
8. Create a finance distribution address or controlled list for weekly summaries and failure notices.
9. Create test users representing the accounting specialist, controller, owner, backup approver, and report-only viewer.
10. Use a copied workbook and a QuickBooks sandbox or non-production company during development. If representative test data is used, remove real customer, vendor, payroll, bank, and tax details.

The Apps Script project requests access to the bound spreadsheet, external HTTP requests, Drive files used by the script, email sending, trigger management, and user identity information available within the Workspace environment. Administrators should review generated scopes before authorization.

Permission boundaries are important:

• The accounting specialist can maintain expected dates and routine commitments.
• The controller controls scenarios, minimum buffer, and controller approvals.
• The owner controls high-value purchase approvals and liquidity decisions.
• The Workspace administrator controls sharing, former-user removal, and application access.
• The automation owner controls OAuth credentials and production triggers.

Step 2: Build the Intake

The implementation uses controlled spreadsheet tables rather than a public form. This is appropriate because there are few recurring contributors, the information is financially sensitive, and the inputs require ongoing review rather than one-time submission.

The AR_Overrides tab captures a QuickBooks invoice ID, expected date, expected amount, confidence, owner, notes, and last-reviewed date. AP_Overrides captures a bill ID, planned payment date, amount, hold flag, owner, notes, and last-reviewed date.

Dropdown validation prevents most inconsistent values. Required fields are also checked by the script because spreadsheet validation can be bypassed through pasted data. Incomplete rows remain visible but do not silently enter the forecast.

Duplicate prevention uses transaction IDs for accounting rows and generated Input_ID values for manual rows. A duplicate manual ID stops the run so finance can resolve the ambiguity instead of double-counting the cash movement.

Because the intake is restricted to authenticated internal users, public-form spam controls are not required. The workbook instructions should state that account credentials, bank account numbers, employee payroll detail, tax identifiers, and unrelated personal data must not be entered.

Step 3: Create the System of Record

The Apps Script setup function creates the following tabs:

• Config
• Bank_Accounts
• AR_Open
• AR_Overrides
• AP_Open
• AP_Overrides
• Manual_Inputs
• Scenarios
• Forecast_Detail
• Weekly_Forecast
• Forecast_History
• Variance_Log
• Run_Log
• Errors
• AI_Summaries

Generated tabs are replaced only after source retrieval and validation succeed. Input tabs are never cleared by the refresh. Header names are treated as a schema. If a required header is renamed or moved, the script stops instead of writing data into an incorrect column.

The weekly forecast follows this calculation:

Net Cash Flow = Customer Receipts
              + Other Inflows
              - AP Payments
              - Payroll
              - Taxes
              - Recurring Expenses
              - Planned Purchases
              - Other Outflows

Ending Cash = Opening Cash + Net Cash Flow

Headroom = Ending Cash - Minimum Cash Buffer

Shortfall Flag = Ending Cash < Minimum Cash Buffer

The first week’s opening cash is the sum of selected QuickBooks bank-account balances unless the controller enters an opening-cash override. Each later week begins with the preceding week’s ending cash.

The Scenarios tab contains one row per scenario:

These assumptions are representative and must be approved by management. The downside multiplier does not assert that 15 percent of receivables will be lost. It is a planning assumption that delays and reduces receipts during the 13-week horizon.

Step 4: Connect the Tools

QuickBooks queries use the company realm ID returned during OAuth authorization. The script sends an HTTP GET request to the QuickBooks company query endpoint with a bearer access token. Pagination uses startposition and maxresults. The implementation retries rate-limit and temporary server responses with increasing delays.

Each invoice maps as follows:

• QuickBooks Id to Transaction_ID
• DocNumber to Document_Number
• CustomerRef.name to Customer
• TxnDate to Transaction_Date
• DueDate to Due_Date
• Balance to Open_Balance
• CurrencyRef.value to Currency when present

Bills use the equivalent vendor fields. Account records map Id, Name, AccountType, AccountSubType, and CurrentBalance.

Looker Studio connects separately to Weekly_Forecast, Forecast_History, Variance_Log, and Run_Log. Using separate data sources keeps each worksheet narrow and avoids mixing current and historical grains.

Step 5: Build the Core Automation

The core refresh uses the following sequence.

• Trigger: Monday time-based trigger or an authorized manual refresh
• Conditions: No active lock, valid configuration, no completed scheduled run for the same date, and valid OAuth credentials
• Actions: Archive current output, retrieve accounting records, normalize data, apply overrides, expand recurring inputs, apply scenarios, aggregate 13 weeks, record variance, create a Drive snapshot, and distribute the summary
• Fields updated: Source tabs, Forecast_Detail, Weekly_Forecast, Forecast_History, Variance_Log, Run_Log, and Errors
• Notification: Success summary, shortfall warning, or failure notice
• Exception: Invalid or incomplete records are logged and either excluded or replaced with a conservative documented default

The exact action order is important:

1. Acquire an execution lock.
2. Generate a deterministic scheduled-run ID.
3. Record Started status.
4. Archive existing weekly rows with unique snapshot keys.
5. Refresh OAuth credentials when required.
6. Retrieve paginated QuickBooks records.
7. Validate currency and open balances.
8. Generate missing manual input IDs and reject duplicates.
9. Match overrides using transaction IDs.
10. Validate dates and amounts.
11. Evaluate planned-purchase approvals.
12. Expand recurring input dates.
13. Generate scenario detail rows.
14. Aggregate 13 sequential weeks.
15. Replace generated current-output tabs.
16. Append the completed-week variance.
17. Create the CSV backup.
18. Mark the run successful.
19. Send the weekly notification.
20. Release the execution lock.

External IDs are preserved in Forecast_Detail so a weekly total can be traced back to an invoice, bill, or manual input. If a refresh fails after a history row is appended, a retry checks the Snapshot_Key before appending it again.

Users recover a failed record by correcting the source or override row and running the manual refresh function. The failed Run_ID and error record remain available for audit purposes.

Step 6: Add Approvals, Reminders, and Escalations

Routine accounting transactions do not require a second forecast approval because their amounts already exist in QuickBooks. Discretionary planned purchases use the following rules:

• Every planned purchase requires Controller_Approval equal to Approved.
• A purchase below the configured owner threshold uses Owner_Approval equal to Not Required.
• A purchase at or above the threshold also requires Owner_Approval equal to Approved.
• Rejected, Deferred, Pending, and More Information statuses prevent inclusion.
• An approver can return the item for more information without deleting it.
• Approval timestamps and approver identities are recorded where the Workspace environment exposes the editing user.
• Google Sheets version history remains supporting evidence for protected approval fields.

The representative owner-approval threshold is $25,000. It is stored in Config and should be replaced with the company’s authorized spending policy.

A daily time-based trigger scans planned purchases. Items approaching their scheduled date generate a reminder to the responsible approver. Items that remain pending beyond the configured escalation interval are also sent to the backup approver.

If the normal approver is unavailable, an administrator grants temporary access to the approved delegate and records the delegation in the support documentation. The automation does not automatically treat an unavailable approver as approval.

When a purchase is rejected or deferred, it remains in the manual input table and appears in an operational report. It is not included in the active base forecast unless an authorized user changes the approval status.

Step 7: Add Documents and File Management

The Drive structure is:

• 01 Active Model: The production workbook and report documentation
• 02 Input Support: Quotes, tax schedules, payroll summaries, and approved purchase support organized by year and month
• 03 Weekly Snapshots: Timestamped CSV exports generated by Apps Script
• 04 Archive: Retired templates and year-end forecast packages

Snapshot files follow the naming convention cash-forecast-WEEKLY-YYYY-MM-DD.csv. Supporting files use the related Input_ID or accounting document number where practical.

Drive links are stored in Document_Link fields. The link does not change the file’s permission. Files remain restricted to approved finance and executive groups, and public link sharing is disabled.

Google Drive provides file versioning for the workbook, while Forecast_History provides row-level forecast snapshots. These controls serve different purposes and both are retained.

If the configured backup folder is missing or inaccessible, the forecast run records an error. Finance can still use the generated spreadsheet output, but the run remains incomplete until the snapshot failure is reviewed.

Large attachments are not copied into the spreadsheet. A Drive link is stored instead. Duplicate documents are identified by the Input_ID, file name, and review process rather than by relying only on visual folder inspection.

Step 8: Add Reporting and Operational Views

Looker Studio uses the generated tabs to create four report pages.

Executive cash outlook

• Base-scenario ending cash by week
• Minimum cash buffer reference line
• Earliest projected shortfall date
• Lowest projected cash balance
• Thirteen-week inflow and outflow totals

Scenario comparison

• Ending cash by week for Base, Downside, and Upside
• Difference between base and downside ending cash
• Customer receipt timing by scenario
• Discretionary purchases by scenario

Actual versus forecast

• Actual accounting cash by completed week
• Prior forecast ending cash
• Variance amount and variance direction
• Rolling forecast accuracy trend
• Controller explanation field when maintained

Operations and exceptions

• Open customer invoices with Low or Unreviewed confidence
• Overdue receipts placed into the first forecast week
• Purchases awaiting approval
• Items missing supporting documents
• Failed or skipped automation runs
• Current shortfall weeks
• Recently completed runs

Google Sheets filter views are also created for New Inputs, Needs Review, Pending Approval, Upcoming Within 14 Days, Inactive Items, and Automation Errors. These operational views are easier to maintain in the source workbook than in an executive dashboard.

The controller owns report definitions. The accounting specialist reviews source freshness and exceptions. Looker Studio refresh behavior depends on the connector and account configuration, so the displayed last-refresh timestamp must be shown prominently.

A useful alert threshold is any base-scenario week with negative headroom. A secondary review threshold can flag headroom below 20 percent of the minimum cash buffer, but that threshold should be agreed by management rather than embedded without approval.

Step 9: Add Security and Governance Controls

• Grant edit access only to users who maintain inputs or approvals.
• Protect generated tabs and formula or output ranges from manual editing.
• Restrict controller and owner approval columns to the corresponding roles.
• Store OAuth client credentials and tokens in script properties, not cells or source code.
• Limit the QuickBooks connection to the required accounting scope.
• Use a company-controlled automation owner rather than an employee’s unmanaged consumer account.
• Disable public Drive links and review group membership quarterly.
• Remove former employees from the workbook, Drive folders, Looker Studio report, mailing lists, and QuickBooks application access.
• Retain forecast history and snapshots according to the company’s finance-record policy.
• Review Apps Script execution logs and the workbook Errors tab after failures.
• Do not send customer names, vendor names, invoice notes, payroll details, bank-account identifiers, or tax identifiers to an optional AI service.
• Require human approval before a purchase, financing, payment, or collection action is taken.

The forecast is a management planning model, not a replacement for bank reconciliation, accounting close, treasury authorization, tax advice, or payment controls.

Step 10: Deploy and Test

1. Run the workbook setup function in a development copy.
2. Populate representative manual inputs and scenarios.
3. Authorize a QuickBooks sandbox connection.
4. Run the connection test and confirm account retrieval.
5. Run a manual forecast refresh.
6. Reconcile imported invoice and bill counts to QuickBooks reports.
7. Reconcile the opening accounting cash balance and document adjustments.
8. Test every approval status and threshold boundary.
9. Test duplicate IDs, missing dates, invalid amounts, and a forced authentication failure.
10. Build the Looker Studio report against the test workbook.
11. Conduct user acceptance testing with the controller, accounting specialist, owner, and backup approver.
12. Pilot the model in parallel with the existing forecast for at least two weekly cycles.
13. Resolve variances before enabling production notifications.
14. Deploy the script as a web app for the OAuth callback and register the exact redirect URL.
15. Install weekly, daily reminder, and approval edit triggers.
16. Activate production credentials and run the first supervised refresh.
17. Document rollback by retaining the prior workbook and disabling triggers if reconciliation fails.
18. Communicate the production schedule, report location, exception owner, and support process.

The controller is the production process owner. A second finance employee is trained as backup. The Apps Script project, credentials, workbook, report, and operating documentation are transferred to company-controlled accounts before launch.

Code and Configuration

The bound Apps Script project contains the automation. Open the forecast Google Sheet, open the Apps Script editor from the spreadsheet’s extensions interface, create a script file named CashForecast.gs, and paste the complete code below.

Interface labels may vary by Google Workspace version. The important configuration is that the script is bound to the production workbook and deployed as a web app with an access setting that permits the authorized finance administrator to complete the QuickBooks OAuth callback.

Create the following script properties before authorization:

QBO_CLIENT_ID=YOUR_CLIENT_ID
QBO_CLIENT_SECRET=YOUR_CLIENT_SECRET
QBO_REDIRECT_URI=YOUR_DEPLOYED_WEB_APP_URL

Optional AI properties:
AI_API_KEY=YOUR_API_KEY
AI_MODEL=YOUR_APPROVED_MODEL_NAME

The Config tab receives these initial values from the setup function:

QBO_ENV=production
BASE_CURRENCY=USD
COLLECTION_LAG_DAYS=7
MINIMUM_CASH_BUFFER=150000
OPENING_CASH_OVERRIDE=
OWNER_APPROVAL_THRESHOLD=25000
APPROVAL_REMINDER_DAYS=7
APPROVAL_ESCALATION_DAYS=3
ALERT_EMAILS=YOUR_EMAIL_ADDRESS
CONTROLLER_EMAIL=YOUR_EMAIL_ADDRESS
OWNER_EMAIL=YOUR_EMAIL_ADDRESS
BACKUP_APPROVER_EMAIL=YOUR_EMAIL_ADDRESS
BACKUP_FOLDER_ID=YOUR_FOLDER_ID
REPORT_URL=YOUR_LOOKER_STUDIO_REPORT_URL
MAX_HTTP_RETRIES=3

Replace the placeholders and adjust the assumptions before production use.

const TAB_HEADERS = {
  Config: ['Key', 'Value', 'Description'],
  Bank_Accounts: [
    'Account_ID', 'Account_Name', 'Account_Type', 'Account_SubType',
    'Current_Balance', 'Included_In_Cash', 'Refreshed_At'
  ],
  AR_Open: [
    'Transaction_ID', 'Document_Number', 'Customer', 'Transaction_Date',
    'Due_Date', 'Open_Balance', 'Currency', 'Expected_Date',
    'Expected_Amount', 'Confidence', 'Override_Status', 'Notes', 'Refreshed_At'
  ],
  AR_Overrides: [
    'Transaction_ID', 'Expected_Date', 'Expected_Amount', 'Confidence',
    'Owner', 'Notes', 'Last_Reviewed'
  ],
  AP_Open: [
    'Transaction_ID', 'Document_Number', 'Vendor', 'Transaction_Date',
    'Due_Date', 'Open_Balance', 'Currency', 'Payment_Date',
    'Planned_Amount', 'Hold', 'Override_Status', 'Notes', 'Refreshed_At'
  ],
  AP_Overrides: [
    'Transaction_ID', 'Payment_Date', 'Planned_Amount', 'Hold',
    'Owner', 'Notes', 'Last_Reviewed'
  ],
  Manual_Inputs: [
    'Input_ID', 'Category', 'Description', 'Direction', 'Amount',
    'Scheduled_Date', 'Recurrence', 'End_Date', 'Owner',
    'Controller_Approval', 'Controller_Approved_By',
    'Controller_Approved_At', 'Owner_Approval', 'Owner_Approved_By',
    'Owner_Approved_At', 'Active', 'Document_Link', 'Notes'
  ],
  Scenarios: [
    'Scenario', 'Receipt_Multiplier', 'Receipt_Delay_Days',
    'Discretionary_Spend_Multiplier', 'Active'
  ],
  Forecast_Detail: [
    'Line_Key', 'Scenario', 'Source_Type', 'Source_ID', 'Category',
    'Description', 'Counterparty', 'Scheduled_Date', 'Week_Start',
    'Direction', 'Amount', 'Confidence', 'Owner', 'Approval_Status',
    'Document_Link', 'Notes', 'Run_ID'
  ],
  Weekly_Forecast: [
    'Scenario', 'Week_Start', 'Week_End', 'Opening_Cash',
    'Customer_Receipts', 'Other_Inflows', 'AP_Payments', 'Payroll',
    'Taxes', 'Recurring_Expenses', 'Planned_Purchases', 'Other_Outflows',
    'Net_Cash_Flow', 'Ending_Cash', 'Minimum_Buffer', 'Headroom',
    'Shortfall_Flag', 'Forecast_Run_ID', 'Refreshed_At'
  ],
  Forecast_History: [
    'Snapshot_Key', 'Archived_At', 'Source_Run_ID', 'Scenario',
    'Week_Start', 'Week_End', 'Opening_Cash', 'Net_Cash_Flow',
    'Ending_Cash', 'Minimum_Buffer', 'Headroom'
  ],
  Variance_Log: [
    'Variance_Key', 'Recorded_At', 'Run_ID', 'Scenario',
    'Comparison_Week_Start', 'Comparison_Week_End',
    'Prior_Forecast_Ending_Cash', 'Actual_Accounting_Cash',
    'Variance', 'Prior_Run_ID', 'Explanation'
  ],
  Run_Log: [
    'Run_ID', 'Started_At', 'Finished_At', 'Status',
    'Invoice_Count', 'Bill_Count', 'Detail_Row_Count',
    'Backup_File_URL', 'Message'
  ],
  Errors: [
    'Timestamp', 'Run_ID', 'Function', 'Error_Message',
    'Retryable', 'Context'
  ],
  AI_Summaries: [
    'Summary_ID', 'Created_At', 'Run_ID', 'Model', 'Summary',
    'Risks_JSON', 'Follow_Ups_JSON', 'Input_Tokens', 'Output_Tokens',
    'Review_Status', 'Reviewer', 'Approved_At', 'Error'
  ]
};

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Cash Forecast')
    .addItem('Set up workbook', 'setupWorkbook')
    .addItem('Authorize QuickBooks', 'beginQuickBooksAuthorization')
    .addItem('Test QuickBooks connection', 'testQuickBooksConnection')
    .addItem('Run forecast refresh', 'forceWeeklyRefresh')
    .addItem('Install triggers', 'installTriggers')
    .addToUi();
}

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

  Object.keys(TAB_HEADERS).forEach(function(name) {
    ensureSheet_(ss, name, TAB_HEADERS[name]);
  });

  putConfigDefaults_({
    QBO_ENV: ['production', 'Use production or sandbox.'],
    BASE_CURRENCY: ['USD', 'Single forecast currency.'],
    COLLECTION_LAG_DAYS: ['7', 'Default days added to invoice due dates.'],
    MINIMUM_CASH_BUFFER: ['150000', 'Management-approved minimum cash.'],
    OPENING_CASH_OVERRIDE: ['', 'Optional reconciled opening cash override.'],
    OWNER_APPROVAL_THRESHOLD: ['25000', 'Purchase amount requiring owner approval.'],
    APPROVAL_REMINDER_DAYS: ['7', 'Days before scheduled purchase for reminders.'],
    APPROVAL_ESCALATION_DAYS: ['3', 'Days pending before escalation.'],
    ALERT_EMAILS: ['YOUR_EMAIL_ADDRESS', 'Comma-separated report recipients.'],
    CONTROLLER_EMAIL: ['YOUR_EMAIL_ADDRESS', 'Controller reminder recipient.'],
    OWNER_EMAIL: ['YOUR_EMAIL_ADDRESS', 'Owner reminder recipient.'],
    BACKUP_APPROVER_EMAIL: ['YOUR_EMAIL_ADDRESS', 'Escalation recipient.'],
    BACKUP_FOLDER_ID: ['YOUR_FOLDER_ID', 'Drive folder for CSV snapshots.'],
    REPORT_URL: ['YOUR_LOOKER_STUDIO_REPORT_URL', 'Executive report URL.'],
    MAX_HTTP_RETRIES: ['3', 'Retry count for temporary HTTP failures.']
  });

  const scenarioSheet = ss.getSheetByName('Scenarios');
  if (scenarioSheet.getLastRow() === 1) {
    scenarioSheet.getRange(2, 1, 3, 5).setValues([
      ['Base', 1.00, 0, 1.00, true],
      ['Downside', 0.85, 14, 1.00, true],
      ['Upside', 1.00, 0, 0.90, true]
    ]);
  }

  applyValidations_();
  ss.setSpreadsheetTimeZone(ss.getSpreadsheetTimeZone() || 'Etc/GMT');
  SpreadsheetApp.getUi().alert('Workbook structure created. Review Config before authorization.');
}

function ensureSheet_(ss, name, headers) {
  let sheet = ss.getSheetByName(name);

  if (!sheet) {
    sheet = ss.insertSheet(name);
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  } else 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];
    const mismatch = headers.some(function(header, index) {
      return existing[index] !== header;
    });
    if (mismatch) {
      throw new Error('Header mismatch on sheet ' + name + '. Restore the expected schema.');
    }
  }

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

function putConfigDefaults_(defaults) {
  const sheet = getSheet_('Config');
  const current = readObjects_('Config');
  const existingKeys = new Set(current.map(function(row) {
    return String(row.Key);
  }));
  const rows = [];

  Object.keys(defaults).forEach(function(key) {
    if (!existingKeys.has(key)) {
      rows.push([key, defaults[key][0], defaults[key][1]]);
    }
  });

  if (rows.length) {
    sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, 3).setValues(rows);
  }
}

function applyValidations_() {
  const manual = getSheet_('Manual_Inputs');
  const rowCount = Math.max(manual.getMaxRows() - 1, 1);

  setListValidation_(manual, 2, rowCount, [
    'Payroll', 'Taxes', 'Recurring Expenses',
    'Planned Purchases', 'Other Inflows', 'Other Outflows'
  ]);
  setListValidation_(manual, 4, rowCount, ['Inflow', 'Outflow']);
  setListValidation_(manual, 7, rowCount, [
    'One-time', 'Weekly', 'Biweekly', 'Monthly'
  ]);
  setListValidation_(manual, 10, rowCount, [
    'Pending', 'Approved', 'Rejected', 'More Information', 'Deferred'
  ]);
  setListValidation_(manual, 13, rowCount, [
    'Not Required', 'Pending', 'Approved', 'Rejected',
    'More Information', 'Deferred'
  ]);

  const scenarios = getSheet_('Scenarios');
  scenarios.getRange(2, 5, Math.max(scenarios.getMaxRows() - 1, 1), 1)
    .insertCheckboxes();
}

function setListValidation_(sheet, column, rowCount, values) {
  const rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(values, true)
    .setAllowInvalid(false)
    .build();
  sheet.getRange(2, column, rowCount, 1).setDataValidation(rule);
}

function beginQuickBooksAuthorization() {
  const props = PropertiesService.getScriptProperties();
  const clientId = requireProperty_('QBO_CLIENT_ID');
  const redirectUri = requireProperty_('QBO_REDIRECT_URI');
  const state = Utilities.getUuid();

  props.setProperty('QBO_OAUTH_STATE', state);
  props.setProperty(
    'QBO_OAUTH_STATE_EXPIRES',
    String(Date.now() + 10 * 60 * 1000)
  );

  const url =
    'https://appcenter.intuit.com/connect/oauth2' +
    '?client_id=' + encodeURIComponent(clientId) +
    '&response_type=code' +
    '&scope=' + encodeURIComponent('com.intuit.quickbooks.accounting') +
    '&redirect_uri=' + encodeURIComponent(redirectUri) +
    '&state=' + encodeURIComponent(state);

  Logger.log(url);
  SpreadsheetApp.getUi().alert(
    'Open the authorization URL shown in the Apps Script execution log.'
  );
  return url;
}

function doGet(e) {
  try {
    const props = PropertiesService.getScriptProperties();
    const expectedState = props.getProperty('QBO_OAUTH_STATE');
    const expires = Number(props.getProperty('QBO_OAUTH_STATE_EXPIRES') || 0);

    if (e.parameter.error) {
      throw new Error('QuickBooks authorization returned: ' + e.parameter.error);
    }
    if (!e.parameter.code || !e.parameter.realmId || !e.parameter.state) {
      throw new Error('Authorization response is missing required parameters.');
    }
    if (e.parameter.state !== expectedState || Date.now() > expires) {
      throw new Error('OAuth state is invalid or expired. Start authorization again.');
    }

    const tokens = requestToken_({
      grant_type: 'authorization_code',
      code: e.parameter.code,
      redirect_uri: requireProperty_('QBO_REDIRECT_URI')
    });

    storeTokens_(tokens, e.parameter.realmId);
    props.deleteProperty('QBO_OAUTH_STATE');
    props.deleteProperty('QBO_OAUTH_STATE_EXPIRES');

    return HtmlService.createHtmlOutput(
      'QuickBooks authorization completed. You may close this window.'
    );
  } catch (error) {
    logError_('OAUTH', 'doGet', error, false, 'OAuth callback');
    return HtmlService.createHtmlOutput(
      'Authorization failed. Review the Apps Script execution log and try again.'
    );
  }
}

function requestToken_(payload) {
  const clientId = requireProperty_('QBO_CLIENT_ID');
  const clientSecret = requireProperty_('QBO_CLIENT_SECRET');
  const basic = Utilities.base64Encode(clientId + ':' + clientSecret);

  return httpJson_(
    'https://oauth.platform.intuit.com/oauth2/v1/tokens/bearer',
    {
      method: 'post',
      contentType: 'application/x-www-form-urlencoded',
      headers: {
        Authorization: 'Basic ' + basic,
        Accept: 'application/json'
      },
      payload: formEncode_(payload),
      muteHttpExceptions: true
    },
    'QuickBooks token request',
    getMaxRetries_()
  );
}

function storeTokens_(tokens, realmId) {
  if (!tokens.access_token || !tokens.refresh_token || !tokens.expires_in) {
    throw new Error('Token response did not contain required fields.');
  }

  const props = PropertiesService.getScriptProperties();
  props.setProperty('QBO_ACCESS_TOKEN', tokens.access_token);
  props.setProperty('QBO_REFRESH_TOKEN', tokens.refresh_token);
  props.setProperty(
    'QBO_ACCESS_TOKEN_EXPIRES',
    String(Date.now() + Number(tokens.expires_in) * 1000)
  );
  if (realmId) {
    props.setProperty('QBO_REALM_ID', String(realmId));
  }
}

function getAccessToken_(forceRefresh) {
  const props = PropertiesService.getScriptProperties();
  const token = props.getProperty('QBO_ACCESS_TOKEN');
  const expires = Number(props.getProperty('QBO_ACCESS_TOKEN_EXPIRES') || 0);

  if (!forceRefresh && token && Date.now() < expires - 120000) {
    return token;
  }

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

  try {
    const refreshedToken = props.getProperty('QBO_ACCESS_TOKEN');
    const refreshedExpires = Number(
      props.getProperty('QBO_ACCESS_TOKEN_EXPIRES') || 0
    );

    if (
      !forceRefresh &&
      refreshedToken &&
      Date.now() < refreshedExpires - 120000
    ) {
      return refreshedToken;
    }

    const refreshToken = props.getProperty('QBO_REFRESH_TOKEN');
    if (!refreshToken) {
      throw new Error('No refresh token exists. Authorize QuickBooks again.');
    }

    const tokens = requestToken_({
      grant_type: 'refresh_token',
      refresh_token: refreshToken
    });

    storeTokens_(tokens, props.getProperty('QBO_REALM_ID'));
    return tokens.access_token;
  } finally {
    lock.releaseLock();
  }
}

function testQuickBooksConnection() {
  const rows = qboQueryAll_('Account', "AccountType = 'Bank'");
  Logger.log('Retrieved bank accounts: ' + rows.length);
  SpreadsheetApp.getUi().alert(
    'QuickBooks connection succeeded. Bank accounts returned: ' + rows.length
  );
}

function weeklyRefresh() {
  return runForecastRefresh_(false);
}

function forceWeeklyRefresh() {
  return runForecastRefresh_(true);
}

function runForecastRefresh_(force) {
  const lock = LockService.getDocumentLock();
  if (!lock.tryLock(5000)) {
    throw new Error('Another forecast refresh is already running.');
  }

  const startedAt = new Date();
  const todayKey = dateKey_(startedAt);
  const props = PropertiesService.getScriptProperties();
  const scheduledRunId = 'WEEKLY-' + todayKey;
  const runId = force
    ? 'MANUAL-' + Utilities.formatDate(
        startedAt,
        getTimeZone_(),
        'yyyyMMdd-HHmmss'
      )
    : scheduledRunId;

  try {
    if (!force && props.getProperty('LAST_SUCCESS_DATE') === todayKey) {
      upsertRunLog_({
        Run_ID: runId,
        Started_At: startedAt,
        Finished_At: new Date(),
        Status: 'Skipped',
        Invoice_Count: 0,
        Bill_Count: 0,
        Detail_Row_Count: 0,
        Backup_File_URL: '',
        Message: 'A scheduled refresh already succeeded today.'
      });
      return {status: 'Skipped', runId: runId};
    }

    upsertRunLog_({
      Run_ID: runId,
      Started_At: startedAt,
      Finished_At: '',
      Status: 'Started',
      Invoice_Count: 0,
      Bill_Count: 0,
      Detail_Row_Count: 0,
      Backup_File_URL: '',
      Message: ''
    });

    archiveCurrentForecast_();

    const config = getConfig_();
    ensureManualInputIds_();

    const accounts = qboQueryAll_('Account', "AccountType = 'Bank'");
    const invoices = qboQueryAll_('Invoice', "Balance > '0'");
    const bills = qboQueryAll_('Bill', "Balance > '0'");

    const start = weekStart_(new Date());
    const end = addDays_(start, 90);
    const normalized = normalizeQboData_(
      accounts,
      invoices,
      bills,
      config,
      start,
      runId
    );

    replaceData_('Bank_Accounts', normalized.bankRows);
    replaceData_('AR_Open', normalized.arRows);
    replaceData_('AP_Open', normalized.apRows);

    const manualRows = readObjects_('Manual_Inputs');
    validateUniqueManualIds_(manualRows);

    const baseDetails = buildBaseDetails_(
      normalized,
      manualRows,
      config,
      start,
      end,
      runId
    );

    const scenarios = readScenarios_();
    const scenarioDetails = applyScenarios_(
      baseDetails,
      scenarios,
      start,
      end,
      runId
    );

    const detailRows = scenarioDetails.map(detailToRow_);
    replaceData_('Forecast_Detail', detailRows);

    const openingCash = getOpeningCash_(normalized.bankRows, config);
    const weeklyRows = buildWeeklyForecast_(
      scenarioDetails,
      scenarios,
      openingCash,
      config,
      start,
      runId
    );
    replaceData_('Weekly_Forecast', weeklyRows);

    appendVariance_(
      runId,
      start,
      openingCash
    );

    const backupUrl = createBackupCsv_(weeklyRows, runId, config);

    upsertRunLog_({
      Run_ID: runId,
      Started_At: startedAt,
      Finished_At: new Date(),
      Status: 'Success',
      Invoice_Count: normalized.arForecast.length,
      Bill_Count: normalized.apForecast.length,
      Detail_Row_Count: detailRows.length,
      Backup_File_URL: backupUrl,
      Message: 'Forecast refresh completed.'
    });

    props.setProperty('LAST_SUCCESS_DATE', todayKey);
    sendForecastSummary_(weeklyRows, runId, config, backupUrl);

    return {
      status: 'Success',
      runId: runId,
      invoiceCount: normalized.arForecast.length,
      billCount: normalized.apForecast.length,
      detailCount: detailRows.length
    };
  } catch (error) {
    logError_(runId, 'runForecastRefresh_', error, false, 'Top-level run');

    upsertRunLog_({
      Run_ID: runId,
      Started_At: startedAt,
      Finished_At: new Date(),
      Status: 'Failed',
      Invoice_Count: 0,
      Bill_Count: 0,
      Detail_Row_Count: 0,
      Backup_File_URL: '',
      Message: truncate_(error.message, 500)
    });

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

function qboQueryAll_(entity, whereClause) {
  const realmId = requireProperty_('QBO_REALM_ID');
  const config = getConfig_();
  const host = String(config.QBO_ENV).toLowerCase() === 'sandbox'
    ? 'https://sandbox-quickbooks.api.intuit.com'
    : 'https://quickbooks.api.intuit.com';

  const all = [];
  const pageSize = 1000;
  let startPosition = 1;

  for (let page = 0; page < 100; page++) {
    const query =
      'select * from ' + entity +
      (whereClause ? ' where ' + whereClause : '') +
      ' startposition ' + startPosition +
      ' maxresults ' + pageSize;

    const url =
      host + '/v3/company/' + encodeURIComponent(realmId) +
      '/query?query=' + encodeURIComponent(query);

    const payload = qboGet_(url);
    const response = payload.QueryResponse || {};
    const rows = response[entity] || [];

    Array.prototype.push.apply(all, rows);

    if (rows.length < pageSize) {
      return all;
    }
    startPosition += pageSize;
  }

  throw new Error('QuickBooks pagination exceeded 100 pages for ' + entity + '.');
}

function qboGet_(url) {
  let token = getAccessToken_(false);

  try {
    return httpJson_(
      url,
      {
        method: 'get',
        headers: {
          Authorization: 'Bearer ' + token,
          Accept: 'application/json'
        },
        muteHttpExceptions: true
      },
      'QuickBooks API request',
      getMaxRetries_()
    );
  } catch (error) {
    if (error.httpCode !== 401) {
      throw error;
    }

    token = getAccessToken_(true);
    return httpJson_(
      url,
      {
        method: 'get',
        headers: {
          Authorization: 'Bearer ' + token,
          Accept: 'application/json'
        },
        muteHttpExceptions: true
      },
      'QuickBooks API request after token refresh',
      getMaxRetries_()
    );
  }
}

function httpJson_(url, options, context, maxRetries) {
  let lastError;

  for (let attempt = 0; attempt <= maxRetries; attempt++) {
    const response = UrlFetchApp.fetch(url, options);
    const status = response.getResponseCode();
    const text = response.getContentText();

    if (status >= 200 && status < 300) {
      try {
        return text ? JSON.parse(text) : {};
      } catch (parseError) {
        throw new Error(context + ' returned invalid JSON.');
      }
    }

    const error = new Error(
      context + ' failed with HTTP ' + status + ': ' + truncate_(text, 700)
    );
    error.httpCode = status;
    lastError = error;

    const retryable =
      status === 429 ||
      status === 500 ||
      status === 502 ||
      status === 503 ||
      status === 504;

    if (!retryable || attempt === maxRetries) {
      throw error;
    }

    const headers = response.getAllHeaders();
    const retryAfter = Number(headers['Retry-After'] || 0);
    const waitMs = retryAfter > 0
      ? retryAfter * 1000
      : Math.min(30000, Math.pow(2, attempt) * 1000);

    Utilities.sleep(waitMs);
  }

  throw lastError || new Error(context + ' failed.');
}

function normalizeQboData_(accounts, invoices, bills, config, start, runId) {
  const refreshedAt = new Date();
  const baseCurrency = String(config.BASE_CURRENCY || 'USD').toUpperCase();
  const lag = integer_(config.COLLECTION_LAG_DAYS, 7);

  const arOverrides = mapBy_(
    readObjects_('AR_Overrides'),
    'Transaction_ID'
  );
  const apOverrides = mapBy_(
    readObjects_('AP_Overrides'),
    'Transaction_ID'
  );

  const bankRows = accounts
    .filter(function(account) {
      return account.Active !== false && account.AccountType === 'Bank';
    })
    .map(function(account) {
      return [
        String(account.Id),
        account.Name || '',
        account.AccountType || '',
        account.AccountSubType || '',
        number_(account.CurrentBalance, 0),
        true,
        refreshedAt
      ];
    });

  const arRows = [];
  const arForecast = [];

  invoices.forEach(function(invoice) {
    const balance = number_(invoice.Balance, 0);
    if (balance <= 0) {
      return;
    }

    const currency = invoice.CurrencyRef && invoice.CurrencyRef.value
      ? String(invoice.CurrencyRef.value).toUpperCase()
      : baseCurrency;

    if (currency !== baseCurrency) {
      logError_(
        runId,
        'normalizeQboData_',
        new Error('Foreign-currency invoice excluded: ' + invoice.Id),
        false,
        'Currency ' + currency
      );
      return;
    }

    const override = arOverrides[String(invoice.Id)] || {};
    const dueDate = asDate_(invoice.DueDate) || start;
    const defaultDate = maxDate_(addDays_(dueDate, lag), start);
    const overrideDate = asDate_(override.Expected_Date);
    const expectedDate = overrideDate || defaultDate;
    const expectedAmount = validForecastAmount_(
      override.Expected_Amount,
      balance,
      runId,
      'AR ' + invoice.Id
    );
    const status = overrideDate
      ? 'Override'
      : (override.Expected_Date ? 'Invalid override, default used' : 'Default');

    const customer = invoice.CustomerRef
      ? invoice.CustomerRef.name || invoice.CustomerRef.value || ''
      : '';

    arRows.push([
      String(invoice.Id),
      invoice.DocNumber || '',
      customer,
      asDate_(invoice.TxnDate),
      dueDate,
      balance,
      currency,
      expectedDate,
      expectedAmount,
      override.Confidence || 'Unreviewed',
      status,
      override.Notes || '',
      refreshedAt
    ]);

    arForecast.push({
      sourceType: 'AR',
      sourceId: String(invoice.Id),
      category: 'Customer Receipts',
      description: 'Invoice ' + (invoice.DocNumber || invoice.Id),
      counterparty: customer,
      date: expectedDate,
      direction: 'Inflow',
      amount: expectedAmount,
      confidence: override.Confidence || 'Unreviewed',
      owner: override.Owner || 'Accounting',
      approvalStatus: 'Included',
      documentLink: '',
      notes: override.Notes || ''
    });
  });

  const apRows = [];
  const apForecast = [];

  bills.forEach(function(bill) {
    const balance = number_(bill.Balance, 0);
    if (balance <= 0) {
      return;
    }

    const currency = bill.CurrencyRef && bill.CurrencyRef.value
      ? String(bill.CurrencyRef.value).toUpperCase()
      : baseCurrency;

    if (currency !== baseCurrency) {
      logError_(
        runId,
        'normalizeQboData_',
        new Error('Foreign-currency bill excluded: ' + bill.Id),
        false,
        'Currency ' + currency
      );
      return;
    }

    const override = apOverrides[String(bill.Id)] || {};
    const dueDate = asDate_(bill.DueDate) || start;
    const overrideDate = asDate_(override.Payment_Date);
    const paymentDate = overrideDate || maxDate_(dueDate, start);
    const plannedAmount = validForecastAmount_(
      override.Planned_Amount,
      balance,
      runId,
      'AP ' + bill.Id
    );
    const hold = boolean_(override.Hold);
    const status = overrideDate
      ? 'Override'
      : (override.Payment_Date ? 'Invalid override, due date used' : 'Default');

    const vendor = bill.VendorRef
      ? bill.VendorRef.name || bill.VendorRef.value || ''
      : '';

    apRows.push([
      String(bill.Id),
      bill.DocNumber || '',
      vendor,
      asDate_(bill.TxnDate),
      dueDate,
      balance,
      currency,
      paymentDate,
      plannedAmount,
      hold,
      status,
      override.Notes || '',
      refreshedAt
    ]);

    if (!hold) {
      apForecast.push({
        sourceType: 'AP',
        sourceId: String(bill.Id),
        category: 'AP Payments',
        description: 'Bill ' + (bill.DocNumber || bill.Id),
        counterparty: vendor,
        date: paymentDate,
        direction: 'Outflow',
        amount: plannedAmount,
        confidence: 'High',
        owner: override.Owner || 'Accounting',
        approvalStatus: 'Included',
        documentLink: '',
        notes: override.Notes || ''
      });
    }
  });

  return {
    bankRows: bankRows,
    arRows: arRows,
    apRows: apRows,
    arForecast: arForecast,
    apForecast: apForecast
  };
}

function validForecastAmount_(candidate, balance, runId, context) {
  if (candidate === '' || candidate === null || candidate === undefined) {
    return balance;
  }

  const amount = Number(candidate);
  if (!Number.isFinite(amount) || amount <= 0 || amount > balance) {
    logError_(
      runId,
      'validForecastAmount_',
      new Error('Invalid override amount; open balance used.'),
      false,
      context
    );
    return balance;
  }
  return amount;
}

function buildBaseDetails_(normalized, manualRows, config, start, end, runId) {
  const details = [];

  normalized.arForecast.concat(normalized.apForecast).forEach(function(item) {
    if (item.date >= start && item.date <= end) {
      details.push(item);
    }
  });

  manualRows.forEach(function(row) {
    if (!boolean_(row.Active)) {
      return;
    }

    const id = String(row.Input_ID || '').trim();
    const category = String(row.Category || '').trim();
    const direction = String(row.Direction || '').trim();
    const amount = Number(row.Amount);
    const scheduledDate = asDate_(row.Scheduled_Date);
    const endDate = asDate_(row.End_Date) || end;

    if (
      !id ||
      !category ||
      !['Inflow', 'Outflow'].includes(direction) ||
      !Number.isFinite(amount) ||
      amount <= 0 ||
      !scheduledDate
    ) {
      logError_(
        runId,
        'buildBaseDetails_',
        new Error('Incomplete manual input excluded.'),
        false,
        id || 'Row without ID'
      );
      return;
    }

    if (category === 'Planned Purchases') {
      if (!purchaseIsApproved_(row, config)) {
        return;
      }
    }

    const dates = expandDates_(
      scheduledDate,
      String(row.Recurrence || 'One-time'),
      endDate,
      start,
      end
    );

    dates.forEach(function(date, index) {
      details.push({
        sourceType: 'Manual',
        sourceId: id + (dates.length > 1 ? '-' + (index + 1) : ''),
        category: category,
        description: row.Description || id,
        counterparty: '',
        date: date,
        direction: direction,
        amount: amount,
        confidence: 'High',
        owner: row.Owner || 'Finance',
        approvalStatus: category === 'Planned Purchases'
          ? 'Approved'
          : 'Included',
        documentLink: row.Document_Link || '',
        notes: row.Notes || ''
      });
    });
  });

  return details;
}

function purchaseIsApproved_(row, config) {
  const amount = Number(row.Amount);
  const threshold = number_(config.OWNER_APPROVAL_THRESHOLD, 25000);

  if (String(row.Controller_Approval) !== 'Approved') {
    return false;
  }
  if (amount >= threshold && String(row.Owner_Approval) !== 'Approved') {
    return false;
  }
  return true;
}

function expandDates_(firstDate, recurrence, endDate, windowStart, windowEnd) {
  const dates = [];
  let current = new Date(firstDate.getTime());
  let guard = 0;

  while (current < windowStart && recurrence !== 'One-time') {
    current = nextRecurrence_(current, recurrence);
    guard++;
    if (guard > 500) {
      throw new Error('Recurrence advance exceeded safety limit.');
    }
  }

  while (current <= windowEnd && current <= endDate) {
    if (current >= windowStart) {
      dates.push(new Date(current.getTime()));
    }
    if (recurrence === 'One-time') {
      break;
    }
    current = nextRecurrence_(current, recurrence);
    guard++;
    if (guard > 500) {
      throw new Error('Recurrence generation exceeded safety limit.');
    }
  }

  return dates;
}

function nextRecurrence_(date, recurrence) {
  if (recurrence === 'Weekly') {
    return addDays_(date, 7);
  }
  if (recurrence === 'Biweekly') {
    return addDays_(date, 14);
  }
  if (recurrence === 'Monthly') {
    return addMonthsSafe_(date, 1);
  }
  throw new Error('Unsupported recurrence: ' + recurrence);
}

function applyScenarios_(baseDetails, scenarios, start, end, runId) {
  const output = [];

  scenarios.forEach(function(scenario) {
    baseDetails.forEach(function(base) {
      let date = new Date(base.date.getTime());
      let amount = base.amount;

      if (base.category === 'Customer Receipts') {
        date = addDays_(date, scenario.receiptDelayDays);
        amount = amount * scenario.receiptMultiplier;
      }

      if (base.category === 'Planned Purchases') {
        amount = amount * scenario.discretionarySpendMultiplier;
      }

      if (date < start || date > end || amount <= 0) {
        return;
      }

      output.push({
        lineKey: [
          scenario.name,
          base.sourceType,
          base.sourceId,
          dateKey_(date)
        ].join('|'),
        scenario: scenario.name,
        sourceType: base.sourceType,
        sourceId: base.sourceId,
        category: base.category,
        description: base.description,
        counterparty: base.counterparty,
        date: date,
        weekStart: weekStart_(date),
        direction: base.direction,
        amount: roundCurrency_(amount),
        confidence: base.confidence,
        owner: base.owner,
        approvalStatus: base.approvalStatus,
        documentLink: base.documentLink,
        notes: base.notes,
        runId: runId
      });
    });
  });

  return output;
}

function readScenarios_() {
  const rows = readObjects_('Scenarios')
    .filter(function(row) {
      return boolean_(row.Active);
    })
    .map(function(row) {
      const receiptMultiplier = Number(row.Receipt_Multiplier);
      const delay = Number(row.Receipt_Delay_Days);
      const spendMultiplier = Number(row.Discretionary_Spend_Multiplier);

      if (
        !row.Scenario ||
        !Number.isFinite(receiptMultiplier) ||
        receiptMultiplier < 0 ||
        receiptMultiplier > 2 ||
        !Number.isInteger(delay) ||
        delay < 0 ||
        delay > 90 ||
        !Number.isFinite(spendMultiplier) ||
        spendMultiplier < 0 ||
        spendMultiplier > 2
      ) {
        throw new Error('Invalid scenario configuration: ' + row.Scenario);
      }

      return {
        name: String(row.Scenario),
        receiptMultiplier: receiptMultiplier,
        receiptDelayDays: delay,
        discretionarySpendMultiplier: spendMultiplier
      };
    });

  if (!rows.some(function(row) { return row.name === 'Base'; })) {
    throw new Error('An active Base scenario is required.');
  }
  return rows;
}

function buildWeeklyForecast_(
  details,
  scenarios,
  openingCash,
  config,
  start,
  runId
) {
  const minimumBuffer = number_(config.MINIMUM_CASH_BUFFER, 0);
  const refreshedAt = new Date();
  const rows = [];

  scenarios.forEach(function(scenario) {
    let opening = openingCash;

    for (let weekIndex = 0; weekIndex < 13; weekIndex++) {
      const weekStart = addDays_(start, weekIndex * 7);
      const weekEnd = addDays_(weekStart, 6);
      const totals = {
        Customer_Receipts: 0,
        Other_Inflows: 0,
        AP_Payments: 0,
        Payroll: 0,
        Taxes: 0,
        Recurring_Expenses: 0,
        Planned_Purchases: 0,
        Other_Outflows: 0
      };

      details
        .filter(function(detail) {
          return (
            detail.scenario === scenario.name &&
            dateKey_(detail.weekStart) === dateKey_(weekStart)
          );
        })
        .forEach(function(detail) {
          const key = categoryKey_(detail.category, detail.direction);
          totals[key] += detail.amount;
        });

      const inflows =
        totals.Customer_Receipts + totals.Other_Inflows;
      const outflows =
        totals.AP_Payments +
        totals.Payroll +
        totals.Taxes +
        totals.Recurring_Expenses +
        totals.Planned_Purchases +
        totals.Other_Outflows;

      const net = roundCurrency_(inflows - outflows);
      const ending = roundCurrency_(opening + net);
      const headroom = roundCurrency_(ending - minimumBuffer);

      rows.push([
        scenario.name,
        weekStart,
        weekEnd,
        roundCurrency_(opening),
        roundCurrency_(totals.Customer_Receipts),
        roundCurrency_(totals.Other_Inflows),
        roundCurrency_(totals.AP_Payments),
        roundCurrency_(totals.Payroll),
        roundCurrency_(totals.Taxes),
        roundCurrency_(totals.Recurring_Expenses),
        roundCurrency_(totals.Planned_Purchases),
        roundCurrency_(totals.Other_Outflows),
        net,
        ending,
        minimumBuffer,
        headroom,
        ending < minimumBuffer,
        runId,
        refreshedAt
      ]);

      opening = ending;
    }
  });

  return rows;
}

function categoryKey_(category, direction) {
  const map = {
    'Customer Receipts': 'Customer_Receipts',
    'Other Inflows': 'Other_Inflows',
    'AP Payments': 'AP_Payments',
    'Payroll': 'Payroll',
    'Taxes': 'Taxes',
    'Recurring Expenses': 'Recurring_Expenses',
    'Planned Purchases': 'Planned_Purchases',
    'Other Outflows': 'Other_Outflows'
  };

  if (map[category]) {
    return map[category];
  }
  return direction === 'Inflow' ? 'Other_Inflows' : 'Other_Outflows';
}

function detailToRow_(detail) {
  return [
    detail.lineKey,
    detail.scenario,
    detail.sourceType,
    detail.sourceId,
    detail.category,
    detail.description,
    detail.counterparty,
    detail.date,
    detail.weekStart,
    detail.direction,
    detail.amount,
    detail.confidence,
    detail.owner,
    detail.approvalStatus,
    detail.documentLink,
    detail.notes,
    detail.runId
  ];
}

function archiveCurrentForecast_() {
  const current = readObjects_('Weekly_Forecast');
  if (!current.length) {
    return;
  }

  const archivedAt = new Date();
  const rows = current.map(function(row) {
    const key = [
      row.Forecast_Run_ID,
      row.Scenario,
      dateKey_(row.Week_Start)
    ].join('|');

    return [
      key,
      archivedAt,
      row.Forecast_Run_ID,
      row.Scenario,
      asDate_(row.Week_Start),
      asDate_(row.Week_End),
      number_(row.Opening_Cash, 0),
      number_(row.Net_Cash_Flow, 0),
      number_(row.Ending_Cash, 0),
      number_(row.Minimum_Buffer, 0),
      number_(row.Headroom, 0)
    ];
  });

  appendUniqueRows_('Forecast_History', rows, 1);
}

function appendVariance_(runId, currentWeekStart, actualAccountingCash) {
  const previousWeekStart = addDays_(currentWeekStart, -7);
  const history = readObjects_('Forecast_History')
    .filter(function(row) {
      return (
        String(row.Scenario) === 'Base' &&
        dateKey_(row.Week_Start) === dateKey_(previousWeekStart)
      );
    })
    .sort(function(a, b) {
      return asDate_(b.Archived_At) - asDate_(a.Archived_At);
    });

  if (!history.length) {
    return;
  }

  const prior = history[0];
  const key = [
    runId,
    'Base',
    dateKey_(previousWeekStart)
  ].join('|');

  const priorEnding = number_(prior.Ending_Cash, 0);
  const variance = roundCurrency_(actualAccountingCash - priorEnding);

  appendUniqueRows_('Variance_Log', [[
    key,
    new Date(),
    runId,
    'Base',
    previousWeekStart,
    addDays_(previousWeekStart, 6),
    priorEnding,
    roundCurrency_(actualAccountingCash),
    variance,
    prior.Source_Run_ID,
    ''
  ]], 1);
}

function getOpeningCash_(bankRows, config) {
  const override = String(config.OPENING_CASH_OVERRIDE || '').trim();
  if (override !== '') {
    const value = Number(override);
    if (!Number.isFinite(value)) {
      throw new Error('OPENING_CASH_OVERRIDE must be numeric or blank.');
    }
    return value;
  }

  return bankRows.reduce(function(total, row) {
    return row[5] === true ? total + number_(row[4], 0) : total;
  }, 0);
}

function createBackupCsv_(weeklyRows, runId, config) {
  const folderId = String(config.BACKUP_FOLDER_ID || '').trim();
  if (!folderId || folderId.indexOf('YOUR_') === 0) {
    return '';
  }

  try {
    const headers = TAB_HEADERS.Weekly_Forecast;
    const allRows = [headers].concat(weeklyRows);
    const csv = allRows.map(function(row) {
      return row.map(csvValue_).join(',');
    }).join('\n');

    const fileName = 'cash-forecast-' + runId + '.csv';
    const file = DriveApp.getFolderById(folderId)
      .createFile(fileName, csv, MimeType.CSV);
    return file.getUrl();
  } catch (error) {
    logError_(runId, 'createBackupCsv_', error, false, 'Drive snapshot');
    throw new Error('Forecast created, but Drive snapshot failed: ' + error.message);
  }
}

function csvValue_(value) {
  let text = value instanceof Date
    ? Utilities.formatDate(value, getTimeZone_(), "yyyy-MM-dd'T'HH:mm:ss")
    : String(value === null || value === undefined ? '' : value);
  text = text.replace(/"/g, '""');
  return '"' + text + '"';
}

function sendForecastSummary_(weeklyRows, runId, config, backupUrl) {
  const recipients = validEmails_(config.ALERT_EMAILS);
  if (!recipients.length) {
    return;
  }

  const base = weeklyRows.filter(function(row) {
    return row[0] === 'Base';
  });
  const shortfalls = base.filter(function(row) {
    return row[16] === true;
  });
  const first = base[0];
  const last = base[base.length - 1];
  const reportUrl = String(config.REPORT_URL || '');
  const lines = [
    '13-week cash forecast completed.',
    'Run ID: ' + runId,
    'Opening cash: ' + formatMoney_(first ? first[3] : 0),
    'Week 13 ending cash: ' + formatMoney_(last ? last[13] : 0),
    'Shortfall weeks: ' + shortfalls.length
  ];

  if (shortfalls.length) {
    lines.push(
      'First shortfall week: ' + dateKey_(shortfalls[0][1]) +
      ', ending cash ' + formatMoney_(shortfalls[0][13])
    );
  }
  if (reportUrl && reportUrl.indexOf('YOUR_') !== 0) {
    lines.push('Report: ' + reportUrl);
  }
  if (backupUrl) {
    lines.push('Snapshot: ' + backupUrl);
  }

  MailApp.sendEmail({
    to: recipients.join(','),
    subject: shortfalls.length
      ? 'Cash forecast action required: ' + runId
      : 'Cash forecast completed: ' + runId,
    body: lines.join('\n')
  });
}

function sendFailureNotice_(runId, error) {
  const config = getConfig_();
  const recipients = validEmails_(
    [config.ALERT_EMAILS, config.CONTROLLER_EMAIL].join(',')
  );
  if (!recipients.length) {
    return;
  }

  try {
    MailApp.sendEmail({
      to: recipients.join(','),
      subject: 'Cash forecast refresh failed: ' + runId,
      body:
        'The forecast refresh failed.\n' +
        'Run ID: ' + runId + '\n' +
        'Error: ' + truncate_(error.message, 700) + '\n' +
        'Review Apps Script executions and the Errors tab.'
    });
  } catch (mailError) {
    logError_(runId, 'sendFailureNotice_', mailError, false, 'Failure email');
  }
}

function dailyActionReminder() {
  const config = getConfig_();
  const manualRows = readObjects_('Manual_Inputs');
  const threshold = number_(config.OWNER_APPROVAL_THRESHOLD, 25000);
  const reminderDays = integer_(config.APPROVAL_REMINDER_DAYS, 7);
  const today = new Date();
  const controllerItems = [];
  const ownerItems = [];

  manualRows.forEach(function(row) {
    if (
      !boolean_(row.Active) ||
      String(row.Category) !== 'Planned Purchases'
    ) {
      return;
    }

    const scheduled = asDate_(row.Scheduled_Date);
    if (!scheduled || scheduled > addDays_(today, reminderDays)) {
      return;
    }

    if (String(row.Controller_Approval) !== 'Approved') {
      controllerItems.push(row.Input_ID + ': ' + row.Description);
    } else if (
      number_(row.Amount, 0) >= threshold &&
      String(row.Owner_Approval) !== 'Approved'
    ) {
      ownerItems.push(row.Input_ID + ': ' + row.Description);
    }
  });

  sendActionList_(
    validEmails_(config.CONTROLLER_EMAIL),
    'Cash forecast purchase approvals awaiting controller action',
    controllerItems
  );
  sendActionList_(
    validEmails_([config.OWNER_EMAIL, config.BACKUP_APPROVER_EMAIL].join(',')),
    'Cash forecast purchase approvals awaiting owner action',
    ownerItems
  );

  const weekly = readObjects_('Weekly_Forecast').filter(function(row) {
    return String(row.Scenario) === 'Base' && boolean_(row.Shortfall_Flag);
  });

  if (weekly.length) {
    sendActionList_(
      validEmails_([config.CONTROLLER_EMAIL, config.OWNER_EMAIL].join(',')),
      'Cash forecast contains a projected shortfall',
      weekly.map(function(row) {
        return dateKey_(row.Week_Start) + ': ' +
          formatMoney_(row.Ending_Cash);
      })
    );
  }
}

function sendActionList_(recipients, subject, items) {
  if (!recipients.length || !items.length) {
    return;
  }
  MailApp.sendEmail({
    to: recipients.join(','),
    subject: subject,
    body: items.join('\n')
  });
}

function approvalEditHandler(e) {
  if (!e || !e.range) {
    return;
  }

  const sheet = e.range.getSheet();
  if (sheet.getName() !== 'Manual_Inputs' || e.range.getRow() < 2) {
    return;
  }

  const headers = TAB_HEADERS.Manual_Inputs;
  const editedHeader = headers[e.range.getColumn() - 1];
  const userEmail =
    e.user && typeof e.user.getEmail === 'function'
      ? e.user.getEmail()
      : Session.getActiveUser().getEmail() || 'Identity unavailable';

  if (editedHeader === 'Controller_Approval') {
    stampApproval_(
      sheet,
      e.range.getRow(),
      'Controller_Approved_By',
      'Controller_Approved_At',
      e.value === 'Approved',
      userEmail
    );
  }

  if (editedHeader === 'Owner_Approval') {
    stampApproval_(
      sheet,
      e.range.getRow(),
      'Owner_Approved_By',
      'Owner_Approved_At',
      e.value === 'Approved',
      userEmail
    );
  }
}

function stampApproval_(
  sheet,
  row,
  approvedByHeader,
  approvedAtHeader,
  approved,
  userEmail
) {
  const headers = TAB_HEADERS.Manual_Inputs;
  const byColumn = headers.indexOf(approvedByHeader) + 1;
  const atColumn = headers.indexOf(approvedAtHeader) + 1;

  sheet.getRange(row, byColumn).setValue(approved ? userEmail : '');
  sheet.getRange(row, atColumn).setValue(approved ? new Date() : '');
}

function installTriggers() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const managed = [
    'weeklyRefresh',
    'dailyActionReminder',
    'approvalEditHandler'
  ];

  ScriptApp.getProjectTriggers().forEach(function(trigger) {
    if (managed.includes(trigger.getHandlerFunction())) {
      ScriptApp.deleteTrigger(trigger);
    }
  });

  ScriptApp.newTrigger('weeklyRefresh')
    .timeBased()
    .onWeekDay(ScriptApp.WeekDay.MONDAY)
    .atHour(6)
    .create();

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

  ScriptApp.newTrigger('approvalEditHandler')
    .forSpreadsheet(ss)
    .onEdit()
    .create();

  SpreadsheetApp.getUi().alert('Forecast triggers installed.');
}

function ensureManualInputIds_() {
  const sheet = getSheet_('Manual_Inputs');
  if (sheet.getLastRow() < 2) {
    return;
  }

  const headers = TAB_HEADERS.Manual_Inputs;
  const idColumn = headers.indexOf('Input_ID') + 1;
  const categoryColumn = headers.indexOf('Category') + 1;
  const rowCount = sheet.getLastRow() - 1;
  const ids = sheet.getRange(2, idColumn, rowCount, 1).getValues();
  const categories = sheet.getRange(2, categoryColumn, rowCount, 1).getValues();
  let changed = false;

  for (let index = 0; index < rowCount; index++) {
    if (!ids[index][0] && categories[index][0]) {
      ids[index][0] =
        'MI-' + dateKey_(new Date()).replace(/-/g, '') + '-' +
        Utilities.getUuid().replace(/-/g, '').slice(0, 8).toUpperCase();
      changed = true;
    }
  }

  if (changed) {
    sheet.getRange(2, idColumn, rowCount, 1).setValues(ids);
  }
}

function validateUniqueManualIds_(rows) {
  const seen = new Set();

  rows.forEach(function(row) {
    const id = String(row.Input_ID || '').trim();
    if (!id) {
      return;
    }
    if (seen.has(id)) {
      throw new Error('Duplicate Manual_Inputs Input_ID: ' + id);
    }
    seen.add(id);
  });
}

function replaceData_(sheetName, rows) {
  const sheet = getSheet_(sheetName);
  const headers = TAB_HEADERS[sheetName];

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

  if (sheet.getMaxRows() > 1) {
    sheet.getRange(
      2,
      1,
      sheet.getMaxRows() - 1,
      headers.length
    ).clearContent();
  }

  if (!rows.length) {
    return;
  }

  if (sheet.getMaxRows() < rows.length + 1) {
    sheet.insertRowsAfter(
      sheet.getMaxRows(),
      rows.length + 1 - sheet.getMaxRows()
    );
  }

  sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
}

function appendUniqueRows_(sheetName, rows, keyColumn) {
  if (!rows.length) {
    return;
  }

  const sheet = getSheet_(sheetName);
  const existing = new Set();

  if (sheet.getLastRow() > 1) {
    sheet.getRange(
      2,
      keyColumn,
      sheet.getLastRow() - 1,
      1
    ).getValues().forEach(function(row) {
      existing.add(String(row[0]));
    });
  }

  const newRows = rows.filter(function(row) {
    const key = String(row[keyColumn - 1]);
    if (existing.has(key)) {
      return false;
    }
    existing.add(key);
    return true;
  });

  if (newRows.length) {
    sheet.getRange(
      sheet.getLastRow() + 1,
      1,
      newRows.length,
      TAB_HEADERS[sheetName].length
    ).setValues(newRows);
  }
}

function upsertRunLog_(record) {
  const sheet = getSheet_('Run_Log');
  const headers = TAB_HEADERS.Run_Log;
  let targetRow = 0;

  if (sheet.getLastRow() > 1) {
    const ids = sheet.getRange(2, 1, sheet.getLastRow() - 1, 1).getValues();
    for (let index = 0; index < ids.length; index++) {
      if (String(ids[index][0]) === String(record.Run_ID)) {
        targetRow = index + 2;
        break;
      }
    }
  }

  const values = headers.map(function(header) {
    return record[header] === undefined ? '' : record[header];
  });

  if (targetRow) {
    sheet.getRange(targetRow, 1, 1, headers.length).setValues([values]);
  } else {
    sheet.getRange(sheet.getLastRow() + 1, 1, 1, headers.length)
      .setValues([values]);
  }
}

function logError_(runId, functionName, error, retryable, context) {
  try {
    const sheet = getSheet_('Errors');
    sheet.appendRow([
      new Date(),
      runId || '',
      functionName,
      truncate_(error && error.message ? error.message : String(error), 1000),
      Boolean(retryable),
      truncate_(context || '', 500)
    ]);
  } catch (loggingError) {
    console.error(loggingError);
  }
}

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

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

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

function getConfig_() {
  const rows = readObjects_('Config');
  const config = {};
  rows.forEach(function(row) {
    config[String(row.Key)] = row.Value;
  });
  return config;
}

function getSheet_(name) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name);
  if (!sheet) {
    throw new Error('Required sheet does not exist: ' + name);
  }
  return sheet;
}

function mapBy_(rows, field) {
  const map = {};
  rows.forEach(function(row) {
    const key = String(row[field] || '').trim();
    if (key) {
      map[key] = row;
    }
  });
  return map;
}

function formEncode_(object) {
  return Object.keys(object).map(function(key) {
    return encodeURIComponent(key) + '=' + encodeURIComponent(object[key]);
  }).join('&');
}

function requireProperty_(name) {
  const value = PropertiesService.getScriptProperties().getProperty(name);
  if (!value || value.indexOf('YOUR_') === 0) {
    throw new Error('Missing required script property: ' + name);
  }
  return value;
}

function getMaxRetries_() {
  const config = getConfig_();
  return Math.max(0, Math.min(5, integer_(config.MAX_HTTP_RETRIES, 3)));
}

function getTimeZone_() {
  return SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();
}

function asDate_(value) {
  if (value instanceof Date && !isNaN(value.getTime())) {
    return new Date(value.getTime());
  }
  if (!value) {
    return null;
  }

  const text = String(value).trim();
  const match = text.match(/^(\d{4})-(\d{2})-(\d{2})/);
  if (match) {
    return new Date(
      Number(match[1]),
      Number(match[2]) - 1,
      Number(match[3])
    );
  }

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

function weekStart_(date) {
  const result = new Date(date.getFullYear(), date.getMonth(), date.getDate());
  const day = result.getDay();
  const offset = day === 0 ? -6 : 1 - day;
  result.setDate(result.getDate() + offset);
  return result;
}

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

function addMonthsSafe_(date, months) {
  const day = date.getDate();
  const result = new Date(date.getFullYear(), date.getMonth() + months, 1);
  const lastDay = new Date(
    result.getFullYear(),
    result.getMonth() + 1,
    0
  ).getDate();
  result.setDate(Math.min(day, lastDay));
  return result;
}

function maxDate_(a, b) {
  return a.getTime() > b.getTime() ? a : b;
}

function dateKey_(date) {
  const parsed = asDate_(date);
  if (!parsed) {
    return '';
  }
  return Utilities.formatDate(parsed, getTimeZone_(), 'yyyy-MM-dd');
}

function number_(value, fallback) {
  const result = Number(value);
  return Number.isFinite(result) ? result : fallback;
}

function integer_(value, fallback) {
  const result = Number(value);
  return Number.isInteger(result) ? result : fallback;
}

function boolean_(value) {
  return value === true || String(value).toLowerCase() === 'true';
}

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

function formatMoney_(value) {
  return Number(value || 0).toLocaleString('en-US', {
    style: 'currency',
    currency: String(getConfig_().BASE_CURRENCY || 'USD')
  });
}

function validEmails_(value) {
  const seen = new Set();
  return String(value || '')
    .split(',')
    .map(function(email) {
      return email.trim();
    })
    .filter(function(email) {
      if (
        !email ||
        email.indexOf('YOUR_') === 0 ||
        !/^[^@\s]+@[^@\s]+\.[^@\s]+$/.test(email) ||
        seen.has(email.toLowerCase())
      ) {
        return false;
      }
      seen.add(email.toLowerCase());
      return true;
    });
}

function truncate_(value, length) {
  const text = String(value || '');
  return text.length > length ? text.slice(0, length) + '...' : text;
}

How to authorize and deploy the script

1. Run setupWorkbook and authorize the Google permissions requested by Apps Script.
2. Create the QuickBooks OAuth application and obtain its client ID and client secret.
3. Deploy the Apps Script project as a web app. Copy the deployment URL.
4. Add that URL to the QuickBooks application’s allowed redirect URI configuration.
5. Add QBO_CLIENT_ID, QBO_CLIENT_SECRET, and QBO_REDIRECT_URI to Apps Script properties.
6. Run beginQuickBooksAuthorization.
7. Open the generated authorization URL from the execution log, sign in, select the intended company, and approve access.
8. Confirm that the callback stores the realm ID, access token, refresh token, and expiry time in script properties.
9. Run testQuickBooksConnection.
10. Run forceWeeklyRefresh in the test workbook.
11. Review source counts, forecast detail, weekly output, history, variance, run log, and errors.
12. Run installTriggers only after testing succeeds.

The weekly trigger is time-based. The approval audit trigger is edit-based. Apps Script time triggers execute within a scheduling window rather than at a guaranteed exact minute.

Expected inputs and outputs

The script expects valid configuration values, an authorized QuickBooks company, correctly named worksheet headers, single-currency accounting records, and controlled manual inputs. It produces 13 rows per active scenario in Weekly_Forecast, one detail row per scenario and dated cash movement, history snapshots, a completed-week variance record, a run-log entry, and an optional Drive CSV file.

Testing and troubleshooting

• If authorization fails, compare the deployed callback URL with the QuickBooks redirect URI character by character.
• If a 401 response persists, revoke the stale connection and complete authorization again.
• If a query fails, inspect the HTTP status and QuickBooks response recorded in Apps Script executions.
• If source tabs are empty, confirm that the selected company contains active bank accounts and open transactions.
• If totals differ, reconcile account inclusion, open balances, overrides, currency, and opening-cash adjustments.
• If a trigger cannot send email or create a Drive file, reauthorize the script under the production automation owner.
• If a worksheet header changes, restore the expected header rather than modifying generated output columns casually.
• If the run times out at high volume, reduce query scope, split extraction from aggregation, or move the data layer to a database or managed integration service.

Duplicate scheduled execution is prevented by the LAST_SUCCESS_DATE property, document locking, stable run IDs, and unique history keys. Failures are recorded in Run_Log and Errors. Temporary HTTP failures are retried. Business-data exceptions require correction and a manual refresh.

Failure Handling and Operational Reliability

The Run_Log acts as the operational queue for complete, failed, and skipped runs. The Errors tab captures record-level exceptions. Apps Script execution history provides deeper technical diagnostics.

There is no separate dead-letter service in this implementation. The Errors tab functions as a lightweight manual-review queue. If failures become frequent or transaction volume increases, a database-backed error queue with explicit retry status, payload hashes, and assignment fields would be appropriate.

Weekly reconciliation should compare:

• Imported open invoice total with the QuickBooks receivables report
• Imported open bill total with the QuickBooks payables report
• Included bank-account total with reconciled accounting cash
• Forecast detail totals with weekly output
• Current Run_ID with the report’s displayed refresh timestamp

A Complete Example

Assume Alder Creek runs the forecast on Monday, July 13, 2026. The script generates Run_ID WEEKLY-2026-07-13.

1. QuickBooks returns two included bank accounts with a combined accounting balance of $428,000. No opening-cash override exists, so $428,000 becomes the base opening cash.
2. QuickBooks returns invoice ID 91345, document number 18472, with an open balance of $62,400 and a due date of July 17.
3. The AR_Overrides tab contains transaction ID 91345, expected date July 24, expected amount $62,400, confidence Medium, and a note that the customer confirmed the later date.
4. The script uses July 24 rather than applying the default seven-day collection lag. The base detail line receives a key containing Base, AR, transaction ID 91345, and the scheduled date.
5. The downside scenario multiplies the receipt by 0.85 and delays it by 14 additional days. This creates a separate scenario line without changing the accounting invoice or base override.
6. QuickBooks returns bill ID 77120 for $31,800 due July 22. No AP override exists, so the due date becomes the expected payment date.
7. A biweekly payroll input of $96,000 begins July 17. The recurrence logic generates payroll rows on July 17, July 31, August 14, and subsequent biweekly dates within the horizon.
8. A planned equipment purchase of $42,000 is scheduled for August 10. Controller approval is Approved, but owner approval is Pending. Because the amount exceeds the $25,000 threshold, the purchase is not included.
9. The daily reminder identifies the pending owner approval. The owner can approve, reject, defer, or request more information.
10. The weekly aggregation calculates sequential ending cash. In this illustrative run, the base scenario first falls below the $150,000 buffer in the week beginning August 31.
11. The success email identifies the first shortfall week and includes the Looker Studio report link and Drive snapshot link.
12. The controller reviews the detailed lines and determines that a purchase should be deferred. The owner records Deferred rather than Approved, preserving the decision in the input table and version history.
13. The following Monday, the script archives the July 13 forecast before creating a new one. It compares the prior completed week’s forecast ending cash with the current accounting cash and appends the variance.

The final result is not an automated financing or payment decision. It is a current, traceable forecast that shows the timing and assumptions behind the projected shortfall so management can decide what action is appropriate.

Implementation Cost

The following amounts are representative planning assumptions, not verified client costs. Existing subscription costs vary by edition, user count, contract, tax, currency, and required features. Current vendor terms should be confirmed before implementation.

Software that has no incremental license amount still requires configuration, security review, monitoring, documentation, and ownership. Those labour requirements should not be omitted from the business case.

Estimated Time and Cost Savings

The savings estimate uses the following representative assumptions:

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

640 × 4.5 ÷ 60 = 48.00 hours

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

Routine handling:
640 × 1.5 ÷ 60 = 16.00 hours

Exception handling:
640 × 8% × 3 ÷ 60 = 2.56 hours

Maintenance:
3.00 hours

Total new monthly labour:
16.00 + 2.56 + 3.00 = 21.56 hours

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

48.00 - 21.56 = 26.44 hours

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

26.44 × $55 = $1,454.20

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

$1,454.20 - $25.00 = $1,429.20

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

$8,150 ÷ $1,429.20 = 5.70 months

Recovered time does not automatically reduce payroll. It may instead provide additional capacity for collections follow-up, account reconciliation, variance analysis, purchasing review, management reporting, and higher transaction volume.

Non-financial benefits include:

• Clear ownership of forecast inputs and approvals
• Fewer recurring export and copy operations
• Stable transaction matching through external IDs
• Earlier visibility into projected cash-buffer breaches
• Consistent base, downside, and upside assumptions
• Preserved forecast history
• More reproducible actual-versus-forecast analysis
• Less dependence on one employee’s spreadsheet knowledge
• Better traceability from a weekly total to its source item
• More consistent reporting to executive leadership

Readers should replace the following assumptions with their own figures:

• Number of open transactions and manual commitments
• Weekly update frequency
• Current extraction, reconciliation, and reporting time
• Expected exception rate
• Loaded labour cost
• Software and storage cost
• Maintenance effort
• Implementation labour and specialist rates
• Value of faster collections or earlier liquidity action

Adding AI to the Automation

AI is optional and should be added only after source retrieval, validation, approvals, weekly calculations, history, and error handling work reliably.

Most of this cash forecast should remain deterministic. Required fields, transaction IDs, exact amounts, date calculations, approval thresholds, weekly buckets, cash formulas, and minimum-buffer comparisons do not require AI.

Potential AI uses include:

• Drafting a weekly management summary from aggregated forecast results
• Classifying unstructured customer payment emails for human review
• Extracting proposed payment dates and amounts from collection notes
• Summarizing actual-versus-forecast explanations
• Identifying recurring themes in forecast exceptions
• Suggesting categories for newly entered unstructured notes

The core automation creates reliable data movement, calculation, alerts, history, and reporting. AI would add value only when interpreting or drafting from unstructured information.

The Recommended AI Enhancement

The recommended enhancement is a draft weekly cash commentary generated from aggregated base-scenario totals and variance records. It does not receive customer names, vendor names, invoice text, bank account identifiers, payroll detail, tax identifiers, or employee information.

• Trigger: Manual action after a successful weekly refresh
• AI input: Aggregated weekly cash totals, shortfall flags, minimum buffer, and recent variance figures
• System instruction: Produce factual draft commentary using only supplied data
• Expected output: Structured JSON containing a summary, evidence-based risks, follow-up questions, and a confidence note
• Validation: JSON schema, required fields, maximum list sizes, and amount checks against supplied data
• Record update: Append a Pending Review row to AI_Summaries
• Human review: Controller verifies every amount and approves any text before distribution
• Low-confidence handling: State uncertainty and create a follow-up question rather than inventing an explanation
• Prohibited data: Personal data, bank details, credentials, tax identifiers, transaction notes, and confidential customer or vendor content
• Logging: Store model name, token usage, Run_ID, output, review status, and errors
• Failure behavior: Leave the standard rule-based report unchanged and draft commentary manually

The reusable system instruction is:

You are assisting a finance controller with a 13-week cash flow forecast. Use only the aggregated figures supplied by the user. Do not invent causes, commitments, dates, amounts, financing availability, or management decisions. Identify mathematical trends and explicit shortfalls, but treat every recommendation as a question or item for human review. Do not make payment, borrowing, purchasing, tax, legal, or accounting decisions. Return only JSON that matches the supplied schema.

The reusable user prompt is:

Prepare draft weekly cash forecast commentary from the supplied JSON.

Requirements:
1. State the opening cash, week-13 ending cash, minimum buffer, and first shortfall week when present.
2. Identify no more than three risks supported directly by the data.
3. Provide no more than five follow-up questions for the controller.
4. Do not infer why a variance occurred unless an explanation is supplied.
5. Do not mention customers, vendors, employees, bank accounts, or confidential transaction details.
6. Do not recommend executing payments, drawing financing, delaying taxes, or approving purchases.
7. State that the output requires controller review.

Forecast data:
{{AGGREGATED_FORECAST_JSON}}

The expected JSON structure is:

{
  "summary": "Draft factual summary requiring controller review.",
  "risks": [
    {
      "week_start": "2026-08-31",
      "issue": "Projected ending cash is below the configured buffer.",
      "evidence": "Ending cash is 137000 and the buffer is 150000.",
      "review_question": "Which approved liquidity or timing actions should management evaluate?"
    }
  ],
  "follow_ups": [
    "Confirm whether material low-confidence receipts have current expected dates."
  ],
  "confidence_note": "This draft uses aggregated forecast data and requires controller verification."
}

The following function can be added to the same Apps Script project. It relies on the workbook helpers in the core script and uses the OpenAI Responses API. The organization should approve the provider, model, contractual terms, data location, retention controls, and security configuration before use.

function draftAiWeeklySummary() {
  const runRows = readObjects_('Run_Log')
    .filter(function(row) {
      return String(row.Status) === 'Success';
    })
    .sort(function(a, b) {
      return asDate_(b.Finished_At) - asDate_(a.Finished_At);
    });

  if (!runRows.length) {
    throw new Error('No successful forecast run exists.');
  }

  const latestRunId = String(runRows[0].Run_ID);
  const weekly = readObjects_('Weekly_Forecast')
    .filter(function(row) {
      return (
        String(row.Forecast_Run_ID) === latestRunId &&
        String(row.Scenario) === 'Base'
      );
    })
    .sort(function(a, b) {
      return asDate_(a.Week_Start) - asDate_(b.Week_Start);
    });

  if (weekly.length !== 13) {
    throw new Error('Expected 13 base-scenario weekly rows.');
  }

  const variances = readObjects_('Variance_Log')
    .filter(function(row) {
      return String(row.Scenario) === 'Base';
    })
    .sort(function(a, b) {
      return asDate_(b.Recorded_At) - asDate_(a.Recorded_At);
    })
    .slice(0, 4)
    .map(function(row) {
      return {
        comparison_week_start: dateKey_(row.Comparison_Week_Start),
        prior_forecast_ending_cash: number_(
          row.Prior_Forecast_Ending_Cash,
          0
        ),
        actual_accounting_cash: number_(row.Actual_Accounting_Cash, 0),
        variance: number_(row.Variance, 0),
        explanation: String(row.Explanation || '')
      };
    });

  const safeInput = {
    run_id: latestRunId,
    currency: String(getConfig_().BASE_CURRENCY || 'USD'),
    weeks: weekly.map(function(row) {
      return {
        week_start: dateKey_(row.Week_Start),
        opening_cash: number_(row.Opening_Cash, 0),
        customer_receipts: number_(row.Customer_Receipts, 0),
        other_inflows: number_(row.Other_Inflows, 0),
        ap_payments: number_(row.AP_Payments, 0),
        payroll: number_(row.Payroll, 0),
        taxes: number_(row.Taxes, 0),
        recurring_expenses: number_(row.Recurring_Expenses, 0),
        planned_purchases: number_(row.Planned_Purchases, 0),
        other_outflows: number_(row.Other_Outflows, 0),
        net_cash_flow: number_(row.Net_Cash_Flow, 0),
        ending_cash: number_(row.Ending_Cash, 0),
        minimum_buffer: number_(row.Minimum_Buffer, 0),
        headroom: number_(row.Headroom, 0),
        shortfall: boolean_(row.Shortfall_Flag)
      };
    }),
    recent_variances: variances
  };

  const schema = {
    type: 'object',
    additionalProperties: false,
    required: ['summary', 'risks', 'follow_ups', 'confidence_note'],
    properties: {
      summary: {
        type: 'string',
        minLength: 1,
        maxLength: 1500
      },
      risks: {
        type: 'array',
        maxItems: 3,
        items: {
          type: 'object',
          additionalProperties: false,
          required: [
            'week_start',
            'issue',
            'evidence',
            'review_question'
          ],
          properties: {
            week_start: {type: 'string'},
            issue: {type: 'string'},
            evidence: {type: 'string'},
            review_question: {type: 'string'}
          }
        }
      },
      follow_ups: {
        type: 'array',
        maxItems: 5,
        items: {type: 'string'}
      },
      confidence_note: {
        type: 'string',
        minLength: 1,
        maxLength: 500
      }
    }
  };

  const instructions =
    'You are assisting a finance controller with a 13-week cash flow ' +
    'forecast. Use only the aggregated figures supplied by the user. ' +
    'Do not invent causes, commitments, dates, amounts, financing ' +
    'availability, or management decisions. Identify mathematical trends ' +
    'and explicit shortfalls, but treat every recommendation as a question ' +
    'or item for human review. Do not make payment, borrowing, purchasing, ' +
    'tax, legal, or accounting decisions. Return only JSON that matches ' +
    'the supplied schema.';

  const userPrompt =
    'Prepare draft weekly cash forecast commentary from the supplied JSON.\n' +
    'State the opening cash, week-13 ending cash, minimum buffer, and first ' +
    'shortfall week when present. Identify no more than three risks supported ' +
    'directly by the data. Provide no more than five follow-up questions. ' +
    'Do not infer why a variance occurred unless an explanation is supplied. ' +
    'The output requires controller review.\n\nForecast data:\n' +
    JSON.stringify(safeInput);

  const model = requireProperty_('AI_MODEL');
  const apiKey = requireProperty_('AI_API_KEY');

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

  try {
    const response = httpJson_(
      'https://api.openai.com/v1/responses',
      {
        method: 'post',
        contentType: 'application/json',
        headers: {
          Authorization: 'Bearer ' + apiKey
        },
        payload: JSON.stringify(requestBody),
        muteHttpExceptions: true
      },
      'AI summary request',
      2
    );

    const outputText = extractResponseOutputText_(response);
    const result = JSON.parse(outputText);
    validateAiSummary_(result);

    const usage = response.usage || {};
    const summaryId =
      'AI-' + Utilities.formatDate(
        new Date(),
        getTimeZone_(),
        'yyyyMMdd-HHmmss'
      );

    getSheet_('AI_Summaries').appendRow([
      summaryId,
      new Date(),
      latestRunId,
      model,
      result.summary,
      JSON.stringify(result.risks),
      JSON.stringify(result.follow_ups),
      number_(usage.input_tokens, 0),
      number_(usage.output_tokens, 0),
      'Pending Review',
      '',
      '',
      ''
    ]);

    return {
      summaryId: summaryId,
      status: 'Pending Review',
      result: result
    };
  } catch (error) {
    getSheet_('AI_Summaries').appendRow([
      'AI-ERROR-' + Date.now(),
      new Date(),
      latestRunId,
      model,
      '',
      '[]',
      '[]',
      0,
      0,
      'Failed',
      '',
      '',
      truncate_(error.message, 1000)
    ]);
    throw error;
  }
}

function extractResponseOutputText_(response) {
  if (!response || !Array.isArray(response.output)) {
    throw new Error('AI response does not contain an output array.');
  }

  for (let outputIndex = 0; outputIndex < response.output.length; outputIndex++) {
    const item = response.output[outputIndex];
    if (!item || !Array.isArray(item.content)) {
      continue;
    }

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

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

function validateAiSummary_(result) {
  if (
    !result ||
    typeof result.summary !== 'string' ||
    !Array.isArray(result.risks) ||
    !Array.isArray(result.follow_ups) ||
    typeof result.confidence_note !== 'string'
  ) {
    throw new Error('AI output failed structural validation.');
  }

  if (result.risks.length > 3 || result.follow_ups.length > 5) {
    throw new Error('AI output exceeded allowed item counts.');
  }

  result.risks.forEach(function(risk) {
    if (
      typeof risk.week_start !== 'string' ||
      typeof risk.issue !== 'string' ||
      typeof risk.evidence !== 'string' ||
      typeof risk.review_question !== 'string'
    ) {
      throw new Error('AI risk item failed validation.');
    }
  });
}

Run draftAiWeeklySummary manually after a successful test forecast. The resulting record remains Pending Review. It must not be distributed automatically. Likely errors include missing API properties, an unapproved model name, HTTP authorization failure, rate limiting, malformed output, or schema validation failure. The fallback is the standard rule-based report and a manually written controller summary.

Benefits of the AI Enhancement

The AI enhancement can reduce the time spent drafting repetitive commentary from already calculated figures. It can also apply a consistent structure to the weekly summary and surface explicit shortfall weeks or unusual variances for review.

These are AI-specific benefits:

• Less time converting structured weekly figures into narrative text
• More consistent commentary format
• Faster identification of figures requiring an explanation
• Draft follow-up questions based on supplied evidence

These benefits come from the core automation rather than AI:

• Retrieving accounting records
• Matching transaction overrides
• Calculating weekly cash balances
• Applying scenarios
• Detecting minimum-buffer breaches
• Preserving history
• Calculating variances
• Sending deterministic alerts

What Remains Rule-Based or Human-Controlled

Estimating the Additional Value of AI

The following estimate is illustrative:

Core automation without AI:
4 × 25 minutes = 100 minutes per month

Automation with AI:
Preparation: 4 × 2 = 8 minutes
Human review: 4 × 7 = 28 minutes
Expected corrections: 4 × 15% × 5 = 3 minutes
Expected failure fallback: 4 × 5% × 25 = 5 minutes

Total with AI:
8 + 28 + 3 + 5 = 44 minutes

Additional capacity:
100 - 44 = 56 minutes, or 0.93 hours

Estimated labour value:
0.93 × $55 = $51.15

Net value after $5 usage allowance:
$51.15 - $5.00 = $46.15 per month

The AI enhancement has a smaller financial effect than the core data integration. Its value is primarily consistent drafting and reduced reporting effort. It does not eliminate controller review or guarantee accurate explanations.

Testing Checklist

Use representative sample data before processing real financial information.

Ongoing Maintenance

The controller is the primary business owner. The accounting specialist is the backup operational owner. A named technical administrator maintains credentials, triggers, and integrations.

Credential rotation should follow the organization’s security policy and the connected provider’s supported process. OAuth refresh failures, application-secret changes, or revoked consent require a supervised reconnection.

Template updates should be tested in a copied workbook. Adding, renaming, or reordering generated columns without updating the script can break field mappings and Looker Studio data sources.

When to Move to Dedicated Software

The connected model does not need to be replaced merely because it uses a spreadsheet. It should be reviewed when its operating risks or requirements exceed the controls available in the current design.

Upgrade indicators include:

• Transaction volume causes Apps Script timeouts or slow spreadsheet performance
• Multiple legal entities or currencies require consolidation and conversion
• Bank availability must be retrieved independently from accounting balances
• Formal segregation of duties exceeds spreadsheet protection controls
• Regulatory or lender requirements demand stronger audit evidence
• Multiple locations need complex local ownership and approval routing
• Exception rates make weekly reconciliation excessive
• Many upstream and downstream integrations require managed support
• The business requires intraday cash positioning
• Scenario models require debt facilities, covenant calculations, or probabilistic forecasts
• Users require a secure customer, vendor, or business-unit portal
• Mobile or offline operating requirements become important
• Maintenance depends on one script developer
• Vendor response-time commitments become a formal requirement
• Advanced forecasting, treasury, consolidation, or planning features justify a specialized platform

Possible next steps include a treasury management system, cash forecasting platform, financial planning and analysis platform, database-backed application, or managed integration layer. The existing Google Sheets model can remain useful as a requirements prototype, validation model, or management reporting layer during a later migration.

Implementation Checklist

• Confirm business requirements, forecast horizon, week definition, and reporting audience.
• Assign the controller, accounting specialist, owner, backup approver, and technical administrator.
• Confirm QuickBooks Online, Google Workspace, Apps Script, Drive, and Looker Studio access.
• Create separate development and production workbooks.
• Configure company-controlled OAuth credentials and redirect URI.
• Define permission boundaries and protected ranges.
• Create source, override, input, scenario, detail, output, history, variance, run-log, and error tables.
• Define stable IDs and duplicate-prevention rules.
• Build AR, AP, bank balance, payroll, tax, recurring expense, purchase, and other cash inputs.
• Document exact field mappings between QuickBooks and Google Sheets.
• Configure collection-lag, minimum-buffer, currency, and scenario assumptions.
• Configure purchase approvals, threshold rules, rejection, deferral, and delegation.
• Configure reminders, escalations, success notices, shortfall alerts, and failure notices.
• Create the Drive folder structure, snapshot naming convention, permissions, retention, and archive process.
• Build Looker Studio executive, scenario, variance, and exception views.
• Store credentials in script properties and remove placeholders.
• Review and authorize Apps Script permissions.
• Test OAuth, token refresh, API pagination, validation, retries, and duplicate prevention.
• Reconcile imported balances and weekly output with accounting reports.
• Complete user acceptance testing with representative sample data.
• Run a parallel pilot before production activation.
• Install weekly, daily reminder, and approval edit triggers.
• Document rollback, manual recovery, and support ownership.
• Replace representative implementation cost assumptions with actual estimates.
• Replace time-saving assumptions with measured process data.
• Add the optional AI summary only after the core forecast is reliable.
• Require controller review of every AI-generated summary.
• Assign primary and backup maintenance owners.
• Define volume, security, audit, integration, and maintenance criteria for moving to dedicated software.