Power Apps ParseJSON Function: Get Collection From A Flow

Power Apps ParseJSON Function: Get Collection From A Flow

Power Apps new ParseJSON function can turn a text string into a record or table. It is especially useful when app is used to trigger a Power Automate flow and returns a value to the app. Power Automate only allows returning a text string with the Respond To Power Apps (V2) action. But with the ParseJSON function we can easily convert that text string to another data type. In this article I will show you how to return a collection from a flow to Power Apps using ParseJSON.

Table Of Contents:
• Setup The SharePoint ListCreate A Flow In Power AutomateSend The Flow Result To Power AppsDetermine The Flow Output JSON SchemaEnable The ParseJSON Function And Untyped ObjectsUse The ParseJSON Function To Create A CollectionDisplay The Collection In A Gallery




Introduction: The Car Sales Inventory App

The Car Sales Inventory app is used by salespeople at a car dealership to create a report on all the cars currently in-stock. They open the app to the inventory screen and click on a button to show all cars in a SharePoint list.




Setup The SharePoint List

Create a new SharePoint list called Car Inventory with the following columns:

  • Year (number)
  • Make (single-line text)
  • Model (single-line text)
  • PurchaseDate (date only)
  • Sold (yes/no)



Load this sample data into the the SharePoint list:

CarYearCarMakeCarModelPurchaseDateSold
2009MazdaMX-510/21/2020Yes
1985HondaAccord5/2/2018Yes
2001FordWindstar10/22/2019Yes
1994MitsubishiEclipse2/16/2019No
2003LamborghiniGallardo11/9/2020Yes
2005SubaruLegacy11/3/2020Yes
1997FordExplorer6/15/2018Yes
2010LexusGS6/11/2019Yes
2010DodgeRam9/7/2020No
1995Volvo9608/22/2018Yes




Create A Flow In Power Automate

Open Power Apps Studio and create a new blank canvas app. Insert a rectangle shape at the top of the app to make a header. Place a label on top of the rectangle to show the app’s title.



Browse to the Power Automate tab on the left-navigation menu. Then select Create new flow.



On the Create your flow menu choose + Create from blank.



Name the flow Get Car Inventory SP List Items.




Send The Flow Result To Power Apps

Next we will retrieve items from the Car Inventory SharePoint list in Power Automate and send the result the result back to Power Apps. Add the SharePoint – Get Items action to the flow as shown below.



The SharePoint Get Items action only returns up to 100 records by default. To increase the limit go the action’s settings menu, turn on pagination, and write a number in the threshold field. The maximum value is 5000.



There are several columns in the SharePoint list we don’t want to bring into Power Apps (modified by, modified on, id, title, etc.). Use the Data Operations – Select action to show the columns we want and drop the other columns we don’t need.



Finally, put a Power Apps – Respond to a PowerApp or flow action at the end of the flow. Add a text type output called Result.



Use this flow expression to return the array of values created in the previous Select action to Power Apps.

outputs('Select:_Car_Inventory_Columns')




Determine The Flow Output JSON Schema

To use the ParseJSON function in Power Apps we must determine the JSON schema output by the flow. The best way to do this is by running a test of the flow and inspecting the final flow action. Go to the maker portal, browse to the flows tab and open the Get Car Inventory SP List Items flow.



Click on the Test button and run a manual test of the flow.



After the flow test is finished, look at the outputs of the Respond to a PowerApp or flow action. The body property contains the JSON schema. Notice that the SharePoint items are contained inside the body property of a JSON object. This information will come in handy soon when using the ParseJSON function in Power Apps.

Enable The ParseJSON Function And Untyped Objects

The ParseJSON function is an experimental feature in Power Apps. To use the ParseJSON function, go to the Settings menu, browse to Upcoming features and turn on the setting called ParseJSON function and untyped objects. If this feature is not turned on the ParseJSON function will not be found in the formula bar.




Use The ParseJSON Function To Create A Collection

When a button is pressed in Power Apps we want to load the flow output into a collection. Insert a new button and place it on the screen.



Use this code in the OnSelect property of the button. It runs the Power Automate flow and converts its output in the result property (text data type) into an untyped object using ParseJSON. Then it uses ForAll to loop over the untyped object and define each column’s data type using the Value function, Text function, Boolean function and DateValue function. These functions are known as type constructors.


Also, notice that we used the body property of the ParseJSON function result. This is why we needed to determine the flow output’s JSON schema in a prior step.

ClearCollect(
    colCarInventory,
    ForAll(
        Table(ParseJSON(GetCarInventorySPListItems.Run().result).body),
        {
            Year: Value(Value.Year),
            Make: Text(Value.Make),
            Model: Text(Value.Model),
            Sold: Boolean(Value.Sold),
            'Purchase Date': DateValue(Value.'Purchase Date')
        }
    )
)




Display The Collection In A Gallery

We’re almost done. The last step is to display the collection in a gallery. Add a new gallery to the screen and use the collection colCarInventory as the datasource.



Insert 5 labels into the gallery: 1 label for each column in colCarInventory.



Then write of line this code block in each label to display the field’s value in the gallery.

ThisItem.Year
ThisItem.Make
ThisItem.Model
ThisItem.PurchaseDate
ThisItem.Sold



Finally, create one additional label and place it on top of the gallery. Write the matching column names into the label. The app is now finished.





Questions?

If you have any questions about Power Apps ParseJSON Function: Get Collection From A Flow please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion.

Matthew Devaney

Subscribe
Notify of
guest
23 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Amit
Amit
1 month ago

Great article Matthew!!

Amy
Amy
1 month ago

This is just awesome 😊💗

Frans
Frans
1 month ago

Why choose SharePoint now that Dataverse is free up to 3 gb and you have no cost for the connector.

Otherwise, of course, an interesting article.

PowerPlatformProfessional
PowerPlatformProfessional
1 month ago
Reply to  Frans

How is Dataverse free up to 3 gb? Very curious about this because I thought if you used dataverse in your apps you were marked as “Premium” license and it wasn’t free unless you build the apps in a team environment. Did something change?

PowerPlatformProfessional
PowerPlatformProfessional
1 month ago

Yeah, I was scratching my head on 3gb and “free”. I’m aware of the teams pieces but that comes with limitations as well. Thought I missed something major from Ignite because you traditionally do not hear “free” from Microsoft lol.

PowerPlatformProfessional
PowerPlatformProfessional
1 month ago

I agree, cost should not be the only factor when looking at what value the platform can be to the business. Having seeded licenses in M365 certainly helps adoption and can showcase “Art of the possible” that then can lead to more robust and creative solutions.

I follow your posts quite a bit and you provide a wealth of knowledge to so many others and it’s great to see just how much you embrace sharing with the community.

adicristea
adicristea
1 month ago

Hi Matt,

Is the formula for the return to Power Apps missing ‘string’? The screenshot surely shows that, but the formula only has the output of the Select which is an array and can’t be returned to Power Apps (outputs(‘Select:_Car_Inventory_Columns’)), isn’t it?

Thank you for the article!

Last edited 1 month ago by adicristea
adicristea
adicristea
1 month ago

Thank you. That is indeed strange.

Dawid 365corner
1 month ago

Hi Matt,
As always great post:)
Just wanted to add two cents that ForAll is quite slow 🙁 with AddColumns function you can process data much quicker:)
Manage to describe the method but here I will end up the self promotion:p

Ps. It is awesome to see how consistent your are with blogging! Keep it up 🙂

Dawid 365corner
1 month ago

Here, is the link, where I described it:)

In general the ForAll allows to access ThisRecord context the AddColums have the same power but is done on batch and not one by one:)

Hope it will help 🙂

https://365corner.pl/2022/09/16/how-to-parse-json-faster-in-power-apps/

PowerPlatformProfessional
PowerPlatformProfessional
1 month ago

this is great and thanks so much for sharing. I had a question about how would you go about returning multiple selects to Power Apps.

So say you wanted to return data back from a single flow with 3 lists can that happen or would you have to call 3 flows to run.

Hoping to use a single flow and if you can combine the data from the selects into a single response back to the Power App.

Can you provide some insight in how that might happen?

PowerPlatformProfessional
PowerPlatformProfessional
1 month ago

What I ended up doing was using a single call to build the JSON in a single collection for 10 lists, in the flow I returned 10 results back to the app, then I used a 2nd Parse to dump them all into a single collection from the 10 results returned.

It worked well and in about 26 seconds pulled back 4k records

Using apply loops and split prior it took over 4 minutes to get the same results.

Next, I want to see the add column approach instead of ForAll and see if that has any improvements. Thanks again for sharing on how you put things together.

Volker Niemeyer
Volker Niemeyer
1 month ago

Thanks for the post. Unfortunately, I am not getting any results in Power Apps on my query from a MSSQL database.
The JSON string also looks different: 

“daten”: “{\”ResultSets\”:{\”Table1\”:[{\”id\”:1,\”Aenderung…

Is this still correct?

ForAll(
    Table(ParseJSON(GetCarInventorySPListItems.Run().result).body),

Best regards

Volker

Ricardo Carneiro
Ricardo Carneiro
27 days ago

I am currently finishing a project to collect data from excel files. It calls 6 different Flows and I am struggling to show the User how the Flows are going. Sync PowerApps and Power Automate is my challenge but I think I will try your solution.
So why 6 Flows?

  1. Copy 2 (entry point) Excel files to a Doc.Library.
  2. transform Sheets into tables in the Excel file. the customer does not want to generate the files with tables.
  3. Read both files and populate a SharePoint List
  4. Verify if there are new Employee IDs in the files. I use another Static List with all existing Employees as a reference.

and then the App will allow the User to update any missing information using alerts I created in a few galleries.

I am having a lot of fun doing it in PowerAps/Power Automate.

Thanks for sharing.