Data validation

Validation turns a spreadsheet into a form. It restricts what a cell accepts and explains the rule when someone gets it wrong, which is worth doing on any file you send out to be filled in. All three rule types share the same range keys and error keys, and differ only in what they check.

int

required

First row of the range.

int

required

First column.

int

required

Last row.

int

required

Last column.

str

Heading of the rejection dialog.

str

Body text. Say what is allowed, not just that the entry was wrong.

"list"

required

list[str]

required

The permitted values.

Show the arrow. Leave it on unless you want the constraint hidden.

data_validations=[{
    "start_row": 2, "start_col": 0,
    "end_row": 100, "end_col": 0,
    "type": "list",
    "items": ["Active", "Pending", "Closed"],
    "show_dropdown": True,
    "error_title": "Invalid status",
    "error_message": "Choose Active, Pending or Closed.",
}]

Numeric ranges

"whole_number" | "decimal"

required

whole_number rejects anything with a fractional part. decimal allows it.

float

required

Lower bound, inclusive.

float

required

Upper bound, inclusive.

data_validations=[{
    "start_row": 2, "start_col": 1,
    "end_row": 100, "end_col": 1,
    "type": "whole_number",
    "min": 1, "max": 100,
    "error_title": "Out of range",
    "error_message": "Enter a whole number between 1 and 100.",
}]

Text length

"text_length"

required

int

required

Fewest characters.

int

required

Most characters.

data_validations=[{
    "start_row": 2, "start_col": 0,
    "end_row": 100, "end_col": 0,
    "type": "text_length",
    "min": 3, "max": 20,
    "error_title": "Invalid username",
    "error_message": "Usernames are 3 to 20 characters.",
}]

Practical notes

Validate past your data

Apply the rule well beyond the last populated row, to row 100 or 1000, so it still holds when someone adds entries.

Validation isn't security

Rules apply to typing. Pasting into a validated cell can bypass them, and any recipient can delete the rule. Treat it as guidance and validate again when the file comes back.

Write useful messages

“Invalid entry” tells nobody anything. Name the permitted values or the bounds.

Get started

Guides

Recipes

Reference

About

Data validation

Numeric ranges

Text length

Practical notes

​Dropdown lists

​Numeric ranges

​Text length

​Practical notes

Dropdown lists

Numeric ranges

Text length

Practical notes