We are new to PowerApps and have tried a variety of methods for saving data from excel spreadsheets, SharePoint Lists to Collections. They all seem to have thier nuances, but I would love feedback on what is the least hassel from an administration perspective. Our basic need is to be able to input data and then report on said data. I really love being able to save to SharePoint lists, because of how easy it is to report using PowerBI, but the limitation of not being able to use the "Everyone" group to shareout the PowerApp is a nonstarter. When we played with Excel, we ran into issues because someone had the file open when people were trying to use the PowerApps App to write to it. Thanks for your feedback!
Definitely curious to see what other people have to say on this as well. My experience so far...
Excel: I tried using it as my main data source for my first app and ran into some very frustrating challenges around getting PowerApps and Excel to 'see' the same data type and 'write' the same data type. Excel would expect one type and PowerApps would be using a different type. Now that I have more experience I could probably fix this but I found less converting of data with my SharePoint list. I also don't like finding global place to store my excel files for scalability. I find managing the permissions to my backend files/data easier in SharePoint personally.
Where I do find Excel helpful is if I have static data that I don't expect to change (or change frequently anyway) and don't want to set up a SharePoint list to store it. For example, we have a 3 people that 'own' our order escalations. Instead of building a SharePoint list to house minimal data points, I stored it in an Excel table and pulled it in.
Collections: I'm still figuring out the best way to utilize collections for actually storing data. Initially I used collections to pull in existing data from SharePoint that would auto-populate my form (alignment) or populate the available choices in my fields. That works quite well.
I also decided I wanted to pull in my data in as a collection so as not to ping SharePoint everytime a screen loads. This is where I'm struggling. It's working well to view. I'm struggling getting the collection and SharePoint to both recognize edits made in the app. SharePoint takes the changes just fine but the associated collection seems to be quite behind in refreshing and accepting those edits. I'm actually trying to work through this today.
SharePoint: So far, this is my preference for housing the actual records for the app. Both for reporting (as you mentioned) and ease of process for building out the app (for the reasons you and I both have mentioned).
I haven't had an app that I want to share with the "Everyone" group so I haven't run into this. What is the issue? Is it a permission thing with SharePoint?
I do wonder if long term, if I'd be better off with a SQL type database storage but our company just isn't there yet.