# Many Benchling tables, driven by a dictionary
> ๐จ **Colour walkthrough:** [open the interactive visual version](../visualizations/benchling-multiple.html) โ colour multi-sheet / stacked layout diagrams. The Unicode grids below are the always-available text version.
This guide shows how to stand up **many** Benchling assay-result tables in one
workbook โ spread across several sheets, or stacked vertically on one sheet โ
all driven from a single Python dictionary, then filled, read back, and
refreshed in a loop.
It builds directly on the single-table guide. If you have not yet stood up
**one** table (auth with a `BenchlingContext`, the `assaysch_โฆ` id rule, what
`initialise_table_benchling` writes, the `sanitize_formulas` default), read
that first and come back here:
> **Prerequisite:** [Benchling โ Excel: a single table](benchling-single.md)
This guide does **not** repeat those basics. It focuses on the *fan-out*: how a
plan dict becomes many tables, how to write/read them in bulk, and how to keep
them all in sync with Benchling.
---
## Contents
1. [Mental model: sheets vs. vertical stacks](#1-mental-model-sheets-vs-vertical-stacks)
2. [The driving data structure + the init loop](#2-the-driving-data-structure--the-init-loop)
3. [Writing results back to many tables (multi-paste)](#3-writing-results-back-to-many-tables-multi-paste)
4. [Reading many tables back into a dict of DataFrames](#4-reading-many-tables-back-into-a-dict-of-dataframes)
5. [Refreshing / reinitialising many tables](#5-refreshing--reinitialising-many-tables)
6. [End-to-end worked example](#6-end-to-end-worked-example)
7. [Common errors & fixes](#7-common-errors--fixes)
8. [Related](#8-related)
---
## 1. Mental model: sheets vs. vertical stacks
A *managed Benchling table* is a header block plus a set of named ranges and
two bookmarks, `_table_start` / `_table_end`, that track where its
data begins and ends. Multiple managed tables can coexist in one of two ways:
**Across multiple sheets โ one (or more) table per tab:**
Potency tab
A
B
1
Potency Assay
2
sample_id
ec50
3
VS0001
0.42
Identity tab
A
B
C
1
Identity Assay
2
sample_id
method
result
3
VS0001
LC-MS
Pass
Purity tab
A
B
C
1
Purity Assay
2
sample_id
peak
pct_purity
3
VS0001
1
98.7
One managed Benchling table per sheet.
**Stacked vertically on one sheet โ two tables sharing a tab:**
Identity tab โ Identity over Purity
A
B
C
1
Identity Assay
2
sample_id
method
result
3
VS0001
LC-MS
Pass
4
VS0002
LC-MS
Pass
โฎ table_gap = 2 blank rows โฎ
7
Purity Assay
8
sample_id
peak
pct_purity
9
VS0001
1
98.7
The blue band is the merged, bold, centred schema **title**; the strong-blue
**header** row carries the per-column sheet-scoped named ranges (warehouse
names); the light-blue cells are live **data**. The purple **Purity Assay** is
the stacked-below neighbour table, landing `table_gap` blank rows below the
first table's `_table_end`.
**The lane rule.** Managed tables on the same sheet form **lanes** based on
column connectivity. Tables within the same lane must occupy **disjoint row
ranges** โ they stack top-to-bottom. Tables in separate column bands (different
lanes) may share rows โ side-by-side, L-shaped, and T-shaped layouts across
disjoint column bands are supported. Tables on **different sheets** have no
constraints โ each sheet is its own canvas.
For the full contract (lane model, how the gap below a table is preserved across
grow/shrink pastes, danger-zone shape detection, and how reinit grows columns) see:
> **Concepts:** [Stacked tables & reinitialisation](../concepts/stacked-tables-and-reinit.md)
**When to use which:**
| Situation | Layout |
|-----------|--------|
| Each assay is conceptually a separate report | **One table per sheet** โ cleanest, no row-collision risk, tabs name themselves after the assay |
| A few related assays a reviewer reads top-to-bottom together | **Stacked on one sheet (same lane)** โ keep `table_gap` โฅ 2 so grow/shrink never collides |
| Many assays (5+), or assays that grow to thousands of rows | **One per sheet** โ avoids any chance of a growing table pushing into its lane-mate |
| Two small lookup tables that always travel together | **Stacked** (same lane) optionally with per-schema column anchors (`column={schema_id: col}`), or **side-by-side in separate column bands** (different lanes) if they share the same rows |
When in doubt, **one table per sheet**. It is the layout with the fewest
failure modes.
---
## 2. The driving data structure + the init loop
`initialise_table_benchling` has the following real signature (copied verbatim
from the source):
```python
def initialise_table_benchling(
wb,
schema_map: "dict[str, list[str]]",
*,
ctx,
mode: Literal["autopaste", "paste"] = "autopaste",
column: "int | str | dict[str, int | str] | None" = None,
at: "str | None" = None,
table_gap: int = 2,
) -> list[SchemaInitResult]:
```
There is **one** calling shape: a dict mapping each **sheet name** to the
ordered **list of schema ids** on it. It reads exactly how you think about the
layout โ "this sheet holds these tables":
- Multiple ids under one sheet **stack vertically** on it, in list order,
separated by `table_gap` rows.
- The same id may appear under more than one sheet.
Keep your layout as a plan dict keyed by sheet โ
```python
tables = {
"Potency": ["assaysch_a"], # one table on the Potency tab
"Identity": ["assaysch_b", "assaysch_c"], # two tables stacked on Identity
}
```
โ and pass it **straight in**; it *is* the argument, no loop or flattening:
```python
import xlwings as xw
from mgtx_benchling_wrapper.context.benchling_context import BenchlingContext
from xlwings_package import initialise_table_benchling
wb = xw.Book.caller() # or xw.Book(r"C:\path\to\book.xlsx")
ctx = BenchlingContext(
client_id="your-oauth2-app-client-id",
client_secret=PLAINTEXT_SECRET, # resolve from your store
base_url="https://yourorg.benchling.com",
token_url="https://yourorg.benchling.com/api/v2/token",
)
tables = {
"Potency": ["assaysch_a"],
"Identity": ["assaysch_b", "assaysch_c"],
}
results = initialise_table_benchling(wb, tables, ctx=ctx)
```
**What happens per sheet:**
- The sheet is **created if it does not already exist** (`wb.sheets.add`); an
existing sheet is reused.
- Each schema id is resolved against Benchling (`assaysch_โฆ` only โ name lookup
is not supported) to get its display name and column list.
- For each id **in list order**: if no table for that schema exists on the
sheet yet, a fresh header block is written and its action is `"created"`; if
it already exists, it is refreshed in place and its action is
`"reinitialised"` (see [ยง5](#5-refreshing--reinitialising-many-tables)).
- Stacked tables on the same sheet each get their **own row cursor** at their
start column, so a new table lands below the previous one with `table_gap`
empty rows between them.
`results` is a **flat** `list[SchemaInitResult]` (one per schema, across all
sheets) โ a frozen, slotted dataclass, so access fields by **attribute** (not
subscript). Each carries `schema_name`, `display_name`, `warehouse_name`,
`range_name`, `sheet_name`, `action`, `columns` (a `tuple[ColumnInfo, โฆ]`), and
โ on reinit โ `diff`. After the loop you get one `INFO` log line per schema:
```
Schema 'Potency Assay' โ Potency [created]
Schema 'Identity Assay' โ Identity [created]
Schema 'Purity Assay' โ Identity [created]
```
### When you still want a loop
A single call shares one `column` anchor across every sheet. If you need a
**different anchor column per sheet**, or want to catch and log errors
sheet-by-sheet, loop and call once per sheet โ each entry is a valid one-entry
`schema_map`:
```python
results = []
for sheet_name, schema_ids in tables.items():
results += initialise_table_benchling(
wb,
{sheet_name: schema_ids},
ctx=ctx,
column="A" if sheet_name == "Potency" else "E",
)
```
### Resulting layout
Potency tab โ one table
A
B
1
Potency Assay
2
sample_id
ec50
Identity tab โ two tables stacked vertically
A
B
C
1
Identity Assay (assaysch_b)
2
sample_id
method
call
โฎ table_gap rows โฎ
5
Purity Assay (assaysch_c)
6
sample_id
peak
pct_purity
---
## 3. Writing results back to many tables (multi-paste)
`paste_df_to_named_range` writes a DataFrame into a managed table addressed by
its **named-range token** โ the schema warehouse name (snake_case), the same
token shown by `import_excel_table`'s mapping. Its real signature:
```python
def paste_df_to_named_range(
wb,
df: pd.DataFrame | list[pd.DataFrame],
named_range: str | list[str],
scope_sheet: Optional[str] = None,
sanitize_formulas: bool = True,
) -> None:
```
It has a built-in **list mode**: pass a `list` of DataFrames *and* a matching
`list` of named-range tokens. The lengths must match; it zips them and pastes
each pair. Under the hood this is exactly a loop over the single-paste path โ
there is no batch optimisation, it is purely a convenience.
### List mode โ many tables on ONE sheet
Because `scope_sheet` is a **single value** in list mode (it is forwarded
unchanged to every paste), list mode is the right tool when all the target
tables live on the **same** sheet:
```python
from xlwings_package import paste_df_to_named_range
paste_df_to_named_range(
wb,
[identity_df, purity_df], # list of DataFrames
["identity_assay", "purity_assay"], # matching list of range tokens
scope_sheet="Identity", # both tables are on this one tab
)
```
### Loop โ many tables across MULTIPLE sheets
When your tables span several sheets, each needs its own `scope_sheet`, so loop
and call the single-paste form once per table. Drive it from a dict keyed by
`(token, sheet)`:
```python
to_write = {
("potency_assay", "Potency"): potency_df,
("identity_assay", "Identity"): identity_df,
("purity_assay", "Identity"): purity_df,
}
for (token, sheet), df in to_write.items():
paste_df_to_named_range(wb, df, token, scope_sheet=sheet)
```
### Column-order matching is enforced **per table**
Each paste validates that **your DataFrame's columns line up with that table's
existing headers**, position by position. The data is written *positionally*
(`df.values`), so a reordered or shifted DataFrame would silently misalign under
the headers โ to prevent that, a mismatch raises `ColumnOrderMismatchError`
**before** anything is written.
Match against either the **range-name token** (warehouse name) *or* the **header
display text** (case- and whitespace-insensitive). The cleanest contract is to
key each DataFrame's columns by the warehouse-name tokens (which is exactly what
`import_excel_table` returns by default โ see [ยง4](#4-reading-many-tables-back-into-a-dict-of-dataframes)).
If Benchling added columns to a schema, **re-initialise that table first**
(ยง5) so its headers exist, *then* paste the wider DataFrame.
Notes that apply to every paste (single or list):
- The DataFrame **index is not written** โ only `df.values`.
- A zero-row DataFrame raises `EmptyDataError`; filter those out of your dict
before looping.
- Formula-injection sanitisation is on by default (`sanitize_formulas=True`):
text cells beginning with `=`/`+`/`-`/`@` are apostrophe-escaped. Pass
`sanitize_formulas=False` only for trusted sources where you deliberately want
formula strings written through verbatim.
---
## 4. Reading many tables back into a dict of DataFrames
To pull every managed table back out, loop `import_excel_table`, keyed by the
schema token and its sheet. `import_excel_table` returns
`(DataFrame, mapping)`, where columns default to the **warehouse-name tokens**
(`use_name_range_names=True`) โ the same tokens `paste_df_to_named_range`
expects, so a read โ transform โ write round-trip lines up by construction.
```python
from xlwings_package import import_excel_table
tables_on_sheet = {
("potency_assay", "Potency"): None,
("identity_assay", "Identity"): None,
("purity_assay", "Identity"): None,
}
frames: dict[str, "pd.DataFrame"] = {}
mappings: dict[str, dict] = {}
for token, sheet in tables_on_sheet:
df, mapping = import_excel_table(wb, token, scope_sheet=sheet)
frames[token] = df
mappings[token] = mapping
# frames["identity_assay"] โ DataFrame with columns ["sample_id", "method", "call", โฆ]
# mappings["identity_assay"] โ {"sample_id": "Sample ID", "method": "Method", โฆ}
```
Want only some columns of a table? Pass `include_list` (cannot be combined with
`ignore_list`):
```python
df, mapping = import_excel_table(
wb, "potency_assay", scope_sheet="Potency",
include_list=["sample_id", "ec50"],
)
```
A completely empty table (or an all-null column, when
`check_empty_columns=True`, the default) raises `EmptyDataError` โ wrap the call
in `try`/`except` if some tables may legitimately be empty at read time.
---
## 5. Refreshing / reinitialising many tables
Re-running your init call (ยง2) against a workbook whose tables **already exist**
refreshes each one in place instead of recreating it โ that schema's `action`
on the returned `SchemaInitResult` becomes `"reinitialised"` and a `diff` is
attached. Just call it again with the same plan dict:
```python
results = initialise_table_benchling(wb, tables, ctx=ctx)
for r in results:
if r.action == "reinitialised":
print(r.sheet_name, r.display_name, r.diff)
```
On reinit, Benchling is the source of truth. For each table:
- **Title and column-header text** are re-synced from the current schema.
- **Columns added in Benchling** are inserted/appended โ reinit **grows the
table's columns** in place. The exact placement rules (middle-insert vs.
end-append, reorder handling) are covered in the concepts doc.
- **Columns dropped in Benchling** are always **purged outright**: cells are
cleared, the remaining columns close up contiguously, and the table's extent
(named range, title merge, and `_table_end` bookmark) shrinks to the new
rightmost column โ **without disturbing any table stacked below it or in a
neighbouring lane.**
Because reinit can grow a table's columns, **always reinit before pasting** when
a schema may have changed โ that way the headers exist and the wider DataFrame
matches the column-order check in ยง3. For the full reinit semantics (column
add/remove/reorder, the schema manifest that protects manually-added named
ranges, and the `partial_diff` attribute on mid-loop failures) see:
> **Concepts:** [Stacked tables & reinitialisation โ "reinit grows columns"](../concepts/stacked-tables-and-reinit.md)
Prune stale `#REF!` names with
[`cleanup_stale_table_names`](../reference/tools.md) (one call per table token).
---
## 6. End-to-end worked example
A dict โ several sheets each with a Benchling table โ fill โ read back โ save.
```python
import pandas as pd
import xlwings as xw
from mgtx_benchling_wrapper.context.benchling_context import BenchlingContext
from xlwings_package import (
initialise_table_benchling,
paste_df_to_named_range,
import_excel_table,
save_dataframe,
)
# --- 0. Workbook + Benchling context -------------------------------------
wb = xw.Book.caller() # or xw.Book(r"C:\path\to\assays.xlsx")
ctx = BenchlingContext(
client_id="your-oauth2-app-client-id",
client_secret=PLAINTEXT_SECRET, # resolve from your store
base_url="https://yourorg.benchling.com",
token_url="https://yourorg.benchling.com/api/v2/token",
)
# --- 1. The plan: which tables live on which sheet -----------------------
tables = {
"Potency": ["assaysch_a"], # one table
"Identity": ["assaysch_b", "assaysch_c"], # two tables stacked vertically
}
# Each table is addressed by its anchor token (SchemaInitResult.range_name).
# Capture it from the real results rather than hard-coding โ but for this
# example assume:
# assaysch_a โ "potency_assay"
# assaysch_b โ "identity_assay"
# assaysch_c โ "purity_assay"
# --- 2. Initialise every table (creates sheets + headers) ----------------
results = initialise_table_benchling(wb, tables, ctx=ctx)
# Capture each table's anchor token + sheet from the real results so
# downstream paste/import code never hard-codes tokens.
layout = {r.range_name: r.sheet_name for r in results}
# --- 3. Fill each table ---------------------------------------------------
# DataFrame columns are keyed by warehouse-name tokens so they line up with
# the headers initialise_table_benchling just wrote (column-order check, ยง3).
potency_df = pd.DataFrame({
"sample_id": ["VS0001", "VS0002"],
"ec50": [0.42, 0.51],
})
identity_df = pd.DataFrame({
"sample_id": ["VS0001", "VS0002"],
"method": ["LC-MS", "LC-MS"],
"call": ["Pass", "Pass"],
})
purity_df = pd.DataFrame({
"sample_id": ["VS0001", "VS0002"],
"peak": [1, 1],
"pct_purity": [98.7, 97.2],
})
# Identity + Purity share the "Identity" sheet โ list mode, one scope_sheet.
paste_df_to_named_range(
wb,
[identity_df, purity_df],
["identity_assay", "purity_assay"],
scope_sheet="Identity",
)
# Potency is on its own sheet โ single paste.
paste_df_to_named_range(wb, potency_df, "potency_assay", scope_sheet="Potency")
# --- 4. Read everything back into a dict of DataFrames -------------------
read_plan = {
"potency_assay": "Potency",
"identity_assay": "Identity",
"purity_assay": "Identity",
}
frames = {}
for token, sheet in read_plan.items():
df, _mapping = import_excel_table(wb, token, scope_sheet=sheet)
frames[token] = df
# --- 5. Save one of them --------------------------------------------------
save_dataframe(frames["purity_assay"], default_file_name="purity_results")
# Opens a save dialog; writes CSV or XLSX depending on user choice.
```
To **refresh** after a schema change in Benchling, just re-run step 2's loop โ
existing tables reinitialise in place (ยง5), then repeat steps 3โ5.
---
## 7. Common errors & fixes
| Exception / symptom | Cause | Fix |
|---------------------|-------|-----|
| `ValueError: Schema lookup requires a Benchling ID โฆ` | A schema key was a name, not an `assaysch_โฆ` id | Use the `assaysch_โฆ` id from the schema's Benchling URL โ name lookup is not supported. |
| `TypeError: schema_map must be a dict[sheet_name, list[schema_id]] โฆ` | Passed a bare sheet-name string (or other non-dict) as the second argument | Wrap it in a dict: `{"Sheet": ["assaysch_โฆ"]}`. |
| `TypeError: schema_map['Sheet'] must be a list of schema ids โฆ` | A sheet's value was a bare id string, not a list | Wrap the single id in a list: `{"Sheet": ["assaysch_โฆ"]}`. |
| `ValueError: schema_map['Sheet'] has no schema ids.` | A sheet was mapped to an empty list | Give each sheet at least one id, or drop the sheet from the dict. |
| `ValueError: schema_map must not be empty.` | Passed an empty dict | Populate the plan dict before calling. |
| `ImportError: mgtx-benchling-wrapper is required.` | The Benchling extra is not installed | `pip install "mgtx-xlwings-utils[benchling]"` |
| `SideBySideNotSupportedError` | Two managed tables in the same lane (overlapping columns) ended up with **overlapping rows** | Stack them vertically (raise `table_gap`, or move a table to its own sheet). Tables in disjoint column bands (different lanes) sharing rows are allowed. See the [concepts doc](../concepts/stacked-tables-and-reinit.md). |
| `TableSpaceOccupiedError` | A fresh table's header band, or the danger zone below an existing table, holds loose user content | Clear the cells, choose a different sheet, or anchor elsewhere (`column` / `mode="paste"` with a different `at`). |
| `ColumnOrderMismatchError` on paste | DataFrame columns don't line up with that table's existing headers | Reorder the DataFrame to match, or re-initialise that table (ยง5) so new/reordered Benchling columns are applied first. |
| `ExcelColumnOverflowError` | A schema (or auto-widen) would land a column past column XFD (16,384) | Anchor the table further left, or split the schema across sheets. |
| `EmptyDataError` on read | The table (or a column) is entirely empty | Skip empty tables, or pass `check_empty_columns=False` to `import_excel_table`. |
| `ManifestNotFoundError` from `cleanup_stale_table_names` | The table was never created by `initialise_table_benchling`, or its `{token}__schema_columns` manifest was deleted | Only run cleanup against tables this package initialised; re-initialise to rewrite the manifest. |
| List-mode paste raises `ValueError: Number of DataFrames must match number of named ranges.` | The DataFrame list and token list have different lengths | Make the two lists the same length, or build them together so they can't drift. |
---
## 8. Related
- [Benchling โ Excel: a single table](benchling-single.md) โ the prerequisite:
auth, the `assaysch_โฆ` id rule, what one table looks like.
- [Stacked tables & reinitialisation](../concepts/stacked-tables-and-reinit.md)
โ the stacked-only contract, gap preservation, and how reinit grows columns.
- [Normal (non-Benchling) tables](normal-tables.md) โ `initialise_table_named_ranges`
+ `import_excel_table` for tables you build by hand.
- [Plates](plates.md) โ 96/384-well plate layouts as managed regions.
- [Tools reference](../reference/tools.md) โ full signatures for
`initialise_table_benchling`, `paste_df_to_named_range`, `import_excel_table`,
`cleanup_stale_table_names`, and the exception types.
- [Documentation home](../index.md)