In your SharePoint travels you may come across a requirement to have
users review documents based on a timeframe. For instance, the
requirement may say something like:
query a document library for documents that fall within a date range and then notify the document's owner that they must review the document.
###############################################################################
## ADD IN SHAREPOINT SNAP IN IF NOT ALREADY LOADED ##
###############################################################################
if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) {
Add-PSSnapin "Microsoft.SharePoint.PowerShell"
}
###############################################################################
## VARIABLES ##
###############################################################################
$webUrl = "http://sp2010"
$listDisplayName = "Annual Review Documents"
$dateColumnInternalName = "Review_x0020_Date"
$ownerColumnInternalName = "Document_x0020_Owner"
$reviewDateDaysOut = 30
$startDateRangeDays = 29
$endDateRangeDays = 31
$smtpServer = "191.168.1.1"
$emailFromAddress = "annualreview@dev.com"
$emailSubjectLine = "Annual Review Documents Notification"
# DO NOT CHANGE THE FOLLOWING VARIABLES!
$dateToStringFormat = "yyyy'-'MM'-'dd HH':'mm':'ss'Z'"
$logTextFileName = ".\log-" + (Get-date).ToString($dateToStringFormat) + ".txt" -replace " ","-" -replace ":","-"
$reviewDate = (Get-date).AddDays($reviewDateDaysOut).ToShortDateString()
$viewFilter = "?FilterField1=" + $dateColumnInternalName + "&FilterValue1=" + $reviewDate
###############################################################################
## FUNCTIONS ##
###############################################################################
# Sends an email to the specified address
function SendEmail($ownerEmail, $docLibraryViewUrl, $documentName) {
try {
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$msg = EmailStructure $ownerEmail $docLibraryViewUrl
$documentUrl
$smtp.Send($msg)
$emailSentMessage = "Email sent to " + $ownerEmail
AppendLogMessage $emailSentMessage
}
catch {
Write-Host "Error sending email, check log file!" -ForegroundColor Red
AppendLogMessage $error[0]
}
}
# Builds the email message that will be sent
function EmailStructure($to, $docLibraryViewUrl
) {
$msg = new-object Net.Mail.MailMessage
$msg.IsBodyHtml = $true
$msg.From = $emailFromAddress
$msg.To.Add($to)
$msg.Subject = $emailSubjectLine
$msg.Body = "<html><body><b>Review items for $listDisplayName<br /><br /></b>The following document ($documentName) has a review date of $reviewDate.<br /><br />Click <a href = '"+ $docLibraryViewUrl + $viewFilter +"'>here</a> to review all documents which have a review date on $reviewDate.</body></html>"
return $msg
}
# Appends text to the log message
function AppendLogMessage($text) {
Add-Content $logTextFileName $text"`r" -Encoding UTF8
}
###############################################################################
## CREATE LOG FILE ##
###############################################################################
# Creating base file to append to, adding a new line for readability
"Log file created." >> $logTextFileName
AppendLogMessage " "
###############################################################################
## WEB AND DOCUMENT LIBRARY ##
###############################################################################
# Get the access to SP lib, open it
$web = Get-SPWeb $webUrl
$docLib = $web.Lists[$listDisplayName]
###############################################################################
## SET DATE RANGE ##
###############################################################################
# Set the date range
$reviewStartDate = (Get-date).AddDays($startDateRangeDays).ToString($dateToStringFormat)
$reviewEndDate = (Get-date).AddDays($endDateRangeDays).ToString($dateToStringFormat)
# Write out the dates we're looking for
$dateRangeText = "INFO: Searching for documents with review dates between " + $reviewStartDate + " - " + $reviewEndDate
Write-Host $dateRangeText -ForegroundColor Green
AppendLogMessage $dateRangeText
###############################################################################
## QUERY THE DOCUMENT LIBRARY ##
###############################################################################
# Build the SPQuery
$camlQuery = '<Where><And><Gt><FieldRef Name="' + $dateColumnInternalName + '" /><Value Type="DateTime" IncludeTimeValue="True">' + $reviewStartDate + '</Value></Gt><Lt><FieldRef Name="' + $dateColumnInternalName + '" /><Value Type="DateTime" IncludeTimeValue="True">' + $reviewEndDate + '</Value></Lt></And></Where>'
$spQuery = new-object Microsoft.SharePoint.SPQuery
$spQuery.Query = $camlQuery
$spListItems = $docLib.GetItems($spQuery)
# Write out the number of documents found
$docFoundText = "INFO: Found " + $spListItems.Count + " document(s)."
Write-Host $docFoundText -ForegroundColor Green
AppendLogMessage $docFoundText
###############################################################################
## PROCESS DOCUMENTS AND SEND EMAILS ##
###############################################################################
# Log start of processing
$beginProcessingText = "INFO: Start processing documents."
Write-Host $beginProcessingText -ForegroundColor Green
AppendLogMessage $beginProcessingText
# Loop through items and write out info
foreach ($item in $spListItems) {
# Get the SPUser object from the column
$spFieldUser = [Microsoft.SharePoint.SPFieldUser]$item.Fields.GetField($ownerColumnInternalName);
$spFieldUserValue = [Microsoft.SharePoint.SPFieldUserValue]$spFieldUser.GetFieldValue($item[$ownerColumnInternalName].ToString());
$user = $spFieldUserValue.User;
# Get the user email to test for null/empty
$userEmail = user.Email
# If the User's email is present send the email, otherwise log the error
if ($userEmail) {
# Build URL for item
$docLibraryViewUrl = $web.Url + "/" + $docLib.DefaultView.Url
$ownerDocEmail = "INFO: Document '" + $item.Name + "' has owner " + $userEmail
AppendLogMessage $ownerDocEmail
SendEmail $userEmail $docLibraryViewUrl $item.Name
}
else {
$emptyDocEmail = "ERROR: No email is set for '" + $item.Name + "' with document owner: " + $user.DisplayName
Write-Host $emptyDocEmail -ForegroundColor Red
AppendLogMessage $emptyDocEmail
}
}
###############################################################################
## CLEAN UP ##
###############################################################################
# Log end of processing
$endProcessingText = "INFO: Finished processing documents."
Write-Host $endProcessingText -ForegroundColor Green
AppendLogMessage $endProcessingText
# Dispose of the web object
$web.Dispose()
"Users must review documents every 12 months to ensureThis can be accomplished in a number of different ways in SharePoint, but in this script will accomplish this using PowerShell. This demo will show you how to
content is correct and up to date. Etc, etc."
query a document library for documents that fall within a date range and then notify the document's owner that they must review the document.
###############################################################################
## ADD IN SHAREPOINT SNAP IN IF NOT ALREADY LOADED ##
###############################################################################
if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) {
Add-PSSnapin "Microsoft.SharePoint.PowerShell"
}
###############################################################################
## VARIABLES ##
###############################################################################
$webUrl = "http://sp2010"
$listDisplayName = "Annual Review Documents"
$dateColumnInternalName = "Review_x0020_Date"
$ownerColumnInternalName = "Document_x0020_Owner"
$reviewDateDaysOut = 30
$startDateRangeDays = 29
$endDateRangeDays = 31
$smtpServer = "191.168.1.1"
$emailFromAddress = "annualreview@dev.com"
$emailSubjectLine = "Annual Review Documents Notification"
# DO NOT CHANGE THE FOLLOWING VARIABLES!
$dateToStringFormat = "yyyy'-'MM'-'dd HH':'mm':'ss'Z'"
$logTextFileName = ".\log-" + (Get-date).ToString($dateToStringFormat) + ".txt" -replace " ","-" -replace ":","-"
$reviewDate = (Get-date).AddDays($reviewDateDaysOut).ToShortDateString()
$viewFilter = "?FilterField1=" + $dateColumnInternalName + "&FilterValue1=" + $reviewDate
###############################################################################
## FUNCTIONS ##
###############################################################################
# Sends an email to the specified address
function SendEmail($ownerEmail, $docLibraryViewUrl, $documentName) {
try {
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$msg = EmailStructure $ownerEmail $docLibraryViewUrl
$documentUrl
$smtp.Send($msg)
$emailSentMessage = "Email sent to " + $ownerEmail
AppendLogMessage $emailSentMessage
}
catch {
Write-Host "Error sending email, check log file!" -ForegroundColor Red
AppendLogMessage $error[0]
}
}
# Builds the email message that will be sent
function EmailStructure($to, $docLibraryViewUrl
) {
$msg = new-object Net.Mail.MailMessage
$msg.IsBodyHtml = $true
$msg.From = $emailFromAddress
$msg.To.Add($to)
$msg.Subject = $emailSubjectLine
$msg.Body = "<html><body><b>Review items for $listDisplayName<br /><br /></b>The following document ($documentName) has a review date of $reviewDate.<br /><br />Click <a href = '"+ $docLibraryViewUrl + $viewFilter +"'>here</a> to review all documents which have a review date on $reviewDate.</body></html>"
return $msg
}
# Appends text to the log message
function AppendLogMessage($text) {
Add-Content $logTextFileName $text"`r" -Encoding UTF8
}
###############################################################################
## CREATE LOG FILE ##
###############################################################################
# Creating base file to append to, adding a new line for readability
"Log file created." >> $logTextFileName
AppendLogMessage " "
###############################################################################
## WEB AND DOCUMENT LIBRARY ##
###############################################################################
# Get the access to SP lib, open it
$web = Get-SPWeb $webUrl
$docLib = $web.Lists[$listDisplayName]
###############################################################################
## SET DATE RANGE ##
###############################################################################
# Set the date range
$reviewStartDate = (Get-date).AddDays($startDateRangeDays).ToString($dateToStringFormat)
$reviewEndDate = (Get-date).AddDays($endDateRangeDays).ToString($dateToStringFormat)
# Write out the dates we're looking for
$dateRangeText = "INFO: Searching for documents with review dates between " + $reviewStartDate + " - " + $reviewEndDate
Write-Host $dateRangeText -ForegroundColor Green
AppendLogMessage $dateRangeText
###############################################################################
## QUERY THE DOCUMENT LIBRARY ##
###############################################################################
# Build the SPQuery
$camlQuery = '<Where><And><Gt><FieldRef Name="' + $dateColumnInternalName + '" /><Value Type="DateTime" IncludeTimeValue="True">' + $reviewStartDate + '</Value></Gt><Lt><FieldRef Name="' + $dateColumnInternalName + '" /><Value Type="DateTime" IncludeTimeValue="True">' + $reviewEndDate + '</Value></Lt></And></Where>'
$spQuery = new-object Microsoft.SharePoint.SPQuery
$spQuery.Query = $camlQuery
$spListItems = $docLib.GetItems($spQuery)
# Write out the number of documents found
$docFoundText = "INFO: Found " + $spListItems.Count + " document(s)."
Write-Host $docFoundText -ForegroundColor Green
AppendLogMessage $docFoundText
###############################################################################
## PROCESS DOCUMENTS AND SEND EMAILS ##
###############################################################################
# Log start of processing
$beginProcessingText = "INFO: Start processing documents."
Write-Host $beginProcessingText -ForegroundColor Green
AppendLogMessage $beginProcessingText
# Loop through items and write out info
foreach ($item in $spListItems) {
# Get the SPUser object from the column
$spFieldUser = [Microsoft.SharePoint.SPFieldUser]$item.Fields.GetField($ownerColumnInternalName);
$spFieldUserValue = [Microsoft.SharePoint.SPFieldUserValue]$spFieldUser.GetFieldValue($item[$ownerColumnInternalName].ToString());
$user = $spFieldUserValue.User;
# Get the user email to test for null/empty
$userEmail = user.Email
# If the User's email is present send the email, otherwise log the error
if ($userEmail) {
# Build URL for item
$docLibraryViewUrl = $web.Url + "/" + $docLib.DefaultView.Url
$ownerDocEmail = "INFO: Document '" + $item.Name + "' has owner " + $userEmail
AppendLogMessage $ownerDocEmail
SendEmail $userEmail $docLibraryViewUrl $item.Name
}
else {
$emptyDocEmail = "ERROR: No email is set for '" + $item.Name + "' with document owner: " + $user.DisplayName
Write-Host $emptyDocEmail -ForegroundColor Red
AppendLogMessage $emptyDocEmail
}
}
###############################################################################
## CLEAN UP ##
###############################################################################
# Log end of processing
$endProcessingText = "INFO: Finished processing documents."
Write-Host $endProcessingText -ForegroundColor Green
AppendLogMessage $endProcessingText
# Dispose of the web object
$web.Dispose()
No comments:
Post a Comment