Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

enter image description here

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

enter image description here

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!

like image 617
JamesMatson Avatar asked Sep 27 '18 04:09

JamesMatson


People also ask

Can Power Apps call a connector?

Power Apps has connectors for many popular services and on-premises data sources, including SharePoint, SQL Server, Office 365, Salesforce, and Twitter. To get started adding data to a canvas app, see Add a data connection in Power Apps. A connector may provide tables of data or actions.

How do I connect Power Apps to Web API?

Go to “Connections” under the “Manage” section in the left navigation menu. And click on “New Connection” on the top right. Switch to the “Custom” tab and click “New Custom API”. You'll now be asked to provide a name and description for your custom API, along with uploading the Swagger definition file and an icon.

Can we use custom code in Power Apps?

You can add these code components to both model-driven and canvas apps. First of all, before you start creating code components, make sure that you have installed all the prerequisites that are required to develop components using the Power Apps component framework.


1 Answers

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.

enter image description here

like image 148
Meneghino Avatar answered Sep 23 '22 23:09

Meneghino