"When a HTTP request is received" JSON to something readable

[rseiler]

I'm doing a POST from a script into a flow. AFAIK it needs to be JSON, and I do have the "When a HTTP request is received" in place and accepting input.

The schema is what's shown below. It's basically just those 5 things, but there tend to be several groups of them at a time, which I intend to email in the last step (Send an email v2). I've tried emailing it as is, and it's an unreadable blob of JSON, of course.

It doesn't have to be an HTML table necessarily. Even if it was just the five things repeated down the body of the email would work. Like:

Received: 5/6/2024 12:00:00 PM

Sender: joe@blah.com

Recipient: Mary@blah.com

Subject: The subject of the email

Type: Phish

etc

Perhaps a "Compose" or two between the above two actions would do it? This is the part that I'm very unclear about and don't understand.

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "ReceivedTime": {
                "type": "string"
            },
            "SenderAddress": {
                "type": "string"
            },
            "RecipientAddress": {
                "type": "array",
                "items": {
                    "type": "string"
                }
            },
            "Subject": {
                "type": "string"
            },
            "QuarantineTypes": {
                "type": "string"
            }
        },
        "required": [
            "ReceivedTime",
            "SenderAddress",
            "RecipientAddress",
            "Subject",
            "QuarantineTypes"
        ]
    }
}

[Marcelehmann · Accepted solution]

Hi @rseiler 

if I understand your question correct and you're looking to POST JSON data to a Power Automate flow and then email it in a structured and readable HTML table format, here's a simple way to do it:

1. HTTP Trigger Setup: you can use the Parse there or in the following step

2.2Parse JSON: Right after your trigger, use the "Parse JSON" action to parse the incoming JSON. Paste the same schema you used in the trigger. This action prepares your data for processing in the flow.

3. **Create an HTML Table**: Now, add the "Create HTML table" action. For its input, select the output from the "Parse JSON" action. This action automatically converts your JSON array into an HTML table.

4. Send an Email: Finally, add a "Send an email (V2)" action. 

[WillPage]

You're right, you don't need an HTML table. If you have the schema in your trigger then the various properties will be available as dynamic content (you don't need to add a Parse JSON action like the previous reply says because the trigger does that for you if you put the schema in there).

After the trigger, add a Select action (from Data operations) and put the array property from the trigger in its "From" input.
In the select action, click the little icon on the right to switch to JSON mode. When you do this the key-value pairs input changes to a single input box. In there, put code like this. The values should be available as dynamic content but also you could probably just paste this and it'll work.

"<b>Received:</b> @{item()?['ReceivedTime']}
<br><br>
<b>Sender:</b> @{item()?['SenderAddress']}
<br><br>
<b>Recipient:</b> @{item()?['RecipientAddress']}
<br><br>
<b>Subject:</b> @{item()?['Subject']}
<br><br>
<b>Type:</b> @{item()?['QuarantineTypes']}"

Note the quotes at the start and end. You need these for your flow to save correctly. There's a bug in Power Automate that means each time you edit the flow you need to add the quotes again. It's annoying but it is what it is.

Anyway, now you've done that, add a Join action, also from Data Operations. The output from Select is the "From" input and string to join on is <br><br><br> (3 HTML line breaks, or whatever you want). You can now drop the output from Join into your email.

[rseiler]

@WillPage:

1) On adding the array property to the Select "From," all I'm seeing for Dynamic Content there are Body and RecipientAddress. I'm not exactly sure what to be expecting, but I chose Body for now.

2) Likewise with the later Join, Body was available so I chose it.

I suppose that was right, since  the content came through in the email. Thanks!

But it's in blob form. So for example:

{"ReceivedTime":"2024-04-08T23:46:16.4955456+00:00","SenderAddress":"sample@blah.com","RecipientAddress":["address@domain.ca"],"Subject":"Delivery Notification For Items / Avis De Livraison Pour Le(s) Article(s) :","QuarantineTypes":"HighConfPhish"}

What would you suggest for at least getting those on their own lines or otherwise making it readable? For the record, I have this:

[rseiler]

Not sure what's going on with editing messages on this site, but if it had let me do that I would have clarified that what I asked about in the last message was because of the JSON section of Select. That was telling me that it was invalid, so I did without the HTML elements there, explaining why there are no line breaks. I just need to figure out now how to mix in those HTML elements with the JSON to get the editor to actually accept it, since there clearly must be a way or you wouldn't have sent what you did.

Update: I did manage to get it to look like this, but the formatting is still not happening at all. Quite puzzling. It's probably notable, as shown in my last message, that what the email actually uses is the terminology on the right that you see below (from the schema) and not the mapping on the left.

Code view:

{
  "type": "Select",
  "inputs": {
    "from": "@triggerBody()",
    "select": {
      "<b>Received:</b><br><br>": "@item()?['ReceivedTime']",
      "<b>Sender:</b><br><br>": "@item()?['SenderAddress']",
      "<b>Recipient:</b><br><br>": "@item()?['RecipientAddress']",
      "<b>Subject:</b><br><br>": "@item()?['Subject']",
      "<b>Type:</b><br><br>": "@item()?['QuarantineTypes']"
    }
  }
}

[rseiler]

While I haven't been able to solve it, I do have a top suspect, and that's the Join.

Looking at the flow from one of the runs, things seem to be going well through the "OUTPUTS" of the Select. For example, this snippet (there's more to it, but this illustrates my point):

[
  {
    "<b>Received:</b><br><br>": "2024-04-08T23:46:16.4955456+00:00",
    "<b>Sender:</b><br><br>": "nepasrepondre@robertlaurente.com",
    "<b>Recipient:</b><br><br>": [
      "test@email.com"
    ],

But now when you look at the "INPUTS" section of the next step, the Join, that same snippet looks like this:

[
  {
    "ReceivedTime": "2024-04-08T23:46:16.4955456+00:00",
    "SenderAddress": "nepasrepondre@robertlaurente.com",
    "RecipientAddress": [
      "test@email.com"
    ],

Note the absence of the formatting and the return to the schema terminology. And this is what's carried through to the email, which is the next step. Maybe I'm misunderstanding INPUTS and OUTPUTS, but it seems to me that the Join isn't doing what it should be. If the Join shouldn't have its "From" set to "Body." I'm not sure what it should be.

[WillPage]

Your issue is that you've wrapped the body of your select in curly braces with each line in quotes. That's not right. It should be more like this:

"<b>Received:</b> @{item()?['ReceivedTime']}<br><br><b>Sender:</b> @{item()?['SenderAddress']}<br><br><b>Recipient:</b> @{item()?['RecipientAddress']}<br><br><b>Subject:</b> @{item()?['Subject']}<br><br><b>Type:</b> @{item()?['QuarantineTypes']}"

Secondly, but probably less important until you get this first bit right, is your RecipientAddress field is an array. To get around that you need another join, but instead of the Join action from data operations, you'd use the join() function in the expression editor within the select. e.g
join(item()?['RecipientAddress'],', ') in the Select instead of just item()?['RecipientAddress']
That way you get a comma-space separated list of email addresses if there's more than one, otherwise you just get the single email address if there's only one.

[rseiler]

OK, so got that done. It now looks like this:

If hovered over, those are in fact items (e.g. item()?['ReceivedTime']. While the flow does complete, the style of the email still looks as it did before. Each section is an  unformatted blurb like you see in my first message to you above.

Note that I haven't done that new Join that you mentioned yet. I'm not actually sure why RecipientAddress is an array in the first place or how it got that way in the schema, but I assume it is for multiple recipients, as rare as that is. I don't think this can be the problem though.

[WillPage]

Can you paste screen shots of the inputs and outputs of the select and the join actions from a successful run?

[rseiler]

Sure. I didn't do the second Join yet to keep this as simple as possible (and because I didn't quite see where it should go/how it should be done).

There are numerous ones for each so I just limited it to one for each for clarity and brevity:

SELECT (Inputs):

[
  {
    "ReceivedTime": "2024-04-11T00:14:20.6010298+00:00",
    "SenderAddress": "account-message-items.packages-account@secure.com",
    "RecipientAddress": [
      "outreach@email.ca"
    ],
    "Subject": "DHL shipping status: Action required",
    "QuarantineTypes": "HighConfPhish"
  },

SELECT (Outputs):

  [
  "<b>Received:</b> 2024-04-11T00:14:20.6010298+00:00<br><br><b>Sender:</b> account-message-items.packages-account@secure.com<br><br><b>Recipient:</b> [\"outreach@email.ca\"]<br><br><b>Subject:</b> DHL shipping status: Action required<br><br><b>Type:</b> HighConfPhish",

JOIN (Inputs):

  [
  {
    "ReceivedTime": "2024-04-11T00:14:20.6010298+00:00",
    "SenderAddress": "account-message-items.packages-account@secure.com",
    "RecipientAddress": [
      "outreach@email.ca"
    ],
    "Subject": "DHL shipping status: Action required",
    "QuarantineTypes": "HighConfPhish"
  },

JOIN (Outputs):

{"ReceivedTime":"2024-04-11T00:14:20.6010298+00:00","SenderAddress":"account-message-items.packages-account@secure.com","RecipientAddress":["outreach@email.ca"],"Subject":"DHL shipping status: Action required","QuarantineTypes":"HighConfPhish"}<br><br>

SEND (Inputs-Body):

  {
  "To": "recipient@outlook.com",
  "Subject": "Quarantined emails",
  "Body": "<p><a href=\"https://security.microsoft.com/quarantine\"><b><strong>Quarantine website</strong></b></a><br>{\"ReceivedTime\":\"2024-04-11T00:14:20.6010298+00:00\",\"SenderAddress\":\"account-message-items.packages-account@secure.com\",\"RecipientAddress\":[\"outreach@email.ca\"],\"Subject\":\"DHL shipping status: Action required\",\"QuarantineTypes\":\"HighConfPhish\"}<br><br>

[WillPage]

Looks like you're putting the same input into your Join as the Select, instead of the output of the Select as the input for the Join.

[rseiler]

The Join did seem suspect based on Inputs/Outputs.

While it is true that both the Select and Join use "Body," that seemed to be the only viable choice, and I didn't interpret that as the same input as the Select but one using the same name (Body) but which would now be different as a result of the Select.

In the Join, when I hit / these are the choices:

Looking at it again now, I see what you mean, as Body is subordinate to the first step in the flow, which is indeed what the Select is using. If the "For each" is meant to represent the Select, unfortunately choosing "Current Item" and trying to Save results in "Error occurred while validating operations parameters," which seems to be a reference to the Select, since at that point an error appears within it: "Invalid parameters" and "Enter a valid JSON." It doesn't like the Map of the Select now that "Current Item" is in place in the Join. And if I try to re-save the revised Map that you provided yesterday, even though I know it's already there, it shows this:

The flow does still look like I show it in my second message above.