Check details between SharePoint and Excel

[MB35]

Hi, 

Please can anyone advise the best way to go about a flow im trying to build. 

My data consists of a Sharepoint list (this is the main data source) - this includes a People/Group formatted column for email addresses.

I also have an excel sheet containing a single column table with a list of email addresses. 

The flow I need to build, which I'm really struggling with, is as below:

If the email address on Sharepoint is listed in the excel, send email to shared mailbox,

If not listed in the excel, send to the colleague

Any help would be great! Thanks

[harshdeol]

Hi @MB35 ,

You can check the SharePoint Person Type Column's email address with the email addresses of excel using the Excel's "List rows Present in a table" by applying filter query.

//Excel Sheet:

I created the excel excel sheet with a single column table for list of email addresses. (Column Name = Email)

//SharePoint List:

I have created a List in SharePoint (Name = List1) and a Person type Column (Name=Users).

//Assumptions:

I am assuming that the flow will check the email addresses when a new item is created in the SharePoint List. So, I am using the SharePoint's Trigger - "When an item is created".

Power Automate Flow:

Added the " When an item is Created" in the SharePoint list Trigger and Added a Action "List Rows present in Table" of Excel to get the list of email addresses. Here if you noticed I applied Filter Query to filter the email addresses based on the email address added in the Sharepoint List's Column.

The Logic we are implementing here is - if the email address is found in the excel table, the flow "List Rows present in the Table" Action's Output Body/value returns that row otherwise return the Empty array ([]), if email not found.

Now, added a condition to check if the Output Values of previous Action ("List Rows present in the Table").

- If the email address doesn't exist in the Excel Table, the "IF YES" action is executed, and email will send to the Shared Email address.

- Otherwise, the if email address is found, "IF No" section is executed and the email will send to your colleague (as per the configuation).

//Here is the complete flow: 

Now, Save the flow and test it by adding an item in the same sharepoint list.

//Testing:

For testing, I added two items in the list, and I received one email on my Shared Mailbox and other is on configured email address.

Please try this at your end and let me know if you have any question or face any issues.

-----------------------------------------------------------------------------------------------------------------
If your question got an answer, please click "Accept as Solution "✅, If you liked my answer, please hit the "thumbs-up" 👍button.

Thank you,
Harsh Deol

[MB35]

Hi @harshdeol , thankyou I will give this a go. 

My flow will be a recurrence rather than a 'when item is created' - the initial criteria checks a completion date stored in the sharepoint list, along with a few other conditions and so I was using a 'get items' action also for this part.

Would I be right to run my first set of conditions, and then under the 'if yes'  add the above flow you have provided to check the email address at that stage? Essentially matching the first set of conditions and then checking the address of those? 

[harshdeol]

Hi @MB35 , Sure, please give it a try.

Yes, you can add the first set of conditions in a Separate "Condition" as well and then add the email one. try it and let me know if you need further assistance.

-----------------------------------------------------------------------------------------------------------------
If your question got an answer, please click "Accept as Solution "✅, If you liked my answer, please hit the "thumbs-up" 👍button.

Thank you,
Harsh Deol