#StackBounty: #powershell #search #api Handling The SharePoint REST API Search Threshold Limit In powershell

Bounty: 50

We are using SharePoint Online. I would like to search in all sitecollections for the word “passport”. I make it working with the REST api in powershell. But I have now only the problem that it returns only the first 500 results. How can I iterate into all the batches of 500 results?

I found some blog posts how to that in spfx:

https://www.c-sharpcorner.com/article/handling-the-sharepoint-rest-api-search-threshold-limit-in-spfx/

This is my powershell code:

    try
{
    $searchKeyWord = "passport"

    $secpasswd = ConvertTo-SecureString "123123!" -AsPlainText -Force
    $mycreds = New-Object System.Management.Automation.PSCredential ("test@mycompany.nl", $secpasswd)

    # Connect to SharePoint Online
    $targetSite = "https://myCompany.sharepoint.com/"
    $targetSiteUri = [System.Uri]$targetSite

    Connect-PnPOnline $targetSite -Credentials $mycreds

    # Retrieve the client credentials and the related Authentication Cookies
    $context = (Get-PnPWeb).Context
    $credentials = $context.Credentials
    $authenticationCookies = $credentials.GetAuthenticationCookie($targetSiteUri, $true)

    # Set the Authentication Cookies and the Accept HTTP Header
    $webSession = New-Object Microsoft.PowerShell.Commands.WebRequestSession
    $webSession.Cookies.SetCookies($targetSiteUri, $authenticationCookies)
    $webSession.Headers.Add("Accept", "application/json;odata=verbose")

    # Set request variables
    $apiUrl = "$targetSite" + "_api/search/query?querytext='$searchKeyWord'&rowlimit=5000"#&rowlimit=$($rowLimit)&startrow=$($startRow)" #&selectproperties='Title,Author,Path'"

    # Make the REST request
    $webRequest = Invoke-WebRequest -Uri $apiUrl -Method Get -WebSession $webSession

    # Consume the JSON result
    $jsonLibrary = $webRequest.Content | ConvertFrom-Json
    $results = $jsonLibrary.d.query.PrimaryQueryResult.RelevantResults.Table.Rows.results

    #MaxRowLimit
    Write-Host $results.Count "Results" -ForegroundColor Green

    $ResultsArray = @()

    for($i=0; $i -le $results.length-1; $i++)
    {
        $row = $results[$i]
        $obj = New-Object PSObject

        for ($j=0; $j -le $row.Cells.results.length-1; $j++)
        {
            if ($row.Cells.results[$j].Key -eq ‘Title’)
            {
                Add-Member -InputObject $obj -MemberType NoteProperty -Name Title -Value $row.Cells.results[$j].Value
            }

            if ($row.Cells.results[$j].Key -eq ‘FileExtension’)
            {
                Add-Member -InputObject $obj -MemberType NoteProperty -Name FileExtension -Value $row.Cells.results[$j].Value
            }

            if ($row.Cells.results[$j].Key -eq ‘Path’)
            {
                Add-Member -InputObject $obj -MemberType NoteProperty -Name Path -Value $row.Cells.results[$j].Value
            }

            if ($row.Cells.results[$j].Key -eq ‘OriginalPath’)
            {
                Add-Member -InputObject $obj -MemberType NoteProperty  -Name OriginalPath -Value $row.Cells.results[$j].Value
            }

            if ($row.Cells.results[$j].Key -eq ‘Author’) #
            {
                Add-Member -InputObject $obj -MemberType NoteProperty -Name Author -Value $row.Cells.results[$j].Value
            }

            if ($row.Cells.results[$j].Key -eq ‘HitHighlightedSummary’)
            {
                Add-Member -InputObject $obj -MemberType NoteProperty -Name HitHighlightedSummary -Value $row.Cells.results[$j].Value
            }

            if ($row.Cells.results[$j].Key -eq ‘SiteName’)
            {
                Add-Member -InputObject $obj -MemberType NoteProperty -Name SiteName -Value $row.Cells.results[$j].Value
            }

            if ($row.Cells.results[$j].Key -eq ‘SPWebUrl’)
            {
                Add-Member -InputObject $obj -MemberType NoteProperty -Name SPWebUrl -Value $row.Cells.results[$j].Value
            }

            if ($row.Cells.results[$j].Key -eq ‘IsDocument’)
            {
                Add-Member -InputObject $obj -MemberType NoteProperty -Name IsDocument -Value $row.Cells.results[$j].Value
            }

            if ($row.Cells.results[$j].Key -eq ‘ParentLink’)
            {
                Add-Member -InputObject $obj -MemberType NoteProperty -Name ParentLink -Value $row.Cells.results[$j].Value
            }

            if ($row.Cells.results[$j].Key -eq ‘ViewsLifeTime’) # aantal hits, niet unieke hits
            {
                Add-Member -InputObject $obj -MemberType NoteProperty -Name ViewsLifeTime -Value $row.Cells.results[$j].Value
            }

            if ($row.Cells.results[$j].Key -eq ‘ViewsRecent’) #
            {
                Add-Member -InputObject $obj -MemberType NoteProperty -Name ViewsRecent -Value $row.Cells.results[$j].Value
            }

            if ($row.Cells.results[$j].Key -eq ‘Rank’) #
            {
                Add-Member -InputObject $obj -MemberType NoteProperty -Name Rank -Value $row.Cells.results[$j].Value
            }
        }

        $ResultsArray += $obj
    }

    $ResultsArray | Export-Csv -Path "c:outfile.csv" -NoTypeInformation
}
catch
{
    Write-Host $_.Exception.Message -ForegroundColor Red
}

Write-Host "Finished" -ForegroundColor Green


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.