SharePoint
SharePoint CSOM Scripting in PowerShell
I was recently faced with a migration issue that left the documents in a document library mapped improperly to a lookup field in a SharePoint list.
The problem?
The migration tool did not preserve the original document ID’s when it migrated them to a new document library. Since the migration tool had no easy way to preserve the ID’s, I was left with the task of remapping the ID’s and fixing the lookup values post-migration.
I had my migration completely scripted with PowerShell and wanted to use PowerShell for the remapping as well.
I needed a way to access internal servers easily and wasn’t too concerned about performance – it’s a one-time script. Enter CSOM. I decided to write two PowerShell scripts – one that mapped the original ID of the item in the list with the document name, and one that read the list of documents, finds the new document ID of the document and associates it with the lookup column.
This works because the Migration tool is able to maintain list item id’s, i.e. source list item ID 10 will still be item ID 10 in the destination list – it just can’t do it for document libraries!
Let’s walk through creating the first script, “MapItemIdToDocumentTitle.ps1”
Step 1: Add command line parameters to the script
Fortunately, PowerShell makes this so easy:
param (
[string]$url = “https://sourcesiteurl”,
[string]$listName = “Source List Name”,
[string]$associatedListName = “Associated Document Library Name”,
[string]$associatedDocsFieldName = “Associated_x0020_Docs”,
[string]$outfile = “out.csv”
)
Just make sure these are the first lines in your PowerShell script.
Step 2: Add the CSOM references to your script
# the path here may need to change if you used e.g. C:\Lib..
Add-Type -Path “c:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll”
Add-Type -Path “c:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll”
# note that you might need some other references (depending on what your script does) for example:
Add-Type -Path “c:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Taxonomy.dll”
Step 3: Create the client context and load the web
# connect/authenticate to SharePoint Online and get ClientContext object..
$clientContext = New-Object Microsoft.SharePoint.Client.ClientContext($url)
if (!$clientContext.ServerObjectIsNull.Value)
{
Write-Host “Connected to SharePoint site: ‘$Url'” -ForegroundColor Green
}
$rootweb = $clientContext.Web
$childWebs = $rootweb.Webs
$clientContext.Load($rootweb)
$clientContext.Load($childWebs)
$clientContext.ExecuteQuery()
Note: Nothing is actually executed on the server until the “ExecuteQuery” method is called. This is the key to working with the CSOM – do stuff locally, then execute on the server. You will see this pattern throughout the code.
Step 4: Process the web (create the CSV file)
processWeb($clientContext.Web)
Where processWeb is the method that does all of the work:
function processWeb($web)
{
Grab the lists – remember, nothing happens on the server until ExecuteQuery is called:
$lists = $web.Lists
$clientContext.Load($web)
$clientContext.Load($lists)
$clientContext.ExecuteQuery()
In order to output CSV easily, all output is stored in an array
Write-Host “Processing web: ” $web.Title
$output = @()
Get all items from the source list (note: this works because the source list has less than 10k items). Again, notice the ExecuteQuery line – data is not retrieved from the server until this line executes.
$theList = $lists.GetByTitle($listName)
$docList = $lists.GetByTitle($associatedListName)
$query = [Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery(9999)
$items = $theList.GetItems($query)
$clientContext.Load($items)
$clientContext.ExecuteQuery()
Now we just loop through each item finding items that have associated docs. I needed to go back to the server to load the actual document item and then again to get the actual file (so I could grab the file name).
foreach($item in $items)
{
$associatedDocs = $item[$associatedDocsFieldName]
if ($associatedDocs)
{
foreach($associatedDoc in $associatedDocs)
{
# get the actual file name from the associated list
$docItem = $docList.GetItemById($associatedDoc.LookupId)
$clientContext.Load($docItem)
$clientContext.ExecuteQuery()
$docFile = $docItem.file
$clientContext.Load($docFile)
$clientContext.ExecuteQuery()
$docName = $docFile.Name
Writing to CSV is picky!
You can output as many columns as you want, just use the “add-member” call with the NoteProperty membertype and name it appropriately. Append each row to the output.
$row = new-object PSObject
$row | add-member -membertype NoteProperty -name “ID” -value $item.ID
$row | add-member -membertype NoteProperty -name “FileID” -value $associatedDoc.LookupId
$row | add-member -membertype NoteProperty -name “FileName” -value $docName
$output += $row
}
}
}
Once we’ve completed the process, simply use the “export-csv” call and PowerShell does all the magic of writing the header and comma separated values. It’s really that easy!
$output | export-csv -NoTypeInformation -Path $outfile
Write-Host “Output saved to $outfile”
}
That’s it.
The second script I wrote is very similar, but reads the CSV file as input and remaps the documents. While writing these scripts, simply refer to MSDN for the CSOM reference: https://msdn.microsoft.com/en-us/library/office/dn268594.aspx
Hopefully, this post will help you get started with your CSOM PowerShell scripting. I love using PowerShell for this type of stuff – it’s super easy to write, easy to run and all it requires is a text editor.
