Answered

Extract text/digits from teams chat, then look up in Excel

(1) Share

Report

Posted on by Robotech12

Hi, looking for any assistance, if possible, this is the main goal:

1-An user will write in a group chat the "OPP-##########" (will always be 10 digit code plus the OPP-). The OPP# and general message will vary, for instance it can be:

"Hi all, I have this OPP-########## to discuss today"

"these are for today OPP-#########, OPP-##########"

2-Extract the "OPP-##########" and then VLookup in an excel file to bring key data such as Date, Status, Owner, etc..

This is my flow so far, but having trouble.

I am not sure if my process is correct, having difficulties to set the body of the teams message in a variable.

any help is appreciated. Thank you.

Categories:

Business Process Flows

I have the same question (0)

All responses (15)

Answers (2)

trice602 14,707 Super User 2025 Season 2 on at

Like (1)

Report
Re: Extract text/digits from teams chat, then look up in Excel

Hi @Robotech12 ,

Here's a similar flow that may be a good example to help out.

First, I parse json to get my lookup value that I want to find in Excel, then List rows present in a table.

Next, I filter my array to find the chat message variable and find it in my Excel table. Then I parse json the results from my filter array.

Next I am returning the results in Teams for the end-user

{ "type": "AdaptiveCard", "body": [ { "type": "Image", "url": "https://somwhere-my.sharepoint.com/aaaaaaa/bbbbbbbbbbbbbb/Documents/logo2.png" }, { "type": "TextBlock", "text": "Hello, here's the latest update.", "size": "Large" }, { "type": "TextBlock", "text": "Always glad to help! The Robots 🤖", "size": "Large" }, { "type": "TextBlock", "text": "ID no: @{items('Apply_to_each')['ID_no']}", "size": "Large" }, { "type": "TextBlock", "text": "Current Status: @{items('Apply_to_each')['Status']}", "size": "Large" }, { "type": "TextBlock", "text": "Last Comment: @{items('Apply_to_each')['Comments']}", "size": "Large" }, { "type": "TextBlock", "text": "Updated by: @{items('Apply_to_each')['UpdatedBy']}", "size": "Large" }, { "type": "TextBlock", "text": "Please select an option:", "wrap": true } ], "actions": [ { "type": "Action.Submit", "title": "Send Follow-up 📨", "data": { "selectedOption": "Option 1" } }, { "type": "Action.Submit", "title": "Escalate to Tier 2 🆘", "data": { "selectedOption": "Option 2" } } ], "$schema": "http://adaptivecards.io/schemas/adaptive-card.json" }

The results look like this:

If this reply was helpful, please mark as a solution so others can find it quickly too!

Was this reply helpful? Yes No
Robotech12 75 on at

Like (0)

Report

Re: Extract text/digits from teams chat, then look up in Excel

Hi @trice602 thank you for the feedback, is the below correct ? I do not have much experience in JSON.

Do I need to modify this code to get the OPP-##########(10 digits)?

Is this one corret? The Opportunity ID will be the actual OPP-######### in the Excel file.

I am not sure on how to modify the PARSOn Jason 2 part, is similar to the 1st one?

Was this reply helpful? Yes No
trice602 14,707 Super User 2025 Season 2 on at

Like (0)

Report

Re: Extract text/digits from teams chat, then look up in Excel

Hi @Robotech12 ,

In my example the chat messages are just the ID# and end-users are instructed to just key in the number for the status.

The scheme for Parse json comes from the output of the previous step and you copy the output and click on Generate from sample and paste it in and it will build it for you. You would do here and also after the filter array, you need to parse the json and get your scheme.

To find OPP-XXXXXXXX in the content of a message, you will need to add a compose here and get the number only with this expression. Please keep in mind this is going to find one OPP# in the message, it's not coded to lookup more than one.

split(body('Parse_JSON')?['body']?['content'], '-')[sub(length(split(body('Parse_JSON')?['body']?['content'], '-')), 1)]

Update your Filter array to find the number in the Excel sheet.

Was this reply helpful? Yes No
Robotech12 75 on at

Like (1)

Report

Re: Extract text/digits from teams chat, then look up in Excel

Thank you @trice602 I just made the changes:

1-I changed In "Get message details" to Message ID
2-From the "Get Message Details" I did a test and got the Outpot and copied, then I generate from Sample to get the Schema.
in compose I put the Split expression
but I am getting an error:, is because the Jparson I wrote incorreclty the Jparson?

Was this reply helpful? Yes No
trice602 14,707 Super User 2025 Season 2 on at

Like (0)

Report

Re: Extract text/digits from teams chat, then look up in Excel

Hi @Robotech12 ,

What was the chat message? In this example, with the extra compose, it is looking for OPP-XXXXXXXXX.

Was this reply helpful? Yes No
Robotech12 75 on at

Like (1)

Report

Re: Extract text/digits from teams chat, then look up in Excel

I wrote these examples @trice602

I have OPP-1234567890 for today
For OPP-1234567890 need to talk with John

Was this reply helpful? Yes No
trice602 14,707 Super User 2025 Season 2 on at

Like (0)

Report

Re: Extract text/digits from teams chat, then look up in Excel

Hi @Robotech12 ,

Ok, I modified my example because it was just an example anyway and made this work for yours. Here are the results:

My excel sheet:

I added these two steps:

This step is going to find the starting position of OPP in your messages and bring back the character number.

The compose directly above the Filter array is a substring. We want the text starting in character position number of OPP and then next 14 characters, so we get a result like OPP-1234567890

substring(body('Parse_JSON')?['body']?['content'],body('Find_text_position'),14)

Make sure your flow looks exactly like this example, with the exception of an adaptive card, you could just return the results as a message, but I used a default card sample, added a logo for testing, and works.

Second test results...

If this was helpful. please mark as a solution. I will be glad to help you get your project up and running this weekend; I am pretty much just building flows for the next 48 hours (give or take a little sleep in between)!

Was this reply helpful? Yes No
Robotech12 75 on at

Like (1)

Report

Re: Extract text/digits from teams chat, then look up in Excel

Thank you @trice602 , just doing it, but I am doing something wrong, but not sure where. Apologies in advance for all the questions.

On the 2nd step, am I correct here? In "get message details", should it be the "Message Id" ?

To generate the Parse JSON, I run a test and copy the Outpot Body and Generate.

but then I am getting this error:

Was this reply helpful? Yes No
trice602 14,707 Super User 2025 Season 2 on at

Like (0)

Report

Re: Extract text/digits from teams chat, then look up in Excel

Hi @Robotech12 ,

I think you are really close here, always glad to help!

Yes, the Message ID is what you will use but notice yours is in an apply to each and not needed. Delete that entire Apply to each and re-enter your Get message details as shown below. Your trigger and first step should look exactly like this:

On Parse JSON and Find Text Position --

Yes, you run it and get your sample JSON. When you go to the Parse JSON action, in your scheme, it should be completely blank when you originally started. Now, highlight all the text and delete it. Click on the Generate from Sample button and that's where you paste in your sample JSON, this will create the scheme for you. Your scheme is not correct and is the root cause and your find text position action is looking for text and should be content.

Was this reply helpful? Yes No
Robotech12 75 on at

Like (1)

Report

Re: Extract text/digits from teams chat, then look up in Excel

Hi thank you @trice602

Trying that part but every time I choose the "Get message details" and write "Message ID", the "apply to each" pops up every time. I have tried to move it out but not possible. is there an issue or configuration?

Was this reply helpful? Yes No