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

 

SharePoint 2013 Access denied due to permission levels

Our customer has a public web application where external users located in a trusted domain can log on to. External users should not be able to browse user information and we created a custom Web Application permission level to deny these permissions to certain AD groups.

Access denied due to permission levels 1

Issue
The Web Application functioned correctly for a long time and all of a sudden external users were getting access denied errors for only 1 site. There are a few blogs on the net to fix access denied errors for example the whole Web Application or Site Collection like checking the cache accounts. This issue was different as externals could log on to almost all sites but not a subsite. This user got the same error even after granting it full control on the Web Application.

Errors in the ULS
There weren’t a lot of errors in the Event Viewer but the ULS log led us in the right direction. We received the following Access Denied stack traces:

Access Denied for <Site>/SitePages/Home.aspx. StackTrace:
at Microsoft.SharePoint.Utilities.SPUtility.HandleAccessDenied(HttpContext context)
at Microsoft.SharePoint.Utilities.SPUtility.HandleAccessDenied(Exception ex)
at Microsoft.SharePoint.Library.SPRequest.GetGroupsDataAsSafeArray(String bstrUrl, UInt32 dwGroupsScope, String bstrValue, UInt32 dwValue, UInt32& pdwColCount, UInt32& pdwRowCount, Object& pvarDataSet)
at Microsoft.SharePoint.SPGroupCollection.InitGroups(Boolean fCustomUsers, String[] strNames, Int32[] groupIds)
at Microsoft.SharePoint.SPBaseCollection.GetEnumerator()
at Microsoft.Office.Server.Audience.AudienceManager.GetUserAudienceIDs(String accountName, SPWeb web, Boolean loadAudiences, Boolean loadSharePointGroups, Boolean loadMemberships)
at Microsoft.Office.Server.WebControls.AudienceLoader.EnsureCurrentUserAudienceIDs(Boolean needAudienceName, Boolean loadAudiences, Boolean loadMemberships, Boolean loadSharePointGroups)

and

GetUserAudienceIDs::GetUserAudienceIDs() failed in SharePoint Group membership resolution (Exception Message : Thread was being aborted. StackTrace
at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.AbortCurrentThread()
at Microsoft.SharePoint.Utilities.SPUtility.Redirect(String url, SPRedirectFlags flags, HttpContext context, String queryString)
at Microsoft.SharePoint.Utilities.SPUtility.RedirectToAccessDeniedPage(HttpContext context)
at Microsoft.SharePoint.Utilities.SPUtility.HandleAccessDenied(HttpContext context)
at Microsoft.SharePoint.Utilities.SPUtility.HandleAccessDenied(Exception ex)
at Microsoft.SharePoint.Library.SPRequest.GetGroupsDataAsSafeArray(String bstrUrl, UInt32 dwGroupsScope, String bstrValue, UInt32 dwValue, UInt32& pdwColCount, UInt32& pdwRowCount, Object& pvarDataSet)
at Microsoft.SharePoint.SPGroupCollection.InitGroups(Boolean fCustomUsers, String[] strNames, Int32[] groupIds)
at Microsoft.SharePoint.SPBaseCollection.GetEnumerator()
at Microsoft.Office.Server.Audience.AudienceManager.GetUserAudienceIDs(String accountName, SPWeb web, Boolean loadAudiences, Boolean loadSharePointGroups, Boolean loadMemberships) . No SharePoint Group IDs will be returned.

Solution
it looked like it had something to do with audiences which hadn’t been configured before. One possible solution is to grant external users the browse user information permission as this solves the issue but then external users are able to find other external users which isn’t a good solution.

We solved this issue by analyzing the possible locations where audiences can be used. We verified all Web Parts but external users also got access denied on document libraries with default Web Parts. We also verified the site navigation and found that a site owner has added a link and enabled audiences for this link. External users are not able to “open” the group that has been added to this link and they receive an access denied because of it.

Make sure that when you denying ‘Browse User Information’ permissions to certain users or groups that audiences are not added to the site navigation or Web Parts!

List SharePoint Services on Server using PowerShell

Manage Services on Server shows which SharePoint service is running on a particular server. You can only select 1 server and you won’t get a quick overview of all the services per server. I have created the below script to create a .html file which shows each server and their active or inactive service.

The following script can be copied into notepad and saved as a .ps1 file. Just execute the script to create a .HTML file. When you run the script, it will ask for an export location. This folder does not have to exist on your harddrive. This has been created and tested for SharePoint 2013 and haven’t tested this yet for SharePoint 2010.

#Import SharePoint PowerShell Modules
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Request the location to store the HTML file
$ExportLocation = Read-Host "Enter the location to store the HTML file. (eg. C:\temp). This directory will be created if it doesn't exist"

Function ListServicesOnServer($path)
{
#Check if directory exists or create the directory
if((Test-Path $path) -eq $false)
{
mkdir $path
write-host "Folder created" -foregroundcolor yellow
}
else
{
write-host "Folder already exists" -foregroundcolor yellow
}

#create an unique name
$date = get-date
$today = $date.ToString("ddMMyyyy_HHmm")
$HTMLpath = "$($path)\ServicesOnServer_$($today).html"

#Create HTML File for the information
New-Item -ItemType file $HTMLpath -force | out-null
add-content -value "<html><body><h1>Services on Server</h1><br />" -path $HTMLpath

#Try / Catch statement to get the information
try
{
#Get the SharePoint servers in the farm
$servers = Get-SPServer | where {$_.Role -ne "Invalid"}

#Create an HTML Table and headers
add-content -value "<table border='1' style='font-family: Calibri, sans-serif'>
<tr>
<th style='background-color:blue; color:white'>Service</th>" -path $HTMLpath
$servers | %{Add-Content $HTMLpath "<th style='background-color:blue; color:white'>$($_.Address)</th>"}
add-content -value "</tr><tr>" -path $HTMLpath

#list all services using 1 server
$services = get-spserviceinstance -server $servers[0]

foreach($service in $services)
{
$serviceTypeName = $service.TypeName | sort-object
Add-Content $HTMLpath "<td>$($service.TypeName)</td>"
$servicesOnServers = get-spserviceinstance | where-object {$_.TypeName -eq $serviceTypeName} | %{Add-Content $HTMLpath "<td>$($_.status)</td>"}
Add-Content $HTMLpath "<tr></tr>"
}

add-content -value "</table></body></html>" -path $HTMLpath
write-host "Script Done" -foregroundcolor green
}
catch
{
write-host "$($_.Exception.Message)" -foregroundcolor red
}
}

ListServicesOnServer($ExportLocation)

Manual

You should already have created a .ps1 file and saved this to a predefined location:

ListServicesOnServer1

Run PowerShell as administrator and run the following command:

& “C:\Temp\ListServicesOnServer.ps1”

ListServicesOnServer2

Press enter and fill in a location:

ListServicesOnServer3

Press enter:

ListServicesOnServer4

The output should look like below:

ListServicesOnServer5