Escribal
Back to Docs

Spreadsheet

Data validation

This group restricts what can be typed into cells and controls the messages shown around those rules. A data validation rule limits a range of cells to, for example, a list of allowed options, a whole number in a range, a text length, a date or time window, or the result of a custom formula. You can also attach an input tooltip and an error alert to a range, or remove validation again.

Prerequisite: before using any of these instructions, the workflow must already have an XLSX spreadsheet created or opened (with Create spreadsheet). Every instruction works on a sheet (tab) inside that spreadsheet, identified by name (Sheet1 by default), which must already exist.

These concepts recur across several instructions in this group; they are explained here once.

  • Document: indicates which document in the workflow the instruction applies to. If left empty, the current document is used. If the document doesn’t exist, the instruction simply does nothing.

  • The sheet must already exist: the Sheet name field chooses which tab the instruction works on, and defaults to Sheet1. If no sheet with that name is found, the instruction simply does nothing and the rest of the workflow carries on.

  • Range: the Range field is the block of cells the rule applies to, written in A1 style as two corners joined by a colon, like A1:A100 (the default); a single cell like B2 also works. The range identifies the rule: Set Input Message, Set Error Message and Remove Validation find a rule by the range it was created on, so A1:A100 and A1:A101 are different rules. Different ways of writing the same block do match, though: upper or lower case, dollar signs and the order of the two corners are all ignored, so a1:a100, $A$1:$A$100 and A100:A1 all find the rule created on A1:A100, and B2:B2 is the same as B2.

  • Re-applying replaces: running a Validate… instruction on a range that already has a rule replaces the old rule on that exact range.

  • Comparison (number, length, date and time rules): the Comparison field chooses how the value is checked, from a fixed list: Between (default) · Not between · Equal · Not equal · Greater than · Greater than or equal · Less than · Less than or equal. For Between and Not between both the minimum and maximum fields matter; for all the others only the first (minimum) field is used as the value to compare against, and the maximum is ignored.

  • Allow blank: when Allow blank is Yes (the default), an empty cell is accepted; when No, the cell must contain a value that passes the rule.

  • Dynamic values (@name): in any text field (sheet name, range, allowed values, dates, times, formula, message text…) you can type @ followed by the name of a value saved earlier in the workflow, and it will be replaced by that value.

  • “From variable” fields: the @name trick only works in text fields. The numeric fields (Minimum, Maximum, Min length, Max length) and the yes/no fields (Allow blank, Show message, Entire sheet) each have a companion “from variable” field:

    • If you leave it empty, the value chosen in the normal field is used.
    • If you fill it in with the name of a saved value, the engine ignores the normal field and reads the value from that variable.

    This is mainly for chaining instructions. If you only work with fixed values, you can ignore these fields.

Remove Validation .xlsx

Removes data validation from a range, or clears every validation on the sheet.

When to use it: to lift a restriction you set earlier.

Parameter Meaning Default Required
Document Document the instruction applies to (current document) No
Sheet name Tab to act on. Supports @name Sheet1 No
Range The range whose rule to remove (A1 style). Supports @name (empty) No
Entire sheet Yes = remove every validation rule on the sheet No No
Entire sheet (from variable) Reads the setting from a saved value (empty) No

Important behavior: to remove one rule, give its exact Range. To clear all rules on the sheet, turn Entire sheet on. If Range is left empty and Entire sheet is off, nothing happens — this protects against wiping every rule by accident when a dynamic range comes out empty. If the range doesn’t match any existing rule, nothing happens.

Set Error Message .xlsx

Sets the error alert shown when someone types an invalid value into a validated range.

When to use it: after creating a rule, to customize the pop-up that appears on a bad entry.

Parameter Meaning Default Required
Document Document the instruction applies to (current document) No
Sheet name Tab the range is on. Supports @name Sheet1 No
Range The validated range (must match the rule’s range). Supports @name A1:A100 No
Title Title of the error pop-up. Supports @name Invalid value No
Message Body text of the error pop-up. Supports @name The entered value is not valid. No
Style Severity: stop, warning or information stop No
Show message Yes = show the error alert on invalid entry Yes No
Show message (from variable) Reads the setting from a saved value (empty) No

Important behavior: Style stop blocks the invalid value; warning and information let the user keep it after confirming. Any unknown Style falls back to stop. If no rule exists yet on that exact range, an empty placeholder rule is created just to hold the message.

Set Input Message .xlsx

Sets the tooltip shown when a validated cell is selected.

When to use it: to give the user a hint before they type.

Parameter Meaning Default Required
Document Document the instruction applies to (current document) No
Sheet name Tab the range is on. Supports @name Sheet1 No
Range The validated range (must match the rule’s range). Supports @name A1:A100 No
Title Title of the tooltip. Supports @name Input No
Message Body text of the tooltip. Supports @name Enter a value. No
Show message Yes = show the tooltip when the cell is selected Yes No
Show message (from variable) Reads the setting from a saved value (empty) No

Important behavior: if no rule exists yet on that exact range, an empty placeholder rule is created just to hold the message.

Validate Date In Range .xlsx

Restricts a range to dates within (or outside) a window.

When to use it: to force valid calendar dates — for example only dates in the current year.

Parameter Meaning Default Required
Document Document the instruction applies to (current document) No
Sheet name Tab the range is on. Supports @name Sheet1 No
Range The cells to restrict (A1 style). Supports @name A1:A100 No
Comparison How to check the date (see Common concepts) between No
Min date Lower/target date, as YYYY-MM-DD. Supports @name 2000-01-01 No
Max date Upper date (only used for between/notBetween). Supports @name 2099-12-31 No
Allow blank Yes = allow empty cells Yes No
Allow blank (from variable) Reads the setting from a saved value (empty) No

Important behavior: dates are written YYYY-MM-DD. If a required date is empty or not a valid date, nothing happens. For between/notBetween both dates apply; every other comparison uses only Min date. Re-applying replaces any rule on the same range.

Validate Decimal Number .xlsx

Restricts a range to decimal numbers matching a comparison.

When to use it: for values that can have a fractional part, like prices or measurements.

Parameter Meaning Default Required
Document Document the instruction applies to (current document) No
Sheet name Tab the range is on. Supports @name Sheet1 No
Range The cells to restrict (A1 style). Supports @name A1:A100 No
Comparison How to check the number (see Common concepts) between No
Minimum Lower/target number 0 No
Maximum Upper number (only used for between/notBetween) 100 No
Allow blank Yes = allow empty cells Yes No
Minimum (from variable) Reads the minimum from a saved value (empty) No
Maximum (from variable) Reads the maximum from a saved value (empty) No
Allow blank (from variable) Reads the setting from a saved value (empty) No

Important behavior: decimals such as 3.5 are allowed. For between/notBetween both fields apply; other comparisons use only Minimum.

Validate Dropdown List .xlsx

Restricts a range to a fixed list of options, shown as a dropdown.

When to use it: to offer a small set of choices (statuses, categories…) and prevent typos.

Parameter Meaning Default Required
Document Document the instruction applies to (current document) No
Sheet name Tab the range is on. Supports @name Sheet1 No
Range The cells to restrict (A1 style). Supports @name A1:A100 No
Allowed values The options, comma-separated. Supports @name Option1,Option2,Option3 No
Show dropdown Yes = show the dropdown arrow in the cell Yes No
Allow blank Yes = allow empty cells Yes No
Show dropdown (from variable) Reads the yes/no value from a saved value (empty) No
Allow blank (from variable) Reads the setting from a saved value (empty) No

Important behavior: if Allowed values is empty, nothing happens. The list is stored inside the rule, so keep it reasonably short. Show dropdown No still enforces the list but hides the arrow. Re-applying replaces any rule on the same range.

Validate Text Length .xlsx

Restricts a range by the number of characters typed.

When to use it: to cap or require a text length — for example a code that must be exactly 5 characters.

Parameter Meaning Default Required
Document Document the instruction applies to (current document) No
Sheet name Tab the range is on. Supports @name Sheet1 No
Range The cells to restrict (A1 style). Supports @name A1:A100 No
Comparison How to check the length (see Common concepts) between No
Min length Lower/target character count 1 No
Max length Upper character count (only for between/notBetween) 255 No
Allow blank Yes = allow empty cells Yes No
Min length (from variable) Reads the value from a saved value (empty) No
Max length (from variable) Reads the value from a saved value (empty) No
Allow blank (from variable) Reads the setting from a saved value (empty) No

Important behavior: the rule checks the character count, not the value itself. For between/notBetween both fields apply; other comparisons use only Min length.

Validate Time In Range .xlsx

Restricts a range to times within (or outside) a window.

When to use it: for time-of-day entries, like a shift between 08:00 and 18:00.

Parameter Meaning Default Required
Document Document the instruction applies to (current document) No
Sheet name Tab the range is on. Supports @name Sheet1 No
Range The cells to restrict (A1 style). Supports @name A1:A100 No
Comparison How to check the time (see Common concepts) between No
Min time Lower/target time, 24-hour HH:MM. Supports @name 08:00 No
Max time Upper time (only used for between/notBetween). Supports @name 18:00 No
Allow blank Yes = allow empty cells Yes No
Allow blank (from variable) Reads the setting from a saved value (empty) No

Important behavior: times are written in 24-hour HH:MM. If a required time is empty or not a valid time, nothing happens. For between/notBetween both times apply; other comparisons use only Min time.

Validate Whole Number .xlsx

Restricts a range to whole numbers matching a comparison.

When to use it: for counts and quantities that can’t have a fractional part.

Parameter Meaning Default Required
Document Document the instruction applies to (current document) No
Sheet name Tab the range is on. Supports @name Sheet1 No
Range The cells to restrict (A1 style). Supports @name A1:A100 No
Comparison How to check the number (see Common concepts) between No
Minimum Lower/target number 0 No
Maximum Upper number (only used for between/notBetween) 100 No
Allow blank Yes = allow empty cells Yes No
Minimum (from variable) Reads the minimum from a saved value (empty) No
Maximum (from variable) Reads the maximum from a saved value (empty) No
Allow blank (from variable) Reads the setting from a saved value (empty) No

Important behavior: only whole numbers are accepted (no decimals). For between/notBetween both fields apply; other comparisons use only Minimum.

Validate With Formula .xlsx

Restricts a range using a custom formula that must return TRUE.

When to use it: for rules the other instructions can’t express — combined conditions, references to other cells, text patterns, and so on.

Parameter Meaning Default Required
Document Document the instruction applies to (current document) No
Sheet name Tab the range is on. Supports @name Sheet1 No
Range The cells to restrict (A1 style). Supports @name A1:A100 No
Formula A spreadsheet formula returning TRUE/FALSE, written for the top-left cell. Supports @name AND(A1>0,A1<1000) No
Allow blank Yes = allow empty cells Yes No
Allow blank (from variable) Reads the setting from a saved value (empty) No

Important behavior: write the formula as if for the range’s first (top-left) cell — the spreadsheet app applies it to each cell relative to that one, row by row. If Formula is empty, nothing happens. Re-applying replaces any rule on the same range.

This documentation is the help of the application, and it comes underCreative Commons Attribution 4.0.