U2U Blog

for developers and other creative minds

Use PowerShell and CSOM to batch create SharePoint Online Site Collections with a custom template



In this post I will show you how to create multiple Site Collections in SharePoint Online using PowerShell and the CSOM (Client Side Object Model) libraries provided by Microsoft. It will also be possible to apply a custom template to the Site Collections that we create.

As at the time of writing the possibilities of the SharePoint Online Management Shell are too limited to do this - especially regarding Sandboxed Solutions and thus applying templates – everything will be done by using the CSOM SharePoint assemblies in PowerShell. We will initialize .NET objects using PowerShell.

These are the steps we want to implement in the PowerShell Script:

  1. First we will check if the Site Collection already exists in SharePoint Online. If it does, we will delete it.
  2. Before we can create a new Site Collection at the URL of a deleted Site Collection, we need to remove it from the recycle bin.
  3. When we are sure no Site Collections exist, let's now create them!
  4. In case we want to apply a custom template, there are a few steps to perform:
    1. Upload the template to the Solutions gallery
    2. Activate the uploaded solution
    3. Apply the uploaded template to the Root Web of the Site Collection
    4. Delete the uploaded template file
  5. Set the security for the site collection

The assemblies we need to load are the following:

  • Microsoft.Online.SharePoint.Client.Tenant.dll
  • Microsoft.SharePoint.Client.dll
  • Microsoft.SharePoint.Client.Runtime.dll
  • Microsoft.SharePoint.Client.Publishing.dll

To get these DLL's, download the SharePoint Server 2013 Client Components SDK here.

The tenant DLL is used for deleting and creating site collections using your SharePoint Online admin URL.
The other DLL's are used to access the created site collections and perform operations on them. The publishing DLL specifically is required to activate sandboxed solutions.


Getting started


First we'll create a PowerShell cmdlet, which we can then call with a set of parameters to get the Site Collection creation process going. All these parameters have a default value, but can be overwritten when calling the function. Notice the SiteTemplate by default uses the Team Site template ("STS#0"). If you wish, you can get a list of available templates from your tenant as well: link.

The rest of the parameter names should speak for themselves.

function CreateSiteCollections{
        [string]$Domain = "*yourid*.onmicrosoft.com",
        [string]$UserName = "*adminusername*@$domain",
        [string]$UserPassword = "*yourpassword*",
        [string]$AdminUrl = "https://*yourid*-admin.sharepoint.com",
        #site details
        [string]$SiteUrl = "https://*yourid*.sharepoint.com",
        [string]$SiteTitle = "My Team Site",
        [string]$SiteOwner = "*siteowner*@$domain",
        [string]$Prefix = "*SiteUrlPrefix*",
        [string]$Suffix = "",
        [string]$SiteTemplate = "STS#0",
        #site numbers
        [int]$StartIndex = 1,
        [int]$NumberOfSites = 5,
        [int]$BatchSize = 5
    #We’ll add the rest here

Inside this function, the first thing we are going to do is load the CSOM assemblies from their installation path. When installing the SDK the assemblies will be found at C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI – the Tenant DLL assembly - and C:\Program Files\SharePoint Client Components\16.0\Assemblies.
As a first step in the script we will verify that both paths exist.

As we only need the publishing assembly when applying a custom template, we will only load it when the $IsCustomTemplate switch parameter is passed in. When executing this script we are assuming there is a folder Templates at the execution path from which we will load the custom template when required. When $IsCustomTemplate is provided to the function call, we expect the parameter $SiteTemplate to contain the filename of the site template, i.e. "MyCustomTemplate.wsp".

If so desired, you could also create a local folder containing your assemblies and point the Add-Type calls to them.

Write-Host "Loading Assemblies`n" -ForegroundColor Magenta
$ClientAssembyPath = resolve-path("C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI") -ErrorAction Stop
$TenantAssembyPath = resolve-path("C:\Program Files\SharePoint Client Components\16.0\Assemblies") -ErrorAction Stop
Add-Type -Path ($ClientAssembyPath.Path + "\Microsoft.SharePoint.Client.dll")
Add-Type -Path ($ClientAssembyPath.Path + "\Microsoft.SharePoint.Client.Runtime.dll")
Add-Type -Path ($TenantAssembyPath.Path + "\Microsoft.Online.SharePoint.Client.Tenant.dll")

Add-Type -Path ($ClientAssembyPath.Path + "\Microsoft.SharePoint.Client.Publishing.dll")
$CurrentPath = Convert-Path(Get-Location)
$PackagePath = resolve-path($CurrentPath + "\Templates\" + $SiteTemplate) -ErrorAction Stop

Write-Host "Can't load assemblies..." -ForegroundColor Red
Write-Host $Error[0].Exception.Message -ForegroundColor Red

Batch create sites

For this script, I've chosen to create the Site Collections in batches. Calling ExecuteQuery after each operation takes too long, but creating 20 sites with one ExecuteQuery call is just too much for the web service to handle. We'll pick 5 as a default batch size, but feel free to choose what works best for you.
The idea is that we launch for instance 5 site collection creation operations in one go, and then wait for all the operations to complete. This will improve the execution time of our script.

First, we will generate the site collection names and put them in the $SiteNames variable. Each site name is a combination of the $Prefix parameter, the current index and the $Suffix parameter.
We will then grab a batch sized chuck of the site names array and copy them into the $BatchSiteNames variable. We will then perform all required operations for the sites in that batch.

$indexes = $StartIndex..($StartIndex + $NumberOfSites - 1)
[string[]]$SiteNames = @() 
$indexes | % { $SiteNames += ($SiteUrl + "/sites/" + $Prefix + $_ + $Suffix) }
Write-Host "The following sites will be created:" -ForegroundColor Magenta

$CurrentIndex = 0

While($CurrentIndex -lt $SiteNames.Length){ 
      	if($CurrentIndex + $BatchSize -le $SiteNames.Length){
$BatchSiteNames = $SiteNames[$CurrentIndex..($CurrentIndex+($BatchSize - 1))] 
$BatchSiteNames = $SiteNames[$CurrentIndex..($SiteNames.Length - 1)] 
Write-Host "`nProcessing batch of sites $CurrentIndex -> $($CurrentIndex + $BatchSiteNames.Length)" -ForegroundColor Yellow

#Site Creation logic goes here

$CurrentIndex += $BatchSiteNames.Length

Write-Host "Something went wrong..." -ForegroundColor Red
throw $Error[0]

Create Sites

Let's start with the first operation, which is deleting the site collection if it already exists! First, we'll need to get all the existing site collections from the SharePoint Online tenant, then we'll match the url's with the ones we are trying to exist. If we have a match we will delete the site collection.

#create the sites   
Write-Host "`nCreating the site collections`n" -ForegroundColor Magenta    
CreateSites -Sites $BatchSiteNames 

We will split up the logic into functions. Each function will execute one step of the site creation process. First we'll create the CreateSites function. This function will accept a set of site names, namely the ones we want to create in the current batch.

The function should be inside the CmdLet body, in order to have the parameters available.

We need to initialize a SiteCreationProperties object with our site name, url and other configuration properties.

You'll notice that we get a ClientContext object from the GetContext function, so we'll implement that one in the next step. When working with a ClientContext, we first need to perform a ClientContext.Load() on all the objects we want to use and read properties from, followed by an ExecuteQuery() to actually get the objects. With the Load we are queueing things to be loaded, while the EecuteQuery will then execute the load.

When we perform operations on the TenantAdministration.Tenant namespace in CSOM, what we get back is an SpoOperation object. On this object we can check if the operation has completed by reading the IsComplete property. As mentioned before, we will create a batch of operations first, execute the query, and then wait for all the operations to complete. As this can be implemented in a generic way for all the operations we are going to perform we'll write a function WaitForOperations that will do exactly that.

Notice that we will also create a generic ExecuteLoad function, as well as a generic ProcessError function.

function CreateSites($Sites){
        #Set Admin Context
        $Context = GetContext -Url $AdminUrl
        #Get the Tenant object
        $Tenant = New-Object Microsoft.Online.SharePoint.TenantAdministration.Tenant($Context)
        $Operations = @()
        $Sites | % {
            $SiteCreateUrl = $_
                #Set the Site Creation Properties values
                $properties = New-Object Microsoft.Online.SharePoint.TenantAdministration.SiteCreationProperties
                $properties.Url = $SiteCreateUrl
                $properties.Title = $SiteTitle
                    $properties.Template = $SiteTemplate
                $properties.Owner = $UserName
                $properties.StorageMaximumLevel = 250
                $properties.UserCodeMaximumLevel = 50
                $properties.TimeZoneId = 3
                $properties.Lcid = 1033
                #Create the site using the properties
                Write-Host "Creating site collection at url $SiteCreateUrl"
                $Operation = $Tenant.CreateSite($properties)
                $Operations += $Operation
                ProcessError("Error creating site $SiteCreateUrl")

        ExecuteLoad -ExecContext $Context

        WaitForOperations -JobContext $Context -Operations $Operations -Description "Site creation"


Let's impletement the GetContext function next. First we'll get a ClientContext object. Then we will add some Credentials to it, using the parameters that were passed in through the CmdLet.

On the ClientContext, we will change a few settings, as the defaults kept giving me errors.

Some errors that I had before these changes:

  • The underlying connection was closed: A connection that was expected to be kept alive was closed by the server.
  • Service endpoint not found

By switching the http protocol to version 1.0 and disabling KeepAlive on the webrequest, these errors seem to go away. I would have to spend some more time to figure out why these errors occur using the SPO web service.

function GetContext($Url){

$Context = New-Object Microsoft.SharePoint.Client.ClientContext($Url)      
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials(
$(ConvertTo-SecureString $UserPassword -AsPlainText -Force)
$Context.Credentials = $credentials
$Context.RequestTimeout = -1
$Context.PendingRequest.RequestExecutor.RequestKeepAlive = $true
$Context.PendingRequest.RequestExecutor.WebRequest.KeepAlive = $false
$Context.PendingRequest.RequestExecutor.WebRequest.ProtocolVersion = [System.Net.HttpVersion]::Version10

return $Context

Next, let's implement the ExecuteLoad function. In this function we will call the ExecuteQuery method on the ClientContext. The reason I put this in a separate function is that it allows us to build our own retry or reload logic when desired.

function ExecuteLoad([Microsoft.SharePoint.Client.ClientContext]$ExecContext, $ReloadObject, $Retry = 0){
            Write-Host "Something went wrong..." -ForegroundColor Yellow
            Write-Host $Error[0].Exception.Message -ForegroundColor Yellow
            if($Retry -lt 5){
                $Retry += 1
                Write-Host "Retrying ($Retry)..." -ForegroundColor Yellow
                ExecuteLoad -ExecContext $ExecContext -ReloadObject $ReloadObject -Retry $Retry 
                ProcessError -Message $Error[0].Exception.Message 

Once ExecuteQuery has been called, we have to wait for all the operations to complete. For this we'll write the WaitForOperations function. It will accept a collection of SpoOperation objects, on which there is a method RefreshLoad available, to check whether they are complete or not. It is important not to perform a Load on the SpoOperation again of course, as that will try to execute the operation again, which will cause errors like "Site already exists". Once the RefreshLoad method has been called, we have to call ExecuteQuery again to perform the actual refresh. While not complete we will wait for 10 seconds and loop.

function WaitForOperations($JobContext, $Operations, $Description){
    Write-Host "$Description executing..."
    $TotalOperations = $Operations.length
    if($TotalOperations -eq 0){
        Write-Host "Nothing to execute!"
    while ($true)
        $CompletedJobs = (($Operations | ? IsComplete -EQ $true) | Measure-Object).Count
        Write-Host "$Description status: $CompletedJobs of $TotalOperations completed!"
        if($CompletedJobs -eq $TotalOperations){
            Write-Host "Operation completed!" -ForegroundColor Green
        Sleep -Seconds 10
        $Operations | % { 
        ExecuteLoad -ExecContext $JobContext -ReloadObject $Operations

Also, let's implement the ProcessError method. This method will store the error in a collection variable we will print at the end of the script execution.
First, add this to the start of the CmdLet:

$ErrorLog = @() 

Implement the ProcessError method like this:

function ProcessError($Message){
    Write-Host "$Message`n" -ForegroundColor Red
    Write-Host $Error[0].Exception.Message -ForegroundColor Red
    $ErrorLog += $Message

Add the following code to the end of the cmdlet body:

if($ErrorLog.Count -ne 0){
    Write-Host "`nThe script was executed successfully, but some errors occured:" -ForegroundColor Red
    $ErrorLog | % {
        Write-Host "`n$_" -ForegroundColor Red
    Write-Host 'Check the $Error variable for more information' -ForegroundColor Red
    Write-Host "`nALL DONE! Virtual pat on the shoulder and everything!`n" -ForegroundColor Magenta   

Delete Sites

Before we (re)create sites, we should check if they exist already. If so, we will delete them. For this we need two steps:

  1. Delete the site
  2. Remove the site from the recycle bin – don't forget this step as creating sites at a URL is not possible when a site that was at that URL is still in the recycle bin

The CmdLet content should now look like this:

#Delete sites first if they exist!
Write-Host "`nDeleting existing sites`n" -ForegroundColor Magenta
DeleteSites -Sites $BatchSiteNames

#Recycle sites first if they exist!
Write-Host "`nRecycling deleted sites`n" -ForegroundColor Magenta
RecycleSites -Sites $BatchSiteNames

#now create the sites   
Write-Host "`nCreating the site collections`n" -ForegroundColor Magenta    
CreateSites -Sites $BatchSiteNames 

Let's implement the DeleteSites function.
In this function we will call the GetSiteProperties method to get all the existing site collections. In my opinion this is more efficient than trying to get a site collection by identity for each site collection you want to create. This depends on the number of site collections your tenant has of course.
We'll loop over the site collections and check if they are in the batch of sitenames we want to create. If so, we'll delete the site.

function DeleteSites ($Sites){
    Write-Host "Getting Sites"
    #Set Admin Context
    $Context = GetContext -Url $AdminUrl
    #Get the Tenant object
    $Tenant = New-Object Microsoft.Online.SharePoint.TenantAdministration.Tenant($Context)
    #Get all site collections
    ExecuteLoad -ExecContext $Context
    $Operations = @()
    $TenantSiteCollections | % {
        if($_.Url -in $Sites){
            $SiteDeleteUrl = $_.Url
                Write-Host "Site with url $SiteDeleteUrl will be deleted" -ForegroundColor Yellow
                $Operation = $Tenant.RemoveSite($SiteDeleteUrl)
                $Operations += $Operation
                ProcessError("Error deleting $SiteDeleteUrl")
    ExecuteLoad -ExecContext $Context -ReloadObject $Operations

    WaitForOperations -JobContext $Context -Operations $Operations -Description "Site deletion"


And in a pretty similar way, the RecycleSites function.

function RecycleSites($Sites){
    Write-Host "Getting Deleted Sites"
    #Set Admin Context
    $Context = GetContext -Url $AdminUrl
    #Get the Tenant object
    $Tenant = New-Object Microsoft.Online.SharePoint.TenantAdministration.Tenant($Context)
    ExecuteLoad -ExecContext $Context
    $Operations = @()
    $OperationsStarted = 0
    $TenantDeletedSiteCollections | % {
        if($_.Url -in $Sites){
            $SiteRecycleUrl = $_.Url
                Write-Host "Site with url $SiteRecycleUrl will be removed from the recycle bin" -ForegroundColor Yellow
                $Operation = $Tenant.RemoveDeletedSite($SiteRecycleUrl)
                $Operations += $Operation
                ProcessError("Error removing $SiteRecycleUrl from recycle bin")
    ExecuteLoad -ExecContext $Context -ReloadObject $Operations
    WaitForOperations -JobContext $Context -Operations $Operations -Description "Recycled sites deletion"

Let's now check if we need to apply a custom template.
A template would typically be saved from a site you created on SharePoint Online that you want to reuse. You may try with solutions from on-prem or that you found on the internet, but none are guaranteed to work as some features might be missing.
Add this to your CmdLet body:

#now apply the template if needed
Write-Host "`nApplying Custom Template`n" -ForegroundColor Magenta    
ApplyTemplate -Sites $BatchSiteNames

Let's implement the ApplyTemplate function. First we need to upload the solution file. Then we'll activate it. Next, we'll apply it to the rootweb of the site collection. After this we'll remove the uploaded file.

function ApplyTemplate($Sites){
    $Sites | % {
        $SiteApplyUrl = $_

            Write-Host "Uploading and applying template $SiteTemplate to $SiteApplyUrl" -ForegroundColor White

            $Context = GetContext -Url $SiteApplyUrl

            $PackageFileStream = New-Object System.IO.FileStream($PackagePath, [System.IO.FileMode]::Open) 
            Write-Host "Uploading template" -ForegroundColor Gray
            $SolutionGallery =  $Context.Web.Lists.GetByTitle("Solution Gallery") 
            $SolutionGalleryRootFolder = $solutionGallery.RootFolder

            $SPFileInfo = New-Object Microsoft.SharePoint.Client.FileCreationInformation 
            $SPFileInfo.Overwrite = $true 
            $SPFileInfo.ContentStream = $PackageFileStream 
            $SPFileInfo.URL = $SiteTemplate 

            $UploadedFile = $SolutionGalleryRootFolder.Files.Add($SPFileInfo)
            ExecuteLoad -ExecContext $Context

            Write-Host "Activating template" -ForegroundColor Gray

            $PackageInfo = New-Object Microsoft.SharePoint.Client.Publishing.DesignPackageInfo
            $PackageInfo.PackageName = $SiteTemplate 
            $PackageInfo.PackageGUID = [GUID]::Empty 
            $PackageInfo.MajorVersion = "1" 
            $PackageInfo.MinorVersion = "0"
            [Microsoft.SharePoint.Client.Publishing.DesignPackage]::Install($Context, $Context.Site, $PackageInfo, $UploadedFile.ServerRelativeUrl)
            ExecuteLoad -ExecContext $Context

            Write-Host "Applying template" -ForegroundColor Gray

            $AvailableTemplates = $Context.Site.GetWebTemplates(1033, 0)
            ExecuteLoad -ExecContext $Context

            $Context.RequestTimeout = 480000
            $Context.Site.RootWeb.ApplyWebTemplate($AvailableTemplates[$AvailableTemplates.Count - 1].Name)
            ExecuteLoad -ExecContext $Context

            Write-Host "Deleting temporary file" -ForegroundColor Gray
            ExecuteLoad -ExecContext $Context

            Write-Host "`n"
            ProcessError("Error applying template to site $SiteApplyUrl")

As a final step we'll add the user from the $SiteOwner parameter as a site collection administrator.
Add this to the CmdLet body:

Write-Host "`nSetting Security`n" -ForegroundColor Magenta    
SetSecurity -Sites $BatchSiteNames 

Let's implement the SetSecurity function:

function SetSecurity ($Sites){
    $Sites | % {
            Write-Host "Adding site collection admin $SiteOwner to $_" -ForegroundColor White

            $Context = GetContext -Url $_

            $User = $Context.Web.EnsureUser($SiteOwner)
	        ExecuteLoad -ExecContext $Context

	        $User.IsSiteAdmin = $true
	        ExecuteLoad -ExecContext $Context

            ProcessError("Error applying security to site $_")

That's it! We should be good to go and create endless batches of SharePoint Online site collections!

Have fun ;)

You can download the script, it is attached to this post...







U2U.CreateOnlineSiteCollections.ps1 (15,4KB)