How to Export Search Results in SharePoint Online using PowerShell?

Requirement: Run Search Query and export search results to a CSV in SharePoint Online using PowerShell.

How to Search SharePoint Online using PowerShell?

PowerShell is a great way to automate tasks in SharePoint Online. With PowerShell, you can quickly find the information you need from your SharePoint Online environment. At times, You may need to export search results for further analysis or to use in other applications. In this blog post, we will show you how to use PowerShell to search for content in SharePoint Online and export the search results to a CSV.

Using Keyword query to execute search and export results to CSV with CSOM is explained in my other post: How to Search SharePoint Online using PowerShell-Keyword Query?, Now, I need to get the List Item ID of the latest item created in a SharePoint Online list:

#Config Variables
$SiteURL = "https://crescent.sharepoint.com/sites/DocHub"
$ListURL= "https://crescent.sharepoint.com/sites/DocHub/Documents"
$SearchQuery = "Path:" + $ListURL
 
#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Interactive

#Perform Search     
$SearchResults = Submit-PnPSearchQuery -Query $SearchQuery -All -SelectProperties ListItemID -SortList @{Created="Descending"}
 
#Get the Last Created Item's ID
Write-host "The Latest Item ID is:" $SearchResults.ResultRows[0]["ListItemID"]

You may have a file with a specific name anywhere on your SharePoint site. Use “filename:something.docx” as a search query to get all files with the particular name.

PnP PowerShell to Search SharePoint Online site

Here is the PnP PowerShell to search for content in your SharePoint Online site:

#Config Variables
$SiteURL = "https://crescent.sharepoint.com/sites/DocHub"
$SearchQuery = "Title:document* Path:" + $SiteURL

#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Interactive

#Run Search Query     
$SearchResults = Submit-PnPSearchQuery -Query $SearchQuery -All

$Results = @()
foreach($ResultRow in $SearchResults.ResultRows)  
{  
    #Get All Properties from search results
    $Result = New-Object PSObject  
    $ResultRow.GetEnumerator()| ForEach-Object { $Result | Add-Member Noteproperty $_.Key $_.Value}  
    $Results+=$Result
}
$Results

To get All available properties of the search results, use: $SearchResults.ResultRows[0] that will get you all properties of the search results: 

  • Author
  • CollapsingStatus
  • contentclass
  • ContentTypeId
  • Culture
  • deeplinks
  • Description
  • DisplayAuthor
  • docaclmeta
  • DocId
  • EditorOWSUSER
  • FileExtension
  • FileType
  • GeoLocationSource
  • HitHighlightedProperties
  • HitHighlightedSummary
  • importance
  • IndexDocId
  • IsContainer
  • IsDocument
  • IsExternalContent
  • LastModifiedTime
  • LinkingUrl
  • OriginalPath
  • ParentLink
  • PartitionId
  • Path
  • PictureThumbnailURL
  • piSearchResultId
  • ProgId
  • RenderTemplateId
  • ResultTypeId
  • ResultTypeIdList
  • SecondaryFileExtension
  • SectionIndexes
  • SectionNames
  • ServerRedirectedEmbedURL
  • ServerRedirectedPreviewURL
  • ServerRedirectedURL
  • SiteDescription
  • SiteId
  • SiteLogo
  • SiteName
  • Size
  • SPWebUrl
  • Title
  • UniqueId
  • UrlZone
  • ViewsLifeTime
  • ViewsRecent
  • WebId
  • WebTemplate
  • WorkId
  • Write

Search SharePoint Online Site and Export Results to a CSV File

Let’s search for all Excel files from a SharePoint Online site and export search results to a CSV file.

#Parameters
$SiteUrl = "https://crescent.sharepoint.com/sites/Marketing"
$SearchQuery = "*.xlsx Path:" + $SiteURL
$CSVFile = "C:\Temp\ExcelFiles.csv"

#Connect to PnP Online
Connect-PnPOnline -Url $SiteUrl -Interactive

#Execute Search 
$SearchResults = Submit-PnPSearchQuery -Query $SearchQuery -All -TrimDuplicates $False -SelectProperties Filename, Author, Size, ListItemID, LastModifiedTime

#Collect Data from search results
$Results = @()
ForEach ($ResultRow in $SearchResults.ResultRows) 
{     
    $Results += [pscustomobject] @{
        Filename   = $ResultRow["Filename"]
        Author = $ResultRow["Author"]
        Size = $ResultRow["Size"]
        LastModified = $ResultRow["LastModifiedTime"]
        ListItemID     = $ResultRow["ListItemID"]
        ParentFolder = $ResultRow["ParentLink"]
        URL       = $ResultRow["Path"]
    } 
}
$Results
#Export results to CSV
$Results | Export-Csv -Path $CSVFile -NoTypeInformation

This script searches the given query and exports search results to a CSV file.

PowerShell to export search results to CSV in SharePoint Online

Please note, this approach queries files from search indexed, but there could be a lag in indexing schedules index may not be real-time. Here is a reference on Submit-PnPSearchQuery

In summary, By following the steps outlined in this article, you can search for and export search results in SharePoint Online using PowerShell. This provides a convenient and automated way for administrators to quickly find the information they need and take action on it. Whether you need to export data for reporting, analysis, or backup purposes, using PowerShell to search and export search results in SharePoint Online is a convenient and effective solution.

Salaudeen Rajack

Salaudeen Rajack - SharePoint Expert with Two decades of SharePoint Experience. Love to Share my knowledge and experience with the SharePoint community, through real-time articles!

4 thoughts on “How to Export Search Results in SharePoint Online using PowerShell?

  • Just thinking,
    reuse this solution and search on tenant level for string contentclass:STS_Site , put into csv, loop against the file and
    identify site admins. Background is the user story give me all sites where I’m are site administrator (group and not group connected)

    Reply
  • Thank you for your valuable information. It was very helpful.
    One comment on this is that it would be better to add encoding option when downloading to Excel like this to avoid weird characters in case of using Asian characters.

    $Results | Export-Csv -Path $CSVFile -NoTypeInformation -Encoding Default

    Reply
  • I am trying to do the same thing but i connecting via AppID. I get access denied when i run the search cmdlet. Any possible solution for this?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *