Solved: How can I create an HTML table in an email based o...

ianwuk · ‎12-15-2022

I am building a flow that reads data from a spreadsheet and if the month name in the spreadsheet is the next month then send an email.

E.g. The current month is December. If data in a specific column is equal to 'January' then send an email with details of the rows in question.

I need help with creating the HTML table using the data from Excel.

The spreadsheet compares the data of a column from an Excel spreadsheet and to see if it matches a stored date variable of next month ('January') - is this correct? When I test it, my spreadsheet has four entries with 'January', but the email includes more entries than that.

It then creates an HTML table of that data - this is what I also need help with.

An email is then sent with that output (the HTML table).

Any help on how to fix this is much appreciated - thanks!

Amit_Sharma · ‎12-15-2022

Hi @ianwuk,

I have Created sample for you.

- Use expression

item()?['Column name of Table']

Output

Could you please show me your flow where you have getting error.

-------------------------------------------------------------------------
If I have answered your question, please mark post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

Amit_Sharma · ‎12-15-2022

Hi @ianwuk,

I have Created sample for you.

- Use expression

item()?['Column name of Table']

Output

Could you please show me your flow where you have getting error.

-------------------------------------------------------------------------
If I have answered your question, please mark post as Solved.
If you like my response, please give it a Thumbs Up.

v-chengfen-msft · ‎12-15-2022

Hi @ianwuk ,

Here is a test for your reference:

Here is excel :

Here is flow:

Through [Recurrence], you can check excel once a day

formatDateTime(item()?['Datetime'], 'MMMM') Get the month in the date

formatDateTime(addToTime(utcNow(), 1, 'Month'), 'MMMM') Get the month in today's date + one month's date

<style>
table {
font-family: arial, sans-serif;
border-collapse: collapse;
width: 100%;
}
td, th {
border: 1px solid #dddddd;
text-align: left;
padding: 8px;
}
tr:nth-child(even) {
background-color: #dddddd;
}
</style>
</head>
<body>
<h2>HTML Table</h2>
<table>
<tr>
<th>DateTime</th>
<th>eamil</th>
<th>Msg</th>
</tr>
<tr>
<td>@{item()?['Datetime']}</td>
<td>@{item()?['email']}</td>
<td>@{item()?['msg']}</td>
</tr>
</table>
</body>

Here is result:

If this post helps, then please consider Accept it as the solution to help the other members find it.

Best Regards

Cheng Feng

v-chengfen-msft · ‎12-15-2022

Hi @ianwuk ,

Here I make further improvements
If you want to compare a certain condition, you can add [Condition] in [Apply to each] to judge whether it is satisfied, and if it is satisfied, send an email (including the html table of this row record)

If this post helps, then please consider Accept it as the solution to help the other members find it.

Best Regards

Cheng Feng

ianwuk · ‎12-15-2022

Thank you @v-chengfen-msft @Amit_Sharma

I am getting there - slowly!

I can read the data from the table.

I can filter out the data I want by comparing if the current month name matches the same as in the spreadsheet.

But when I then create an HTML table it adds extra columns, like this:

How can I remove them? And how can I decide what columns from the spreadsheet to put in to the HTML table?

I have this step after the filter array, but it doesn't seem to work.

Based on the screenshot above, I'd expect the HTML table to just have the Full Name (EN) and Email columns, using the item values from the filter array.

What am I doing wrong?

Many thanks.

v-chengfen-msft · ‎12-15-2022

HI @ianwuk ,

Please change Columns to custom
Use item()?['Write the fields you need'] to get your need columns

If this post helps, then please consider Accept it as the solution to help the other members find it.

Best Regards

Cheng Feng

ianwuk · ‎12-15-2022

Thank you @v-chengfen-msft and @Amit_Sharma. It all works now.

ianwuk · ‎12-15-2022

@v-chengfen-msft @Amit_Sharma

Sorry, one more question, how can I create a condition based on if the generated HTML table is empty or not?

Basically, the email sent out will be different based on whether or not the table is empty.

I can't seem to get this to work because the table headers always seem to be shown.

Both these didn't work.

empty(outputs('Create_HTML_table')?['body/value']) is TRUE

empty(outputs('List_Rows_Present_In_A_Table_2')?['body/value']) is TRUE

Any help is much appreciated - thank you!

Amit_Sharma · ‎12-15-2022

Hi @ianwuk,

Instead of apply condition on body output of Create_HTML_table' Action You can apply condition Output body of Filter array After this, I guess you are good to go.