Tag Archives: SharePoint 2013

SharePoint Surveys and the mystery of the missing partial response

Note: this applies to SharePoint 2010 and 2013, it is not viable for SharePoint online / Office 365.

Fun fact, SharePoint, if you’ve got branching logic in your survey, allows you to save your response to a survey!

This is great news. However on TechNet someone asked if it were possible to find these incomplete posts to remind the user that they haven’t finished?

Normally you’d assume that all you’d need to do is log in with a site collection admin account and you’d magically be able to see all the responses, even those that haven’t been completed. However in this case you’d be wrong. Even with Site Collection admin rights those incomplete responses are hidden from you.

Now that data is still in the SharePoint list and rather than mess about with permissions and settings, which could have unforeseen consequences, let’s see if we can’t pull it out of the list with PowerShell?

The first thing to do is see if we can get the real number of items from the survey. The easiest way to do that is to check the item count.

$web = Get-SPWeb "URL"
$survey = $web.Lists["SurveyName"]
Write-Host "Items in the survey : " $survey.ItemCount

That gives you the number of items, which you can compare to those visible. In my case I had one completed and one partial, leading to a total count of two. Which the script agreed with.

When you look at the object in PowerShell you can see that there’s a few fields that are different between a completed and a partial response. In this case we have two that are of interest.

Image showing different HasPublishedVersion and Level for two items.

The differences between a visible, completed item (1) and an incomplete item (2)

Of the two i’m going to use the ‘HasPublishedVersion’ field. Live the ‘Level’ field it will become true as long as a version has been completed, however unlike Level it will remain true even if the user starts to edit it later and somehow manages to do a partial save.

Let’s extend our script to list out the number in each group, then list the users who created them.

$web = Get-SPWeb "http://portal.tracy.com/sites/GD/"
$survey = $web.lists["Survey"]
$unPublishedEntries = $survey.Items | ? {-not $_.HasPublishedVersion}

Write-Host "Surveys in list: " $survey.ItemCount
Write-Host "Of which entries are incomplete: " $unPublishedEntries.Count

Foreach ($entry in $unPublishedEntries)
{
    Write-Host "User: {0} has not completed their entry" -f $entry["Created By"]
}

And voila, my results give us:

PowerShell results showing the results from a script

Printout from script

Cataloging Choice Columns

It’s been a while but it’s time for a new post.

Someone asked on TechNet () how to get a summary of all the custom choice columns and the options they have. I didn’t have the time to put it together at that moment but I thought It’d be a good exercise.

This will work for 2010 or 2013 but will not work for Office 365 or SharePoint online. For that a different approach would be needed.

“I inherited a SharePoint 2010 Foundation site that contains about 40 custom columns and about 10 of those custom columns are of the type “Choice”. Is there a way using Powershell or something else to export to a .csv file a list of the custom columns and if they are the type “choice” to show the list of what the various choices are for each column?”

So, let’s assume that we’re only interested in the site columns. To do that we’ll have to grab the SPWeb object and loop through the columns there that are of the appropriate type and list them out.

#Get the web
$web = Get-SPWeb "http://sharepoint.domain.com/sites/sitecollection/subsite"

#Get the columns (note, these are named fields)
$Columns = $web.Fields | ? { $_.TypeAsString -eq "Choice"}

#Print out the number of columns
Write-Host "Number of columns found: " $Columns.Count

#Loop through each choice and print the name
foreach ($entry in $columns)
{
Write-Host ("Field Name: {0}" -f $entry.InternalName)
#Loop through the choices and print those out
foreach ($choice in $entry.Choices)
{
Write-Host ("  Choice: {0}" -f $choice)}
}

From here

That’ll list out the columns to the screen but it’s not a great solution. It’s printing out too many columns, it’s also just printing them to the screen. We need it to serialise this into a format that we can use.

Let’s start with serialisation.

There’s loads of ways to do this but my preference is to create custom objects to contain the information we collect, then assign them to an array which we can process later.

For a quick guide to PSObjects have a look here
So, after changing the write-hosts to write verbose and putting in our custom objects we get this!

Add-PSSnapin Microsoft.SharePoint.PowerShell -ea SilentlyContinue

#Get the web
$web = Get-SPWeb "http://portal.tracy.com/sites/cthub"

#Get the columns (note, these are named fields) 
$Columns = $web.Fields | ? { $_.TypeAsString -eq "Choice"}

#Print out the number of columns
Write-Host "Number of columns found: " $Columns.Count

#Create empty array
$ColumnDetailsArray = @()

#Loop through each choice column and create an object to hold it
foreach ($entry in $columns)
{
    $choicesArray = @()
    Write-Verbose ("Field Name: {0}" -f $entry.InternalName)
    
    #Loop through the choices and print those out
    foreach ($choice in $entry.Choices)
    {
        #Add each choice to the (local) array
        Write-Verbose ("  Choice: {0}" -f $choice)
        $choicesArray += $choice
    }
    #Create a result object to hold our data
    $ColumnDetailsArray += New-Object PSObject -Property @{
                        "Name" = $entry.InternalName
                        "Choices" = $choicesArray
                        }
}

Which actually makes things worse as we no longer get any results! Let’s add that in some xml work. I’m still not entirely happy with the way PowerShell and XML work together so this example is a bit clunky but it works.

#Create a starter XML for the system to work with
[xml] $xml = "<root><summary rundate='{0}' web='{1}'/></root>" -f 
    (Get-Date -f ddMMyyyy), 
    $web.Title

#loop through the results and genearate an xml object
foreach ($column in $ColumnDetailsArray)
{
    #Create an element to hold the top level item
    $columnElement = $xml.CreateElement("Choice")
    $columnElement.SetAttribute("Name", $column.Name) 

    #Loop through the choices and add entries for each
    foreach ($choice in $column.Choices)
    {
        $choiceElement = $xml.CreateElement("Choice")
        
        #Note that you need this Pipe Out-Null to prevent it writing to the console
        $choiceElement.InnerText = $choice
        $columnElement.AppendChild($choiceElement) | Out-Null
    }
    #Once it's built add the element to the root node
    $xml.root.AppendChild($columnElement)  | Out-Null
}
$xml.Save("C:\ResultFolder\ColumnSummary.xml")

So, this now dumps out the data we’ve asked for but it also dumps out all the pre-packaged columns. This is a problem that isn’t easily fixed, there isn’t an ‘OOTB’ flag on fields but there are a few we can use to filter them out.

If we grab a column from the results and run Get-Members on it there are a couple of fields that should be useful for filtering the results:

Sealed – This shows if the column is not supposed to be edited by human hands. Note that this could give false negatives in scenarios where a column has been deployed via the CTHub which I think seals columns (it definitely seals Content Types) as it pushes to consuming site collections

Hidden – Not relevant in this case but often handy. In this case we’ll filter out groups that are part of the ‘_hidden’ group earlier.

So if we now add that criteria to the earlier $columns filtering process we get

$Columns = $web.Fields | ? { $_.TypeAsString -eq "Choice" -and 
        -not $_.Sealed -and $_.Group -ne "_Hidden"
    }

But that’s still not perfect, so instead of filtering the terms out right now let’s make it a bit more useful first. When you look at columns in SharePoint they are organised in Groups. We can see those properties in PowerShell and group our elements using that field.

Add-PSSnapin Microsoft.SharePoint.PowerShell -ea SilentlyContinue

#Get the web
$web = Get-SPWeb "http://portal.tracy.com/sites/cthub"

#Get the columns (note, these are named fields) 
#Also filter out the sealed fields
$Columns = $web.Fields | ? { $_.TypeAsString -eq "Choice" -and 
        -not $_.Sealed -and $_.Group -ne "_Hidden"
    }


#Print out the number of columns
Write-Host "Number of columns found: " $Columns.Count

#Create empty array
$ColumnDetailsArray = @()

#Loop through each choice column and create an object to hold it
foreach ($entry in $columns)
{
    $choicesArray = @()
    Write-Verbose ("Field Name: {0}" -f $entry.InternalName)
    
    #Loop through the choices and print those out
    foreach ($choice in $entry.Choices)
    {
        #Add each choice to the (local) array
        Write-Verbose ("  Choice: {0}" -f $choice)
        $choicesArray += $choice
    }
    #Create a result object to hold our data
    $ColumnDetailsArray += New-Object PSObject -Property @{
                        "Name" = $entry.InternalName
                        "Group" = $entry.Group
                        "Choices" = $choicesArray
                    }
}

#Create a starter XML for the system to work with
[xml] $xml = "<root><summary rundate='{0}' web='{1}'/></root>" -f 
    (Get-Date -f ddMMyyyy), 
    $web.Title

#Get a unique list of the groups in use in the site
foreach ($group in $ColumnDetailsArray | select Group -Unique)
{
    $groupText = $group.Group
    Write-Host "Group name: " $groupText
    $groupElement = $xml.CreateElement("Group")
    $groupElement.SetAttribute("Name", $groupText)
    
    #loop through the results and add them to the xml object
    foreach ($column in $ColumnDetailsArray | ? {$_.Group -eq $groupText})
    {
        #Create an element to hold the top level item
        $columnElement = $xml.CreateElement("Choice")
        $columnElement.SetAttribute("Name", $column.Name)

        #Loop through the choices and add entries for each
        foreach ($choice in $column.Choices)
        {
            $choiceElement = $xml.CreateElement("Choice")
        
            #Note that you need this Pipe Out-Null to prevent it writing to the console
            $choiceElement.InnerText = $choice
            $columnElement.AppendChild($choiceElement) | Out-Null
        }
        #Once it's built add the element to the root node
        $groupElement.AppendChild($columnElement)  | Out-Null
    }
    $xml.root.AppendChild($groupElement)  | Out-Null
}
$xml.Save("C:\ResultFolder\ColumnSummary.xml")

Of course once you have the group name you can filter those options out by using a blacklist of groups to avoid reporting on.


Add-PSSnapin Microsoft.SharePoint.PowerShell -ea SilentlyContinue

#BlackList Group Names
#These are the known groups you get in a non publishing team site:
$blackList = @(
    "_Hidden",                                                                         
    "Base Columns",                                                                    
    "Content Feedback",                                                   
    "Core Contact and Calendar Columns",                                               
    "Core Document Columns",                                                         
    "Core Task and Issue Columns",                                                          
    "Custom Columns",                                                            
    "Display Template Columns",                                                          
    "Document and Record Management Columns",                                                
    "Enterprise Keywords Group",                                                             
    "Extended Columns",                                                             
    "JavaScript Display Template Columns",                                                   
    "Reports",                                                                  
    "Status Indicators"
)

#Get the web
$web = Get-SPWeb "http://portal.tracy.com/sites/cthub"

#Get the columns (note, these are named fields) 
#Also filter out the sealed fields
$Columns = $web.Fields | ? { $_.TypeAsString -eq "Choice" -and 
        -not $_.Sealed -and $_.Group -ne "_Hidden"
    }


#Print out the number of columns
Write-Host "Number of columns found: " $Columns.Count

#Create empty array
$ColumnDetailsArray = @()

#Loop through each choice column and create an object to hold it
foreach ($entry in $columns)
{
    $choicesArray = @()
    Write-Verbose ("Field Name: {0}" -f $entry.InternalName)
    
    #Loop through the choices and print those out
    foreach ($choice in $entry.Choices)
    {
        #Add each choice to the (local) array
        Write-Verbose ("  Choice: {0}" -f $choice)
        $choicesArray += $choice
    }
    #Create a result object to hold our data
    $ColumnDetailsArray += New-Object PSObject -Property @{
                        "Name" = $entry.InternalName
                        "Group" = $entry.Group
                        "Choices" = $choicesArray
                    }
}
#Create a starter XML for the system to work with
[xml] $xml = "<root><summary rundate='{0}' web='{1}'/></root>" -f 
    (Get-Date -f ddMMyyyy), 
    $web.Title

foreach ($group in $ColumnDetailsArray | select Group -Unique)
{
    $groupText = $group.Group

    #Check to see if the group name is in our blacklist
    if (-not $blackList.Contains($groupText))
    {
        Write-Verbose "Group name: " $groupText
        $groupElement = $xml.CreateElement("Group")
        $groupElement.SetAttribute("Name", $groupText)
    
        #loop through the results and genearate an xml
        foreach ($column in $ColumnDetailsArray | ? {$_.Group -eq $groupText})
        {
            #Create an element to hold the top level item
            $columnElement = $xml.CreateElement("Choice")
            $columnElement.SetAttribute("Name", $column.Name)

            #Loop through the choices and add entries for each
            foreach ($choice in $column.Choices)
            {
                $choiceElement = $xml.CreateElement("Choice")
        
                $choiceElement.InnerText = $choice
                #Note that you need this Pipe Out-Null to prevent it writing to the console
                $columnElement.AppendChild($choiceElement) | Out-Null
            }
            #Once it's built add the element to the root node
            $groupElement.AppendChild($columnElement)  | Out-Null
        }
        $xml.root.AppendChild($groupElement)  | Out-Null
    }
    else
    {
        Write-Verbose "Group skipped:" $groupText
    }
}
$xml.Save("C:\ResultFolder\ColumnSummary.xml")

And there you have it. A working report that will summarise all custom choice columns in a SPWeb object and save them in an XML file.

Changing Modified, and Created details in SharePoint

Sometimes you need to lie to SharePoint. In this post i’ll show you how to change the details for who created an item, modified it and when they modified it.

When you’re doing bulk uploads, dealing with lists where you wish to use the Advanced features of only allowing users to edit their own items or just testing some behaviour, eventually you’ll wish you can change the values that SharePoitn doesn’t let you change.

The first thing is, as always, to find the value we want to change:

#Add the SharePoint snapin
Add-PSSnapin Microsoft.SharePoint.Powershell -ea SilentlyContinue

#set the web url and the list name to work upon
$url = "http://sharepoint/sites/cthub"
$listName = "Shared Documents"
$fileName = "FileName.xlsx"

#Get the appropriate list from the web
$web = get-SPWeb $url
$list = $web.lists[$listName]

#Get the file using the filename
$item = $list.Items | ? {$_.Name -eq $fileName}

#Print out current Created by and Created date
Write-Output ("item created by {0} on {1}" -f $item["Author"].tostring(), $item["Created"] )

#Print out current Created by and Created date
Write-Output ("item last modified by {0} on {1}" -f $item["Editor"].tostring(), ($item["Modified"] -f "dd-MM-yyyy"))

As you can see we access the item properties by treating the $item as a hashtable and use the property name as the key.

#Set the created by values
$userLogin = "ALEXB\AlexB"
$dateToStore = Get-Date "10/02/1984"

$user = Get-SPUser -Web $web | ? {$_.userlogin -eq $userLogin}
$userString = "{0};#{1}" -f $user.ID, $user.UserLogin.Tostring()

#Sets the created by field
$item["Author"] = $userString
$item["Created"] = $dateToStore

#Set the modified by values
$item["Editor"] = $userString
$item["Modified"] = $dateToStore


#Store changes without overwriting the existing Modified details.
$item.UpdateOverwriteVersion()

Setting the value is a bit more complicated. To do that you have to build the appropriate user string. In my example the user is already part of the site, if they haven’t previously been added to the user information list you’ll need an extra step here to insert them.

The second bit which differs from your usual PowerShell update is the use of the UpdateOverwriteVersion() method. There’s several update methods in SharePoint but only this one will preserve your changes to modified by and created by.

And now, the full script:

<#
Title: Set modified and created by details
Author: Alex Brassington
Category: Proof of Concept Script
Description
This script is to show how to read, modify and otherwise manipulate the created by and modified by details on documents.
This is to enable correction of incorrect data as part of migrations. It is also useful to enable testing of retention policies.
#>

#Add the SharePoint snapin
Add-PSSnapin Microsoft.SharePoint.Powershell -ea SilentlyContinue

#set the web url and the list name to work upon
$url = "http://sharepoint/sites/cthub"
$listName = "Shared Documents"
$fileName = "FileName.xlsx"

#Get the appropriate list from the web
$web = get-SPWeb $url
$list = $web.lists[$listName]

#Get the file using the filename
$item = $list.Items | ? {$_.Name -eq $fileName}

#Print out current Created by and Created date
Write-Output ("item created by {0} on {1}" -f $item["Author"].tostring(), $item["Created"] )

#Print out current Created by and Created date
Write-Output ("item last modified by {0} on {1}" -f $item["Editor"].tostring(), ($item["Modified"] -f "dd-MM-yyyy"))

#Set the created by values
$userLogin = "ALEXB\AlexB"
$dateToStore = Get-Date "10/02/1984"

$user = Get-SPUser -Web $web | ? {$_.userlogin -eq $userLogin}
$userString = "{0};#{1}" -f $user.ID, $user.UserLogin.Tostring()


#Sets the created by field
$item["Author"] = $userString
$item["Created"] = $dateToStore

#Set the modified by values
$item["Editor"] = $userString
$item["Modified"] = $dateToStore


#Store changes without overwriting the existing Modified details.
$item.UpdateOverwriteVersion()

Deleting Versions

Someone asked for a script that could delete previous versions for SharePoint 3.0. I don’t have a 3.0 dev environment but I do have a 2010 build and it interested me.

Function Delete-SPVersions ()
{
[CmdletBinding(SupportsShouldProcess=$true)]
param(
    [Parameter(Mandatory=$True)][string]$webUrl, 
    [Parameter(Mandatory=$True)][string]$listName, 
    [Parameter(Mandatory=$False)][string]$numberOfMajorVersions
    ) 

    #Get the web
    $web = Get-SPWeb $webUrl


    #Get the list
    $list = $web.Lists[$listName]

    $list.Items | % {
        #Get the item in a slightly more usable variable
        $item = $_

        Write-Output ("Deleting versions for item {0}" -f $item.Name)
        
        #Get all major versions
        #Note, the version ID goes up by 512 for each major version.
        $majorVersions = $item.Versions | ? { $_.VersionID % 512 -eq 0}

        #get the largest version number
        $latestMajorID = $majorVersions | select VersionID -ExpandProperty VersionID | sort -Descending | select -First 1

        #Slightly lazy way to get the latest major version and format it as a single point decimal
        Write-Output ("   Latest major version to retain is {0:0.0}" -f ($latestMajorID /512))
        
        #Filter the major versions to only those which are lower than the highest number - 512 * $numberOfMajorVersions
        $majorVersionsToDelete = $majorVersions | ? {$_.VersionID -le ($latestMajorID - 512 * $numberOfMajorVersions)}
        if ($majorVersionsToDelete)
        {
            $majorVersionsToDelete | % {
                Write-Verbose ("  Deleting major version {0}" -f $_.VersionLabel)
                if ($pscmdlet.ShouldProcess($_.VersionLabel,"Deleting major version"))
                {
                    $_.Delete()
                }
            }
        }
        else
        {
            Write-Verbose "No major versions to delete"
        }
        
        #Re-fetch the item to ensure that the versions are still valid
        $item = $list.GetItemByUniqueId($item.UniqueId)
        
        #Get all the minor versions
        $minorVersions = $item.Versions | ? { $_.VersionID % 512 -ne 0}

        #Delete Minor versions greater than the last major version kept
        $minorVersionsToDelete = $minorVersions | ? {$_.VersionID -lt $latestMajorID}
        if ($minorVersionsToDelete)
        {
            $minorVersionsToDelete | % {
                Write-Verbose ("Deleting minor version {0}" -f $_.VersionLabel)
                if ($pscmdlet.ShouldProcess($_.VersionLabel,"Deleting minor version"))
                {
                    $_.Delete()
                }
            }
        }
        else
        {
            Write-Verbose "No minor versions to delete"
        }
    }
    $web.Dispose()
}