SharePoint Workflow and PowerShell Report
If you have ever worked on SharePoint workflows, you will notice that sometimes your task list gets crowded with many entries, and you want a way to query that task list to get some information, like number of pending tasks, tasks overdue, or any other kind of information.
For example, you may want to create a PowerShell script to query a specific task list, and send weekly report to each user about his pending tasks via email in a nicely formatted HTML table. While SharePoint workflow task actions can send overdue emails, it will do that per task, and not by aggregating all pending tasks per user.
People do not want to get email for each pending task, they want instead to have a weekly or daily email showing all their pending tasks. You can do anything once you know how to interact with SharePoint lists using PowerShell.
I will not give you a ready PowerShell script to do all the magic, but instead, I will show you how to start doing that, and how to use PowerShell to get the essential data. You can then use your PowerShell skills to do whatever you want with it (Send email, create an HTML table, aggregate data..).
First of all, you must load the SharePoint snap-in inside your PowerShell host environment.
[code language="PowerShell"]
Add-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue
[/code]
Next, you want to connect to your list:
[code language="PowerShell"]
$weburl = "https://mysite.contoso.com/HumanResource"
$web = get-spweb $WebUrl
$list = $web.GetList("https://mysite.contoso.com/HumanResource/Lists/New%20Hire%20Tasks/AllItems.aspx")
[/code]
here my SharePoint site is https://mysite.contoso.com/HumanResource while my list URL is the one I used in the last command.
Now that we are connected to the list using $List variable, $List is an array of all items in that list. So for example, to get the first item of the list:
[code language="PowerShell"]
$FirstItem = $List[0]
[/code]
This will return to us the first item of the list, and since we are talking about task list, we can expect this item to have all the columns in a typical task list.
To get this tasks' due date:
[code language="PowerShell"]
$FirstItem["DueDate"]
[/code]
Now we can go and get task assignee for this task. This can be tricky as this field is of type (People or Group), and you may need the user/Group ID, or perhaps the email address in case you want to send them email notifications about their pending tasks:
[code language="PowerShell"]
$myField=$FirstItem["Task Status"].ToString()
$userfield = New-Object Microsoft.SharePoint.SPFieldUserValue($web,$myField.ToString());
$userfield.User.DisplayName;
$userfield.User.Email;
[/code]
This will give you the DisplayName and email address for task assignee. Although it says User.DisplayName and User.Email, even if the task assignee is a group, it will bring the group email and displayname also.
Finally, you can also do some cool stuff. Instead of getting all the task list items and store them in the $List variables and then looping through those tasks, it is much easier if you only get the items you want. Suppose you only want to process tasks not completed and due. To do this, you will use a CAMEL query to query only those tasks. Here is an example:
[code language="PowerShell"]
#We will set the Enddate to today
$EndDate = [Microsoft.SharePoint.Utilities.SPUtility]::CreateISO8601DateTimeFromSystemDateTime([DateTime]::Now)
#we will define a query that will get only tasks that are NOT Completed and DueDate less then Today
$caml='<Where><And><Neq><FieldRef Name="Status"/><Value Type="Text">Completed</Value></Neq><Lt><FieldRef Name="DueDate"/><Value Type="DateTime">{0}</Value></Lt></And></Where> ' -f $EndDate
$query=new-object Microsoft.SharePoint.SPQuery
$query.Query=$caml
#Then will only get list items matching the query we did
$ListItems=$list.GetItems($query)
[/code]
Now the sky is the limit. You can do any kind of crazy thing using the above commands :) Enjoy.