This argument was also true when using Excel files as a data source in Power Apps, because the following important functionalities were not supported in this case :
- Simultaneous editing by multiple users - Any operation will be refused when other user open file
- Delegable data source - App cannot access record exceeding 500 (default delegation limit)
Recently, Power Apps team announced improvements for Excel files as a data source in following post.
In conclusion, this improvement of Excel Online connector allows that Excel files can be a strong candidate as a data source for Power Apps.
In order to take advantage of the update, you need to add a data source in the Data pane in Power Apps.
Note : If you start from data with "Excel Online", the app will connect Excel table through OneDrive connector, not Excel Online connector.
Key update #1 : Simultaneous editing by multiple users
With (old) OneDrive connector, it is not available to create/update data from Power Apps while opening file, but Excel connector allows you editing data with multiple users simultaneously!!
In following tweet, 2 users create records from Power Apps and another 1 user opening Excel file on web browser.
Excel table datasourceでPower Appsから2アカウントで更新操作 pic.twitter.com/RSJe4XbSba— Hiro (@mofumofu_dance) April 20, 2021
This resolved one of the problems, the file will be no longer locked by opening file :)
Key update #2 : Delegation support
As is well known in the context of Power Automate, the Excel online connector allows you to set up a Filter and OrderBy query for data retrieval.
If this is also available in Power Apps, it means that delegation will be partially supported - related to Filter(....) and Sort(...) functions.
With new Excel Online connector, Filter operation will work well, and can retrieve data exceeding delegation limit 500!!!
Note : Same as Power Automate action, Filter(...) function with single condition using
EndsWith and Search(...) function are supported.
In Power Apps, OrderBy query is written as
SortByColumns(...). For both functions, only single expression is supported in Excel Online connector.
If you set multiple sort condition by
SortByColumns, the following error will be displayed and no data will be shown in Gallery control.
Bonus : Excel Table containing formula is supported
Now we have BIG bonus by Excel online connector.
As it has been already pointed out in following blog post by Eickhel, Excel online connector in Power Automate can get table data containing calculated columns.
In Power Apps, it is now possible to retrieve data from tables containing calculated columns as well!
Maker can now leverage Excel functions for data operation in Power Apps without use of flow or Graph API!!
This is not yet documented though, you can try it :)
There has been long discussion about using Excel file as data source in Power Apps. With help of new Excel Online connector, it allows us...
- Document library Excel file support
- Simultaneous editing by multiple user
- Evaluation of cells containing formulas
- Simple delegation support
Of course, it depends on the properties of your business, but I think it's time to revisit about using Excel files as a data source with help of Excel Online connector.
*IMO, it can be good candidate :)