cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PhantomPhixer
Frequent Visitor

converting an HTML teams post into an excel data

Hi,

bit of a newbie here and struggling to understand the finer points of flow.

I have a teams channel that takes in events from a process and I want to get that data into an excel sheet for auditing use.

 

so far I have worked out this much.

Screenshot 2020-03-13 at 14.58.59.pngwhich gives me a text string like this

 

 

idcontentTypecontentUrlcontentnamethumbnailUrlbe28b51c80d84790a0c110770c7a4473
application/vnd.microsoft.teams.card.o365connector{ "summary": "Jamf ", "text":
"", "title": "Test message", "themeColor": "0072C6", "sections": [ { "text":
"<p>Testing is happening</p>", "facts": [ { "name": "Computer Name:", "value":
"<p>my computer</p>" }, { "name": "Serial:", "value":
"<p>abcgabcdabcd</p>" }, { "name": "User:", "value": "<p>auser</p>" } ],
"title": "", "activityTitle": "", "activitySubtitle": "", "activityText": "",
"markdown": true, "startGroup": false } ] }

 

 

but I have absolutely no idea how to pull out the values I need, these being;  Computer Name, Serial and user. 

 

any help would be massively appreciated as everything I've tried gives no output or fails..

2 ACCEPTED SOLUTIONS

Accepted Solutions
abm
Super User
Super User

Hi @PhantomPhixer 

 

You need to use Parse JSON to extract the values. Here it is

 

Initialise a string and assig the JSON value 

 

image.png

 

Next we need JSON Parser (Couple of them)

 

image.png

 

The above schema is as follows:

{
"type": "object",
"properties": {
"@@odata.type": {
"type": "string"
},
"replyToId": {},
"etag": {
"type": "string"
},
"messageType": {
"type": "string"
},
"createdDateTime": {
"type": "string"
},
"lastModifiedDateTime": {},
"deletedDateTime": {},
"subject": {},
"summary": {
"type": "string"
},
"importance": {
"type": "string"
},
"locale": {
"type": "string"
},
"webUrl": {
"type": "string"
},
"policyViolation": {},
"id": {
"type": "string"
},
"from": {
"type": "object",
"properties": {
"device": {},
"user": {},
"conversation": {},
"application": {
"type": "object",
"properties": {
"id": {
"type": "string"
},
"displayName": {
"type": "string"
},
"applicationIdentityType": {
"type": "string"
}
}
}
}
},
"body": {
"type": "object",
"properties": {
"contentType": {
"type": "string"
},
"content": {
"type": "string"
}
}
},
"attachments": {
"type": "array",
"items": {
"type": "object",
"properties": {
"id": {
"type": "string"
},
"contentType": {
"type": "string"
},
"contentUrl": {},
"content": {
"type": "string"
},
"name": {},
"thumbnailUrl": {}
},
"required": [
"id",
"contentType",
"contentUrl",
"content",
"name",
"thumbnailUrl"
]
}
},
"mentions": {
"type": "array"
},
"reactions": {
"type": "array"
}
}
}
 
Next iterate the attachments first
 
image.png
 
Above schema is as follows:
 
{
"type": "array",
"items": {
"type": "object",
"properties": {
"id": {
"type": "string"
},
"contentType": {
"type": "string"
},
"contentUrl": {},
"content": {
"type": "string"
},
"name": {},
"thumbnailUrl": {}
},
"required": [
"id",
"contentType",
"contentUrl",
"content",
"name",
"thumbnailUrl"
]
}
}
 
 
Next iterate the contents
image.png
 
Schema for the above is
 
{
"type": "object",
"properties": {
"summary": {
"type": "string"
},
"text": {
"type": "string"
},
"title": {
"type": "string"
},
"themeColor": {
"type": "string"
},
"sections": {
"type": "array",
"items": {
"type": "object",
"properties": {
"text": {
"type": "string"
},
"facts": {
"type": "array",
"items": {
"type": "object",
"properties": {
"name": {
"type": "string"
},
"value": {
"type": "string"
}
},
"required": [
"name",
"value"
]
}
},
"title": {
"type": "string"
},
"activityTitle": {
"type": "string"
},
"activitySubtitle": {
"type": "string"
},
"activityText": {
"type": "string"
},
"markdown": {
"type": "boolean"
},
"startGroup": {
"type": "boolean"
}
},
"required": [
"text",
"facts",
"title",
"activityTitle",
"activitySubtitle",
"activityText",
"markdown",
"startGroup"
]
}
}
}
}
 
Next Parse Sections
 
image.png
 
 
{
"type": "array",
"items": {
"type": "object",
"properties": {
"text": {
"type": "string"
},
"facts": {
"type": "array",
"items": {
"type": "object",
"properties": {
"name": {
"type": "string"
},
"value": {
"type": "string"
}
},
"required": [
"name",
"value"
]
}
},
"title": {
"type": "string"
},
"activityTitle": {
"type": "string"
},
"activitySubtitle": {
"type": "string"
},
"activityText": {
"type": "string"
},
"markdown": {
"type": "boolean"
},
"startGroup": {
"type": "boolean"
}
},
"required": [
"text",
"facts",
"title",
"activityTitle",
"activitySubtitle",
"activityText",
"markdown",
"startGroup"
]
}
}
   Next iterate sections and get facts
 
image.png
 
 
 Above schema is
 
{
"type": "array",
"items": {
"type": "object",
"properties": {
"name": {
"type": "string"
},
"value": {
"type": "string"
}
},
"required": [
"name",
"value"
]
}
}
 
image.png
 
 
 
Note: All three foreach loops are within the loops each other.
 
foreach
     foreach2
           foreach3
           end
    end
end
 
 
Test Result
image.png


Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

View solution in original post

So many thanks to @abm it's now all working. I'll document it here in case anyone else wants a similar thing

As shown above the input data is a team message in HTML format giving four parameters, in this case computername, serial, user and a reason and I wanted to push that into an excel sheet along with the date.

 

Screenshot 2020-03-17 at 13.56.45.pngStarted with initialize a variable

 

 

 

 

 

 

Then need to parse the JSON

Screenshot 2020-03-17 at 13.59.38.png below is the schema for this one

{
    "type": "object",
    "properties": {
        "@@odata.type": {
            "type": "string"
        },
        "replyToId": {},
        "etag": {
            "type": "string"
        },
        "messageType": {
            "type": "string"
        },
        "createdDateTime": {
            "type": "string"
        },
        "lastModifiedDateTime": {},
        "deletedDateTime": {},
        "subject": {},
        "summary": {
            "type": "string"
        },
        "importance": {
            "type": "string"
        },
        "locale": {
            "type": "string"
        },
        "webUrl": {
            "type": "string"
        },
        "policyViolation": {},
        "id": {
            "type": "string"
        },
        "from": {
            "type": "object",
            "properties": {
                "device": {},
                "user": {},
                "conversation": {},
                "application": {
                    "type": "object",
                    "properties": {
                        "id": {
                            "type": "string"
                        },
                        "displayName": {
                            "type": "string"
                        },
                        "applicationIdentityType": {
                            "type": "string"
                        }
                    }
                }
            }
        },
        "body": {
            "type": "object",
            "properties": {
                "contentType": {
                    "type": "string"
                },
                "content": {
                    "type": "string"
                }
            }
        },
        "attachments": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "id": {
                        "type": "string"
                    },
                    "contentType": {
                        "type": "string"
                    },
                    "contentUrl": {},
                    "content": {
                        "type": "string"
                    },
                    "name": {},
                    "thumbnailUrl": {}
                },
                "required": [
                    "id",
                    "contentType",
                    "contentUrl",
                    "content",
                    "name",
                    "thumbnailUrl"
                ]
            }
        },
        "mentions": {
            "type": "array"
        },
        "reactions": {
            "type": "array"
        }
    }
}

  Then parse JSON again to pull out the attachment section

 Screenshot 2020-03-17 at 14.02.22.pngand below is the Schema for this one

 

 

 

 

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "content": {
                "type": "string"
            },
            "id": {
                "type": "string"
            },
            "contentType": {
                "type": "string"
            },
            "contentUrl": {},
            "name": {},
            "thumbnailUrl": {}
        },
        "required": [
            "id",
            "contentType",
            "contentUrl",
            "content",
            "name",
            "thumbnailUrl"
        ]
    }
}

 

 

and now the really good bit...

an apply to each is next as there is an array to pull apart

Screenshot 2020-03-17 at 14.05.16.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The two schemas are here

parse_JSON_3 schema

{
    "type": "object",
    "properties": {
        "summary": {
            "type": "string"
        },
        "text": {
            "type": "string"
        },
        "title": {
            "type": "string"
        },
        "themeColor": {
            "type": "string"
        },
        "sections": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "text": {
                        "type": "string"
                    },
                    "facts": {
                        "type": "array",
                        "items": {
                            "type": "object",
                            "properties": {
                                "name": {
                                    "type": "string"
                                },
                                "value": {
                                    "type": "string"
                                }
                            },
                            "required": [
                                "name",
                                "value"
                            ]
                        }
                    },
                    "title": {
                        "type": "string"
                    },
                    "activityTitle": {
                        "type": "string"
                    },
                    "activitySubtitle": {
                        "type": "string"
                    },
                    "activityText": {
                        "type": "string"
                    },
                    "markdown": {
                        "type": "boolean"
                    },
                    "startGroup": {
                        "type": "boolean"
                    }
                },
                "required": [
                    "text",
                    "facts",
                    "title",
                    "activityTitle",
                    "activitySubtitle",
                    "activityText",
                    "markdown",
                    "startGroup"
                ]
            }
        }
    }
}

 

parse_json_4 schema

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "text": {
                "type": "string"
            },
            "facts": {
                "type": "array",
                "items": {
                    "type": "object",
                    "properties": {
                        "name": {
                            "type": "string"
                        },
                        "value": {
                            "type": "string"
                        }
                    },
                    "required": [
                        "name",
                        "value"
                    ]
                }
            },
            "title": {
                "type": "string"
            },
            "activityTitle": {
                "type": "string"
            },
            "activitySubtitle": {
                "type": "string"
            },
            "activityText": {
                "type": "string"
            },
            "markdown": {
                "type": "boolean"
            },
            "startGroup": {
                "type": "boolean"
            }
        },
        "required": [
            "text",
            "facts",
            "title",
            "activityTitle",
            "activitySubtitle",
            "activityText",
            "markdown",
            "startGroup"
        ]
    }
}

and now the actual values can be pulled from the data and tidied up for pushing to excel

Screenshot 2020-03-17 at 14.09.13.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Firstly a parseJSON to get the Facts section

the schema is here

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "name": {
                "type": "string"
            },
            "value": {
                "type": "string"
            }
        },
        "required": [
            "name",
            "value"
        ]
    }
}

 

then four Compose sections, one for each of the data values we have. These are the items in an array and are html formatted so have tags to remove <p> and </p>. Name the compose blocks as per the item they are then use this in the expression;

replace(replace(body('Parse_JSON_5')[0]['value'],'<p>',''),'</p>','') 
  • The value in the [x] is incremented for each of the values in the array, so from 0 to 3 in this case.
  • there are two nested replace functions to remove two different strings.

 

This now gives us all the four required values ready for use

 

The excel sheet needs to be premade with the a table setup for the columns required.

 

Screenshot 2020-03-17 at 14.18.43.png

We've added the data using an Expression directly

then mapped the compose outputs

 

because we named the outputs they can easily be mapped as shown below

 

 

 

 

 

 

 

Screenshot 2020-03-17 at 14.24.05.pngNote that the name set shows above the output

 

 

 

 

 

 

 

And that's it. now when a message comes into teams the sheet is populated

View solution in original post

7 REPLIES 7
abm
Super User
Super User

Hi @PhantomPhixer 

 

Could you post your run time history details of "When a new channel message is added"?  Open the run history expand the first step and send me the details of output.

 

image.png



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

Thanks for offering to help. I think you mean this; 

{
  "@odata.type": "#microsoft.graph.chatMessage",
  "replyToId": null,
  "etag": "1584097207615",
  "messageType": "message",
  "createdDateTime": "2020-03-13T11:00:07.615Z",
  "lastModifiedDateTime": null,
  "deletedDateTime": null,
  "subject": null,
  "summary": "Jamf ",
  "importance": "normal",
  "locale": "en-us",
  "webUrl": "https://teams.microsoft.com/l/message/19%3A595359cc14b042bf98a8e7954cd5f2f6%40thread.tacv2/1584097207615?groupId=b147e627-zzzz-zzzz-zzzz-zzzzbcda017b&tenantId=zzzzzzzz-zzzz-zzzz-zzzz-zzzze760c89a&createdTime=1584097207615&parentMessageId=1234123412345",
  "policyViolation": null,
  "id": "1584097207615",
  "from": {
    "device": null,
    "user": null,
    "conversation": null,
    "application": {
      "id": "203azzzz-zzzz-zzzz-zzz-zzzzf960b6b2",
      "displayName": "JamfLogging",
      "applicationIdentityType": "office365Connector"
    }
  },
  "body": {
    "contentType": "html",
    "content": "<attachment id=\"17729b674a514bbfad72b8e13df0830c\"></attachment>"
  },
  "attachments": [
    {
      "id": "17729b674a514bbfad72b8e13df0830c",
      "contentType": "application/vnd.microsoft.teams.card.o365connector",
      "contentUrl": null,
      "content": "{\r\n  \"summary\": \"Jamf \",\r\n  \"text\": \"\",\r\n  \"title\": \"Admin Elevation\",\r\n  \"themeColor\": \"0072C6\",\r\n  \"sections\": [\r\n    {\r\n      \"text\": \"<p>Admin elevator was run</p>\",\r\n      \"facts\": [\r\n        {\r\n          \"name\": \"Computer Name:\",\r\n          \"value\": \"<p>Tom MacBook Pro</p>\"\r\n        },\r\n        {\r\n          \"name\": \"Serial:\",\r\n          \"value\": \"<p>abcdabcdabcd</p>\"\r\n        },\r\n        {\r\n          \"name\": \"User:\",\r\n          \"value\": \"<p>tom</p>\"\r\n        },\r\n        {\r\n          \"name\": \"Reason:\",\r\n          \"value\": \"<p>App install</p>\"\r\n        }\r\n      ],\r\n      \"title\": \"\",\r\n      \"activityTitle\": \"\",\r\n      \"activitySubtitle\": \"\",\r\n      \"activityText\": \"\",\r\n      \"markdown\": true,\r\n      \"startGroup\": false\r\n    }\r\n  ]\r\n}",
      "name": null,
      "thumbnailUrl": null
    }
  ],
  "mentions": [],
  "reactions": []
}

 

abm
Super User
Super User

Hi @PhantomPhixer 

 

You need to use Parse JSON to extract the values. Here it is

 

Initialise a string and assig the JSON value 

 

image.png

 

Next we need JSON Parser (Couple of them)

 

image.png

 

The above schema is as follows:

{
"type": "object",
"properties": {
"@@odata.type": {
"type": "string"
},
"replyToId": {},
"etag": {
"type": "string"
},
"messageType": {
"type": "string"
},
"createdDateTime": {
"type": "string"
},
"lastModifiedDateTime": {},
"deletedDateTime": {},
"subject": {},
"summary": {
"type": "string"
},
"importance": {
"type": "string"
},
"locale": {
"type": "string"
},
"webUrl": {
"type": "string"
},
"policyViolation": {},
"id": {
"type": "string"
},
"from": {
"type": "object",
"properties": {
"device": {},
"user": {},
"conversation": {},
"application": {
"type": "object",
"properties": {
"id": {
"type": "string"
},
"displayName": {
"type": "string"
},
"applicationIdentityType": {
"type": "string"
}
}
}
}
},
"body": {
"type": "object",
"properties": {
"contentType": {
"type": "string"
},
"content": {
"type": "string"
}
}
},
"attachments": {
"type": "array",
"items": {
"type": "object",
"properties": {
"id": {
"type": "string"
},
"contentType": {
"type": "string"
},
"contentUrl": {},
"content": {
"type": "string"
},
"name": {},
"thumbnailUrl": {}
},
"required": [
"id",
"contentType",
"contentUrl",
"content",
"name",
"thumbnailUrl"
]
}
},
"mentions": {
"type": "array"
},
"reactions": {
"type": "array"
}
}
}
 
Next iterate the attachments first
 
image.png
 
Above schema is as follows:
 
{
"type": "array",
"items": {
"type": "object",
"properties": {
"id": {
"type": "string"
},
"contentType": {
"type": "string"
},
"contentUrl": {},
"content": {
"type": "string"
},
"name": {},
"thumbnailUrl": {}
},
"required": [
"id",
"contentType",
"contentUrl",
"content",
"name",
"thumbnailUrl"
]
}
}
 
 
Next iterate the contents
image.png
 
Schema for the above is
 
{
"type": "object",
"properties": {
"summary": {
"type": "string"
},
"text": {
"type": "string"
},
"title": {
"type": "string"
},
"themeColor": {
"type": "string"
},
"sections": {
"type": "array",
"items": {
"type": "object",
"properties": {
"text": {
"type": "string"
},
"facts": {
"type": "array",
"items": {
"type": "object",
"properties": {
"name": {
"type": "string"
},
"value": {
"type": "string"
}
},
"required": [
"name",
"value"
]
}
},
"title": {
"type": "string"
},
"activityTitle": {
"type": "string"
},
"activitySubtitle": {
"type": "string"
},
"activityText": {
"type": "string"
},
"markdown": {
"type": "boolean"
},
"startGroup": {
"type": "boolean"
}
},
"required": [
"text",
"facts",
"title",
"activityTitle",
"activitySubtitle",
"activityText",
"markdown",
"startGroup"
]
}
}
}
}
 
Next Parse Sections
 
image.png
 
 
{
"type": "array",
"items": {
"type": "object",
"properties": {
"text": {
"type": "string"
},
"facts": {
"type": "array",
"items": {
"type": "object",
"properties": {
"name": {
"type": "string"
},
"value": {
"type": "string"
}
},
"required": [
"name",
"value"
]
}
},
"title": {
"type": "string"
},
"activityTitle": {
"type": "string"
},
"activitySubtitle": {
"type": "string"
},
"activityText": {
"type": "string"
},
"markdown": {
"type": "boolean"
},
"startGroup": {
"type": "boolean"
}
},
"required": [
"text",
"facts",
"title",
"activityTitle",
"activitySubtitle",
"activityText",
"markdown",
"startGroup"
]
}
}
   Next iterate sections and get facts
 
image.png
 
 
 Above schema is
 
{
"type": "array",
"items": {
"type": "object",
"properties": {
"name": {
"type": "string"
},
"value": {
"type": "string"
}
},
"required": [
"name",
"value"
]
}
}
 
image.png
 
 
 
Note: All three foreach loops are within the loops each other.
 
foreach
     foreach2
           foreach3
           end
    end
end
 
 
Test Result
image.png


Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

Wow! Thanks @abm  I shall try this straight away I would never have worked that out.

Hi @abm. I'm making progress but I have a feeling you have pasted for each2 in twice and missed for each 1 . I just can't get my inputs to show the names you have and the json looks the same for both.

@abm I'm really stuck! I've spent hours trying to achieve the state shown here

but I can't ever get an input of sections let alone a content of FactsScreenshot 2020-03-17 at 11.16.17.png

So many thanks to @abm it's now all working. I'll document it here in case anyone else wants a similar thing

As shown above the input data is a team message in HTML format giving four parameters, in this case computername, serial, user and a reason and I wanted to push that into an excel sheet along with the date.

 

Screenshot 2020-03-17 at 13.56.45.pngStarted with initialize a variable

 

 

 

 

 

 

Then need to parse the JSON

Screenshot 2020-03-17 at 13.59.38.png below is the schema for this one

{
    "type": "object",
    "properties": {
        "@@odata.type": {
            "type": "string"
        },
        "replyToId": {},
        "etag": {
            "type": "string"
        },
        "messageType": {
            "type": "string"
        },
        "createdDateTime": {
            "type": "string"
        },
        "lastModifiedDateTime": {},
        "deletedDateTime": {},
        "subject": {},
        "summary": {
            "type": "string"
        },
        "importance": {
            "type": "string"
        },
        "locale": {
            "type": "string"
        },
        "webUrl": {
            "type": "string"
        },
        "policyViolation": {},
        "id": {
            "type": "string"
        },
        "from": {
            "type": "object",
            "properties": {
                "device": {},
                "user": {},
                "conversation": {},
                "application": {
                    "type": "object",
                    "properties": {
                        "id": {
                            "type": "string"
                        },
                        "displayName": {
                            "type": "string"
                        },
                        "applicationIdentityType": {
                            "type": "string"
                        }
                    }
                }
            }
        },
        "body": {
            "type": "object",
            "properties": {
                "contentType": {
                    "type": "string"
                },
                "content": {
                    "type": "string"
                }
            }
        },
        "attachments": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "id": {
                        "type": "string"
                    },
                    "contentType": {
                        "type": "string"
                    },
                    "contentUrl": {},
                    "content": {
                        "type": "string"
                    },
                    "name": {},
                    "thumbnailUrl": {}
                },
                "required": [
                    "id",
                    "contentType",
                    "contentUrl",
                    "content",
                    "name",
                    "thumbnailUrl"
                ]
            }
        },
        "mentions": {
            "type": "array"
        },
        "reactions": {
            "type": "array"
        }
    }
}

  Then parse JSON again to pull out the attachment section

 Screenshot 2020-03-17 at 14.02.22.pngand below is the Schema for this one

 

 

 

 

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "content": {
                "type": "string"
            },
            "id": {
                "type": "string"
            },
            "contentType": {
                "type": "string"
            },
            "contentUrl": {},
            "name": {},
            "thumbnailUrl": {}
        },
        "required": [
            "id",
            "contentType",
            "contentUrl",
            "content",
            "name",
            "thumbnailUrl"
        ]
    }
}

 

 

and now the really good bit...

an apply to each is next as there is an array to pull apart

Screenshot 2020-03-17 at 14.05.16.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The two schemas are here

parse_JSON_3 schema

{
    "type": "object",
    "properties": {
        "summary": {
            "type": "string"
        },
        "text": {
            "type": "string"
        },
        "title": {
            "type": "string"
        },
        "themeColor": {
            "type": "string"
        },
        "sections": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "text": {
                        "type": "string"
                    },
                    "facts": {
                        "type": "array",
                        "items": {
                            "type": "object",
                            "properties": {
                                "name": {
                                    "type": "string"
                                },
                                "value": {
                                    "type": "string"
                                }
                            },
                            "required": [
                                "name",
                                "value"
                            ]
                        }
                    },
                    "title": {
                        "type": "string"
                    },
                    "activityTitle": {
                        "type": "string"
                    },
                    "activitySubtitle": {
                        "type": "string"
                    },
                    "activityText": {
                        "type": "string"
                    },
                    "markdown": {
                        "type": "boolean"
                    },
                    "startGroup": {
                        "type": "boolean"
                    }
                },
                "required": [
                    "text",
                    "facts",
                    "title",
                    "activityTitle",
                    "activitySubtitle",
                    "activityText",
                    "markdown",
                    "startGroup"
                ]
            }
        }
    }
}

 

parse_json_4 schema

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "text": {
                "type": "string"
            },
            "facts": {
                "type": "array",
                "items": {
                    "type": "object",
                    "properties": {
                        "name": {
                            "type": "string"
                        },
                        "value": {
                            "type": "string"
                        }
                    },
                    "required": [
                        "name",
                        "value"
                    ]
                }
            },
            "title": {
                "type": "string"
            },
            "activityTitle": {
                "type": "string"
            },
            "activitySubtitle": {
                "type": "string"
            },
            "activityText": {
                "type": "string"
            },
            "markdown": {
                "type": "boolean"
            },
            "startGroup": {
                "type": "boolean"
            }
        },
        "required": [
            "text",
            "facts",
            "title",
            "activityTitle",
            "activitySubtitle",
            "activityText",
            "markdown",
            "startGroup"
        ]
    }
}

and now the actual values can be pulled from the data and tidied up for pushing to excel

Screenshot 2020-03-17 at 14.09.13.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Firstly a parseJSON to get the Facts section

the schema is here

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "name": {
                "type": "string"
            },
            "value": {
                "type": "string"
            }
        },
        "required": [
            "name",
            "value"
        ]
    }
}

 

then four Compose sections, one for each of the data values we have. These are the items in an array and are html formatted so have tags to remove <p> and </p>. Name the compose blocks as per the item they are then use this in the expression;

replace(replace(body('Parse_JSON_5')[0]['value'],'<p>',''),'</p>','') 
  • The value in the [x] is incremented for each of the values in the array, so from 0 to 3 in this case.
  • there are two nested replace functions to remove two different strings.

 

This now gives us all the four required values ready for use

 

The excel sheet needs to be premade with the a table setup for the columns required.

 

Screenshot 2020-03-17 at 14.18.43.png

We've added the data using an Expression directly

then mapped the compose outputs

 

because we named the outputs they can easily be mapped as shown below

 

 

 

 

 

 

 

Screenshot 2020-03-17 at 14.24.05.pngNote that the name set shows above the output

 

 

 

 

 

 

 

And that's it. now when a message comes into teams the sheet is populated

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Top Solution Authors
Top Kudoed Authors
Users online (4,056)