Workflow #2

Retrieving Currency Exchange Rate Data in Tabular format

Script Source (Table)

First, in the Script Source (Table) node, we retrieve data from an external API and return it in tabular format to the following node.

// Retrieve exchange rates from the API
var request = HttpUtil.get("https://boi.org.il/PublicApi/GetExchangeRates");
var jsonContent = JsonUtil.parse(request.asString().getBody());
var exchanges = jsonContent.exchangeRates;

// Create a table to store the data
var table = SuiteTable.create("MyTable");
table.addStringColumn("key");
table.addDoubleColumn("rate");
table.addStringColumn("lastUpdate");

// Populate the table with data from the API
for (var i = 0; i < exchanges.size(); i++) {
    var item = exchanges[i];
    var key = item.key.asText();
    var rate = item.currentExchangeRate.asDouble();
    var date = item.lastUpdate.asText();
    
    var row = {
        "key": key,
        "rate": rate,
        "lastUpdate": date
    };
    

    table.addRow(row);
}

// Filter table with some type of selection example:
var selection = table.stringColumn("key").isEqualTo("USD");
var filteredTable = table.where(selection);

// Return the filtered table
return filteredTable;

Custom Script

Next, we can connect the source script node to custom script node. It receives the table and returns it as it is. In the node itself though, we perform some calculations to set some properties for future use.

// Get a copy of the table reference from the previous node
var table = NodeInputReader.inputAsDataFrame();

//Get a new table sorted by the second column descendant (highest to lowest rates)
table = table.sortOn(-1);

//Get the values of the second column from the first row (Index 0, highest rate)
var maxcurr = table.column(0).get(0); // key
var maxvalue = table.column(1).get(0); // rate

Property.global.set("maxcurr", maxcurr); // max currency name
Property.global.set("maxvalue", maxvalue); // max currency value

return table;

Under properties tab in the Custom Script Node we create the following properties:

MAXCURR

Property.global.get("maxcurr");

MAXVALUE

Property.global.get("maxvalue");

LOCALDATE

LocalDate.now()

USD

var table = NodeInputReader.inputAsDataFrame(); 
//Get the value of the first column from the first row (USD rate) 
var maxValue = table.column(1).get(0);

EUR

var table = NodeInputReader.inputAsDataFrame(); 
//Get the values of the 4th column from the first row (EUR rate) 
var maxValue = table.column(1).get(3);

Data Prep

Next, the custom script node sends the table to data prep node in tabular format.

Unfortunately, the table we received contains dates with terrible format such as

2023-11-20T13:23:13.8650642Z.

We can use custom column to write a script for a new column with better date format.

// Parse the datetime string into an Instant
var instant = Instant.parse(lastupdate);

// Get the year, month and day components from the Instant
var year = InstantUtil.getYear(instant);

var month = InstantUtil.getMonth(instant);

var day = InstantUtil.getDayOfMonth(instant);

// Create a LocalDate object using the extracted year, month, and day
var localDate = LocalDate.of(year, month, day);

Now we have a new date column which displays the date in a clearer format.

Excel Builder

Next, we can connect the data prep node to excel builder node to get the table in excel format.

Send Email

Lastly, we can use the Send Email node to send an email at the end of our workflow with the excel file as an attachment and the following body:

${DATE} currency exchange rates:

USD: ${USD}
EUR: ${EUR}

Max is ${MAXCURR} at ${MAXVALUE}

Have great day!

Here we used the properties we defined earlier as global properties:

DATE, USD, EUR, MAXCURR, and MAXVALUE.

Extra: Scheduler

We can also set a scheduler to run this workflow on a daily basis and receive this email with up-to-date info about currency exchange rates with the following CRON expression

@daily or 0 0 8 * * *