Use PowerShell to Automate Site Collection Setup in SharePoint Online

In a recent blog post, I illustrated how to use PowerShell to bulk provision security groups and users in SharePoint Online using CSV files. That blog assumed you already had one or more site collections to which you wanted to add security groups and users. However, if you do not yet have site collections, you can also create them in PowerShell, either manually or automatically. In this post, we will again use a CSV file to create multiple site collections with a simple PowerShell script.

PowerShell enables creating site collections in SharePoint Online with the cmdlet New-SPOSite. While this cmdlet is useful for creating one or a few site collections, it’s cumbersome to invoke it repeatedly if we want to provision a larger number of site collections. Fortunately, as explained in my previous blog, PowerShell has a powerful cmdlet that enables bulk provisioning in SharePoint: Import-CSV. This cmdlet treats the first line of a CSV file as “Headers” or field names, and succeeding lines as “records”. Thus, we can use a combination of the Import-CSV and New-SPOSite cmdlets to automate the process.

The procedure in this blog assumes you have the following prerequisites:

  • An enterprise-level tenant, and login credentials that provide administrative permissions. In this blog, we will use the administrative alias “admin”.
  • A list of site collections you wish to create. It is assumed that your site collections will have URLs similar to https://<tenant>.sharepoint.com/sites/<sitecollection> where <sitecollection> is the name of the site collection; for example a sales site collection could be located at the URL https://<tenant>.sharepoint.com/sites/sales. If your tenants and site collections have different naming conventions, the Excel file can easily be customized.
  • For each site collection, assign the following attributes:

             a.  Site collection template (see a list of templates at the end of the blog)
             b.  Site collection URL
             c.  Resource quota (default is 300)
             d.  Storage quota, in MB (minimum is 50)
             e.  Time zone ID (see this link for a list; 10 corresponds to Eastern Time (U.S. and Canada)
             f.   Site collection name, e.g. “Contoso Team Site”

  • Install the following software (in order):

             a.  .NET Framework 4.0; see http://www.microsoft.com/en-us/download/details.aspx?id=17851
             b.  PowerShell 3.0 (part of Windows Management Framework 3.0); see http://www.microsoft.com/en-us/download/details.aspx?id=34595
             c.  SharePoint Online Management Shell; see http://www.microsoft.com/en-us/download/details.aspx?id=30359

Follow these steps to create the Excel workbook from which you will subsequently create the CSV file.

  1. Open Excel.
  2. Save the workbook to a convenient location as CreateSiteCollections.xlsx.
  3. In cells A1:G1, enter the following headings:   Owner   StorageQuota   Url   ResourceQuota   Template   TimeZoneID   Name
  4. In cells A2:G2, enter data for the first site collection you wish to create. For example:  admin@<tenant>.onmicrosoft.com   100   https://<tenant>.sharepoint.com/sites/TeamSite01   100   EHS#1   10   Contoso Team Site

  5. Repeat adding data in cells A3:G3, A4:G4, etc. for the remaining site collections. You should have a worksheet similar to the following example: 7-10-2013 2-26-53 PM 2
  6. Save the file to C:\Scripts as NewSiteCollections.csv (If you do not yet have this folder, create it.)
  7. Accept the reminders concerning CSV files.

Now, we’ll create the PowerShell script. Open NotePad and copy the following text block into it (you should have three separate lines): 

$csvfile = ‘c:\scripts\NewSiteCollections.csv’
Connect-SPOService -Url https://<tenant>-admin.sharepoint.com -Credential admin@<tenant>.onmicrosoft.com
Import-Csv $csvfile | where {New-SPOSite -Owner $_.Owner -StorageQuota $_.StorageQuota -Url $_.Url -NoWait -ResourceQuota $_.ResourceQuota -Template $_.Template -TimeZoneID $_.TimeZoneID -Title $_.Name}

  1. For <tenant>, substitute the name of your tenant (two substitutions).
  2. Save the file to C:\Scripts as CreateSiteCollections.ps1.
  3. Start SharePoint Online Management Shell with elevated permissions (Start > SharePoint Online Management Shell > Run as Administrator).
  4. Ensure that you will be able to run your script: at the PowerShell prompt, type or copy/paste the following cmdlet and press ENTER:

    Set-ExecutionPolicy RemoteSigned

  5. Press Y to confirm. 
  6. If needed, change your directory to C:\Scripts.
  7. At the PowerShell prompt, type the following line and press ENTER: 

    .\CreateSiteCollections.ps1

  8. At the login screen, enter the password for your user and press ENTER. 7-10-2013 2-46-23 PM
  9. Wait for the PowerShell prompt to reappear (it may take several minutes). If you see error messages, they are usually due to incorrect or misspelled URLs, owners, etc. Check to make sure your CSV file is correct and try again. 

We will now confirm that the new users and groups were added to the tenant.

  1. At the PowerShell prompt, copy/paste the following text and press ENTER: 

    Get-SPOSite -Detailed | Sort-Object StorageUsageCurrent -Descending | Format-Table Url, Template, WebsCount, StorageUsageCurrent, StorageQuota, ResourceUsageCurrent, LastContentModifiedDate -AutoSize

  2. Note the new site collections that have been added (your screen may not exactly match the screen shot). 7-10-2013 2-59-29 PM

In summary, this blog showed you how to bulk create site collections in SharePoint Online, using a CSV files and simple PowerShell script. Enjoy!

As promised, here is a list of  Site Collection Templates: 

7-11-2013 5-34-45 PM

Leave a Reply

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