Spreadsheet-template tags and rendering

KayanOS reads template tags from ordinary workbook cells and evaluates them against approved structured context. It preserves untouched ZIP parts and rewrites only the workbook structures affected by expansion or rendering. Reliable authoring depends on choosing the correct direction: row blocks repeat downward, column blocks repeat horizontally, and crossing blocks form a Cartesian matrix. Start with Spreadsheet templates for versions, actions, permissions, PDF output, and AI approval. KayanOS XLSX template settings and AI helper for spreadsheet tags, rendering, and PDF output.

KayanOS XLSX template settings and AI helper for spreadsheet tags, rendering, and PDF output.

When to use it

Use tags for values that must come from a record, conditional rows or columns, repeated line items, monthly or category columns, safe links, images, and QR codes. Use Excel formulas for workbook calculations and use KayanOS expressions for choosing data and structure. Keep approval logic in governed fields rather than hiding business decisions inside a formula or tag.

Configuration

Scalar cells and Excel types

An ordinary scalar tag is {{ expression }}. When it is the only content in a cell, the result is stored using an Excel number, Boolean, blank, or text type. When it appears beside other text, the complete result is text.

{{ $record.invoice_total }}
Reference: {{ $record.reference }}
{{ $record.approved }}

Strings that begin with =, +, -, or @ remain text; a data value cannot become a new formula. Existing formulas are preserved and reference rewriting respects relative, absolute, and mixed references. External workbook references and three-dimensional sheet references are rejected when an AI operation proposes a formula.

Vertical row loops and conditions

Row control markers occupy dedicated rows. The marker cell must be the only non-empty cell in that entire row.

A2: {{#each $record.lines as line}}
A3: {{ line.description }}   B3: {{ line.quantity }}   C3: {{ line.amount }}
A4: {{/each}}

Conditions use the same row lane:

A6: {{#if $record.show_notes}}
A7: {{ $record.notes }}
A8: {{else}}
A9: No notes supplied
A10: {{/if}}

The renderer removes marker rows and repeats or selects the body rows. Nested blocks are permitted when one range fully contains another; partially overlapping row blocks are rejected.

Horizontal column loops and conditions

Horizontal repetition uses eachCol, not each. The opening and closing markers occupy dedicated columns, and each marker must be the only non-empty cell in its column.

B1: {{#eachCol $record.months as month}}
C2: {{ month.label }}
C3: {{ month.amount }}
D1: {{/eachCol}}

Use ifCol, elseCol, and /ifCol for conditional columns:

F1: {{#ifCol $record.show_variance}}
G2: Variance
G3: {{ $record.variance }}
H1: {{/ifCol}}

The marker columns are structural lanes. Do not place headings, formulas, hidden values, or formatting-dependent content in them. A normal body column sits between the opening and closing markers.

Cartesian matrices

A row loop and a column loop may cross. KayanOS evaluates them independently and creates a Cartesian matrix. For example, a row loop over departments crossed with a column loop over months produces one row per department and one column per month. Inside the crossing cell, both aliases are available.

A4: {{#each $record.departments as department}}
B1: {{#eachCol $record.months as month}}
C5: {{ department.values[month.key] }}
Z1: {{/eachCol}}
A6: {{/each}}

Design a small source matrix and test zero, one, and multiple items on each axis. Large crossings grow multiplicatively and count toward the expanded-cell limit.

Hide, link, image, and QR directives

The renderer recognizes these directives:

Purpose	Syntax	Notes
Hide a row	`{{@hideRow expression}}`	Hides the rendered row when the expression is true.
Hide a column	`{{@hideColumn expression}}`	Hides the rendered column when true.
Link	`{{@link urlExpression labelExpression}}`	Only safe HTTP, HTTPS, or mail destinations are allowed.
Image	`{{@image expression width=120}}`	Fetches a bounded supported image and creates a drawing anchor.
QR	`{{@qr expression width=120}}`	Generates a bounded PNG QR code.

Rich directives should occupy a complete cell. Image bytes are bounded, normalized to PNG, and stored in workbook media parts. Drawing anchors move with expanded rows and columns. Never encode secrets in a QR code or place credentials in an image URL.

Formulas, ranges, and preserved structures

When expansion moves a formula cell, KayanOS tokenizes its A1 references and applies Excel mixed-reference semantics. It also rewrites merged cells, data validation ranges, tables, chart formulas, drawings, defined names, print areas, and other supported range-bearing structures. The workbook is marked for full calculation on open because the service does not evaluate Excel formulas itself.

=B4*C4
=$B4*C$4
=SUM(B4:D4)

After expansion, open the workbook in the supported office viewer and confirm calculated values. Formula recalculation is requested, but a PDF converter and a desktop viewer can differ in font, chart, or calculation behavior.

Print and PDF setup

Configure page orientation, margins, print area, fit-to-width, repeated titles, and manual page breaks in the source workbook. PDF conversion uses the rendered workbook; it does not invent print settings. For wide horizontal loops, landscape orientation and an intentional fit-to-width value are usually required. Test Arabic and other RTL content in both cell view and printed output.

Context and bounded lookups

Managed entity rendering supplies $record, $relations, $inverseRelations, $organization, $member, and $now. Use stored keys, not translated labels. Helpers such as getRecords are bounded and run under caller or approved admin access. Every lookup must have an explicit small limit. The renderer enforces row, image, output-size, and timeout limits before a generated workbook can exhaust service resources.

Worked example

A project office creates a budget matrix. Projects repeat down rows and quarters repeat across columns. The source has one project body row, one quarter body column, a total formula, a chart series, and a landscape print area. The author uses each around the project row and eachCol around the quarter column, then references both aliases in the crossing amount cell.

{{ project.amounts[quarter.key] }}

They render two projects across four quarters. The expected workbook has eight amount cells, formulas point to the expanded range, the chart series includes the new cells, and the PDF fits on the approved number of pages. A second test uses no projects, and a third hides the variance column with @hideColumn.

Testing

Test	Expected result
Typed scalar	A number remains numeric, a Boolean remains Boolean, and dangerous leading characters remain text.
Marker exclusivity	Any other value in a marker row or column causes validation failure.
Nested direction	Fully nested blocks succeed; partial overlap in the same direction fails.
Matrix	`rows × columns` body cells are produced and both aliases resolve.
Formula movement	Relative axes move, absolute axes stay fixed, and quoted sheet names survive.
Assets	Safe links, images, and QR codes render; active schemes and oversized images fail.
Preservation	Merges, validation, tables, charts, drawings, widths, heights, and print settings remain usable.
Recalculation	The output requests full calculation and shows correct values in the supported viewer.
PDF	Page setup, RTL text, charts, and page breaks are visually reviewed.

Troubleshooting

Symptom	Likely cause	Safe fix
`ROW_MARKER_NOT_EXCLUSIVE`	Another non-empty cell exists in the marker row.	Clear the complete row except for the marker cell.
`COLUMN_MARKER_NOT_EXCLUSIVE`	Another non-empty cell exists in the marker column.	Move all body content between dedicated marker columns.
`OVERLAPPING_*_BLOCKS`	Same-direction ranges cross without nesting.	Make one block fully contain the other or separate them.
Loop expression error	The expression did not return an array.	Inspect the test context and provide a bounded array.
External reference rejected	A formula points to another workbook or a 3D sheet range.	Replace it with an internal reviewed formula or precomputed value.
Chart or print range is wrong	Source ranges or print settings did not represent the expandable body.	Simplify the source range and test the rendered XLSX and PDF.

Permissions and data-quality limits

Tag expressions do not grant data access. Caller mode uses the requesting member’s scope; admin mode requires explicit configuration and narrow allowed entities. The package reader rejects macros, ActiveX, embedded objects, external workbook links, path traversal, excessive entry counts, excessive uncompressed size, and dangerous compression ratios. Structural expansion is bounded by loop, nesting, cell, image, output, and timeout limits. These protections cannot judge whether a total is conceptually correct, whether a column should be disclosed, or whether a chart is misleading. Review the source data, formulas, labels, blank-state behavior, and recipient permissions as part of release approval.

Manage versions, actions, and AI approval in Spreadsheet templates.
Learn shared expressions in Expression language syntax.
Build stable record structures in Entities.
Configure action logic in Form actions, logic, and signatures.

​When to use it

​Configuration

​Scalar cells and Excel types

​Vertical row loops and conditions

​Horizontal column loops and conditions

​Cartesian matrices

​Hide, link, image, and QR directives

​Formulas, ranges, and preserved structures

​Print and PDF setup

​Context and bounded lookups

​Worked example

​Testing

​Troubleshooting

​Permissions and data-quality limits

​Related guides