This sample UDF takes a List of JSON as input and returns an HTML table, with some basic formatting applied.
Given the following input (dataType: List of JSON):
[
{
"my": "this",
"csv": "is",
"headers": "one row"
},
{
"my": "this",
"csv": "is",
"headers": "another row"
},
{
"my": "and this",
"csv": "here is",
"headers": "yet another row"
}
]
Let’s build a UDF that takes the List of JSON and returns an HTML table. To build it out we’ll be concatenating the text strings:
- style tag
- table tag and children:
- theader
- tbody
The Function Body is:
"<style>
.tb { border-collapse: collapse; }
.tb th, .tb td { padding: 5px; }
.tb th { background-color: #777; border: solid 1px #f4f4f4; color: white;}
.tb td { border: solid 1px #777; }
</style>
<table class=\"tb\">
{{
CONCAT("<thead><tr>",
JOIN(
FROM th IN KEYS(json[0])
SELECT "<th title=\"{{th}}\">{{th}}</th>"
),
"</tr></thead>"
)
}}
<tbody>{{
JOIN(
FROM
tr
IN
FROM obj IN json SELECT JOIN(VALUES(obj), ",")
SELECT
"<tr>
{{JOIN(FROM td IN SPLIT(tr, ",") SELECT "<td>{{td}}</td>")}}</tr>"
)
}}
</tbody>
</table>"
And the Function’s body should be set to Text (datatype: Text), the Text HTML output will look like:
"<style>
.tb { border-collapse: collapse; }
.tb th, .tb td { padding: 5px; }
.tb th { background-color: #777; border: solid 1px #f4f4f4; color: white;}
.tb td { border: solid 1px #777; }
</style>
<table class="tb">
<thead>
<tr>
<th title="my">my</th>
<th title="csv">csv</th>
<th title="headers">headers</th>
</tr>
</thead>
<tbody>
<tr>
<td>this</td>
<td>is</td>
<td>one row</td>
</tr>
<tr>
<td>this</td>
<td>is</td>
<td>another row</td>
</tr>
<tr>
<td>and this</td>
<td>here is</td>
<td>yet another row</td>
</tr>
</tbody>
</table>"
The HTML can then be used as the body of an HTML email to display reporting data, etc.
TIP: Play around with the UDF’s Inputs to allow dynamic formatting (<style></style>), or getting the headers and the table rows from different sources.