The single-table lifecycle, drawn cell-by-cell. One Benchling
result schema maps to one managed Excel table: its name
becomes the merged title band, each field becomes a
header with a per-column named range, and a hidden manifest
records which columns Benchling owns. We follow the schema
Purification Assay (warehouse token purification_assay,
fields Sample ID · Conc (mg/mL) ·
Pass/Fail) through init → write → colour → reinitialise.
initialise_table_benchling(wb, {"Assay Results": ["assaysch_abc123"]}, ctx=ctx)
reads the schema through ctx, then lays out a managed table on an
empty sheet: a merged title band carrying the
schema name, an auto-written header row (one
cell per field, bold), per-column sheet-scoped named ranges, a schema
anchor name spanning the header row, and a hidden column
manifest. No data is written yet.
Named ranges written (all sheet-scoped):
purification_assay → A2:C2 (the schema
anchor, spans the header row) · sample_id → A2 ·
conc_mg_ml → B2 · pass_fail → C2 ·
purification_assay__schema_columns → the hidden manifest formula
="sample_id,conc_mg_ml,pass_fail". Column tokens are the schema
warehouse names, lowercased to snake_case
(Conc (mg/mL) → conc_mg_ml). The
_table_start / _table_end bookmarks are
not written yet — they appear on the first paste (card 2).
paste_df_to_named_range(wb, df, "purification_assay", scope_sheet="Assay Results")
writes df.values positionally into the
data rows beneath the Benchling headers and
creates the _table_start / _table_end bookmarks
tracking the live extent.
Column order is checked, not assumed. Each
df column is matched against the header at the same position
(by token or display value, case/whitespace-normalised). A mismatch
raises ColumnOrderMismatchError rather than silently
misaligning data — so read with use_name_range_names=True,
transform, and the tokens line up on write-back. _table_end
now points at C5.
colour_rows(wb, name_range="purification_assay", df=df, mode="values_match",
column_name="pass_fail", values_to_colour=["Fail"], colour_name="light red",
scope_sheet="Assay Results") shades every row where
pass_fail is "Fail". Colouring is
positional against the header — pass the DataFrame in the
same row order as the sheet.
Other modes: "equals_no" reds any row where the column equals
"No" exactly; "group_alternate" bands repeated
categories light-blue / white. colour_rows raises
EmptyDataError if column_name is missing from
df or entirely null.
Benchling drops Conc (mg/mL) from the schema. Re-running
initialise_table_benchling(wb, {"Assay Results": ["assaysch_abc123"]}, ctx=ctx)
sees the existing anchor and reinitialises in place. The
dropped column is purged outright: its cells are
cleared and its named range deleted, the
remaining schema columns close up the gap, and the table
extent (anchor width, title merge, _table_end) shrinks to the
new rightmost column.
Columns dropped in Benchling are always purged — there is no soft-mark option. New columns append at the right edge and are placed in schema order by the repack pass (no propagating column shift).
After a column change, re-read before you write back. The
header order changed, so read the table again (with
import_excel_table) so your DataFrame's columns match the new
header — otherwise the next paste_df_to_named_range raises
ColumnOrderMismatchError.
About ctx. This package never sees your
Benchling client_id / client_secret. You build a
BenchlingContext from mgtx-benchling-wrapper
and pass it in; the package only calls ctx.benchling() to pull
schema metadata. Credential resolution is your responsibility — see
credentials.md.