Home

SharePoint: How to export all the Publishing pages from a complete site collection to CSV Stats

%3CLINGO-SUB%20id%3D%22lingo-sub-282765%22%20slang%3D%22en-US%22%3ESharePoint%3A%20How%20to%20export%20all%20the%20Publishing%20pages%20from%20a%20complete%20site%20collection%20to%20CSV%20Stats%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-282765%22%20slang%3D%22en-US%22%3E%3CP%3EWhen%20the%20Intranet%20site%20built%20in%20SharePoint%20Online%20is%20based%20on%20the%20Publishing%20site%20(not%20modern)%26nbsp%3Bwith%20a%20complex%20structure%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EEach%20department%20placed%20as%20subsite%3C%2FLI%3E%0A%3CLI%3EDifference%20between%20the%20countries%20and%20region%20(organized%20in%20Treeview)%3C%2FLI%3E%0A%3CLI%3EDifference%20between%20business%20and%20function%3C%2FLI%3E%0A%3CLI%3E...%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3EWe%20have%20at%20the%20end%20a%20large%20number%20of%20subsites%20with%20the%20news%20published%20in%20each.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20only%20way%20to%20present%20the%20result%20to%20the%20user%20into%20aggregated%20view%20is%20to%20pass%20threw%20the%20search%20engine%20and%20the%20associated%20queries.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%26nbsp%3Bquestion%20of%20statistic%20will%20become%20hard%20to%20give%20the%20answers%20for%20the%20following%20question%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EWhat%20is%20the%20department%20with%20the%20biggest%20news%20number%3F%3C%2FLI%3E%0A%3CLI%3EWhat%20is%20the%20number%20of%20news%20published%20per%20month%3F%3C%2FLI%3E%0A%3CLI%3EWho%20is%20the%20most%20active%20content%20manager%3F%3C%2FLI%3E%0A%3CLI%3E...%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20only%20way%20to%20give%20that%20feedback%20is%20to%20export%20all%20the%20news%20(with%20couple%20of%20metadata)%20into%20a%20CSV%20and%20apply%20the%20Excel%20transformation%20(or%20PowerBI)%20to%20create%20simple%20presentation%20mode.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20following%20script%20will%20give%20you%20that%20kind%20of%20export%2C%20you%20only%20have%20to%20adapt%20the%20search%20query%20and%20fields%20to%20get%20from%20the%20Search%20to%20have%20your%20result.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%5Bstring%5D%24SitePagesURL%20%3D%20%22https%3A%2F%2Fyourtenant.sharepoint.com%22%0A%5BDateTime%5D%24PortalPublishingDate%20%3D%20Get-Date%0A%5Bstring%5D%24CSVFileName%20%3D%20%22ExportAllNewsItems.csv%22%0A%0A%5Bstring%5D%24queryText%20%3D%20%22ContentTypeId%3A0x010100C568DB52D9D0A14D9B2FDCC96666E9F2007948130EC3DB064584E219954237AF3900242457EFB8B24247815D688C526CD44D00DAB155038B062847A409F1E450E9E5E3*%20%20Path%3Ahttps%3A%2F%2Fyourtenant.sharepoint.com%2Fintranet%20%22%0A%5Bstring%5D%24outputline%20%3D%20%22%22%0A%0A%5Bint%5D%24TempUserID%20%3D%200%0A%0A%23%20---------------------------------------------------------------------------------------------------------------%0Afunction%20Load-DLLandAssemblies%0A%7B%0A%20%5Bstring%5D%24defaultDLLPath%20%3D%20%22%22%0A%0A%20%23%20Load%20assemblies%20to%20PowerShell%20session%20%0A%20%24defaultDLLPath%20%3D%20%22C%3A%5CProgram%20Files%5CSharePoint%20Online%20Management%20Shell%5CMicrosoft.Online.SharePoint.PowerShell%5CMicrosoft.SharePoint.Client.dll%22%0A%20%5BSystem.Reflection.Assembly%5D%3A%3ALoadFile(%24defaultDLLPath)%0A%0A%20%24defaultDLLPath%20%3D%20%22C%3A%5CProgram%20Files%5CSharePoint%20Online%20Management%20Shell%5CMicrosoft.Online.SharePoint.PowerShell%5CMicrosoft.SharePoint.Client.Runtime.dll%22%0A%20%5BSystem.Reflection.Assembly%5D%3A%3ALoadFile(%24defaultDLLPath)%0A%0A%20%24defaultDLLPath%20%3D%20%22C%3A%5CProgram%20Files%5CSharePoint%20Online%20Management%20Shell%5CMicrosoft.Online.SharePoint.PowerShell%5CMicrosoft.Online.SharePoint.Client.Tenant.dll%22%0A%20%5BSystem.Reflection.Assembly%5D%3A%3ALoadFile(%24defaultDLLPath)%0A%0A%20%24defaultDLLPath%20%3D%20%22C%3A%5CWindows%5CMicrosoft.NET%5Cassembly%5CGAC_MSIL%5CMicrosoft.SharePoint.Client.Search%5Cv4.0_16.0.0.0__71e9bce111e9429c%5CMicrosoft.SharePoint.Client.Search.dll%22%0A%20%5BSystem.Reflection.Assembly%5D%3A%3ALoadFile(%24defaultDLLPath)%0A%0A%7D%0A%0Afunction%20Get-SearchResults(%5Bint%5D%20%24startIndex%2C%20%24myclientContext)%0A%7B%0A%20try%0A%20%7B%0A%20%20%24keywordQuery%20%3D%20New-Object%20Microsoft.SharePoint.Client.Search.Query.KeywordQuery(%24MyctxTemp)%20%0A%20%20%20%20%20%20%20%20%24keywordQuery.StartRow%20%3D%20%24startIndex%20%23gets%20or%20sets%20the%20first%20row%20of%20information%20from%20the%20search%20results%0A%20%20%24keywordQuery.QueryText%20%3D%20%24queryText%0A%20%20%24keywordQuery.RowLimit%20%3D%20500%0A%20%20%24keywordQuery.RowsPerPage%20%3D%20500%0A%20%20%24keywordQuery.TrimDuplicates%3D%24false%20%20%0A%20%20%24keywordQuery.Timeout%20%3D%2010000%3B%0A%20%20%24keywordQuery.SelectProperties.Add(%22LastModifiedTime%22)%0A%20%20%24keywordQuery.SelectProperties.Add(%22RefinableDate00%22)%20%20%20%23%20Date%20Article%0A%20%20%24keywordQuery.SelectProperties.Add(%22RefinableString00%22)%20%23%20Business%20%EF%BC%86%20Function%0A%20%20%24keywordQuery.SelectProperties.Add(%22RefinableString01%22)%20%23%20News%20Type%0A%20%20%24keywordQuery.SelectProperties.Add(%22RefinableString03%22)%20%23%20Publishing%20Area%20%2F%20Country%0A%20%20%24keywordQuery.SelectProperties.Add(%22CreatedBy%22)%0A%20%20%24keywordQuery.SortList.Add(%22RefinableDate00%22%2C%22Desc%22)%0A%0A%20%20%24searchExecutor%20%3D%20New-Object%20Microsoft.SharePoint.Client.Search.Query.SearchExecutor(%24MyctxTemp)%0A%20%20%0A%20%20%24ClientResult%20%3D%20%24searchExecutor.ExecuteQuery(%24keywordQuery)%0A%20%20%24MyctxTemp.ExecuteQuery()%0A%20%20%20%20%20%20%20%20%23%24MyctxTemp.ExecuteQueryWithIncrementalRetry(5%2C%2030000)%3B%20%235%20retries%2C%20with%20a%20base%20delay%20of%2030%20secs.%0A%20%20%0A%20%20Write-Host%20%22%20%20%20%20%20%20%20%20%20-%20Item%20number%20into%20the%20function%3A%22%2C%20%24ClientResult.Value%5B0%5D.ResultRows.Count%20%0A%20%20%20%20%0A%20%20return%20%24ClientResult.Value%5B0%5D%0A%0A%20%7D%0A%20Catch%20%5BException%5D%20%7B%0A%20%20Write-host%20%22%20%26gt%3B%26gt%3B%26gt%3B%26gt%3B%20ERROR%20MESSAGE%3A%22%2C%20%24_.Exception.Message%20-f%20Red%0A%20%20Return%20%24False%0A%20%7D%20%20%20%20%20%20%20%20%0A%0A%7D%0A%0A%23%20---------------------------------------------------------------------------------------------------------------%0AFunction%20Get-All-Intranet-News-Published-ExportCSV(%24MyctxTemp%2C%20%24MyspoRootwebTemp)%0A%7B%0A%20%5BSystem.Data.DataTable%5D%24resultDataTable%20%3D%20new-object%20System.Data.DataTable(%22SGSWORLDNEWS%22)%0A%20%5BSystem.Data.DataColumn%5D%24titleCol%20%3D%20new-object%20System.Data.DataColumn(%22Title%22)%0A%20%5BSystem.Data.DataColumn%5D%24pathCol%20%3D%20new-object%20System.Data.DataColumn(%22Path%22)%0A%20%5BSystem.Data.DataColumn%5D%24RefinableDate00Col%20%3D%20new-object%20System.Data.DataColumn(%22RefinableDate00%22)%0A%20%5BSystem.Data.DataColumn%5D%24RefinableString00Col%20%3D%20new-object%20System.Data.DataColumn(%22RefinableString00%22)%0A%20%5BSystem.Data.DataColumn%5D%24RefinableString01Col%20%3D%20new-object%20System.Data.DataColumn(%22RefinableString01%22)%0A%20%5BSystem.Data.DataColumn%5D%24RefinableString03Col%20%3D%20new-object%20System.Data.DataColumn(%22RefinableString03%22)%0A%20%5BSystem.Data.DataColumn%5D%24CreatedByCol%20%3D%20new-object%20System.Data.DataColumn(%22CreatedBy%22)%0A%0A%20%24resultDataTable.Columns.Add(%24titleCol)%0A%20%24resultDataTable.Columns.Add(%24pathCol)%0A%20%24resultDataTable.Columns.Add(%24RefinableDate00Col)%0A%20%24resultDataTable.Columns.Add(%24RefinableString00Col)%0A%20%24resultDataTable.Columns.Add(%24RefinableString01Col)%0A%20%24resultDataTable.Columns.Add(%24RefinableString03Col)%0A%20%24resultDataTable.Columns.Add(%24CreatedByCol)%0A%20%0A%20%5Bint%5D%24currentRowIndex%20%3D%200%0A%20%24resultTable%20%3D%20Get-SearchResults%20%24currentRowIndex%20%24MyctxTemp%0A%20Write-Host%20%22%20%20%26gt%3B%26gt%3B%20Total%20Rows%20Include%20Duplicated%3A%22%2C%20%20%20%24resultTable.TotalRowsIncludingDuplicates%20-ForegroundColor%20Red%0A%20%0A%20if((%24resultTable%20-ne%20%24null)%20-and%20(%24resultTable.TotalRowsIncludingDuplicates%20-gt%200))%0A%20%7B%0A%20%20while%20(%24resultTable.TotalRowsIncludingDuplicates%20-gt%20%24resultDataTable.Rows.Count)%0A%20%20%7B%0A%20%20%20foreach(%24resultRow%20in%20%24resultTable.ResultRows)%0A%20%20%20%7B%0A%20%20%20%20%5BSystem.Data.DataRow%5D%24myrow%20%3D%20%24resultDataTable.NewRow()%0A%20%20%20%20%24myrow%5B%22Title%22%5D%20%3D%20%24resultRow%5B%22Title%22%5D%0A%20%20%20%20%24myrow%5B%22Path%22%5D%20%3D%20%24resultRow%5B%22Path%22%5D%0A%20%20%20%20%24myrow%5B%22RefinableDate00%22%5D%20%3D%20%24resultRow%5B%22RefinableDate00%22%5D%0A%20%20%20%20%24myrow%5B%22RefinableString00%22%5D%20%3D%20%24resultRow%5B%22RefinableString00%22%5D%0A%20%20%20%20%24myrow%5B%22RefinableString01%22%5D%20%3D%20%24resultRow%5B%22RefinableString01%22%5D%0A%20%20%20%20%24myrow%5B%22RefinableString03%22%5D%20%3D%20%24resultRow%5B%22RefinableString03%22%5D%0A%20%20%20%20%24myrow%5B%22CreatedBy%22%5D%20%3D%20%24resultRow%5B%22CreatedBy%22%5D%0A%20%20%20%20%24resultDataTable.Rows.Add(%24myrow)%0A%20%20%20%7D%0A%20%20%20%0A%20%20%20%24currentRowIndex%20%3D%20%24resultDataTable.Rows.Count%0A%20%20%20%0A%20%20%20%24resultTable%20%3D%20%24null%0A%20%20%20%24resultTable%20%3D%20Get-SearchResults%20%24currentRowIndex%20%24MyctxTemp%0A%20%20%20if%20((%24resultTable%20-ne%20%24null)%20-and%20(%24resultTable.TotalRowsIncludingDuplicates%20-gt%200))%0A%20%20%20%7B%0A%20%20%20%20if%20(%24resultTable.RowCount%20-le%200)%0A%20%20%20%20%7B%0A%20%20%20%20%20break%0A%20%20%20%20%7D%0A%20%20%20%7D%0A%20%20%20else%0A%20%20%20%7B%0A%20%20%20%20break%0A%20%20%20%7D%0A%20%20%7D%0A%20%7D%0A%20%5Bstring%5D%20%24totalResults%20%3D%20%24resultDataTable.Rows.Count%3B%0A%20Write-Host%20%22%20%20%20%20%20%26gt%3B%26gt%3B%26gt%3B%26gt%3B%26gt%3B%20Table%20Items%20placed%20into%20Datatable%3A%20%22%2C%20%24totalResults%20-ForegroundColor%20Yellow%0A%0A%20Clear-Content%20%24CSVFileName%0A%20%24outputline%20%3D%20'%22NewsTitle%22%3B%22PublicationDate%22%3B%22NewsURL%22%3B%22BusinessFunction%22%3B%22NewsType%22%3B%22PublishingAreaCountry%22%3B%22NewsCreator%22%3B'%0A%20Add-Content%20-Encoding%20UTF8%20-Force%20%24CSVFileName%20%24outputline%20%20%20%0A%0A%20foreach(%24result%20in%20%24resultDataTable.Rows)%0A%20%7B%0A%20%20if(%24result%5B%22RefinableDate00%22%5D%20-ne%20%22%22)%0A%20%20%7B%0A%20%20%20%24TempString%20%3D%20%24result%5B%22RefinableDate00%22%5D.split('%3B')%5B0%5D%0A%20%20%20%24PortalPublishingDate%3D%5Bdatetime%5D%3A%3AParseExact(%5Bstring%5D%24TempString%2C%20'M%2Fd%2Fyyyy%20h%3Amm%3Ass%20tt'%2C%20%5BCultureInfo%5D%3A%3AInvariantCulture)%20%2310%2F2%2F2018%2010%3A00%3A00%20PM%0A%20%20%0A%20%20%20Write-Host%20%22%20%20----------------------------------------%20%22%0A%20%20%20Write-Host%20%22%20------%26gt%3B%26gt%3B%26gt%3B%20NewsPath%3A%22%2C%20%24result%5B%22Path%22%5D%0A%20%20%20Write-Host%20%22%20------%26gt%3B%26gt%3B%26gt%3B%20Title%3A%22%2C%20%24result%5B%22Title%22%5D%20%0A%20%20%20Write-Host%20%22%20------%26gt%3B%26gt%3B%26gt%3B%20RefinableDate00%3A%22%2C%20%20%24result%5B%22RefinableDate00%22%5D%20%23%24PortalPublishingDate%0A%20%20%20Write-Host%20%22%20------%26gt%3B%26gt%3B%26gt%3B%20PublicationDate%3A%22%2C%20%20%24PortalPublishingDate%0A%20%20%20Write-Host%20%22%20------%26gt%3B%26gt%3B%26gt%3B%20BusinessFunction%3A%22%2C%20%24result%5B%22RefinableString00%22%5D%0A%20%20%20Write-Host%20%22%20------%26gt%3B%26gt%3B%26gt%3B%20NewsType%3A%22%2C%20%24result%5B%22RefinableString01%22%5D%0A%20%20%20Write-Host%20%22%20------%26gt%3B%26gt%3B%26gt%3B%20PublishingAreaCountry%3A%22%2C%20%24result%5B%22RefinableString03%22%5D%0A%20%20%20Write-Host%20%22%20------%26gt%3B%26gt%3B%26gt%3B%20NewsCreator%3A%22%2C%20%24result%5B%22CreatedBy%22%5D%0A%20%20%20Write-Host%20%22%20%20----------------------------------------%20%22%0A%0A%20%20%20%23CSV%20file%20location%2C%20to%20store%20the%20result%0A%20%20%20%24outputline%20%3D%20'%22'%2B%20%24result%5B%22Title%22%5D%20%2B'%22%3B%22'%2B%20%24PortalPublishingDate.ToString(%22dd.MM.yyyy%22)%20%2B'%22%3B%22'%2B%20%24result%5B%22Path%22%5D%20%2B'%22%3B%22'%2B%20%24result%5B%22RefinableString00%22%5D%20%2B'%22%3B%22'%2B%20%24result%5B%22RefinableString01%22%5D%20%2B'%22%3B%22'%2B%20%24result%5B%22RefinableString03%22%5D%20%2B'%22%3B%22'%2B%20%24result%5B%22CreatedBy%22%5D%20%2B'%22%3B'%0A%20%20%20Add-Content%20-Encoding%20UTF8%20-Force%20%24CSVFileName%20%24outputline%0A%20%20%7D%0A%20%7D%0A%7D%0A%0A%23%20---------------------------------------------------------------------------------------------------------------%0ALoad-DLLandAssemblies%0A%0A%23get%20and%20save%20your%20O365%20credentials%0A%5Bstring%5D%24username%20%3D%20%22loginadmin%40yourtenant.onmicrosoft.com%22%0A%5Bstring%5D%24PwdTXTPath%20%3D%20%22C%3A%5CSECUREDPWD%5CExportedPWD-%24(%24username).txt%22%0A%24secureStringPwd%20%3D%20ConvertTo-SecureString%20-string%20(Get-Content%20%24PwdTXTPath)%0A%24cred%20%3D%20New-Object%20System.Management.Automation.PSCredential%20-ArgumentList%20%24username%2C%20%24secureStringPwd%0A%0A%23connect%20to%20the%20web%20site%20using%20the%20stored%20credentials%0AWrite-host%20%22%20%22%0AWrite-host%20%22%20--------------------------------------------------------------------------------------------%20%22%20-ForegroundColor%20green%0AWrite-host%20%22%20----%20CONNECT%20THE%20SITE%20---%20%22%20-ForegroundColor%20green%0AWrite-host%20%22%20%20%20CONNECTED%20SITE%3A%22%2C%20%24SitePagesURL%20%20-ForegroundColor%20Yellow%0A%0A%24Myctx%20%3D%20New-Object%20Microsoft.SharePoint.Client.ClientContext(%24SitePagesURL%20%2B%22%2Fintranet%22)%0A%0A%24Myctx.Credentials%20%3D%20New-Object%20Microsoft.SharePoint.Client.SharePointOnlineCredentials(%24cred.UserName%2C%24cred.Password)%0A%24Myctx.RequestTimeout%20%3D%201000000%20%23%20milliseconds%0A%24MyspoRootweb%20%3D%20%24Myctx.Web%0A%24Myctx.Load(%24MyspoRootweb)%0A%24Myctx.ExecuteQuery()%0A%0AWrite-Host%20%22%20%22%0AWrite-Host%20%22%20---------------------------------------------------------%22%0AWrite-Host%20%22%20%20%26gt%3B%26gt%3B%26gt%3B%26gt%3B%20%23%20Server%20Version%3A%22%20%24Myctx.ServerVersion%20%22%20%23%20%26lt%3B%26lt%3B%26lt%3B%26lt%3B%26lt%3B%26lt%3B%22%20-ForegroundColor%20Green%20%0AWrite-Host%20%22%20---------------------------------------------------------%22%0AWrite-Host%20%22%20%22%0A%0AWrite-host%20%22%20--------------------------------------------------------%20%22%0AWrite-host%20%22%20%20%20--%26gt%3B%26gt%3B%20RootSite%3A%22%2C%20%24MyspoRootweb.URL%20-ForegroundColor%20green%0A%0AWrite-host%20%22%20%22%0A%0AGet-All-Intranet-News-Published-ExportCSV%20%24Myctx%20%24MyspoRootweb%0A%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20solution%20was%20used%20on%20my%20production%20site%20with%20thousands%20of%20news%20published%20and%20give%20us%20now%20statistics%20for%20the%20last%20years%20of%20that%20Intranet%20site.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFabrice%20Romelard%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CEM%3E%3CSTRONG%3EFrench%20version%20of%20that%20message%3A%3C%2FSTRONG%3E%3C%2FEM%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CEM%3E%3CA%20href%3D%22http%3A%2F%2Fblogs.developpeur.org%2Ffabrice69%2Farchive%2F2018%2F11%2F06%2Fsharepoint-extraire-les-pages-publi-es-dans-un-site-de-publishing-en-csv.aspx%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fblogs.developpeur.org%2Ffabrice69%2Farchive%2F2018%2F11%2F06%2Fsharepoint-extraire-les-pages-publi-es-dans-un-site-de-publishing-en-csv.aspx%3C%2FA%3E%26nbsp%3B%3C%2FEM%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CEM%3EThe%20source%20sites%20used%20to%20build%20that%20script%20are%3A%3C%2FEM%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CEM%3E%3CA%20href%3D%22https%3A%2F%2Fblogs.msdn.microsoft.com%2Fvesku%2F2014%2F08%2F26%2Fsystem-update-for-sharepoint-list-items-using-app-model%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fblogs.msdn.microsoft.com%2Fvesku%2F2014%2F08%2F26%2Fsystem-update-for-sharepoint-list-items-using-app-model%2F%3C%2FA%3E%3C%2FEM%3E%3C%2FLI%3E%0A%3CLI%3E%3CEM%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FSharePoint%2FHow-to-change-Modern-page-Author-from-the-quot-Created-By-quot%2Fm-p%2F220432%23M20147%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FSharePoint%2FHow-to-change-Modern-page-Author-from-the-quot-Created-By-quot%2Fm-p%2F220432%23M20147%3C%2FA%3E%3C%2FEM%3E%3C%2FLI%3E%0A%3CLI%3E%3CEM%3E%3CA%20href%3D%22https%3A%2F%2Fsharepoint.stackexchange.com%2Fquestions%2F129926%2Fextract-email-from-listitem-user%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsharepoint.stackexchange.com%2Fquestions%2F129926%2Fextract-email-from-listitem-user%3C%2FA%3E%3C%2FEM%3E%3C%2FLI%3E%0A%3CLI%3E%3CEM%3E%3CA%20href%3D%22https%3A%2F%2Fwww.c-sharpcorner.com%2Farticle%2Foffice-365-sharepoint-online-powershell-script-to-call-search-api-and-get-th%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.c-sharpcorner.com%2Farticle%2Foffice-365-sharepoint-online-powershell-script-to-call-search-api-and-get-th%2F%3C%2FA%3E%3C%2FEM%3E%3C%2FLI%3E%0A%3CLI%3E%3CEM%3E%3CA%20href%3D%22https%3A%2F%2Fsharepoint.stackexchange.com%2Fquestions%2F208462%2Fsharepoint-search-js-returns-only-500-search-results%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsharepoint.stackexchange.com%2Fquestions%2F208462%2Fsharepoint-search-js-returns-only-500-search-results%3C%2FA%3E%3C%2FEM%3E%3C%2FLI%3E%0A%3CLI%3E%3CEM%3E%3CA%20href%3D%22http%3A%2F%2Fwww.thesharepointguide.com%2Fsharepoint-search-how-to-return-all-results%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fwww.thesharepointguide.com%2Fsharepoint-search-how-to-return-all-results%2F%3C%2FA%3E%3C%2FEM%3E%3C%2FLI%3E%0A%3CLI%3E%3CEM%3E%3CA%20href%3D%22https%3A%2F%2Fwww.spjeff.com%2F2015%2F04%2F21%2Fdatatable-in-powershell-for-crazy-fast-filters%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.spjeff.com%2F2015%2F04%2F21%2Fdatatable-in-powershell-for-crazy-fast-filters%2F%3C%2FA%3E%3C%2FEM%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-282765%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EPowerShell%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPublishing%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESites%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Frequent Contributor

When the Intranet site built in SharePoint Online is based on the Publishing site (not modern) with a complex structure:

  • Each department placed as subsite
  • Difference between the countries and region (organized in Treeview)
  • Difference between business and function
  • ...

We have at the end a large number of subsites with the news published in each.

 

The only way to present the result to the user into aggregated view is to pass threw the search engine and the associated queries.

 

The question of statistic will become hard to give the answers for the following question:

  • What is the department with the biggest news number?
  • What is the number of news published per month?
  • Who is the most active content manager?
  • ...

 

The only way to give that feedback is to export all the news (with couple of metadata) into a CSV and apply the Excel transformation (or PowerBI) to create simple presentation mode.

 

The following script will give you that kind of export, you only have to adapt the search query and fields to get from the Search to have your result.

 

[string]$SitePagesURL = "https://yourtenant.sharepoint.com"
[DateTime]$PortalPublishingDate = Get-Date
[string]$CSVFileName = "ExportAllNewsItems.csv"

[string]$queryText = "ContentTypeId:0x010100C568DB52D9D0A14D9B2FDCC96666E9F2007948130EC3DB064584E219954237AF3900242457EFB8B24247815D688C526CD44D00DAB155038B062847A409F1E450E9E5E3*  Path:https://yourtenant.sharepoint.com/intranet "
[string]$outputline = ""

[int]$TempUserID = 0

# ---------------------------------------------------------------------------------------------------------------
function Load-DLLandAssemblies
{
	[string]$defaultDLLPath = ""

	# Load assemblies to PowerShell session 
	$defaultDLLPath = "C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.dll"
	[System.Reflection.Assembly]::LoadFile($defaultDLLPath)

	$defaultDLLPath = "C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.Runtime.dll"
	[System.Reflection.Assembly]::LoadFile($defaultDLLPath)

	$defaultDLLPath = "C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.Online.SharePoint.Client.Tenant.dll"
	[System.Reflection.Assembly]::LoadFile($defaultDLLPath)

	$defaultDLLPath = "C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SharePoint.Client.Search\v4.0_16.0.0.0__71e9bce111e9429c\Microsoft.SharePoint.Client.Search.dll"
	[System.Reflection.Assembly]::LoadFile($defaultDLLPath)

}

function Get-SearchResults([int] $startIndex, $myclientContext)
{
	try
	{
		$keywordQuery = New-Object Microsoft.SharePoint.Client.Search.Query.KeywordQuery($MyctxTemp) 
        $keywordQuery.StartRow = $startIndex #gets or sets the first row of information from the search results
		$keywordQuery.QueryText = $queryText
		$keywordQuery.RowLimit = 500
		$keywordQuery.RowsPerPage = 500
		$keywordQuery.TrimDuplicates=$false  
		$keywordQuery.Timeout = 10000;
		$keywordQuery.SelectProperties.Add("LastModifiedTime")
		$keywordQuery.SelectProperties.Add("RefinableDate00")   # Date Article
		$keywordQuery.SelectProperties.Add("RefinableString00") # Business & Function
		$keywordQuery.SelectProperties.Add("RefinableString01") # News Type
		$keywordQuery.SelectProperties.Add("RefinableString03") # Publishing Area / Country
		$keywordQuery.SelectProperties.Add("CreatedBy")
		$keywordQuery.SortList.Add("RefinableDate00","Desc")

		$searchExecutor = New-Object Microsoft.SharePoint.Client.Search.Query.SearchExecutor($MyctxTemp)
		
		$ClientResult = $searchExecutor.ExecuteQuery($keywordQuery)
		$MyctxTemp.ExecuteQuery()
        #$MyctxTemp.ExecuteQueryWithIncrementalRetry(5, 30000); #5 retries, with a base delay of 30 secs.
		
		Write-Host "         - Item number into the function:", $ClientResult.Value[0].ResultRows.Count 
				
		return $ClientResult.Value[0]

	}
	Catch [Exception] {
		Write-host " >>>> ERROR MESSAGE:", $_.Exception.Message -f Red
		Return $False
	}        

}

# ---------------------------------------------------------------------------------------------------------------
Function Get-All-Intranet-News-Published-ExportCSV($MyctxTemp, $MyspoRootwebTemp)
{
	[System.Data.DataTable]$resultDataTable = new-object System.Data.DataTable("SGSWORLDNEWS")
	[System.Data.DataColumn]$titleCol = new-object System.Data.DataColumn("Title")
	[System.Data.DataColumn]$pathCol = new-object System.Data.DataColumn("Path")
	[System.Data.DataColumn]$RefinableDate00Col = new-object System.Data.DataColumn("RefinableDate00")
	[System.Data.DataColumn]$RefinableString00Col = new-object System.Data.DataColumn("RefinableString00")
	[System.Data.DataColumn]$RefinableString01Col = new-object System.Data.DataColumn("RefinableString01")
	[System.Data.DataColumn]$RefinableString03Col = new-object System.Data.DataColumn("RefinableString03")
	[System.Data.DataColumn]$CreatedByCol = new-object System.Data.DataColumn("CreatedBy")

	$resultDataTable.Columns.Add($titleCol)
	$resultDataTable.Columns.Add($pathCol)
	$resultDataTable.Columns.Add($RefinableDate00Col)
	$resultDataTable.Columns.Add($RefinableString00Col)
	$resultDataTable.Columns.Add($RefinableString01Col)
	$resultDataTable.Columns.Add($RefinableString03Col)
	$resultDataTable.Columns.Add($CreatedByCol)
	
	[int]$currentRowIndex = 0
	$resultTable = Get-SearchResults $currentRowIndex $MyctxTemp
	Write-Host "  >> Total Rows Include Duplicated:", 		$resultTable.TotalRowsIncludingDuplicates -ForegroundColor Red
	
	if(($resultTable -ne $null) -and ($resultTable.TotalRowsIncludingDuplicates -gt 0))
	{
		while ($resultTable.TotalRowsIncludingDuplicates -gt $resultDataTable.Rows.Count)
		{
			foreach($resultRow in $resultTable.ResultRows)
			{
				[System.Data.DataRow]$myrow = $resultDataTable.NewRow()
				$myrow["Title"] = $resultRow["Title"]
				$myrow["Path"] = $resultRow["Path"]
				$myrow["RefinableDate00"] = $resultRow["RefinableDate00"]
				$myrow["RefinableString00"] = $resultRow["RefinableString00"]
				$myrow["RefinableString01"] = $resultRow["RefinableString01"]
				$myrow["RefinableString03"] = $resultRow["RefinableString03"]
				$myrow["CreatedBy"] = $resultRow["CreatedBy"]
				$resultDataTable.Rows.Add($myrow)
			}
			
			$currentRowIndex = $resultDataTable.Rows.Count
			
			$resultTable = $null
			$resultTable = Get-SearchResults $currentRowIndex $MyctxTemp
			if (($resultTable -ne $null) -and ($resultTable.TotalRowsIncludingDuplicates -gt 0))
			{
				if ($resultTable.RowCount -le 0)
				{
					break
				}
			}
			else
			{
				break
			}
		}
	}
	[string] $totalResults = $resultDataTable.Rows.Count;
	Write-Host "     >>>>> Table Items placed into Datatable: ", $totalResults -ForegroundColor Yellow

	Clear-Content $CSVFileName
	$outputline = '"NewsTitle";"PublicationDate";"NewsURL";"BusinessFunction";"NewsType";"PublishingAreaCountry";"NewsCreator";'
	Add-Content -Encoding UTF8 -Force $CSVFileName $outputline   

	foreach($result in $resultDataTable.Rows)
	{
		if($result["RefinableDate00"] -ne "")
		{
			$TempString = $result["RefinableDate00"].split(';')[0]
			$PortalPublishingDate=[datetime]::ParseExact([string]$TempString, 'M/d/yyyy h:mm:ss tt', [CultureInfo]::InvariantCulture) #10/2/2018 10:00:00 PM
	 
			Write-Host "  ---------------------------------------- "
			Write-Host " ------>>> NewsPath:", $result["Path"]
			Write-Host " ------>>> Title:", $result["Title"] 
			Write-Host " ------>>> RefinableDate00:",  $result["RefinableDate00"] #$PortalPublishingDate
			Write-Host " ------>>> PublicationDate:",  $PortalPublishingDate
			Write-Host " ------>>> BusinessFunction:", $result["RefinableString00"]
			Write-Host " ------>>> NewsType:", $result["RefinableString01"]
			Write-Host " ------>>> PublishingAreaCountry:", $result["RefinableString03"]
			Write-Host " ------>>> NewsCreator:", $result["CreatedBy"]
			Write-Host "  ---------------------------------------- "

			#CSV file location, to store the result
			$outputline = '"'+ $result["Title"] +'";"'+ $PortalPublishingDate.ToString("dd.MM.yyyy") +'";"'+ $result["Path"] +'";"'+ $result["RefinableString00"] +'";"'+ $result["RefinableString01"] +'";"'+ $result["RefinableString03"] +'";"'+ $result["CreatedBy"] +'";'
			Add-Content -Encoding UTF8 -Force $CSVFileName $outputline
		}
	}
}

# ---------------------------------------------------------------------------------------------------------------
Load-DLLandAssemblies

#get and save your O365 credentials
[string]$username = "loginadmin@yourtenant.onmicrosoft.com"
[string]$PwdTXTPath = "C:\SECUREDPWD\ExportedPWD-$($username).txt"
$secureStringPwd = ConvertTo-SecureString -string (Get-Content $PwdTXTPath)
$cred = New-Object System.Management.Automation.PSCredential -ArgumentList $username, $secureStringPwd

#connect to the web site using the stored credentials
Write-host " "
Write-host " -------------------------------------------------------------------------------------------- " -ForegroundColor green
Write-host " ---- CONNECT THE SITE --- " -ForegroundColor green
Write-host "   CONNECTED SITE:", $SitePagesURL  -ForegroundColor Yellow

$Myctx = New-Object Microsoft.SharePoint.Client.ClientContext($SitePagesURL +"/intranet")

$Myctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($cred.UserName,$cred.Password)
$Myctx.RequestTimeout = 1000000 # milliseconds
$MyspoRootweb = $Myctx.Web
$Myctx.Load($MyspoRootweb)
$Myctx.ExecuteQuery()

Write-Host " "
Write-Host " ---------------------------------------------------------"
Write-Host "  >>>> # Server Version:" $Myctx.ServerVersion " # <<<<<<" -ForegroundColor Green 
Write-Host " ---------------------------------------------------------"
Write-Host " "

Write-host " -------------------------------------------------------- "
Write-host "   -->> RootSite:", $MyspoRootweb.URL -ForegroundColor green

Write-host " "

Get-All-Intranet-News-Published-ExportCSV $Myctx $MyspoRootweb

 

The solution was used on my production site with thousands of news published and give us now statistics for the last years of that Intranet site.

 

Fabrice Romelard

 

French version of that message:

 

The source sites used to build that script are:

 

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
11 Replies