By 647 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.
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.”
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.
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
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.
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
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
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
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:
{
"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.
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:
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:
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:
Step 2:
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)
- You may visit the Power Automate articles hub
- Take care of what’s important. Automate the rest
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
