How to compare a Sharepoint list with a Excel file in Automate

Like (1) Share

Report

Posted on 10 Sep 2021 21:26:15 by JorgeT

Hi everyone,

I new in automate and i've been trying to create a flow that compare a Sharepoint list with a Excel file, i have a Excel file with pending tasks ids and the user assigned to each of them, for example:

And i have a sharepoint list with all users and emails into the work team, like:

I need to send the ids of their pending tasks to the email of each user in the excel file, I've been trying to do this flow for two weeks, which at first I thought was simple, but the flow always fails me for some reason, I think that the answer is in the use of filters but I don't know how to use them

Thanks in advance!

All responses (8)

Answers (1)

Verified answer

Ellis Karim 11,179 Super User 2025 Season 2 on 11 Sep 2021 at 17:35:18

Like (2)

Report

Re: How to compare a Sharepoint list with a Excel file in Automate

Here is one possible approach to your problem. The flow produces an email message text for each user in the SharePoint List that has a task in the Excel file:
This is the high level flow:
(1) Starting with Get Items and List rows present in a table actions:
(2) We create an Apply to Each loop, and for each SharePoint item record, we will try to find a matching user record in the Excel data. We can use the Filter Array function find and select these user records. Get get all the Excel rows where the SharePoint column user name (TeamUser) matches the Excel column User:
(3) We can use the Select action to select only the Tasks for that user from the Filter Array action. This way we can ignore any other user data we dont need or want to see:
Example:
(4) Next we check the output of the Select action to find out if any tasks were found:
If Yes, we can construct the email text body including a task list in a Compose action:
And if no tasks were found we construct a different email text in a Compose action:
Here is a summary of the condition:

Hope this helps.
Ellis