cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Super User III
Super User III

Re: converting an HTML teams post into an excel data

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

Highlighted
Frequent Visitor

Re: converting an HTML teams post into an excel data

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
Highlighted
Super User III
Super User III

Re: converting an HTML teams post into an excel data

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
Highlighted
Frequent Visitor

Re: converting an HTML teams post into an excel data

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": []
}

 

Highlighted
Super User III
Super User III

Re: converting an HTML teams post into an excel data

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

Highlighted
Frequent Visitor

Re: converting an HTML teams post into an excel data

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

Highlighted
Frequent Visitor

Re: converting an HTML teams post into an excel data

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.

Highlighted
Frequent Visitor

Re: converting an HTML teams post into an excel data

@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

Highlighted
Frequent Visitor

Re: converting an HTML teams post into an excel data

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Upcoming Events

Experience what’s next for Power Automate

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors
Users online (3,248)