Get Social Comments From SharePoint 2013 OnPrem Using PowerShell CSOM

Step by step procedure to retrieve social comments from Sharepoint 2013 on premise environment

I had a requirement to get all the social comments from a news board web part for a specific page. Though this can be achieved using "Server Side Object Model," it is restricted for some of the companies to run their "SSOM" code on production environment. Thus, we came up with an idea of creating PowerShell script using CSOM to retrieve social comments using "Social Data Web Service".

 

Here you will be seeing how to get all the social comments in SharePoint 2013 using the PowerShell + CSOM for a specific page using "Social Data WebService" (SocialDataService.asmx)

 

The below code retrieves all the comments for the specified page URL and exports the resulting data to Excel.

 

Code Usage

##Create an empty System.Array object
$PageNoteBoardComments = @()

## Web Service Reference - http://Site/_vti_bin/SocialDataService.asmx
$uri="http://<sitepath>/_vti_bin/SocialDataService.asmx?wsdl"

##PageURL to be specified as input parameter of which contains social comments.
$url="http://<sitepath>/Pages/TopNews.aspx"

## if you are executing this code in a Virtual Machine it's default credentials will be used to connect to proxy.
$socialDataServiceWebServiceReference = New-WebServiceProxy -Uri $uri -UseDefaultCredential

##Get total number of comments for a specified URL
[int]$CountCommentsOnUrl=$socialDataServiceWebServiceReference.CountCommentsOnUrl($url)

Write-Host -ForegroundColor Green "Number of comments for the specified URL : " $CountCommentsOnUrl

##Get Comments for the URL Specified
$CommentsOnUrl = $socialDataServiceWebServiceReference.GetCommentsOnUrl($url,$null,$null,$null)

##Iterate on the comments collection and store the value in PageNoteBoardComments array object.
foreach($item in $CommentsOnUrl)
{

 $PageNoteBoardComments += New-Object
 PsObject -property @{

   'Comments' = $item.Comment

   'Last Modified Time' = $item.LastModifiedTime

   'URL' = [string]$item.Url

   'Owner' = [string]$item.Owner

   'Page Title' = [string]$item.Title
   }
}

##Finally, use Export-Csv to export the data to a csv file
$PageNoteBoardComments | Export-Csv -NoTypeInformation -Path "D:\Madhu\Powershell\NoteBoardComments.csv"

Scenario 2

To iterate all the page URL's from excel and to retrieve all the social comments from social data web service based on page URL.

Note
All the Page URL's are stored under "PageUrl" column in excel sheet.

#Path to the CSV output path file.
   $CSVOutputfilepath = "D:\Madhu\GetSocialComments.csv"

   #Path to the log file.
   $outputfilepath = "D:\Madhu\GetMySocialCommentsLogs.txt"

   #Excel File path.
   $FilePath = "D:\Madhu\pagelist.xls"

   #Create an Object Excel.Application using Com interface
   $objExcel = New-Object -ComObject Excel.Application

   # Disable the 'visible' property so the document won't open in excel
   $objExcel.Visible = $False

   # Open the Excel file and save it in $WorkBook
   $WorkBook = $objExcel.Workbooks.Open($FilePath)


   # Load First sheet
   $sheet = $WorkBook.Worksheets.Item(1)


   #Find PageUrl and get the column position
   $pageurlcolumns = $sheet.UsedRange.Columns.Find('PageUrl').Column

   #Get all the Rows within Excel
   $rows = $sheet.UsedRange.Rows.Count

   ## Get number of user in excel
   $rowCount  = $rows - 1

   "-"*80 | Write-Host
   Write-Host -ForegroundColor Green "Total number of the pages initiated : " $rowCount
   Write-Host -ForegroundColor Green "Started at : $((Get-Date).ToString())"
   "-"*80 | Write-Host

   "-"*80 | Out-File -Encoding Ascii -append $outputfilepath
   "Total number of the pages initiated : " + $rowCount | Out-File -Encoding Ascii -append $outputfilepath
   "Started at :" + $((Get-Date).ToString()) | Out-File -Encoding Ascii -append $outputfilepath
   "-"*80 | Out-File -Encoding Ascii -append $outputfilepath

   ## Get Page Number Count
   $count = 1;

   # loop for each row of the excel file
   for ($i=2; $i -le $rows; $i++)
   {
        try{

               #Get PageUrl column values
               $filePath = $sheet.Cells.Item($i, $pageurlcolumns).text

               #Log the result to log file
               Write-Host -ForegroundColor Green "$count : " $filePath
               "$count : " + $filePath | Out-File -Encoding Ascii -append $outputfilepath

               ## $url is a string that contains the URL from where we need to count the social comments
               ## list contains the pages from pages library and hence we got a substring by spliting the url.
               $url=$filePath.Substring(0,$filePath.IndexOf('/Pages'))

               ## Get path of socail comments webservice file
               $uri = $url + "/_vti_bin/SocialDataService.asmx"

               ## Web Service Reference - http://Site/_vti_bin/SocialDataService.asmx
               $socialDataServiceWebServiceReference = New-WebServiceProxy -Uri $uri -UseDefaultCredential

               ##Retrieve number of comments for a specific page.
               [int]$CountCommentsOnUrl=$socialDataServiceWebServiceReference.CountCommentsOnUrl($filePath)
               Write-Host -ForegroundColor yellow "Number of comments for the specified URL : " $CountCommentsOnUrl
               "Number of comments for the specified URL : " +  $CountCommentsOnUrl | Out-File -Encoding Ascii -append $outputfilepath

               ##Retrieve comments for a specific page.
               $CommentsOnUrl = $socialDataServiceWebServiceReference.GetCommentsOnUrl($filePath,$null,$null,$null)

               foreach($item in $CommentsOnUrl)
               {
                       $PageNoteBoardComments += New-Object PsObject -property @{
                           'Comments' = $item.Comment
                           'URL' = [string]$item.Url
                           'Last Modified Time' = $item.LastModifiedTime
                           'Owner' = [string]$item.Owner

                        }

                         ##Add comments for a specific page.
                         #$WriteCommentsOnUrl = $socialDataServiceWebServiceReference.AddComment($url,$item.Comment,0,"TestMultiline")

               }

               $count++
            }
       catch{
           Write-Host -ForegroundColor Green "$accountID : " $_.Exception.Message
           "$accountID : " + $_.Exception.Message | Out-File -Encoding Ascii -append $outputfilepath
       }

   }

   #Finally, use Export-Csv to export the data to a csv file
   $PageNoteBoardComments | Export-Csv -NoTypeInformation -Path $$CSVOutputfilepath

   "-"*80 | Write-Host
   "-"*80 | Out-File -Encoding Ascii -append $outputfilepath
   Write-Host -ForegroundColor Green "Completed at : $((Get-Date).ToString())"
   "Completed at : " +  $((Get-Date).ToString()) | Out-File -Encoding Ascii -append $outputfilepath
   Write-Host -ForegroundColor Green "Total Comments Found : " $PageNoteBoardComments.Count
   "Total Comments Found : " + $PageNoteBoardComments.Count | Out-File -Encoding Ascii -append $outputfilepath
   "-"*80 | Write-Host
   "-"*80 | Out-File -Encoding Ascii -append $outputfilepath

   #Close the workbook object
   $WorkBook.close()

   #close the excel object
   $objexcel.quit()

Please feel free to share your comments.

 

I hope this helps!!!!!