Develop your own project systems using Free Tools, just add labor
At this point in the series, we have the basic elements of a status-based workflow structure in place. In this topic, we will be exploring the use of PowerShell with Sharepoint - both as external scripts to run on the server, as well as in custom workflows. We will cover the following topics as examples of using PowerShell to work with Sharepoint:
- Dump any Sharepoint List [server script]
- Conditional Mass-update of a List [server script]
- Traverse folder structure to test or update values [server script]
- Create folder structure in Document Library [workflow script]
- Update multiple items in other list(s) [workflow script]
Advanced Workflows with PowerShell
Before we begin, you will need to have Sharepoint Designer PowerActivity by iLoveSharepoint installed on your Sharepoint server. This will allow you to run PowerShell scripts from within your Sharepoint custom workflows.
You may also find it handy to install Sharepoint Designer Actions, which provides the following actions in Sharepoint Designer:
- Call a WebService (supports SOAP 1.1 and SOAP 1.2)
- Query XML
- Execute SQL (supports ADO Provider Model, returns a scalar value)
- Get parent folder (returns the ID of the folder)
- Create a site
- Start workflow
- Set role inheritance of an item
- Clear role assignments of an item
- Assign role to an item
Tip: I use the Start Workflow action to help simplify code maintenance.
Dump any Sharepoint List [server script]
This script shows the basic elements required to use PowerShell to query and manipulate Sharepoint data from a server-side script. We will describe each section of the script as we go through it.
Include Sharepoint .NET Assemblies
You must include the following text near the top of your script (server-sidePS1 scripts only, not in Workflows). This loads the Sharepoint object library definitions so we can work with them.
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Sharepoint")
Set the site URL
Because we will be using the site URL in many places in the script, it makes sense to define a a variable and set it once:
$siteUrl = "http://mysharepointsite.com"
Initiate the Site object
This populates a SPSite object variable with the object details for the Sharepoint site
$mySite=new-object Microsoft.Sharepoint.SPSite($siteUrl)
Initiate the Web object
The Web object will be used often in the Sharepoint PowerShell scripts.
$web = $mySite.openweb()
Specify the Named List
Specify list name as it shows in the URL. %20 will be needed for any spaces
$ListToDump = "Projects"
For example, if you are in Sharepoint with the list open, the URL at the top of the screen will look something like the following:
http://mysharepointsite.com/Lists/Projects/AOpen%20Items%20Queue.aspx?View={916C686B-2607-473B-AC29-F825E9B6D185}&FilterField1=Customer&FilterValue1=xxxxxxxx
The highlighed section in yellow is the site base URL, the /Lists/ part indicates we are working with lists, and the Projects name highlighted indicates the list name in Sharepoint - this may not be the exact name as what you see, especially if there is punctuation in the visible list name, so always check the URL when you have the list open. The rest of the URL indicates the view and object name.
In the next example, you see that any spaces in a list name are replaced with %20 in standard web URL fashion.
http://mysharepointsite.com/Lists/Project%20Assignments/My%20Active%20Assignments.aspx?View={8F14253B-0168-40B5-B20E-678617939656}&FilterField1=LinkTitle&FilterValue1=Revision%20Repair%20Required
Fetch the List Object
We use the $web.GetList function with the URL and list name to open the list object.
$list = $web.GetList($siteUrl + "/Lists/" + $ListToDump)
Define query to work with List Contents
This initializes the variable as a SPQuery type object.
$qry = new-object Microsoft.Sharepoint.SPQuery
Fetch the items in the list into a Lists collection object
This will return a collection of all items in the list, that we can then loop through.
$items = $web.Lists[$list].GetItems($qry)
Loop through the List, one Item at a time
The example below shows how to loop through the $items list object (in no prescribed order), outputting selected field values for each item in the list, including the key value from a lookup field into another list.
Tip: You can reference field values in Sharepoint objects by specifying the object variable name, and then the field name in quotes, surrounded by square brackets, i.e.
$Listitem["Title"]
As we will see later, it will be necessary to be able to extract the lookup key values in order to work with other lists in the same script.
# Process the list
foreach ($Listitem in $items)
{
# Only printing specific fields instead of full object dump
$Listitem["Title"]
$Listitem["ID"]
# This is how to extract the ID field from a
# lookup reference to another list
$XX = $Listitem["Status"]
# This returns a value of the format nn;Text
$XX
$Status_ID = $XX.SubString(0,$XX.IndexOf(";",0))
# This is the FK ID (nn) into the other list
$Status_ID
# You can now use this numeric key field value for operations
# when working with multiple lists at one time.
"--------------------------------------------------------"
} # foreach
Note that this example is fairly simple, in that it does not do any conditional tests, it simply dumps out information from the named list. However this can be a very useful script when you want to quickly dump the contents of a list to see record keys, etc.
Note that from a coding practice, we are not using the obvious variable choice of "$item" to step through the list. That is because, as you see later on, the PowerActivity PowerShell script initializes several variables that we will use, including $web and $item. In order to write scripts that can first be tested on the server before being utilized in the workflow (which is hard to debug), we use some standard coding practices to make the code relatively transportable from the Server-side script into the workflow, with minimal changes.
Cleanup - Garbage Collection
Although less critical in server-side scripts, the objects should be disposed when each script completes. This becomes imperative in workflow-based PowerShell scripts, as the running process never exits and you can get memory leaks, which may crash the timer process, or make it unable to start workflows until you do an IIS Restart.
Each object used should be disposed, so for this example, our cleanup would look like:
##############################################################
# CLEANUP - GARBAGE COLLECTION
# All objects should be disposed to prevent memory leaks, especially for
# workflow-based scripts
##############################################################
$siteUrl.dispose
$mySite.dispose
$web.dispose
$ListToDump.dispose
$list.dispose
$qry.dispose
$items.dispose
$Listitem.dispose
Conditional Mass-update of a Single List [server script]
This script uses the same basic structure and object variable definitions as the Dump List script. However, within the loop we will test for conditions, and if those conditions are met for the List Item, we will update it. We will use the condition test and a $doupdate flag to indicate that we should update the record, based on one or more of the tests.
In this example, we are populating "Prior" values from "current" field values if they are empty. This simulates what happens if you decide to track a field value and use it in workflow state-based decisions (i.e. is the value different from last time?). This script would then set the "initial state" so that events would not trigger unnecessarily the next time the List Item was edited.
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Sharepoint")
$siteUrl = "http://mysharepointsite.com"
$mySite=new-object Microsoft.Sharepoint.SPSite($siteUrl)
$web = $mySite.openweb()
$ListToUpdate = "Projects"
$list = $web.GetList($siteUrl + "/Lists/" + $ListToUpdate)
"Started at: "
date
# Define query object and get list of items to loop through
$qry = new-object Microsoft.Sharepoint.SPQuery
$items = $web.Lists[$list].GetItems($qry)
# Process the list
foreach ($item in $items)
{
# Initialize flag for no update
$doupdate = 0
# Print field values for debugging
$item["Title"]
$item["Estimate-Administration"]
$item["Estimate-Development"]
$item["Estimate-Testing"]
# Test if there is a "Prior" field value, and if there is a
# "current" field value but no "prior" value, then set the
# Prior value to the current value. Repeat for several fields.
if ( (($item["Estimate-Administration"] -ne $null) -and ($item["Prior Estimate-Administration"] -eq $null)))
{
# Set PRIOR to CURRENT value
$item["Prior Estimate-Administration"] = $item["Estimate-Administration"]
"Updating Prior Estimate-Administration"
# Set flag to perform update
$doupdate = 1
}
if ( (($item["Estimate-Development"] -ne $null) -and ($item["Prior Estimate-Development"] -eq $null)))
{
$item["Prior Estimate-Development"] = $item["Estimate-Development"]
"Updating Prior Estimate-Development"
$doupdate = 1
}
if ( (($item["Estimate-Testing"] -ne $null) -and ($item["Prior Estimate-Testing"] -eq $null)))
{
$item["Prior Estimate-Testing"] = $item["Estimate-Testing"]
"Updating Prior Estimate-Testing"
$doupdate = 1
}
if ( $doupdate -eq 1 )
{
### DEBUG
# During the initial stages of development the following
# is commented out so that we can make sure we are identifying
# records to update properly.
# UN-comment when you are ready to do the actual update.
# $item.update()
"NEXT>>>>>>>>>>>>>>>>"
}
} # foreach
"Ended at: "
date
Traverse folder structure to test and update values [server script]
This script uses a recursive technique to work through a nested structure such as a document library, or a list with sub-folders. This particular example copies specific field values from the parent (described in the script as "metadata") so that column filtering of those common values (i.e. customer ref#, client ref#, etc) will work as you drill down into the sub-folders.
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Sharepoint")
############################################################
function TraverseListFolder ( $folder )
{
$web = $folder.ParentWeb
$qry = new-object Microsoft.Sharepoint.SPQuery
$qry.Folder = $folder
$items = $web.Lists[$folder.ParentListId].GetItems($qry)
# Process the list
foreach ($item in $items)
{
if ($item["Project"] -eq $null)
{
# Get parent folder
if ($item.Folder -ne $null)
{
$itemP = $web.GetListItem($item.Folder.ParentFolder.ServerRelativeUrl)
}
if ($item.File -ne $null)
{
$myFile = $item.Web.GetFile($item.Url)
$itemP = $web.GetListItem($myFile.ParentFolder.ServerRelativeUrl)
}
if ($itemP["Project"] -ne $null)
{
# Update metadata from parent
"Updating metadata for folder " + $item["Name"] + " from parent folder " + $item.Folder
$item["Project"] = $itemP["Project"]
$item["Customer"] = $itemP["Customer"]
$item["Functional Area"] = $itemP["Functional Area"]
$item["Cust Ref#"] = $itemP["Cust Ref#"]
$item["Client Ref#"] = $itemP["Client Ref#"]
$item["For Product"] = $itemP["For Product"]
$item.Update()
}
}
if ($item.Folder -ne $null )
# Recursive call
{
TraverseListFolder ( $item.Folder )
}
} # foreach
#Cleanup
$web.dispose
} # Function
############################################################
# MAIN
$siteUrl = "http://mysharepointsite.com"
$mySite=new-object Microsoft.Sharepoint.SPSite($siteUrl)
$web = $mySite.openweb()
$docShare = "Deliverables%20Repository"
$list = $web.GetList($siteUrl + "/" + $docShare)
"Started at: "
date
TraverseListFolder ( $list.RootFolder )
"Started at: "
date
#############################################################
# CLEANUP - GARBAGE COLLECTION
#############################################################
$siteUrl.dispose
$mySite.dispose
$web.dispose
$docShare.dispose
$list.dispose
Create folder structure in Document Library [workflow script]
This workflow creates a standard directory structure for a project. The trigger condition for this workflow is based on specific status code(s) being met in the Project, and that the folders have not already been created. This was coded in a separate workflow for maintenance simplicity, and so that it could be called manually if needed. This section shows code examples of working with PowerShell to create folders, which cannot be done from a normal workflow action.
However, please note that the full solution is not described here, i.e. custom object type definitions etc.
We will create a new workflow called "Create Root Folder for Project". We will use the custom "Execute PowerShell Script" action:
First we need to write the script. In this (older) example, we show that you can use the String Builder lookups for data elements; we could also have written it in more portable code as above, but this provides an example of a different method. Note that this method cannot be copied-and-pasted from Sharepoint Designer (thus the screen shots), so a more portable coding method is suggested. An example of this approach follows in the next section.
Note that the $web and $item variables already exist, as they have been pre-defined by the PowerActivity action. So we could have written the update code in a more portable style like this:
$folder["BaseName"] = $item["Title"]
$folder["Project"] = $item["ID"]
etc.
We will use fields from the current list item (project record) to populate fields in the Document Library. Note that we have already pre-defined the custom fields that we want in the Document Library, under a custom "Deliverable Folder" object type.
The main folder is created, followed by the standard sub-folder structure in the following screen shots:
We are defining three sub-folders here, Specifications, Testing
and Development.
Finally, we will hook the new workflow into the Project Update workflow, where we will use the custom "Start a Workflow" action call the "Create root folder for object" workflow when the Project enters the "In Development" status:
Note: The full solution for the document library folder/metadata inheritance also incorporates PowerEventReceivers. Only selected elements of the solution have been detailed here.
Update multiple items in other list(s) [server script/workflow script]
These example scripts test for a specific status condition (i.e. project cancelled) in the main Project record(s), and then traverses several related lists (Project Assignments, Held Time), updating the status of dependent records in those lists to "cleanup/close-out" those records.
The server script and the workflow script are almost identical in logic, except that the server script loops through the Projects list, scanning all records, and the Workflow script starts with the specific Project record that has just been updated ($item). In order to develop the same logic (essentially copy-and-paste) for two versions, we will use the $item variable to represent the List Item, and $ProjectMainID to hold the ID field for this List Item.
Server-side script version "wrapper":
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Sharepoint")
$siteUrl = "http://mysharepointsite.com"
$mySite=new-object Microsoft.Sharepoint.SPSite($siteUrl)
$web = $mySite.openweb()
$Projects = "Projects"
$list = $web.GetList($siteUrl + "/Lists/" + $Projects)
$qry = new-object Microsoft.Sharepoint.SPQuery
$items = $web.Lists[$list].GetItems($qry)
# Project Assignments that are open will be closed
$ProjectAssignments = "Project%20Assignments"
$list2 = $web.GetList($siteUrl + "/Lists/" + $ProjectAssignments)
$qry2 = new-object Microsoft.Sharepoint.SPQuery
$list2Items = $web.Lists[$list2].GetItems($qry2)
#Held time that was unprocessed will be cancelled/denied
$HeldTime = "Held%20Time"
$list3 = $web.GetList($siteUrl + "/Lists/" + $HeldTime)
$qry3 = new-object Microsoft.Sharepoint.SPQuery
$list3Items = $web.Lists[$list3].GetItems($qry3)
# Process the list
foreach ($item in $items)
{ $ProjectMainID = $item["ID"]
... common logic
} # foreach
Workflow script header
$siteUrl = "http://mysharepointsite.com"
$ProjectMainID = $item["ID"]
# Project Assignments that are open will be closed
$ProjectAssignments = "Project%20Assignments"
$list2 = $web.GetList($siteUrl + "/Lists/" + $ProjectAssignments)
$qry2 = new-object Microsoft.Sharepoint.SPQuery
$list2Items = $web.Lists[$list2].GetItems($qry2)
#Held time that was unprocessed will be cancelled/denied
$HeldTime = "Held%20Time"
$list3 = $web.GetList($siteUrl + "/Lists/" + $HeldTime)
$qry3 = new-object Microsoft.Sharepoint.SPQuery
$list3Items = $web.Lists[$list3].GetItems($qry3)
... common logic
Common Logic Block
## CANCEL ASSIGNMENTS FIRST
# Process the list
foreach ($listItem in $listItems)
{
$doupdate = 0
$XX = $listItem["Project"]
$Project_ID = $XX.SubString(0,$XX.IndexOf(";",0))
#$Project = $ProjectList.GetItemByID($Project_ID)
if ( ($listItem["Status"] -ne "Complete") -and ($listItem["Status"] -ne "Cancelled") -and ($listItem["Status"] -ne "QA Test Requested [Stage 3,5]") -and ($Project_ID -eq $ProjectMainID) )
{
$listItem["Status"] = "Cancelled"
$doupdate = 1
}
if ( $doupdate -eq 1 )
{
$listItem.update()
}
} # For Each
##############################################################
# Held Time
##############################################################
## CANCEL HELD TIME
# Process the list
foreach ($listItem in $listItems)
{
$doupdate = 0
$XX = $listItem["Project"]
$Project_ID = $XX.SubString(0,$XX.IndexOf(";",0))
#$Project = $ProjectList.GetItemByID($Project_ID)
if ( ($Project_ID -eq $ProjectMainID) )
{
if (( $listItem["Status"] -eq "Pending Overage Approval") )
{
$listItem["Status"] = "Overage Denied"
$listItem["Processed"] = $true
$listItem["Rejected Hours"] = $listItem["Hours"]
$listItem["Workflow Message"] = "Denied due to Project status change to Abandoned"
$listItem["Prior Status"] = "Overage Denied"
$doupdate = 1
}
if (( $listItem["Status"] -eq "Pending Project Approval") )
{
$listItem["Status"] = "Project Cancelled"
$listItem["Processed"] = $true
$listItem["Rejected Hours"] = $listItem["Hours"]
$listItem["Workflow Message"] = "Denied due to Project status change to Abandoned"
$listItem["Prior Status"] = "Project Cancelled"
$doupdate = 1
}
}
if ( $doupdate -eq 1 )
{
$listItem.update()
}
} # For Each
(Update) Important Note on Working With Fields
In Powershell, although you can reference any field in the list in your script, you can only compare retrieve values from "static" fields - that is, you cannot use calculation fields. PowerShell will not complain - but you will not get results in your script. This is because the .Net library for Sharepoint will not do the field calculation for you - that only happens inside the Sharepoint UI itself.
If you need to have access to a "calculated" field, you actually need to have two fields - the calculated field (usually hidden) and a "stored result" field, which must be updated from the calculated value in the last step of the "Update" workflow. Then you can use the "stored value" field in PowerShell - and also, incidentally, in View calculations in Sharepoint.
That's it for this session. I hope you found it helpful.
File Downloads
Here are the file downloads from this session. Note that the downloads are suffixed ".txt" to prevent any potential browser download issues with executable scripts. To run these on your server, they should be renamed to ".PS1" suffix first.
Coming next:
Part 7: Hosting tips, Email & Notifications
Part 8: Doing it Yourself - Recommendations
Part 9: Using what you've built - more free enhancements
Go back to:
Part 1: The Need
Part 2: Designing the Solution
Part 3: Building the Structure - Sharepoint Lists
Part 4: Process Workflow Design
Part 5: Implementing Custom Workflows with Sharepoint Designer
For more information or questions, contact:
Gary Nelson, PMP
www.gazzaconsulting.com
