cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wnguyen23
Helper III
Helper III

Flow to Update/Create/Delete list in SharePoint by using excel

Hi I'm new to flow and was wondering if there is a way to compare 2 arrays and make updates/creates/deletes?   If not array, then any other direction/guidance will be helpful!

 

For example an excel sheet with data as such:  Red, Blue, Green

For example a sharepoint list with data as such:  Red, Blue, Orange

-If sharepoint list is different, update with data from excel, but only the entry that is different.

-If the item in the excel sheet got deleted, then the sharepoint list will get updated.

-If the item exist in excel but not sharepoint, create it.

I've tried something like this but it only creates the item the first time.

 

Thanks!

Untitled.png

1 ACCEPTED SOLUTION

Accepted Solutions
SCTdan
Continued Contributor
Continued Contributor

Slightly more complex than I initially thought, but this will keep a sharepoint list in sync with an excel file.  

 

https://i.imgur.com/cj2l5F0.png

 

Just set whatever fields you want to compare on (where I have Title vs C1), and compare for updates (C3 vs S3)

 

 

Now, the easier option is to just delete all the items in the sp list and then just recreate it from the excel data.

 

Let me know if you have any questions on that

 

Thanks

 

 

If you have found my post helpful, please mark thumbs up.

If this post has solved your problem, please click "Accept as Solution".

 

 

View solution in original post

18 REPLIES 18
SCTdan
Continued Contributor
Continued Contributor

The reason it's only doing one is because you need to put it inside an Apply to Each loop, so it looks at each entry.  But you'll also need some extra logic to account for the other conditions you've specified.

 

 

Think of it like this (For each = Apply to each, If = condition)-

 

  • For each item in excel
    • If it exists in sharepoint 
      • Yes it exists,
        • If it is the same (based on whatever property)
          • No
            • update
      • No it does not exist
        • Create

 

  • For each item in sharepoint
    • If it exists in excel
      • No
        • delete sharepoint item

 

Now, you don't need to create an array for the sharepoint or excel data.  Get Items and List Rows both return data in array format already, so you can just use the appropriate field in your condition and it'll automatically put it inside a Apply to each loop.

 

Let me know if that makes sense.  If you're not sure, I can put an example together in flow in a little while.

@SCTdan Thanks for the quick reply!  If you get a chance can you put an example together for me?  thanks!

Can we compare each item from sharepoint to its excel counter part?

 

abm
Super User III
Super User III

Hi @wnguyen23 ,

 

What's your trigger for this flow? If you want to iterate then you need a flow step Add an apply to each or Add a do until. It all depends on your logic what you trying to implement. From your step it only shows Get Items. Is any other steps before that?

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials

Capture.PNG@abm this is all I have, will you be able to provide an example for me?  Thanks!

abm
Super User III
Super User III

Hi @wnguyen23 ,

 

Thanks for your screenshot.

 

What's your first data source? Excel sheet? 

 

So read the Excel sheet and compare the Sharepoint list?

 

If  you could tell me something like this that will be great.

 

1. Read Excel Data (More than one rows  exists)

2. Compare SharePoint List Exists or not

3. Then make decisions to create or delete?

 

So if you could reveal your pseudo code I could try building something for you. 

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials

@abm 

1.  Read Excel Data

2.  A blank sharepoint list exist with corresponding columns to excel

3.  Compare sharepoint list to excel, if empty then copy data from excel to sharepoint

4.  If sharepoint has data that does not exist on excel, then delete

5.  If some changes are made in excel, compare with sharepoint and update if different

6.  If new data is added to excel, compare with sharepoint and create new entries

 

Something like this, thanks!

SCTdan
Continued Contributor
Continued Contributor

Slightly more complex than I initially thought, but this will keep a sharepoint list in sync with an excel file.  

 

https://i.imgur.com/cj2l5F0.png

 

Just set whatever fields you want to compare on (where I have Title vs C1), and compare for updates (C3 vs S3)

 

 

Now, the easier option is to just delete all the items in the sp list and then just recreate it from the excel data.

 

Let me know if you have any questions on that

 

Thanks

 

 

If you have found my post helpful, please mark thumbs up.

If this post has solved your problem, please click "Accept as Solution".

 

 

View solution in original post

abm
Super User III
Super User III

Hi @wnguyen23 ,

 

Here are the overall details.

 

Flow step 1
Recurrence. Schedule to run every 1 hour or so.

 

Flow step 2
List rows present in a table. Se the Filter Query where FlowChecked is 'No'

 

Flow Step 3
Apply to Each

 

Flow Step 4
Send an HTTP Request to SharePoint
Set the site address: Your sharepoint address
Method: GET
Uri: _api/web/lists/GetByTitle('yoursharepointlistname')/items?select=Title,Id$filter=Title eq Name

 

Flow Step 5
Check condition and do the rest. Create or Delete


Flow Step 6
Update the excel sheet column FlowChecked set to Yes. So that in next occurence this won't process again.

 

If you need further help please let me know.

 

If you may found this useful then please accept this as solution.

 

Thanks

 

 



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials

@SCTdan thanks for creating the flow!  If I wanted to create a comparsion for each item, I would need to create a condition parallel to 'update sharepoint item'? and compare let's say S4 is not equal to S3? and one parallel to that for each individual comparison and update?

SCTdan
Continued Contributor
Continued Contributor


@wnguyen23 wrote:

@SCTdan thanks for creating the flow!  If I wanted to create a comparsion for each item, I would need to create a condition parallel to 'update sharepoint item'? and compare let's say S4 is not equal to S3? and one parallel to that for each individual comparison and update?


That's correct, yes.  

jukow
Frequent Visitor

Hi,

 

I've tried your solution, however, my flow is running all the time on "apply to each" stage. 

 

I would like to check by Flows if the value from column Y in Sharepoint List is in Excel in column X.

Additionally, base on that I would like to get info from column Z in Excel where was that value.
Some kind of vlookup I guess.

 

Would you be so kind and help me with that? 

 

my flow chart: https://imgur.com/a/Z4BOMNv

 

 

SCTdan
Continued Contributor
Continued Contributor

How many rows in your excel spreadsheet?  Does it ever time out?  

jukow
Frequent Visitor

In Excel I got around 400, in SharePoint I made 23. 
One test run has been running since 2,5h now...

 

But I guess it runs through the whole workbook and I would like to run it in one column. 

SCTdan
Continued Contributor
Continued Contributor

Looking at your flow, you don't need the 'Get a row' action inside Apply to Each 3.  You already have a 'List rows present in table' outside the loop, use that.  

 

 

Edit: It's probably taking so long because the Excel connector has a limit of 20 api calls per minute, with a 1 minute renewal period.  So by calling 'Get a row' inside the loop, every 20 runs it's pausing for an extra minute because it hit the limit.

jukow
Frequent Visitor

Ok, I will delete the Get a row.

 

So it should take around 40 min (400 rows / 20 API calls = 20 * 2 min = 40 min) and it's running since 2.5h so I don't know what is wrong. Or one API call is for one cell? 

 

Does the flow look correct to what I want to achieve? 

SCTdan
Continued Contributor
Continued Contributor

Well that time is just what's spent waiting.  You still have to factor in the time of each operation in the loop.  2 conditions and a possible sharepoint item update.  2.5h still seems rather excessive though yes.  But give it a shot without that extra Get a Row action and see how that changes it.  

jukow
Frequent Visitor

Made some modifications and now it takes 6-10 minutes to update 8 items in a SharePoint list. 

 

Is there any way to stop the loop if all items list has been already updated? 

How does it take so long, isn't this just useless if you can only have 8 rows? I want to update/remove/delete 8000 rows inside of my excel list, I guess this is not do-able? @jukow @wnguyen23 @SCTdan @abm 

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (1,109)