Templating Directives

Often, worksheets in spreadsheets aren't arranged in simple, computation-optimized tabular formats. Instead, they present data for human consumption. These directives instruct Pebble Stream Zen to pull sections of data from these human-optimized worksheet formats and arrange this data in the tabular format expected by Pebble Stream workflows. This means spreadsheet loaders can push data to compute-optimized input worksheets, and workflows can output worksheets into human-optimized reporting formats.

Templating connects the worksheets of a pebble to a template worksheet in the user's own workbook — a sheet with a free-form layout (stacked tables, side-by-side blocks, fixed report cells) that Zen reads data out
of and writes results back into.

Template directives are ordinary PebbleScript cell comments. They come in two
families:

Family	Placed on	Direction	What it does
Extraction directives	input worksheets	template → pebble	Tell Zen how to find the tables inside the user's template worksheet and turn them into flat input rows.
Output fill directives (`template-out-*`)	output worksheets	pebble → template	Tell Zen where and how to write the workflow's results back into the template worksheet.

The mandatory `template-name`

Every worksheet that carries any other template directive must also declare
template-name — the name of the template worksheet in the
user's workbook it is rendered from (or filled into). A sheet with template
directives but no template-name fails compilation with
missing-the-template-name-directive.

Several pebble worksheets may share one template name: Zen extracts/fills all of
them from the same template worksheet.

Extraction directives

Used on input worksheets. See Extracting data from a template below for how they work together.

Directive	Level	Arguments	Purpose
`template-name`	sheet	one (template worksheet name)	Declares the template worksheet this sheet is rendered from.
`block-id`	column	none	Column that records which stacked block/table a row came from.
`template-row`	column	none	Column that records the template row index of the record.
`template-column`	column	none	Column that records the template column index of the record.
`leftmost-column`	column	none	Constant: first column of the record's block.
`rightmost-column`	column	none	Constant: last column of the record's block.
`top-row`	column	none	Constant: first row of the record's block.
`bottom-row`	column	none	Constant: last row of the record's block.
`block-row-count`	column	none	Constant: number of rows in the record's block.
`row-oriented-headers`	sheet	none	Hint: tables are stacked vertically (headers on rows).
`column-oriented-headers`	sheet	none	Hint: tables are side by side (headers on columns).

Output fill directives

Used on output worksheets (alongside thetemplate-name directive, which then names the
template worksheet to fill). A template-out sheet is always classified :final
and may have no dependent worksheets.

Directive	Level	Arguments	Purpose
`template-out-header`	column	none	Anchor filling next to the column's header text found in the template.
`template-out-cell`	column	none	Anchor filling at the A1 address written as the column's header.
`template-out-down`	column	none	Fill the column's records downward.
`template-out-right`	column	none	Fill the column's records rightward.
`template-out-group`	column	none	Column holding the group key that advances the anchors.
`template-out-style`	column	optional quoted headers	Column whose values name stored Excel styles to apply.
`template-out-value-type`	column	none	Column whose values choose `value` / `formula` / `note` per cell.
`template-out-generate-clean`	sheet	none	Recreate the template worksheet blank before filling.

Extraction flow

The user opens their workbook (containing the template worksheet) and clicks
extract (or submit) in the Zen taskpane.
For each template name, Zen locates the template worksheet, reads its cells and
runs the extraction algorithm:
- find every cell whose text matches one of the worksheet's expected headers;
- cluster the header cells into lines and split them into blocks — each block
  is one stacked table;
- pick the orientation (honoring row-oriented-headers /
  column-oriented-headers if present, otherwise whichever orientation matches
  more headers);
- read the data lines below/beside each header line into records, appending the
  meta columns declared by block-id, template-row/template-column,
  leftmost-column/rightmost-column/top-row/bottom-row/block-row-count.
The records are written into the workflow's input worksheets (headers in row 1,
data from row 2) or uploaded directly, and the workflow runs on them.

Output fill flow

After a run completes, the user picks a request and clicks generate data.
Zen downloads the run's output worksheets and the template worksheet's grid,
resolves the template-out-header / template-out-cell, buckets the
records by template-out-group and computes one write plan per template.
The plan is applied to the workbook: styles from template-out-style columns,
content kinds from template-out-value-type, advancing anchors per group.

Validation summary

Error	Cause
`missing-the-template-name-directive`	A sheet uses template directives but has no `template-name`.
`incompatible-template-directives`	`row-oriented-headers` and `column-oriented-headers` on one sheet.
`template-out-conflict`	`template-out-header` + `template-out-cell`, `template-out-down` + `template-out-right`, an anchor + a styling directive, or `template-out-group` + an anchor/styling directive on one column.
`template-out-clean-conflict`	`template-out-generate-clean` together with `template-out-header`.
`template-out-bad-cell-address`	A `template-out-cell` column's header is not a valid A1 address.
`template-out-style-unknown-header`	A `template-out-style` header argument does not name exactly one data column.
`template-out-style-conflict`	Two style columns claim the same column, or more than one parameterless default style column.
`template-out-has-dependents`	A template-out output worksheet is used as input by another worksheet.

template-name — the mandatory anchor of every template sheet
template-out-style — stored Excel styles and how they are applied

The mandatory template-name