I created an App that uses Excel Online file on OneDrive as a DB. The purpose of this App is to take user inputs and update the records on Excel file.
In order to make this happen, I shared both Excel file and OneDrive with the users. The problem I realized is that everyone who uses the App has become Administrators of the Excel File, giving them capability to see the Excel file directly.
I would like to make users just to update their records, view the history without giving them any access to the original Excel file. I've searched on Google and explored Flow and OneDrive, but could not find anything.
Is it possible to do so?
Your requirement is not easy to implement OOB. But you can do it using some trick - pass information that need to be read/updated in excled to MS Flow and from that Flow pass data to another flow over HTTP action - thanks to this HTTP integration the MS Fecond flow can be 100% impersonated! So with that you can have control over what EVERY user sees as well as control their access to the file (even deny access at all).
I've done similar config for one of my app and it works like a charm
An answer resolved your issue? Mark it as a solution
Thanks for the input. This can be so useful in my project.
I have couple of questions if you don't mind me asking:
1. It seems that using HTTP action, I can control which user can access the DB - correct?
2. So does that mean I can 'unshare' OneDrive & DB with the users, because they can only access it using generated code (CUID in your case)?
3. Also by using this, each user will be able to ONLY see their past submission history, right?
4. In your case, I noticed that some AuthorCUID are duplicated, but SumissionCUID are different every time.
Did you give CUID per user and then make SubmissionCUID generate differently every time? How do they work?
A1. Yes but remember that in HTTP connector you don't have user context. So passing some user context info (mail, group, name) is required. However think if that couldn't be done on the PowerApps side since PowerApps are faster than Flow(!).
2. Exactly. Theoretically none of my users need access to the data - everything can be handled by http flow (but for simplicity of this app I use SP connector only to read the data).
3. Yes. In my app I did it by requiring user to provision GUID that is used to filter data (which correlates only with specific user submissions)
4. Submissions has unique GUID every time. Users has new GUID only when they create first submission. If they want to see their previous submissions they need to provide their GUID. Otherwise they will be treated as new users (applications don't use user context as much as possible)
If you're curious you can read my blog post about this app...and on the end of it there is download link to my app