Transform Query to HTML Table

[pskeens]

I have a query that pulls changes from an audit table. I want to put this change summary into the body of an email and send it with the update notification.

Transformer Code:

---

> function transformQuery7Results() {
>     const queryResults = {{ query7.dataArray }};
>     const gridHtml = queryResults.map(result => {
>         return `
>             <tr>
>                 <td>${result.Field}</td>
>                 <td>${result.OldValue}</td>
>                 <td>${result.NewValue}</td>
>             </tr>
>         `;
>     }).join('');
> 
>     const htmlTable = `
>         <table>
>             <tr>
>                 <th>Field.value</th>
>                 <th>OldValue.value</th>
>                 <th>NewValue.value</th>
>             </tr>
>             ${gridHtml}
>         </table>
>     `;
>     return htmlTable;
> }

---

return transformQuery7Results();

It "kind of" works, but it does not format the data as a table with rows from the array.

It takes the values from each array group and puts it in the table under the header together.

Any assistance would be appreciated.

[pskeens]

I have also used this code and it does the exact same thing. Puts all of the Values into one cell under each header.

const queryResult = {{ query7.dataArray }}; // Replace "query1" with the name of your query

let html = '<table border="1"><thead><tr>';

// Construct table headers
Object.keys(queryResult[0]).forEach(key => {
  html += `<th>${key}</th>`;
});

html += '</tr></thead><tbody>';

// Construct table rows
queryResult.forEach(row => {
  html += '<tr>';
  Object.values(row).forEach(value => {
    html += `<td>${value}</td>`;
  });
  html += '</tr>';
});

html += '</tbody></table>';

return html;

[joeBumbaca]

Hey @pskeens can you share a bit more about how you have this set up? I ran the transformation in a JS query and sent the email via Retool Email and it seems to work:

Can you share what email service you are using, and how you are using the transformed data in the email body? Thanks!