2

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
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)
  • 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 Sep 3 '19 at 9:42
  • 1
    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 Sep 3 '19 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 Sep 3 '19 at 10:24
  • @Ola You would add each iteration request to a collection variable. – Mike Sep 3 '19 at 23:33
  • Could you please show me an example of how it will dynamic iterating into all pages? – Ola Sep 4 '19 at 7:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.