Repository Public License
1# ORIX OS Automation2# File: Workflows/Examples/14-spreadsheet-validation.yaml3# Description: Built-in automation: checks an .xlsx sheet for required columns, empty rows and missing key values, and reports.4#5# Author: Simon-Pierre Boucher6# Contact: contact@spboucher.ai7#8# Limitations9# - Reads .xlsx files through documents.xlsxRead (one sheet, by name or 1-based position); formulas are read as their10# cached values, dates as Excel serial numbers. Legacy .xls, .numbers and encrypted workbooks are not supported.11# - The first row of the sheet is the header. Checks implemented here: required columns present, no fully empty row,12# no empty cell in the key column. No type or range rules.13# - At most 20 000 rows are read; a longer sheet fails the validation rather than being reported as valid.14# - The workbook is never modified. The report is written before the verification, so a failed validation still leaves15# a report explaining why; the run then ends FAILED on purpose (the verification is the validation).16apiVersion: orix.ai/v117kind: Automation18metadata:19 id: spreadsheet-validation20 name: Automated spreadsheet validation21 description: >-22 Reads a spreadsheet, checks that the required columns exist, that no row is empty and that the key23 column is always filled, writes a validation report, and fails the run when a rule is broken.24 tags: [spreadsheet, validation, xlsx]25spec:26 triggers:27 - type: manual28 - type: fileChange29 path: ${{ params.workbook }}30 debounceSeconds: 1031 parameters:32 - name: workbook33 type: path34 default: ~/ORIX Samples/spreadsheet-validation/data.xlsx35 description: Spreadsheet to validate.36 - name: sheet37 type: string38 default: "1"39 description: Sheet name, or its position starting at 1.40 - name: requiredColumns41 type: array42 default: [id, name, amount]43 description: Column titles that must be present in the header row.44 - name: keyColumn45 type: integer46 default: 047 description: Zero-based index of the column that must never be empty.48 - name: reportFolder49 type: path50 default: ~/ORIX Samples/spreadsheet-validation/Reports51 description: Folder that receives validation.md.52 permissions:53 filesystem:54 read: ["${{ params.workbook }}", "${{ params.reportFolder }}"]55 write: ["${{ params.reportFolder }}"]56 budget:57 maxDurationSeconds: 30058 maxToolCalls: 1059 steps:60 - id: read61 tool: documents.xlsxRead62 with:63 path: ${{ params.workbook }}64 sheet: ${{ params.sheet }}65 limit: 2000066 - id: header67 needs: [read]68 transform: ${{ map(default(first(steps.read.output.rows), []), c => trim(string(default(c, "")))) }}69 - id: rows70 name: Data rows (everything under the header)71 needs: [read]72 transform: ${{ slice(steps.read.output.rows, 1) }}73 - id: missingColumns74 needs: [header]75 transform: ${{ filter(params.requiredColumns, c => !contains(steps.header.output, c)) }}76 - id: emptyRows77 needs: [rows]78 transform: ${{ count(steps.rows.output, r => count(r, c => c != null && c != "") == 0) }}79 - id: missingKeys80 needs: [rows]81 transform: ${{ count(steps.rows.output, r => r[params.keyColumn] == null || r[params.keyColumn] == "") }}82 - id: valid83 needs: [rows, missingColumns, emptyRows, missingKeys]84 transform: ${{ len(steps.rows.output) > 0 && !steps.read.output.truncated && len(steps.missingColumns.output) == 0 && steps.emptyRows.output == 0 && steps.missingKeys.output == 0 }}85 - id: prepare86 tool: filesystem.mkdir87 with:88 path: ${{ params.reportFolder }}89 - id: report90 name: Write the validation report91 needs: [valid, prepare]92 tool: filesystem.write93 with:94 path: ${{ params.reportFolder }}/validation.md95 overwrite: true96 content: |97 # Spreadsheet validation — ${{ env.DATETIME }}9899 Workbook: ${{ params.workbook }}100 Sheet: ${{ steps.read.output.sheet }}101 Columns: ${{ join(steps.header.output, ", ") }}102 Data rows read: ${{ len(steps.rows.output) }} (truncated: ${{ steps.read.output.truncated }})103104 - Missing required columns: ${{ len(steps.missingColumns.output) }} ${{ join(steps.missingColumns.output, ", ") }}105 - Fully empty rows: ${{ steps.emptyRows.output }}106 - Rows without a key value: ${{ steps.missingKeys.output }}107108 Valid: ${{ steps.valid.output }}109 - id: check110 name: The validation itself111 needs: [report]112 verify:113 - fileNonEmpty: ${{ params.reportFolder }}/validation.md114 - truthy: ${{ len(steps.rows.output) > 0 }}115 - truthy: ${{ !steps.read.output.truncated }}116 - equals: ["${{ len(steps.missingColumns.output) }}", 0]117 - equals: ["${{ steps.emptyRows.output }}", 0]118 - equals: ["${{ steps.missingKeys.output }}", 0]119 - id: result120 needs: [check]121 output:122 valid: true123 rows: ${{ len(steps.rows.output) }}124 columns: ${{ steps.header.output }}125 report: ${{ params.reportFolder }}/validation.md126 artifacts:127 - ${{ params.reportFolder }}/validation.md128