Appearance
Most GREL failures come from four causes: nulls propagating through a chain, case-sensitive function names, literal replace() not matching hidden characters, and toDate() parsing the wrong format. Read the live preview's error count first, then isolate the failing function by simplifying the expression until the preview turns green. Fix the root cause rather than wrapping the symptom.
How do you read GREL errors in the first place?
Open Edit cells > Transform. The dialog has a preview grid showing each row's original value and the computed result, plus an error counter at the top. A red Error in a result cell tells you which rows fail. Click into the expression box and the message appears below it. Never click OK while the counter shows errors — you would write Error strings into your data.
Why does my expression return null everywhere?
Null is the most common silent failure. GREL functions return null on blank input, and null passed to the next function usually returns null again, so one empty cell can blank a whole chain. Guard the input:
text
if(isBlank(value), "", value.trim().toTitlecase())For chained slicing, check length first:
text
if(length(value) >= 4, value[0,4], value)This keeps short or empty values from throwing an index error.
Fixing "Unknown function" and case mistakes
GREL is case-sensitive and does not share JavaScript's names. The usual culprits:
| You typed | GREL wants |
|---|---|
toUpperCase() | toUppercase() |
toLowerCase() | toLowercase() |
substring() | substring() or value[start,end] |
parseInt() | toNumber() |
trim (with space) | trim() |
If you prefer JavaScript or Python syntax, change the Language dropdown at the top of the dialog rather than fighting GREL's names.
Why doesn't replace() change my text?
value.replace(" ", " ") looks right but often does nothing because the real character is not a normal space. Archival exports are full of non-breaking spaces ( ), zero-width spaces and trailing whitespace. Two robust fixes:
text
value.replace(/ /, " ").trim()or switch to a regex that collapses any whitespace run:
text
value.replace(/\s+/, " ").trim()The slash syntax /.../ enables regular expressions; quotes alone do a literal match.
How do you stop toDate() from mangling dates?
toDate() guesses the format and frequently swaps day and month — 03/04/1850 becomes April rather than March. Always pass the format explicitly:
text
toDate(value, "dd/MM/yyyy")For mixed or partial historical dates, parse conditionally and leave failures as text so you can find them:
text
if(value.match(/\d{4}/), toDate(value, "yyyy"), value)Then facet the column by type (isError(value) or a text facet) to isolate the rows that did not parse, and handle those outliers by hand.
value versus cell, and other object confusions
Use value for the current cell's plain content. Reach for cell only when you need its sub-objects: cell.recon.match.id for a reconciled QID, or cell.cross("other project","key") for a cross-project lookup. Mixing them up — for example writing cell.trim() — throws an error because cell is an object, not a string.
A repeatable troubleshooting routine
- Read the error counter; if zero, your worry is logic not syntax.
- Simplify: cut the expression back to
valueand rebuild one function at a time until the error returns. - Guard blanks with
if(isBlank(value), ...). - Trim before any
replace()or comparison. - Pin formats in
toDate()andtoNumber(). - Facet the survivors to find the genuine outliers, then fix those separately.
Key Takeaways
- Read the preview's error counter before clicking OK; never write
Errorinto your cells. - Guard blank inputs with
if(isBlank(value), ...)to stop null propagating through a chain. - GREL names are case-sensitive —
toUppercase,toNumber, not the JavaScript spellings. replace()fails on hidden characters; trim first or use a/\s+/regular expression.- Always pass an explicit format to
toDate()to avoid day/month swaps on historical dates. - Simplify a failing expression to
valueand rebuild it function by function to find the culprit.
Frequently Asked Questions
Why does my GREL expression return null for some cells?
Usually because the cell is empty or a prior function in the chain already returned null, and null propagates. Guard with an if test such as if(isBlank(value), '', value) or check length before slicing.
What causes an 'Error: Unknown function' message in GREL?
GREL function names are case sensitive and must use the exact spelling, for example toUppercase not toUpperCase. A typo or a function from another language such as JavaScript triggers the unknown-function error.
How do I test a GREL expression before applying it?
The Transform dialog shows a live preview of the first rows with old and new values side by side. Always read that preview and check the error count at the top before clicking OK.
Why does value.replace() not change anything?
replace does a literal substring match by default, so hidden characters such as non-breaking spaces or trailing whitespace stop it matching. Trim first, or switch to a regular expression with the slash syntax.
How do I handle dates that GREL cannot parse?
Wrap toDate in a conditional and supply the format explicitly, for example toDate(value, 'dd/MM/yyyy'). If parsing still fails the cell stays as text, so facet the unparsed values and fix the outliers separately.
What is the difference between value and cell in GREL?
value is the plain content of the current cell, while cell is an object exposing extras like cell.recon for reconciliation data and cell.cross for lookups. Use value for ordinary text work and cell for richer operations.