Getting Excel attachment from outlook mail, then populate sharepoint Excel file with the data

[MadsV]

Hi Automaters,

I'm trying to create a flow where I do the following:

1. Register when a mail comes into a shared mailbox in Outlook. With a specific name for example: "Robot Data: 20/10/2023". In this activity i need help figuring out how to make sure I would also catch future mails where the date is at a later time. Is it possible to just write "Robot Data: *" in my SubjectFilter in the activity?

2. Then i need to get the attached file from the mail (Always an Excel file).

3. Take each row and populate them into an Excel file that is in a Teams group.

How would I go about each step?

[AlexEncodian · Accepted solution]

@MadsV 

1) Yes - you can just write "Robot Data:" in the Subject Filter. Best way to ensure it works, is to test it out.

2) Get file attachment and check the file extension e.g. Solved: get ONLY PDF attachement from email and save to Sh... - Power Platform Community (microsoft....

3) If your data is in an Excel content type Table, then you can use the native Excel connector "List Rows" action.

4) Loop through each value and use Excel connector to Add rows in your new excel file (again assuming your target excel file data is in a table structure).

If your data is not in a table content structure, you might be able to use the following Encodian actions:

Get Rows from Excel
Add Rows to Excel
Parse CSV

[MadsV]

Hi @AlexEncodian,

First I would like to thank you a lot for replying 🙂 

1. Great that I can just write the first part in my Subject Filter.

2. It seems that this just loops through and checks if the file format is the correct type. Which I don't need since i know that every mail i get with the specific Subject will only have 1 attachment that is an Excel file. (Please correct me if I didn't understand the post that you linked 🙂 )

3.  Should i use the "List Rows..." or the "Add a row into a table" function for what i'm trying to do? 

- That is: Take the rows from the Excel attachment from the mail, and insert them into an existing Excel File that is located in a Teams folder.

4. That makes sense 🙂 

[EmadBeshai]

Hi @MadsV ,

I think you can find the needed solution after checking the below link 
Extract excel data from email attachment and add t... - Power Platform Community (microsoft.com)

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

[MadsV]

Hi @EmadBeshai,

Thank you for replying. 

I can't seem to wrap my head around how to apply that solution to my problem. 

[EmadBeshai]

Hi @MadsV ,

1- As you mentioned you can just write "Robot Data:" in the subject filter.

2- Getting the file attached and make sure from its extension by following the second step link at @AlexEncodian's reply.

3- If you want to catch each row inside the excel sheet and create an excel file then you need to follow the same steps here Extract excel data from email attachment and add t... - Power Platform Community (microsoft.com)

4- To send these files to teams group then you can follow this article File management with Power Automate and Microsoft Teams - nBold

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

[MadsV]

Hi @EmadBeshai,

I've almost finished my flow, the only thing i'm struggling with is getting the data from sharepoint when the file is dynamic (e.g. it changes name for each time).

The flow does the following now:

1. Checks the mailbox for mails with a specific subject and attachment.

2. Takes the attachment and uploads it into a folder to Sharepoint

3. STEP THAT IS NOT DONE - Take the data from the excel sheet and paste into another excel sheet in another Sharepoint folder. (I can only do this when the filename is static (Doesn't change)) - So if you could help me with how i use a file that changes name for each run.

[EmadBeshai]

Hi @MadsV ,

Which file you are facing the issue with, is it the excel sheet created from the email's attachment or the excel you want to copy the rows to it?

[expertopinionsa · Accepted solution]

Hey there!

Sure thing, happy to help you out with this automation adventure. 

Subject Filtering in Outlook:

For subject filtering in Outlook.. Yup, you're on the right track! To catch those future mails, use "Robot Data: *" in your SubjectFilter. The asterisk acts like a wildcard, so it'll match any subject that starts with "Robot Data:" followed by anything. 

Now, for the Excel attachment snatch. In your flow, after the trigger, add an action to "Get attachments." Choose the "List rows present in a table" action from Excel Online (Business), and point it to your Teams group Excel file. This sets the stage.

To populate rows into your Teams group Excel file, use the "Add a row into a table" action. Map the columns from your incoming Excel attachment to the corresponding columns in your Teams group file. It's like giving each piece of data a comfy seat in its new home.

Remember to handle potential hiccups gracefully, like checking if the attachment is indeed an Excel file. This is how me and my team at triotech systems work. Test, tweak, and you're good to go!

Give it a whirl and let me know how it dances. If any snags come up, we'll untangle them together.

[MadsV]

Hi @EmadBeshai,

It's the file that i get from the email and i upload to sharepoint. After uploading it to sharepoint i can't access it again when i try to "Add row into a table"

The reason i can't access it, is because i don't know how to get the file name from sharepoint since the file i upload changes name everytime i upload a new file

[MadsV]

Hi @expertopinionsa,

What wonderful message, it was a joy to read 😄 

The only place i have problems is with this part of the flow:

"To populate rows into your Teams group Excel file, use the "Add a row into a table" action. Map the columns from your incoming Excel attachment to the corresponding columns in your Teams group file. It's like giving each piece of data a comfy seat in its new home.." 

I have a hard time figuring out how i get a hold of the Excel file i just uploaded into sharepoint. Because the name isn't static, it changes everytime the flow runs because of the date 🙂

[EmadBeshai · Accepted solution]

Hi @MadsV ,

You should be able to find the file name when you click on the dynamic content as shown below

If this post helps you with your problem, please mark this answer as Accepted Solution.
If you like my response, please give it a Thumbs Up.

[MadsV]

Hi @EmadBeshai,

Sorry for all the trouble, I might have miscommunicated my issue.

I don't have trouble taking the attachment from the email and uploading it into sharepoint. 
The issue is when trying to write the data from the uploaded file, into the static document that is always available on Sharepoint.

I get the following error when running:

"The execution of template action 'Apply_to_each:_Sæt_data_ind_i_Statistikark' failed: the result of the evaluation of 'foreach' expression '@outputs('Create_file_Børneflyt2_-_Sharepoint')?['body/Id']' is of type 'String'. The result must be a valid array."

[MadsV]

I see my issue now since i used the apply each to "Name" content from the sharepoint and it needs to be an array. Now i switched it to "Value" content from the List data, but i get this error instead 

"File /BF2 PowerAutomate Midlertidig test/Børneflyt2_Robotdata_test_23102023.xlsx is locked for shared ussage of {My ID}[membership].
clientRequestId:...
serviceRequestId:..."

[EmadBeshai]

Hi @MadsV ,

Please make sure to close the file you are updating against if you are opening it anywhere, and also make sure if you give the needed SharePoint permissions to the user where the power automate is running by using this user's connection.

If this post helps you with your problem, please mark this answer as Accepted Solution.

If you like my response, please give it a Thumbs Up.

[expertopinionsa]

Hi @MadsV 

I'm thrilled to hear that you found my message helpful! Let's tackle the challenge of dynamically retrieving the Excel file from SharePoint, considering the changing date in the file name.

After you've uploaded the Excel file to SharePoint, you can use the "List files in folder" action for SharePoint. Configure it to point to the folder where your Excel files are stored within the Teams group. Since the file name changes with the date, you'll need to filter the results based on some other criteria.

• Add the "List files in folder" action for SharePoint.
• Specify the folder where your Excel files reside.
• Add a condition to filter the files based on criteria like file creation/modification date or any unique identifier present in the file content.
• For example, if the date is present in the file content or metadata, you can filter the results to match the date of the incoming email.

Once you have identified the correct file, you can proceed with the "Add a row into a table" action as mentioned in the previous message.

Remember to handle any potential errors or exceptions gracefully to ensure the robustness of your flow.

Give this approach a shot, and if you encounter any hiccups or need further clarification, feel free to reach out. We're in this automation journey together!