Unanswered

Flow to keep SharePoint list synced with Excel table

(0) Share

Report

Posted on by RR-17031910-0

Hello all, new to forum, so I may not be saying my question in quite the right way, but here it goes. I've been trying to understand how to build this flow for a minute and scoured forums on internet. Looking for a simple way to keep a SharePoint List updated from an Excel table. Just trying to add and update items as the Excel table changes.

Here's my flow:

My questions center around:

The problem is I'm not sure how to write the Condition statement (i.e. length equal to or not equal to zero) and its confusing me.
I'm not sure if I should be having another "Apply to each" OR "For each" inside the True or False statement.
Lastly, when I have gotten close to almost solving, the Update Item was creating duplicates in my SharePoint list or removing data that was already there.

Thank you for whatever help or direction someone can provide so I can become more proficient with flows.

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (1)

creativeopinion 10,502 Super User 2025 Season 2 on at

Like (0)

Report

@RR-17031910-0 It's best practice to have the Get Items action in the root of the flow. Nesting it inside an Apply to Each action is inefficient. You will need to use a Filter Array action instead to cross-reference the data from your Excel Table and your SP list to see which items you need to update or create. I cover how to do that in the first YT Tutorial linked below. I've included a couple other YT Tutorials that you might be interested in.

Are you using the Microsoft Power Automate Filter Array Action wrong?

In this video tutorial I’ll show you 3 practical ways to use the Filter Array action and how to use it properly.
1️⃣ Cross-Referencing Data
2️⃣ Filtering by Key
3️⃣ Substring Matching

Did you know that the Condition action has a limit of 10 conditions? Although it might look like the Filter Array action can only accept one condition—this is not true. By using the advanced mode you can enter multiple conditions into a Filter Array action with an expression.

IN THIS VIDEO:
✓ 3 Ways to Use the Filter Array Action
✓ How to use the Scope Action to Group Actions
✓ How to Check the Number of Items returned from a Filter Array Action
✓ How to Cross-Reference Data in Excel with a SharePoint List
✓ How the Filter Array Action Works
✓ How to Access the Dynamic Content from a Filter Array Action
✓ How to Filter Items by a Key
✓ How to Filter Items by Matching a Substring
✓ How to Use Multiple Conditions in a Filter Array Action

---

Filter Array + Apply to Each: The Best Tip You Need to Know

In this tutorial—I’m going to show you a quicker way to get the dynamic content from your Filter Array action—and it doesn’t require writing an expression.

IN THIS VIDEO:
✓ How to Loop Through Filter Array Results in Power Automate
✓ Using Apply to Each with Filtered Arrays
✓ The Easiest Way to Access Dynamic Content from Filter Array
✓ Fixing Nested Apply to Each Actions
✓ When to Use Value vs. Body Dynamic Content
✓ Simplifying Power Automate Flows with Filter Array
✓ Troubleshooting Filter Array and Apply to Each Issues

---

3 Mistakes YOU 🫵 are Making with the Apply to Each Action in your Microsoft Power Automate Flow

In this video tutorial I’ll go over how to avoid these common mistakes when using the Apply to Each action in a Power Automate flow:
1️⃣ Looping through a Single Item
2️⃣ Creating Unnecessary Nested Loops
3️⃣ Looping through an Unfiltered Array

At the end of the video I share a few helpful insights when it comes to using the Apply to Each action in your flow.

IN THIS VIDEO:
✓ How to avoid the Apply to Each action with a single item array
✓ How to use the item() function to access dynamic content in an array
✓ How to prevent unnecessary nested Apply to Each action loops
✓ How to use the Select action
✓ How to convert an array to a string with the Select action
✓ How to use the Filter Query field
✓ How to count the number of items in an array
✓ How to use a condition control
✓ How to use the concurrency control
✓ How to set a top count
✓ How to use Compose actions for troubleshooting

Hope this helps!

Consider giving me a ❤️ if you liked my response!

👉 Level up your Power Automate skills by checking out my tutorials on YouTube
👉 Tips and Tricks on TikTok and Instagram

Was this reply helpful? Yes No
RR-17031910-0 8 on at

Like (0)

Report

Well, I watched and rewatched video but still having an issue. Using Compose I can see I'm returning the right results of the ID of a column in Excel and the corresponding column in SharePoint list.

But when it comes to my Filter Array, I'm having an issue getting the results correct there. In the From I'm using my Excel data, then my first filter query is my Sharepoint IDs does not contain Excel IDs.

However, its like the two aren't really comparing each other in my output. So, I'm a little stuck.

flow 1.png

Was this reply helpful? Yes No
ronaldwalcott 3,847 Super User 2025 Season 2 on at

Like (0)

Report

I have a question.

Why would you do this? If the structure in the Excel file isn't maintained correctly then the process won't work.

It presents the possibility that your data may be compromised.

What are you trying to accomplish? There may be better ways of accomplishing your goals.

Was this reply helpful? Yes No
rubin_boer 4,841 Super User 2024 Season 1 on at

Like (0)

Report
hi @RR-17031910-0

You on the right path if I get what you trying to achieve is to compare the two dataset and update SharePoint with the added data in Excel. If this is the case this is how you can achieve it with a loop to apply the changes only (you don't have an issue with this so I won't to venture into it).

Steps:

Get the data from SharePoint as the data to compare with.

Get the data from Excel as the data which will be compared

Filter the Excel Data by Data already in SharePoint

Loop there the data in step three to update SharePoint

Let's start with the result

This is an illustration to do this whereby i will use two array variables which in essence be the SharePoint (varSpoList) and Excel Data (varExcelData)

Figure: the variable being initialized in these steps in the flow

varSpoList

varExcelData

Now that we have the data we need to compare or filter them.

First lets select the data in varSpoList by adding a Data operation Select

Switch to advance mode in Map and add your column you wish evaluate here, im going to evaluate item so it will be item()?['item']

Now we have the SharePoint data, all that left is to compare or filter the Excel Data

Add a data operation Filter and from the Excel Data (your file) evaluate the output of the SharePoint Selected Data Output

So what does it look like when its all put together.

"Sharepoint" have the items 1,2

"Excel" have the items 1,2,3,4,5,6 - the user added the data 3,4,5,6 and that what we want to write to excel

We expect the filter to give us an array with the data 3-6.

Was this reply helpful? Yes No
RR-17031910-0 8 on at

Like (0)

Report
@rubin_boer So thank you for sharing that info. I think I am getting closer to what I'm trying to do. You are correct, I am trying to update a SharePoint list on an automated schedule once a day based on a current data set is in an excel table.

I have enforced a unique value in my SharePoint list on one of the columns and am using that column in both Excel and SharePoint to filter by.

My new issue is with the condition statement: My condition expression is length(body('Filter_array')) is not equal to 0. This flow creates the items just fine when nothing is in SharePoint. However:

If I add a new row in Excel, the flow says it failed, but it does add the new row to SharePoint. (strange).

If I update info in the mapped columns in Excel, nothing is updating in my SharePoint list because I get the following error

The 'inputs.parameters' of workflow operation 'Update_item' of type 'OpenApiConnection' is not valid. Error details: The resolved string values for the following parameters are invalid, they may not be null or empty: 'id'

Seems like I'm closer, but that final action of creating and updating items is giving me the problem. What am I missing?

Was this reply helpful? Yes No
Verified answer

rubin_boer 4,841 Super User 2024 Season 1 on at

Like (1)

Report
Ok @RR-17031910-0

Here we go (I was not aware you wanted to update as well), Steps

Get Rows From Excel and Get rows from List

Loop through all of the rows in Excel

Add a Fiter Array as the first action in the loop (Check if the title in SharePoint exists in Excel)

Add a condition based on the filtered array if the there is no items in the Array, then create it in SharePoint if not Update it

Result looks something like this:

Walkthrough

Get new Items - Filter array:

From: outputs('Get_items')?['body/value']

Filter: SharePoint item()?['Title'] , Excel: items('Apply_to_each')['Title']

{ "type": "Query", "inputs": { "from": "@outputs('Get_items')?['body/value']", "where": "@contains(item()?['Title'],items('Apply_to_each')['Title'])" } }

Get row of existing Item - Filter array 1:

From: outputs('Get_items')?['body/value']

Filter: Excel: items('Apply_to_each')['Title'], SharePoint item()?['Title']

{ "type": "Query", "inputs": { "from": "@outputs('Get_items')?['body/value']", "where": "@equals(items('Apply_to_each')['Title'],item()?['Title'])" } }

Update the item where the Row ID is from the array above

first(body('Filter_array_1'))?['ID']

Putting it all together

Was this reply helpful? Yes No
RR-17031910-0 8 on at

Like (1)

Report

@rubin_boer Thank you so much for taking the time to help educate me on this!!!!!!!! Oh wow. I feel like my knowledge level just leaped forward. It's working just fine now and I have a better grasp of what is happening behind the scenes in Power Automate. Thank you, thank you, thank you.

Was this reply helpful? Yes No