Bulk Add to Excel or SharePoint using Power Apps

[@keimomomon]

This is what I need for my current project with the procurement team! Thank you!

[@andrewhess123]

Awesome! Glad to help Kristina!

[@mohanadmohamed5501]

I cannot thank you enough. This is amazing work. Despite the complexities that I imagined in this scenario, you made it simpler than it is. 🌹🙏

[@andrewhess123]

Thank you for watching MOHANAD. Wish you the best! Feel free to reach out if you have more questions.

[@MrAbueslam]

Amazing work

[@andrewhess123]

Thank you Awad!

[@MrAbueslam]

@@andrewhess123 I am working on building shop invoice that request items from selected vendors and I need to generate pdf through automate . I will try to use your approach and see how it works .

[@jray1429]

Thanks, I really like this info

[@andrewhess123]

Thanks for your continual support J Ray

[@rodelski5514]

Great video and tutorial!!! Anyway that when you export excel it also send it as an email attachment to the user? What would be your approach? Thanks

[@andrewhess123]

You could try to build a new power automate flow for this or use the existing, but I think for simplistic reasons I think I would create a separate Power Automate Flow for this, and the trigger would be when a new item is created or modified, depending on the requirements, and when that trigger sets off you could email whomever you would like. You could have a field/column to email who you want or put in a hard coded email address to email to.

[@TeruMaldi]

Absolutely one of the best places to get tricks for PowerApps, thank you for doing this! Question: Is there a possibility to let people bulk paste items from an excel sheet to PowerApps? Kind of like an Inline editing form that writes to SP.

[@andrewhess123]

That's a really good question Armin.. I have to think about that one. You could do it in SharePoint in Quick Edit, if you matched up everything perfectly, it's a little tricky sometimes. You could maybe have a way to add a new connection to a Excel Sheet that auto adds the data to a collection and then puts it in Power Apps when you add the connection. This sounds tricky but I do think possible! Or even Power Automate is a choice. But copying and pasting, i'll have to think about that one! Thank you Armin!

[@adilaishak1476]

Amazing video! Just wondering what would be the workaround if I want to create a new Excel file every time the user has hit the Write to Excel button? I'm planning to have the date exported to Excel file instead and when a new Excel file is added to Sharepoint file, approval workflow will be triggered.

[@andrewhess123]

Hi Adila, so the trick to creating a new Excel file is to have an Excel Template stored somewhere. Most likely in your Site Assets of your SharePoint site. Then push the data as you would like. I would recommend naming your file with the date and time that way you never have a naming issue with your Excel file.

[@MrAbueslam]

Can show us how to formulate an invoice out of collection or records from sp list and may be present invoice in form of pdf or word doc by automate. Thanks

[@andrewhess123]

Hi Awad, it took me some time but I put together a video on how to populate a word doc by power apps + automate. Here: kzbin.info/www/bejne/eV6nhmSMjr6UlZI&t

[@andrewhess123]

Check out the description of the video for the 2 formulas to write the For All Patch Statements

[@HoneyBadgerJAB]

Awesome video, thank you! Does patching the collection like this count as one api call for say 4 records, or is it 4 api calls because of the ForAll looping? I’m still fuzzy on APIs and it’s something I need to know because of our power platform 2000 API calls per user per day plan we are on. Hope I’m making sense but thank you again!!

[@andrewhess123]

I'm just guessing but I think, everytime a patch statement runs its an api call. But what you could do is put everything in a collection first before patching, and then patch the collection when you are done.

[@vincemosquera1698]

I followed your coding but still have an error. Error is in the stage of submitting to SharePoint. Appreciate it men if you can help me on this.

[@andrewhess123]

Hi Vincent, what is the error?

[@mudskipperdrones]

'Bulk' wise I still presume this doesn't get around SP's limited rows. Whats the maximum you can achieve this way? 256 items?

[@andrewhess123]

SharePoints Limited Rows? In what way. This method uses a For All Statement so each row is added individually over time in a loop. SharePoint 2013 had a 5000 limit per view, you can exceed that with Modern SharePoint. There are limits to viewing datasources from SharePoint in PowerApps, but also ways around those delegation warnings. Excel has a fetch limit, but we are just writing here. We are not getting rows, only writing from manual inputs in the video. Still those limits can be overcome too: powerusers.microsoft.com/t5/Building-Flows/256-rows-limit/td-p/268351 powerusers.microsoft.com/t5/Building-Power-Apps/256-rows-limit-getting-rows-from-excel/td-p/24092/page/5

[@mudskipperdrones]

@@andrewhess123 Sorry didn't phrase it very well. Using for all still won't do large amount so I wanted to know using your method in to SP what was the maximum you had achieved? Collections are limited to 2000 rows so have you found a better way? I tried using an excel table imported in to the app space then using forall which didn't do much other than put a load of blanks in to the list. (30k rows excel table with 960,000 items) If you collect from the table your limited to 2000 again. Any thoughts?

[@andrewhess123]

@@mudskipperdrones Matt Devaney does a great job explaining different ways to collect over 2000, who I also follow on Twitter, he has a nice blog also: www.matthewdevaney.com/create-power-apps-collections-over-2000-rows-with-these-4-tricks/

[@andrewhess123]

Usually if I want to bulk move data not create new line items, I use ShareGate. Which I have been using for about 5-6 years now for movement and migration of data to SharePoint Online.

[@mudskipperdrones]

@@andrewhess123 Cheers, in the end I've had to use flow to get it going. Max 5k lines at a time could possibly push to 10k but 5k per time isn't so bad with pagination on. Thanks for your replies, happy new year too!