Skip to main content

Transform data with JavaScript

Learn how to write JavaScript to manipulate data.

Transformers enable you to write larger, reusable, blocks of JavaScript code that can be referenced anywhere in the app. You can use use JavaScript libraries and utility methods, and Transformers are scoped to the page.

Use transformers whenever you need to reuse the same blocks of JavaScript code in an app.

Create a transformer

In the Code panel, click + > Transformer to create a JavaScript transformer.

Transformers are written in JavaScript. The return value at the end is what becomes the value of the transformer in the rest of the app. Here's an example of using a transformer to split out the first name from a string value in a table column.

Example transformer and usage

You can use {{ yourTransformerName.value }} to reference the transformer's value. This is how the name is displayed in the Text component in the example above.

You can also select Preview to check the results of a transformer, but you must Save the transformer to use its value in the rest of your app.

Delete a transformer

Select the transformer you want to delete in the bottom panel. Then click ••• > Delete to remove the transformer.

Query transformers

Transformers are often used to change the results of a query into a different format. When you attach a transformer directly to a query, it changes the value of the query everywhere. This means query1.data uses the results of the query after the transformer has been applied.

Here's an example using query transformers to return a specific field from the query results.

After writing this query transformer, instead of writing query1.data.result, you can use query1.data.

Query transformers work the same way as regular transformers. In the transformer, you are provided a field data which contains the results of the query without any modification. The return value from the transformer you write becomes the value of yourQuery.data in the rest of the app.

If you want the unaltered results from the query, you can use yourQuery.rawData to access the results before transformation.

Transformers are read-only

Transformers in Retool are read-only: they cannot affect the values of other components or state in Retool. They can only update their own value using a return statement. This means that if you're writing JS in a Transformer, you cannot:

  • Set the value of a variable
  • Set the value of a component
  • Trigger a query

To do any of these, you'll need to use a JS Code query.

Examples

The following show common use cases for transformers.

Return data

To return data from the transformer, use the return syntax.

const val = {{textinput1.value}}
return parseFloat(val) * 10

Include a variable from your app

Use the {{ }} syntax to include a variable used elsewhere on the your app. The transformer will automatically re-run when the variable value changes.

const val = {{textinput1.value}}
return parseFloat(val) * 10

Use query data

To access the query results, use {{sqlQuery.data}}. If you need to access the original response of the query, use {{sqlQuery.rawData}}.

// Retrieves query data
{{ sqlQuery.data }}

// Retrieves the original query response
{{ sqlQuery.rawData }}

Convert query data to an array

If you want to use query data as an array of objects, use formatDataAsArray.

{{ formatDataAsArray(sqlQuery.data) }}

Sort an API query

If your API doesn't support filtering using parameters, you can sort using the sort() syntax.

const data = {{query1.data}}

return data.sort((e1, e2) => e1.fieldToSortBy - e2.fieldToSortBy)

Modify query data

To modify query data, use the map() syntax. The following example adds 1 to every query data value.

const data = {{query1.data}}
return data.map(x => x + 1)

Filter an API query

If your API doesn't support filtering using parameters, you can filter using the .filter() syntax.

const data = {{query1.data}}

return data.filter(f => f.fieldToFilterBy.toLowerCase().indexOf(textInput3.value.toLowerCase()) !== -1 )

Join two queries

To join the result of a REST query with a SQL query in to one table, use the zipWith() syntax.

var sqlDataSorted = {{formatDataAsArray(query1.data)}}.sort((e1, e2) =>  e1.id - e2.id)
var apiDataSorted = {{query2.data}}.sort((e1, e2) => e1.id - e2.id)

_.zipWith(sqlDataSorted, apiDataSorted, (sql, api) => Object.assign({}, api, sql))

Failure conditions

You can use failure conditions in queries to mark your queries as failing. In the Response tab of the query editor, add conditions to the Failure conditions section. The keys in this section are the conditions, and the values are the error messages to display. If any condition results in a truthy value, Retool shows the error message.

For example, to throw an error if your API returns fewer results than you've configured in a Text Input, set a failure condition of {{ data.results.length < textinput1.value }}.

Example of a query failure condition

To use similar logic outside of the Failure conditions section, you need to include the query name (e.g., {{ queryName.data.results.length < textinput1.value }}).

Failure condition properties

Within the failure conditions inputs, you can access .data, .error and .metadata properties. Failure conditions are especially useful to set when your resource always returns a success. For example, most GraphQL APIs return a 200 status code, even when the response contains errors.

Use .data to reference the data returned by your query (rows in a database, the response of an API, etc.). The .error property extracts an error out of the query's response. The .metadata property contains metadata about your query's response, such as response headers for a REST API. Note that .metadata is generally only available for REST and GraphQL queries.

The green evaluation box for failure conditions autocompletes your data based on the last run of the query. For example, {{ data.error }} in the autocomplete shows you the .error field from the last query result.