Answered

email body with html text and body to sharepoint excel table

(0) Share

Report

Posted on by rhys87

Hi everyone,

I've had some success building the flow to add to excel table on sharepoint but the problem is the format in which i get the email in.

Bolded below.....

Hi,

The following items and values are here:

Column1	Column2	Column3	Column4	Column5	Column6	Column7
Value1	Value2	Value3	Value4	Value5	Value6	Value7

The problem is that I only want the html table, not the text and the values which change.

When I convert from html to text the format is hard to clean up in power query.

Thanks!

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (1)

Sort by

eliotcole 4,390 Moderator on at

Like (1)

Report
Copy link

Link copied!
The following expression will extract the first table in that email's HTML body.

For the purposes of this, I have placed the email HTML body variable called "emailBodyVAR".

concat('<table', first(split(first(skip(split(toLower(variables('emailBodyVAR')), '<table'), 1)), '</table')), '</table>')

However there will be better ways to process this information.

Also, if the information isn't purely a mathematical issue, consider storing it in a SharePoint list, rather than an excel sheet. This will enable you to do much more with the information (include export it to excel if someone REALLY wants it there 😉).

Was this reply helpful? Yes No
Verified answer

v-chenzhi Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
Hi @rhys87,

I assume your email’s table has seven columns and two rows.
As mentioned by @rhys87, we could use
concat('<table', first(split(first(skip(split(toLower(Body), '<table'), 1)), '</table')), '</table>')
to get the content of the html table part.

And use Html to text action to convert html to text, Then use
split(outputs('Html_to_text')?['body'],outputs('Compose_3'))[7]
to get Value1, probably like this

Best Regards,
Zhi Chen

Was this reply helpful? Yes No
eliotcole 4,390 Moderator on at

Like (0)

Report
Copy link

Link copied!
@rhys87 & @v-chenzhi

I realised a while back one thing, and just now another.

All of our answers thus far are not strictly valid, because HTML allows for a table to be made within a table.

So you will need to caveat to whomever this is for that the following failure scenarios are true:
Definite Failure - When there is a table within a table.
Probable Failure - When there any <colspan¹ or <rowspan² tags.

Example of a table in a table:
nameGiven nameFamily favouriteHtml
Jane Smith
Emboldened text like this.
John Jones
Tables like this:
header1 header1 header1
entryA answerA dataA
entryB answerB dataB

The code for this (literally ripped from this post 😅😞
<table border="1" width="100%"> <tbody> <tr> <td width="14.197530864197535%"><strong>nameGiven</strong></td> <td width="16.54320987654321%"><strong>nameFamily</strong></td> <td width="69.25925925925927%"><strong>favouriteHtml</strong></td> </tr> <tr> <td width="14.197530864197535%">Jane</td> <td width="16.54320987654321%">Smith</td> <td width="69.25925925925927%"> <p>Emboldened text like <strong>this</strong>.</p> </td> </tr> <tr> <td width="14.197530864197535%">John</td> <td width="16.54320987654321%">Jones</td> <td width="69.25925925925927%"> <p>Tables like this:</p> <table border="1" width="100%"> <tbody> <tr> <td width="33.333333333333336%"><strong>header1</strong></td> <td width="33.333333333333336%"><strong>header1</strong></td> <td width="33.333333333333336%"><strong>header1</strong></td> </tr> <tr> <td width="33.333333333333336%">entryA</td> <td width="33.333333333333336%">answerA</td> <td width="33.333333333333336%">dataA</td> </tr> <tr> <td width="33.333333333333336%">entryB</td> <td width="33.333333333333336%">answerB</td> <td width="33.333333333333336%">dataB</td> </tr> </tbody> </table> </td> </tr> </tbody> </table>
As you can see, if we split as we have been doing, this will mean the first table will not close properly('legally'), especially if there are more rows below the table. If that table is on a page where there are two root level tables (with one or more having a sub table) then you are in even more trouble.

To be clear, it's still doable, and I suspect some creative use of indexOf() and/or substring() could help here, but it will result in either huge expressions, or increased (and therefore slower) action steps, for sure.

_________
Footnotes:
data spans multiple columns
data spans multiple rows

Was this reply helpful? Yes No