cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Manc_Gurl
Helper V
Helper V

Excel to Sharepoint Scheduled Flow

I've created a flow that goes through an excel file stored in a document library and updates a SharePoint list based on the contents of the spreadsheet. It deletes items from the list if they're not in the spreadsheet, creates now items in the list if they're not in sharepoint and so on.  This worked fine when I was just starting out with a few items in my excel file, but now I have over 1500 rows.  The first issue is that it seems to take forever to run because it's basically looping through all items.  I'm now wondering if it would be quicker to delete the contents of the SP list with the flow at the start then just import the entire spreadsheet into the list with the flow, I'm not sure if that's possible to do?

 

The other issue I was having was that some columns weren't getting populated, I'm not sure why, I checked all the columns, I may just try to recreate the flow to see if that resolves the issue there.

 

The third issue was that it was only retrieving a maximum of 256 items from excel and copying them into SP list.  I found another post that says the limit is 2048 and the 256 limit can be changed to 2048.  Is 2048 really the most rows I can copy into the list with a flow?

 

Sorry for all the questions.

7 REPLIES 7
manuelstgomes
Community Champion
Community Champion

HI @Manc_Gurl 

 

You've got yourself a challenge there. Let's see if we can figure this out.

 

SYNC

Pulling all items is not scalable. Is your flow running once a day or triggering automatically? 

 

Let's say that you run this once a day. Add a "Changed Date" to Excel. SharePoint already does that for you, so you don't need to worry about that. Then, when you run the Flow, you can fetch only the ones that changed during that period. By doing this, you only get items that you need to do something and not all of them.

 

Also, you'll get a lot fewer rows, and the Flow will run a lot faster. If you hit a limitation, increase the period. For example, if you get 1000 items to sync if you sync once a day, do it 2 or 3 times a day, and the number of things to sync will be lower. 

 

Does it make sense? 

 

To fetch only the ones parsed in a specific period you can use the Advanced Options (to access it press the "Advanced Options") and in the filter query do something like:

 

small-Screenshot_2019-10-22_at_10_46_41.jpg

 

ISSUES IN POPULATING

These issues are trickier to debug, but try to solve the first issue first. Then you'll have fewer data do check, and probably you can find a pattern. Check if you're trying to insert data in columns that are not correct, for example, a date formatted incorrectly.

 

MAX ITEMS

You can check the solution above. Run more frequent updates until you have only a few items to sync at a time. It will run faster and will have less information to sync.

 

Does this help you?

 

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

Cheers
Manuel

 

 

yashag2255
Super User
Super User

Hey @Manc_Gurl 

 

So as you are iterating over each item it definitely will take a lot of time. If you can share a few screenshots of the flow you have built so far, we might be able to suggest some workarounds to make it faster. 

 

About the other question on the row limit, I myself tried for about 36K rows and it worked just fine. In the list rows action, you can mention the number as 50000 and it can get you those many nmber of rows. (50K) is the limit here and you might not get 50K rows due to the data restrictions but the point here is you can get upto 40K easily. 

 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

11223344556677

 

Here is what the flow looks like now. It's probably not right and there are probably much better or more efficient ways of doing what I'm doing, I'm still fairly new to all this Flow stuff 🙂

 

 

Hey @Manc_Gurl 

 

This looks good. Here is one more thing you can do to keep deleting items on the go: Use an array for the unique values or values that you want to keep and then check with those, delete the ones that do not match. 

 

You can refer this blog for some hints and tips: https://powerusers.microsoft.com/t5/Microsoft-Flow-Community-Blog/Delete-duplicate-records-items-fro... 

 

This might make your flow a bit faster. 

 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

@yashag2255 Thanks for the information that will help a lot 🙂

 

This might seem like a crazy idea, but I am thinking now it might be better to just empty/delete the SP list items every week (not sure if this is possible with a flow)?  Then run a flow to import the whole spreadsheet into the list.  That cuts out the need for iterating through the list and checking for updates, if I could use flow to delete the entire list contents and then copy the excel data into via flow to replace it all that would work too.  Would that be more efficient? And if so how the heck do I do that 😄

Hey @Manc_Gurl 

 

Yes you can do that as well. But does the excel get new data every week? Because if not, it would not be that efficient to reload all the data you just deleted from the list. 

 

However, if you plan to do it that way, you can set up a recurrecne flow and then get items from SP and delete item action to delete the items. Ensure that you appropriately coordinate the two flows so that one does not interfere with the outcomes of the other and produce any undesired results/scenario. 

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

@yashag2255 I took a look at that article and I don't really understand it or rather I don't really understand how to apply that to my situation.  I've never done an array before so I'm not really sure how this would help me in my situation or how to apply that to my flow.  Would it speed it up? I think you're right about deleting all items not being efficient.  I really just want the flow to run faster, it is taking over half an hour just to do around 50 items so if I have over 1500 items in my spreadsheet you can imagine how long that is going to take to run.

 

The SharePoint list is never going to manually be changed, I want it to be updated only from the Spreadsheet contents.  The Spreadsheet it a query from a database that will change on a regular basis so I want any deletions, updates or new items in the spreadshee to be reflected in the Sharepoint list.

 

Could you give me some advice about how to use an array in my scenario to speed it up please?

Helpful resources

Announcements

Exclusive LIVE Community Event: Power Apps Copilot Coffee Chat with Copilot Studio Product Team

It's time for the SECOND Power Apps Copilot Coffee Chat featuring the Copilot Studio product team, which will be held LIVE on April 3, 2024 at 9:30 AM Pacific Daylight Time (PDT).     This is an incredible opportunity to connect with members of the Copilot Studio product team and ask them anything about Copilot Studio. We'll share our special guests with you shortly--but we want to encourage to mark your calendars now because you will not want to miss the conversation.   This live event will give you the unique opportunity to learn more about Copilot Studio plans, where we’ll focus, and get insight into upcoming features. We’re looking forward to hearing from the community, so bring your questions!   TO GET ACCESS TO THIS EXCLUSIVE AMA: Kudo this post to reserve your spot! Reserve your spot now by kudoing this post.  Reservations will be prioritized on when your kudo for the post comes through, so don't wait! Click that "kudo button" today.   Invitations will be sent on April 2nd.Users posting Kudos after April 2nd at 9AM PDT may not receive an invitation but will be able to view the session online after conclusion of the event. Give your "kudo" today and mark your calendars for April 3, 2024 at 9:30 AM PDT and join us for an engaging and informative session!

Tuesday Tip: Unlocking Community Achievements and Earning Badges

TUESDAY TIPS are our way of communicating helpful things we've learned or shared that have helped members of the Community. Whether you're just getting started or you're a seasoned pro, Tuesday Tips will help you know where to go, what to look for, and navigate your way through the ever-growing--and ever-changing--world of the Power Platform Community! We cover basics about the Community, provide a few "insider tips" to make your experience even better, and share best practices gleaned from our most active community members and Super Users.   With so many new Community members joining us each week, we'll also review a few of our "best practices" so you know just "how" the Community works, so make sure to watch the News & Announcements each week for the latest and greatest Tuesday Tips!     THIS WEEK'S TIP: Unlocking Achievements and Earning BadgesAcross the Communities, you'll see badges on users profile that recognize and reward their engagement and contributions. These badges each signify a different achievement--and all of those achievements are available to any Community member! If you're a seasoned pro or just getting started, you too can earn badges for the great work you do. Check out some details on Community badges below--and find out more in the detailed link at the end of the article!       A Diverse Range of Badges to Collect The badges you can earn in the Community cover a wide array of activities, including: Kudos Received: Acknowledges the number of times a user’s post has been appreciated with a “Kudo.”Kudos Given: Highlights the user’s generosity in recognizing others’ contributions.Topics Created: Tracks the number of discussions initiated by a user.Solutions Provided: Celebrates the instances where a user’s response is marked as the correct solution.Reply: Counts the number of times a user has engaged with community discussions.Blog Contributor: Honors those who contribute valuable content and are invited to write for the community blog.       A Community Evolving Together Badges are not only a great way to recognize outstanding contributions of our amazing Community members--they are also a way to continue fostering a collaborative and supportive environment. As you continue to share your knowledge and assist each other these badges serve as a visual representation of your valuable contributions.   Find out more about badges in these Community Support pages in each Community: All About Community Badges - Power Apps CommunityAll About Community Badges - Power Automate CommunityAll About Community Badges - Copilot Studio CommunityAll About Community Badges - Power Pages Community

Tuesday Tips: Powering Up Your Community Profile

TUESDAY TIPS are our way of communicating helpful things we've learned or shared that have helped members of the Community. Whether you're just getting started or you're a seasoned pro, Tuesday Tips will help you know where to go, what to look for, and navigate your way through the ever-growing--and ever-changing--world of the Power Platform Community! We cover basics about the Community, provide a few "insider tips" to make your experience even better, and share best practices gleaned from our most active community members and Super Users.   With so many new Community members joining us each week, we'll also review a few of our "best practices" so you know just "how" the Community works, so make sure to watch the News & Announcements each week for the latest and greatest Tuesday Tips!   This Week's Tip: Power Up Your Profile!  🚀 It's where every Community member gets their start, and it's essential that you keep it updated! Your Community User Profile is how you're able to get messages, post solutions, ask questions--and as you rank up, it's where your badges will appear and how you'll be known when you start blogging in the Community Blog. Your Community User Profile is how the Community knows you--so it's essential that it works the way you need it to! From changing your username to updating contact information, this Knowledge Base Article is your best resource for powering up your profile.     Password Puzzles? No Problem! Find out how to sync your Azure AD password with your community account, ensuring a seamless sign-in. No separate passwords to remember! Job Jumps & Email Swaps Changed jobs? Got a new email? Fear not! You'll find out how to link your shiny new email to your existing community account, keeping your contributions and connections intact. Username Uncertainties Unraveled Picking the perfect username is crucial--and sometimes the original choice you signed up with doesn't fit as well as you may have thought. There's a quick way to request an update here--but remember, your username is your community identity, so choose wisely. "Need Admin Approval" Warning Window? If you see this error message while using the community, don't worry. A simple process will help you get where you need to go. If you still need assistance, find out how to contact your Community Support team. Whatever you're looking for, when it comes to your profile, the Community Account Support Knowledge Base article is your treasure trove of tips as you navigate the nuances of your Community Profile. It’s the ultimate resource for keeping your digital identity in tip-top shape while engaging with the Power Platform Community. So, dive in and power up your profile today!  💪🚀   Community Account Support | Power Apps Community Account Support | Power AutomateCommunity Account Support | Copilot Studio  Community Account Support | Power Pages

Super User of the Month | Chris Piasecki

In our 2nd installment of this new ongoing feature in the Community, we're thrilled to announce that Chris Piasecki is our Super User of the Month for March 2024. If you've been in the Community for a while, we're sure you've seen a comment or marked one of Chris' helpful tips as a solution--he's been a Super User for SEVEN consecutive seasons!   Since authoring his first reply in April 2020 to his most recent achievement organizing the Canadian Power Platform Summit this month, Chris has helped countless Community members with his insights and expertise. In addition to being a Super User, Chris is also a User Group leader, Microsoft MVP, and a featured speaker at the Microsoft Power Platform Conference. His contributions to the new SUIT program, along with his joyous personality and willingness to jump in and help so many members has made Chris a fixture in the Power Platform Community.   When Chris isn't authoring solutions or organizing events, he's actively leading Piasecki Consulting, specializing in solution architecture, integration, DevOps, and more--helping clients discover how to strategize and implement Microsoft's technology platforms. We are grateful for Chris' insightful help in the Community and look forward to even more amazing milestones as he continues to assist so many with his great tips, solutions--always with a smile and a great sense of humor.You can find Chris in the Community and on LinkedIn. Thanks for being such a SUPER user, Chris! 💪 🌠  

Find Out What Makes Super Users So Super

We know many of you visit the Power Platform Communities to ask questions and receive answers. But do you know that many of our best answers and solutions come from Community members who are super active, helping anyone who needs a little help getting unstuck with Business Applications products? We call these dedicated Community members Super Users because they are the real heroes in the Community, willing to jump in whenever they can to help! Maybe you've encountered them yourself and they've solved some of your biggest questions. Have you ever wondered, "Why?"We interviewed several of our Super Users to understand what drives them to help in the Community--and discover the difference it has made in their lives as well! Take a look in our gallery today: What Motivates a Super User? - Power Platform Community (microsoft.com)

March User Group Update: New Groups and Upcoming Events!

  Welcome to this month’s celebration of our Community User Groups and exciting User Group events. We’re thrilled to introduce some brand-new user groups that have recently joined our vibrant community. Plus, we’ve got a lineup of engaging events you won’t want to miss. Let’s jump right in: New User Groups   Sacramento Power Platform GroupANZ Power Platform COE User GroupPower Platform MongoliaPower Platform User Group OmanPower Platform User Group Delta StateMid Michigan Power Platform Upcoming Events  DUG4MFG - Quarterly Meetup - Microsoft Demand PlanningDate: 19 Mar 2024 | 10:30 AM to 12:30 PM Central America Standard TimeDescription: Dive into the world of manufacturing with a focus on Demand Planning. Learn from industry experts and share your insights. Dynamics User Group HoustonDate: 07 Mar 2024 | 11:00 AM to 01:00 PM Central America Standard TimeDescription: Houston, get ready for an immersive session on Dynamics 365 and the Power Platform. Connect with fellow professionals and expand your knowledge. Reading Dynamics 365 & Power Platform User Group (Q1)Date: 05 Mar 2024 | 06:00 PM to 09:00 PM GMT Standard TimeDescription: Join our virtual meetup for insightful discussions, demos, and community updates. Let’s kick off Q1 with a bang! Leaders, Create Your Events!  Leaders of existing User Groups, don’t forget to create your events within the Community platform. By doing so, you’ll enable us to share them in future posts and newsletters. Let’s spread the word and make these gatherings even more impactful! Stay tuned for more updates, inspiring stories, and collaborative opportunities from and for our Community User Groups.   P.S. Have an event or success story to share? Reach out to us – we’d love to feature you!

Users online (5,719)