How to Get Filtered Items from SharePoint List to PowerApps using Microsoft Flow

In this blog post, I will explain “How to get filtered items from SharePoint list to PowerApps” when SharePoint List is able to hold more than 2000 records. It will cover a much more efficient solution: Using the “Send an HTTP Request to SharePoint” and “Request – Response” method. Inside flow, using the action of the same name, you will be able to return a collection of items of any shape and size to PowerApps.

So let us get into a quick walkthrough.

The Big Idea

In PowerApps, by using “SharePoint Connector” we can retrieve first 2000 records from a SharePoint List. So how to get filtered record if your list is going to be large (more than 2000 items) – To put into this perspective: we can execute a REST query with filter criteria to get filtered record using the “Send an HTTP Request to SharePoint” action in Flow. Flow is now capable of sending its resulting collection back into the app where it was triggered using “Request – Response” and we can bind collection with a gallery or dropdown menu in PowerApps.

The Microsoft Flow consists of these steps:

• Trigger the flow from an action in PowerApps.
• Capture the REST query from PowerApps.
• Execute the REST query.
• Parse the HTTP response in a JSON format.
• Detect a 200 response for success and send that data back into PowerApps.

Creating the Flow:

In this example suppose we want to retrieve only the list items those are belongs to the current logged in user. For this we create a “Single line text” field called “OwnersEmail” in the SharePoint list. Using REST we perform a GET of list items and filtering where a column “OwnersEmail” value contains current logged in user email.

Now we will explain each of the actions of the flow

• We will be creating the flow from a blank. As a best practice when creating a flow, start by adding the steps for the trigger and final action

PowerApps trigger

• Add a new action called “Send an HTTP Request to SharePoint” after the PowerApps trigger.

Set SharePoint site URL in “Site Address”

Method – “Get”

In the field “Url” , use the dynamic content box to choose “Ask in PowerApps”

Set the “Header” field as

Set Body field with some sample data.

As we need a sample HTTP response schema for our third and fourth action, we will use some sample data to answer the below questions so that Flow can generate the schema:

• What type of data is in each column?
• What are the names of those columns?
• Which columns are required?

Before we proceed, name your flow GetSPItem_REST, or another name of your choice, and save it.

Only then can we test the flow using the “Test” button to get some sample data.

A panel called “Test Flow” will slide out from the right. Select “I’ll perform the trigger action.” Then click on “Save & Test” button.

A dialog will appear asking you to enter REST Query.

(Sample REST Query : 
_api/lists/getByTitle('Migration%20Mapping')/items?$select=Title,Destination_x0020_Type,Jive_x0020_URL,Migration_Needed,Needs_x0020_Details,Site_x0020_Type,Special_x0020_Needs,Wave_x0020_Preference_x0020_1,Wave_x0020_Preference_x0020_2,GroupType,PrivateChoice,JiveType,OwnersEmail,UATUsers,Id&$filter=substringof('Demouser@nw.com',OwnersEmail)&$top=4999)

Note: Be sure to select all fields you want in “select” criteria in REST query to generate sample schema.

The purpose of this test is to get two or more records from the table that can be a representative sample of data with the type of the column. If Flow sees that a column consistently receives text records, it will identify that column as containing text. For sample data it is recommended to fill all the fields with some data.

If the flow run successfully, you will see this dialog:

You may also like: Find better ways to collaborate, share and organize

Click “Done” to see exactly how the flow performed. Expand the section for “Send an HTTP Request to SharePoint”

Now, we will copy the schema from OUTPUTS. This begins with the curly bracket { and ends with its corresponding bracket }.

Click “Edit” in the top right corner so that we can finish our flow.

• Add a new action “Parse JSON“after”Send an HTTP Request to SharePoint”step

In the Content field, select “Body” under “Send an HTTP Request to SharePoint”

At the bottom of the Response step, click the label for “Use sample payload to generate schema” and Paste the data you copied earlier, and then click on Done.

Now again we Save &Test the flow and copy the OUTPUTS of “Parse JSON”

For now, we will copy the data from “results“. This begins with the square bracket [ and ends with its corresponding square bracket ]. Be sure not to include the curly brackets at the end since they are there for ending the parent data. We will paste this later.

Delete the “__metadata” block from the schema.

"__metadata": {
          "id": "bbeebfd6-f41e-4010-994b-7d573dd43f95",
          "uri": "https://nw.sharepoint.com/sites/JiveCommunicationSite/_api/Web/Lists(guid'6b9599b4-5e90-493d-aced-a4b869ba179b')/Items(2425)",
          "etag": "\"3\"",
          "type": "SP.Data.Migration_x0020_MappingListItem"
        },

Sample Schema
[
      {
        "Id": 2425,
        "Title": "Test Site 2425",
        "Destination_x0020_Type": "Comm",
        "Jive_x0020_URL": "https://planet.nw.com/groups/testsite2425",
        "Migration_Needed": "Not Started",
        "Needs_x0020_Details": "No",
        "Site_x0020_Type": "Communication Site",
        "Special_x0020_Needs": true,
        "Wave_x0020_Preference_x0020_1": "Pilot",
        "Wave_x0020_Preference_x0020_2": "Wave 1",
        "GroupType": "Private",
        "PrivateChoice": true,
        "JiveType": "Group",
        "OwnersEmail": "mchakraborty@nwindia.com ",
        "UATUsers": " mchakraborty@nwindia.com",
        "ID": 2425
      }
    ]

• Add a new action “Response“(Response Request) after the “Parse JSON“step

In the Body field, select “results” under “Send an HTTP Request to SharePoint”

At the bottom of the Response step, click the label for “Use sample payload to generate schema”and Paste the data you copied earlier from “Parse JSON” outputs, then click Done. Use the sample data we just copied to generate a kind of structure or skeleton for the names of columns and the data types to expect in each column.

The Response Body JSON Schema field will no longer be empty at this point. Scroll through and check if each column was correctly named and validated. If a column’s type is not specified as a string, integer, or by a Boolean value, the flow will not register when connecting it to PowerApps.

Save the flow again and we are ready to incorporate it into an app!

Featured Workshop: Power Platform 3-Day Jumpstart Program

Creating the PowerApp

The app we are making simply consists of a Button and a Gallery; insert each one.

Configure the Button’s OnSelect property to run the flow.

Action > Flows > Select the flow “GetSPItem_REST“ from the right-hand panel that pops out

We will create a collection using “ClearCollect” function from the flow result and bind the collection with a gallery control

ClearCollect(SPMigrationMappingItemsColl,GetSPItem_REST.Run(Concatenate("/_api/lists/getByTitle('Migration%20Mapping')/items?$select=Title,Destination_x0020_Type,Jive_x0020_URL,Migration_Needed,Needs_x0020_Details,Site_x0020_Type,Special_x0020_Needs,Wave_x0020_Preference_x0020_1,Wave_x0020_Preference_x0020_2,GroupType,PrivateChoice,JiveType,OwnersEmail,UATUsers,Id&$filter=substringof(%27",User().Email,"%27,OwnersEmail)&$top=4999")))

In the above REST query we pass logged in user email as filter criteria.

Then set the Gallery’s Items property to the name of your collection “SPMigrationMappingItemsColl”

Set the field that we want to display in the gallery.

Rework the Labels inside the gallery to the desired columns.

The app is ready–simply hit your button, and see results appear in the gallery.

Output :

It is always a stumble or resistant that drives you to get a quick fix in an ongoing project. However, finding a solution is not the way forward, it grows when we share the solution with other. With the use of Microsoft Flow, I was able to get filtered record from a large SharePoint List. I think this is surely going to help people facing the same issue and return a collection of items of any shape and size to PowerApps. Will like to hear from you and discuss further if the guide has simplified your limit.