SharePoint Workflow and PowerShell Report

Hi everyone,

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, I 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.


Add-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue

Next, you want to connect to your list:


$weburl = "https://mysite.contoso.com/HumanResource"

$web = get-spweb $WebUrl

$list = $web.GetList("https://mysite.contoso.com/HumanResource/Lists/New%20Hire%20Tasks/AllItems.aspx")

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:


$FirstItem = $List[0]

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:

$FirstItem["DueDate"]

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:

$myField=$FirstItem["Task Status"].ToString()
$userfield = New-Object Microsoft.SharePoint.SPFieldUserValue($web,$myField.ToString());
$userfield.User.DisplayName;
$userfield.User.Email;

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:


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

Now the sky is the limit. You can do any kind of crazy thing using the above commands 🙂 Enjoy.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s