Spreadsheet block
A document-local grid with formulas.
The Spreadsheet block (localGridSheet) is an inline grid with a real formula engine,
a formula bar and sticky headers. It is the document-local option: the rows live in
the document, not in a workspace data environment.
Insert it by typing / and picking Spreadsheet from the Compute category.
When to reach for it
Use a spreadsheet when you want to capture or compute values inside a document rather than persisting them to a workspace-level data environment. Typical uses:
- Buffer recipes with concentration / volume / final-concentration columns
- Daily body-weight readings for a small mouse cohort
- Reagent-prep checklists with computed totals
- Quick PK parameter calculations alongside narrative
If the data needs to be queryable across documents, use a Registration table (writes to an environment) or a Lookup table (reads from one) instead.
Example: reagent prep for an ELISA
The component below is the actual spreadsheet engine Dalea uses inside the block. Edit any cell, change a stock concentration or retarget a final volume, and the formulas in the Vol stock (µL) column recompute live:
This is the same spreadsheet engine Dalea uses inside the data-table block. Try editing the Final (µg/mL) or Stockcolumns — the formulas in Vol stock recompute live and the total updates. Click any cell starting with = to see its formula.
Formulas
Cell references and ranges work the way you expect (=B2*D2/A2, =SUM(B2:B9)), and a
sheet can address another spreadsheet block in the same document by that block's name
(=Samples!B4).
The registered functions include:
- Arithmetic
- ABS, SQRT, POWER, MOD, LOG, LOG10, LN, EXP, PI, RADIANS, SIN, COS, TAN, ASIN, ACOS, ATAN, ATAN2, RAND.
- Aggregation
- SUM, SUMIF, AVERAGE, MIN, MAX, PRODUCT, COUNT, COUNTA, COUNTIF.
- Logic
- IF, IFERROR, NOT, AND, OR.
- Lookup
- VLOOKUP, HLOOKUP, INDEX, MATCH, ROW, COLUMN.
- Rounding
- ROUND, ROUNDDOWN, ROUNDUP.
- Text and time
- CONCAT, CONCATENATE, LEN, LENB, NOW.
The function is AVERAGE; there is no AVG. #REF! is an error value, not a
function.
Reading other blocks
A BLOCK* family reaches any other tabular block in the document by its display name,
so a spreadsheet can pull from a Lookup table, Registration table, Well plate, Python
output or another spreadsheet, and re-evaluates when that block changes:
- BLOCKLOOKUP(block, keyColumn, keyValue, returnColumn)
- VLOOKUP across blocks: the returnColumn of the first matching row.
- BLOCKAGG(block, valueColumn, op, [filterColumn], [filterValue])
- sum, mean, min, max, count or counta over a column.
- BLOCKVALUE(block, rowIndex, column)
- One cell by 1-based row number and column.
- BLOCKMATCH(block, keyColumn, keyValue)
- The 1-based row number of the first match.
- BLOCKFIELD(block, path)
- A named value from a block with no table, e.g. a file attachment fileName.
- BLOCKCOUNT(block, [filterColumn], [filterValue])
- Row count, optionally filtered.
=DETAILS(A1) resolves an entity chip to its display ID, and
=DETAILS(A1, "Column1") reads one of that object's columns.
Columns
A spreadsheet column carries a header and a width, nothing more. There are no column types, no units, no enums and no per-column validation in this block: cells hold whatever you type, and the formula engine decides what is numeric.
Typed columns, with units, min/max, enums and references, belong to data-environment tables. Reach them from a document through the Registration table block, which validates each cell against the target table's column definitions, or the Lookup table block for reading.
Getting a CSV into a document
Drop a .csv, .tsv, .xls or .xlsx file anywhere in the document and Dalea
inserts a new Spreadsheet block from it, named after the file, with the first row
taken as the headers. Dropping onto an existing spreadsheet does not fill it.
For anything larger, import into a data environment through the Import into Dalea wizard instead. Inline spreadsheets are sized for tens to a few hundred rows.