Get SharePoint search results using the Rest API in PowerAutomate

In this blog, we will be learning about how to get SharePoint search results using the Rest API through PowerAutomate flow. The search results will be exported into a CSV file and saved into a SharePoint library. 

Create Flow

1). Sign in to Power Automate

2). Open the flow, click on the Create option, and select "Instant Cloud Flow".

Enter Flow name.
Choose the flow trigger as "Manually trigger a flow".
Click on the Create button.

3). Add an action "Send an HTTP request to SharePoint".

Site Address	https://yoursite.sharepoint.com/sites/VideoHub
Method	GET
Uri	_api/search/query ?querytext='Path:https://yoursite.sharepoint.com/sites/VideoHub*' &trimduplicates=false&rowlimit=500 &selectproperties='Title,RefinableString00,RefinableInt00, IsDiscontinued,RefinableString03' &refiners='contentclass,SecondaryFileExtension,ListID' &refinementfilters='and( contentclass:equals("STS_ListItem_DocumentLibrary"), SecondaryFileExtension:or("mp4","mkv"), ListID:equals("f7fb8ca6-aaaa-bbbb-cccc-7816d24bdf42") )' &sortlist='Created:descending'
Headers	{ "accept": "application/json; odata=nometadata" }

4). Add an action "Parse JSON" and enter output of previous step in Content.
In the Schema field use the below JSON content.

Parse JSON - Schema

{ "$schema":"http://json-schema.org/draft-07/schema#", "title":"Generated schema for Root", "type":"object", "properties": { "ElapsedTime": { "type":"number" }, "PrimaryQueryResult": { "type":"object", "properties": { "CustomResults": { "type":"array", "items": {} }, "QueryId": { "type":"string" }, "QueryRuleId": { "type":"string" }, "RefinementResults": {}, "RelevantResults": { "type":"object", "properties": { "GroupTemplateId": {}, "ItemTemplateId": {}, "Properties": { "type":"array", "items": { "type":"object", "properties": { "Key": { "type":"string" }, "Value": { "type":"string" }, "ValueType": { "type":"string" } }, "required": [ "Key", "Value", "ValueType" ] } }, "ResultTitle": {}, "ResultTitleUrl": {}, "RowCount": { "type":"number" }, "Table": { "type":"object", "properties": { "Rows": { "type":"array", "items": { "type":"object", "properties": { "Cells": { "type":"array", "items": { "type":"object", "properties": { "Key": { "type":"string" }, "Value": { "type": [ "string", "null" ] }, "ValueType": { "type": [ "string", "null" ] } }, "required": [ "Key", "ValueType" ] } } }, "required": [ "Cells" ] } } }, "required": [ "Rows" ] }, "TotalRows": { "type":"number" }, "TotalRowsIncludingDuplicates": { "type":"number" } }, "required": [ "GroupTemplateId", "ItemTemplateId", "Properties", "ResultTitle", "ResultTitleUrl", "RowCount", "Table", "TotalRows", "TotalRowsIncludingDuplicates" ] }, "SpecialTermResults": {} }, "required": [ "CustomResults", "QueryId", "QueryRuleId", "RefinementResults", "RelevantResults", "SpecialTermResults" ] }, "Properties": { "type":"array", "items": { "type":"object", "properties": { "Key": { "type":"string" }, "Value": { "type":"string" }, "ValueType": { "type":"string" } }, "required": [ "Key", "Value", "ValueType" ] } }, "SecondaryQueryResults": { "type":"array", "items": {} }, "TriggeredRules": { "type":"array", "items": {} } }, "required": [ "ElapsedTime", "PrimaryQueryResult", "Properties", "SecondaryQueryResults", "TriggeredRules" ] }

Add additional type null - this will take care of the null values coming in search results.

5). Initialize variables one of type Object and Array.

{ "Title":"", "BodyType":"", "LaunchYear":"", "IsDiscontinued": false, "VideoFormat":"" }

6). Add "Apply to each" loop for rows and enter step 4 output (or enter below) as input value.

body('ParseJSONtoGetRows')['PrimaryQueryResult']['RelevantResults']['Table']['Rows']

Add another nested "Apply to each" loop for cells and enter current cells value (or enter below) as input value.

items('Apply_to_each-Rows')['Cells']

7). Add a "Switch" condition and enter current cell key value (or enter below) as On value.

items('Apply_to_each-Cells')?['Key']

8). Add a case statement to fetch field value from the search property.

Enter search managed property Title in equals field.

Add an action "Compose" and add below value in the inputs parameter.

setProperty(variables('varResponseJson'),'Title',items('Apply_to_each-Cells')?['Value'])

Here, setProperty(object, property, value) function is used; it returns an object with a property set to the provided value.

Add an action "Set variable" (defined in step 5) object variable and enter previous step output as input value.

9). Add more case statements; we are fetching five field values from the search properties. Therefore, five case statements are needed.
Repeat the step 8 for all the required search managed property.

Note: Create search managed properties for your custom fields and use them.

10). Append the array variable (defined in step 5) and enter object variable output as value after closing on inner "Apply to each" loop.

11). Add an action "Create CSV table" and enter array variable in From and Automatic in Columns.

12). Add an action "Create file" to create CSV file to export the search results.

Enter the Site Address and Folder Path as required.

File Name as concat('BlogData_', formatDateTime(utcNow(), 'yyyyMMdd_HHmmss'), '.csv')

File Content as output from previous step 12.

I hope this will help all of you! 🧲
Feel free to provide feedback.

🚀 "Happy Coding" 🚀

SharePoint REST API $expand query option.

A few months ago, I got a task that only logged-in users can edit list items created by themselves, which requires the Created By (Author) and Modified By (Editor) field values of that list item.

So, I will share my experience in resolving it. The Created By and Modified By values doesn't appear when a GET request is done; only AuthorId and EditorId are returned, as given below.

The Created By (Author) and Modified By (Editor) fields are of the People type. If you want the Author and Editor Title values, then you have to use the REST API $expand query option. The $expand query option is used to lookup column values, or you can think of it as a JOIN on two lists like in SQL for getting data. The SharePoint REST API supports OData query options. In this scenario, the lookup column points to the User Information List to get the person's details.

The code snippet below defines the proper usage of the $expand query option in the REST API. The code expands the Author and Editor entity fields, and in the $select column name, a forward slash (/) is used.

The success response object is logged using the Console API.

The below image shows all the values returned.

🚀 "Happy Coding" 🚀

SharePoint activates the feature using the REST API

In this blog, I will show you how to activate SharePoint feature using REST API.

The REST API provides a simple and easy way to activate features at the site and web level.
I have created simple JavaScript code to activate the feature.

function activateFeature() {
    $.ajax({
        url: _spPageContextInfo.siteAbsoluteUrl + "/_api/web/features/add('7e2e2482-9007-4db3-8a7f-9bccd986ec4e')",
        type: "POST",
        headers: {
            "Accept": "application/json;odata=verbose", //return data format.
           "X-RequestDigest": $("#__REQUESTDIGEST").val()
        },
        success: function (data) {
// perform operation on the response object.
        },
        error: function (err) {
            alert("Error: " + JSON.stringify(err));
        }
    });
}


Change the URI, and the feature will be activated based on the endpoint.

1. /web - then feature will be activated at the web level.
2. /site - then feature will be activated at the site collection level.

Now pass the feature GUID into the add method. You can get the GUID value by using the PowerShell command or by "SharePoint Manager 2013".

🚀 "Happy Coding" 🚀

SharePoint 2013 REST API Get requests from the designer workflow

SharePoint 2013 provides a completely new workflow engine, which has a new "Call HTTP web service" action that enables us to communicate with the web service from our workflow.

In this blog post, I will show you how you can use this new action method for a REST API GET request to a SharePoint list.

I have created a list "WebServiceList" and inserted dummy data into it as shown below.

Open SharePoint Designer 2013 and create a workflow attached to the list created above. Use the platform type as SharePoint 2013 workflow; otherwise Call HTTP web service method will not be available.

In our normal CSOM(Client Side Object Model), we call the REST API to get data back in JSON format. You need these headers as:

 Accept: application/json;odata=verbose 

 Content-Type: application/json;odata=verbose 

In the workflow add the "Build the Dictionary" action of above headers as:

Click on the three dots to add items to the dictionary.

Now, add the HTTP call web service method with type GET as given below:

Test your REST call in the browser as given below:

Add the header dictionary variable created just above to the web service and set the request type to "GET" as:

Then click OK.

Add the GET action, and now we will get the result from the response content variable as

Now get the CountItems of the Output variable (allTitles) from the get action.

Then take one variable and set it to "0" and insert Loop (Loop n times). Inside the loop again, add the GET action to get all titles from the variable JsonResult and log it. Add the Calculate action to add "1" to the loop index variable and then again set its value. Now end the workflow.

Complete workflow is given below

See the Tasks and Workflow History list details below; you will get the data fetched from the source list is logged in the history list.

Hope this will help you!

🚀"Happy Coding"🚀