Cleaning
Fill Null Values
Replaces null, undefined and empty values in one column with fixed text or a value worked out from the row.
When to use it
Use it before grouping, joining or exporting, when blank cells would otherwise produce empty keys or gaps in a report.
In a pipeline
Google SheetsFill Null ValuesGroup By
Settings
| Setting | Type | Required | Default or hint |
|---|---|---|---|
| Column | select | Yes | — |
| Fill Value | textarea | Yes | Text, or a formula: type { for columns, $ for variables |
Example
Give blank regions a placeholder
- Column=Region
- Fill Value=Unknown
| Order | Region | Amount |
|---|---|---|
| A-1 | West | 120 |
| A-2 | — | 80 |
| Order | Region | Amount |
|---|---|---|
| A-1 | West | 120 |
| A-2 | Unknown | 80 |
Only the blank cell changes; existing values are left alone, including the number 0.
Worth knowing
- A cell holding only spaces is not treated as empty, so put Trim Whitespace before this node.
- The fill value can be a formula: type { to pick a column and $ to pick a variable, e.g. {First Name} & " " & {Last Name} or IF({Qty} > 0, "In stock", "Out").
- Text that mentions columns but is not a formula, such as Dear {First Name}, is filled in as a template.
- Plain text is written exactly as typed.
- Zero and the text false count as real values and are not replaced.