Tag: powershell

Office 365 Advent Calendar – 23 Bulk-updating File Extensions in SharePoint Online with PowerShell

office-365-advent-calendar

Scenario

A short while ago, I had to update a fairly large number of documents in multiple libraries in a SharePoint Online site. Problem was that their file extension was .html which doesn’t display in the browser if stored in SharePoint Online, but rather these files get downloaded upon opening. The workaround – rename them to .aspx. The following scripts does this for all .html files and renames them to .aspx

Code

$cred = Get-Credential
Connect-PnPOnline -Url "http://mytenant.sharepoint.com/sites/demo" -Credentials $cred
$ctx = Get-PnPContext
$libraries = Get-PnPlist | Where{$_.BaseTemplate -eq 101}
foreach($lib in $libraries) {
	write-host "Getting items from $($lib.Title)"
	$items = Get-PnPListItem -List $lib -Fields "Title", "FileLeafRef" | where {$_["FileLeafRef"] -like "*.html"}
	write-host "Looping through the items"
	foreach($item in $items) {
		$file = $item.File
		$ctx.Load($file)
		$ctx.Load($file.ListItemAllFields)
		$ctx.ExecuteQuery()
		write-host "Renaming file $($file.Name)"
		$newName = $file.Name -Replace "\.html",".aspx"
		Move-PnPFile -ServerRelativeUrl $file.ServerRelativeUrl -TargetUrl "$($file.ListItemAllFields['FileDirRef'])/$($newName)" -Force
	}
}

Office 365 Advent Calendar – 21 Get all Instances of a Web Part in a SharePoint Online Site

office-365-advent-calendar

Scenario

Sometimes you may need to figure out where a specific type of web part has been used. Today, I’ll show you how you can check all pages in your Site Pages library for a specific web part.

I’ve had this code for a while as I was looking for some specific web parts (Content Editor) before. Yesterday, I briefly saw a blog post on dev.office.com announcing the deprecation of the Visio Web Access web part on SharePoint Online (as Visio Online is coming!), though that blog post has disappeared again (I suspect we’ll see it again soon). So I decided to modify it slightly to look for all instances of Visio Web Access web parts and save the results to a CSV file.

Code

The following code checks all pages in the given site’s Site Pages library for instance of the ‘Visio Web Access’ web part. You could easily modify it to loop through all sites in a site collection, or even all sites within your whole tenant.

$cred = Get-Credential
Connect-PnPOnline -Url https://mytenant.sharepoint.com/sites/pagestest -Credentials $cred

$visioWebParts = @()

$ctx = Get-PnPContext
$lists = (Get-PnPWeb).Lists
#Note: I'm not accessing the Site Pages library by URL or title, as I had sites in different languages
$sitePages = $lists.EnsureSitePagesLibrary()
$ctx.Load($lists)
$ctx.Load($sitePages)
$ctx.ExecuteQuery()

$pages = Get-PnPListItem -List $sitePages[0].Id
foreach($page in $pages) {
	$ctx.Load($page.File)
	$ctx.ExecuteQuery()
	$webparts = Get-PnPWebPart -ServerRelativePageUrl $page.File.ServerRelativeUrl
	foreach($webpart in $webparts) {
		$wpxml = Get-PnPWebPartXml -ServerRelativePageUrl $page.File.ServerRelativeUrl -Identity $webpart.Id
		# The Visio Web Access part is referenced in the XML definition of the web part
		# with type Microsoft.Office.Visio.Server.WebControls.VisioWebAccess. Change this for
		# any other types of web parts you want to find 
		if($wpxml -Match '<type name="Microsoft.Office.Visio.Server.WebControls.VisioWebAccess,') {
			$visioWebParts += New-Object PSObject -Property @{
				'PageUrl' = $page.File.ServerRelativeUrl
				'WebPartTitle' = $webpart.WebPart.Title
			}
		}
	}
}

$visioWebParts | select PageUrl, WebPartTitle | Export-Csv C:\visio.csv

Office 365 Advent Calendar – 18 Adding a Web Part to a SharePoint Online Page with PowerShell

office-365-advent-calendar

Scenario

You’ve got a Web Part which you want to add to one, but more likely multiple web part pages in a SharePoint Online Site. Or maybe even on multiple pages across multiple sites. You could do this manually, but adding the same web part with the same configuration over and over again may not be a productive use of it. Let’s make use of PowerShell to do this!

Code

The code itself is quite simple, as the PnP PowerShell cmdlets contain a cmdlet named Add-PnPWebPartToWebPartPage. All you need is the page where you want to add the web part as well as the XML of your web part.

Let’s say you’ve got a “This SharePoint site will be archived on 31/12/2016” message which you want to add to the default.aspx page on a list of sites. You can create the Web Part with the desired message and formatting once, then export it:

Once exported, you can use the following script to add the same web part to all default.aspx pages on all sites listed in SitesToArchive.csv. The Web Part will be added to the Zone named “Left” with an index of 0, that is at the top of it:

$cred = Get-Credential
$websToUpdate = Import-Csv SitesToArchive.csv
foreach($web in $websToUpdate) {
	write-host "Connecting to $($web.Url)"
	Connect-PnPOnline -Url $web.Url -Credentials $cred
	Add-PnPWebPartToWebPartPage -ServerRelativePageUrl "default.aspx"`
	-Path "c:\users\rmodery\Desktop\ArchiveMessageWebPart.dwp" -ZoneId "Left" -ZoneIndex 0
}

 

How about wiki pages in SharePoint Online? Add-PnPWebPartToWikiPage may help you here!

Office 365 Advent Calendar – 15 Activating Publishing Features on SharePoint Online Sites with PowerShell

office-365-advent-calendar

Scenario

Activating SharePoint’s Publishing features can easily be done on a single site collection or site. But what if you need to activate Publishing on a hierarchy of subsites, or across multiple site collections?

Code

Once again, we’re using the PnP PowerShell cmdlets.

First some basics. The ‘main’ feature to activate is the PublishingSite feature on the Site Collection level. After you connected to a site collection (Connect-PnPOnline), you can activate it as follows:

Enable-PnPFeature -Identity f6924d36-2fa8-4f0b-b16d-06b7250180fa -Scope Site

The -Scope Site parameter is important, as the default for this parameter is set to Web, and the feature wouldn’t get activated without specifying it correctly.

Once that’s done, we can activate the PublishingWeb feature for the current site (on a Web scope):

Enable-PnPFeature -Identity 94c94ca6-b32f-4da9-a9e3-1f3d343d7ecb

Office 365 Advent Calendar – 14 Exporting SharePoint Online List/Library Details

office-365-advent-calendar

Well, this is a bit embarrassing. Yesterday, for the first time, I didn’t verify if my scheduled blog post got published properly. And yesterday, also for the first time, it didn’t get published. I made a small scheduling mistake (it’s not 2017 yet…), so the 14th December post comes a day late:

Scenario

From time to time you may need an overview of all lists and libraries in your site together with the number of items in each list/library, and when an item in it was last modified. You can easily see this information from the Site Contents page:

But there are times when this information needs to be available in Excel, which is what I’ll show you today how to retrieve:

Code

$cred = Get-Credential
Connect-PnPOnline -Url "https://mytenant.sharepoint.com/sites/demo" -Credentials $cred

#the following is required to load the corresponding list templates for each list/library
$ctx = Get-PnPContext
$templates = (Get-PnPWeb).ListTemplates
$ctx.Load($templates)
$ctx.ExecuteQuery()

Get-PnPList | select Title, @{label="Url";Expression={$_.RootFolder.ServerRelativeUrl}},`
@{label="Type";Expression={$bt=$_.BaseTemplate;$templates |`
Where{$_.ListTemplateTypeKind -eq $bt} | foreach{$_.Name}}},`
ItemCount, LastItemModifiedDate | Export-Csv c:\ListInfo.csv

And here’s the output from the script:

 

Office 365 Advent Calendar – 11 Adding sample items with random data to a SharePoint Online List

office-365-advent-calendar

Scenario

Sometimes you want to create some random sample data to be used in a SharePoint Online list. For example, in yesterday’s post I was using a list with some data which I entered manually, but I thought afterwards that I could’ve scripted it to create many more items in my demo list. Today, I show you how you can easily fill a list in SharePoint Online with as many sample items as you need, all with random values based on some predefined arrays.

Code

The code below requires a few things from you:

  1. You need to define the number of items you want to create
  2. You need to define a few arrays with potential values for each of your columns (in my case, $Company, $Region, and $Product)
  3. You need to define the list where you want to add those items to
  4. Lastly, you need to update the -Values parameter with the correct column names for your list
$itemsToGenerate = 10
$listName = "Sales Pipeline"

# Sample data for the list
$Company = ("Ah Loong Pte Ltd", "Contoso Inc", "Fabrikam Corp", "Flowers by Irene",`
 "NorthSouth Trading Inc", "Petrox Oil Company", "Spacely Sprockets", "The Frying Dutchman")
$Region = ("North", "East", "South", "West")
$Product = ("Admin ToolKit", "EZClean", "WonderTool 2000", "Business Process WonderKit")

$cred = Get-Credential
Connect-PnPOnline -Url https://mytenant.sharepoint.com/sites/demo -Credentials $cred

foreach($i in 1..$itemsToGenerate) {
	#Update the -Values below with the correct column names from your list
	Add-PnPListItem -List $listName -Values @{`
		# I'm assigning a randomly chosen value from the $Company array to the item column
		"Title" = $Company[(Get-Random -Minimum 0 -Maximum ($Company.Count))];`
		"Region" = $Region[(Get-Random -Minimum 0 -Maximum ($Region.Count))];`
		"Product" = $Product[(Get-Random -Minimum 0 -Maximum ($Product.Count))];`
		# Here, I want to create a random number between 150 and 99999
		"Potential_x0020_Value" = (Get-Random -Minimum 150 -Maximum 99999);}
}

Office 365 Advent Calendar – 08 Get Versioning Details for all Lists in a SharePoint Online Site

office-365-advent-calendar
Scenario

By default, libraries in SharePoint Online keep the last 500 major versions of a item, but lists have no versioning turned on. And while you can easily finetune the settings for all lists and libraries in a site, it may be good to know how versioning is set on each library before you look at changing those settings. Today’s post provides you with a script that retrieves all versioning related information (everything you seen on the ‘Versioning settings’ page of a list/library) from all lists and libraries within a given site.

Code

$cred = Get-Credential
Connect-PnPOnline -url https://mytenant.sharepoint.com/sites/testsite -credentials $cred

$lists = Get-PnPList

$VersioningDetails = @()
foreach($list in $lists) {
	$VersioningDetails += New-Object PSObject -Property @{
		'List' = $list.Title
		'Require Content Approval' = $list.EnableModeration
		'Versioning Enabled' = $list.EnableVersioning
		'Major Version limit' = $list.MajorVersionLimit
		'Draft Version limit' = $list.MajorWithMinorVersionsLimit
		'Drafts visible to' = $list.DraftVersionVisibility
		'Checkout required' = $list.ForceCheckout
	}
}

$VersioningDetails | Select 'List', 'Require Content Approval', 'Versioning Enabled',`
'Major Version limit', 'Draft Version limit', 'Drafts visible to', 'Checkout required'`
| Export-Csv versioningdetails.csv

Note: By default, all system libraries such as Solutions Gallery, Master Page Gallery, Web Part Gallery, User Information List, etc. are included.

Here’s a sample output, excluding the system libraries:

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.