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
  1. ##Create an empty System.Array object
  2. $PageNoteBoardComments = @()
  4. ## Web Service Reference - http://Site/_vti_bin/SocialDataService.asmx
  5. $uri="http://<sitepath>/_vti_bin/SocialDataService.asmx?wsdl"
  7. ##PageURL to be specified as input parameter of which contains social comments.
  8. $url="http://<sitepath>/Pages/TopNews.aspx"
  10. ## if you are executing this code in a Virtual Machine it's default credentials will be used to connect to proxy.
  11. $socialDataServiceWebServiceReference = New-WebServiceProxy -Uri $uri -UseDefaultCredential
  13. ##Get total number of comments for a specified URL
  14. [int]$CountCommentsOnUrl=$socialDataServiceWebServiceReference.CountCommentsOnUrl($url)
  16. Write-Host -ForegroundColor Green "Number of comments for the specified URL : " $CountCommentsOnUrl
  18. ##Get Comments for the URL Specified
  19. $CommentsOnUrl = $socialDataServiceWebServiceReference.GetCommentsOnUrl($url,$null,$null,$null)
  21. ##Iterate on the comments collection and store the value in PageNoteBoardComments array object.
  22. foreach($item in $CommentsOnUrl)
  23. {
  25. $PageNoteBoardComments += New-Object
  26. PsObject -property @{
  28. 'Comments' = $item.Comment
  30. 'Last Modified Time' = $item.LastModifiedTime
  32. 'URL' = [string]$item.Url
  34. 'Owner' = [string]$item.Owner
  36. 'Page Title' = [string]$item.Title
  37. }
  38. }
  40. ##Finally, use Export-Csv to export the data to a csv file
  41. $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.
  1. #Path to the CSV output path file.
  2. $CSVOutputfilepath = "D:\Madhu\GetSocialComments.csv"
  4. #Path to the log file.
  5. $outputfilepath = "D:\Madhu\GetMySocialCommentsLogs.txt"
  7. #Excel File path.
  8. $FilePath = "D:\Madhu\pagelist.xls"
  10. #Create an Object Excel.Application using Com interface
  11. $objExcel = New-Object -ComObject Excel.Application
  13. # Disable the 'visible' property so the document won't open in excel
  14. $objExcel.Visible = $False
  16. # Open the Excel file and save it in $WorkBook
  17. $WorkBook = $objExcel.Workbooks.Open($FilePath)
  20. # Load First sheet
  21. $sheet = $WorkBook.Worksheets.Item(1)
  24. #Find PageUrl and get the column position
  25. $pageurlcolumns = $sheet.UsedRange.Columns.Find('PageUrl').Column
  27. #Get all the Rows within Excel
  28. $rows = $sheet.UsedRange.Rows.Count
  30. ## Get number of user in excel
  31. $rowCount = $rows - 1
  33. "-"*80 | Write-Host
  34. Write-Host -ForegroundColor Green "Total number of the pages initiated : " $rowCount
  35. Write-Host -ForegroundColor Green "Started at : $((Get-Date).ToString())"
  36. "-"*80 | Write-Host
  38. "-"*80 | Out-File -Encoding Ascii -append $outputfilepath
  39. "Total number of the pages initiated : " + $rowCount | Out-File -Encoding Ascii -append $outputfilepath
  40. "Started at :" + $((Get-Date).ToString()) | Out-File -Encoding Ascii -append $outputfilepath
  41. "-"*80 | Out-File -Encoding Ascii -append $outputfilepath
  43. ## Get Page Number Count
  44. $count = 1;
  46. # loop for each row of the excel file
  47. for ($i=2; $i -le $rows; $i++)
  48. {
  49. try{
  51. #Get PageUrl column values
  52. $filePath = $sheet.Cells.Item($i, $pageurlcolumns).text
  54. #Log the result to log file
  55. Write-Host -ForegroundColor Green "$count : " $filePath
  56. "$count : " + $filePath | Out-File -Encoding Ascii -append $outputfilepath
  58. ## $url is a string that contains the URL from where we need to count the social comments
  59. ## list contains the pages from pages library and hence we got a substring by spliting the url.
  60. $url=$filePath.Substring(0,$filePath.IndexOf('/Pages'))
  62. ## Get path of socail comments webservice file
  63. $uri = $url + "/_vti_bin/SocialDataService.asmx"
  65. ## Web Service Reference - http://Site/_vti_bin/SocialDataService.asmx
  66. $socialDataServiceWebServiceReference = New-WebServiceProxy -Uri $uri -UseDefaultCredential
  68. ##Retrieve number of comments for a specific page.
  69. [int]$CountCommentsOnUrl=$socialDataServiceWebServiceReference.CountCommentsOnUrl($filePath)
  70. Write-Host -ForegroundColor yellow "Number of comments for the specified URL : " $CountCommentsOnUrl
  71. "Number of comments for the specified URL : " + $CountCommentsOnUrl | Out-File -Encoding Ascii -append $outputfilepath
  73. ##Retrieve comments for a specific page.
  74. $CommentsOnUrl = $socialDataServiceWebServiceReference.GetCommentsOnUrl($filePath,$null,$null,$null)
  76. foreach($item in $CommentsOnUrl)
  77. {
  78. $PageNoteBoardComments += New-Object PsObject -property @{
  79. 'Comments' = $item.Comment
  80. 'URL' = [string]$item.Url
  81. 'Last Modified Time' = $item.LastModifiedTime
  82. 'Owner' = [string]$item.Owner
  84. }
  86. ##Add comments for a specific page.
  87. #$WriteCommentsOnUrl = $socialDataServiceWebServiceReference.AddComment($url,$item.Comment,0,"TestMultiline")
  89. }
  91. $count++
  92. }
  93. catch{
  94. Write-Host -ForegroundColor Green "$accountID : " $_.Exception.Message
  95. "$accountID : " + $_.Exception.Message | Out-File -Encoding Ascii -append $outputfilepath
  96. }
  98. }
  100. #Finally, use Export-Csv to export the data to a csv file
  101. $PageNoteBoardComments | Export-Csv -NoTypeInformation -Path $$CSVOutputfilepath
  103. "-"*80 | Write-Host
  104. "-"*80 | Out-File -Encoding Ascii -append $outputfilepath
  105. Write-Host -ForegroundColor Green "Completed at : $((Get-Date).ToString())"
  106. "Completed at : " + $((Get-Date).ToString()) | Out-File -Encoding Ascii -append $outputfilepath
  107. Write-Host -ForegroundColor Green "Total Comments Found : " $PageNoteBoardComments.Count
  108. "Total Comments Found : " + $PageNoteBoardComments.Count | Out-File -Encoding Ascii -append $outputfilepath
  109. "-"*80 | Write-Host
  110. "-"*80 | Out-File -Encoding Ascii -append $outputfilepath
  112. #Close the workbook object
  113. $WorkBook.close()
  115. #close the excel object
  116. $objexcel.quit()
Please feel free to share your comments.
I hope this helps!!!!!