SharePoint 2013 Hosting – HostForLIFE.eu :: [SharePoint] Get Social Comments From SharePoint 2013 OnPrem Using PowerShell CSOM

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"

##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()

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

#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()

SharePoint 2013 Hosting Recommendation

HostForLIFE.eu’s SharePoint 2013 Hosting solution offers a comprehensive feature set that is easy-to-use for new users, yet powerful enough for the most demanding web developer expert. Hosted SharePoint Foundation 2013 is the premiere web-based collaboration and productivity enhancement tool on the market today. With SharePoint 2013 Foundation, you can quickly access and manage documents and information anytime, anywhere though a Web browser in a secure and user friendly way. SharePoint hosting services start at only at €9.99/mo, allowing you to take advantage of the robust feature set for a small business price. HostForLIFE.eu offers a variety of hosted SharePoint Foundation 2013 plans as well as dedicated SharePoint 2013 Foundation options.

SharePoint 2013 Hosting – HostForLIFE.eu :: [SharePoint] Get Social Comments From SharePoint 2013 OnPrem Using PowerShell CSOM

SharePoint 2013 Hosting Recommendation

BLOGROLL

Featured On

SharePoint 2013 Hosting – HostForLIFE.eu :: [SharePoint] Get Social Comments From SharePoint 2013 OnPrem Using PowerShell CSOM

SharePoint 2013 Hosting Recommendation

BLOGROLL

Featured On

Tags