Spreadsheet templates - KayanOS Documentation

Spreadsheet templates turn approved business data into reusable Excel workbooks. Use them for invoices, schedules, inspection registers, financial summaries, rosters, and other outputs where formulas, formatting, charts, print areas, and sheet structure matter. A managed template contains an .xlsx source, immutable versions, validation results, output rules, action bindings, and an optional AI-assisted draft history. It does not replace the record that authorized the output or the review required before a workbook is shared. This guide explains the managed lifecycle. For exact tags, vertical and horizontal loops, conditions, assets, formulas, and hard limits, read Spreadsheet-template tags and rendering.

When to use it

Use a spreadsheet template when recipients need a real workbook or a print-ready PDF whose layout is already designed. Good examples include a monthly service register, project-cost sheet, attendance roster, inspection checklist, or payment schedule. Keep authoritative values in KayanOS records; the workbook presents those values and may calculate display totals, but it should not be the only place where a decision or approval exists. Choose a dashboard for live exploration, a data export for unrestricted analysis, a form for collecting information, or a DOCX template for narrative correspondence. Prefer one focused workbook per operational purpose. A single workbook that mixes unrelated audiences, broad lookups, and many hidden sheets becomes difficult to test and risky to approve.

Configuration

Create and version a workbook

Open XLSX Templates in Build and upload a normal .xlsx file. Macro-enabled workbooks are not accepted. Give the template a clear name and description, choose the default execution mode, and optionally define a filename expression. Upload validation checks the ZIP package, workbook relationships, formulas that the renderer must rewrite, and all recognized template tags. A clean result means the package and supported grammar are safe to process; it does not prove that every real record has complete data or that every printed page looks correct. Each upload creates an immutable version and makes it current. Existing actions follow the configured current version unless they are explicitly pinned. Retain the reviewed source and release decision according to your organization’s records policy. KayanOS managed spreadsheet-template workspace with XLSX templates and render actions.

KayanOS managed spreadsheet-template workspace with XLSX templates and render actions.

Configure output and filenames

Managed rendering can return XLSX or PDF. XLSX preserves editable cells, formulas, styles, charts, tables, drawings, validation rules, and print settings. PDF conversion occurs after rendering through the office conversion service, so page size, orientation, margins, scaling, print area, repeated headings, RTL text, and chart placement must be tested in the PDF output. A filename expression uses the shared JEXL expression language. KayanOS sanitizes unsafe characters and forces the chosen extension. For example:

'monthly-service-register-' + $record.reporting_month

If the expression fails, the original uploaded filename is the fallback. Treat an unexpected fallback as a test issue rather than a production naming policy.

Bind entity and form actions

An entity action can explicitly select an XLSX template and allow XLSX, PDF, or both. Legacy actions whose document kind is unset continue through the existing DOCX-then-generic fallback; XLSX is used only when configured. A form action uses the separate xlsx_template type and can supply additional data context. Runtime data cannot replace protected roots such as the managed record, organization, member, and current time. Execution mode controls data access:

Mode	Meaning	Recommended use
`caller`	Lookups run with the requesting member’s normal access.	Default for staff-facing exports.
`admin`	Approved lookups can run with elevated service access.	Only for a documented purpose with narrowly allowed entities and reviewed output.

Use the AI workbook helper safely

The AI helper never edits the managed version directly. It builds a bounded map of sheets, used ranges, cell values, formulas, styles, and SHA-256 cell hashes without exposing raw OOXML. Each proposed content edit must name a sheet, cell address, and expected hash. If the workbook changed after the proposal, approval fails rather than overwriting the newer cell. Generation creates a private immutable source and revision chain. Review the downloadable XLSX and the rendered PDF, resolve mapping questions, then use the explicit approval dialog. Approval creates one managed version and is idempotent for the draft/revision key. Discarded, expired, and approved temporary objects are removed or scrubbed by scheduled cleanup.

Worked example

A service directorate prepares a monthly inspection register. The source workbook contains a title sheet, a repeated row for inspections, twelve month columns, formulas for totals, a landscape print area, Arabic notes, and a summary chart. The template uses a vertical loop for inspection records and a horizontal loop for months. Where the two regions cross, KayanOS produces a row-by-column matrix. The builder binds the workbook to the inspection_registers entity with caller execution, allows XLSX and PDF, and uses this filename expression:

'inspection-register-' + $record.year + '-' + $record.region_code

The repeating worksheet body can then use a dedicated opening row, one body row, and a dedicated closing row:

A5: {{#each $record.inspections as inspection}}
A6: {{ inspection.reference }}   B6: {{ inspection.location }}
A7: {{/each}}

They test an empty register, one inspection, and several inspections with twelve months. They open the XLSX to confirm formulas recalculate and inspect the PDF for landscape scaling, Arabic direction, page breaks, and the summary chart. Only after both outputs match the approved register do they release the version.

Testing

Test a representative workbook rather than only the upload response.

Test	Expected evidence
Package safety	`.xlsx` succeeds; macros, embedded objects, external workbook links, unsafe paths, and excessive expansion are rejected.
Scalar values	Numbers and Booleans remain typed; text beginning with `=`, `+`, `-`, or `@` cannot become formula injection.
Vertical loop	Zero, one, and several records create the correct row count and preserve row formatting.
Horizontal loop	Several periods repeat across columns and preserve column widths and formulas.
Matrix	Crossing row and column loops create every expected cell without overlapping markers.
XLSX review	Formulas, merges, validation, tables, charts, drawings, and hidden state remain correct.
PDF review	Print area, margins, orientation, scaling, page breaks, RTL text, and charts are acceptable.
Access	Caller and approved admin scenarios expose only intended records.
AI approval	A stale hash is rejected and a repeated approval key does not create a second version.

Troubleshooting

Symptom	Check	Response
A loop marker is printed or rejected	Is the marker the only non-empty cell in its entire marker row or column?	Move it to a dedicated marker lane and validate again.
A horizontal loop repeats downward	Was `#each` used instead of `#eachCol`?	Use the column grammar and dedicated marker columns.
A formula points to the wrong cell	Does it use mixed absolute references as intended?	Review `$A1`, `A$1`, and `$A$1`, then test the expanded workbook.
PDF clips columns	Are orientation, print area, margins, and fit-to-width configured?	Correct print setup in the template and retest conversion.
AI approval is disabled	Does the active revision contain unresolved mapping questions?	Resolve or regenerate the revision before approval.
PDF works locally but fails in deployment	Is the Collabora/WOPI conversion service configured for XLSX?	Verify conversion health and network policy; XLSX rendering alone does not prove PDF availability.

Permissions and data-quality limits

Creating templates, uploading versions, using the AI helper, changing execution mode, and configuring actions require Builder administration. Using an approved action may be delegated separately. Prefer caller mode. Admin mode must have a documented purpose, narrow entity scope, bounded lookups, and periodic review. Do not place credentials, access tokens, hidden sensitive notes, or unreviewed personal data in cells, formulas, URLs, QR codes, image sources, filenames, or AI instructions. Validation protects package and renderer boundaries; it cannot decide whether disclosed data is lawful, current, or appropriate for the recipient. Store decisions in governed records and treat the rendered workbook as an output of those records.

Author loops, conditions, assets, and formulas in Spreadsheet-template tags and rendering.
Define stable data fields in Entity fields and layouts.
Learn JEXL syntax in Expression language syntax.
Compare narrative outputs in Document templates.

​When to use it

​Configuration

​Create and version a workbook

​Configure output and filenames

​Bind entity and form actions

​Use the AI workbook helper safely

​Worked example

​Testing

​Troubleshooting

​Permissions and data-quality limits

​Related guides