Can a PowerApp connect to an Excel-file living in SharePoint and refresh? If not can I make a flow to do it?

[blinabj]

Can a PowerApp connect to an Excel-file living in SharePoint and refresh?

I have read other posts asking about the same thing from 2018 and 2020, saying it is not possible.

But, I am really hoping that it is possible now?

If not, is it possible to create a Power Automate flow from that gets the Excel file from SharePoint and adds it into PowerApps?

Appreciate the help 🙂

[SebS]

@blinabj 

There is an Excel connector, and You can use Excel as DataSource but I do not recommend it to do that upload excel file make sure all data is in a Table and in PowerApps use Excel Online connector than point it to the file in SharePoint.

Hope this help

[WarrenBelz · Accepted solution]

@blinabj ,

Just adding to @SebS wise comments - if you have access to SharePoint, I recommend you forget about Excel as a data source. Also do not create the SharePoint list with the Excel option - your underlying field names will not be what you expect.
If you create a blank SP list (with the field names you need), you can import Excel with Power automate as below - just format your Excel data as a Table, put the Excel file in OneDrive and bring it all in.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[poweractivate]

@blinabj 

It's "possible" but I don't recommend it as @WarrenBelz and @SebS says.

If you really want to do it you can try it. The functionality is supported.

There are some practical concerns because you just generally might be better off using SharePoint.

Here's an example. You may think if Excel is supported you can throw in anything random you want like all kinds of calculated formulas in Excel, and so forth. Putting such calculations in Excel will not do any good because those cells with Excel formulas inside of them will not be calculated on Power Apps side and are not supported by Power Apps. I don't think something like this is planned to be supported, ever, as well.

Here's a more practical example. You may not be able to use too many delegable operations in Excel data source compared with SharePoint List. That means you are limited by the data row limit which by default is 500 but can be raised to 2000 but not higher than that.

There are also many other even more pressing practical concerns, in my opinion, with using Excel as a data source, even for a very small project. You may go ahead and ignore them if you want and just try it because Excel is supported as a data source. I just do not recommend it either and would much rather use SQL Server, Dataverse, etc. and of course I would much, much rather go with a SharePoint List than Excel, there is so much you can do with just SharePoint Lists and Power Apps.

[WarrenBelz]

@poweractivate ,

Thank you for the further detail - Delegation (or lack of compared to SharePoint) is a big issue with Excel. Also, if you want to pay for Premium connectors for Dataverse/SQL etc, then they are certainly better data sources for Power Apps, but bear in mind (and this is highly relevant with a lot of work I do), you lose the SharePoint interface where users can do lot of their own reporting and data extraction/cleanup.

[SebS]

@blinabj 

Ok, I will explain why I will never use it as my DataSource, even for micro projects. I hope this helps others to make better decisions when planning new apps.

1. Way how Power Apps interact with the file, If Excel File is open, it will go into Lock mode Power Apps will not be able to create or Update any records. So if a group of people are working on it for some reason and forget to close it, **bleep**, what a pain.

Not only that, if you would like to manipulate data by using Power Automate, sadly Power Automate, after Modification putting the Excel file in Lock mode for 6 min means Power Apps will again connect to it and can't do any operations.

2. Power Apps in a moment when You first time connecting to the file creating a column called _PowerAppsID_ and creating a really long ID for each record in the table. Now it's very buggy, and if you play too much with the table, the only way to fix it is to create a new File, create a new table and re-connect it to Power Apps again.

3.Power Apps on the first connection decide what the type of column in your Table will be, and my gosh, this can surprise you a lot when most of Number types become Text and Date become something else and then even if you change them, power apps will ignore the changes till you run the process of reconnecting of the connector again keep in mind that will not give you 100% result as well 🙂

4. DateTime iso8601 Power Automate hates it so do PowerApps in some scenarios, as both use UTC

5. Delegations nearly not existing

Sharepoint is easy to learn and has so much to give for a free Data Source. The time you spend on frustrating and debugging Excel connectors will be twice or more longer than just checking some YouTube video on how to set up SharePoint sites and Lists to drive Power Apps from them.

[poweractivate]

@WarrenBelz Yes you lose the SharePoint interface when you don't use SharePoint. It's easier to use the SharePoint interface than to use Excel Online in my opinion. Also it's possible to model the data with a lot of flexibility with SharePoint and with some tricks it's possible to get so close enough to SQL / Dataverse that any disadvantages may not even matter anymore as they can be even almost removed, if not removed even entirely in most cases. It is not necessary at all to pay for Dataverse with the proper planning, I agree with you @WarrenBelz 

[blinabj]

Hi @WarrenBelz ,

Thank you so much for this explanation, it is very helpful! If I do this, will I have any issues with data access when the app has several users? Will all the users need access to the OneDrive?

Really appreciate the help!😊

[WarrenBelz]

@blinabj ,

Yes, the users will need to have access to the file in OneDrive.

[blinabj]

Great, thank you so much for the help!