Stephanie Kahlam
This will be the first in a series of posts about using Office 365 (specifically SharePoint Online) as a Learning Management System. In this post we will look at bulk creating sites, which can be used to create a SharePoint site for each of your school’s courses.
These posts assume knowledge of SharePoint Online administration and some basic knowledge of PowerShell.
Introduction:
One common task that System Administrators and IT Pros face in school environments is the need to reproduce a task a large number of times, for example for each course or for each students. In large schools with hundreds of courses and thousands of students this can put enormous pressure on time and resources. This is compounded by the fact that often these tasks need to be completed on a tight timeline at the busiest time of year (ie. The week before school starts).
Take the following scenario. A great way to make use of Office 365 and specifically SharePoint Online is to create a site for each course. This can be done with a template so that each course gets a predefined set of lists and libraries (ie. A calendar for homework, a document library for the teacher to upload resources etc.) The idea of creating templates or, if you want to get really fancy, feature stapling is the subject for another post (or perhaps more aptly a series of posts).
By giving each course its own site you create a collaboration space for students and teachers. It can be a fantastic way to look after distributing content and materials, communicating with students and parents through announcements and calendars and perhaps even using InfoPath forms to have students complete quizzes and assignments. (Again the subject for a future post).
This post describes a method for completing the scenario described above by using PowerShell. While Microsoft has released a fantastic set of tools for managing SharePoint Online with PowerShell (The SharePoint Online Management Shell – https://technet.microsoft.com/en-us/library/fp161388.aspx) it lacks one crucial ability that we need to make the magic described above happen. We need to be able to create a subsite (sometimes referred to as a web). SPO Management Shell allows you to create site collections but not subsites. What we will use instead is referred to as the Client Object Model (CSOM). Ed Wilson (the Microsoft Scripting Guy) has a fantastic post on the method used (http://blogs.technet.com/b/heyscriptingguy/archive/2014/04/24/create-and-manage-sharepoint-online-sites-by-using-powershell.aspx) and full credit goes to him for the technique.
While this post is tailored for education the method can easily be repurposed for business (ie. Quickly creating a site for each organizational unit or department).
Step 1: Create a site collection to hold the courses
The first step is to create a site collection that will hold all the course sites we are about to create. This is completed through the SharePoint Admin area of Office 365. Think of this as the bin that will hold all of your courses.
Pro tip: Create a site collection with the semester code or year attached to it so that archiving courses in the future can be easily handled. For example:
https://<your domain>.sharepoint.com/sites/courses2014-2015/
Because the sites we are about to create will inherit permissions from their parent (ie. The Site Collection) setup the permissions that you want the courses to have at the Site Collection level. In most cases put your teachers into the Members group so that they can create and manage content and put the students into the Visitors group so that they have read only access.
Step 2: Create a CSV file with your course information
We need a CSV file that defines all the courses that we will be creating sites for. A great way to do this is using the reporting features common to many Student Information Systems. Create a report of all active courses and export it to Excel. Then use Excel to manipulate it into the format described below.
To use the script described in this post ensure your CSV file has two columns: CourseTitle and CourseURL (they must be named exactly so – without spaces – for the script to be able to process the file properly).
The CourseTitle column holds the user readable name for the course (ie. Biology 30).
The CourseURL column holds the values that will makeup to URL to the course site. For example:
https://<your domain>.sharepoint.com/sites/courses2014-2015/<CourseURL>
The URL column can’t have spaces or certain special characters. (Details here: http://support.microsoft.com/kb/905231)
There are a couple ways to go with the URL. One way (if you are using Excel to create your CSV file) is to use a formula to generate the URLs. For example:
This formula takes out the spaces in the Course Title and replaces them with dashes and makes the whole thing lower case.
Another way to go is to use something along the lines of a “course code” for the URL. Most student information systems have some kind of value that they use as a unique identifier for each course. Using this kind of identifier is a great way to go as it allows you to programmatically interact with your sites further down the road.
Save the csv file as something like courses.csv and put it in the folder where you will create your PowerShell script.
Step 3: Create and run the script against your CSV file
Now that your CSV file is ready to go we need to create the PowerShell script. At the end of the post you will find the script in its entirety. Copy and paste it into a text editor and save it with the .ps1 extension. Modify the variables that need to be changed for your environment and run it in PowerShell.
The first step is to load the client assemblies for SharePoint into our script. If you are running this script on a machine that does not have SharePoint or Visual Studio installed you will have to download and install the SharePoint 2013 Client Components SDK (http://www.microsoft.com/en-ie/download/details.aspx?id=35585). Once you have the SDK installed you can easily add the assemblies to your script:
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime")
Then we are going to import the data from the CSV file created above into a variable. If you saved the CSV in a different directory than the PowerShell script you will have to put in the full path:
$courses = Import-Csv "courses.csv"
We create a variable with the site collection URL where we will be creating our course sites.
$siteCollectionUrl = “https://<your domain>.sharepoint.com/sites/courses2014-2015/
Next we create a client context to use to create our course sites. We specify the user who will be executing the operations (ie. Your admin account). This account must have permission to create sites in the specified site collection. The password is prompted for at runtime.
$clientContext = New-Object Microsoft.SharePoint.Client.ClientContext($siteCollectionUrl)
$username = "[email protected]"
$password = Read-Host -Prompt "Enter password" -AsSecureString
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $password)
$clientContext.Credentials = $credentials
We then loop through each course in the CSV file and create its site. First we create a WebCreationInformation object that stores the Title and URL values from our CSV file.
foreach ($course in $courses) {
$courseInformation = New-Object Microsoft.SharePoint.Client.WebCreationInformation
$courseInformation.Url = $course.CourseURL
$courseInformation.Title = $course.CourseTitle
Here we are using the STS#0 template. This is SharePoint’s code for the Team Site template. This means that all the course sites provisioned will be Team Sites. This is where if we created a custom course site template we could specify its code instead.
$courseInformation.WebTemplate = "STS#0"
We create the web using the WebCreationInformation object created above.
$newWeb = $clientContext.Web.Webs.Add($courseInformation)
We then load the web into the client context and execute the query to process the request on SharePoint Online.
$clientContext.Load($newWeb)
$clientContext.ExecuteQuery()
# Display created message
We then write a status message with the courses title that was just created.
Write-Host "Created: " $newWeb.Title
}
The full script can be found below. Once the execution is complete when you go to the Site Content for the courses site collection created in the first step above you will see your newly provisioned courses:
The next step from here is to create a dashboard and lading page so that when students and staff login they can easily jump into their courses. But once again… this is a subject for a future post.
Enjoy!
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime")
# Import the courses.csv file - change this to the name of the CSV file you created.
# If the file is not located in the same directory as the PowerShell script insert
# the full path to the file.
$courses = Import-Csv "courses.csv"
# Set the URL of the SITE COLLECTION where you would like to create the course sites.
$siteCollectionUrl = “https://yourtenant.sharepoint.com/yoursitecollection”
# Create a client context for the site Collection so that we can interact with it
$clientContext = New-Object Microsoft.SharePoint.Client.ClientContext($siteCollectionUrl)
# Specify the credentials that will be used to complete the operation.
# Change the username variable to the account that you wish to use.
# This account must have permission to create sites in the site collection specified.
$username = "[email protected]"
# Prompt for password at runtime.
$password = Read-Host -Prompt "Enter password" -AsSecureString
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $password)
$clientContext.Credentials = $credentials
# Loop through each course in the CSV file and create it
foreach ($course in $courses) {
$courseInformation = New-Object Microsoft.SharePoint.Client.WebCreationInformation
$courseInformation.Url = $course.CourseURL
$courseInformation.Title = $course.CourseTitle
$courseInformation.WebTemplate = "STS#0"
$newWeb = $clientContext.Web.Webs.Add($courseInformation)
$clientContext.Load($newWeb)
$clientContext.ExecuteQuery()
# Display created message
Write-Host "Created: " $newWeb.Title
}
Please note: Scripts in this blog are provided as is with “no-warranties”. They should be run by admins with a knowledge of the systems that will be affected.