Aller au contenu principal
Version: 3.4

General Refine Expression Language

Basics#

GREL (General Refine Expression Language) is designed to resemble Javascript. Formulas use variables and depend on data types to do things like string manipulation or mathematical calculations:

ExampleOutput
value + " (approved)"Concatenate two strings; whatever is in the cell gets converted to a string first
value + 2.239Add 2.239 to the existing value (if a number); append text "2.239" to the end of the string otherwise
value.trim().length()    Trim leading and trailing whitespace of the cell value and then output the length of the result
value.substring(7, 10)Output the substring of the value from character index 7, 8, and 9 (excluding character index 10)
value.substring(13)Output the substring from index 13 to the end of the string

Note that the operator for string concatenation is + (not “&” as is used in Excel).

Evaluating conditions uses symbols such as <, >, *, /, etc. To check whether two objects are equal, use two equal signs (value=="true").

See the GREL functions page for a thorough reference on each function and its inputs and outputs. Read on below for more about the general nature of GREL expressions.

Syntax#

In GREL, functions can use either of these two forms:

  • functionName(arg0, arg1, ...)
  • arg0.functionName(arg1, ...)

The second form is a shorthand to make expressions easier to read. It simply pulls the first argument out and appends it to the front of the function, with a dot:

Dot notationFull notation
value.trim().length()length(trim(value))
value.substring(7, 10)substring(value, 7, 10)

So, in the dot shorthand, the functions occur from left to right in the order of calling, rather than in the reverse order with parentheses. This allows you to string together multiple functions in a readable order.

The dot notation can also be used to access the member fields of variables. For referring to column names that contain spaces (anything not a continuous string), use square brackets instead of dot notation:

ExampleDescription
FirstName.cellsAccess the cell in the column named “FirstName” of the current row
cells["First Name"]Access the cell in the column called “First Name” of the current row

Square brackets can also be used to get substrings and sub-arrays, and single items from arrays:

ExampleDescription
value[1,3]A substring of value, starting from character 1 up to but excluding character 3
"internationalization"[1,-2]Will return “nternationalizati” (negative indexes are counted from the end)
row.columnNames[5]Will return the name of the fifth column

Any function that outputs an array can use square brackets to select only one part of the array to output as a string (remember that the index of the items in an array starts with 0).

For example, partition() would normally output an array of three items: the part before your chosen fragment, the fragment you've identified, and the part after. Selecting only the third part with "internationalization".partition("nation")[2] will output “alization” (and so will [-1], indicating the final item in the array).

Controls#

GREL offers controls to support branching and looping (that is, “if” and “for” functions), but unlike functions, their arguments don't all get evaluated before they get run. A control can decide which part of the code to execute and can affect the environment bindings. Functions, on the other hand, can't do either. Each control decides which of their arguments to evaluate to value, and how.

Please note that the GREL control names are case-sensitive: for example, the isError() control can't be called with iserror().

if(e, eTrue, eFalse)#

Expression e is evaluated to a value. If that value is true, then expression eTrue is evaluated and the result is the value of the whole if() expression. Otherwise, expression eFalse is evaluated and that result is the value.

Examples:

Example expressionResult
if("internationalization".length() > 10, "big string", "small string")“big string”
if(mod(37, 2) == 0, "even", "odd")“odd”

Nested if (switch case) example:

if(value == 'Place', 'http://www.example.com/Location',
    if(value == 'Person', 'http://www.example.com/Agent',
    if(value == 'Book', 'http://www.example.com/Publication',
null)))

with(e1, variable v, e2)#

Evaluates expression e1 and binds its value to variable v. Then evaluates expression e2 and returns that result.

Example expressionResult
with("european union".split(" "), a, a.length())2
with("european union".split(" "), a, forEach(a, v, v.length()))[ 8, 5 ]
with("european union".split(" "), a, forEach(a, v, v.length()).sum() / a.length())6.5

filter(e1, v, e test)#

Evaluates expression e1 to an array. Then for each array element, binds its value to variable v, evaluates expression test - which should return a boolean. If the boolean is true, pushes v onto the result array.

ExpressionResult
filter([ 3, 4, 8, 7, 9 ], v, mod(v, 2) == 1)[ 3, 7, 9 ]

forEach(e1, v, e2)#

Evaluates expression e1 to an array. Then for each array element, binds its value to variable v, evaluates expression e2, and pushes the result onto the result array.

ExpressionResult
forEach([ 3, 4, 8, 7, 9 ], v, mod(v, 2))[ 1, 0, 0, 1, 1 ]

forEachIndex(e1, i, v, e2)#

Evaluates expression e1 to an array. Then for each array element, binds its index to variable i and its value to variable v, evaluates expression e2, and pushes the result onto the result array.

ExpressionResult
forEachIndex([ "anne", "ben", "cindy" ], i, v, (i + 1) + ". " + v).join(", ")1. anne, 2. ben, 3. cindy

forRange(n from, n to, n step, v, e)#

Iterates over the variable v starting at from, incrementing by the value of step each time while less than to. At each iteration, evaluates expression e, and pushes the result onto the result array.

forNonBlank(e, v, eNonBlank, eBlank)#

Evaluates expression e. If it is non-blank, forNonBlank() binds its value to variable v, evaluates expression eNonBlank and returns the result. Otherwise (if e evaluates to blank), forNonBlank() evaluates expression eBlank and returns that result instead.

Unlike other GREL functions beginning with “for,” forNonBlank() is not iterative. forNonBlank() essentially offers a shorter syntax to achieving the same outcome by using the isNonBlank() function within an “if” statement.

isBlank(e), isNonBlank(e), isNull(e), isNotNull(e), isNumeric(e), isError(e)#

Evaluates the expression e, and returns a boolean based on the named evaluation.

Examples:

ExpressionResult
isBlank("abc")false
isNonBlank("abc")true
isNull("abc")false
isNotNull("abc")true
isNumeric(2)true
isError(1)false
isError("abc")false
isError(1 / 0)true

Remember that these are controls and not functions: you can’t use dot notation (for example, the format e.isX() will not work).

Constants#

NameMeaning
trueThe boolean constant true
falseThe boolean constant false
PIFrom Java's Math.PI, the value of pi (that is, 3.1415...)