UDFs - List of JSON objects to HTML table (with basic formatting!)

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.

Screen Shot 2022-02-11 at 23.34.41


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.

3 Likes

This is awesome to display data either in an email or generating a pdf using the HTML To PDF data operation

1 Like