Get SharePoint Library or List Total Items from Power Apps

Have you ever created a Power App consuming one or more data source connections to SharePoint Libraries or Lists and been frustrated because you have no way of determining the total number of items in the data source should said data sources contain more items than the Data row limit configured for the app for non delegatable queries (being between 500 – 2000)?

In such cases at the very least you may wish to provide visibility to users of the app the actual total number of items in the library or list data source however unfortunately the Power Apps CountRows function does not support delegation for SharePoint data sources. As such it will only display the correct total number of items for a SharePoint data source when the total number of items in the library or list is equal to or less than the data row limit configured for the app.

Fortunately it is possible to expose this info by leveraging a Power Automate flow along with the help of the SharePoint / Graph REST APIs.

Whilst the technique detailed in this blog won’t in isolation solve degalation limitations working with large lists and libraries (2000+) in Power Apps, it can provide useful insight to users prior to calling a function to load large data sets into a collection commonly implemented as a workaround such that the users will have an idea how long that action will take to complete. For example if the list/library has say 18500 items in total and this flow surfaces that infomation, an app maker can display a progress bar reliably updating as each batch of 500-2000 items is added into the collection. The flow as detailed in this blog should take no more than a few seconds to run.

Solution

Overview – SharePoint_Get_LibraryList_Items_Count Power Automate flow

Step 1 – Create a new Instant Cloud Flow with a PowerApps Trigger

Step 2 – Initialize variable – Library List Url

Add a step to the flow "Initialize variable"
Rename the step to "Library List Url"

Specify "Library List Url" for the variable name

Set the variable value to "Ask in Power Apps" from the Dynamic content popup window

Step 3 – Initialize variable – Tenant Url

Add a step to the flow "Initialize variable"
Rename the step to "Tenant Url"

Specify "Tenant Url" for the variable name

Set the variable value to the following expression:
concat( 'https://', uriHost(variables('Library List Url')) )

Step 4 – Initialize variable – Site Relative Path

Add a step to the flow "Initialize variable"
Rename the step to "Site Relative Path"

Specify "Site Relative Path" for the variable name

Set the variable value to the following expression:
replace( toLower( replace( substring( variables('Library List Url'), 0, lastIndexOf( variables('Library List Url'), '/' ) ), variables('Tenant Url'), '' ) ), '/lists', '' )

When querying a SharePoint list '/Lists' is removed from this variable otherwise it will fail in subsequent steps. 

Step 5 – Initialize variable – Total Items Count

Add a step to the flow "Initialize variable"
Rename the step to "Total Items Count"

Specify "Total Items Count" for the variable name

Step 6 – Send an HTTP Request to SharePoint – Get Site Libraries and Lists

In step 8 of the flow where we use the SharePoint REST API GetByTitle endpoint that enables us to surface the total number of items in a SharePoint library or list we need to provide the displayName in the request and not the actual name of the library or list that appears in the full web url used to navigate to the library or list in SharePoint.

In this step in the flow we query the Graph REST API to retrieve a collection of all libraries and list in the site including system libraries and lists (such as Site Assets) along with the displayName metadata values for each item.
Ref: https://docs.microsoft.com/en-us/graph/api/list-list?view=graph-rest-1.0&tabs=http

There is no way to specify a filter in this request to limit the results returned to the specific library or list required. Thus in the next step (7) a Filter array data operation action is used as a workaround.

Add a step to the flow "Send an HTTP Request to SharePoint"
Rename the step to "Get Site Libraries and Lists"

For the Site Address select "Enter custom value" & then from the Dynamic content popup:
select the "Tenant Url" variable

For Method select "Get"

For Uri enter the following expression in the Dynamic content / Expression popup pane:
concat( '_api/v2.0/sites/', uriHost(variables('Library List Url')), ':', variables('Site Relative Path'), ':/lists?$select=name,displayName,webUrl,system' )

Add a Header property specifying Accept for the Key and application/json for the Value

Step 7 – Filter array – Filter Library List Being Queried

Add a step to the flow "Filter array"
Rename the step to "Filter Library List Being Queried"

For From enter the following expression in the Dynamic content / Expression popup pane: 
array( body('Get_Site_Libraries_and_Lists')['value'] )

For the field to filter (Choose a value) enter the following expression:
item()?['webUrl']
Leave the condition as "is equal to"
For the value to compare to select the "Library List Url" variable from the Dynamic content popup pane

Step 8 – Send an HTTP Request to SharePoint – Get Library List Total Items Count

In this step we query the legacy SharePoint REST API to get total count of items in a given library or list.
Ref: https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/working-with-lists-and-list-items-with-rest

I was unable to find any way of accomplishing the same desired outcome alternatively leveraging the SharePoint REST API v2 / Graph REST API v1 at the time of publishing this blog. Understandably without this functionality implemented in the current SharePoint & Graph API release it cannot in turn be implemented natively in the SharePoint connector for Power Platform.

Add a step to the flow "Send an HTTP Request to SharePoint"
Rename the step to "Get Library List Total Items Count"

For the Site Address select "Enter custom value" & then from the Dynamic content popup:
select the "Tenant Url" variable followed by the "Site Relative Path" variable

For Method select "Get"

For Uri enter the following expression:
concat( '_api/web/lists/GetbyTitle(''', first( body('Filter_Library_List_Being_Queried') )?['displayName'], ''')/ItemCount' )

Add a Header property specifying Accept for the Key and application/json for the Value

Step 9 – Set variable – Total Items Count

Add a step to the flow "Set variable"
Rename the step to "Set variable - Total Items Count"

Select the "Total Items Count" variable

Enter the following expression for the value:
body('Get_Library_List_Total_Items_Count')['value']

Step 10 – Respond to a PowerApp or flow

Add a step to the flow "Respond to a PowerApp or flow"

Add an output property of type Text
NB: When I used type Number for this output property, the value would not be returned to the Power App. When I changed it to type Text for some reason it then worked.

Enter a name for the property, e.g. "TotalItemsCount"
For the value select the "Total Items Count" variable from the Dynamic content popup pane

Testing

Having created the flow per the steps detailed above save the new flow and execute a Test run in Power Automate.

You should be prompted to Sign in to authenticate your SharePoint connection when the flow runs the first time. Click Sign in to continue. You should then be prompted to enter a Url to a SharePoint Library or List. This is the same Url you would enter to navigate to the library or list in SharePoint, for example:

https://contoso.sharepoint.com/sites/Development/poc/Shared%20Documents
https://contoso.sharepoint.com/sites/Development/Bookings/Lists/POC%20Items%20Count

Enter a Url to a SharePoint library or list in your tenant and click the Run flow button to instantiate the flow. All boding well the flow should run successfully and the outputs of the final step (Respond to a PowerApp or flow) should look as follows (noting the SharePoint library I tested against has nearly 15k items, well above any typical list view threshold):

The total items count for a given library or list queried using this flow should match the total number of items stated for the library or list in the Site Contents page on the corrosponding SharePoint site.

Consuming in a Power App

Having implemented the flow in Power Automate you can henceforth add the flow to any Power App where you would like to query the exact number of items in a SharePoint library or list, passing the full Url to the library or list as a parameter for the flow.

Where you choose to run the flow from within your app is entirely up to you. The following are a few sample code snippets that you could potentially use:

App OnStart:
Set( v_sharepoint_librarylist_count, SharePoint_Get_LibraryList_Items_Count.Run( "https://contoso.sharepoint.com/sites/Development/Shared%20Documents" ) )

Get_ItemsCount_Button OnSelect:
UpdateContext({ v_sharepoint_librarylist_count: SharePoint_Get_LibraryList_Items_Count.Run( "https://contoso.sharepoint.com/sites/Development/Bookings/Lists/POC%20Items%20Count" ) })
ItemsCount_Label Text:
v_sharepoint_librarylist_count.totalitemscount

Get_ItemsCount_Button OnSelect:
UpdateContext({ v_sharepoint_librarylist_count: SharePoint_Get_LibraryList_Items_Count.Run( "https://contoso.sharepoint.com/sites/Development/Shared%20Documents" ).totalitemscount })
ItemsCount_Label Text:
v_sharepoint_librarylist_count

Refresh_Button OnSelect:
Refresh( Documents );
UpdateContext({ v_sharepoint_librarylist_count: SharePoint_Get_LibraryList_Items_Count.Run( "https://contoso.sharepoint.com/sites/Development/Shared%20Documents" ).totalitemscount })

Wrapping Up & Download Link

Sharing is caring so by all means download the sample Power Automate flow as is detailed in this blog.

GitHub – Download SharePoint_Get_LibraryList_Items_Count Power Automate flow

Import the downloaded package into your desired Power Automate environment. Once imported Turn on the flow in Power Automate to enable it. Thereafter do a Test run of the flow in Power Automate to authenticate the SharePoint connection in your own SharePoint tenant and validate it runs successfully (as it hopefully should).

Should you find it of use please share your comments and feedback below ?.