Power Apps leveraging the SharePoint Search API

In this blog post  I will guide in building a power app that trigger the SharePoint Search APi through Power Automate.

So first let's have a look on the Power App

Basically we have one screen, on that screen an input box a button and a gallery.

The button triggers a power automate flow with the content of the search box as parameter

an put the results in a collection called _apiResults

Then the Gallery is just binded to the _apiResults collection.

Before we go to the details of the Gallery let's see the SPSearchAPi flow that the Search button triggers

The flow is triggered by Power Apps, call the Sharepoint Search APi with a HTTP request, parse the results and then send a response.

Let's look into the HTTP request

Basically here we are calling the search api with a search termbut we are specifying with IsDocument:true and FileExtension<>apsx that we want only documents that are not aspx page files.

The we are selecting only the Title, Path, Version and ParentLink of every documents filtered by last modified date.

Then with the Parse Json we will only be getting the part of the response that matters to us with this schema

{
    "type": "object",
    "properties": {
        "odata.metadata": {
            "type": "string"
        },
        "ElapsedTime": {
            "type": "integer"
        },
        "PrimaryQueryResult": {
            "type": "object",
            "properties": {
                "CustomResults": {
                    "type": "array"
                },
                "QueryId": {
                    "type": "string"
                },
                "QueryRuleId": {
                    "type": "string"
                },
                "RefinementResults": {},
                "RelevantResults": {
                    "type": "object",
                    "properties": {
                        "GroupTemplateId": {},
                        "ItemTemplateId": {},
                        "ResultTitle": {},
                        "ResultTitleUrl": {},
                        "RowCount": {
                            "type": "integer"
                        },
                        "Table": {
                            "type": "object",
                            "properties": {
                                "Rows": {
                                    "type": "array",
                                    "items": {
                                        "type": "object",
                                        "properties": {
                                            "Cells": {
                                                "type": "array",
                                                "items": {
                                                    "type": "object",
                                                    "properties": {
                                                        "Key": {
                                                            "type": [
                                                                "string",
                                                                "null"
                                                            ]
                                                        },
                                                        "Value": {
                                                            "type": [
                                                                "string",
                                                                "null"
                                                            ]
                                                        },
                                                        "ValueType": {
                                                            "type": [
                                                                "string",
                                                                "null"
                                                            ]
                                                        }
                                                    },
                                                    "required": [
                                                        "Key",
                                                        "Value",
                                                        "ValueType"
                                                    ]
                                                }
                                            }
                                        },
                                        "required": [
                                            "Cells"
                                        ]
                                    }
                                }
                            }
                        },
                        "TotalRows": {
                            "type": "integer"
                        },
                        "TotalRowsIncludingDuplicates": {
                            "type": "integer"
                        }
                    }
                },
                "SpecialTermResults": {}
            }
        }
    }
}

As you can see the api reply in a way that I like to call it vertically, meaning that it will reply for every document a Row object with an array of Cells object that contains the name and the value of the properties selected.

To consume it like that in Gallery in Power App it is not convenient and believe me I tried it :)

So we have to pivot in a way the structure of the results and this is why the Select composit function comes handy.

With this composite function we are able to select the items we want 

We will map every index of Cells array to a property, here the Title is mapped to item()?['Cells'][2]['Value'] , Path is mapped to item()?['Cells'][3]['Value'] etc... We can use this technique because the search api always returns the object in the same order.

And finally we reply with the output generated by the Select composite action

With this schema structure

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "Title": {
                "type": "string"
            },
            "Path": {
                "type": "string"
            },
            "UIVersionStringOWSTEXT": {
                "type": "string"
            },
            "ParentLink": {
                "type": "string"
            },
            "FileExtension": {
                "type": "string"
            },
            "LinkingUrl": {
                "type": "string"
            }
        },
        "required": [
            "Title",
            "Path",
            "UIVersionStringOWSTEXT"
        ]
    }
}

Now back to the Power App, the Gallery displays the Title, Version and document type. It has two icons as well, one for opening the document in the browser and one for opening the location of the document.

To open a document in the browser knowing its path just do a

Launch(ThisItem.Path&"?web=1")

 

The full code is available on my github at

https://github.com/alaabitar/powerapps/blob/master/PowerAppSearchSPAPI_20200512133953.zip

That is it ! Happy coding !

Stay safe

 

SharePoint Online PnPProvisionning include document library file contents and file Versions !

Last year I wrote an article about using the PnP Provisioning engine to add references to documents in the Template file generated.

A couple of days a friend on linked in asked me if it were possible to include also all the documents versions in the Template file generated. So though about it and came up with a solution.

Basically the idea is of course to get all the versions of the document then download them and inject their reference in the Template pnp provisioning file.

First you have to know that when you get a reference to a file with

$folder = Get-PnPFolder -RelativeUrl $folderUrl
For ($i = 0; $i -lt $total; $i++) {
        $file = $folder.Files[$i]

The $file.Versions will return an error as the collection has not being initialised. This is because the collection property has not been specified. So in order to load the .Version collection you have to specifically load it with

$total = $folder.Files.Count
$ctx = Get-PnPContext
	
For ($i = 0; $i -lt $total; $i++) {
        $file = $folder.Files[$i]
        
        $ctx.load($file.Versions)
        $ctx.ExecuteQuery()

Now we can iterate through the Version collection and get the Url property to download the file.
Again I am not sure why if I used the Get-PnpFile -Url $file.Url it was returning me an error 404 not found so I used the WebClient object to download the file with

$webClient = New-Object System.Net.WebClient 
$webClient.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userName, $sPassword)
$webClient.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f")

$webclient.DownloadFile($ServerFileLocation,$DownloadPath)

If we put it all together my ProcessFolder function becomes

function ProcessFolder($folderUrl, $destinationFolder) {
	Write-Output "Folder URL " $folderUrl  " destinationFolder " $destinationFolder
    $folder = Get-PnPFolder -RelativeUrl $folderUrl
    $tempfiles = Get-PnPProperty -ClientObject $folder -Property Files
   
    if (!(Test-Path -path $destinationfolder )) {
        $dest = New-Item $destinationfolder -type directory 
    }

    $total = $folder.Files.Count
	$ctx = Get-PnPContext
	
    For ($i = 0; $i -lt $total; $i++) {
        $file = $folder.Files[$i]
        
		$ctx.load($file.Versions)
        $ctx.ExecuteQuery()

		foreach($version in $file.Versions)
		{
			$filesplit = $file.Name.split(".") 
			$fullname = $filesplit[0] 
			$fileext = $filesplit[1] 
			$FullFileName = $fullname+"\"+$version.VersionLabel+"\"+$file.Name         

			$fileURL = $destination+"/"+$version.Url


			$DownloadPath = $FullFileName

			if (!(Test-Path ($destinationfolder + "\" + $fullname + "\" + $version.VersionLabel)))
			{
				New-Item ($destinationfolder + "\" + $fullname + "\" + $version.VersionLabel) -type directory -Force
			}

			HTTPDownloadFile "$fileURL" ($destinationfolder + "\" + $fullname + "\" + $version.VersionLabel + "\" + $file.Name)
			
			$versionSourceFolder =  "./" + $siteTitle + "/" + $folder.Name + "/" + $fullname + "/" + $version.VersionLabel + "/" + $file.Name
			Add-PnPFileToProvisioningTemplate -Path ($saveDir + "Template.xml") -Source $versionSourceFolder -Folder $folderUrl -FileLevel Published
		}
		
        Get-PnPFile -ServerRelativeUrl $file.ServerRelativeUrl -Path $destinationfolder -FileName $file.Name -AsFile -Force	

		Add-PnPFileToProvisioningTemplate -Path ($saveDir + "Template.xml") -Source ($destinationfolder + "\" + $file.Name) -Folder $folderUrl -FileLevel Published
		
    }
	
}

with the HTTPDownloadFile function

function HTTPDownloadFile($ServerFileLocation, $DownloadPath)
{
	$userName = "LOGIN_NAME"
	$password = "PASSWORD"

	#create secure password
	$sPassword = $password | ConvertTo-SecureString -AsPlainText -Force

	$webClient = New-Object System.Net.WebClient 
	$webClient.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userName, $sPassword)
	$webClient.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f")

    $webclient.DownloadFile($ServerFileLocation,$DownloadPath)
}

The complete script is available on github at https://github.com/alaabitar/provisioning/blob/master/scriptUpgraded.ps1

Fill out of a SharePoint Survey List from PowerApps

PowerApps and SharePoint are very well integrated and you can quickly build some great PowerApp forms binded to SharePoint list in just a couple of minute.

Unfortunatly there are some restrictions like for example you cannot bind a survey list type to PowerApps.

But hey it is your lucky day I will guide you through how you can build a PowerApps application that dynamically retrieves all the surveys in a SharePoint site and lets you respond to a survey.

The PowerApps application will have two screens, one to display all the available surveys and one to fill the survey.

In order to retrieve the available survey we will have a Flow named GetAvailableSurveys that is triggered from PowerApps that queries the SharePoint list API. For a fact a survey type list has a BaseTemplate equals to 102 so the REST API call is just

URL_OF_YOUR_SHAREPOINT_SITE/_api/web/lists?$filter=BaseTemplate eq 102

 

This is the Flow corresponding block

Now we just have to parse the json and reply to the PowerApp. We will use the Response action block in Flow

Here the body is set to the node "value" of the result from the GetAvailableSurveys block. To make things easier I set this JSON body schema

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "odata.type": {
                "type": "string"
            },
            "odata.id": {
                "type": "string"
            },
            "Id": {
                "type": "string"
            },
            "ListItemEntityTypeFullName": {
                "type": "string"
            },
            "StaticName": {
                "type": "string"
            },
            "Title": {
                "type": "string"
            }
        },
        "required": [
            "Id"
        ]
    }
}

Because I don't want to send all the properties back to my PowerApp.

Great we have a Flow that retrieves all the Survey of a particular SharePoint site. Now we have to create another Flow called Get Polls that takes in parameter the name of a survey and respond with all the questions of that survey. Again the Flow will query the SharePoint REST API to get all the questions. The URI is

_api/web/lists/getbytitle('NAME_OF_THE_SURVEY')/fields?$filter=(CanBeDeleted eq true)

So the corresponding Flow block is

Now again we just have to parse the json and reply to the PowerApp. We will use the Response action block in Flow

Here the body is set to the node "Results" of the result from the GetQuestionsFromSurvey block. To make things easier I set this JSON body schema

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "odata.type": {
                "type": "string"
            },
            "odata.id": {
                "type": "string"
            },
            "odata.editLink": {
                "type": "string"
            },
            "AutoIndexed": {
                "type": "boolean"
            },
            "CanBeDeleted": {
                "type": "boolean"
            },
            "ClientSideComponentId": {
                "type": "string"
            },
            "Description": {
                "type": "string"
            },
            "Direction": {
                "type": "string"
            },
            "EnforceUniqueValues": {
                "type": "boolean"
            },
            "EntityPropertyName": {
                "type": "string"
            },
            "Filterable": {
                "type": "boolean"
            },
            "FromBaseType": {
                "type": "boolean"
            },
            "Group": {
                "type": "string"
            },
            "Hidden": {
                "type": "boolean"
            },
            "Id": {
                "type": "string"
            },
            "Indexed": {
                "type": "boolean"
            },
            "InternalName": {
                "type": "string"
            },
            "JSLink": {
                "type": "string"
            },
            "PinnedToFiltersPane": {
                "type": "boolean"
            },
            "ReadOnlyField": {
                "type": "boolean"
            },
            "Required": {
                "type": "boolean"
            },
            "SchemaXml": {
                "type": "string"
            },
            "Scope": {
                "type": "string"
            },
            "Sealed": {
                "type": "boolean"
            },
            "ShowInFiltersPane": {
                "type": "integer"
            },
            "Sortable": {
                "type": "boolean"
            },
            "StaticName": {
                "type": "string"
            },
            "Title": {
                "type": "string"
            },
            "FieldTypeKind": {
                "type": "integer"
            },
            "TypeAsString": {
                "type": "string"
            },
            "TypeDisplayName": {
                "type": "string"
            },
            "TypeShortDescription": {
                "type": "string"
            },
            "FillInChoice": {
                "type": "boolean"
            },
            "Choices": {
                "type": "object",
                "properties": {
                    "__metadata": {
                        "type": "object",
                        "properties": {
                            "type": {
                                "type": "string"
                            }
                        }
                    },
                    "results": {
                        "type": "array",
                        "items": {
                            "type": "string"
                        }
                    }
                }
            },
            "EditFormat": {
                "type": "integer"
            },
            "AllowHyperlink": {
                "type": "boolean"
            },
            "AppendOnly": {
                "type": "boolean"
            },
            "NumberOfLines": {
                "type": "integer"
            },
            "RestrictedMode": {
                "type": "boolean"
            },
            "RichText": {
                "type": "boolean"
            },
            "UnlimitedLengthInDocumentLibrary": {
                "type": "boolean"
            },
            "WikiLinking": {
                "type": "boolean"
            }
        },
        "required": [
            "Id"
        ]
    }
}

 Now we will need a Flow called Submit Poll to submit the answers. To do so we will again use the SharePoint REST API to post a json in order to create an entry in the survey.

This is the corresponding Flow block