GREL Variables

Refine provides a set of Variables to access your data inside of expressions.

The most commonly used variables include:

  • value – the value of the current cell, generally your starting point!
  • cells – an array of all the cells of the current row.
    • Use bracket or dot notation to access values from other columns.
    • e.g. cells["example_col"].value or cells.example_col.value.
  • rowIndex – index number of the current row, starting from 0 (or row.index).

Less commonly used:

  • columnName – name of the column of the current cell.
  • row – access details associated with the row.
    • e.g. row.flagged (true/false), row.starred (true/false), row.columnNames (array of all columns).
  • row.record – access fields for record containing the current row.
  • cell.recon – access reconciliation fields for the current cell.
row of spreadsheet table showing cell labelled with 'value' and 'cells[two].value' in the next cell to demonstrate variables

Example

GREL uses + to concatenate values. Use quotes around string values you want to add, e.g. value + "-example".

Let’s combine the values from different columns to create a unique id:

  • Edit column > Add column based on this column…
  • Name the column “newid”
  • rowIndex + "_" + value + "_" + cells["example"].value

Exercises

pi_dogs annotations

The table has some extra columns at the end without names. The content seems like it belongs in the last named column, “Annotations”. Move the values over.

On “Annotations” column, first facet by blank to ensure you are not over writing anything, then transform cells using cells["Column 64"].value.

pma_cataloguedeluxeo00unse id

Create a new unique id field for all rows based on a string and some existing fields.

From “Page_Num”, create a new column using "deluxe_" + value + "_" + cells.Item_Num.value. Check for with duplicates facet for unique-ness.

Expressions Basics GREL Functions