Workflow #2 — Exchange Rate Email Report

This workflow retrieves currency exchange rates into a table, sets global properties for the highest rate, reformats dates, builds an Excel file, and sends the result by email on a daily schedule.

Nodes used: Source Script (Table) → Custom Script → Data Prep → Excel Builder → Send Email + Scheduler

---

Step 1 — Source Script (Table)

Use a Source Script (Table) node to call an external API and return the data directly as a table.

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

// Create a table with key, rate, and lastupdate columns
var table = SuiteTable.create("MyTable");
table.addStringColumn("key");
table.addDoubleColumn("rate");
table.addStringColumn("lastupdate");

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

// Filter to USD only
var selection = table.stringColumn("key").isEqualTo("USD");
return table.where(selection);

Step 2 — Custom Script (Set Properties)

A Custom Script node receives the table, sorts it to find the highest rate, and stores values as global Properties for use in later nodes.

var table = NodeInputReader.inputAsDataFrame();

// Sort descending by the rate column
table = table.sortOn(-1);

// Store the top currency and its rate as global properties
var maxcurr = table.column(0).get(0);
var maxvalue = table.column(1).get(0);
Property.global.set("maxcurr", maxcurr);
Property.global.set("maxvalue", maxvalue);

return table;

In the node's Properties tab, define the following property expressions:

Property	Expression
MAXCURR	Property.global.get("maxcurr");
MAXVALUE	Property.global.get("maxvalue");
LOCALDATE	LocalDate.now()
USD	var table = NodeInputReader.inputAsDataFrame(); table.column(1).get(0);
EUR	var table = NodeInputReader.inputAsDataFrame(); table.column(1).get(3);

Step 3 — Data Prep (Reformat Date)

Add a custom column to convert the raw ISO-8601 timestamp into a readable LocalDate:

var instant = Instant.parse(lastupdate);
var year = InstantUtil.getYear(instant);
var month = InstantUtil.getMonth(instant);
var day = InstantUtil.getDayOfMonth(instant);
LocalDate.of(year, month, day);

Step 4 — Excel Builder

Connect an Excel Builder node to export the table as an Excel file.

Step 5 — Send Email

Use a Send Email node to send the Excel file as an attachment. The email body references the global properties set in Step 2:

${DATE} currency exchange rates:

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

Max is ${MAXCURR} at ${MAXVALUE}

Have a great day!

Extra — Scheduler

Add a Scheduler trigger to run the workflow daily:

0 0 8 * * *

This runs the workflow every day at 08:00.

Summary

This workflow demonstrates how to combine Source Script, Custom Script, Data Prep, and destination nodes to build an automated reporting pipeline — fetching data, computing values, storing them as properties, and delivering a formatted email on a schedule.