Friday, 2 April 2021

Power Apps using Excel in One Drive or SharePoint

Overview:  A Canvas Power App can easily connect to Excel held with SharePoint of OneDrive.   It is great for getting values in, or for reading static values from a list.

Limitations

1. You need to create a table in Excel to connect from Power Apps.  The problem is the table cannot contain any formulas.  I wanted to input a value, use Excel formulas to get a calculate risk value.  It can't connect.  The work around is to build the formulas in Power Apps but for my customer complex Excel sheet I don't want to spend weeks re-engineering the logic from code.

Excel: Trying to read field B19 that reference my calculation:


Power Apps: Trying to connect to the Excel table results in the "Excel file containing formula are currently not supported" error message:

2. Size limit, max 2 MB Excel file.  This may be bigger now.

Summary:  You can't work with large data sets and you can read from calculation cells in tables o Excel is fine for inputting into an Excel document that will be used as excel but pretty useless as a data store for Power Apps.  Rather use a SharePoint list but this doesn't help if you actually want to use Excel as it contains complex logic.

0 comments:

Post a comment