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!

I have the same question (0)

All responses (8)

Answers (1)

Ellis Karim 11,235 Super User 2025 Season 2 on 20 Mar 2024 at 23:59:50

Like (0)

Report

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

Hi @PowerAppUser25 ,

If we have the following SharePoint list containing a list of applications:

...and we want to import the following Excel table into the SharePoint list:

but we don't want to import Applications that already exist in SharePoint, a flow could look like this:

(1) Read the Excel table of Applications we want to import

(2) For each Application in our Excel sheet use Get Items to find a matching application in SharePoint (i.e. Is the application already present in our SharePoint list?). Note the use of the single quotes in the Filter Query below:

The length function can be used to count the number of matching applications that were found in the SharePoint list. A length of zero means nothing was found.

length(outputs('Get_items')?['body/value'])

If nothing was found, add the application to our SharePoint List:

Hope this helps.

Ellis
____________________________________
If I have answered your question, please mark the post as ☑️ Solved.
If you like my response, please give it a Thumbs Up.
My Blog Site

Was this reply helpful? Yes No
PowerAppUser25 15 on 18 Mar 2024 at 09:01:53

Like (0)

Report

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

Hi@ekarim2020 ,
I want to add one condItion in my flow, i will upload excel file which will have application names. if the applicationname already present (Application Details sharepoint list name )it should not add the applicationname in that list.
Could you please help me with this

Was this reply helpful? Yes No
JorgeT 20 on 14 Sep 2021 at 20:50:39

Like (0)

Report

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

Thanks, more than the solution to my problem was what I learned from your answers!

Was this reply helpful? Yes No
Ellis Karim 11,235 Super User 2025 Season 2 on 13 Sep 2021 at 18:20:13

Like (0)

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

This will require a small change to the flow. We can join the text for Task Priority and Task together in the Select action to produce an email body text like this:
The expression to enter is:
concat('Priority ', item()?['Task Priority'],': ',item()?['Task'])
Ellis

Was this reply helpful? Yes No
JorgeT 20 on 13 Sep 2021 at 17:10:56

Like (0)

Report

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

Thank you very much, I had not seen that little button, one last question, if also in excel I had a column called "priority" with values of (1, 2, 3) like:

How could I relate the "task" array to the "task priority" array returned by select operations?
So that in the email the tasks are listed by their priority, for example:

Dear Anthael
Your tasks are:
Priority 1: 6474
Priority 2: -
Priority 3: 3424

I think of making an apply to each to go through the tasks and a counter and relating them by the indexes, but I don't think this is the most optimal way to do it.

Anyway, thanks for your response!

Was this reply helpful? Yes No
Ellis Karim 11,235 Super User 2025 Season 2 on 13 Sep 2021 at 15:54:44

Like (0)

Report

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

Please press the small icon to toggle the mode to text mode:
Ellis

Was this reply helpful? Yes No
JorgeT 20 on 13 Sep 2021 at 15:02:09

Like (0)

Report

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

Thanks a lot!
Just one question, I don't understand how the "Select" indicates that the map should take the value "Task", in the dynamic content of the map section i only see the options "Element" and "Body" and I don't know how to tell it to take the value "task" from the filter array
Thanks for the reply

Was this reply helpful? Yes No
Verified answer

Ellis Karim 11,235 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

Was this reply helpful? Yes No