Using Powershell to Help Migrate Your Users' Connection to Sharepoint to a New Site

If you are using many SharePoint lists and want to connect them to Outlook automatically for your users, you have probably noticed that the official tools for this are lacking. We don’t get nearly the simplicity of Exchange Public Folders without a lot of work, from the difficulty in just getting the SharePoint URLs in the format Outlook requires, to deploying those lists with the limited Group Policy options Microsoft provides. This situation isn’t really acceptable once you get above 10-20 lists, so I spent some time putting together some tools in Powershell that make this process a little less painful. The full set of scripts can be found here.

Deploying SharePoint lists the manual way

For many years, Microsoft has offered SharePoint as the path to migrate away from Exchange Public Folders. Despite this positioning, Microsoft offers just one tool to help with connecting those lists to Outlook like the Public Folders you’re replacing : the Office 2013 Group Policy Administrative Templates. That tool has some serious limits.

Here’s what connecting your newly migrated lists to Outlook looks like, the manual way:

1. From the Ribbon in SharePoint, connect each individual list you want to publish to Outlook.
2. Right-click on each list in Outlook and share the list to an external email acount (to make the list URL visible)
3. Copy the URL from the email.
4. Create a new GPO for each department, using the “Default SharePoint Lists” policy. (User Configuration | Policies | Administrative Templates | Microsoft Outlook 2013 | Account Settings | SharePoint Lists | Default SharePoint Lists.) (If you haven’t already done so, you’ll need to copy the Administrative Templates to your Domain Controller’s Policy Definitions Folder).
5. For each list, copy the URL and type in a name for the list that will display once it’s connected to Outlook.

Automating Deployment of  SharePoint Lists with Powershell

I actually had to deploy SharePoint lists to our users twice, because after we migrated to SharePoint on-premises, shortly after we did a second migration to SharePoint Online. I did it the manual way the first time, but didn’t want to repeat the mistake for the second migration. Here’s what I found to be the biggest pain points:

1. Obtaining the list URLs in Microsoft’s proprietary STSSync encoding format.
2. Creating the nearly identical GPOs for each department.
3. Copying over the URLs, and then typing in the name to describe each list. It was a remarkably tedious and error-prone process.
4. There’s no built-in way to mass-remove or update a SharePoint list in Outlook.

I solved each of the problems above with PowerShell.

Generating an STSSyncURL with PowerShell from a SharePoint Online Site

As far as my research turned up, the STSSync URI is not available with built-in SharePoint Powershell methods. It is well documented, but it uses a proprietary encoding.

Here’s a sample URI:

 stssync://sts/?ver=version&type=folder-type&cmd=command-name&base-url=sts-url&guid=the-guid&site-name=site-friendly-name&list-name=list-friendly-name&list-url=list-url&user-id=uid&folder-url=relative-url&folder-id=id

Here’s what goes into the URI:

• A URI prefix and command which will be the same for all of our lists
• List Type: one of “calendar”, “contacts”, “discussions”, “documents”, or “tasks”
• GUID that uniquely identifies the list
• A “base” URL and “list” URL which point to the specific location of the list on the SharePoint site.
• A “site” and “list” name of your choice

Here’s the code to generate STSSYNC URLs from a SharePoint Online site (a little too long to embed).

Automating GPO creation

Once we have our nicely formatted URLs, Powershell can also help us turn them into GPOs. As of Server 2012 R2, the cmdlets for manipulation GPOs in Powershell are limited to just setting values in the registry, but that’s enough to get our job done. As a bonus, GPOs that set registry values can coexist, allowing us to set up a “master” GPO with our shared lists, and not have it overwritten by a more specific GPO.

The stssyncurl script linked above outputs objects with three properties: “siteName”,”listName”,”stssyncurl”, suitable for piping to export-CSV. I created a second script that relies on those 3 properties, plus two more: gpoName and OU. To add those two values, I exported the output of the script to a CSV, added the two columns, and used Excel’s autofill to add in the appropriate GPO names and OUs. The GPO will only be created once. For subsequent rows in the CSV, the existing GPO will be updated.

Quick way to get the distinguishednames for your OUs:

get-adorganizationalunit -filter * -searchbase “ou=MYDEPARTMENTBASE,dc=Contoso,dc=com” | select distinguishedname 

Great! Now when your users next close and reopen Outlook, they’ll see those SharePoint lists.

Cleaning up old SharePoint lists from Outlook

As I mentioned above, not only did I have to add these SharePoint lists to Outlook, because we were migrating, I also wanted to remove the old lists for my users. As nice and “simple” as it is to add a SharePoint list to Outlook with Group Policy, unfortunately r
emoving the same list isn’t as easy.

I looked at several methods, but the most reliable seems to deploy a script on each machine that connects with Outlook MAPI and deletes the lists that I specified.

It’s a short script and the Outlook MAPI namespace is pretty simple. It will only work if the user is logged in. I deployed my script with System Center Configuration Manager as an application, but you could also set this up as a login script. I noticed that if Outlook was already open, it appeared to take a few runs to clean up all of the lists I had specified.