Displaying JSON data via a custom connector in PowerApps

[meghav93]

Hi All,

I have created a custom connector to connect to an API which returns JSON data in table format as a response. I added the connector to my PowerApps environment and tried fetching the data into a collection but I'm not seeing any data, only the column names. I'm pretty sure I'm missing something here, any insights will be helpful in tackling this issue. 

The images I'm sharing describe the problem.

P1 - Showing my method of fetching the data from the connector.

P2 - Showing that the gallery control isn't displaying any data.
P3 - Test operation showing that the connector is working fine while also showing the structure and expected data.

I checked the collection, which is empty as well.

Thank you!

[RandyHayes]

@meghav93 

I'm sorry to say that your Substances.data is the JSON string, your Collection will end up with a single row and a single column called Value with a text string of the JSON data.

PowerApps does not have the ability to convert JSON data into a collection from a string.

You would have to convert it from the string to a record in your app.

Based on your screenshot, you can utilize this formula to get that - not sure the need for a single record collection, so I will based this on a variable, you can substitute as needed.

Set(APIResultRecord,
    With({_apiResult: nametest.RunAPI({rxid: TestInput1_1.Text, format: TextInput1_2.Text}).Substances.data)},
        With({convertedJSON:
            ForAll(MatchAll(_apiResult, """(?<nvalue>[^""]+)"": *""?(?<jvalue>[^(""|\n)]+)""?").SubMatches,
                {Name:First(SubMatches).Value, Value:Last(SubMatches).Value}
            )},

        {uuid: LookUp(convertedJSON, Name="uuid", Value), 
         nsinchikey: LookUp(convertedJSON, Name="nsinchikey", Value),
         inchikey : LookUp(convertedJSON, Name="inchikey", Value),
         smiles: LookUp(convertedJSON, Name="smiles", Value),
         role: LookUp(convertedJSON, Name="role", Value)
        }
    )
)

I hope this is helpful for you.

[meghav93]

Hi Randy,

I understand the problem that you are mentioning now. I'm trying to use the example you shared but it has some format errors that I'm not able to figure out. Would you please be able to verify them? I'm trying to understand the relevance of With and ForAll functions as well since I haven't used them before.
Thank you!

[RandyHayes]

@meghav93 

What are you running into?

[meghav93]

So I ran into some syntax/type errors that I tried resolving, apart from that there seem to be function usage errors that I'm not able to resolve. The With, ForAll, MatchAll functions.

I appreciate your help on this, apologies if the questions are too trivial. 

[RandyHayes]

@meghav93 

Let's start here - I noticed I had and extra closing paren in my formula where it should not be.  Oddly enough, it looks like you switch that and the curly close around (this is at the end of the second line).

And then I also noticed I was missing a closing parent (for the second With).

So, replace with this formula:

Set(APIResultRecord,
    With({_apiResult: nametest.RunAPI({rxid: TestInput1_1.Text, format: TextInput1_2.Text}).Substances.data},
        With({convertedJSON:
            ForAll(MatchAll(_apiResult, """(?<nvalue>[^""]+)"": *""?(?<jvalue>[^(""|\n)]+)""?").SubMatches,
                {Name:First(SubMatches).Value, Value:Last(SubMatches).Value}
            )},

            {uuid: LookUp(convertedJSON, Name="uuid", Value), 
             nsinchikey: LookUp(convertedJSON, Name="nsinchikey", Value),
             inchikey : LookUp(convertedJSON, Name="inchikey", Value),
             smiles: LookUp(convertedJSON, Name="smiles", Value),
             role: LookUp(convertedJSON, Name="role", Value)
            }
        )
    )
)

See if that clears it up.

Sorry, I type these things by hand without any aid from the formula editor...so mistakes, they do happen!

[Kevin00]

Randy, I think you are answering my question as well and I am trying to convert your answer. I am a coder but new to Power Apps. Could you, or meghav93, describe your variables. For instance is "nametest" a connector?

I can do this 

UpdateContext({locURL: "https://www.mapquestapi.com/geocoding/v1/address?key=##################&inFormat=kvp&outFormat=json&location={"& dcTxtSupportLoc.Text & "}&thumbMaps=false"}); 
Launch(locURL);

and get JSON in a new tab, but I think I need your code to put the JSON from a string into key:value pairs per record. 

[RandyHayes]

@Kevin00 

Welcome to PowerApps...leave your developer hat behind! 😁  This is a no-code platform built around the concept of Excel (formulas).

So, what you are trying to do, you cannot do in PowerApps directly.  In @meghav93 case, nametest is the name of a Flow in PowerAutomate. 

Only from PowerAutomate would you be able to get the JSON from that url and then return to PowerApps as text.

AND...as this whole post is about, PowerApps will not convert to a record.  You can do some of that in PowerAutomate with the ParseJSON block, but if you just have raw JSON text coming back to your PowerApp, then the method in this post is one method to convert it.

[Kevin00]

Thank you. Definitely a different mindset. I will look into PowerAutomate to design a flow as my next step.

[meghav93]

Hey @RandyHayes and @Kevin00 , actually the nametest is a Custom Connector that I created which is talking to an API and trying to import JSON data from an endpoint. Is that something I'm doing wrong @RandyHayes ? 

I tried the new formula, it seems to have invalid argument errors for ForAll, MatchAll and LookUp commands that I'm trying to solve now.

Thank you Randy!