Data validation

Constrain what people can enter into a range

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.

start_row · int · required

First row of the range.

start_col · int · required

First column.

end_row · int · required

Last row.

end_col · int · required

Last column.

error_title · str

Heading of the rejection dialog.

error_message · str

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

Dropdown lists

type · "list" · required

items · list[str] · required

The permitted values.

show_dropdown · bool

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

type · "whole_number" | "decimal" · required

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

min · float · required

Lower bound, inclusive.

max · 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

type · "text_length" · required

min · int · required

Fewest characters.

max · 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.