cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JaguarMonkey
Frequent Visitor

Building an app using excel stored in SharePoint

Good day

I am trying to build an app that to aggregate customer service requests.  The requests arrive via an organizational MS Form.  The data land in an Excel Sheet that is stored in a SharePoint site (just because that is the way forms are working).  I would like to use that Excel workbook to feed the app so team members can use the app to track requests.

How can I connect to that Excel Book.  Right now, as far as I can tell, I can only use Excel as a data source if it is stored in OneDrive.

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
timl
Super User
Super User

@JaguarMonkey 

To connect to an Excel spreadsheet that's stored in a SharePoint site, you can use the Excel Online Business connector (rather than the OneDrive/Excel connector).

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

 

There are limitations to this connector - such as the ability to only filter by one column. Therefore, copying the records to a SharePoint list like @RandyHayes  suggests would create a more queriable/referenceable data source.

View solution in original post

7 REPLIES 7
RandyHayes
Super User
Super User

@JaguarMonkey 

You are correct that you would need your Excel to be a datasource in OneDrive.  So, with that in mind, having a Excel file in another location would not be supported.

I would suggest that you establish a PowerAutomate Flow to digest the Excel file and any additions to create records in a SharePoint list.  Then you will have a referenceable datasource for your app.

 

I hope this is helpful for you. 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
timl
Super User
Super User

@JaguarMonkey 

To connect to an Excel spreadsheet that's stored in a SharePoint site, you can use the Excel Online Business connector (rather than the OneDrive/Excel connector).

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

 

There are limitations to this connector - such as the ability to only filter by one column. Therefore, copying the records to a SharePoint list like @RandyHayes  suggests would create a more queriable/referenceable data source.

View solution in original post

JaguarMonkey
Frequent Visitor

@RandyHayes This works.  Thanks.  Small issue.  I have date fields and while it knows they are there, I will not display them in galleries or forms.  The sheet defines them as short date, 

In the gallery or in a display form, using the ThisItem.StartDate , for example, to define a text property, it returns a 

'ThisItem.StartDate' - This formula uses scope which is not support for evaluation.  | Datatype: DateTime

RandyHayes
Super User
Super User

@JaguarMonkey 

The message "This formula uses scope..." is a normal message.  This is NOT an error, it is the formula editor telling you that it can't determine what ThisItem is - only because this item references a row in the gallery...how would the editor know which row it should evaluate?  Anyway, it is normal.

 

Now...I'm a little confused on what method you are working with.  You chose the response from @timl as the solution, so is that what you are trying to work with or have you used the Excel file in your OneDrive as a datasource?  

Either way, PowerApps seems to always have issues with understanding dates from Excel. What is your region?  If it is not a US based date "mm/dd/yyyy", then there seems to be some problems getting PowerApps to recognize the column correctly.  

I don't work with the Excel file much and I am US region, so never have much problem with dates in it when I do.  But, I do know that you really need to make sure that the columns are formatted properly for PowerApps to recognize it.

Perhaps @timl has some more insight on this as he is not US region and perhaps has run into the cause and resolution for dates.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

@RandyHayes Thanks for the follow up.  I chose your answer, so I am using the excel online business connector.

I am in the US.  The dates created by the MS Form are formatted as Date in mm/dd/yyyy .  I have been able to do a workaround by creating a formula that looks at Date cell content and copies it to text in another cell, but that is clunky for shure.

 

I started from blank.  Created a screen, and inserted a Details Form.  I connected it using the Excel Online for Business connector.  I selected the fields to display. It does identity the field as a date one, as it created the data card, but then the Data Card Value fails to display

JaguarMonkey_0-1629915539699.png

 

RandyHayes
Super User
Super User

@JaguarMonkey 

Yeah, the Excel date bit is clunky in general.  For the few times that I use Excel, I've never experienced an issue with the dates, but I know a lot of people run into this and it is usually out of the US region.  So...it was a shot.

 

However, you mention the Excel Online for Business Connector...I can't tell you much on that one - it is what @timl had mentioned, my suggestion was the OneDrive for Business Connector (although I did not specifically state that connector).  That is the one that I've always used for a PowerApp and, again, never had issues with dates.  So, perhaps give that a try instead.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
timl
Super User
Super User

@JaguarMonkey 

I can recreate this issue and I understand what you're saying. Unfortunately, the Excel Online Business connector doesn't provide good support for data types other than text, which is the reason for the error that you see. I would like Microsoft to improve this and  for some background reading, I posted a thread in the ideas forum about improving numeric support with this connector here:

 

https://powerusers.microsoft.com/t5/Power-Apps-Ideas/Excel-Online-Business-Connector-Add-support-for...


If you need to work with date fields - using the OneDrive for Business Connector as Randy suggests will provide a workable solution. The caveat I would make about this connector however, is that you can't access spreadsheet rows above row 2,000. Depending on the number of customer service requests that you need to aggregate, this may or may not cause you a problem. In comparison, the Excel Online Business connector has a limit of around 64k records, and it does work against Excel files that are actually stored in a SharePoint site/document library (which was the crux of the intial question).

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

secondImage

Power Apps Community Call

Please join us on Wednesday, October 20th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (1,276)