cancel
Showing results for 
Search instead for 
Did you mean: 

Parse JSON in PowerApps

Hi everyone,

 

The new JSON function in PowerApps is great and we have been using it extensively. Another great addition would be to parse JSON directly in PowerApps. If PowerApps receives a stringified JSON string, it should be able to easily Parse this and use this data in the app, to write it into a collection, variable or whatever.

 

We have been using the HTTP response in flow to return dynamic data to PowerApps, but seeing that this is now longer included in the Office 365 entitlement and that normal Respond To PowerApps Flow action not supporting Arrays or JSON, this request is quite urgent.

 

Please let us know if you have any questions or suggestions.

 

Kind regards

 

Dawid van Heerden
Follow on Twitter: @davestechtips
Subscribe to YouTube: https://www.youtube.com/davestechtips?sub_confirmation=1

Status: Completed
Comments
elmz
Helper I

Hi PowerApps Team,

 

Any idea when this feature will be out?

Hopefully by June 2022!

 

Thanks!

kartom
Regular Visitor

Hi!

 

Is there any updates on releasing this feature?

 

 

sumanguha82
Frequent Visitor

This feature is very much needed for our application. When this feature will be available? 

PauloRech
Advocate IV

If I give my customers the same treatment and time that Microsoft gives us, I would be fired!

CatSchneider
Frequent Visitor

So I've been seriously tinkering with a potential solution for this issue over the past week or so... I'm curious if anyone else finds this useful, or even viable enough until a solution from Microsoft is in place?

This requires a PowerAutomate Flow with a PowerApps(V2) Trigger.

  • For my setup the Flow needs to take in some text, either a string of JSON, or the SharePoint REST API string that results in some JSON text
  • The flow needs to then format the whole JSON value as an HTML table (from the Data Operations group), and return the body of the HTML table to the PowerApp via text

In the PowerApp, set a button's OnClick, or some other control's behavior formula to the following:

CatSchneider_0-1657189376323.png

  1. This is the Name of the PowerAutomate Flow that I've linked in my PowerApp.
  2. This is the text that I need for my flow to generate the JSON output for my SharePoint List.
  3. This is the name of the returned HTML table from my flow.

Is it the prettiest? No.

Is the end result exactly what I would hope for from a legitimate JSON Parser? Also no.

 

This does get me a collection of all my rows, with all my columns and the associated values in a string format that PowerApps doesn't fuss about. And the best part is that I don't even need to know the exact JSON Schema to get the information into the collection. Hopefully this helps someone! Let me know if there are any questions about the how or why of it all, or if you have any suggestions to help this run better.

This is the code text that I placed in a button's OnClick property:

 

Clear(objectDetail);

With({i: 
    Substitute(Substitute(Substitute(Substitute(
        'PowerApp-GetAllSPOListLibraryDetails'.Run(TextInput1_2.Text,"_api/Web/Lists").htmltable
        ,"'","'")
        ,"&","&")
        ,""",Char(34))
        ," "," ")
    },

    With({lh: MatchAll(i, "\<th\>(?<th>.+?)(?=\<\/th\>)\<\/th\>", MatchOptions.Multiline)},
        
        ForAll(MatchAll(i, "\<tr\>(?=\<td)(?<tr>.+?)(?=\<\/tr\>)\<\/tr\>", MatchOptions.Multiline) As lr,
            
            With({rc: MatchAll(lr.tr, "\<td\/\>|\<td\>\<\/td\>|\<td\>(?<td>.+?)?(?=\<\/td\>)\<\/td\>", MatchOptions.Multiline)},
                
                Collect(objectDetail,
                    {
                        RW: CountRows(objectDetail) + 1,
                        COL: ForAll(Sequence(CountRows(lh)) As c,
                                {
                                    FLD: Index(lh, c.Value).th,
                                    VAL: With({v: Index(rc, c.Value).td}, If(!(IsBlank(v) || v = "null"), Substitute(Substitute(v,"&lt;","<"),"&gt;",">")))
                                }
                            )
                    }
                )
            )
        )
    )
)

 




vpip
Regular Visitor

@CatSchneider That is fantastic. Thanks for sharing it. An approach I’ve taken is to use Power Automate as well, but to instead run the JSON text through the xml() workflow expression function. Based on the structure of your JSON data, you may need to finesse it a little by converting it to a string and concatenating elements the xml() function expects. Then, when you return the now XML-formatted data to Power Apps, you can much more easily extract key-value pairs (without needing to hassle with prone-to-break Match statements) like:

 

{
    property_name: Last(Split(First(Split(Your_XML_Formatted_Value,"</property_name>")).Result,"<property_name>")).Result,
}

 

 

TedBabcock
Advocate II

This is cool! I started working on something similar with HTML tables, based on Shane Young's video on pasting Excel data:

https://www.youtube.com/watch?v=foGaOrBzVLc

 

But MS announced that they were going to have the JSON parser done this summer, so I stopped working on my makeshift version. At least I learned a lot, though.

CatSchneider
Frequent Visitor

@vpip the reason I went with the HTML data operation over the xml function, was due to the following three facts:

  • All the field names (the key of the key-value JSON pairs) are always going to come through with the <th> tags.
  • All rows are always going to come through with <tr> tags
  • All rows will always contain the same number of <td> tags (the value of the key-value JSON pairs) as <th> tags, and will be in the exact same order.

This means that I know the value within the 1st, 4th, or nth <td> tag in every single row is going to respectively associate with the 1st, 4th, and nth <th> tag for the field/key name. And now it doesn't matter what the JSON schema is, or how the JSON is even introduced into the flow, because the output is being grouped by row and dropping the Field/Key name from the column headers, and the Value (as a string) from the row cells into a 2-column record for each column value.

I also only need 3 regex expressions to generate the values:

  • For the table column header values: "\<th\>(?<th>.+?)(?=\<\/th\>)\<\/th\>"
  • For each of the non-header table rows (which is practically the exact same as the header regex, but checks for <td> tags) "\<tr\>(?=\<td)(?<tr>.+?)(?=\<\/tr\>)\<\/tr\>"
  • For each value of the row's columns: "\<td\/\>|\<td\>\<\/td\>|\<td\>(?<td>.+?)?(?=\<\/td\>)\<\/td\>"
    • With this expression I needed to also account for empty/null values by including the closed-open <td/> tag.


@TedBabcock I too basically quit trying to fight with PowerApps once I saw Microsoft state they were expecting a solution by this summer, but then this solution hit me like a ton of bricks late last week and I've just been churning it over and over with all different variations of JSON values and getting a workable output each time. I figured I share it with anyone who keeps beating their head against this problem while we all await the deployed solution from the developers.

vpip
Regular Visitor

@CatSchneider That's pretty insightful. You’ve got a sweet solution to this painful Power Apps problem

TedBabcock
Advocate II

Thanks, @CatSchneider ! I'm going to look at this again and use your insights.