Power Platform Community Forum Thread Details

I'm four layers deep on yak shaving and rapidly losing the will to live.

Layer 1: My actual problem is that I'm trying to securely get a small amount of data from a non-MS back-end into a Power App front-end, as someone with a decent amount of previous coding experience, but none with Power Apps or Flow specifically or with low-code environments more generally. The data involved changes dynamically, but rarely (normally at most 10 times per day). Frankly I don't think Power Apps are the best tool for the job, but I don't have a choice - I have to use it to comply with our security policies.
Layer 2: Power Apps can't parse CSVs natively and they certainly can't hook into SQLite (the format the data is actually stored in). The Dataverse also can't hook into these things. It looks like the least-worst method of passing the data is to have the back-end maintain an Excel file containing it all and have my Power App read the Excel file. OK, I already have back-end code to write to Excel files, so this is annoying but not the worst thing in the world.
Layer 3: Power Apps can only read an Excel file if the user has full read permissions for that file - they can't directly inherit my permissions. In this case, that's not an option since every user has some data in the sheet that should only be private to them. Also, some of the people using this software are literal cybersecurity researchers, so anything involving "security by obscurity" is also not an option. The only way round this that I can find is for me to integrate the Power App into a solution with two flows. The parent flow is invoked by the Power App and calls a child flow. The child flow includes expected users of the Power App as "run-only users", and then uses my permissions to read the Excel file. The child flow reads the Excel file and returns the data to the parent flow, which responds to the Power App. I have to keep the two flows separate because "run-only users" are only allowed for flows which are triggered manually - not for flows which are triggered from a Power App. OK. This took a long time to figure out, but I have a proof-of-concept working.
Layer 4: The content I want to access from the Power App is several specific rows of the Excel sheet (depending on which user is running the app), and MS Flow can't pass or return arrays or dictionaries. If I was working with Power Apps directly, I could just use the Excel connector and everything would be fine. If I had just one flow, then I could apparently use the Select widget to encode the rows as a JSON and then return it to the Power App via HTTP using the Response widget. But you can't send an HTTP response from a child flow to a parent flow, and you can't respond from the child flow to the Power App directly. And I can't find any straightforward widgets for converting back and forth between JSONs and strings.

This is the point where I start howling in pain and run to the Internet for help. Please tell me that I don't have to manually convert the Excel data into strings and then re-parse it into a sensible form inside Flow and Power Apps...!

Categories:

Building Power Apps