Retrieve SharePoint List Data into Power Apps Using Power Automate

I received a client requirement to retrieve data from a SharePoint list into Power Apps using a Power Automate flow. Although it is possible to fetch SharePoint list data directly in Power Apps, there are scenarios where using a flow is a more effective approach.

For example, handling delegation issues when working with large datasets in Power Apps. Additionally, some administrators may not want users to have direct access to SharePoint via a connector. In such cases, or for any other similar reason, using a Power Automate flow to retrieve the data is a better approach.

In this article, I will explain how to retrieve SharePoint list data into Power Apps using Power Automate.

Create a Power Automate Flow to Retrieve SharePoint List Data

To get SharePoint list data into a Power Apps application using Power Automate, we need to go through the following two stages:

• Create a flow to get the list data and send it to Power Apps
• Power Apps receives the output from the flow and uses it as needed

Here is the SharePoint list named “Employee Expense Claims.”

Follow the steps below to create the flow:

1. Create a Power Automate instant cloud flow and choose When Power Apps calls a flow(V2) trigger.

2. Then, add the Get items action, as shown in the image below, and provide the SharePoint site and list addresses.

3. Since we are also fetching the attachments of each list item, we need to store them in a variable, so add an Initialize variable action. Choose variable type as Array.

4. Then, add the Get attachments action to retrieve the attachments for the SharePoint list items. Then provide data for the following parameters.
   • Site Address = Select the site address from the dropdown.
   • List Name = Select the list name.
   • Id = Provide the ID from the “Get items” action.

Once you choose ID, the foreach loop will be added automatically.

5. Now, add the Append to array variable action to update the variable we previously created. Then, provide the data for the parameters as follows:
   • Name = VarAttachments
   • Value = provide the code below:

{
  "Id": @{items('For_each')?['ID']},   //Get items action Id field
  "Name": "@{items('For_each_1')?['DisplayName']}", //Get attachments action field
  "AbsoluteUriI": "@{items('For_each_1')?['AbsoluteUri']}" //Get attachments action field
}

6. After that, exit the loop and add a Select action. Assign the parameter values with ones below:
   • From = Provide the body/value from the “Get Items” action.
   • Map = Provide the column names on the left side and, on the right side, assign those values from the “Get items” action.

7. Finally, now we need to send the SharePoint list items and their attachments to Power Apps. So choose two text outputs and rename them like:
   • Attachments = Provide the VarAttachments variable over here.
   • ListData = Provide the Select action output.

That’s it; now the flow is complete.

Get Power Automate Flow Response in Power Apps

So far, we have created a flow that retrieves data from a SharePoint list and its attachments, and sends it to Power Apps. Now, in this section, we will see how to retrieve that flow data and convert it from JSON to Tabular format.

After converting, we will display the list data in a gallery and display the attachments in a form control for the selected item from the gallery. To achieve that, continue with the following steps:

1. Now, go to Power Apps and add that flow to the application, as shown in the example below.

2. Now, add a button control in Power Apps, and provide the code below in its OnSelect property.

Set(varOutput,RetrieveSPListDataWithAttachments.Run());
ClearCollect(
    colListData,
    ForAll(
        Table(ParseJSON(varOutput.listdata)),
        {
            Title: Text(Value.Title),
            EmployeeName: Text(Value.EmployeeName),
            Department: Text(Value.Department),
            ExpenseDate: Text(Value.ExpenseDate),
            ExpenseAmount: Value(Value.ExpenseAmount),
            ExpenseCategory: Text(Value.ExpenseCategory),
            ID:Value(Text(Value.ID))
        }
    )
);
ClearCollect(
    colAttachments,
    ForAll(
        Table(ParseJSON(varOutput.attachments)),
        {
            ID: Value(Text(Value.Id)),
            URI: Text(Value.URI),
            Name: Text(Value.Name)
        }
    )
);

Let’s understand how exactly we are converting the Power Automate JSON output into a format that Power Apps can read.

• RetrieveSPListDataWithAttachments.Run() = It is calling the Power Automate flow.
• varOutput = This variable will store the outputs of the flow. The outputs are:
  • listdata = Holds the SharePoint list data.
  • attachments = Holds all the attachments of each list item.
• Above, both outputs are returning in a JSON format, so we need to parse them. For this, we use the ParseJSON() function in Power Apps.
• This ParseJSON() takes a text string in JSON format, such as “[{…},{…}]”, and converts it into a Dynamic object in Power Apps.
• Now, the Dynamic is a special data type in Power Fx that can hold any structure, such as nested objects, arrays, strings, and more. We can’t directly use a dynamic value without converting its fields into a supported type. As below:
  • Text(Value.Title)
  • Value(Value.ExpenseAmount)
• Now, the collection colListData contains SharePoint list items, all field data except the attachments. Then, colAttachments contains all the attachments for the list items.
• ForAll() = This function iterates over each item present in the dynamic value returned by the ParseJSON function.

Display the Retrieved SharePoint List Data From Flow in the Power Apps Gallery

3. Add a Gallery control, and provide the collection name below in the gallery’s items property.

colListData

Within the gallery, add text labels and provide the following formulas in its Text property so that we can get the retrieved fields’ data:

ThisItem.Title
ThisItem.EmployeeName
ThisItem.Department
ThisItem.ExpenseCategory
ThisItem.ExpenseAmount
ThisItem.ExpenseDate

Display the Attachments in the Form Control of the Selected Item From the Gallery

4. Now, we will add a form control and display the selected item details from this gallery on that form. At that time, I will explain how to display the attachments of a specific item that has been chosen.
   • Add the code below to the OnSelect property of the arrow icon where it is present within the gallery.

Set(varRecord,ThisItem);Navigate(Screen3)

The varRecord variable contains the current record details, and using the Navigate() function, we will navigate to another screen.

5. On Screen3, add a Power Apps form control and connect with a SharePoint list, then for the attachment control items property, add the below code:

Filter(colAttachments, ID = varRecord.ID)

Here, the filter will return the attachments where the attachment ID matches the current item ID.

This way, we can fetch the SharePoint list data along with attachments using a Power Automate flow into a Power Apps application. Additionally, convert the Power Automate flow output into a format that Power Apps can understand.

I hope you found this article helpful!, In this article, I have explained how to retrieve SharePoint list data into Power Apps using a Power Automate flow. Follow this article if you are also looking to achieve the same thing!

Also, you may like:

• Power Apps Form Validations
• Set No Selected Item in a Power Apps Gallery
• Power Apps Currency Format
• Save Power Apps Form Attachments to SharePoint List Using Power Automate
• Hide Return to Classic SharePoint link in SharePoint