Home

Office 365 Advent Calendar – 04 Moving/Copying files within SharePoint Online Libraries

office-365-advent-calendar

Scenario

You have a couple of files within a SharePoint Online document library which you want to move within the same library, for example to a new folder.

Steps

Note: The following steps are only available in the ‘Modern Experience’ for document libraries. If you’re still using the ‘Classic Experience’ (for example because your tenant settings have been configured to use Classic), then you can’t make use of this new functionality yet.

In my example here, I have a library with 4 reports as well as a newly created ‘Reports’ folder. I want to move the 4 reports into the new folder.move-copy-files-sharepoint-online-1

 

Select all files that you want to copy or move. The options for the library change and you can now see ‘Delete’, ‘Move to’, and ‘Copy to’. I’m going to use ‘Move to’

move-copy-files-sharepoint-online-2

 

The next step now is to choose where to move the items to. Moving and Copying is only possible within the same library, at the moment you can’t move/copy documents to other libraries. You can also create a new Folder in the current location that you navigated to in the sidepane.

move-copy-files-sharepoint-online-3

 

After I selected where I want to move my documents, I simply need to click the ‘Move here’ button.move-copy-files-sharepoint-online-4

 

And that’s it, my 4 reports are now in my newly created folder.

move-copy-files-sharepoint-online-5

 

Office 365 Advent Calendar – 03 Programatically accessing a SharePoint Online Recycle Bin

office-365-advent-calendar

Scenario

You’ve got a big amount of items in the recycle bin of a site which you want to access. For example, a user accidentally deleted hundreds of files, and now you want to restore them back easily. Or you have a thousand items in the recycle bin and you want to export some information (which items are in the recycle bin, who deleted them and when) to Excel

Code

Once again, we’re using the PnP PowerShell cmdlets here as a basis, but then also make use of the underlying CSOM calls to access the recycle bin.

$cred = Get-Credential
#Set URL to the web for which you want to retrieve the recycle bin
Connect-PnPOnline -Url https://mytenant.sharepoint.com/sites/departmentsite -Credentials $cred

$web = Get-PnPWeb
$ctx = Get-PnPContext

#Loading the Recycle Bin Items
$recycleBin = $web.RecycleBin
$ctx.Load($recycleBin)
$ctx.ExecuteQuery()

#We are now narrowing down the items. We only want to restore all Excel spreadsheets
$spreadsheets = $recycleBin | Where {$_.Title -like "*.xlsx"}

#Another example: select all items deleted by a specific user
#$deletedByUser = $recycleBin | Where {$_.DeletedByEmail -eq "rene@mytenant.onmicrosoft.com"}
#Restoring the spreadsheets
$spreadsheets | %{ $_.Restore() }
$ctx.ExecuteQuery()

#Exporting all items to a CSV file
$recycleBin | Select Title, DeletedByEmail, DeletedDate | Export-Csv C:\RecycleBinItems.csv

Example of a CSV export:

TitleDeletedByEmailDeletedDate
Project_Template.pptxrene@mytenant.onmicrosoft.com12/3/2016 1:19
Azure_Information_Protection_licensing_datasheet_EN-US.pdfrene@mytenant.onmicrosoft.com12/3/2016 1:20
Security in Office 365 Whitepaper.docxrene@mytenant.onmicrosoft.com12/3/2016 1:20

If you are curious which methods and properties are available for the recycle bin in general as well as individual items, please review RecycleBinItemCollection and RecycleBinItem.

Office 365 Advent Calendar – 02 Retrieving SharePoint Online Site Collection Sizes

office-365-advent-calendar
Scenario

You want to get an overview of the storage usage of all site collections in your tenant, similar to what you can see in the “SharePoint Administration” section in Office 365. But as you may have hundreds of site collections, or as you want to pass on that information to someone else, you want to extract this information into a spreadsheet

Code

$cred = Get-Credential
Connect-PnPOnline -Url "https://mytenant-admin.sharepoint.com" -Credentials $cred
$sites = Get-PnPTenantSite -Detailed | select Url, StorageUsage, StorageMaximumLevel, @{label="Usage in %";Expression={[math]::Round($_.StorageUsage*100/$_.StorageMaximumLevel,2)}}
$sites | Export-Csv C:\SiteStorage.csv

Output

Here is a sample output:

UrlStorageUsageStorageMaximumLevelUsage in %
https://mytenant.sharepoint.com/12351024012.06
https://mytenant.sharepoint.com/portals/hub1102400.01
https://mytenant.sharepoint.com/search27102400.26
https://mytenant.sharepoint.com/sites/americas43231024042.22
https://mytenant.sharepoint.com/sites/asia149102401.46
https://mytenant.sharepoint.com/sites/emea109102401.06

Note: StorageUsage and StorageMaximumLevel are shown in MB

Office 365 Advent Calendar – 01 Getting all Permissions Levels in SharePoint Online

office-365-advent-calendar

Scenario

You want to get an overview of all Permission Levels that are set up in your tenant’s SharePoint Online site collections. Not only the out-of-the-box permission such as “Full Control” or “Read”, but also any custom permission levels which may have been defined by your site collection’s administrators (“Add Only” is a common example).

Code

A prerequisite for this code are the OfficeDev PnP PowerShell cmdlets. Ensure that you have a current version installed before proceeding.
The following code connects to your tenant and fetches all site collections (with a small where query, as I wanted to limit it to all sites in the /sites/ or /teams/ paths). It then loops through all site collections, retrieves the permission levels, and writes that information into a CSV file.

$cred = Get-Credential
Connect-PnPOnline -Url https://mytenant-admin.sharepoint.com -Credentials $cred
$sites = Get-PnPTenantSite | where {$_.Url -like "*mytenant.sharepoint.com/sites/*" -or $_.Url -like "*mytenant.sharepoint.com/teams/*"}
foreach($site in $sites) {
	Connect-PnPOnline -Url $site.Url -Credentials $cred
	$ctx = Get-PnPContext
	$roleDefs = (Get-PnPWeb).RoleDefinitions
	$ctx.Load($roleDefs)
	$ctx.ExecuteQuery()
	foreach($rd in $roleDefs) {
		Add-Content ".\permissionlevels.csv" "$($site.url), $($rd.Name)"
	}
}

Office 365 Advent Calendar – 24 Days of Goodies

office-365-advent-calendar

Christmas is approaching, and I’ve decided to spread some joy and share some knowledge  by running an Office 365 Advent Calendar on my blog from December 1 – December 24 2016! This means that on each of these days in December, I’ll publish a small blog post with some Office 365 goodies – PowerShell scripts, helpful advice, guidance, …. While the content will mainly be focused on SharePoint Online, the other Office 365 services may be referenced in some posts as well.

When will new content be posted?

A new blog post will be published every day from December 1 – December 24 at 8am GMT.

How can I stay up to date on this and get notified of new content?

There are a few ways how you can do this:

  1. Bookmark http://modery.net/office-365-advent-calendar/ and visit it on a daily basis
  2. Subscribe to this blog’s RSS feed
  3. Get notified via email, for example via https://blogtrottr.com
  4. Follow me on Twitter, I’ll tweet updates at least once a day

Using Azure Functions to receive daily Website Updates via Email

Note: In this blog post, I’m talking about Azure Functions, which are currently in preview. Settings, functionality, pricing, etc. may still change.

Introduction

I’m constantly trying to optimise my life by automating small tasks, so that I can either save some time and get things done without having to remember them. For example, I’m a big fan of both IFTTT and Instapaper, and use these two in combination to have new blog articles (e.g. from dev.office.com/blogs) posted into my Instapaper account automatically, so I can’t read those easily and quickly when I’m travelling to and from work. I do not need to check on a daily basis for new articles, and I can start reading new ones the moment I’m on public transport.

Recently, I started looking at Azure Functions. If you’ve never heard of them before, I recommend starting with the official Azure Functions Overview for an introduction, followed by Scott Hanselman’s and Troy Hunt’s recent blog post on potential use cases. After playing around for a short while with them, I thought I’d give them a chance for a mini-project: I’ve been following Packt Publishing’s Free Learning eBook offer for a while now. When I say follow, I actually mean I open my browser, open the page, and check which new eBook is available.

Pack Publishing Free Learning eBook Offer

This obviously takes some time, and additionally I forget to do it on a daily basis. While I do not get every free daily eBook, I still want to know if there’s something interested published. So, what I was looking at doing was to implement a small website monitor that checks the offer page once per day and sends me an email with the offer details. Quite simple, should be up and running within a short time.

 

Preparation

First of all, you obviously need an Azure account. In addition to that, you need a SendGrid account to send out emails. Luckily, Azure subscribers can get a free SendGrid account with up to 25,000 emails each month. More than enough for personal use! Within Azure, click on New, type in SendGrid, and follow the steps to add it:

Azure SendGrid

Once done, create a new API Key in SendGrid and keep it somewhere (as we will need it later).

Furthermore, you also need to add a Function app in Azure if you haven’t already done so. When you create a Function app, you need to specify whether to use a Dynamic App Service Plan or a Classic one. For our small exercise, Dynamic is the best choice (“You don’t have to worry about resource management, and you only pay for the time that your code runs.“). Read more about the differences on How to scale Azure Functions.

Note: You can review the pricing for Functions here (note: Functions are still in Preview, pricing may change). You can also estimate your total cost by using the Azure Pricing calculator. Here’s my estimate for the Function I’m talking about:

azure-functions-pricing

Yes, you can safely run your Function for quite a couple of times before getting charged for it.

 

Once both the Function App and the SendGrid API Key are available, you need to add the key to the Function App’s settings. Open your Function App, go to Function app settings, and select Configure app settings.

azure-functions-app-settings

Under App settings, add a new setting with AzureWebJobsSendGridApiKey as key and your SendGrid API Key as value.

azure-functions-app-settings-sendgrid-api

Once that’s done, save your changes. Your Function apps are now capable of sending out emails via SendGrid.

Creating the Function

In your Function App, create a new Function. Microsoft provides several templates, we’ll use the simplest one and do all the plumbing from scratch (but feel free to check out the available templates to learn how you can integrate for examples Azure Queues or Azure Blob Storage), so select the “Empty – C#” template and give it a name.

Once created, each Function offers you 4 pages where you can manage it – Develop, Integrate, Manage, and Monitor.

azure-functions-pages

Let’s start with setting up the basic “structure” of our Function on the Integrate page. Initially, as we’ve chosen the Empty template, there are no Triggers, Inputs, or Outputs defined. What we want to set up is a Timer trigger (to run our code on a daily basis) and a SendGrid Output (to inform us about the latest offer):

azure-functions-integrate

First, add a new Trigger and select the Timer trigger template. Set the Schedule to 0 0 0 * * *. which means that the Function runs every day at midnight (once you add a Timer trigger, the page will also provide some documentation on the format for this in case you want to define your own schedules). Remember to click Save.

azure-functions-integrate-timer

Next, add a SendGrid Output. You have the possibility to configure a couple of required parameters either on the configuration page or in your code. Leave the Message parameter name as it is (this is the variable we’ll use in our code), and define the other values as required. As I’m sending the email to myself, I added my email address to both the To and From fields. The Subject and Body will get defined in the code.

azure-functions-integrate-sendgrid

Save again, and click on Develop. At the top of the page you’ll see the Code section where you can define the underlying code of your Function.

azure-functions-develop-code

Copy the following code sample and replace any existing code in your Function:

#r "SendGridMail"

using System;
using SendGrid;

 static string packtUrl = "https://www.packtpub.com/packt/offers/free-learning/";

 public static void Run(TimerInfo myTimer,  out SendGridMessage message, TraceWriter log)
 {
     message = null;    
     try
     {
        using (var httpClient = new HttpClient())
         {
             string pageContent = httpClient.GetStringAsync(packtUrl).Result;
             //the content we're looking for comes after a div tag with the class dotd-title
             int tmpIndex = pageContent.IndexOf("dotd-title");
             string offerDetails = pageContent.Substring(tmpIndex +15, 3000);
             //we've now got more content than required, removing anything that is not part of the article description
             offerDetails = offerDetails.Substring(1, offerDetails.IndexOf("dotd-main-book-form")-20);
             offerDetails += String.Format("<br/><br/><a href='{0}'>PacktPub Free Learning</a>",packtUrl);
             //parsing the book title here
             string bookTitle = offerDetails.Substring(offerDetails.IndexOf("<h2>")+4,
                                 offerDetails.IndexOf("</h2>")-9).Trim();
             
             message = new SendGridMessage()
             {
                 Subject = String.Format("Packt's Free Learning Offer: {0}",bookTitle),
                 Html = offerDetails
             };

             log.Info("Finished successfully");
         }
     }
     catch (Exception exc)
     {
         log.Info("Exception:" + exc.Message + exc.StackTrace);
     }
 }

Note the following:

  1. The first line (starting with #r) is used to add a reference to an external assembly (documentation)
  2. public static void Run is the main function which gets called when your Function runs. As you can see, we have 3 arguments for it here. TimerInfo myTimer needs to be added as we’re using a Timer trigger. out SendGridMessage message is the SendGrid email output which we defined in the Integration configuration. We need to instantiate it and set any required properties if we want to send an email. Lastly, TraceWriter log provides a mechanism to log any information that you want during your code execution – errors, warnings, general stuff. Anything written to this log gets displayed in the Log section just below your Code section.
    azure-functions-develop-logs
  3. As for the code fetching and extracting the information I want to receive via email, this is pretty straightforward. The few lines I have here instantiate a new HttpClient object, read the offer page content, and fetch the title and description (not in the nicest of ways, but hey, it’s working!).
  4. Lastly, we’re creating our SendGrid email message and assign it the appropriate subject and email body.

Here’s the final result once the code was executed (either when and the email was sent:

azure-functions-packtpub-email

 

That’s it! A couple of minutes of configuration and coding, and I now receive daily email alerts about the latest free eBook.

User Group presentation: OneDrive for Business – Current State and New Features

Yesterday, I talked at the SharePoint Community meetup about “OneDrive for Business – Current State and New Features”. Here are the slides that I used:

Getting your Office 365 tenant’s SharePoint site structure with PowerShell

Introduction

From time to time you may want to get an overview of the structure of your SharePoint Online environment. That is, you want to know the number of site collections and subsites, and know how they are organised.

While you could review the existing site collections either in the UI (not very convenient once you have more than 20) or via PowerShell, both approaches don’t provide you with details about a site collection’s structure itself. How many subsites are there, how are they organised? How many levels deep do they go, or are there dozens of subsites located directly underneath a specific site collection?

Preparation

Just like in my recent post, what you need are the OfficeDev PnP PowerShell cmdlets available at https://github.com/OfficeDev/PnP-PowerShell (review the installation instructions on that page if you haven’t installed those awesome cmdlets yet) and an account which has been assigned either the “SharePoint administrator” or “Global administrator” role in your tenant. Additionally, this account needs to have access to all SharePoint Online site collections – this is where the script to set site collection administrators on all site collections comes in very handy.

The Script

What the script does is the following: it first fetches the list of all site collections (apply a filter if necessary) and then collects the structure for each site collection by going through all subsites. Lastly, it writes the collected information into a CSV file for further manual analysis, e.g. in Excel.

#Config Variables - update these as required
$tenant = "mytenant"
$ReportPath = "SPOStructure.csv"

# Note: If you run this script regularly, please have a look at
# the following site to see how you can store credentials securely in Windows
# https://github.com/OfficeDev/PnP-PowerShell#settings-up-credentials
$cred = Get-Credential
Connect-SPOnline -Url "https://$($tenant).sharepoint.com" -Credentials $cred

write-host "Getting all sites"
#Note: we do not make use of the IncludeOneDriveSites parameter here,
# which would include personal sites as well
#You could also filter here to get only specific sites, or use
# the -Url parameter for the Get-SPOTenantSite to get the structure of a single site collection only
$tenantSites = Get-SPOTenantSite

function Get-SPOSubWebs($Context, $RootWeb){ 
	$arrWebs = @()
        $Webs = $RootWeb.Webs 
        $Context.Load($Webs) 
        $Context.ExecuteQuery()  
        ForEach ($sWeb in $Webs) 
        { 
	    $arrWebs  += $sWeb.Url
            $arrWebs += Get-SPOSubWebs -RootWeb $sWeb -Context $Context 
        } 
	return $arrWebs
    } 

$allWebs = @()
foreach($site in $tenantSites) {
    write-host "Connecting to $($site.Url)"
    Connect-SPOnline -Url $site.Url -Credentials $cred
    $allWebs += $site.Url
    $allWebs += Get-SPOSubWebs (Get-SPOContext) (Get-SPOWeb)
}
write-host "Finished"

$allWebs | Out-File -FilePath $ReportPath

Here’s the script in action:

Supply values for the following parameters:
Credential
Getting all sites
Connecting to https://mytenant.sharepoint.com/ 
Connecting to https://mytenant.sharepoint.com/portals/hub 
Connecting to https://mytenant.sharepoint.com/search 
Connecting to https://mytenant.sharepoint.com/sites/a-datum-corporation-inc 
Connecting to https://mytenant.sharepoint.com/sites/CompliancePolicyCenter 
Connecting to https://mytenant.sharepoint.com/sites/proseware-inc 
Connecting to https://mytenant-my.sharepoint.com/ 
Finished

 

And the result written to the CSV file:

https://mytenant.sharepoint.com/
https://mytenant.sharepoint.com/paris
https://mytenant.sharepoint.com/portals/hub
https://mytenant.sharepoint.com/search
https://mytenant.sharepoint.com/sites/a-datum-corporation-inc
https://mytenant.sharepoint.com/sites/a-datum-corporation-inc/amsterd
https://mytenant.sharepoint.com/sites/a-datum-corporation-inc/amsterd/it
https://mytenant.sharepoint.com/sites/a-datum-corporation-inc/amsterd/it/financi
https://mytenant.sharepoint.com/sites/CompliancePolicyCenter
https://mytenant.sharepoint.com/sites/proseware-inc
https://mytenant-my.sharepoint.com/

Setting Administrators on all SharePoint Online Site Collections

Introduction

Imagine the following scenario: A user or a group of people need full access to all site collections in your Office 365 tenant. It could be a service account that gathers some statistics regularly, or a group of users who provide regular detailed support to your organization. How can you ensure that these users have access to all site collections, even newly created ones? What is the best of all to manage this group of users?

Currently, there is no way how you automatically assign users or groups as site collection administrators in your tenant. And while you can manage the settings per site collection in the SharePoint Online Administration area of the Office 365 portal, doing so for dozens or hundreds of sites is not a productive use of time.

Preparation

But there’s a way to automate this process, and as usual PowerShell comes to the rescue. What you need are the OfficeDev PnP PowerShell cmdlets available at https://github.com/OfficeDev/PnP-PowerShell (review the installation instructions on that page if you haven’t installed those awesome cmdlets yet) and an account which has been assigned either the “SharePoint administrator” or “Global administrator” role in your tenant.

The cmdlet we are interested in is called Set-SPOTenantSite. It allows you to manage some selected properties of a SharePoint Online site collection, among them the owners. The ‘Owners’ parameter expected a list of accounts, for example: ‘user1@mytenant.onmicrosoft.com’,  ‘user2@mytenant.onmicrosoft.com’,  ‘user3@mytenant.onmicrosoft.com’. As you can see, it requires a user’s login name. But how about groups? If I create an Office 365 group, how can I determine its login name? While you can also achieve the same thing via PowerShell, this is one way to do it via the browser:

  1. Go to a SharePoint site and grant the group access
  2. While still on the permissions view, click on the Group name so that you access it’s Personal Settings page
  3. On that page, it will list something like “Account c:0-.f|rolemanager|s-1-5-21-784567607-4288704409-1262486537-2161342”. “c:0-.f|rolemanager|s-1-5-21-784567607-4288704409-1262486537-2161342” is the login name which you need
  4. You can then remove the permissions for the group again

Next, you also need to think about which users you want to add as site collection administrators. If you have a group of users that should be added to all site collections, it makes sense to add all those users either to an Active Directory group (if you’re synchronising your Active Directory with Office 365) or an Office 365 group. That way, you can manage the group of users fairly easily, and add or remove users simply by managing the group – without having to do anything on the site collections directly.

The Script

Here’s the script that helps you set the site collection administrators on a filtered set of site collections (I’m skipping any personal OneDrive for Business sites in the *mytenant-my.sharepoint/* path, e.g. Update: I just realised that OneDrive sites aren’t returned by default, the IncludeOneDriveSites parameter has to be set to $true for this. Either way, we’re skipping sites in the mytenant.sharepoint.com/teams/* path in this example). Please note that in its current form below, it is meant to be run directly by someone who has account credentials for a “SharePoint administrator” or “Global administrator”. It can be adapted to use stored credentials, e.g. when you want to run it as a daily scheduled task on a server.

#comma separated list of users and groups to be added
$adminAccounts = "support@mytenant.onmicrosoft.com","superadmin@mytenant.onmicrosoft.com"

#Specify the tenant here
$tenant = "mytenant"

# Note: If you run this script regularly, please have a look at the following site to see how you can store credentials securely in Windows
# https://github.com/OfficeDev/PnP-PowerShell#settings-up-credentials
$cred = Get-Credential

write-host "Connecting to https://$($tenant)-admin.sharepoint.com"
Connect-SPOnline -Url "https://$($tenant)-admin.sharepoint.com" -Credentials $cred
write-host "Getting list of site collections"

#Note: we are only fetching the root site collection and any site collection in the /sites/ path
#Update filters here accordingly to match your requirements
$sitecollections = Get-SPOTenantSite | where {($_.Url -like "*$($tenant).sharepoint.com/") -or ($_.Url -like "*$($tenant).sharepoint.com/sites/*")}

foreach($sitecollection in $sitecollections) {
	write-host "Adding administrators to $($sitecollection.Url)"
	Set-SPOTenantSite -Url $sitecollection.Url -Owners $adminAccounts
}

That’s it, just a couple of lines of PowerShell which can save you a lot of time and help you with your support processes.

Lastly, if you want to run this script regularly as you want to ensure that your users/groups are also added to new site collections and added to existing ones (if they have been removed), I would recommend to follow the instructions given on the OfficeDev PnP PowerShell page for setting up credentials in Windows’ credentials manager and running the script as a scheduled task.

 

SharePoint Online vs On-premises vs Hybrid – 2016 edition

My presentation from yesterday’s Singapore SharePoint Community event: