Access JSON results from Custom Connector (Web API) in PowerApps

Question

I'm trying to get the JSON text results, e.g.

[
    {
        "TABLE_NAME": "UpdatePlanning"
    },
    {
        "TABLE_NAME": "StoreInfo"
    },
    {
        "TABLE_NAME": "InSiteTxPerHourPerDay"
    },
    {
        "TABLE_NAME": "inSiteTaskRecordsLocal"
    },
    {
        "TABLE_NAME": "InSiteStoreInformation"
    },
    {
        "TABLE_NAME": "InSiteLogExtractionTest"
    },
    {
        "TABLE_NAME": "InSiteDailySalesPerDay"
    },
    {
        "TABLE_NAME": "FredOfficeLogAlerts"
    },
    {
        "TABLE_NAME": "DPTestAutoScaleTable"
    },
    {
        "TABLE_NAME": "DPGenHoldTable"
    },
    {
        "TABLE_NAME": "DPDailyTopSellerItems"
    },
    {
        "TABLE_NAME": "DPDailyTierSales"
    },
    {
        "TABLE_NAME": "DPDailySales"
    },
    {
        "TABLE_NAME": "DPDailyAvgBasketSize"
    },
    {
        "TABLE_NAME": "ASGInSiteStoreInformation"
    }
]

From a web API I have wrapped in a 'custom connector' to use in PowerApps. My custom connector works great, and I can test it within the custom connector screen OK, but I can't figure out how to access that JSON data within Power Apps to - for example - populate a data table, or populate a list, or gallery, or even just a label? Bear in mind the JSON schema returned from some of the GET methods aren't fixed, e.g. the JSON structure can be different depending on the type of object queried etc, but no matter what I can't get it working.

Example: I've tried this in my OnStart method for the first screen in the PowerApps app

Set(myTable,InSiteConnector.gettables())

With the assumption my JSON would be stored in the variable myTable.. but if I reference myTable in a lable, or anywhere, it doesn't produce anything. Using the same method for a data table or chart or list is the same, no results. What am I missing here? I've scoured the web, but nothing I try syntax wise seems to work.

Similarly, if I create a data table and try to select my custom connector as the data source, e.g. it is in the list of data sources

But adding it just causes it to show up again and again in this list...

but I can't click it, or do anything with it that seems obvious in so far as getting data from it? (e.g. being able to choose fields from my JSON, or even manipulating the raw BODY back from the GET method(s))

I have started a bounty for someone to please show me a working and replicable example of getting the JSON from this web API into a PowerApps app for use in a gallery or similar. Thank you!

Answer 1

The JSON schema for the return data must be defined, any data that does not fit the schema will not be passed back to PowerApps. This is because the schema is used to define the return type in PowerApps for further use. You could say that the connections are strongly typed, in a way.

Below is a screenshot of where you can define the response body in order for the data to 'show up as the outputs in designer', as is helpfully hinted.

What you can do if your schema is variable, is to use Flow to get the data and process it and conform to your defined response schema before returning it to PowerApps.

For example see this blog post. If your response body is variable then you could insert some logic in the Flow after the GetDailySales step to shape the reponse body to fit the JSON schema defined in the Response step.