Power Apps import Excel to SharePoint list or any data source with Power Automate

Подписаться 163 тыс.

Просмотров 76 тыс.

50% 1

In this video, you will learn how to use Power Apps to import an Excel file to your SharePoint List or other data source. This allows users to update SharePoint with their excel table data. This is done with the use of Power Automate flow actions that Create a file in SharePoint, then get tables, list rows present in table, and finally in an apply to each use Create item in SharePoint. Not terrible hard but has a few gotchas because of dates and because you want the files from XLSX to be dynamic.
How to upload files to SharePoint with Power Apps and Automate • PowerApps upload file ...
0:00 power apps import Excel to SharePoint list or any data source
0:59 Demo of the Excel xlsx file import to SharePoint list with a Power Automate flow
2:18 A demo and explanation of the Power Automate flow
8:25 Create the flow, connect it to Power Apps, and Create file in SharePoint
9:48 Excel Online Business Get Tables
11:17 List Rows Present in a Table and the ISO 8601 date format
12:00 Create Item in SharePoint using the dynamic content we imported
14:03 Updating the flow to make the IDs dynamic
14:46 Using the First expression in Power Automate
www.cleverworkarounds.com/2021...
Power Apps Consulting and training at www.PowerApps911.com
#Powerapps #excel #powerautomate

Наука

Опубликовано:

21 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 130

@jackpost760 Год назад

Shane. Again thank you. I have about 3 ways I’ve done this before and now you’ve shown me a 4th. Love the content

@ShanesCows Год назад

Glad to help Jack. :)

@Dexcelo Год назад

Absolutely a clear and concise explanation of power apps. Your delivery, articulation and presentation was exceptional. Great teacher. Thank you so very much!!! 🙏🙏🙏

@ShanesCows Год назад

Much appreciated!

@juliovilaca2120 Год назад

The tip you provided about Date Format - ISO 8601 was so usefull, you have no Idea how much this helped me! Thank you. Great job

@ShanesCows Год назад

Awesomeness 😎 Glad to help. 😀

@CloudhoundCoUk Год назад

Shane, you appear to have a comedian in the background. Fabulous entertaining and educational video. The best way to learn.

@ShanesCows Год назад

Yeah, Jennifer was running around this place when I was recording, and she couldn't control herself. 🤣

@nicolontaneda3115 Год назад

Shane thank you very much for your video and explanation, I was searching and miraculously found yours! Congratulations. One question, I would like to build a table like the one you show in the video, in which video did you do that? I would really appreciate it a lot. :) Again, awesome job!!!

@AMindGame Год назад

You have the most straighforward and clear videos.

@ShanesCows Год назад

Appreciate that. 🐶

@antoniobranderas Год назад

I’ll definitely give this a try in the very near future. Thank you.

@ShanesCows Год назад

Awesome :)

@cesaritriago7038 Год назад

Great video. To prevent errors with null cells, you can add an expresio. Use coalecence(columnToBring,null) or a conditional if(empty(columnToBring), null, columnToBring). This second fórmula I use It for date in case that in false u need to add another formula as formatdatetime

@ShanesCows Год назад

Nice tip Cesar! Thanks

@jacksorjacksor Год назад

Basically I'm trying to go "for each row, for each cell, if it's empty make it null" but am struggling!

@johnvincentpalisoc7623 11 месяцев назад

Hi Shane, thanks for this. I've mapped out everything 1:1 except I get an error when the user that runs the PowerApp isn't an owner of the flow. Is there any way around this?

@felixverduin569 Год назад

Hi Shane, amazing video! Thank you so much. Is there a way to use Dataverse as primary database to save the Excel files? I noticed there is no (easy) way to choose Dataverse as a location in the Get tables action. Thanks again!

@carolerlston3876 Год назад

Hi Shane, thank you for this video, it is exactly what I need however my problem is that the SharePoint list I need to writ to consists mainly of lookup columns. Is it possible to send the information to a collection that can then be saved to the SharePoint list? Sorry if my question seems silly, I am very inexperienced. Thanks and regards Carol

@oluwatobiyusuf Год назад

Awesome video Shane. This should assist in one of my projects

@ShanesCows Год назад

😎

@dankirchner4460 Год назад

I understood the requirement to have predefined columns so things import well. Is it possible to have say a 20 column sheet but only import 5 out of the 20 columns?

@user-jw2kd8ym6m Год назад

Is there a way to make this dynamic? My flow only works for one specific list and one specific excel sheet. My app needs to have a user create a sharepoint list with the table columns as their columns in the list and be able to insert an excel table right into that list.

@hegui345 11 месяцев назад

Hi Shane. I did the steps listed and my flow failed. It fails on the create file saying it is unable to process template language expressions in action ‘Create_file’ inputs at line ‘0’ and column ‘0’. The template language function “base64toBinary” was invoked with a parameter that is not valid. The value cannot be decoded from base64 representation” if you could help me out here tht would be great!!

@sandroguzman6968 Год назад

Hello Shane. How I can build the table to show the data?? it's not a simple Gallery...

@karthikeyan-ev3eg 10 месяцев назад

@ShanesCows Hi i m getting the error in Get Tables action as ""This workbook couldn't be opened because the file format may not be matching with the file extension.". What to do?

@savithasworld2972 Год назад

Great video. I have one requirement. I need to send Excel data to azure SQL through power apps i e. Have to create a button in power app such that it imports the data from Excel and exports it to azure SQL. Please reply. Thanks

@dimabilan8120 Год назад

Hey, what is the trick with _api/web/Lists/GetByTitle('Documents')/GetItemById(ID)/recycle ? Does this fragment with HTTP request should remove excel file from 'Document' library? for some reason I receive code 400. Maybe i provided wrong link?

@farolconservador3052 10 месяцев назад

There is a possibility to fill a drop down list from an Excel file in Powerapps and send the selection to Sharepoint list?

@shawnsiegrist3632 Месяц назад

Is there a way to skip blank rows in the excel table?

@hatsunemikumusicnightcore5653 8 месяцев назад

I have the error "Acess denied" on step Create File. Can you help me?

@NtetheleloButhelezi-bh8bp 3 месяца назад

can you please give more details on how to Import excel to power apps and display the data on a gallery to perform CRUD functionality

@puterimawarshazleen5941 11 месяцев назад

hye, i already have do all like u show in the video but still my data from excel does not import into the spo list. may i know why ?

@ShanesCows 11 месяцев назад

Sorry, I don't know why. I would recommend breaking the problem down into smaller problems. Try with a really small, generic Excel file and see if you can get that work. Then go from there. Remember to avoid formulas/calculations/references in the Excel. Has to be flat data in a table.

@marielamireya 4 месяца назад

Thanks for your time on this work, please can you specify what syntaxis write in the List rows present in table / Table ... first(....)?

@mohammednizamuddin1348 2 месяца назад

first(outputs('Get_tables')?['body/value'])?['Name'] As shown at 15:49

@elifkaragoz2606 6 дней назад

I want to display my excel data which is stored in sharepoint documents. Can I display the data on Powerapps without using PowerAutomate? I connected the site documents but I couldnt reach my xlsx files inside. Help pls :)

@dennerz Год назад

Thanks again for this Shane! Just one more question, how do you change the loading or at least add a "OnSuccess" image there?

@ShanesCows Год назад

You would need to have some logic that showed a second pop-up when you got to the end of the first one. So show the loading. When you set its variable to false, to make it quit spinning, set another variable to true to show your Success message. :)

@kingsleyudeagha Год назад

Thank You, Shane.

@ShanesCows Год назад

Happy to help 😀

@JonathanRamirez-ch2cg Год назад

Hi, Shane thank you for all your videos, your are making a wonderful job for your community. I have a little question, How could I collect and display all items I upload in the attachment control to a gallery as you have it in minute 1:22? or if you have another video with the explanation?...Thanks a lot... Greetings from Dominican Republic.

@ShanesCows Год назад

Start with this video. :) ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-3QaiM8SeWfM.html

@janicemendoza376 9 месяцев назад

Hi Shane! Absolute gem of a video. I have a question though. I'm under 'Create File' and I don't have the 'Ask in PowerApps' option in File Name? Thank you!

@ShanesCows 8 месяцев назад

They changed the trigger, I put out a new video yesterday to explain it ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-vpjf5mD-COs.html

@erikk587 10 месяцев назад

Hi Shane, learned a lot again! There is one question i have, is there any possibility to add one ID to all excel rows that are imported? The content is part of a single "item".

@ShanesCows 10 месяцев назад

I don't think so. But maybe this video that talks about adding row numbers will help :) ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Uuolue31t5o.html

@ryanisjones Год назад

Hi Shane. Really loving the videos. Is it possible if you could make one for a simple use case? I have a SharePoint list and I'd like to update records that are already in the list and create new items for ones which are not. Your videos show how to add new, but I can't find anything on updating pre-existing items.

@ShanesCows Год назад

Ryan that is possible but you would have to load the Excel data then as you looping through it try to get the item, if it exists then update it, if not found then create new. I haven't had to build it before but it would be trickier than it seems. If you have a primary key that will make it a lot easier.

@ryanisjones Год назад

@@ShanesCows Thanks for the reply, my list does use a primary key. However, I have gone the novel route of connecting the SharePoint list to excel with a refreshale query, using power query within excel to compare it to the new data, then creating 3 tables (also with Power Query) for create, update and delete. I have then created 3 Power Automate routines that simply get the required table. Now the user just needs to overwrite the excel data, refresh the tables and run the automation. It's been a fun evening!

@robfoletta7829 Год назад

Shane This is exactly what I was looking for. I went through this video and the PowerApps upload file to SharePoint document library video and was able to get this to work. I do have a question about the Excel Online (Business) actions Get table and List rows present in a table. when selecting the SharePoint site in the Create file action before the Get table action I was able to select my communications type SharePoint and or my Team type SharePoint site but it looks like I can only select from the team SharePoint sites with the Excel actions. Is there a limit on the type of SharePoint sites we can use with the Excel actions?

@ShanesCows Год назад

Rob trying adding the site with its URL as a custom value and see if works. I haven't tried but quick test should tell you.

@user-ts1wl3sh3d Год назад

Hi. Thanks for the video. I've hit a problem, though. I copied the code from the 'PowerApps upload file to SharePoint document library' video, but when I use it in my test app, EasyUpload.Run asks for a third parameter that you're not asked for - Createfile_FileName. What do I put for that?

@ShanesCows Год назад

Paul if the flow wants 3 things then you asked on Power Apps 3 times. Easiest thing to do is build the flow again and only ask on Power Apps twice.

@karthikaraj9842 Месяц назад

Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The action(s) 'Get tables' referenced by 'inputs' in action 'List_rows_present_in_a_table' are not defined in the template.'.'. I get this error

@707jeevan Год назад

Your videos are very very helpful. I need help with something..I have to display datatable(from sharepoint list) in canvas app ..it's source should be dynamic bcz any number of lists could be created in that sharepoint site.so I have to display only the one that is searched for ..please suggest a way

@ShanesCows Год назад

Galleries or data tables aren’t meant to be dynamic. If the list have the exact same columns you can make the items property an If but I don’t think you will like where this adventure ends. 🤨

@joeldiaz8480 Год назад

Hello, how can I dynamically add combo boxes with powerapps filter, using sharepoint tables and save the data set in another table?

@ShanesCows Год назад

Joel, sorry I don't follow what you are looking for. I have a lot of videos on dropdowns and comboboxes. What part are you have challenges with?

@programsinfo8974 9 месяцев назад

Hi Shane, amazing video! Thank you so much. I need to create a button in my app to upload excel or csv data to my tblData sharepoint list inside the app. Like an Import button then it will open dialog box to choose the csv file then when the user hit Open it will save to the SharePoint list. Is that possible?

@ShanesCows 9 месяцев назад

Yes you can. Look at this video Power Apps import Excel to SharePoint list or any data source with Power Automate ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-MhFNwjaR10A.html and then adapt the trigger to be Power Apps

@eloisamarieiniego1070 Год назад

hi Shane, did you also create a video for the creation of the power apps upload and the preview?

@ShanesCows Год назад

I think you are looking for this video? ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-QcWjAt7QVn4.html

@eloisamarieiniego1070 Год назад

@@ShanesCows thank you so much Shane.. all your videos are really helpful

@GSMonkey Год назад

Thanks, great video :) I got the upload and insert to sharepoint list to work fine, but I am having some "speed" issues i would call them. Seems it takes a very long time to get the items from the excel file into the sharepoint list. Is there any way to speed the process up? I tried 2000+ 5 minutes it only at around 1000 items. It is still running though, and adding more items, so i think eventually everything will be there, just takes a very long time.

@ShanesCows Год назад

Look at the settings for the Apply to each action. You can enable Concurrency and then get it to process more than 1 at a time. 😎

@GSMonkey Год назад

@@ShanesCows Thanks :)

@jeffrosse3910 6 месяцев назад

Hi Shane! Thanks for the video, simple, straight to the point! Like always. I am however having trouble with my data. I can only upload 256 items from my Excel file. Do you know if this is the limit, or if I can enhance that? Thank you so much!!

@jeffrosse3910 5 месяцев назад

Found an alternative way, using the "import to a sharepoint list" option in Excel ;) Thanks anyway!

@pandaracer 4 месяца назад

@@jeffrosse3910 I came to the comments with that same problem, thanks for posting your solution!

@norvintabale9323 8 месяцев назад

Hello. A question here. Can we do it like instead of saving directly to list, can we insert a submit command before saving it to list? thank you.

@ShanesCows 8 месяцев назад

I don't exactly understand your question but I think the answer is yes. You could update the flow to have other actions that run before you save the data.

@LeeJordison Год назад

Dear Shane, that was an amazing video! thanks on the line Json(Image1_4.image) what's the image 1_4?

@ShanesCows Год назад

Image1_4 is the name of my image control. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-3QaiM8SeWfM.html That video gets into the details if you need them for uploads.

@hailm03 3 месяца назад

Thank!!!

@ShanesCows 3 месяца назад

You're welcome!

@hamnamh7818 11 месяцев назад

Hi Shane, i am having an excel file which is having 84k+ records, while using the 'List row present in table' it is failing. and getting an error 'BadRequest. The action 'List_rows_present_in_a_table' has an aggregated page results size of more than '210429765' bytes. This exceeded the maximum size '209715200' bytes allowed.' any idea of any alternate soln to avoid this? i am just using 2 column from this step to filter in next step ie, list rows.Since the column header is having space, eg Transaction Number, this is not accepting in select query of list row present in table but when i change the column header to TransactionNumber and the same in List row present in table, it is accepting. can you help out?

@ShanesCows 11 месяцев назад

I haven’t ran into that issue before so I am not sure. Sorry.

@GohilSnapshot 8 месяцев назад

Hi Shane! Can we add the excel file of any name? I'm trying Its showing error to it.

@ShanesCows 8 месяцев назад

Yes, adapt what I taught in this video and you have what you want. :) ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-QcWjAt7QVn4.html

@LeoNinh Год назад

Nice video but I still confuse in PowerApps. How to add AttachmentControl? How you add the button to import file? Thank you!

@ShanesCows Год назад

Hi Leo - This video will walk you through that part. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-3QaiM8SeWfM.html

@craig.galbraith Год назад

Great video! I've learned so much from this and its associated videos! I have an issue where the column names in the workbooks to be imported contain special characters (namely "¿ ") so any data for those columns appears blank in my SharePoint list. My flow does not produce any errors, it simply ignores those columns. Data from columns whose names contain no special characters work just fine!! Thanks!!

@ShanesCows Год назад

Interesting. Sorry I haven't ran into that before so not sure.

@craig.galbraith Год назад

@@ShanesCows I seem to always find all of the edge cases :D Thanks for checking in!

@craig.galbraith Год назад

The problem turned out to be slashes "/" in the header names and I was inspired to solve it by watching this video ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-S5eqq3KjaBI.html I basically had to manually edit the expressions for each of the items('Apply_to_each')? for the Create Item step where the header in the .xlsx file contained a "/". For some reason, Power Automate read in "/" as "~1".

@vjthanasekaran6752 Год назад

Hi Shane Thanks for the video. File gets created, however flow is stuck with get tables section for long time. I’m using ID (not item id)from the file created as you suggested. Any idea?

@ShanesCows Год назад

Did you try it by manually upload the file and specifying it. Maybe something is wrong with the file? NOt sure.

@ChrisLee-ov2sn Год назад

Hey Shane.. Not sure if you are tracking this, but the US Government has done a bit of investing in M365. Government employees need to start learning SPO and M365. Your videos have been a lifesaver for me and my office as solutions "guys." I push all your videos when people ask me how they can use PowerApps and Automate to improve their fighting positions. We are struggling to find M365 trainers in our area to help us do some more in-depth training. Can you help us find some "in-person" training in NC? We are specifically focusing on PL900+ material.

@ShanesCows Год назад

Hi Chris - We teach classes live both online and inperson. So depending on what you are looking to learn we could potential help. Feel free to email me shane at powerapps911.com and I can help you get more info. Thanks

@malcolmchetty8398 9 месяцев назад

Awesome Video Shane! is the download locked behind a paid subscription?

@ShanesCows 9 месяцев назад

Yes, sorry. Https://training.powerapps911.com

@cdfoodiereviewchannel8936 Год назад

Do you have a video on how you created that power app ?

@ShanesCows Год назад

I don't but you can download the app if you subscribe to our RU-vid Library. training.powerapps911.com/courses/video-library

@MP-py7pt Год назад

Hi thanks for this however I can't find a link to the video for the first step in all of this. Please could you kindly share it again? Thanks. p.s. great videos, absolute life savers.

@ShanesCows Год назад

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-3QaiM8SeWfM.html There you go. :)

@MP-py7pt Год назад

@@ShanesCows legend, thank you.

@MP-py7pt Год назад

p.s would this be possible with SQL as a datasource? Or only Sharepoint List?

@ShanesCows Год назад

YOu could change the flow to write to SQL

@matthewmcguire728 Год назад

is there a limit to the number of items this is able to upload?

@ShanesCows Год назад

Hmmm. Fair question. I think you can change the limit on the Get List Items to a pretty high max.

@axessbv Год назад

@@ShanesCows I did it and put 1000 items but it is only uploading 256 items out of the table that has around 700. Any ideas on why this may be happening? Thanks for all your videos!!

@ShanesCows Год назад

@@axessbv If you click on the Excel Get Items connection under settings there is Pagination you need to enable. :)

@axessbv Год назад

@@ShanesCows Thanks! That solved it.

@user-ku6ik6uj6e Год назад

if the data is not in a table? How to proceed?

@ShanesCows Год назад

Has to be in a table or you cannot use the Excel data. Sorry

@maheshgupte1495 4 месяца назад

Hi Shane, Thank you for sharing this tutorial I hope you are doing well need one help in one of my power App Project. where I want to provide a user to 1- Download a Excel template 2 Fill the template 3 User should be able upload the template using power App. 4 and patch the records to database MS lists I found 2 Tutorial on RU-vid but it confusing to me please check if you can help me with this requirement it will be great help. I am new to PowerApp world please if you can create a tutorial that will be great help for new learners.

@ginocuccia6706 Год назад

ha ha, Just did this exact thing last week!

@ShanesCows Год назад

Nice Gino. 😎

@Mahi-qb3nq Год назад

With the help of this flow can i upload 2000+ records to SharePoint

@ShanesCows Год назад

I don’t see why not.

@geralddahl9159 Год назад

Initials of mystery women eq AB?

@ShanesCows Год назад

Nope. But good guess.

@antoniobranderas Год назад

eq J.H.

@rjjalosjos9454 Год назад

Could you please put the tutorial for the power apps import excel?

@ShanesCows Год назад

Hi RJ. Sorry, not sure what you mean.

@rjjalosjos9454 Год назад

@@ShanesCows I'm just referring to the front-end design of the power apps itself. Lowkey curious how you were able to add a file

@alessiovacca2600 Год назад

@@ShanesCows yeah, can you show us how to set up the app in order to connect the uploaded file to the flow

@ScottOtt Год назад

I've built this, and it runs, but not when I use the dynamic data (Sharepoint Id in 'Get Tables' and 'List Rows Present in Table' steps). It stalls (and retries) at the 'Get Tables' step of the flow, and throws a 'BadGateway' error. I thought it might be because the Sharepoint List is in a different SP Site from the SP Documents Library where I'm uploading my Excel table, but I've tried it both ways. I'm sure I'm missing something obvious. Lil' help? [BTW, Shane continues to produce excellent content that has helped me create nearly 10 apps, several of which are in daily use by hundreds of staff at my W-2 gig. Thank you.]

@ScottOtt Год назад

After two weeks of fruitless trying, I changed direction. I have created a Power Automate flow that listens for an email with a keyword in the subject line, grabs the Excel attachment, gets the table from the spreadsheet, lists the table rows, matches them with my SharePoint list columns, then imports them into the list. Then they're displayed in a PowerApps gallery. It works beautifully, and should actually be easier for the end users who are already accustomed to emailing spreadsheets for this purpose. If like to see how I did that, just reach out to me here.

@ibrahim98ahmed 10 месяцев назад

8:25

@ybunsee Год назад

wutwuut!

@ShanesCows Год назад

🤘

@serieslimited9064 Год назад

Great Walkthrough. I tried everything you've said and as shown on your screen. I get an error on the 5th line " ImportandUpload.Run(Last(AttachmentControl.Attachments).Name, varBase640nly); "Comment saying : Invalid number of arguments: Received 2, Expected 1. kindly assist, please.

@ShanesCows Год назад

That means you only did Ask In PowerApps 1 time inside of your flow.

@GSMonkey Год назад

Another question, i tried replacing the Sharepoint list with Dataverse, and use "Add a new row" instead of "create item". It seems like the logical way to do it, but unfortunately the flow fails. Do you know how to make this work?

@GSMonkey Год назад

Actually after redoing it all, it works just fine. But seems it fails if i am too quick to upload a second file, so have to test that a bit more :) So following your tutorial, and just doing "add a new row" in Dataverse works just fine, just needs to be tuned a bit more.

@xBiiaBianca Год назад

@@GSMonkey Hi, can you please tell me if you just replaced "create file" or did you add other steps as well? thanks

@GSMonkey Год назад

@@xBiiaBianca Hi, i actually moved away from Dataverse, i found out that most people in the company where i work, do not have a license that allows them to do all that is needed in dataverse, so unfortunately i didnt find the solution, as there was no reason to.