XLSX

Data Validation

List, number, date, and custom data validations

List Validation

Create dropdown lists for cells:

{
  "worksheets": [
    {
      "name": "Sheet1",
      "dataValidations": [
        {
          "sqref": "B2:B5",
          "type": "list",
          "formula1": "\"Yes,No,Maybe\""
        }
      ],
      "children": [
        { "cells": [{ "value": "Question" }, { "value": "Answer" }] },
        { "cells": [{ "value": "Do you agree?" }, { "value": null }] }
      ]
    }
  ]
}

{
  name: "Sheet1",
  dataValidations: [
    {
      type: "list",
      sqref: "B2:B5",
      formula1: '"Yes,No,Maybe"',
    },
  ],
  children: [
    { cells: [{ value: "Question" }, { value: "Answer" }] },
    { cells: [{ value: "Do you agree?" }, { value: null }] },
  ],
}

Number Validation

{
  "worksheets": [
    {
      "name": "Sheet1",
      "dataValidations": [
        {
          "sqref": "A2:A100",
          "type": "whole",
          "operator": "between",
          "formula1": "0",
          "formula2": "100",
          "showErrorMessage": true,
          "errorTitle": "Invalid",
          "error": "Must be between 0 and 100"
        }
      ],
      "children": [{ "cells": [{ "value": "Score" }] }, { "cells": [{ "value": 85 }] }]
    }
  ]
}

dataValidations: [
  {
    type: "whole",
    operator: "between",
    sqref: "A2:A100",
    formula1: "0",
    formula2: "100",
    showErrorMessage: true,
    errorTitle: "Invalid",
    error: "Must be between 0 and 100",
  },
],

Validation Types

Type	`type` value	Description
List	`"list"`	Dropdown from values
Whole Number	`"whole"`	Integer validation
Decimal	`"decimal"`	Decimal number validation
Date	`"date"`	Date validation
Time	`"time"`	Time validation
Text Length	`"textLength"`	Text length validation
Custom	`"custom"`	Custom formula validation

Operators: between, notBetween, equal, notEqual, greaterThan, lessThan, greaterThanOrEqual, lessThanOrEqual.

Conditional Formatting

Apply formatting rules based on cell values:

{
  "worksheets": [
    {
      "name": "Sheet1",
      "conditionalFormats": [
        {
          "sqref": "B2:B5",
          "rules": [{ "type": "cellIs", "operator": "greaterThan", "formulas": ["100"] }]
        }
      ],
      "children": [
        { "cells": [{ "value": "Name" }, { "value": "Score" }] },
        { "cells": [{ "value": "Alice" }, { "value": 150 }] },
        { "cells": [{ "value": "Bob" }, { "value": 42 }] }
      ]
    }
  ]
}

conditionalFormats: [
  {
    sqref: "B2:B5",
    rules: [
      { type: "cellIs", operator: "greaterThan", formulas: ["100"] },
    ],
  },
],

DataValidation Options Reference

Option	Type	Description
`sqref`	`string`	Cell range, e.g. `"A1:A10"`
`type`	`string`	Validation type (see table above)
`operator`	`string`	Comparison operator
`formula1`	`string`	First formula or value
`formula2`	`string`	Second formula (for `between`)
`allowBlank`	`boolean`	Allow blank cells
`showErrorMessage`	`boolean`	Show error alert
`errorTitle`	`string`	Error dialog title
`error`	`string`	Error message
`showInputMessage`	`boolean`	Show input message
`promptTitle`	`string`	Input message title
`prompt`	`string`	Input message text