Can anyone help me extract Teams message data and put into Excel please?

[ianwuk]

Hello Everyone.

I have data sent to an MS Teams channel via a webhook.  The data looks like this:

I also have a spreadsheet that looks like this:

How can I make it so that, for every new Teams channel message, the data is added to a new row of the above spreadsheet in the correct columns?

Here is my flow so far:

1 - When a new message is posted in the channel, get the details.

2 - Capture the body message

3 - Split the text using the expression split(triggerOutputs()?['body/body/content'],'|')

Here is where I am stuck, how can I work with that data to then use it with Excel?

4 - List rows in the table I want to update.

5 - Add a new row

What variables do I put in the above from step 3?

The end result in the spreadsheet should look like this for each row added:

stock_code     total_sku_have_stock  total_sku_enable     enable_lower_than_stock_percentage

TOPS_AG_008          17942                        13797                             23.10

Thanks for any help!

[Amit_Sharma · Accepted solution]

Hi @ianwuk 

I Have Created Sample for You.

split(body('Html_to_text'),'|')

outputs('Compose')?[0]

outputs('Compose')?[1]

outputs('Compose')?[2]

outputs('Compose')?[3]

In My Example I try to Add Data in Excel You can Update as well. 

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

Contact me if you are interested in custom Power Automate development.

[ianwuk]

Thank you so much @Amit_Sharma - I will try this and test.

Can you explain more about how it works?  Why does it have to have HTML to text conversion for example?

[ianwuk]

@Amit_Sharma 

I have tested this, and I think I may be doing something wrong.

My output (Excel) looks like this:

It does one row correctly, how can I get it to loop and do the rest?

In order to get that I had to use:

Stock_code - outputs('Compose')?[6]
total_sku_have_stock - outputs('Compose')?[7]
total_sku_enable - outputs('Compose')?[8]
enable_lower_than_stock_percentage - outputs('Compose')?[9]

Should I change Update a row to be add a row and just increment the array elements (so the next row would be 9, 110, 11, 12 from the array?

My flow is now:

1 - New message is posted in Teams channel.
2 - Convert message body content from HTML to text.

3 - Split the text from step 2 - split(outputs('Html_to_text')?['body'], '|')

4 - List rows in the table I want to add data to.

5 - Use apply to each to update each row based on value from spreadsheet from step 4 using the output arrays listed above:

Thanks again for your help.

[ianwuk]

@Amit_Sharma  I got it to work completely one time, by altering the array values.

How can I get it to update those same cells with new values when the next Teams message is recieved?  Right now, it just makes a new row each time.

Thanks!

[ianwuk]

@Amit_Sharma  I got it all working now, except one really simple thing I cannot figure out.

I take the Excel data and put it in to an HTML table inside a Teams message.

But it looks like this:

How do I align the text better and to be centered for each column?

Thanks.

[Amit_Sharma]

Hi @ianwuk 

This Work for Both Update Existing data if is already present otherwise add data as a new row.

empty(outputs('List_rows_present_in_a_table')?['body/value'])

[ianwuk]

@Amit_Sharma  Thank you.  I figured it out using the slower 'add data as a new row' as a flow step.

Now just trying to figure out how to format the data.

[ianwuk]

@Amit_Sharma 

Do you know how I can format this data to be nicer?  E.g. columns and text centered?  It all seems left aligned.

Thanks.

[Amit_Sharma]

Hi @ianwuk 

I think MS Team Not Supported CSS for formatting html table it's work perfectly when you are sent HTML table on email but not working in MS Teams So you can send your table as an Adaptive card in your team channel.

Please follow this reference

How to create a (faux) table in Adaptive Cards with Power Automate (m365princess.com)

Hope it help!

[Amit_Sharma]

Hi @ianwuk 

I have Found Another Way for Formatting HTML Table.

replace(body('Create_HTML_table'),'<table>','<head><style>table, th, td {border: 5px solid black;border-collapse: collapse; padding:5px;}</style></head><table border="1">')

Output:

[ianwuk]

@Amit_Sharma  Thank you so much for all your help on this!