Power Automate flow get items filter query by content type in SharePoint online document library

Power Automate flow get items filter query by content type in SharePoint online document library – 3 ways

No comments

 361 total views,  7 views today

In this “Power Automate flow get items filter query by content type” article, we will learn how to get all files from a SharePoint Online document library filtered by content type and custom metadata columns.

Table of Contents

Power Automate flow get items filter query by content type in SharePoint online document library

This Power Automate flow retrieves items from the SharePoint online document library that have been filtered by content type.

For this demo, we have a document library named “GSD Test Document Library” that has the Content Type “Custom PO” and “DocumentStatus” choice type columns; the choice values are “Draft, In Progress, and Approved.”

Content type filter in SharePoint Online document library
Content type filter in SharePoint Online document library

In this demo, we will retrieve all files from the library where “DocumentStatus” is empty and the content type is “Custom PO”.

Flow: Power Automate flow get items filter query by content type and choice column

Below is the complete flow that will get all files from the SharePoint Online document library based on the content type and metadata filter.

Power Automate flow get items filter query by content type and choice column - Part 1

Power Automate flow get items filter query by content type and choice column - Part 2
Power Automate flow get items filter query by content type and choice column

Flow Explanation: Power Automate flow get items filter query by content type and choice column

In this section, we will describe each step of the flow:

Variable declaration section

Create the following variables:

  • varStrContentTypeName: A type of string (this will hold the value of the content type)
  • varStrPOStatus: A type of string (this will hold the value of the PO status column).
  • varStrFilePath: A type of string (this will hold the value of the file path).

Variable declaration section in Power Automate

Variable declaration section in Power Automate

Send an HTTP request to SharePoint to filter items with content type and choice column

This is the main part of the flow; here we get the information for all files based on the query.

Add a “Send an HTTP request to SharePoint” action.

Send an HTTP request to SharePoint to filter items with content type and choice column
Send an HTTP request to SharePoint to filter items with content type and choice column

In the “Send an HTTP request to SharePoint” action, pass the below parameters:

  • Site Address: Your site URL
  • Method: GET
  • URI: In the URI pass the below:

_api/lists/getbytitle('GSD Test Document Library')/items?$select=EncodedAbsUrl,FileLeafRef,FieldValuesAsText/FileRef,File/ServerRelativeUrl,ContentType/Name,POStatus&$filter=(ContentType eq 'Custom PO' and POStatus eq null)&$top=5000&$expand=ContentType&$expand=File&$expand=FieldValuesAsText

 

Note:

  • In the above query, replace the document library with your document library, and replace the content type with your content type.
  • This query will retrieve all files from a document library based on the custom content type “Custom PO” and where the “POStatus” is null or empty.
  • This query ensures that it doesn’t throw the items threshold limit error, as we have added top=5000 in the query.
  • In the above query, “EncodedAbsUrl, FileLeafRef, FieldValuesAsText/FileRef,File/ServerRelativeUrl” are related to file path and name.

 

Find length of the query results from “Send an HTTP request to SharePoint” to filter query Get items

Add a compose action to find the length of the “Send an HTTP request to SharePoint” to filter the items query result.

Add the below value in the “inputs” box:


 

length(outputs('Send_an_HTTP_request_to_SharePoint')?['body'].d.results)

 

Compose action to find the length of the Send an HTTP request to SharePoint to filter items query result Compose action to find the length of the Send an HTTP request to SharePoint to filter items query result

For each loop in Power Automate in “Send an HTTP request to SharePoint” filter query Get items results

Add an “Apply to each” control to loop through the “Send an HTTP request to SharePoint” filter query Get items results.

In the “Select an output from previous steps”, enter the below:


body('Send_an_HTTP_request_to_SharePoint').d.results

For each loop in Power Automate in Send an HTTP request to SharePoint filter Get items results - Part 1

For each loop in Power Automate in Send an HTTP request to SharePoint filter Get items results - Part 2
For each loop in Power Automate in Send an HTTP request to SharePoint filter Get items results

Set variable inside for each loop of Send an HTTP request to SharePoint filter query Get items results

Enter the following to set the variable content type:


items('Apply_to_each')?['ContentType/Name']

Enter the following for the variable PO Status:


items('Apply_to_each')?['POStatus']

For set variable file path enter the below:


items('Apply_to_each')?['EncodedAbsUrl']

The below sections are optional; they are to format query results in an HTML table and the output to email. Here we are just adding the first step in this.

Select compose operation to map the necessary columns from the query result – Send an HTTP request to SharePoint filter Get query items results

Add a “Select” compose data operation action and enter the below in the “From” field:


body('Send_an_HTTP_request_to_SharePoint').d.results

Select compose operation to map the necessary columns from the query result
Select compose operation to map the necessary columns from the query result

To map the file path, add the following to the right-side box:


item()?['EncodedAbsUrl']

To map the content type, add the below at the right side box:


item()?['ContentType/Name']

For details on how to create an HTML table in Power Automate and send email, refer to our previous article, Using Power Automate create HTML table with Dynamic Hyperlink.

Demo: Send an HTTP request to SharePoint – Body after execution

Once we execute this flow, we can see the following inside the body of the Send an HTTP request to SharePoint Get query:

Demo - Send an HTTP request to SharePoint - Body after execution
Demo – Send an HTTP request to SharePoint – Body after execution

 

{

  "d": {

    "results": [

      {

        "__metadata": {

          "id": "a6d4dab5-4d0d-445d-84a7-971998e5f48d",

          "uri": "https://globalsharepoint2020.sharepoint.com/sites/GSDRnD/_api/Web/Lists(guid'35f6d8f7-3bc6-4751-9ebf-0cebe9817d57')/Items(19)",

          "etag": "\"4\"",

          "type": "SP.Data.GSD_x0020_Test_x0020_Document_x0020_LibraryItem"

        },

        "ContentType": {

          "__metadata": {

            "id": "https://globalsharepoint2020.sharepoint.com/sites/GSDRnD/_api/Web/Lists(guid'35f6d8f7-3bc6-4751-9ebf-0cebe9817d57')/Items(19)/ContentType",

            "uri": "https://globalsharepoint2020.sharepoint.com/sites/GSDRnD/_api/Web/Lists(guid'35f6d8f7-3bc6-4751-9ebf-0cebe9817d57')/Items(19)/ContentType",

            "type": "SP.ContentType"

          },

          "Name": "Custom PO"

        },

        "FieldValuesAsText": {

          "__metadata": {

            "id": "https://globalsharepoint2020.sharepoint.com/sites/GSDRnD/_api/Web/Lists(guid'35f6d8f7-3bc6-4751-9ebf-0cebe9817d57')/Items(19)/FieldValuesAsText",

            "uri": "https://globalsharepoint2020.sharepoint.com/sites/GSDRnD/_api/Web/Lists(guid'35f6d8f7-3bc6-4751-9ebf-0cebe9817d57')/Items(19)/FieldValuesAsText",

            "type": "SP.FieldStringValues"

          },

          "FileRef": "/sites/GSDRnD/GSD Test Document Library/DS0001/Sample File 3.docx"

        },

        "File": {

          "__metadata": {

            "id": "https://globalsharepoint2020.sharepoint.com/sites/GSDRnD/_api/Web/Lists(guid'35f6d8f7-3bc6-4751-9ebf-0cebe9817d57')/Items(19)/File",

            "uri": "https://globalsharepoint2020.sharepoint.com/sites/GSDRnD/_api/Web/Lists(guid'35f6d8f7-3bc6-4751-9ebf-0cebe9817d57')/Items(19)/File",

            "type": "SP.File"

          },

          "ServerRelativeUrl": "/sites/GSDRnD/GSD Test Document Library/DS0001/Sample File 3.docx"

        },

        "FileLeafRef": "Sample File 3.docx",

        "POStatus": null,

        "EncodedAbsUrl": "https://globalsharepoint2020.sharepoint.com/sites/GSDRnD/GSD%20Test%20Document%20Library/DS0001/Sample%20File%203.docx"

      },

      {

        "__metadata": {

          "id": "ca1e5c90-c56a-445f-aae3-7632105bc61f",

          "uri": "https://globalsharepoint2020.sharepoint.com/sites/GSDRnD/_api/Web/Lists(guid'35f6d8f7-3bc6-4751-9ebf-0cebe9817d57')/Items(20)",

          "etag": "\"3\"",

          "type": "SP.Data.GSD_x0020_Test_x0020_Document_x0020_LibraryItem"

        },

        "ContentType": {

          "__metadata": {

            "id": "https://globalsharepoint2020.sharepoint.com/sites/GSDRnD/_api/Web/Lists(guid'35f6d8f7-3bc6-4751-9ebf-0cebe9817d57')/Items(20)/ContentType",

            "uri": "https://globalsharepoint2020.sharepoint.com/sites/GSDRnD/_api/Web/Lists(guid'35f6d8f7-3bc6-4751-9ebf-0cebe9817d57')/Items(20)/ContentType",

            "type": "SP.ContentType"

          },

          "Name": "Custom PO"

        },

        "FieldValuesAsText": {

          "__metadata": {

            "id": "https://globalsharepoint2020.sharepoint.com/sites/GSDRnD/_api/Web/Lists(guid'35f6d8f7-3bc6-4751-9ebf-0cebe9817d57')/Items(20)/FieldValuesAsText",

            "uri": "https://globalsharepoint2020.sharepoint.com/sites/GSDRnD/_api/Web/Lists(guid'35f6d8f7-3bc6-4751-9ebf-0cebe9817d57')/Items(20)/FieldValuesAsText",

            "type": "SP.FieldStringValues"

          },

          "FileRef": "/sites/GSDRnD/GSD Test Document Library/DS0001/Sample File 1.csv"

        },

        "File": {

          "__metadata": {

            "id": "https://globalsharepoint2020.sharepoint.com/sites/GSDRnD/_api/Web/Lists(guid'35f6d8f7-3bc6-4751-9ebf-0cebe9817d57')/Items(20)/File",

            "uri": "https://globalsharepoint2020.sharepoint.com/sites/GSDRnD/_api/Web/Lists(guid'35f6d8f7-3bc6-4751-9ebf-0cebe9817d57')/Items(20)/File",

            "type": "SP.File"

          },

          "ServerRelativeUrl": "/sites/GSDRnD/GSD Test Document Library/DS0001/Sample File 1.csv"

        },

        "FileLeafRef": "Sample File 1.csv",

        "POStatus": null,

        "EncodedAbsUrl": "https://globalsharepoint2020.sharepoint.com/sites/GSDRnD/GSD%20Test%20Document%20Library/DS0001/Sample%20File%201.csv"

      },

      {

        "__metadata": {

          "id": "f4061ed1-1900-40c2-8b10-6724741a749b",

          "uri": "https://globalsharepoint2020.sharepoint.com/sites/GSDRnD/_api/Web/Lists(guid'35f6d8f7-3bc6-4751-9ebf-0cebe9817d57')/Items(21)",

          "etag": "\"3\"",

          "type": "SP.Data.GSD_x0020_Test_x0020_Document_x0020_LibraryItem"

        },

        "ContentType": {

          "__metadata": {

            "id": "https://globalsharepoint2020.sharepoint.com/sites/GSDRnD/_api/Web/Lists(guid'35f6d8f7-3bc6-4751-9ebf-0cebe9817d57')/Items(21)/ContentType",

            "uri": "https://globalsharepoint2020.sharepoint.com/sites/GSDRnD/_api/Web/Lists(guid'35f6d8f7-3bc6-4751-9ebf-0cebe9817d57')/Items(21)/ContentType",

            "type": "SP.ContentType"

          },

          "Name": "Custom PO"

        },

        "FieldValuesAsText": {

          "__metadata": {

            "id": "https://globalsharepoint2020.sharepoint.com/sites/GSDRnD/_api/Web/Lists(guid'35f6d8f7-3bc6-4751-9ebf-0cebe9817d57')/Items(21)/FieldValuesAsText",

            "uri": "https://globalsharepoint2020.sharepoint.com/sites/GSDRnD/_api/Web/Lists(guid'35f6d8f7-3bc6-4751-9ebf-0cebe9817d57')/Items(21)/FieldValuesAsText",

            "type": "SP.FieldStringValues"

          },

          "FileRef": "/sites/GSDRnD/GSD Test Document Library/DS0001/Sample File 2.csv"

        },

        "File": {

          "__metadata": {

            "id": "https://globalsharepoint2020.sharepoint.com/sites/GSDRnD/_api/Web/Lists(guid'35f6d8f7-3bc6-4751-9ebf-0cebe9817d57')/Items(21)/File",

            "uri": "https://globalsharepoint2020.sharepoint.com/sites/GSDRnD/_api/Web/Lists(guid'35f6d8f7-3bc6-4751-9ebf-0cebe9817d57')/Items(21)/File",

            "type": "SP.File"

          },

          "ServerRelativeUrl": "/sites/GSDRnD/GSD Test Document Library/DS0001/Sample File 2.csv"

        },

        "FileLeafRef": "Sample File 2.csv",

        "POStatus": null,

        "EncodedAbsUrl": "https://globalsharepoint2020.sharepoint.com/sites/GSDRnD/GSD%20Test%20Document%20Library/DS0001/Sample%20File%202.csv"

      }

    ]

  }

}

 

 

Find query result length from Send an HTTP request to SharePoint Get Items query

We can see that the above query returns 3 items (3 rows) from the Send an HTTP request to SharePoint Get Items query.

Find query result length from Send an HTTP request to SharePoint Get Items query
Find query result length from Send an HTTP request to SharePoint Get Items query

Apply to each loop in Power Automate Send an HTTP request to SharePoint

Apply to each loop in Power Automate Send an HTTP request to SharePoint
Apply to each loop in Power Automate Send an HTTP request to SharePoint

Other SharePoint REST API – Good to know in Power Automate

The following are the most important SharePoint File-related REST APIs that we can use in a Power Automate flow:

Example 1: Filter List Items by Content Type Name using SharePoint REST API GET list Items

Syntax:


YourSiteURL/_api/lists/getbytitle('Your List/Document library Title')/items?$select=ContentTypeId,ContentType/Name&$filter=ContentType eq 'Your Content Type Name' &$expand=ContentType

Example:


https://globalsharepoint2020.sharepoint.com/sites/GSDRnD/_api/lists/getbytitle('GSD Test Document Library')/items?$select=ContentTypeId,ContentType/Name&$filter=ContentType eq 'Custom PO' &$expand=ContentType

Flow execution result:

Filter List Items by Content Type Name using SharePoint REST API Flow execution result
Filter List Items by Content Type Name using SharePoint REST API Flow execution result

 

Note:

  • The above query will return the mentioned content type wherever it is used in the document library. In our demo, the said content type is used in three documents, so we can see three times it appears in the query execution result.

Example 2:  Filter multiple values in SharePoint REST API GET list Items

To filter multiple values in the SharePoint REST API from Power Automate, you can use the below example:

Checking to see if the “ContactNumber” equals 1234567890 or 0987654321 in this example.


/_api/web/lists/getbytitle('Your List Title')/items?$select=Title,ContactNumber&$filter=((ContactNumber eq 1234567890) or (ContactNumber eq 0987654321))

The above query can be written in the below way as well:


/_api/web/lists/getbytitle('Your List Title')/items?$select=Title,ContactNumber&$filter=ContactNumber eq 1234567890 or ContactNumber eq 0987654321

 

Example 3: Get document URL from document library SharePoint REST API Get list Items

To get the document’s full URL from the SharePoint Online Document Library, use the below query:

Syntax:


/_api/web/lists/getbytitle('Your List/Library Title')/items?$select=EncodedAbsUrl

Example:

Get document URL from document library SharePoint REST API
Get document URL from document library SharePoint REST API

Example 4 : Get all file URLs from a SharePoint Online Document library with a single REST API Query

Use the below query to get all file URLs from a SharePoint Online document library with a single REST API query:

Approach 1: Get file URLs by the File object


/_api/Web/Lists/getByTitle('Your List/Library Title')/items?$select=File/ServerRelativeUrl&$expand=File

Approach 1: Get file URLs by the FileRef property


/_api/Web/Lists/getByTitle('Your List/Library Title')/items?$select=FieldValuesAsText/FileRef&$expand=FieldValuesAsText

 

SharePoint Online View: Filter documents by content type in document library view (SharePoint online document library filter only documents)

Using the below document library view filter, we can filter all documents created using the certain content type and metadata.

Step 1:

Show items only when the following is true:

Filter document library in SharePoint Online by content type
Filter document library in SharePoint Online by content type

Step 2:

Show all items without folders in SharePoint document library view
Show all items without folders in SharePoint document library view

Summary: Power Automate flow get items filter query by content type

Thus, in this article, we have learned the following with respect to retrieving files from the SharePoint Online Document Library based on certain content types and metadata:

  • How to get all files from a SharePoint Online document library created by a certain content type using Power Automate
  • How to use the Send HTTP Request to SharePoint action to perform a GET operation in Power Automate
  • How to get file URLs from a SharePoint document library using the REST API and Power Automate
  • How to work with file objects in SharePoint Online using the REST API and Power Automate
  • How to use Power Automate to query items by filtering the content type
  • How to use the foreach loop in Power Automate to send an HTTP request to SharePoint query results
  • How to get only files from the SharePoint Online document library using the customized view
  • How to filter the get items query in Power Automate with the choice column

 

See Also: Power Automate Articles Hub (with real-time example)

If you like our articles, please appreciate our efforts by liking and sharing them with your friends and colleagues. Please join the mailing list to get our articles in your inbox as and when we publish them.

Happy automating and learning!

 

About Post Author

Do you have a better solution or question on this topic? Please leave a comment