Unanswered

Mirror Sharepoint List with Excel File

(0) Share

Report

Posted on by StuartSmith

225

I have read 10's of posts on how to great a flow to update a SharePoint list based on an Excel file, but I just cant them to work or they are not suitable for me.

I have therefore tried to create my flow, but struggling. I want the flow to auto check the Excel file (Stored in Sharepoint Docs) and if a row is present in the excel file, but not in sharepoint list, then add the row, if the row is present in the excel file and sharepoint list, to update the row in the sharepoint list and if the row is in the sharepoint list, but not the excel, then remove the row from the sharepoint list. So to summarise, the Sharepoint list should mirror the excel file.

I got this working with the flow clearing the sharepoint list and copying the whole excel file back into the list. Its not the most elegant and for a short period the sharepoint list is completely empty, which isnt great.

How can i improve this?

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (0)

DeepakS 2,301 Most Valuable Professional on at

Like (0)

Report

Hi @StuartSmith

To achieve the update/add functionality form EXCEL to SP List , you should use one column as key , generally in SharePoint its Id column but as you want to compare this with Excel data you have to use one(or more column).
Steps:
1-Get all rows from excel
2- For each row check if key column combination matched with same column is SharePoint list
1) Get Items SP List with query filter (key column value<from excel>)
3- If it exist update list item
4- If not create new item

Let me know if this make scene?

-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Was this reply helpful? Yes No
StuartSmith 225 on at

Like (0)

Report

Thanks for getting back to me, I am struggling with the condition... I guess I should be comparing the "Employee_ID" row value in the excel file to the "Employee_ID" in the Sharepoint list, but the condition doesnt allow show me the sharepoint list?

Sorry there was a screenshot attached, but it keeps getting removed.

Was this reply helpful? Yes No
DeepakS 2,301 Most Valuable Professional on at

Like (0)

Report

Hi @StuartSmith

It will be great if you can share a screen capture of you flow to understated it better

-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Was this reply helpful? Yes No
StuartSmith 225 on at

Like (0)

Report

This is what I have so far...

Was this reply helpful? Yes No
DeepakS 2,301 Most Valuable Professional on at

Like (0)

Report

Hi @StuartSmith

Two Solution:

Option 1)To check if item exist or not in SharePoint list you need to query SP list for each row in excel (not best but you can do it quickly)
include following in "apply to each" action:

Option 2) Recommend but require more work
->Get SP list items (All, hope fully you wont have 5000+ items in list)
-> Create an Array and add Employee_ID' column value to it
-> in "Apply To each" check if Excel Employee ID exist in array (you can use filter action)
-> if yes update list item , if no create new

-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Was this reply helpful? Yes No