Retrieve List Metadata from SharePoint Online (Office365) Using Windows PowerShell and C#

One useful tool used at Risolv IT is Windows PowerShell,  a task automation and configuration management framework from Microsoft.

The PowerShell command line shell uses a scripting language built on the .NET framework, and  contains many useful functions, including the ability for third parties to write module to integrate with their applications and servers.

This is particularly useful when it comes to accessing and managing Office365 services such as Exchange and SharePoint. There are however, few cmdlets for interacting with SharePoint Online sites, which means a few extra steps are needed.

Recently I was required to write a script which would retrieve the metadata from a document library on a SharePoint Online site in order to apply that same meta data to a on-premise SharePoint server. The process was less straight-forward than some, as there are very few built-in PowerShell cmdlets for interacting with SharePoint Online (as opposed to SharePoint hosted on a local server). In addition, the files in question were located within a folder of the document library rather than in the root, which required some extra steps.

The base code was obtained from the ScriptingGuy blog at http://blogs.technet.com/b/heyscriptingguy/archive/2011/02/15/using-powershell-to-get-data-from-a-sharepoint-2010-list.aspx which I then modified to suit my needs. The following outlines the process and any issues/resolutions I found along the way:

Required Files

This solution will make use of C# code, in to that end, a few assemblies need to be present in order to make this all work using the SharePoint CSOM (Client-Side Object Model). The required assemblies are Microsoft.SharePoint.Client.dll, Microsoft.SharePoint.Client.Runtime.dll,System.Core.dll and System.Security.dll

The two SharePoint-specific libraries can be retrieved from on-premise installation of SharePoint (or by installing a trial version of SharePoint Server (http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=16631). They are located in %systemroot%\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI

The System.Core.dll and System.Security.dll libraries should already be present in%systemroot%:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.5 but if System.Security.dll is missing you may have to search online for it (For example:http://www.dllme.com/dll/files/system_security_dll.html).

Code

Now that all the required files have been downloaded, the real fun starts. For the first part of the code, we are using C# and running it in the PowerShell session thanks to Add-Type commandlet.

$cSharpCode = @"
using System;
using System.Collections.Generic;
using System.Security;
using Microsoft.SharePoint.Client;
 
namespace SPClient
{
    public class SharePointList
    {
        public static ListItemCollection GetList()
        {
            ClientContext clientContext = new
//Replace with your sharepoint site URL
ClientContext("https://example.sharepoint.com/location");
            SecureString pass = new SecureString();
//append each character of your password to the securestring
            pass.AppendChar('P');
            pass.AppendChar('A');
            pass.AppendChar('S');
            pass.AppendChar('S');
            pass.AppendChar('W');
            pass.AppendChar('O');
            pass.AppendChar('R');
            pass.AppendChar('D');
            SharePointOnlineCredentials creds = new
//Replace with your username
SharePointOnlineCredentials("Username", pass);
            clientContext.Credentials = creds;
//Replace with the title of your list
            List list = clientContext.Web.Lists.GetByTitle("List Title");
            CamlQuery camlQuery = new CamlQuery();
//if the files are in the root list and not a subfolder this should be changed to “</view>”
            camlQuery.ViewXml = "<View Scope='Recursive'><Query></Query></View>";
//Replace with the path to the subfolder in the list
            camlQuery.FolderServerRelativeUrl = "/location/List Title/Subfolder";
            ListItemCollection listItems = list.GetItems(camlQuery);
            clientContext.Load(list);
            clientContext.Load(listItems);
            clientContext.ExecuteQuery();
            return listItems;
        }
    }
}
"@
 
$assemblies = @(
    "C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.5\Microsoft.SharePoint.Client.dll",
    "C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.5\Microsoft.SharePoint.Client.Runtime.dll",
    "System.Core, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089",
    "C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.5\System.Security.dll"
)

Add-Type -ReferencedAssemblies $assemblies -TypeDefinition $cSharpCode

Note: The version of System.Core needs to match the framework version of the SharePoint assemblies so it may need to be changed depending on your version (e.g. Version=3.5.0.0 for .NET 3.5 SharePoint assemblies). This code does all of the SharePoint related stuff and returns objects for each file. The objects still need to be converted to PowerShell objects however which is what the second part of the code accomplishes. 

$items = [SPClient.SharepointList]::GetList()
$table = @()
 
foreach ($item in $items){
    $obj = new-object psobject

    foreach ($i in $item.FieldValues){
        $keys = @()
        $values = @()

        foreach ($key in $i.keys){
            $keys += $key
        }
        foreach ($value in $i.values){
            $values += $value
        }
 
        for ($j = 0;$j -lt $keys.count - 1;$j++){
            $obj | Add-Member -MemberType noteproperty -Name $keys[$j] -Value $values[$j]
        }
    }
#put string parsing code here
    $obj
    $table += New-Object psobject -Property @{ Name = $($obj.FileLeafRef)}
}
$table | Export-Csv -path "C:\temp\metadata.csv" -noType -Encoding UTF8

And there you have it! This code will simply output each object to the console and output the file name of each object to a .csv file. It can easily be modified to output more properties.

Note that certain columns such as hyperlinks return an object rather than a string, so you will have to access that object’s attributes to get the data you need. For hyperlinks, you can access the string with $obj.hyperlink.description and the URL with $obj.hyperlink.url.

Another thing to note that I found problematic is that if you have a property column in your document library which can have more than one property (e.g. type = dog; mammal), this will cause all other values below “type” to shift down and so you will end up with values associated with the wrong properties (e.g. if type was above date and date was above colour, then type=dog, date=mammal, colour=1/1/2015). To remedy this, I added if statements to check how far down the values had shifted, then compensate by retrieving values from the properties they had shifted to (e.g. type=type; date).


11 thoughts on “Retrieve List Metadata from SharePoint Online (Office365) Using Windows PowerShell and C#

  1. In addition to standard lookup columns, single-value managed metadata, multiple-value managed metadata, single-value people and group columns, and multiple-value people and group columns all count as lookup columns which can lead to the query exceeding the List View threshold.

Leave a Reply

Your email address will not be published. Required fields are marked *