SPB Git

Repository Public License

14-spreadsheet-validation.yaml 5.1 KB 128 lines yaml

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
Files main
Menu

Theme