Hi,
I'm fairly new to power automate and looking for some help.
I want to create a flow where when a teams message is posted in a group chat (this can also be a channel message if easier), that the flow will identify a 7 digit number in the message (which I can ask the team to format so it is at the end or beginning of the message if easier) and adds the 7 digit number to a spreadsheet table along with the time the message was posted. Just for reference the typical message would look something like this: "Good evening all, please see attached performance and walk backs #######". I have been trying to create this alongside the help of ChatGPT but I haven't had much success. These are the steps Chat GPT has given me:
Choose a Trigger
- Name your flow (e.g., "Insert Teams Message into Excel").
- Search for the trigger: "When a new message is added to a channel" (from Microsoft Teams).
- Select this trigger and click Create.
3. Configure the Trigger
- After selecting the trigger, configure the following details:
- Team: Select the team where you want the flow to listen for messages.
- Channel: Select the specific channel within the team.
This ensures the flow is triggered every time a new message is added to the specified Teams channel.
4. Add an Action to Format the Data
- Click on New Step.
- Search for the Compose action. This will allow you to format the message content.
- Add dynamic content such as:
- Message content: This represents the actual message.
- From: The sender of the message.
- Date sent: The time and date the message was sent.
Optionally, you can use the Format DateTime action if you want to change how the date appears.
5. Add Excel Connector to Write Data
- Click on New Step.
- Search for the Excel connector.
- Select Add a row into a table.
- Configure the action by providing:
- Location: Select OneDrive, SharePoint, or wherever the Excel file is stored.
- Document Library: Choose the appropriate document library if applicable.
- File: Navigate to the Excel file where the data will be stored.
- Table: Choose the table inside your Excel file.
Once you've selected the table, Power Automate will automatically show fields for each column in the table.
6. Map the Teams Message Data to Excel Columns
- Map the dynamic content (from the Teams message) to the appropriate columns in the Excel table:
- Message content →
Message column.
- From →
Sender column.
- Date sent →
Date column.
This flow works, however it enters the whole message body into the excel.
When I ask chat GPT to modify to just extract the 7 digit number this is what it tells me:
1. Open Your Existing Flow
- Navigate to Power Automate and open the flow you previously created for inserting Teams messages into Excel.
2. Add an Action to Extract the Last 7 Digits
- Click New Step after the trigger (when a new Teams message is received).
- Add the Compose action to manipulate the message content.
3. Use an Expression to Extract the Last 7 Digits
-
In the Compose action, click the Expression tab.
-
Use the following expression to extract the last 7 digits from the message:
last(split(triggerOutputs()?['body']?['messageBody'], ' '))
Alternative Extraction Logic (if needed):
If the number isn’t always the last part of the message or if there are other complications, you can use the match() function to extract just digits from the message body:
substring(triggerOutputs()?['body']?['messageBody'], length(triggerOutputs()?['body']?['messageBody'])-7, 7)
-
This expression splits the message content into an array of words by spaces, and the last() function gets the last "word," which should be the 7-digit number at the end of the message.
Note: This assumes the 7-digit number is the last "word" of the message. If the number can sometimes be surrounded by text, you may need to refine this based on other patterns.
4. Modify the Excel Insertion Step
- In the step where you insert data into Excel, map the output of the Compose action (which contains the 7-digit number) to the appropriate column in your Excel table.
- Click on the column where the 7-digit number should go.
- In the dynamic content selector, choose the output of the Compose action (this will contain the 7-digit number).
However the first expression gives me this error: The error occurred because the 'split' function expects a string as its first parameter, but it received a 'Null' value. To fix this, you should check the output of the 'triggerOutputs' operation to ensure that 'messageBody' contains a valid string before using it as input for the 'split' function. and the second expression is invalid.
Is anyone able to help please?
