# Boardflare Notebook Reference: Overview This reference is the canonical guide for AI assistants and human developers working with Boardflare Python for Excel workbooks and the Boardflare Office add-in. ## What is Boardflare? Boardflare is an application-development platform for Microsoft Excel. It embeds a reactive Python environment powered by [Marimo](https://marimo.io) running in browser WebAssembly via [Pyodide](https://pyodide.org) directly inside Excel's shared runtime. With Boardflare, a workbook becomes an interactive application: - **Reactive Python Notebook**: Write Python cells that automatically recalculate when upstream inputs change. - **Worksheet Formulas**: Publish computed data to Excel via `=BF.OUTPUT("name")` and callable Python functions via `=BF.FUNCTION("name", arg1, ...)`. - **Table Selection & Reactive Views**: Read workbook inputs, track user selections in Excel tables, and compute dynamic derived views. - **Durable Workbook Storage**: Store the complete Marimo notebook source directly inside the `.xlsx` file (in the hidden `_BOARDFLARE` worksheet) so the application travels with the workbook without external file dependencies. ## Two Reference Layers: User Guide vs. Technical Authoring The Boardflare Notebook Reference maintains a strict split between two distinct layers of information depending on your task: ### 1. User Guide (Add-in Usage & End-User Support) Consult these sections when assisting end users with the add-in, answering questions about features, or troubleshooting Excel formula errors: - [02-addin.md](02-addin.md): Add-in installation, task pane navigation, Edit vs. App mode, saving, persistence indicators, and recovery. - [08-troubleshooting.md](08-troubleshooting.md): Common spreadsheet formula errors (`#BUSY!`, `#NAME?`, `#VALUE!`, `#SPILL!`), recovery options, and user FAQ. ### 2. Technical Reference & Authoring Specification Consult these sections when authoring, modifying, or programmatically inspecting Python for Excel workbooks and templates: - [03-workbook-editing.md](03-workbook-editing.md): Hidden code sheet layout (`_BOARDFLARE`), A1–H11 cell definitions, and the AI handshake protocol (request counter in `H1`, response JSON in `H2`). - [04-python-notebooks.md](04-python-notebooks.md): Marimo reactive execution rules, Anywidget communication, imports and dependencies, and Pyodide WebAssembly runtime constraints. - [05-boardflare-api.md](05-boardflare-api.md): Canonical API reference for the `boardflare` (`bf`) package. - [06-workbook-design.md](06-workbook-design.md): Input ranges, table selections, and published outputs and functions. - [07-verification.md](07-verification.md): Testing, verification protocol, error interpretation, and convergence checks. - [09-limits.md](09-limits.md): Authoritative system limits, timeouts, and payload constraints. - [10-changelog.md](10-changelog.md): Release history and API evolution. ## How AI Agents Should Use This Reference When assisting users with a Boardflare workbook or modifying notebook code: 1. **Identify the Task Layer**: If answering questions about using the add-in or spreadsheet formula states, refer to the **User Guide** sections. If authoring or modifying code in `_BOARDFLARE`, refer to the **Technical Authoring** sections. 2. **Respect the Boundaries**: Never invent APIs or package imports. Boardflare runs in a strict zero-network Pyodide environment with a fixed set of pre-bundled packages. 3. **Follow the Edit Loop**: When modifying workbook code via Office.js or file editing, follow the deterministic handshake (increment `H1`, read `H2`) described in [Workbook Editing](03-workbook-editing.md). 4. **Targeted Fixes**: Inspect errors returned in the response JSON (`H2`) before making changes. Limit automated fix attempts to two iterations. 5. **Verify Outputs**: Verify that formulas resolve properly and do not show `#BUSY!`, `#NAME?`, `#VALUE!`, or `#SPILL!`. --- # The Boardflare Add-in This section describes the installation, user interface, execution model, task pane, and lifecycle of the Boardflare Office Add-in in Microsoft Excel. ## Installing and Opening the Add-in The Boardflare add-in runs as an Office Web Add-in in Microsoft Excel desktop (Windows and macOS) and Excel on the web. ### Installation - **Microsoft AppSource / Office Store**: In Excel, go to **Home > Add-ins > More Add-ins**, search for **Boardflare**, and select **Add**. - **Admin Deployment**: Organizations can deploy Boardflare centrally through the Microsoft 365 Admin Center to users or groups. ### Opening the Add-in - Click the **Boardflare** button in the Excel ribbon (typically on the **Home** tab) to open the task pane. ### Task Pane Navigation The task pane provides three main tabs: 1. **Notebook Tab**: The primary interactive surface. Houses the reactive Marimo authoring interface (Edit mode) or the full-pane application view (App mode). 2. **Templates Tab**: A gallery of pre-built workbook templates published by Boardflare. Cards allow previewing templates and downloading them directly. Opening or switching to Templates does not disrupt background calculation. 3. **Support Tab**: Documentation links, access to the copyable `llms.txt` URL, diagnostic status, and legacy editor preferences. ## Operating Modes: Edit vs. App Mode - **Edit Mode**: Displays the standard Marimo authoring UI with code cells, cell controls, run controls, error messages, and package status. Used by developers and AI agents to author and modify notebook logic. - **App Mode**: Presents a clean, full-pane interactive application UI. Code cells are hidden, and only UI widgets (inputs, dropdowns, charts, sliders, markdown text) are displayed. The Boardflare status footer is collapsed, offering a native application experience for end users. ### Switching and Persisting the Mode - In Edit mode, the footer selector allows toggling **Open as: Edit** or **Open as: App**. - Selecting a mode stages the preference without remounting the active notebook; the footer displays **Save required**. Saving the notebook persists this preference to the workbook so that future sessions open in the selected mode. Choosing the already-saved mode again clears the staged change without requiring a save. - The selector is disabled until the notebook is ready, has been saved at least once, has no unsaved source changes, and has no save in progress. Save the notebook before changing the mode. - In App mode, users can click the small pencil icon (**Edit notebook**) to switch temporarily to Edit mode for the current session without altering the saved workbook preference. The edit session starts fresh from the saved source. - The embedded notebook offers no AI features. AI assistants edit the notebook through the code sheet, as described in [Workbook Editing](03-workbook-editing.md). ## Saving and Persistence Lifecycle Saving a Boardflare notebook involves two distinct stages: 1. **Marimo Serialization**: The Marimo kernel serializes all code cells and sends them to Boardflare's FileStore. 2. **Workbook Persistence**: Boardflare writes the source cells into the hidden `_BOARDFLARE` worksheet and verifies the stored content. ### What Triggers a Save - In Edit mode, Marimo autosaves an edited notebook about one second after the last edit, and the user can also save with Marimo's Save control or its keyboard shortcut. Marimo saves only a notebook it considers edited, so opening a notebook never writes to the workbook. - Autosave keeps the code sheet in step with the pane, so an AI assistant reading the sheet sees pane edits. A reload requested through the request counter (`H1`) waits for saves already in progress but not for an edit still inside the one-second autosave delay. - If the code sheet was changed outside the editor (for example by undo, another user, or an AI assistant) after the pane loaded it, the next save does not overwrite it. The pane shows **Notebook changed outside the editor** once, with the choice to reload the changed notebook (discarding the pane's unsaved edits) or to overwrite it with the pane's version. - Restarting the notebook, replacing the mode, or closing the pane saves what Marimo has already handed over; it cannot recover edits Marimo has not yet saved. ### Status Footer Indicators The footer at the bottom of the task pane reflects the authoritative persistence state. Marimo's own clean indicator only means the source was handed to Boardflare, so rely on the footer: - **Starting**: The runtime frontend is booting. - **Startup failed**: Runtime initialization encountered a fatal error. - **Not saved**: No durable notebook source has been persisted to this workbook yet. - **Saving…**: Source was received and is being written and verified in the workbook. - **Unsaved**: There are unsaved edits and autosave is off (for example `?autosave=off`), so they wait for a manual save. - **Saved**: The notebook was successfully written to the worksheet and verified. - **Save failed**: Persistence or verification failed. - **Save required**: An opening mode (Edit/App) change has been staged but not yet saved. - **Session saved**: A website demo notebook was saved in the page's memory only (see [Website demos](#website-demos-web-host)). The footer also holds a button to download the notebook as a Marimo `.py` file and, in Excel and Google Sheets, a button to upload one (see [Uploading and Importing Python Scripts](03-workbook-editing.md#uploading-and-importing-python-scripts)). App mode hides the footer. Always wait for **Saved** before closing the workbook or relying on persisted changes. ## Reset and Recovery A recovery strip appears when the notebook cannot load or start, or when a reset fails. Ordinary save failures stay in the footer and the error strip, and do not discard the current session. - **Retry** stops the failed session and starts another from the current saved source. A failed App startup retries in Edit mode so the source can be inspected. - **Reset saved notebook** clears the notebook from the workbook, verifies that, and opens the starter notebook in Edit mode. Marimo edits not saved with Save are lost, so an active Edit session asks for confirmation first. - **Restore saved notebook** appears instead after an uploaded file fails to load; it discards the upload and restores the last saved notebook. - Reset and Retry do not preserve live inputs, outputs, functions, or kernel state; running the notebook rebuilds them. ## Shared-Runtime Cold Start Excel custom functions (`=BF.OUTPUT(...)` and `=BF.FUNCTION(...)`) and the task pane share a single long-lived Office shared runtime (`taskpane.html`). ### Key Invariants - **Background Execution**: When a workbook opens or recalculates, Excel automatically initializes the shared runtime in the background to evaluate `BF.OUTPUT` and `BF.FUNCTION` formulas. The user **does not** need to click the ribbon icon or open the task pane. - **Calculation Without Visible UI**: The notebook runtime initializes Pyodide, restores the saved notebook from the worksheet, executes the cells, and registers published outputs behind the scenes. - **Formulas Wait for Ready State**: While the runtime initializes, formulas remain in Excel's native `#BUSY!` state. Once the notebook finishes evaluating and publishes its outputs, formulas resolve to their calculated values automatically. If the user later opens the task pane, it connects to the exact same running session. ## Website Demos (Web Host) Template pages on boardflare.com run notebooks in a browser-hosted spreadsheet with the same notebook surface as Excel. - **Session-only saves**: Save keeps the notebook in the page's memory and the footer shows **Session saved**. Reloading the page restores the notebook the template shipped with. - **Reset template notebook** restores the template's notebook and its configured opening mode. - **Opening mode**: A template opens in Edit or App mode as configured. - **Cell number formats**: A published date or time arrives as a serial number and shows with the destination cell's own number format, so format those cells as dates. Text results that parse as numbers, dates, percentages, or currency (such as `"2026-06-22"` or `"12%"`) are converted to numbers by the website spreadsheet, whereas Excel keeps them as text. - **`BF.FUNCTION`**: Calls run once per formula evaluation rather than as a streaming subscription. [`publication.consumers`](06-workbook-design.md#publishing) lists each formula cell that uses a published name. - **Error results**: A failing formula shows text that starts with `#ERROR:` instead of an Excel error value. An unpublished name gives `#ERROR: Unknown notebook output: ` or `#ERROR: Unknown notebook function: `, and a function or output that does not answer in time gives `#ERROR: Notebook function timed out` or `#ERROR: Notebook output timed out`. ## Sharing Workbooks - **Self-Contained in `.xlsx`**: The entire Marimo notebook source, dependency header, and opening mode preference are stored inside the hidden `_BOARDFLARE` worksheet. The application travels with the workbook without requiring external `.py` script files or cloud notebooks. - **Recipients with Boardflare**: Anyone opening the workbook with the Boardflare add-in installed can immediately run, interact with, and edit the notebook. - **Recipients without Boardflare**: If a recipient opens the workbook without Boardflare installed, Excel displays the last saved calculated cell values. If the sheet is recalculated, cells containing `=BF.OUTPUT(...)` or `=BF.FUNCTION(...)` will display `#NAME?` until the Boardflare add-in is enabled. ## Legacy Editor Compatibility The legacy Editor (`BOARDFLARE.EXEC`) is an individual-function Python editor. - Legacy Editor workbooks remain fully supported. The **Editor** tab appears automatically if the workbook contains legacy functions or if **Always show legacy editor** is enabled in Support preferences. - The legacy execution path is completely isolated from the Notebook runtime. - All new development should use the reactive Marimo Notebook environment. ## Google Sheets Boardflare is also available as an Editor add-on for Google Sheets, powered by the hosted web runtime and Apps Script container integration. ### Supported Capabilities - **Marimo Notebook Surface**: Authors and users have access to the standard reactive Marimo notebook environment directly inside the Google Sheets sidebar. - **Workbook Source Persistence**: Notebook source is saved within Google Sheets `DocumentProperties`, traveling with the document across copies and shares. - **Workbook Inputs (`bf.inputs`)**: Read data into Python using `bf.inputs()`. Supports active-sheet A1 addresses, sheet-qualified A1 addresses (e.g. `"Sheet1!A1:D100"`), and Google Sheets named ranges. - **Manual Refresh**: Active `bf.inputs()` bindings rehydrate via a manual **Refresh** button in the sidebar. - **Pre-Bundled Companion Packages**: All 15 pre-bundled pure-Python companion wheels (`seaborn`, `plotly`, `humanize`, `openpyxl`, `babel`, etc.) are available for immediate import without external network calls. - **Security Sandbox**: Notebook code runs in the same zero-network Pyodide WebAssembly child iframe under a strict `connect-src 'self'` Content Security Policy. ### Unsupported Capabilities Google Sheets does not support: - `bf.selection()` table selections. - `bf.publish()` and `=BF.OUTPUT()`. - `BF.FUNCTION()` or calling Python functions directly from Google Sheets worksheet formulas. - Automatic background polling or change triggers (updates require clicking the Refresh button). - Direct workbook range writes from Python. Unsupported features fail explicitly rather than silently altering semantics. --- # Editing a Notebook Workbook: Code Sheet & AI Handshake This section documents the internal structure of the hidden `_BOARDFLARE` worksheet and the protocol AI agents must use to inspect, modify, and verify notebook code. ## The Hidden Code Sheet (`_BOARDFLARE`) Boardflare stores the complete Marimo notebook directly within the Excel workbook on a VeryHidden worksheet named `_BOARDFLARE`. Storing the notebook in worksheet cells ensures that: - Any tool (Office.js in chat assistants, or external Python scripts using `openpyxl`) can inspect and update code. - Notebook code survives workbook operations that strip Custom XML parts. ### Sheet Layout | Range / Cell | Content | Description | | --- | --- | --- | | `A1` | Instructions | AI edit instructions. They point at the `llms.txt` and `llms-full.txt` of the notebook runtime the workbook executes from (production, or the preview or local runtime a development add-in uses). | | `A2` | (Blank) | Empty row separating instructions from notebook cells. | | `A3:A` | Notebook Cells | **Column A holds only real notebook cells**, one cell per row. Empty rows are skipped. Formatted as text (`@`). | | `G1 / H1` | Request | Request counter. Incremented by AI agents as the final step after modifying code. | | `G2 / H2` | Response | Execution response JSON written by the add-in after reloading the notebook. | | `G3 / H3` | Revision | Monotonically increasing workbook notebook revision counter. | | `G4 / H4` | Startup mode | Opening mode: `"edit"` or `"run"` (App mode). Formatted as text (`@`). | | `G5 / H5` | Format version | Layout version, value `"1"`. Formatted as text (`@`). | | `G6 / H6` | Saved at | ISO 8601 timestamp of last workbook save. Formatted as text (`@`). | | `G7 / H7` | Source SHA-256 | Lowercase hex SHA-256 digest of the canonical joined source. Formatted as text (`@`). | | `G8 / H8` | Engine | Always `"python"`. Formatted as text (`@`). | | `G9 / H9` | Notebook header | **The notebook header verbatim**: `import marimo` and `app = marimo.App(...)`. Formatted as text (`@`). | | `G10 / H10` | Template ID | Optional template identifier (e.g. `"clean-data"`). Formatted as text (`@`). | | `G11 / H11` | Template version | Optional template release version (e.g. `"rt-..."`). Formatted as text (`@`). | Ranges `A3:A1048576` and `H4:H11` are explicitly formatted as text (`@`) so code and metadata are never reinterpreted by Excel as numbers, formulas, or dates. ### Important Formatting Rules 1. **Column A holds only cells**: Every row from `A3` down must be a valid Marimo code unit (e.g. `@app.cell`, `@app.function`, or `with app.setup`). 2. **Header lives in H9**: Never place header code (`import marimo`, `# /// script`, `app = marimo.App(...)`) in column A. It belongs in `H9`. 3. **Closing boilerplate is never stored**: Never write `if __name__ == "__main__": app.run()`. The add-in automatically appends the run guard when reassembling the notebook. 4. **Cell character limits**: Excel cells store at most 32,767 UTF-16 characters. Keep each cell under 32,000 characters. If a cell is too large, split it into multiple Marimo cells. 5. **Setup cell**: `with app.setup` must appear in at most one cell. If present, it will be placed immediately after the header during reassembly. 6. **Do not modify metadata**: Do not edit rows G3:H8 or G10:H11, which the add-in maintains automatically. ### Hand-Edited Layouts Readers tolerate rows written by hand, and the next save rewrites the sheet in canonical order: - Blank lines or extra newlines around a row's code are ignored, so inserting a row can never glue two cells together. Empty rows are skipped. - A stray run guard row (`if __name__ == "__main__": app.run()`) is skipped. - A `with app.setup` row that is not first is moved to first. Other layouts are rejected with a `LayoutError` that names the row, for example `{"cell": "A7", "type": "LayoutError", ...}` in the response JSON (`H2`). The notebook is not loaded and the sheet is left as it is: a save over it is refused, and a reset still works. - A row that looks like the notebook header (`import marimo`, `__generated_with`, `# /// script`, `app = marimo.App`). - More than one `with app.setup` row. - Cells in column A while `H9` is empty. - A run guard inside `H9`. Storage rules: - A cell that exceeds the limit rejects the save, naming the cell and asking you to split it. Nothing is truncated. - Control characters that XML 1.0 cannot carry (such as `\x00` or lone surrogates) are rejected. - `H7` records the source's digest as last written by Boardflare, so Boardflare can tell the cells were edited outside it. A mismatch is not an error: the cells are authoritative. - A sheet whose format version in `H5` is anything other than `"1"` (an empty `H5` counts as `"1"`) is reported as `unsupported_schema` and never overwritten. - `A1` is rewritten whenever it differs, so an open workbook points at the runtime of the add-in that opens it. - The add-in creates the code sheet on the first save and repairs it on start. - Boardflare ignores its own writes to the sheet. In Excel, any other edit to `A1:H1048576` (an AI assistant, another user, undo) is noticed shortly afterwards. With no unsaved edits in the pane, the notebook reloads and the pane says so. With unsaved edits, the pane asks whether to reload from the workbook or keep editing. Saving over a changed sheet raises the [Notebook changed outside the editor](02-addin.md#what-triggers-a-save) choice. Incrementing the request counter in `H1` also reloads the notebook. ## The AI Edit Loop Handshake When an AI assistant (such as ChatGPT, Copilot, or Claude inside Excel) modifies a workbook, it communicates with the Boardflare add-in via the request counter (`H1`) and the response JSON (`H2`): ```text AI Agent Boardflare Add-in │ │ ├─ 1. Edit code rows in Column A / H9 │ ├─ 2. Increment H1 (e.g. 0 -> 1) │ │ ├─ 3. Detects request change │ ├─ 4. Sets H2 status="running" │ ├─ 5. Reloads and runs notebook │ ├─ 6. Writes final response JSON to H2 │◄─ 7. Polls H2 until status!="running" ──────────────┤ │ │ ▼ ▼ Verify status: "ok" or fix errors ``` ### Handshake Protocol Steps 1. **Diagnose First**: When a user reports an issue, do not blindly edit code. First, add 1 to the integer in `_BOARDFLARE!H1` and read `_BOARDFLARE!H2` to inspect any existing errors and traceback. 2. **Apply Edits**: - To update an existing cell: update the corresponding row in Column A (`A3:A`). - To add a new cell: append it to the next empty row in Column A. - To change notebook width or app settings: update the header text in cell `H9`. 3. **Signal Reload**: As your **last action**, increment the integer in `_BOARDFLARE!H1` by 1. 4. **Wait for Settlement**: - Read `_BOARDFLARE!H2`. Because Office.js environments may lack `setTimeout`, poll `H2` with short successive reads until `request` equals the integer in H1 and `status` is not `"running"`. Allow up to the AI edit settle timeout in [System Limits](09-limits.md). 5. **Evaluate Response**: - `status: "ok"`: The notebook compiled and ran all cells without uncaught Python exceptions. - `status: "errors"`: One or more cells failed. Inspect the `errors` array in the JSON response, address the issue, and repeat. Stop after at most two fix attempts. ### Response JSON Schema (cell H2) Cell `H2` contains a JSON string with the following fields: ```json { "request": 1, "status": "ok", "time": "2026-10-02T21:00:00.000Z", "errors": [ { "cell": "A5", "type": "ValueError", "message": "Unsupported driver: Units sold", "traceback": "Traceback (most recent call last):\n..." } ], "note": "All cells ran without errors." } ``` - `request` (integer): The request counter value (`H1`) this response answers. - `status` (`"running"` | `"ok"` | `"errors"`): Current execution state. - `time` (string): ISO 8601 timestamp. - `errors` (array): List of error objects containing: - `cell`: Cell address (e.g. `"A5"`, `"H9"`, or `"(notebook)"`). - `type`: Exception class (e.g. `"ValueError"`, `"LayoutError"`, `"FormatVersionError"`, `"StartupError"`). - `message`: Truncated error message (capped at 1,000 characters). - `traceback`: Truncated traceback (capped at 4,000 characters). - `note` (string): Summary message. #### What a Request Does Every request reloads the notebook from the code sheet, waits until the new session reports that nothing is running or queued (up to the AI edit settle timeout in [System Limits](09-limits.md)), and answers with the same request number. Cells Marimo cannot parse appear in `errors` as `SyntaxError` entries, because Marimo never runs them. A `ModuleNotFoundError` for a cell includes a pointer to the [package list](https://notebook.boardflare.com/python/packages.html). If the notebook is still running when the wait ends, `status` is `"errors"` and `note` says it did not finish in time. #### Response Truncation Caps To ensure the response JSON fits inside Excel's single-cell limit (32,767 characters): - Total response payload is capped at 32,000 characters. - If tracebacks exceed the limit, tracebacks are shortened to 300 characters each. - If the payload still exceeds 32,000 characters, trailing errors are omitted, and `note` indicates: `Tracebacks were shortened and X of Y errors were left out to fit the cell limit.` ## Editing via Office.js vs. OpenPyXL ### Office.js (Within Excel) - Read and write `_BOARDFLARE!A3:A` using standard `range.values`. - Ensure range formats are text (`@`) so code is not parsed as formulas. - Read `_BOARDFLARE!H1`, write back its integer plus 1, then poll `_BOARDFLARE!H2` until its `request` equals the new H1 value and `status` is not `"running"`. ### OpenPyXL (External File Modification) - Load the workbook with `openpyxl.load_workbook(filename)`. - Access the `_BOARDFLARE` worksheet. - Modify column A and/or cell `H9`. - Save the workbook. The next time the workbook is opened in Excel with Boardflare installed, the add-in automatically detects the changed source, updates its caches, and runs the notebook. - *Note*: OpenPyXL may discard non-standard Excel parts such as certain embedded controls or drawing artifacts. ## Uploading and Importing Python Scripts The add-in task pane allows uploading local `.py` Marimo notebook files: - **File extension**: Must be a `.py` file. - **Encoding**: Must be valid UTF-8 encoded text. - **Content**: Must be non-empty (cannot contain only whitespace). - **Size limit**: Total file size must not exceed the 200,000-byte workbook storage limit. --- # Python Notebooks: Marimo, Reactivity, and Runtime This section describes how Python code executes inside Boardflare workbooks, including the Marimo reactive model, interactive UI controls for App mode, imports and dependencies, and the Pyodide WebAssembly environment. ## The Marimo Reactive Model Boardflare uses [Marimo](https://marimo.io) as its notebook engine. Unlike traditional Jupyter notebooks, execution order is determined by a Directed Acyclic Graph (DAG) of variable references, not top-to-bottom cell order. ### Core Authoring Rules 1. **One Definition Per Variable**: A global variable name can be defined in **at most one cell**. If two cells define the same variable name (e.g. `x = 10` in Cell 1 and `x = 20` in Cell 2), Marimo halts both with a `MultipleDefinitionError`. To reuse intermediate variable names without conflicts, prefix them with an underscore (e.g. `_temp = ...`) or encapsulate logic in local functions. 2. **Reactivity**: When a cell changes its output or when an Anywidget traitlet updates (such as `inputs` receiving a new workbook snapshot), Marimo automatically recalculates all downstream cells that reference those variables. 3. **Keep Widgets Displayed**: The Anywidget models for `inputs = bf.inputs(...)` and `publication = bf.publish(...)` must be displayed as the return value of a cell (e.g. putting `inputs` on the final line of the cell). Displaying them maintains the live communications channel with the host. 4. **Cell Functions**: Functions that contain application logic or helper code can be decorated with `@app.function` or declared inside ordinary `@app.cell` functions. 5. **Replacement Models**: When an edit re-creates `bf.inputs(...)`, the new model becomes authoritative only after its initial workbook snapshot succeeds. If the snapshot is invalid, the previous working model stays active. 6. **Durable and Live State**: The notebook source is durable: it is saved with the workbook. Workbook input snapshots, published outputs, published functions, widget state, and in-flight function calls are live-session state. Running the notebook rebuilds them, and they do not survive a restart or reset. --- ## Interactive UI and App Mode Authoring In **App Mode**, Boardflare hides all Python code cells and displays only rendered UI controls, markdown text, charts, and application widgets in a clean, full-pane task-pane interface. ### Marimo UI Elements (`mo.ui`) Marimo provides native UI controls that bind directly to reactive variables. Import Marimo as `import marimo as mo`: ```python import marimo as mo # Declare interactive controls scenario_dropdown = mo.ui.dropdown( options=["Baseline", "Optimistic", "Stress Case"], value="Baseline", label="Scenario:", ) discount_slider = mo.ui.slider( start=0.0, stop=0.5, step=0.01, value=0.10, label="Discount Rate:", ) # Display controls together in the cell mo.hstack([scenario_dropdown, discount_slider]) ``` Downstream cells access the current value of the control via `.value`: ```python selected_scenario = scenario_dropdown.value discount_rate = discount_slider.value # Calculations update automatically when the user interacts with the control adjusted_revenue = base_revenue * (1 - discount_rate) ``` ### Common UI Components - **Inputs**: `mo.ui.text`, `mo.ui.number`, `mo.ui.slider`, `mo.ui.date`, `mo.ui.checkbox`, `mo.ui.switch`. - **Selectors**: `mo.ui.dropdown`, `mo.ui.radio`, `mo.ui.multiselect`. - **Tabular Data**: `mo.ui.table(df, selection="single" | "multi")`. - **Layout & Structure**: `mo.vstack([...])` (vertical column), `mo.hstack([...])` (horizontal row), `mo.accordion({...})`, `mo.tabs({...})`. - **Narrative & Formatting**: `mo.md("# Heading\nYour narrative here...")` formats rich Markdown text. - **KPI Metrics**: `mo.stat(value="$125,000", label="Total Revenue", caption="+12% YoY")`. ### Visualizations Charts created with `matplotlib`, `seaborn`, or `plotly` render directly in both Edit and App modes: - **Plotly**: Return the `figure` object as the final expression of a cell. - **Matplotlib / Seaborn**: Return the `fig` or `ax` object. Do **not** call `plt.show()`. --- ## Imports and Dependencies Notebooks have **no PEP 723 `# /// script` block**. Do not write one: the runtime injects its own block (pandas and the `boardflare` wheel) when it loads the notebook, and notebook headers start at `import marimo`: ```python import marimo app = marimo.App(width="medium") ``` Just `import` what you need. Imports resolve from the Pyodide lockfile, which includes the [pre-bundled companion packages](#pre-bundled-companion-packages), plus the Python standard library. Nothing is fetched from PyPI. Why no declarations: Marimo sends every declared dependency that is not already loaded to `micropip.install`, which fetches from PyPI and fails under the notebook's `connect-src 'self'` policy. A declaration adds nothing for packages the lockfile already provides, and only adds a failure path for packages it does not. --- ## The Pyodide WebAssembly Runtime Boardflare executes Python in the user's browser using Pyodide (Python compiled to WebAssembly). ### Security and Network Isolation - **Zero-Network Sandbox**: The notebook execution iframe runs under a strict Content Security Policy (`connect-src 'self'`). It has **no access to external internet addresses**, raw sockets, or local filesystem resources. - **No Runtime Pip Installation**: Dynamic package installation at runtime via pip or network fetching is disabled. Packages cannot be installed at runtime; imports resolve from the Pyodide lockfile, which includes the pre-bundled companion packages. ### Pre-bundled Companion Packages To provide rich analytical and data capabilities without external network access, the Boardflare runtime pre-bundles 15 pure-Python companion packages directly into the Pyodide image lockfile: | Package | Version | Primary Import Name(s) | Description | | --- | --- | --- | --- | | `babel` | 2.18.0 | `babel` | Internationalization and formatting utilities | | `chardet` | 7.6.0 | `chardet` | Universal character encoding detector | | `colorcet` | 3.2.1 | `colorcet` | Perceptually uniform colormaps | | `defusedxml` | 0.7.1 | `defusedxml` | XML bomb and entity expansion protection | | `et-xmlfile` | 2.0.0 | `et_xmlfile` | Low-memory XML generator for OpenPyXL | | `humanize` | 4.16.0 | `humanize` | Human-readable numbers, times, and file sizes | | `intervaltree` | 3.2.1 | `intervaltree` | Interval tree data structures | | `jmespath` | 1.1.0 | `jmespath` | Declarative JSON query language | | `openpyxl` | 3.0.9 | `openpyxl` | Excel spreadsheet reading and writing | | `plotly` | 7.1.0 | `plotly`, `_plotly_utils` | Interactive visualization library | | `python-slugify` | 9.0.0 | `slugify` | String slugification library | | `seaborn` | 0.13.2 | `seaborn` | Statistical data visualization | | `tabulate` | 0.10.0 | `tabulate` | Pretty-print tabular data | | `text-unidecode` | 1.3 | `text_unidecode` | Unicode to ASCII transliteration | | `textdistance` | 4.6.3 | `textdistance` | Distance and similarity algorithms between sequences | These packages can be imported immediately in any notebook without installation: ```python import humanize import plotly.express as px import seaborn as sns ``` ### Verified Package List - Human-readable package catalog: - Machine-readable JSON inventory: Only packages available in Pyodide or included on the companion list can be imported. Attempting to import an unavailable package raises `ModuleNotFoundError`. --- # The Boardflare Python API The `boardflare` (`bf`) package provides reactive workbook integration, table selection, and worksheet formula publishing for Marimo notebooks. All names are exported directly under `boardflare` (usually imported as `import boardflare as bf`). ## `bf.inputs` ```python bf.inputs(**named_specs: str | Reference | SelectionSpec) ``` Create a reactive workbook-input widget and return its Marimo wrapper. Declare workbook dependencies as keyword arguments. Keep the returned widget displayed in a cell, and read synchronized values by name in downstream cells (e.g. ``inputs["sales"]``). A notebook may declare at most one ``bf.inputs()`` widget. Args: **named_specs: Named inputs where each value is: - A string range reference (e.g. ``"A1:C10"`` or ``"Sheet!TaxRate"``). - A configured reference via ``bf.ref(reference, headers=True)``. - An active table selection via ``bf.selection(...)``, yielding a pandas DataFrame of the visible row at the active cell. Returns: A Marimo UI Anywidget. Access materialized inputs with dictionary subscript syntax: ``inputs[name]``. Do not use attribute access (e.g. ``inputs.name``). ## `bf.publish` ```python bf.publish(*, outputs: Mapping[str, Any] | None=None, functions: Mapping[str, Callable[..., Any]] | None=None) ``` Publish live output values and callable Python functions to worksheet formulas. Must be displayed in a cell so the Anywidget retains its connection to the host. Values are consumed in Excel via ``=BF.OUTPUT("name")`` and functions are invoked via ``=BF.FUNCTION("name", arg1, ...)``. Args: outputs: Mapping of output names to values (DataFrames, Series, lists, scalars). Scalars return in a single cell; DataFrames, Series, and 1D/2D arrays spill. functions: Mapping of function names to Python callables. Supported signatures include positional parameters with trailing defaults, optional ``*args``, keyword-only parameters that have defaults, and sync or async callables. Required keyword-only parameters and ``**kwargs`` are rejected. Returns: A marimo UI element. Read the live consumer formulas from ``publication.consumers``; ``publication.value`` raises ``RuntimeError``. ## `bf.ref` ```python bf.ref(reference: str, *, headers: bool=False) -> Reference ``` Configure a reactive workbook reference for ``bf.inputs``. Use a plain reference string when no options are needed. ``bf.ref(...)`` is useful when the binding needs options such as ``headers=True`` and can be passed to ``bf.inputs()``. Args: reference: Range address (e.g. ``"Sales!A1:D20"``) or defined name. headers: When True, treats the first row as column headers and materializes a rectangular range as a pandas DataFrame. When False (default), returns a scalar for one cell or a DataFrame with numbered columns for several. Cells whose number format is a date or date-time arrive as ``datetime.datetime`` (a date-only cell is midnight), in a DataFrame as ``datetime64[ns]`` columns, never as Excel serial numbers. Time-only cells arrive as ``datetime.time``. Cells with any other format arrive as numbers, so an unformatted date is a serial float. Returns: A Reference specification to pass to ``bf.inputs(...)``. ## `bf.selection` ```python bf.selection(table: str) -> SelectionSpec ``` Configure an active workbook table selection input for ``bf.inputs``. Args: table: Excel workbook table name. Returns: A SelectionSpec to pass to ``bf.inputs(...)``. When read through the inputs widget (e.g. ``inputs["selected"]``), returns a pandas DataFrame with the table's headers as columns and at most one row: the row of the user's active cell (the cell the cursor is on), even when a larger range is highlighted. The result is empty (an empty DataFrame with those columns) when the active cell is outside the table's body rows or columns, on another sheet, on the header or total row, or on a hidden or filtered row. Filtering that hides the active row does not refresh the result until the next selection change. If the table does not exist in the workbook, it produces an input error (not an empty DataFrame). ## Returned Object Interfaces and Methods In addition to top-level module functions, Boardflare Anywidgets and return objects provide the following interfaces: ### Inputs Widget (returned by `bf.inputs(...)`) - Display the returned widget as the cell's last expression so its communications channel with the workbook host remains connected. - Access synchronized input values with dictionary subscript syntax: `inputs["name"]`. Attribute access (e.g. `inputs.name`) is not supported. - At most one `bf.inputs(...)` widget is allowed per notebook. Calling `bf.inputs(...)` a second time raises `RuntimeError("Only one bf.inputs(...) is allowed per notebook; use one bf.inputs(...) with all inputs")`. - A bare table name includes the header row, so use `bf.ref("TableName", headers=True)` for named columns. - Reading a selection input (configured via `bf.selection("TableName")`) returns a pandas `DataFrame` with the table's headers as column names and at most one row: the row of the user's active cell (the cell the cursor is on), even when a larger range is highlighted. It is empty (an empty `DataFrame` with those columns, never `None`) when the active cell is outside the table's body rows or columns, on another sheet, on the header or total row, or on a hidden or filtered row. The cursor must be on a table cell; cells beside the table select no row. If the table does not exist in the workbook, it produces an input error (not an empty DataFrame). - Selection rows carry values, not positions (use column values like `picked["id"]`, not index alignment). Filtering that hides the active row does not refresh the result until the next selection change. ### `Publication` Object (returned by `bf.publish(...)`) - Display the publication element as the cell's last expression so its connection to the workbook host remains connected. - `publication.consumers`: Inspect active worksheet formula consumers for published outputs (`=BF.OUTPUT("name")`) and functions (`=BF.FUNCTION("name", ...)`). - `publication.value`: Raises `RuntimeError`. For complete usage patterns and code examples, see [Workbook Design](06-workbook-design.md). --- # Workbook Design: Inputs, Selections, and Outputs This section details how to structure Excel workbooks for Python integration: reading workbook ranges, tracking table selections, and publishing outputs and functions. ## Core Marimo Rules When writing Python in Boardflare, two Marimo rules apply: 1. **Unique top-level names**: Every global variable name can be defined in at most one cell. Defining the same variable name across multiple cells raises a `MultipleDefinitionError`. Use local functions or prefix private names with an underscore (`_temp = ...`) to prevent name conflicts. 2. **Widgets as last expression**: A UI widget (such as `inputs = bf.inputs(...)` or `publication = bf.publish(...)`) must be the last expression in a cell to render and maintain its live communication channel with the workbook host. ## 1. Reading Workbook Inputs (`bf.inputs`) Declare workbook dependencies with `bf.inputs(...)`: ```python inputs = bf.inputs( tax_rate="Assumptions!B2", orders=bf.ref("Orders", headers=True), picked=bf.selection("Orders"), ) inputs ``` - **At most one `bf.inputs` per notebook**: A notebook may declare at most one `bf.inputs(...)` widget. Calling `bf.inputs(...)` a second time raises `RuntimeError: Only one bf.inputs(...) is allowed per notebook; use one bf.inputs(...) with all inputs`. Consolidate all workbook inputs into a single call. - **Dictionary lookup**: Access materialized values with `inputs["name"]`, never `inputs.name`. Attribute access conflicts with widget properties and methods. - **Reference strings and `bf.ref`**: A plain string such as `"Sheet1!A1:C10"` or `"B2"` is equivalent to `bf.ref("B2")`. - **Bare table names and headers**: In Excel, a bare table name such as `"Orders"` resolves to the entire table including its header row. Use `bf.ref("Orders", headers=True)` so column names are taken from the first row and data rows form a typed pandas DataFrame. - **Date and time types**: Cells formatted as a date or date-time arrive as `datetime.datetime` (a date-only cell is midnight), and in a DataFrame (`bf.ref(..., headers=True)` or `bf.selection`) as a `datetime64[ns]` column of `pd.Timestamp`. Time-only cells arrive as `datetime.time`. Excel serial numbers are never delivered for formatted cells; a cell with a non-date number format stays a plain number. Serials appear only in template scenario `set` and `expect` values, which are written to the workbook as raw cell values. Compare dates as datetimes, for example `orders["due"] < pd.Timestamp.today().normalize()`. - **Reactivity**: When referenced cells change in Excel, the host sends a new snapshot and Marimo automatically recalculates all dependent cells. ## 2. Table Selection (`bf.selection`) Track the user's active row in an Excel table using `bf.selection("TableName")`: - **Single table per selection**: `bf.selection` takes one table name. - **DataFrame value**: Reading a selection input (e.g. `inputs["picked"]`) returns a pandas DataFrame with the table's headers as column names and at most one row: the row of the user's **active cell**, the cell the cursor is on, even when a larger range is highlighted. Moving the cursor within that range changes the row. The row is a full record, not just an ID. - **Empty selection**: The value is an empty DataFrame with those columns, never `None`, when the active cell is outside the table's body rows or columns, on another sheet, on the table's header or total row, or on a hidden or filtered row. Handle it with a labelled default: show a fallback such as the first record, and say in the pane that the user should select a row to change it. - **Cursor must be on a table cell**: Cells beside the table select no row, so a value in an attention list or published output next to the table does not drive the pane. Select a cell inside the table's columns. - **Transient**: The value is the user's current grid selection, not a remembered one. It empties when the user clicks any cell outside the table or leaves the table's sheet, and a template should assume nothing is selected when it opens. - **Values, not positions**: The DataFrame index is not aligned with the source table. Always filter or match using column values (such as `picked["id"]`), not row indices, so changes to the table cannot target the wrong row. - **Hidden rows**: If the active row is hidden by AutoFilter or manually hidden, the value is empty. - **Filtering refresh**: Filtering that hides the active row does not refresh the value until the user makes another selection. Do not assume the pane reflects the filter immediately. - **Missing table**: If the table does not exist in the workbook, it produces an input error (not an empty DataFrame). ### Selection drives the pane, not the cells Use a selection to decide what the notebook (task pane) shows, and do not publish selection-dependent values to cells. The selection is transient and cell contents persist, so a published value that follows the selection changes or vanishes whenever the user clicks elsewhere. Anything the user means to keep (an invoice, a mail merge) should be driven by a data column or an explicit action instead. Patterns that fit: - **Record detail**: select a row and see it joined with related tables in the pane. - **Lookup by key**: take the active row's key and compute a value from another table, such as the open orders for the selected customer, shown in the pane. ### Nothing selected Always handle the empty selection with a deliberate, labelled default, such as the first record (or all rows, or the top N), plus a line telling the user how to change it: ```python if picked.empty: shown = orders.head(1) note = mo.md("Showing the first order. Select a row in the Orders table to see its details.") else: shown = picked # the one row at the active cell note = mo.md("Showing the selected order.") # Record detail: join the shown rows to a related table (match on values) detail = shown.merge(customers, left_on="customer_id", right_on="id", suffixes=("", "_customer")) mo.vstack([note, mo.ui.table(detail, selection=None)]) ``` ### Pane-first layout A pane-first template has one selectable table on its sheet and everything else in the pane: - **No input or settings cells outside the table.** Do not put as-of dates, thresholds or other settings in cells beside or above the table. They crowd the first screen and are easy to overwrite. - **Settings are constants in the notebook's rules section**, for example `LOW_STOCK = 10`. For a date that should follow the clock, use a one-liner: `TODAY = pd.Timestamp.today().normalize()`. ### Pane recipe Build the pane as one `mo.vstack` in the last expression of a cell: - **Table options**: for a read-only display table use `mo.ui.table(df.reset_index(drop=True), selection=None, show_data_types=False, show_column_summaries=False, show_download=False)`. Each option is a `mo.ui.table` parameter in marimo 0.23.15. `selection=None` removes the row checkboxes, and the others remove the type row, the summary charts and the download button. - **Escape `$` in `mo.md`**: a pair of `$` characters renders as LaTeX. Write `\$` for a literal dollar sign, for example `mo.md(f"Total: \\${total:,.2f}")` (the backslash is doubled inside a normal string, or use a raw string). - **Re-indexing NaN trap**: after `reset_index(drop=True)` or any filter, a Series from another frame aligns on index labels, so `shown["x"] = other["x"]` fills `NaN` where labels differ. Assign values, not Series: `shown["x"] = other["x"].to_numpy()`, or use `merge` on a key column. ```python if picked.empty: shown, note = orders.head(1), "Showing the first order. Select a row to change it." else: shown, note = picked, "Showing the selected order." view = shown[["id", "customer_id", "due", "total"]].reset_index(drop=True) mo.vstack([ mo.md(note), mo.md(f"**Total:** \\${view['total'].sum():,.2f}"), mo.ui.table(view, selection=None, show_data_types=False, show_column_summaries=False, show_download=False), ]) ``` ## 3. Publishing Outputs and Functions (`bf.publish`) Publish calculated values and Python functions to Excel formulas using `bf.publish(...)`: ```python # Fixed 10-row block: pad short results with "" so the spill covers the same 10 rows summary = summary_df.head(10).reset_index(drop=True).reindex(range(10), fill_value="") bf.publish( outputs={"summary": summary}, functions={"discount": calculate_discount}, ) ``` - **Worksheet outputs**: Consumed in Excel cells via `=BF.OUTPUT("summary")`. Scalars return in a single cell; DataFrames, Series, and 2D arrays spill dynamically into adjacent cells. - **Spill area**: A DataFrame, Series, or 2D array spills into the cells below and to the right of its anchor. Those cells must stay clear, or Excel shows `#SPILL!`. To keep that area fixed, pad short results with `""` up to the rows and columns you leave clear. - **Worksheet functions**: Invoked in Excel formulas via `=BF.FUNCTION("discount", A1, B1)`. Functions accept positional arguments with optional trailing defaults, optional `*args`, and keyword-only arguments that have defaults. - **Formula consumers**: Inspect `publication.consumers` to see active worksheet formulas subscribed to each published output and function. Accessing `publication.value` raises `RuntimeError`. ## 4. Complete Example Notebook The following complete Marimo notebook reads `Orders` and `Customers` tables, shows the selected order joined to its customer in the pane (falling back to the first order when nothing is selected), and publishes selection-independent results to Excel formulas: ```python import marimo __generated_with = "0.23.15" app = marimo.App() @app.cell def _(): import boardflare as bf import marimo as mo return bf, mo @app.cell def _(bf): # At most one bf.inputs per notebook; widget must be the cell's last expression inputs = bf.inputs( orders=bf.ref("Orders", headers=True), customers=bf.ref("Customers", headers=True), picked=bf.selection("Orders"), ) inputs return (inputs,) @app.cell def _(inputs, mo): orders = inputs["orders"] customers = inputs["customers"] picked = inputs["picked"] # Selection drives the pane only; no active row gets a labelled default if picked.empty: shown = orders.head(1) note = mo.md("Showing the first order. Select a row in the Orders table to see its details.") else: shown = picked note = mo.md("Showing the selected order.") # Record detail: join the selected row to the related table by value detail = shown.merge(customers, left_on="customer_id", right_on="id", suffixes=("", "_customer")) mo.vstack([note, mo.ui.table(detail, selection=None)]) return (orders,) @app.cell def _(bf, orders): # Published values come from the whole table, so they do not depend on the selection total_revenue = float(orders["amount"].sum()) def get_order_revenue(order_id: int) -> float: matches = orders[orders["id"] == order_id] return float(matches["amount"].iloc[0]) if not matches.empty else 0.0 # Publish output for =BF.OUTPUT("total_revenue") and function for =BF.FUNCTION("order_revenue", A2) publication = bf.publish( outputs={"total_revenue": total_revenue}, functions={"order_revenue": get_order_revenue}, ) publication return (publication,) if __name__ == "__main__": app.run() ``` --- # Testing and Verification This section defines how AI assistants and developers verify changes to Boardflare notebooks and ensure calculations and published outputs remain robust. ## 1. What `status: "ok"` Means (and Doesn't Mean) When modifying a notebook via the code sheet (`_BOARDFLARE`), incrementing the request counter in `H1` causes the add-in to reload and evaluate the notebook. - **What `ok` means**: All Marimo cells were parsed, compiled, and executed without raising uncaught Python exceptions. - **What `ok` does NOT mean**: - It does not mean the analytical logic or business calculations are correct. - It does not mean worksheet outputs are populated (e.g. if `bf.publish()` was not called or omitted an output name). - It does not mean downstream worksheet formulas (`BF.OUTPUT`, `BF.FUNCTION`) evaluated without errors. Always inspect both the `H2` response status and the actual worksheet cells after applying an edit. --- ## 2. Inspecting Worksheet Formula Errors When checking cells in Excel containing `=BF.OUTPUT(...)` or `=BF.FUNCTION(...)`, look for the following indicator values: | Cell Display | Cause | Resolution | | --- | --- | --- | | `#BUSY!` | The shared runtime is booting or recalculating. | Normal during cold start. Interactive task pane startup times out after 90 seconds; when worksheet `BF.OUTPUT` or `BF.FUNCTION` formulas wait on the runtime, the cold-start deadline is five minutes. | | `#NAME?` | The formula name is unrecognized. | Ensure the Boardflare add-in is running and formulas are spelled `=BF.OUTPUT` or `=BF.FUNCTION`. | | `#VALUE!` | An invalid argument was passed to a function, a timezone-aware datetime was returned, or an empty sequence `[]` was published. An unknown notebook output or function name also gives `#VALUE!` (see [troubleshooting item 20](08-troubleshooting.md#20-unknown-notebook-output-or-function-value-in-excel)). | Check argument types and ensure returned datetimes/times are timezone-naive. | | `#NUM!` | The calculation produced `NaN`, `inf`, or `pd.NaT`. | Replace missing or invalid numeric values with valid numbers or `""`. | | `#N/A` | The calculation returned `pd.NA` or a complex number, ragged 2D arrays were padded, a published function exceeded the 60-second execution deadline, or the notebook is stopped. | Verify array rectangularity and DataFrame values. For a timeout, optimize function logic and move expensive computations into reactive notebook cells. For a stopped notebook, restart it. | | `#SPILL!` | The dynamic array output cannot expand because adjacent cells contain data. | Clear all non-empty cells in the spill range below and to the right of the anchor cell. | | `#CALC!` | Excel encountered a calculation engine error with dynamic arrays. | Ensure the published data format is supported (scalars, 1D/2D arrays, Series, DataFrames). | | `0` (or `"None"`) | The notebook published a Python `None` value. | In Excel, `None` displays as `"None"`. Use empty strings `""` for intentional blank cells. | --- ## 3. Verifying Formula Consumers To confirm that worksheet formulas are successfully connected to notebook outputs: - Inspect `publication.consumers["outputs"]` and `publication.consumers["functions"]`. - The consumers map lists active worksheet cells and ranges consuming each published name. - If a name is missing from `consumers`, no worksheet formula has requested it yet, or the formula contains a typo. --- ## 4. Scenario Testing (Deterministic Verification) The gold standard for validating a Boardflare workbook is **scenario testing**: 1. **Assert Baseline**: Confirm that when the workbook opens with its default inputs, output cells evaluate to expected baseline values. 2. **Apply Input Edits**: Modify input cells in the worksheet (e.g. changing an interest rate or driver assumption). 3. **Verify Convergence**: Confirm that the dependent output cells recalculate and match the expected new values. 4. **Assert Changed State**: At least one output value must differ from the baseline state to prove that the reactive notebook executed. Example testing pattern: - **Baseline**: Set `Assumptions!B2` to `0.08`; assert `Dashboard!D4` evaluates to `125,000`. - **Stress Case**: Change `Assumptions!B2` to `0.05`; assert `Dashboard!D4` converges to `95,000`. --- ## 5. AI Agent Verification Protocol When an AI assistant updates a notebook on behalf of a user: 1. **Step 1: Diagnostic Read** - Check `H1` (request counter) and `H2` (response JSON). - If `status === "errors"`, read the traceback to diagnose the existing failure. 2. **Step 2: Apply Targeted Edit** - Modify only the required cell rows in column A of `_BOARDFLARE` (or the header text in cell `H9`, to change notebook width or app settings). - Increment `H1` by 1. 3. **Step 3: Await Settlement** - Poll `H2` until `request === H1` and `status !== "running"`. 4. **Step 4: Check Response & Cell Values** - If `status === "errors"`, evaluate the traceback. - If `status === "ok"`, inspect the destination cells in the visible worksheet to verify numbers, dates, and tables populated cleanly. 5. **Step 5: Two Fix Attempts Rule** - If an error occurs, attempt at most **two automated fixes**. - If the issue persists after two attempts, stop and ask the user for clarification, presenting the error message and current status. --- # Troubleshooting and FAQ This section catalogs common errors encountered when developing and running Boardflare notebooks, their root causes, and standard resolutions. ## Common Errors & Resolutions ### 1. `MultipleDefinitionError` - **Cause**: Two or more Marimo cells define the same variable name at the module scope (e.g. `df = ...` in Cell 1 and `df = ...` in Cell 2). - **Resolution**: Variable names must be globally unique across all cells. Prefix intermediate variables with an underscore (e.g. `_df`), encapsulate them within local functions, or merge related logic into a single cell. ### 2. `ModuleNotFoundError: No module named '...'` - **Cause**: Attempting to import a package that is not available in the Pyodide WebAssembly runtime or pre-bundled companion list. - **Resolution**: When this happens in a notebook cell, the error in the response JSON (`H2`) includes a pointer to the package list. Dynamic installation (such as `pip install`) is not supported. Check the [pre-bundled package list](https://notebook.boardflare.com/python/packages.html) and import only packages on it; do not add a PEP 723 header. If a package is not on the list, implement the logic using standard libraries or supported alternatives (e.g. `scipy`, `numpy`, `pandas`). ### 3. `LayoutError: Row A... looks like the notebook header` - **Cause**: Header code (`import marimo`, `# /// script`, `app = marimo.App(...)`) was placed in Column A of `_BOARDFLARE`. - **Resolution**: Column A holds only real Marimo cells (starting with `@app.cell`, `@app.function`, etc.). All preamble and setup code belongs verbatim in cell `H9`. ### 4. Downstream cell waits for the workbook's first input snapshot - **Cause**: A downstream cell attempted to read `inputs["name"]` before the initial workbook data arrived. - **Resolution**: Ensure the cell containing `inputs = bf.inputs(...)` is displayed. In reactive cells, reading a required input before the first snapshot stops the current cell and its descendants, so no placeholder value is ever published, and Marimo reruns them once the snapshot arrives. ### 5. `RuntimeError: Only one bf.inputs(...) is allowed per notebook; use one bf.inputs(...) with all inputs` - **Cause**: A notebook declared more than one `bf.inputs(...)` widget across cells. - **Resolution**: Consolidate all workbook inputs (ranges, named references, and table selections) into a single `bf.inputs(...)` call in one cell. ### 6. Table input includes header row as data - **Cause**: A bare table name (e.g. `orders="Orders"`) was passed to `bf.inputs()`. In Excel, bare table names include the header row. - **Resolution**: Use `bf.ref("Orders", headers=True)` so column names are taken from the first row and excluded from the data rows. ### 7. `TypeError: Boardflare function ... cannot require keyword-only arguments` - **Cause**: A Python function published via `bf.publish(functions={...})` declared a required keyword-only argument or `**kwargs`. - **Resolution**: Published worksheet functions take positional parameters with optional trailing defaults, optional `*args`, and keyword-only parameters that have defaults. Replace required keyword-only parameters with positional parameters and remove `**kwargs`. ### 8. `ExcelResultConversionError: Boardflare published value is a timezone-aware datetime; Excel stores wall-clock values, ...` - **Cause**: Publishing a timezone-aware datetime or time to Excel via `=BF.OUTPUT` or `=BF.FUNCTION`. - **Resolution**: Excel serial numbers carry no time zone. Convert first: `value.astimezone(zone).replace(tzinfo=None)`. Datetime values read from `bf.ref` are already naive. ### 9. `ExcelResultConversionError: Python set results are intentionally unsupported...` - **Cause**: Returning a Python `set` as an output or function result. - **Resolution**: Python set iteration is non-deterministic. Convert the set to a sorted list before publishing: `sorted(my_set)`. ### 10. Formula displays `#NAME?` in Excel - **Cause**: Excel does not recognize `=BF.OUTPUT(...)` or `=BF.FUNCTION(...)`. - **Resolution**: Confirm the Boardflare add-in is installed and enabled for the workbook. If the add-in is running, check for typos in the formula name. ### 11. Formula displays `#BUSY!` indefinitely - **Cause**: The shared runtime encountered a startup timeout or a synchronous Python function blocked the event loop. - **Resolution**: Check the response JSON in `H2` for startup errors. Ensure published functions are lightweight and do not contain blocking operations or infinite loops. ### 12. Formula displays `#SPILL!` in Excel - **Cause**: The published output (matrix, Series, or DataFrame) cannot expand because existing cells, formulas, or formatting block the spill range. - **Resolution**: Clear all non-empty cells below and to the right of the anchor cell containing `=BF.OUTPUT(...)`. ### 13. `None` displays as `"None"` in Excel or `0` in Web Demos - **Cause**: The notebook returned `None` for a cell. - **Resolution**: In Excel, Python `None` formats as the text `"None"` (via a custom number format `0;-0;"None"`). In website demos, it coerces to `0`. If you want a visually empty cell, pad the spill with the empty string `""` instead of `None`. ### 14. Uploaded notebook fails to start or parse - **Cause**: An uploaded `.py` script breaks the [upload rules](03-workbook-editing.md#uploading-and-importing-python-scripts), contains syntax errors, uses unsupported packages, or fails during startup execution. - **Resolution**: Use **Restore saved notebook** in the recovery strip to discard the upload (see [Reset and Recovery](02-addin.md#reset-and-recovery)). ### 15. "Notebook initialization timed out" - **Cause**: The notebook frontend did not report ready within the Notebook Startup Timeout in [System Limits](09-limits.md). - **Resolution**: Click **Retry**. Use **Reset saved notebook** only when the saved source should be discarded. See [Reset and Recovery](02-addin.md#reset-and-recovery). ### 16. "Notebook failed to start" - **Cause**: The notebook runtime or the saved source could not be loaded. - **Resolution**: The recovery strip offers **Retry** and **Reset saved notebook**. Check the response JSON in `H2` for a `StartupError`. ### 17. Footer shows **Save failed** or **Not saved** - **Cause**: See the [footer indicators](02-addin.md#status-footer-indicators); the error strip gives the reason. - **Resolution**: Fix the cause shown in the error strip (for example a cell over the [32,767-character limit](09-limits.md)) and save again. Save the notebook before expecting it to persist. ### 18. Inputs do not update - **Cause**: The `bf.inputs(...)` widget is not displayed, a reference does not resolve, or the host lacks automatic workbook-change notifications (Google Sheets needs the manual **Refresh** button). - **Resolution**: Display the widget, correct the references, and inspect `inputs.errors` for invalid, oversized, or unavailable references. ### 19. Formula reports that the notebook is not running (`#N/A` in Excel) - **Cause**: The notebook session stopped or failed. Outputs and functions exist only while the session runs. - **Resolution**: Retry or restart the session. In Excel the task pane does not have to be visible for formulas to start the notebook; open the Notebook tab to inspect or recover it. ### 20. Unknown notebook output or function (`#VALUE!` in Excel) - **Cause**: The name is not in the `outputs={...}` or `functions={...}` mapping of the currently displayed `bf.publish()` cell, differs in case, or the publication has not been published yet. - **Resolution**: Match the exact, case-sensitive name and check `publication.consumers`. ### 21. A function stays busy - **Cause**: A synchronous function is long-running or blocked. Cancellation and timeout keep a stale result from reaching the worksheet but cannot interrupt Python code that is already running. - **Resolution**: Make the function short, or use `async def` for long work. ### 22. Changes disappeared after a restart - **Cause**: The edits were not saved before the editor restarted, or they were made in a website demo (see [Website Demos](02-addin.md#website-demos-web-host)). - **Resolution**: Wait for **Saved** in the footer before closing the pane or restarting. --- ## Anti-Patterns to Avoid 1. **Do not use `plt.show()`**: In Marimo, Matplotlib figures are displayed by making the `Figure` or `Axes` object the cell's final expression. Using `plt.show()` does nothing in the browser runtime. 2. **Do not access inputs with attribute syntax**: Always use `inputs["name"]`, not `inputs.name`. Attribute access conflicts with internal Anywidget methods and properties. 3. **Do not declare multiple `bf.inputs` widgets**: Exactly one `bf.inputs(...)` call is permitted per notebook. Consolidate all workbook range references and table selections into that single call. 4. **Do not execute blocking operations inside `=BF.FUNCTION`**: Worksheet functions share a single Python thread in the browser. Avoid long synchronous loops, heavy recalculations, or blocking delays in functions; move intensive modeling into reactive notebook cells and publish lightweight lookup functions or completed results. 5. **Do not attempt network requests**: The notebook runtime executes in a zero-network Pyodide WebAssembly sandbox. Calls to `urllib`, `requests`, or raw sockets will fail. 6. **Do not place static content in dynamic spill ranges**: Keep dashboard layout areas separate from output spill paths so future row/column expansions never trigger `#SPILL!`. --- # System Limits and Constraints This section catalogs the system boundaries, size limits, and concurrency caps enforced across the Boardflare runtime, Office add-in, and workbook serialization layers. ## Workbook & Storage Limits | Resource | Limit | Boundary | Description | | --- | --- | --- | --- | | **Cell Character Limit** | 32,767 UTF-16 code units | Excel OOXML | Maximum size of an individual notebook cell stored in `_BOARDFLARE`. Code cells exceeding this must be split. | | **Notebook Source Size** | 200,000 bytes | Boardflare Storage | Maximum total canonical source size of a notebook in a workbook. Uploaded `.py` scripts must also meet the [upload rules](03-workbook-editing.md#uploading-and-importing-python-scripts); the source must not exceed this limit. | | **Public Identifier Names** | 128 ASCII chars | Runtime Protocol | Maximum length of input, output, and function names. | | **Reference String Length** | 512 chars | Runtime Protocol | Maximum length of an A1 or defined-name reference passed to `bf.ref()` or `bf.inputs()`. | | **Safe Integers** | ±(2^53 − 1) | JavaScript / Protocol | Range `[-9,007,199,254,740,991, 9,007,199,254,740,991]`. Larger integer identifiers must be stored as strings. | | **AI Edit Settle Timeout** | 120,000 ms (2 min) | Add-in Host | Maximum time allowed for notebook reload to settle after incrementing the request counter in `H1`. | | **AI Response Size** | 32,000 chars | Add-in Host | Truncation boundary for the JSON payload written to the response JSON in `H2`. | | **Capability Message Size** | 1 MiB | Runtime Protocol | Maximum encoded JSON size of a general message between the notebook and the workbook host. | | **Notebook Startup Timeout** | 90 s (5 min when worksheet formulas use the notebook) | Add-in Host | Time the notebook frontend has to report ready before startup fails with "Notebook initialization timed out". | | **Capability Connection Timeout** | 10 s | Runtime Protocol | Time a displayed `bf.inputs` or `bf.publish` widget has to connect to the workbook host. | | **Source Request Timeout** | 30 s | Runtime Protocol | Time allowed for a notebook source save or load request. | --- ## Inputs Limits (`bf.inputs`) | Resource | Limit | Boundary | Description | | --- | --- | --- | --- | | **Max Inputs Widgets** | 1 | Runtime Protocol | Maximum number of `bf.inputs(...)` widget declarations per notebook. | | **Max Inputs** | 128 | Runtime Protocol | Maximum number of named arguments per `bf.inputs()` declaration. | | **Max Cells per Reference** | 100,000 cells | Runtime Protocol | Maximum number of cells in a single workbook reference range. | | **Total Input Cells** | 250,000 cells | Runtime Protocol | Maximum combined number of cells across all active workbook input references. | | **Input Snapshot Size** | 512 KB | Runtime Protocol | Maximum serialized size of an input snapshot payload. | | **Input Debounce Window** | 50 ms | Add-in Host | Debounce duration applied to workbook changes before bound inputs refresh. | --- ## Outputs and Functions Limits (`BF.OUTPUT` & `BF.FUNCTION`) | Resource | Limit | Boundary | Description | | --- | --- | --- | --- | | **Max Outputs** | 128 | Runtime Protocol | Maximum number of published outputs in `bf.publish(outputs={...})`. | | **Max Functions** | 128 | Runtime Protocol | Maximum number of published functions in `bf.publish(functions={...})`. | | **Max Output Cells** | 100,000 cells | Runtime Protocol | Maximum spill matrix size returned by a single published output. | | **Function Argument Cells** | 100,000 cells | Runtime Protocol | Maximum cells passed across arguments into a `BF.FUNCTION` call. | | **Function Subscriptions** | 512 | Runtime Protocol | Maximum active worksheet cell formulas subscribing to notebook functions. | | **Function Timeout** | 60,000 ms (60s) | Runtime Protocol | Maximum execution time for a single worksheet function invocation before returning `#N/A` in Excel (website demos show text; see [Website Demos](02-addin.md#website-demos-web-host)). | | **Active Function Concurrency**| 8 | Runtime Protocol | Maximum concurrent function invocations evaluated in parallel. | | **Queued Function Calls** | 128 | Runtime Protocol | Maximum pending function calls queued when all execution slots are busy. | | **Function Batch Size** | 64 | Runtime Protocol | Maximum function calls batched in a single evaluation round-trip. | When `bf.publish()` registers a new publication, active `BF.FUNCTION` subscriptions are invoked again against it. When a formula is removed or recalculated, Boardflare sends a best-effort cancellation: an `async` Python function is cancelled, but a synchronous function that is already running blocks the kernel and cannot be preempted. Use `async` functions for long-running work. --- ## Selection Limits (`bf.selection`) | Resource | Limit | Boundary | Description | | --- | --- | --- | --- | | **Selection Debounce Window** | 150 ms | Add-in Host | Debounce duration applied to grid selection updates before delivery to Python. | --- # Boardflare Notebook Changelog This changelog records public API, protocol, and runtime contract changes across Boardflare releases. ## October 2026 release (v1.4.0) ### What this release ships - **Reactive Inputs & Selections**: Read workbook inputs with [`bf.inputs(...)`](05-boardflare-api.md#bf-inputs), cell and table references with [`bf.ref(...)`](05-boardflare-api.md#bf-ref), and capture active grid selections with [`bf.selection(...)`](05-boardflare-api.md#bf-selection). See [Workbook Design: Reading Workbook Inputs](06-workbook-design.md#reading-inputs). - **Pre-Bundled Packages**: Import 15 curated companion packages directly in the zero-network runtime, with no dependency declarations (notebooks have no PEP 723 script block; see [Imports and Dependencies](04-python-notebooks.md#imports-and-dependencies)). See [Python Notebooks: Pre-bundled Companion Packages](04-python-notebooks.md#pre-bundled-companion-packages). - **Worksheet Code Sheet & AI Edit Loop**: Inspect, modify, and reload notebook code directly in Excel through the `_BOARDFLARE` worksheet and the AI edit handshake (increment cell `H1`, read cell `H2`). See [Workbook Editing](03-workbook-editing.md). - **Google Sheets Add-in**: Run reactive Marimo notebooks inside Google Sheets with workbook input synchronization via `bf.inputs()`, manual Refresh, and pre-bundled packages. See [The Boardflare Add-in: Google Sheets](02-addin.md#google-sheets). ### Changes from main - **`bf.selection` Table Selections**: Active table selections can be tracked using `bf.selection("TableName")`, returning a pandas DataFrame with the table's headers and at most one row: the row at the user's active cell (the cell the cursor is on), even when a larger range is highlighted. The result is empty when the active cell is outside the table's body rows or columns, on another sheet, on the header or total row, or on a hidden or filtered row. Rows carry values rather than positions. Limits: the cursor must be on a table cell, because cells beside the table select no row; filtering that hides the active row does not refresh the value until the next selection event. - **One `bf.inputs` per Notebook**: Enforced at most one `bf.inputs(...)` widget per notebook. Declaring more than one raises `RuntimeError("Only one bf.inputs(...) is allowed per notebook; use one bf.inputs(...) with all inputs")`. - **Notebook Source Moves to Code Sheet**: Notebook code is now stored in the hidden `_BOARDFLARE` worksheet (format version 1) rather than Custom XML parts, allowing chat assistants (Copilot, ChatGPT, Claude) and external tools to edit code and workbook data together. Template provenance (`templateId` and `templateVersion`) is stored directly in cells `H10:H11`. Excel workbooks with a Custom XML notebook migrate on opening: the source is written to the code sheet, read back, and only then is the Custom XML part removed. The built-in AI editor in the task pane has been removed in favor of direct code-sheet editing. - **Zero-Network Execution Sandbox**: Notebook code has no direct external network access. Dynamic package installation over the network is disabled; dependencies are served from Boardflare via pre-bundled companion packages in the Pyodide lockfile, and notebooks cannot make external HTTP requests. - **Blank header cells stay `None`**: When reading ranges with `headers=True`, blank header cells remain `None` rather than being coerced to the string `"nan"`. This matches Python in Excel (`xl()`) behaviour and uses `pd.Index(..., dtype=object)`. - **Duplicate headers now work**: Range inputs containing duplicate column header labels are now handled cleanly. On `main`, accessing columns by name caused an `AttributeError` when pandas returned a DataFrame for duplicates; the runtime now normalizes columns by position index. - **Publication Value Property Raises RuntimeError**: Accessing `.value` on a `bf.publish(...)` result raises `RuntimeError` pointing authors to `publication.consumers` instead of exposing internal traits. - **Clearer timezone-aware datetime error**: Publishing a timezone-aware `datetime` raises an actionable error message explaining that Excel stores wall-clock values and providing a concrete conversion snippet (`value.astimezone(zone).replace(tzinfo=None)`), rather than a generic rejection message. - **Web host reads formatted and error cells as typed values**: In the web (Univer) host, currency, percent, date, datetime and time cells now arrive as numbers, `datetime`/`time` values, and formula errors as `ExcelErrorValue`, as they already did in Excel. On `main` the web host passed their display strings (for example `"$49.99"`, `"2026-09-01"`, `"#DIV/0!"`). - **Web host keeps text that looks like a boolean**: A published string `"True"` stays text in the web host; on `main` the web host turned it into a boolean.