Extract email content to add row to Smartsheet.

(0) Share

Report

Posted on by RichLucas

Hello All,

This is my first Flow so I'm trying to piece together what I have discovered in my searches.

I'm trying to copy text from an email and add it to a row in Smartsheet.

Email looks like this:

SUBJECT

ICPO SUBMITTED FOR APPROVAL #IC79-0591 Plywood

BODY

Submitted for approval by USER.

Job: Job_Name LA791723
Order Date: 12/2/2019 5:00 PM
Need By: 12/3/2019 5:00 PM
EF Job Number:

Cheers!

Additional hyperlink here.

I want to extract the following text and add it to the row.
#IC79-0591
Plywood
USER
LA791723
Need By: DATE

Any assistance would be greatly appreciated.

All responses (5)

Answers (1)

v-alzhan-msft on at

Like (0)

Report

Re: Extract email content to add row to Smartsheet.

Hi @RichLucas ,

Have your problem been solved by @Amanthaper 's solution?

If yes, please go ahead and mark the post as solved by clicking “Accept as Solution” so that this thread will be marked for other users to easily identify!

Best regards,

Alice

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Verified answer

Amanthaper 441 on at

Like (0)

Report

Re: Extract email content to add row to Smartsheet.

Hi @RichLucas, you're on the right track. Kudos on your attempt in your first flow.
I would have (and have indeed) used the same combination of functions\expressions.
Source
Flow / Results
At a high level, each variable is tied to an expression. They are:
IC number - grabs anything between "Approval" and "Plywood" in subject
substring(triggerOutputs()?['body/subject'],add(lastIndexOf(triggerOutputs()?['body/subject'],'APPROVAL'),9),sub(lastIndexOf(triggerOutputs()?['body/subject'],' '),add(lastIndexOf(triggerOutputs()?['body/subject'],'APPROVAL'),9)))
Product - Grabs anything after the ICnumber(#IC79-0591) in subject
substring(triggerOutputs()?['body/subject'],lastIndexOf(triggerOutputs()?['body/subject'],' '),sub(Length(triggerOutputs()?['body/subject']),lastIndexOf(triggerOutputs()?['body/subject'],' ')))
User - Anything between 'approval by' and 'Job:' in the body
substring(triggerOutputs()?['body/bodyPreview'],add(indexOf(triggerOutputs()?['body/bodyPreview'],'approval by'),11),sub(indexOf(triggerOutputs()?['body/bodyPreview'],'Job:'),add(indexOf(triggerOutputs()?['body/bodyPreview'],'approval by'),11)))
Job - Grabs anything between 'Job:' and 'Order Date:' in the body
substring(triggerOutputs()?['body/bodyPreview'],add(indexOf(triggerOutputs()?['body/bodyPreview'],'Job:'),4),sub(indexOf(triggerOutputs()?['body/bodyPreview'],'Order Date:'),add(indexOf(triggerOutputs()?['body/bodyPreview'],'Job:'),4)))
Need By - Grabs anything between 'Order Date:' and 'EF Job Number:' in the body
substring(triggerOutputs()?['body/bodyPreview'],add(indexOf(triggerOutputs()?['body/bodyPreview'],'Need By:'),8),sub(indexOf(triggerOutputs()?['body/bodyPreview'],'EF Job Number:'),add(indexOf(triggerOutputs()?['body/bodyPreview'],'Need By:'),8)))
This was quick and very preliminary.The expressions may need tweaking and you can choose to use variables or not. It's up to you. Might be easier to use variables to help troubleshoot.

Hope this helps,
Aman
------------------------------------------------------------------------------------------------------
If my post helps you with your problem or answers your question, please mark it Solved or Answered. This helps anyone with similar challenges. If you like my response, please give it a Thumbs Up.
------------------------------------------------------------------------------------------------------
RichLucas 4 on at

Like (0)

Report

Re: Extract email content to add row to Smartsheet.

Thank you @Hardesh15 for replying.
When you have a chance, could you elaborate more?
Hardesh15 7,087 Super User 2024 Season 1 on at

Like (0)

Report

Re: Extract email content to add row to Smartsheet.

@RichLucas

Let make it in simple way using split.

1 use compose pass subject

2. Another compose with expression

Last(split(compose (output),'Approval'))

It will give your string starting with # in subject.

3. To collect words from string which we got in step 2. Use another compose pass same expression with index like Last(split(compose (output),'Approval'))[0] it will give you #with code string.

In this way you can extract all words.

Use split, first, last,index.

Not infront of laptop so unable give screen. If you need will give you later.

Thanks

Hardesh
RichLucas 4 on at

Like (0)

Report

Re: Extract email content to add row to Smartsheet.

This is what I have so far.