power-automate

How To Dynamically Insert Table into MS Teams Chat and Outlook Email Steps with JSON Data


​I am building a process where people can submit between 1 - 20 Power BI reports for internal review.

As part of that process, I am wanting to implement the following:

  1. Create a MS Teams chat with the Flow Bot between the requestor and reviewer for faster collaboration
  2. Create an Outlook email between the requestor, reviewer, and admin that a review has been requested so that it can be attached in ServiceNow

I already have a step in Power Automate which creates a filtered list of JSON values, which will be the unique reports that have been requested for review. I'm then taking that JSON and setting an array variable ("Non-EmptyReports") with that finalized array, and here's what the output looks like.

​​​​​​​{
   "name":"Non-EmptyReports",
   "value":[
      {
         "PBIReport1":"https://app.powerbi.com/groups/group1/reports/report1/ReportSectionf1?experience=power-bi",
         "PBIReport2":"https://app.powerbi.com/groups/group1/reports/report1/ReportSectionf1?experience=power-bi"
      }
   ]
}

I would like to dynamically insert a table into a MS Teams Chat and Outlook email with these values. There could be anywhere from 1 - 20 values in this array, so I cannot define a table with a set number of rows within it. I would like to build the table like this:

I know that MS Teams and Outlook will require different setups since MS Teams can use an Adaptive Card and Outlook would need HTML. It would be great if there's any possibility that a single template can be used between both of them, but I am unsure if this is possible. It would also be great if they could be formatted as hyperlinks to make it easier to get to the links within the browser.

Any help/advice would be greatly appreciated! ​


Solution

  • Here's a simple flow with which you can retrieve the URLs without having a limit on the number of rows and create a dynamic table for teams message and outlook.

    The output message is like this enter image description here

    You can use this same message to an outlook email body. Works same.

    Note: this method does not depend upon the field names 'PBREport1' or any other thing. It parses the attributes of 'value' array

    Steps:

    1. JSON > declare your JSON or variable (can be output of a connection)

    2. Compose > extract URL(s) as an array from the JSON

      split(replace(string(outputs('JSON')?['value']?[0]),'}',''),',') enter image description here

    3. Select > Create an array of objects with the extracted list for parsing.

      replace(last(split(replace(string(item()),' ',''),'":"')),'"','') enter image description here

    4. Create a HTML Table > for a formatted look. enter image description here

    5. Create a message for teams/outlook. enter image description here

    Here's the final structure of the Flow. enter image description here