Download and synchronize documents from SharePoint using CSOM

I have created a script in 2015 to download all documents from a site collection to a file share or local drive (blogpost) but we now received the request to download and synchronize documents from SharePoint Online. The customer wants to download all documents from one library and keep the documents updated on the file share. SharePoint Online will be the source location where users will be working daily and the file share will be the destination with files max 1 day old. The customer also wanted to use a list field to categorize the documents and place them in folders on the file share. This would make it easier to find the files. I’ve created this script with CSOM using PowerShell for SharePoint Online but it can be also used for SharePoint 2013/2016 and also 2019 with a few modifications.

You can find the full script below where I will break down the script in a few sections to explain it in detail.

function get-SharePointLibraryDocuments{
#Variables
$rootSite = "https://spfiredeveloper.sharepoint.com"
$sourceSite = "https://spfiredeveloper.sharepoint.com/sites/dev/"
$sourceLibrary = "Documents"
$destinationPath = "D:\xxxxxxxxxx\DownloadLibrary\"

#Credentials
$userName = "mpadmin@spfiredeveloper.onmicrosoft.com"
$password = Read-Host "Please enter the password for $($userName)" -AsSecureString
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userName, $password)

#set the client context
$context = New-Object Microsoft.SharePoint.Client.ClientContext($sourceSite)
$context.credentials = $Credentials

#Retrieve list
$list = $context.web.lists.getbytitle($sourceLibrary)
$context.load($list)
$context.executequery()

#Retrieve all items
$listItems = $list.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery()) 
$context.load($listItems)
$context.executequery()

foreach($listItem in $listItems) 
{ 
$fileName = $listItem["FileLeafRef"]
$fileUrl = $listItem["FileRef"]
$fileMeta = $listItem["Meta"]
$modified = $listItem["Modified"]
$created = $listItem["Created"]
$sourceItemPath = "$($rootSite)$($fileUrl)"
$destinationFolderPath = "$($destinationPath)$($fileMeta)\"
$destinationItemPath = "$($destinationFolderPath)$($fileName)"

try{
if((Test-Path $destinationFolderPath) -eq 0)
{
mkdir $destinationFolderPath | out-null
}

if((Test-Path $destinationItemPath) -eq 0)
{
try{
$client = New-Object System.Net.WebClient 
$client.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userName, $password)
$client.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f")
$client.DownloadFile($sourceItemPath, $destinationItemPath)
$client.Dispose()

$file = Get-Item $destinationItemPath
$file.LastWriteTime = $modified
$file.CreationTime = $created

write-host "New document $($destinationItemPath)" -ForegroundColor green
}
catch{
write-host "Error occurred: $($destinationItemPath) - $($_.Exception.Message)" -foregroundcolor red
}
}
else{
try{
$item = get-childitem $destinationItemPath

if ($item.LastWriteTime.ToString("d-M-yyyy hh:mm:ss") -ne $modified.addhours(1).ToString("d-M-yyyy hh:mm:ss")){
$client = New-Object System.Net.WebClient 
$client.Credentials = $Credentials
$client.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f")
$client.DownloadFile($sourceItemPath, $destinationItemPath)
$client.Dispose()

$file = Get-Item $destinationItemPath
$file.LastWriteTime = $modified

write-host "Overwritten document $($destinationItemPath)" -ForegroundColor green
} 
else{
write-host "Skipped document $($destinationItemPath)" -ForegroundColor yellow
} 
}
catch{
write-host "Error occurred: $($destinationItemPath) - $($_.Exception.Message)" -foregroundcolor red
} 
}
}
catch{
write-host "Error occurred: $($_.Exception.Message)" -foregroundcolor red
}
} 
}

get-SharePointLibraryDocuments

Variables

I’ve hard coded the source and destination in this script as it was only required for one list but you can use a .CSV file if you have multiple sources and destinations. Note that you should keep the last slash or backslash in the string for $sourceSite and $destinationPath.

#Variables
$rootSite = "https://spfiredeveloper.sharepoint.com"
$sourceSite = "https://spfiredeveloper.sharepoint.com/sites/dev/"
$sourceLibrary = "Documents"
$destinationPath = "D:\xxxxxxxxxx\DownloadLibrary\"

Assembly

I’m running the script in the SharePoint Online Management Shell but you may need to add the SharePoint client DLL’s if you want to use this script on-premises.

SharePoint 2013

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"

SharePoint 2016/2019

Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

Credentials

The credentials are again hard coded for this blog but we created a secure text file with the username and password on the server which it will use for its daily schedule. The below can be used to test the script.

SharePoint Online

The below needs to be used for SharePoint Online CSOM

#Credentials
$userName = "mpadmin@spfiredeveloper.onmicrosoft.com"
$password = Read-Host "Please enter the password for $($userName)" -AsSecureString
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userName, $password)

SharePoint On-Premise

The below needs to be used for SharePoint On-premises

#Credentials
$userName = "<Domain>\<LoginName>"
$password = Read-Host "Please enter the password for $($userName)" -AsSecureString
$Credentials = New-Object System.Net.NetworkCredential($userName, $password)

Client Context

Use the following two lines to get the client context

#get the client context
$context = New-Object Microsoft.SharePoint.Client.ClientContext($sourceSite)
$context.credentials = $Credentials

Retrieve the list

First we will retrieve the proper list

#Retrieve list
$list = $context.web.lists.getbytitle($sourceLibrary)
$context.load($list)
$context.executequery()

Retrieve all items

Next we will collect all items present in the list

#Retrieve all items
$listItems = $list.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())  
$context.load($listItems)
$context.executequery()

File variables

We are going to use some item properties which we will store in variables directly after the for each. We will also build our source and destinations strings to be used in the next bits.

#file variables
$fileName = $listItem["FileLeafRef"]
$fileUrl = $listItem["FileRef"]
$fileMeta = $listItem["Meta"]
$modified = $listItem["Modified"]
$created = $listItem["Created"]
$sourceItemPath = "$($rootSite)$($fileUrl)"
$destinationFolderPath = "$($destinationPath)$($fileMeta)\"
$destinationItemPath = "$($destinationFolderPath)$($fileName)"

Create folder structure

We have a column “Meta” in our test environment which we use to categorize our items and we will need a folder for each unique “Meta”. The below section will create a folder if it doesn’t exist.

if((Test-Path $destinationFolderPath) -eq 0)
{
mkdir $destinationFolderPath | out-null
}

Download the file and save to the correct location

The below bit of code will first verify if the item does not exist and will then download the file using system.net.webclient which works for SharePoint on premises and SharePoint Online. We will update the last write time and creation time of the item to the same dates known in SharePoint. Otherwise it will have the dates of the download. You can also use the last saved date of the item but it is harder to retrieve this property and this property may have a mismatch with the modified date of SharePoint.

if((Test-Path $destinationItemPath) -eq 0)
{
try{
$client = New-Object System.Net.WebClient 
$client.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userName, $password)
$client.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f")
$client.DownloadFile($sourceItemPath, $destinationItemPath)
$client.Dispose()</pre>
$file = Get-Item $destinationItemPath
$file.LastWriteTime = $modified
$file.CreationTime = $created

write-host "New document $($destinationItemPath)" -ForegroundColor green
}
catch{
write-host "Error occurred: $($destinationItemPath) - $($_.Exception.Message)" -foregroundcolor red
}
}

Overwrite items

In the next try catch we will verify if the item present is not the same item as currently in SharePoint because we will otherwise use server resources which are unnecessary as we only want to download new items or update current items. Note that I had to add “.addhours(1)” to my modified variable as in my case the time zone wasn’t matching for SharePoint and the file share. This may also be correct or off by 2 hours, but you will notice this when files are being overwritten and not skipped.

if ($item.LastWriteTime.ToString("d-M-yyyy hh:mm:ss") -ne $modified.addhours(1).ToString("d-M-yyyy hh:mm:ss")){
$client = New-Object System.Net.WebClient 
$client.Credentials = $Credentials
$client.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f")
$client.DownloadFile($sourceItemPath, $destinationItemPath)
$client.Dispose()</pre>
$file = Get-Item $destinationItemPath
$file.LastWriteTime = $modified

write-host "Overwritten document $($destinationItemPath)" -ForegroundColor green
}
else{
write-host "Skipped document $($destinationItemPath)" -ForegroundColor yellow
} 

Using the script

You can update all variables to represent your environment and copy/paste the code in the SharePoint Online Management Shell

Press enter to run the script and fill in your password

Press enter again and the script will create the necessary folders and download the items

You can run the script again to verify if the items are being skipped

Next I will be updating two items and run the script again

 

Configure SharePoint managed metadata columns using PowerShell CSOM

I received the request from a colleague to create a script that will configure all managed metadata columns in SharePoint Online to allow people to fill in values.
My colleague first tried this using PnP but was unable to configure the necessary property of a managed metadata column.
It is possible to set this property using the client object model (CSOM) in PowerShell.
You will need the SharePoint Online Client Components SDK which you can download at https://www.microsoft.com/en-us/download/confirmation.aspx?id=42038

We first verified that we were able to change this property by using the below script:

$url = "https://<TenantName>.sharepoint.com"
$userName = "admin@<TenantName>.onmicrosoft.com"
$password = Read-Host "Please enter the password for $($userName)" -AsSecureString

Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Taxonomy.dll"

$SPOCredentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userName, $password)

$context = New-Object Microsoft.SharePoint.Client.ClientContext($url)
$context.Credentials = $SPOcredentials
$web = $context.Web
$context.Load($web)
$list = $web.lists.GetByTitle("Documenten")
$context.Load($list)
$field = $list.fields.getbytitle("Zoekwoorden")

$context.load($field)
$context.ExecuteQuery()

$taxField = [Microsoft.SharePoint.Client.ClientContext].GetMethod("CastTo").MakeGenericMethod([Microsoft.SharePoint.Client.Taxonomy.TaxonomyField]).Invoke($context, $field)

$taxfield.open = $true
$taxfield.update()

$context.executequery()

Once we retrieved the correct field we had to cast this to a taxonomyField to be able to set the property “Open”.
The next step was to build a script around this that allows us to configure all managed metadata columns on the rootsite and all subsites.
This script has been upload to GitHub: https://github.com/peetersm12/ConfigureManagedMetadaColumnCSOM

The script will loop through all subsites to find the specified column on all the lists and libraries available.
You can first use a “preview” action to find all lists and libraries which have a column with the name you are looking to update.
Then you can update these columns so that users are allowed to fill in values.
Note that you can use this script for all updates for a specific column, just change the part where you  find the above code.

Using the script

Open the .ps1 file and copy the whole content of the file to preload the functions in the SharePoint Online Management Shell as administrator.

Next run 1 of the following commands:

To first list all lists/libraries which currently has the specified column title
set-mmcolumn -Action “Preview”

To update all lists and libraries which has the specified column title
set-mmcolumn -Action “Update”

Running the preview

Run the following command:

set-mmcolumn -Action "Preview"

and answer the following questions 1 by 1

The script will start and the output will be shown on screen, you can also verify the transcript although this isn’t color coded but search for “Found:”

We can see that our column has been found for the library “Documenten” and users are currently not allowed to fill in values.
Next run the script to update these columns.

Running the update

Run the following command:

set-mmcolumn -Action "Update"

and enter the following questions 1 by 1

The script will start and the output will be shown on screen, you can also verify the transcript although this isn’t color coded but search for “Found:”


We can see that our column has been found for the library “Documenten” and the value has been updated from False to true.
Run the preview again to see if all “green lines” are now set to true

SharePoint Online site provisioning using Microsoft Flow, Azure Functions and Azure Storage Queue

A while back I created a provider hosted app using CSOM in C# for creating project sites but this required the users to have sufficient permissions to create a site. Using Microsoft Flow, Azure Function, Azure Storage Queue, PowerShell and SharePoint Online I created a proof of concept with the latest techniques and using the AppId/AppSecret so the user doesn’t need additional permissions. This solution isn’t free as it needs an Azure Subscription but the costs are minimal. Please find references to Microsoft in the summary at the end.

This article describes the following scenario:

  1. The user creates an item in a SharePoint list.
  2. Microsoft Flow will be triggered on item creation.
  3. Microsoft Flow will add a message on the Azure Storage Queue.
  4. The Azure Function will monitor the Azure Storage Queue and create the subsite based on the values entered in the SharePoint list using PowerShell.

This article has the following chapters:

  • Create SharePoint List
  • Get and register AppId and AppSecret in SharePoint Online
  • Create Azure Storage Queue
  • Create Azure Function
  • Create PowerShell Script
  • Test Azure Storage Queue
  • Create Microsoft Flow

Create SharePoint List

First we are going to create a list in SharePoint which we are going to use for our site metadata.

SNAGHTML1b92ddf2
Add an App

image
Custom List

image
Create

image
Add the below columns:

  • SiteURL –> Single line of Text
  • SiteTemplate –> Choice
  • SiteLanguage –> Choice

image

The list has been created which we are going to use for our site provisioning.

Get AppId and AppSecret in SharePoint Online

It is possible to use a username and password for the Azure Function but it is also possible to use an AppId and AppSecret for impersonation.
In this scenario we are going to use an AppId and AppSecret.

Go to the site collection where you want to register the app by appending the url with “_layouts/15/appregnew.aspx”

image
Fill in the above information and click on create

image

Save the Client Id and Secret as we are going to need it for our Azure Function.
Next append /_layouts/appinv.aspx to the url

image
With the below Permission Request XML we allow the app access to the site collection. You can specify different levels which are explained at https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/add-in-permissions-in-sharepoint .

<AppPermissionRequests AllowAppOnlyPolicy=”true”>

<AppPermissionRequest Scope=”http://sharepoint/content/sitecollection” Right=”FullControl” />

</AppPermissionRequests>

and click on Create

image
Trust It

Create Azure Storage Queue

We are going to setup the Azure Storage Queue which will handle all our messages which have been sent using Microsoft Flow.
Please note that this can also be achieved without the Azure Storage Queue as you can directly sent the message to the Azure Function using an Azure HttpTrigger function.

First go to your Azure Dashboard

image
Storage accounts

image
Add

image
Create

image
Open the newly created storage account

image
Click on Queues

image
+ Queue

SNAGHTML1bad6cd5
OK

The Azure Storage Queue has now been created which we use within our Microsoft Flow and Azure Function.

Create Azure Function

The next thing we will build is the Azure Function. The Azure Function will be created based on PowerShell and the SharePointPnPPowerShellOnline module.
We are going to start from the Azure Dashboard.

image
Go to the App Services

image
Add

image
Function App

image
Create

image
We are going to use the existing resource group and storage which we created during the Azure Storage Account. Click on Create

image
Open the newly created Azure Function

image
New function

SNAGHTML1bcaa56d
Enable Experimental Language Support and navigate to Queue trigger

image
Click on PowerShell

SNAGHTML1bcdd008
Enter the queue name we created earlier. And click on New

image
Select the Azure Storage Account

image
Create and navigate back to the Platform features

image
Go to Platform features

image
Open Advanced tools (Kudu)

image
Click on Debug Console and then on PowerShell

image
Navigate to Site –> wwwroot –> QueueTriggerPowerShell

image
Create a new folder called “modules”

image
We are going to upload the PowerShell DLL’s which we are going to use here as it is not possible to import-modules from within the Azure Function. You can drag and drop the files to this folder.
The files we need are by default installed in the following location: C:\Program Files\WindowsPowerShell\Modules\SharePointPnPPowerShellOnline

image
Copy the contents from this folder to the Azure Function.
If you are missing this folder; Install this using PowerShell on the workstation with the command: Install-Module SharePointPnPPowerShellOnline

image

Also copy the items from the following locations:

  • C:\Windows\assembly\GAC_MSIL\Microsoft.IdentityModel
  • C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.IdentityModel.Extensions

Go back to the function

image
Go to the application settings

image
Select 64-bit and scroll down

image
Add the AppId and AppSecret with the key to the application settings as we can reference to these settings from the Azure Function.
Save the modification and in the next chapter we will create the PowerShell script.

Create PowerShell Script

Go to the QueueTriggerPowerShell in the Azure Function

image

image
Add the below PowerShell code

$requestBody = Get-Content $triggerInput -Raw | ConvertFrom-Json

$ParentSiteUrl = "https://spfire.sharepoint.com/sites/projectsitecreation/"

$WebTemplate = $requestBody.WebTemplate

$SiteTitle = $requestBody.SiteTitle

$SiteDescription = "Site with PowerShell"

$SiteURL = $requestBody.SiteURL

$SiteLanguage = $requestBody.SiteLanguage

$AppId = $env:AppId

$AppSecret = $env:AppSecret

connect-PnPOnline -AppId $AppId -AppSecret $AppSecret -url $ParentSiteUrl

New-PnPWeb -Title $SiteTitle -url $SiteURL -Locale $SiteLanguage -Template $WebTemplate -Description $SiteDescription

Write-Output "PowerShell script processed queue message '$requestBody'"

image
Click on test in the right corner

image
Enter the below Request body

{
“WebTemplate”: “STS#0”,
“SiteTitle”: “TestCreation1”,
“SiteURL”: “TestCreation1”,
“SiteLanguage”: 1033
}

and click on Save and run

image
You can verify the log for success and navigate to the created site

SNAGHTML2a2781
We now know that the PowerShell code is successful.

Test Azure Storage Queue

Go to the Azure Storage Queue to test if adding a message is being successfully processed by the Azure Function.

image
Add message

image
OK and you can verify if the Azure function picked up the message if you still have the log open

image
Or go to the newly created site

SNAGHTML302e3a
We confirmed the Azure Storage Queue with the Azure Function is working correctly.

Create Microsoft Flow

We can now create a Microsoft Flow that will add an message in the Azure Storage Queue which will be picked up by our Azure Function.
Go to https://flow.microsoft.com

image
Create from blank

image
When an item is created

image
And add a new step

image
Put a message on a queue

image
Add a new connection if you already had one like me

image
The Connection Name can be anything where the Storage Account Name and Shared Storage Key can be found in Azure

SNAGHTML5123e9

image

Save the flow and create a new item in the previous created SharePoint List

image

Save and first verify the Microsoft Flow

image
Next verify the Azure Function Log if still open

image
And last verify if the site has been created

SNAGHTML59251b

The site has been created successfully.

Summary

We have now created a working site provisioning solution based on a SharePoint list.
This solution uses multiple techniques such as Microsoft Flow, Azure Storage Queues, Azure Functions and SharePoint Online.
This is just an example of working with these techniques but you can for example do more after the site creation such as adding extra permissions and set default columns.
It is possible to do more with Microsoft Flow as for example send an email after creation or update the status during the creation

You can find more information at https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/site-design-pnp-provisioning regarding for example an app ID and app secret with administrative rights on your tenant, Microsoft Flow and an Azure function. Costs for an Azure Function are mentioned in https://azure.microsoft.com/en-us/pricing/details/functions and queue costs at https://azure.microsoft.com/en-us/pricing/details/storage/queues/

Information about the SharePoint PnP PowerShell CmdLets can be found at https://github.com/SharePoint/PnP-PowerShell and https://docs.microsoft.com/en-us/powershell/sharepoint/sharepoint-pnp/sharepoint-pnp-cmdlets?view=sharepoint-ps

Please let me know your use case for Azure Functions and if there are any questions.