|
<< Click to Display Table of Contents >> Navigation: Reference > Transforms > Javascript |
Create a custom transform using Javascript (ECMAScript).
Easy Data Transform allows you to carry out a wide range of data transformations without programming. But sometimes you might need a specialist transformation that can't be done with the built-in transforms. For that you can use the Javascript transform. It allows you to write the body of a Javascript function, to calculate a value for each row in a new column. Existing column values can be used as variables.
Javascript is a fully-fledged programming language and can perform arbitrarily complex transforms. It can handle numbers, dates and text.
This is an experimental feature and might change in future releases.
One.
To multiply the value in column 'items' by the value in column 'item price':
return $(items) * $(item price);
To concatenate 'last' and 'first' columns with a comma and a space:
return $(last) + ', ' + first;
To calculate the biggest of columns 'v1' and 'v2':
return Math.max( $(v1), $(v2) );
To determine whether phone numbers in the 'phone_num' column are valid using a regular expression:
const validPhoneNum = /^[\+]?[(]?[0-9]{3}[)]?[-\s\.]?[0-9]{3}[-\s\.]?[0-9]{4,6}$/;
if ( validPhoneNum.test( $(phone_num) ) )
return "valid";
else
return "invalid";
To calculate the number of years difference between dates in column 1 and column 2:
return new Date( $(1) ).getFullYear() - new Date( $(2) ).getFullYear();
To calculate the number of whole days difference between a date in the 'created' column and today (negative for future dates):
return Math.floor( ( new Date() - new Date( $(created) ) ) / ( 1000*60*60*24 ) );
To reverse the text in the 'key' column:
var newString = $(key);
for (var i = a.length - 1; i >= 0; i--) {
newString += a[i];
}
return newString;
•Enter your script into the Javascript field. The script should be the body of a Javascript function.
•Select a column from Insert variable to add that variable into the Javascript field at the current cursor position.
•Click the Evaluate button to evaluate your Javascript expression over every row and show any errors.
•You can reference column values by their name (e.g. $(item cost) for the 'item cost' column) or index (e.g. $(1) for the first column). The column name is case sensitive. You will get a warning if more than one column has the same name.
•The Javascript transform is calculated every time:
oThe Evaluate button is pressed.
oThe Javascript transform item is unselected in the Center pane and script changes have been made without the Evaluate button being clicked.
oThe item upstream of it changes.
•The new column is added at the right end. You can change the column order with Reorder Cols and the column name with Rename Col.
•Any errors from the Javascript engine are shown in a message window when Evaluate is clicked.
•The Javascript Date() object evaluates to the number of milliseconds since 1 January 1970 UTC. Date() is the current date.
•Date values passed to Javascript Date() objects should be in ISO ('yyyy-mm-dd') format, e.g. '2020-01-31' (not '2020-1-31').
•If you want to carry out your transform across more than one dataset, you should Join them first.
•The Javascript transform is very versatile and quite fast. But is not as fast as built-in transforms. So we recommend you use built-in transforms where possible.
•Javascript running in Easy Data Transform is not 'sandboxed' and has the same privileges as the Easy Data Transform executable. However the Javascript does not have access to window(), XMLHttpRequest() or ActiveXObject(). So we aren't aware of any way that a bad actor could damage your system from Javascript sent in a .transform file.
•Javascript is far too big a topic to cover here. However there are many detailed resources online. If you are stuck contact support.
•If you only want to combine text from columns, use the simpler Substitute transform.