ExcelOnline (Business) Connector

[PowerAppsJunkie]

So I'd like to get a definitive answer from hopefully someone inside the PowerApps team because I'm just scratching my head on how this connector makes no sense and why is it here.  I've searched and found no "real" answer.

This connector and everything it has with it would have you think you can use it for accessing a document library in SharePoint Online that contains an Excel file and that you can use this file as a datasource.  However when you start to use the connector you see there are things it says such as file drop downs (that don't exist) or it tells you to browse (which also doesn't look like it exists).

Posts have gone back 6 months or so on this but still the connector shows up in PowerApps and gives the "feeling" that this is the way you use it.

So does it work or not? Has anyone figured out the syntax? 

My need and goal is to create a powerapp that can use an excel file located in a SharePoint document library (NOT ONEDRIVE) and then use that to populate a list.  It only has to be a read-only from the file and will have no need to write anything back to the excel file.

I've done this already with flow etc...but that's not what I want to do when clearly there is a native connector in PowerApps that should allow this functionality based on descriptions you see on the connector and descriptions of it's usage.

[Pstork1]

Based on what I read in the documentation located here:

https://docs.microsoft.com/en-us/connectors/excelonlinebusiness/

The Excel Online (Business Connector) only works with Logic Apps and Power Automate. Power Apps is not listed. In fact, when I try to create a new connection in Power Apps for Excel my only option is to do a static import.

I know this used to work in Power Apps, but the capability doesn't seem to be available anymore.  Its also not available using the Excel OneDrive connector either.  That documentation also says its only available for Power Automate.

[PowerAppsJunkie]

That's very odd.  I have the ability to add the connector and show it's syntax.

Hopefully you can understand my confusion on this because searching you can see 1 of 100 answers with very little consistency about it.

[Pstork1]

I checked and that's actually a new connector that is currently being rolled out.  I think there may still be some issues with it and I'm not sure it will support SharePoint despite what the documentation currently says.

[Merley72]

Hi PowerAppsJunkie,

Did you get any further around this?  I have a very similar requirement as yourself.  I'm looking to use stored data from a spreadsheet in a Power App as a source for dropdowns, choices and other stuff - basically to use it as a set of parameters.  I also want to use it a repository for 2000+ names for Supplier and Factories which I want to search on/filter and then display as sub-sets in drop-downs etc.

I'd normally want to use SharePoint as that source however in this situation, the interface between Power Apps and SharePoint list/libraries and the 500 item limit when you do a 'Search' for data was something I hoped to avoid.  Pulling the data from an interactive spreadsheet seemed a good way around this, as there are few constraints.  

If you've got anything to advise / report back I'd very much appreciate it.

Regards Alan C.

[Pstork1]

In the scenario you describe, where the data is ReadOnly, you can just import the spreadsheet into your Power App. If you try to use the regular connector you will have a problem anyway, because Excel doesn't support any delegation and therefore can't handle a table as large as you describe.

[Merley72]

HI Pstork1

The use of the import a spreadsheet option is one I've used where the data is static and read-only however in the instance I'm wanting to address the Supplier names are regularly updated from the Finance software and therefore need to be updated daily.  Some new ones appears and other are removed etc, so in those circumstance the one-time import isn't for me.

I've used SharePoint very effectively in the past but having a 'Search' criteria running against 2000+ names isn't a goer with the 500 item limit due to delegable constraints.  'Filter' instead of 'Search' is OK to work with however the next problem comes because I can use the 'StartsWith' (EndsWith) option but if I were to use 'in' then there is the 500 item limit again.  And it is the 'in' option where the formula compares a value in a text field to the overall set of data (I'm using it to work with Supplier names etc), that I want to work. 

So if I want to identify all of the Suppliers with the word 'tool' in their title, I'd love to have an option to 'Filter' which has a 'contains' parameter (which works just like the 'in') which works across all items in the list and not against the first 500 of them.

As I'm a relatively new PowerApps developer, I don't know if there are other methods I could employ or simple storage of the Supplier name records which would allow for this kind of filtering/searching.

All ideas and contributions will be gratefully received.

Regards, Alan C.

[Pstork1]

As I mentioned, Excel is even more limited than SharePoint when it comes to working with large data sources because it doesn't support any delegation.  You can increase the 500 item limit to a max of 2,000, but if your list is over 2,000 that won't really help.  You basically have two options for workarounds.  One is to use SQL or CDS as the data source since they both have more robust delegation support, including 'in'. The second option is to call a flow to access the records.  Flow with SharePoint can retrieve records using pagination and is therefore not constrained by delegation.  The major problem with both of these approaches is that they require premium licensing.

[mkujalowicz]

Hello,

I'v been facing some serious issues with PowerApps since this Monday. The app becomes unresponsive as soon as I open/edit an app, the one that uses One Drive for Business connector/Excel spreadsheet. I have searched everywhere to find more information about it, unfortunately, I still don't know what makes the app freeze and kill my browser.

I have learnt that there was a new PowerApps version rolled out somewhere between Fri, Nov-20 and Mon,Nov-23 since I have noticed that the version has changed to 3.20111.52.

Also, you have mentioned that Excel OneDrive connector doesn't work anymore. is that correct?

Any hint would be very appreciated. 

Thanks a lot!