cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
meghav93
Helper I
Helper I

Displaying JSON data via a custom connector in PowerApps

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!

42 REPLIES 42
RandyHayes
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
meghav93
Helper I
Helper I

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
Super User
Super User

@meghav93 

What are you running into?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
meghav93
Helper I
Helper I

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
Super User
Super User

@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!

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
Kevin00
Helper II
Helper II

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
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
Kevin00
Helper II
Helper II

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

meghav93
Helper I
Helper I

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!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (1,535)