Flow - parsing email and writing to a sharepoint list - getting a blank line?

[Seuadr]

Hi all,

yesterday, several of you helped me with a flow that i was working on: link! 

This was mostly successful, but i'm running into an issue when creating new items in the sharepoint list.

 here is my current flow:

here is an example of the type of email message this is dealing with:

email:

Message 1:

Monday, 04/05/2021 at 11:48:36. The Point ANT.REF 1125C is in Alarm at PRI3.

Message 2:

Monday, 04/05/2021 at 11:48:44. The Point ANT HVAC4A.DISCH AIR is at NORMAL.

now, i have 2 options when creating the new item in the sharepoint list (at least, that i am aware of) and i get not quite the results i am hoping for from either:

Option 1: use the output of "Step 3" - this gives me the first value in the list 3 times (even though there are only 2 values, i assume because it ends up with an array of 3 - but i'm not sure why it ends up with an array of 3?

Option 2: use the "apply to each - current item" output, which gives the 2 messages, but not trimmed, and, an extra blank entry.

i'm not sure what my next steps should be to get the output to be the trimmed value, and just the number of messages?

Thanks for your time in advance!

[ScottShearer]

@Seuadr 

In your post yesterday I understood you to say that multiple messages would be included in one email.  If that is not so and you are processing one message at a time, my example below should work for you.  My Flow is working under the assumption the each message will be formatted the same and that there is always a date/time followed by a period before the text that you want to extract.

Please let me know if you have additional questions.

[Seuadr]

@ScottShearer that is correct. i will have a single email with multiple messages inside it, they vary in number, based on outside factors. they are also formatted the same and i was the stuff after the first period. 

what i think i failed to do was explain that ultimately i want each of those messages to be a new item (or update an item if they exist) in the sharepoint list.

so like the email example above:

Message 1:

Monday, 04/05/2021 at 11:48:36. The Point ANT.REF 1125C is in Alarm at PRI3.

Message 2:

Monday, 04/05/2021 at 11:48:44. The Point ANT HVAC4A.DISCH AIR is at NORMAL

would make my sharepoint list look like:

The Point ANT.REF 1125C is in Alarm at PRI3.

The Point ANT HVAC4A.DISCH AIR is at NORMAL

or in reality the first period, or after "point" or after "the" doesn't really matter that much.

so your solution from yesterday does that - i think i am applying the create item wrong? because it is either entering the same value n+1 times depending on how many "message:" splits there are, or, if i use the "current item" it posts each one, but, without the trimming so it looks like this:

1:Monday, 04/05/2021 at 11:48:36. The Point ANT.REF 1125C is in Alarm at PRI3.

2:Monday, 04/05/2021 at 11:48:44. The Point ANT HVAC4A.DISCH AIR is at NORMAL

[Seuadr]

So, i had a little time to tinker with it this morning and figured part of it out?

this works to trim what i want out of the "apply to each", but the flow fails because it can't parse one of the items (because it is a blank from the array?) so the question now is how do i eliminate the blank in the array?

[ScottShearer]

@Seuadr 

Your best bet is to look at the output of each action in one of your failed runs.  Is the output what you expect?  If not, then adjust as required.

[Seuadr]

@ScottShearer the output from split on message is:

"\r\n",

  " 1:\r\nMonday, 04/05/2021 at 11:48:36. The Point ANT.REF 1125C is in Alarm at PRI3.\r\n\r\n\r\n",

  " 2:\r\nMonday, 04/05/2021 at 11:48:44. The Point ANT HVAC4A.DISCH AIR is at NORMAL.\r\n\r\n\r\n"

]

I need to figure out how to trim the leading space - but if i use a trim in that compose, it goes from being an array to being string., so my apply to each fails.

[Seuadr]

@ScottShearer steps 1-3 don't iterate through the values from "split on message"

so the raw output for split on message is:

[

"\r\n",

  "1:\r\nMonday, 04/05/2021 at 11:48:36. The Point ANT.REF 1125C is in Alarm at PRI3.\r\n\r\n\r\n",

  "2:\r\nMonday, 04/05/2021 at 11:48:44. The Point ANT HVAC4A.DISCH AIR is at NORMAL.\r\n\r\n\r\n"

]

step 3 raw output:

ANT.REF 1125C is in Alarm at PRI3.

then the apply to each iterates 3 times, but the raw outputs are the same each time:

Step 1:

"1:\r\nMonday, 04/05/2021 at 11:48:36. The Point ANT.REF 1125C is in Alarm at PRI3."

Step 2:

[

    "1:\r\nMonday, 04/05/2021 at 11:48:36. The ",

    " ANT.REF 1125C is in Alarm at PRI3."

]

Step 3:

"ANT.REF 1125C is in Alarm at PRI3."

not sure why it doesn't do that.

if i change the create item to "current item" and put the trim/split in there it works the way i'd like, except that it includes the "\r\n" which ends up being interpreted as a blank line.

[Paulie78]

Could you just post the html source of the message? or the output of the HTML to text action, within the "Insert Code Sample" feature of the forum. It's not clear exactly what the content of the email is (at least to me).

[Seuadr]

@Paulie78 so the email is plain text, i have no html to text action. if i go to "actions" --> "other actions" in the message action menu there is no "View Source" if i hit reply, it automatically formats as plain text.

for the example above - the email looks like this:

it has the following lines of text only

here are all of my steps with expressions in comments:

Split on message raw output:
[
    "",
    "1:\r\nMonday, 04/05/2021 at 11:48:36. The Point ANT.REF 1125C is in Alarm at PRI3.\r\n\r\n\r\n",
    "2:\r\nMonday, 04/05/2021 at 11:48:44. The Point ANT HVAC4A.DISCH AIR is at NORMAL.\r\n\r\n\r\n"
]

Step 1 raw output:
"1:\r\nMonday, 04/05/2021 at 11:48:36. The Point ANT.REF 1125C is in Alarm at PRI3."

Step 2 raw ouput:
[
    "1:\r\nMonday, 04/05/2021 at 11:48:36. The ",
    " ANT.REF 1125C is in Alarm at PRI3."
]
Step 3 raw output:
"ANT.REF 1125C is in Alarm at PRI3."

Create item raw input:
{
    "host": {
        "connectionReferenceName": "shared_sharepointonline",
        "operationId": "PostItem"
    },
    "parameters": {
        "dataset": "https://michiganstate.sharepoint.com/sites/IPF.BS.BPS.CentralControl",
        "table": "ea4c302d-f80f-431b-9a68-2c36eface8f4",
        "item/Title": "ANT.REF 1125C is in Alarm at PRI3."
    }
}

note - the output is the same for all 3 iterations of apply to each, which is why i tried using "current item" instead in "create item"

doing that, i get the iterations i expect, but create items fails:

Unable to process template language expressions in action 'Create_item' inputs at line '1' and column '30510': 'The template language expression 'trim(split(items('Apply_to_each'),'Point')[1])' cannot be evaluated because array index '1' is outside bounds (0, 0) of array. Please see https://aka.ms/logicexpressions for usage details.'.

i can't look at the inputs because of the failure, so i can only presume it is because of this item:

[Paulie78]

Ok, can you post the plain text source into the code window please? The reason I am asking is because these sort of operations are really sensitive, and this forum is really bad at dealing with link breaks. So although your screenshot shows the content nicely, in order for me to try it out my end, I need the exact email content as you receive it.

If you want to see the source, just look click on View source in outlook, or get it from the trigger action in the run history. 

[Seuadr]

i am unable to see "view source" in the email, but i did pull the input and output of the trigger:

Input:

{
    "host": {
        "connectionReferenceName": "shared_office365",
        "operationId": "OnNewEmailV3"
    },
    "parameters": {
        "folderPath": "Id::AAMkAGJlZDM0NDk4LTUxODYtNDI0Ny1iMjM3LTRjZTgzYjA2NTE5NAAuAAAAAAB8AXjTAi2zRqYNGLLzLgjDAQA65CqJMkCyS5bnSNUUUIZiAAAKdZ3AAAA=",
        "includeAttachments": false,
        "importance": "Any",
        "fetchOnlyWithAttachment": false
    }
}

Output:

{"headers":{"Pragma":"no-cache","Transfer-Encoding":"chunked","Retry-After":"3600","Vary":"Accept-Encoding","x-ms-request-id":"ab520a0a-ee00-48b8-bbcf-eab700b6340b;4c90473f-7913-4cdb-a9fb-2d82191aaffe","Strict-Transport-Security":"max-age=31536000; includeSubDomains","X-Content-Type-Options":"nosniff","X-Frame-Options":"DENY","Cache-Control":"no-store, no-cache","Location":"https://flow-apim-msmanaged-na-westus2-01.azure-apim.net/apim/office365/shared-office365-7411b170-e09f-4eee-8259-c93f-cc8e39b7/v3/Mail/OnNewEmail?folderPath=Id%3a%3aAAMkAGJlZDM0NDk4LTUxODYtNDI0Ny1iMjM3LTRjZTgzYjA2NTE5NAAuAAAAAAB8AXjTAi2zRqYNGLLzLgjDAQA65CqJMkCyS5bnSNUUUIZiAAAKdZ3AAAA%3d&importance=Any&fetchOnlyWithAttachment=false&includeAttachments=false&LastPollInformation=eyJMYXN0UmVjZWl2ZWRNYWlsVGltZSI6IjIwMjEtMDQtMDdUMTQ6NTY6NTYrMDA6MDAiLCJMYXN0Q3JlYXRlZE1haWxUaW1lIjoiMjAyMS0wNC0wN1QxNDo1Njo1NiswMDowMCIsIkxhc3RNZXNzYWdlSWQiOiJBQU1rQUdKbFpETTBORGs0TFRVeE9EWXROREkwTnkxaU1qTTNMVFJqWlRnellqQTJOVEU1TkFCR0FBQUFBQUI4QVhqVEFpMnpScVlOR0xMekxnakRCd0E2NUNxSk1rQ3lTNWJuU05VVVVJWmlBQUFLZFozQUFBQTY1Q3FKTWtDeVM1Ym5TTlVVVUlaaUFBQVp4a004QUFBPSIsIkxhc3RJbnRlcm5ldE1lc3NhZ2VJZCI6IjxCTDBQUjEyTUIyNTMxQzI4MTc3NTUxRkI4Q0QwNTUxRTZBODc1OUBCTDBQUjEyTUIyNTMxLm5hbXByZDEyLnByb2Qub3V0bG9vay5jb20%2bIn0%3d","Set-Cookie":"ARRAffinity=2c3f44181ada7afbc1fac53fd81447c03f95667d82576c215f703a1f387d63ab;Path=/;HttpOnly;Secure;Domain=office365-eus.azconn-eus-01.p.azurewebsites.net,ARRAffinitySameSite=2c3f44181ada7afbc1fac53fd81447c03f95667d82576c215f703a1f387d63ab;Path=/;HttpOnly;SameSite=None;Secure;Domain=office365-eus.azconn-eus-01.p.azurewebsites.net","Timing-Allow-Origin":"*","x-ms-apihub-cached-response":"false","Date":"Wed, 07 Apr 2021 14:56:59 GMT","Content-Type":"application/json; charset=utf-8","Expires":"-1","Content-Length":"1099"},"body":{"id":"AAMkAGJlZDM0NDk4LTUxODYtNDI0Ny1iMjM3LTRjZTgzYjA2NTE5NABGAAAAAAB8AXjTAi2zRqYNGLLzLgjDBwA65CqJMkCyS5bnSNUUUIZiAAAKdZ3AAAA65CqJMkCyS5bnSNUUUIZiAAAZxkM8AAA=","receivedDateTime":"2021-04-07T14:56:56+00:00","hasAttachments":false,"internetMessageId":"<BL0PR12MB2531C28177551FB8CD0551E6A8759@BL0PR12MB2531.namprd12.prod.outlook.com>","subject":"FW: 2 new Messages","bodyPreview":"Message 1:\r\nMonday, 04/05/2021 at 11:48:36. The Point ANT.REF 1125C is in Alarm at PRI3.\r\n\r\n\r\nMessage 2:\r\nMonday, 04/05/2021 at 11:48:44. The Point ANT HVAC4A.DISCH AIR is at NORMAL.","importance":"normal","conversationId":"AAQkAGJlZDM0NDk4LTUxODYtNDI0Ny1iMjM3LTRjZTgzYjA2NTE5NAAQAGEX3OfIb-tOooFo60uyXZ4=","isRead":false,"isHtml":false,"body":"Message 1:\r\nMonday, 04/05/2021 at 11:48:36. The Point ANT.REF 1125C is in Alarm at PRI3.\r\n\r\n\r\nMessage 2:\r\nMonday, 04/05/2021 at 11:48:44. The Point ANT HVAC4A.DISCH AIR is at NORMAL.\r\n\r\n\r\n","from":"IPF.SA.HVACFlow@msu.edu","toRecipients":"IPF.SA.HVACFlow@msu.edu","attachments":[]}}

[Seuadr]

wow that was a mess - i am assuming the portion you are looking for is:

"body":"Message 1:\r\nMonday, 04/05/2021 at 11:48:36. The Point ANT.REF 1125C is in Alarm at PRI3.\r\n\r\n\r\nMessage 2:\r\nMonday, 04/05/2021 at 11:48:44. The Point ANT HVAC4A.DISCH AIR is at NORMAL.\r\n\r\n\r\n","from":"IPF.SA.HVACFlow@msu.edu"

[Paulie78]

That's perfect

[Paulie78 · Accepted solution]

Ok, so my method is quite simple compared to your flow:

Expressions:

splitBody Action:
split(outputs('mailbody'), decodeUriComponent('%0D%0A'))

Filter array aciton:
length(item())

Output of the filter array is an array:

[
  "Monday, 04/05/2021 at 11:48:36. The Point ANT.REF 1125C is in Alarm at PRI3.",
  "Monday, 04/05/2021 at 11:48:44. The Point ANT HVAC4A.DISCH AIR is at NORMAL."
]

You can then just use the array as a the input to your apply/each.

[Seuadr]

@Paulie78 Thanks for your hard work, it is appreciated.

so i did a little reading on the uricomponent (function?) and i'm guessing that the /n/r/n stuff is what it is being filtered out?

so then the length of each item in the array is being evaluated to drop out the short stuff that was appearing for some reason?

to be fair, i am very novice at power automate, so Scott broke his solution out into a lot of steps so it would be easy for me to follow.

i will apply this and follow up one way or another. thanks!

[Paulie78]

Yes, your analysis is spot on. Well done.

[Seuadr]

@Paulie78 This worked well, thanks!