Handling The SharePoint REST API Search Threshold Limit In powershell

Question

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 ("[email protected]", $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

harshal gite · Accepted Answer · 2019-09-04 07:44:40Z

0

You can try the code given below:

#set variable to count of results you want
$resultsLimit = 5000

#set how many rows to get in single page of search results & starting index
$rowsPerPage = 500
$startIndex = 0

Do {
    #Set request variables
    $apiUrl = "$targetSite" + "_api/search/query?querytext='$searchKeyWord'&startrow=$($startIndex)&rowsperpage=$($rowsPerPage)"

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

    #consumption logic goes here...

    #increment the $startIndex by $rowsPerPage + 1 (for next page)
    $startIndex = $startIndex + $rowsPerPage + 1
}
While ($startIndex -le 5000)

edited Sep 4, 2019 at 7:44

answered Sep 3, 2019 at 6:16

harshal gite

1,4741 gold badge13 silver badges24 bronze badges

Hi, the problem is I dont want some resultsLimit to some number. I would like to have all the results. So it needs to be dynamic.

Ola
– Ola

2019-09-03 09:42:50 +00:00
Commented Sep 3, 2019 at 9:42
2

SharePoint Online has a HARD SET limit of 500 results. You just cannot change that. Having to view more than 500 results in search makes no sense. Why do we search? To find information quickly and we prefer it to be within first 100 results or else changing the search keyword should be preferred. In the above code $resultsLimit is configurable. You can change it within your code and make it 10000 or anything.

harshal gite
– harshal gite

2019-09-03 10:23:14 +00:00
Commented Sep 3, 2019 at 10:23
What I have added is how to use paging in your code. and in your code itself you were trying rowlimit of 5000, so I put it as 5000.

harshal gite
– harshal gite

2019-09-03 10:24:21 +00:00
Commented Sep 3, 2019 at 10:24
@Ola You would add each iteration request to a collection variable.

Mike
– Mike

2019-09-03 23:33:12 +00:00
Commented Sep 3, 2019 at 23:33
Could you please show me an example of how it will dynamic iterating into all pages?

Ola
– Ola

2019-09-04 07:42:01 +00:00
Commented Sep 4, 2019 at 7:42

| Show 1 more comment

Stack Exchange Network

Handling The SharePoint REST API Search Threshold Limit In powershell

1 Answer 1

Hot Network Questions

Handling The SharePoint REST API Search Threshold Limit In powershell

1 Answer 1

Related

Hot Network Questions