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 List
โ€ข Create A Flow In Power Automate
โ€ข Send The Flow Result To Power Apps
โ€ข Determine The Flow Output JSON Schema
โ€ข Use The ParseJSON Function To Create A Collection
โ€ข Display 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.




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

58 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Amit
Amit
1 year ago

Great article Matthew!!

Amy
Amy
1 year ago

This is just awesome ๐Ÿ˜Š๐Ÿ’—

Frans
Frans
1 year 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 year 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 year 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 year 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 year 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 year ago by adicristea
adicristea
adicristea
1 year ago

Thank you. That is indeed strange.

Dawid 365corner
1 year 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 year 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 year 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 year 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 year 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
1 year 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.

Anthony.L
Anthony.L
1 year ago

Hello Matthew! The info you’ve provided gives me hope that what I am try to accomplish is possible :). With that said here is the date recieved from flow:

{
  "9780980200447": {
    "bib_key": "9780980200447",
    "info_url": "https://openlibrary.org/books/OL22853304M/Slow_reading",
    "preview": "borrow",
    "preview_url": "https://archive.org/details/slowreading00mied",
    "thumbnail_url": "https://covers.openlibrary.org/b/id/5546156-S.jpg",
    "details": {
      "number_of_pages": 92,
      "table_of_contents": [
        {
          "level": 0,
          "title": "The personal nature of slow reading",
          "type": {
            "key": "/type/toc_item"
          }
        },
        {
          "level": 0,
          "title": "Slow reading in an information ecology",
          "type": {
            "key": "/type/toc_item"
          }
        },
        {
          "level": 0,
          "title": "The slow movement and slow reading",
          "type": {
            "key": "/type/toc_item"
          }
        },
        {
          "level": 0,
          "title": "The psychology of slow reading",
          "type": {
            "key": "/type/toc_item"
          }
        },
        {
          "level": 0,
          "title": "The practice of slow reading.",
          "type": {
            "key": "/type/toc_item"
          }
        }
      ],
      "contributors": [
        {
          "role": "Cover Photographs",
          "name": "C. Ekholm"
        }
      ],
      "isbn_10": [
        "1936117363"
      ],
      "covers": [
        5546156
      ],
      "lc_classifications": [
        "Z1003 .M58 2009"
      ],
      "ocaid": "slowreading00mied",
      "weight": "1 grams",
      "source_records": [
        "marc:marc_loc_updates/v37.i01.records.utf8:4714764:907",
        "marc:marc_loc_updates/v37.i24.records.utf8:7913973:914",
        "marc:marc_loc_updates/v37.i30.records.utf8:11406606:914",
        "ia:slowreading00mied",
        "marc:marc_openlibraries_sanfranciscopubliclibrary/sfpl_chq_2018_12_24_run04.mrc:135742902:2094",
        "marc:marc_loc_2016/BooksAll.2016.part35.utf8:160727336:914",
        "promise:bwb_daily_pallets_2022-09-12"
      ],
      "title": "Slow reading",
      "languages": [
        {
          "key": "/languages/eng"
        }
      ],
      "subjects": [
        "Books and reading",
        "Reading"
      ],
      "publish_country": "mnu",
      "by_statement": "by John Miedema.",
      "oclc_numbers": [
        "297222669"
      ],
      "type": {
        "key": "/type/edition"
      },
      "physical_dimensions": "7.81 x 5.06 x 1 inches",
      "publishers": [
        "Litwin Books"
      ],
      "description": "\"A study of voluntary slow reading from diverse angles\"--Provided by publisher.",
      "physical_format": "Paperback",
      "key": "/books/OL22853304M",
      "authors": [
        {
          "key": "/authors/OL6548935A",
          "name": "John Miedema"
        }
      ],
      "publish_places": [
        "Duluth, Minn"
      ],
      "pagination": "80p.",
      "classifications": {},
      "lccn": [
        "2008054742"
      ],
      "notes": "Includes bibliographical references and index.",
      "identifiers": {
        "amazon": [
          "098020044X"
        ],
        "google": [
          "4LQU1YwhY6kC"
        ],
        "librarything": [
          "8071257"
        ],
        "goodreads": [
          "6383507"
        ]
      },
      "isbn_13": [
        "9780980200447",
        "9781936117369"
      ],
      "dewey_decimal_class": [
        "028/.9"
      ],
      "local_id": [
        "urn:sfpl:31223095026424",
        "urn:bwbsku:O8-CNK-818"
      ],
      "publish_date": "March 2009",
      "works": [
        {
          "key": "/works/OL13694821W"
        }
      ],
      "latest_revision": 24,
      "revision": 24,
      "created": {
        "type": "/type/datetime",
        "value": "2009-01-07T22:16:11.381678"
      },
      "last_modified": {
        "type": "/type/datetime",
        "value": "2022-12-04T10:37:36.772707"
      }
    }
  }
}

The first number is a string passed as a variable from power apps to flow.

So my questions is: Why do you suppose the Collection is reading the keys but not returning the values?

Here is my simple code (in power apps):

ClearCollect(
    openLIBSearchResults,
    openLibraryResponse.Run(isbnInput.Text)
);

Thank you!

-Anthony

Capture.PNG
xiao
xiao
1 year ago

This is Great Article, thank you for your sharing, Mattew!
I followed the steps, but got error for “Table(ParseJSON(GetCarInventorySPListItems.Run().result).body)”
I found that if I add “Set(ResultJson, GetCarInventorySPListItems.Run());”, ResultJson shows the dataType bool, then no “result” option.

Last edited 1 year ago by xiao
Ramzi
Ramzi
1 year ago

Great job Matthew ! i tried this exemple and it works fine. i have a question please ? if we have a nested array in the Json :

[
{
“companyname”: “MD”,
“companyID”: “327733184”,
“nombre_employee”: 51,

“managers”: [
“firstname”: “matthiew”,
“lastname”: “Devaney”
]
}
]

How can we access to manager list by compagny in this case in power apps ?

Best regards

Ramzi
Ramzi
1 year ago

Hi Matthew !
I attached the flow screenshot in this discussion.

In power apps i used :

Table(ParseJSON(CallAPI.Run().jsonresultfrompowerautomate).body);
but i cannot access to managers values of the selected campany.

i simplified the JSON ๐Ÿ™‚

{
“type”: “object”,
“properties”: {
“type”: {
“type”: “string”
},
“properties”: {
“type”: “object”,
“properties”: {
“companyid”: {
“type”: “object”,
“properties”: {
“type”: {
“type”: “string”
}
}
},
“companyname”: {
“type”: “object”,
“properties”: {
“type”: {
“type”: “string”
}
}
},
“managers”: {
“type”: “object”,
“properties”: {
“type”: {
“type”: “string”
},
“items”: {
“type”: “object”,
“properties”: {
“type”: {
“type”: “string”
},
“properties”: {
“type”: “object”,
“properties”: {
“firstname”: {
“type”: “object”,
“properties”: {
“type”: {
“type”: “string”
}
}
},
“lastname”: {
“type”: “object”,
“properties”: {
“type”: {
“type”: “string”
}}}}}}}}}}}}}

Thank you !

flow.png
Nakul
Nakul
1 year ago

Hi Matthew,
Could you please help me with the following scenario?

I have excel sheets in the SharePoint document library. I want to extract the content of an excel sheet(table) and want that tabular content inside a collection in Powerapps.

Arnold
Arnold
1 year ago

I was facing a simular issue with a response from a child flow and didnยดt want to use a premium connector. Thanks a lot.

Last edited 1 year ago by Arnold
Daniel
Daniel
1 year ago

I think there are a few people with similar issues as mine. I’m trying to access nested tables -records but haven’t got it to work yet.
One column that’s causing me a headache is:
diver_KBCategory
With the below data.
[{“@odata.type”:”#Microsoft.Azure.Connectors.SharePoint.SPListExpandedTaxonomy”,”TermGuid”:”3axxxxxxxe”,”WssId”:81,”Label”:”Policies”,”Path”:null,”Value”:”Policies|3ae6dxxxxxxxx6e”}]

I’m using this to access single nested tables: Text(ThisItem.Value.Editor.’Department’)
But no luck using any variation I can think of, any suggestions?

Mike
Mike
1 year ago

This is a great article, Matthew, but I need help with something where the solution may be similar. When I retrieve a single row from the list, I would like it to be in JSON format, with the values. My SP list has almost every data type. Do you know how I can accomplish this? Thanks for any help/advice you may offer.

Ram
Ram
1 year ago

How can we convert received data in powerapps, if we receive a file Thumbnail, file identifier

Brad Allison
Brad Allison
1 year ago

This is exactly what I was looking for in a recent project. I have a massive excel file that is generated everyday from a process and is saved into a SharePoint location. Because that file is over 64,000 rows, I does not play well with a PowerApp connection. So I am sending a parameter value (selected employee ID) to a power automate job which selects only those records whose employee ID matches (oData query), I call a Select to create the JSON, and then return that Select string. I didnt know how to parse that file until I did a search and found this article! It is brilliant and thank you for what you do as this is not the only article and help from you that I have saved in my list of Edge links.

Seema
Seema
11 months ago

I have followed all the steps but the last collection doesnt show the values. I see headers but not values.
Can you please help ?

ValuesMissing.png
Julie
Julie
6 months ago
Reply to  Seema

I am facing a similar issue did you manage to solve it?

Michael
Michael
9 months ago

Thank you SO much for this guide! Your scenario is almost identical to what I was trying to accomplish.

I was about to pull out my hair trying to figure this out. And I couldn’t find any other guides online that covered this scenario.

I need to learn to head straight to your website whenever I’m faced w/ a PowerApps issue.

Jaime
Jaime
9 months ago

This was exactly what I needed to verify my PowerApp was even feasible (needed to dynamically switch between SharePoint list folders based on user selection). The ParseJSON feature saved me from tons of Power Automate headache! The only thing is I had to remove the “.body” ending to the ParseJSON() function. Once I did that, everything came into my PowerApp collection! Thank you for this article!