Renaming a file in SharePoint Online using PowerShell

Renaming a file in SharePoint Online using Powershell….

Wasn’t as simple as I initially thought, you need to make use of MoveTo …

# Call the SharePoint DLL
Add-Type -Path "c:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll" 

#ASSUMES CONNECTION TO SHAREPOINT ONLINE

# Get reference to the site / library
$web = Get-Web
$list = $web.Lists.GetByTitle('Documents')
$ctx.Load($list)
$ctx.ExecuteQuery()
$list.ItemCount

# Pull back 5 documents
$camlQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
$camlQuery.ViewXml = "
      5
  "

$items = $list.GetItems($camlQuery)
$ctx.Load($items)
$ctx.ExecuteQuery()

# Foreach file...rename
foreach($file in $items)
{
	$file1 = $file.File
	$ctx.Load($file1)
	$ctx.ExecuteQuery()
		
	$docName = $file1.name
	$newDocName = [String]::Concat($docName, "-Test-")

	$filePath = "/Documents/" + $newDocName
	
	# Make use of MoveTo to rename the file...
	$file1.MoveTo($filePath, [Microsoft.SharePoint.Client.MoveOperations]::Overwrite)
	$ctx.ExecuteQuery()
}



Azure AD – Retrieving Group Members

Recently I needed to query Azure AD from a web api to retrieve members of an AD group, this was prototyped in a console app – C#.

Got the following packages from Nuget…

using Microsoft.Azure.ActiveDirectory.GraphClient;
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using Microsoft.Azure.ActiveDirectory.GraphClient.Extensions;

Declared my constants – I created a new application from within the Azure Management Portal (Active Directory Area), this allowed me to get a client ID and client Secret – required for authentication.

public class Constants
{
public const string auth = "https://login.windows.net/MYDOMAIN.onmicrosoft.com";
public const string clientID = "client ID GUID";
public const string clientSecret = "client SECRET GUID";
public const string azureGraphAPI = "https://graph.windows.net";
public const string serviceRoot = "https://graph.windows.net/MYDOMAIN.onmicrosoft.com";
}

Created a method to instantiate ActiveDirectoryClient – essentially creating a connection to Azure AD.

private static async Task<string> GetAzureAdToken()
{
AuthenticationContext authenticationContext = new AuthenticationContext(Constants.auth, true);
ClientCredential clientCred = new ClientCredential(Constants.clientID, Constants.clientSecret);
AuthenticationResult authenticationResult = await authenticationContext.AcquireTokenAsync(Constants.azureGraphAPI, clientCred);
return authenticationResult.AccessToken;
}

And wrote another two methods allowing me to get a single user by querying using a UPN, and another method to allow me to retrieve a group and its members based on the group name.

private static async Task<string> GetUser(ActiveDirectoryClient adClient, string Upn)
{
var userLookupTask = adClient.Users.Where(x => x.UserPrincipalName.Equals(Upn, StringComparison.CurrentCultureIgnoreCase)).ExecuteSingleAsync();

User me = (User)await userLookupTask;
Console.WriteLine(me.DisplayName);
return me.DisplayName;
}

private static async Task<string> GetGroup(ActiveDirectoryClient adClient, string GroupName)
{
var groupLookup = adClient.Groups.Where(x => x.DisplayName.Equals(GroupName, StringComparison.CurrentCultureIgnoreCase)).ExecuteSingleAsync();

Group grp = (Group)await groupLookup;
IGroupFetcher groupFetcher = (IGroupFetcher)grp;
IPagedCollection<IDirectoryObject> members = groupFetcher.Members.ExecuteAsync().Result;

do
{
List<IDirectoryObject> directoryObjects = members.CurrentPage.ToList();
foreach (IDirectoryObject member in directoryObjects)
{
if (member is User)
{
User usr = member as User;
Console.WriteLine("user: {0} : {1} : {2}", usr.DisplayName, usr.TelephoneNumber, usr.Mobile);
}
}
members = members.MorePagesAvailable ? members = members.GetNextPageAsync().Result : null;

} while (members != null);

return grp.DisplayName;
}

Within my Main method, it was a case of connecting to Azure AD, and calling the methods…

class Program
{
static void Main(string[] args)
{
try
{
Uri serviceRoot = new Uri(Constants.serviceRoot);
ActiveDirectoryClient adContext = new ActiveDirectoryClient(serviceRoot,async () => await GetAzureAdToken());

var user = GetUser(adClient, "david.hendry@MyDomain.com");
var group = GetGroup(adContext, "IT Solutions");

Console.ReadKey();
}
catch (AuthenticationException ex)
{
Console.ReadKey();
}
}
}
}

CRM Online – Retrieve all users

Being reasonably new to the CRM SDK I needed a method to retrieve all users from the system.  This was needed as I was doing a piece of work to replicate permissions between CRM business units and SharePoint.

Retrieving all users via a query expression turned out to be quite simple…

private static EntityCollection RetrieveAllUsers(IOrganizationService service)
        {
            QueryExpression query = new QueryExpression
            {
                EntityName = "systemuser",
                ColumnSet = new ColumnSet(true),
                Criteria =
                {
                    Conditions =
                    {
                        new ConditionExpression
                        {
                            AttributeName = "businessunitid",
                            Operator = ConditionOperator.NotNull
                        }
                    }
                }
            };
            return service.RetrieveMultiple(query);
        }

The EntityCollection returns a number of useful attributes, such as the users’ business unit, whether they have a valid licence, account status and so on.

Uploading large files to SharePoint Online

I’ve uploaded many files to SharePoint 2013 Online over the last couple of years, all these files have been pretty small.  These files have been uploaded via PowerShell scripts.

The other day I needed to upload a slightly larger file (more then 2MB) and got the following issue:

The request message is too large. The server does not allow messages that are larger than 2097152 bytes.

Previously I was uploading files in the following way:

function Add-FileToSpLib($file)
{
	$filePath = Get-ChildItem $file	
	$fileBytes = [System.IO.File]::ReadAllBytes($filePath.FullName)
	$fileinfo = New-Object Microsoft.SharePoint.Client.FileCreationInformation
	$fileinfo.Content = $fileBytes
	$fileinfo.Url = $library.RootFolder.ServerRelativeUrl + "/" + $filePath.Name
	$fileinfo.Overwrite = $true
	$newFile = $library.RootFolder.Files.Add($fileinfo)
	

	$ctx.Load($newFile)
	$ctx.ExecuteQuery()
}

The above function caused issues when the file was larger than 2MB.

Using the method below I was able to workaround this issue:

	$CurrentList = $ctx.Web.Lists.GetByTitle("My Library");
	$ctx.Load($CurrentList.RootFolder);
	$ctx.ExecuteQuery();


	$filePath = 'C:\Files\fileToUpload.txt'
	$stream  = New-Object IO.FileStream $filePath ,'Open'
	
	
	[Microsoft.SharePoint.Client.File]::SaveBinaryDirect($ctx, $CurrentList.RootFolder.ServerRelativeUrl.ToString() + “/” + $filePath.Split(‘\\’)[2], $stream, $true);
	$stream111.Close()

More info on the SaveBinaryDirect method:

https://msdn.microsoft.com/EN-US/library/office/microsoft.sharepoint.client.file.savebinarydirect.aspx

 

Twilio Sending SMS from a SharePoint List

The following example can be worked into event receivers, workflow, etc – for simplicity and due to time constraints I used a console application.

For this simple PoC I wanted to send text messages from a SharePoint list, I signed up for a Twilio (https://www.twilio.com/) trial, Twilio offer a very good api for dealing with SMS and voice calls.

I’ve created a new console application and installed two nuget packages:

Install-Package Microsoft.SharePointOnline.CSOM

Install-Package Twilio

The following code assumes you have created a Client Context…

The code below is standard SharePoint code, this deals with getting the data from the list, and populating a dictionary containing the recipients number, and the message to be sent.  I also update the list item so I know it has been processed.

using (var context = new ClientContext(contextWebUrl))
            {
                context.Credentials = new SharePointOnlineCredentials(userName, password);
                Web web = context.Web;
                context.Load(context.Web, w => w.Title);
                context.ExecuteQuery();

                List sms = web.Lists.GetByTitle("SMS");
                context.Load(sms);
                context.ExecuteQuery();

                CamlQuery query = CamlQuery.CreateAllItemsQuery(100);
                ListItemCollection items = sms.GetItems(query);
                context.Load(items);
                context.ExecuteQuery();

                Dictionary<string, string> messageToSend = new Dictionary<string, string>();
                context.Load(items);
                context.ExecuteQuery();

                foreach (ListItem item in items)
                {
                    if (string.Equals(item["Status"], "new"))
                    {
                        context.Load(item);
                        context.ExecuteQuery();
                        messageToSend.Add(item["Title"].ToString(), item["Body"].ToString());
                        item["Status"] = "Sent";
                        item.Update();
                        context.ExecuteQuery();
                    }
                }
          }

The next piece of code deals with sending the messages (this is rough and ready code – purely for a PoC)..

            string AccountSid = "xxxxxxxxxxxxxxxxxxxxxxxx";
            string AuthToken = "xxxxxxxxxxxxxxxxxxxxx";
            var twilio = new TwilioRestClient(AccountSid, AuthToken);

            foreach (var msg in messageToSend)
            {
                var newMsg = twilio.SendMessage("My Twilio Number", msg.Key, msg.Value);
                Console.WriteLine(newMsg.Sid + " " + newMsg.Status);
            }

Once you have a Twilio trial account, you’ll get a phone number, and credentials for interacting with the API.

The next article covers building a web API to receive text messages.

SharePoint Online (2013) Unique List Items Limit

I found conflicting information on the number of items within a list that can contain unique item permissions, a number of resources said 5000, and some mentioned 50000 – however these limits were for SharePoint 2010/2013 on-prem and no information around SharePoint Online.

After a quick test, I can confirm there is a hard limit at 50000 items that can contain unique permissions.

Running a PowerShell CSOM script to create 55000 items, the script failed at 50001 items with the error:

Exception calling “ExecuteQuery” with “0” argument(s): “You cannot break inheritance for this item because there are too many items with unique permissions

Script snippet:

$list = $web.Lists.GetByTitle('limit')
$ctx.Load($list)
$ctx.ExecuteQuery()

$count = 0
$push = 0

for($count -eq 0; $count -lt 55000; $count++)
{ 
Write-Host "adding $count"
$ListItemInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
$Item = $list.AddItem($ListItemInfo)
$Item["Title"] = $count
$ctx.Load($list)
$item.BreakRoleInheritance($True, $true);
$Item.Update() 
$push++

if($push -eq 50)
{
$ctx.ExecuteQuery()
$push = 0
Write-Host "pushed"
}
}


Uploading files to a specific folder within a SharePoint document library using CSOM + PowerShell

Uploading files to a specific folder within a SharePoint document library using Client Side Object Model + PowerShell….

# Source folder, for example the display templates folder within the masterpage gallery
$sourceFolder = "_catalogs/masterpage/Display%20Templates/Content%20Web%20Parts"

#file path, location of the file to upload
£filePath = "C:\Projects\POC\Upload\Control_Default.html"

$sourceFolder = $ctx.Web.GetFolderByServerRelativeUrl($relativeFolderLocation);
$ctx.Load($sourceFolder)
$ctx.ExecuteQuery()

$filePath = Get-ChildItem $filePath	
$fileBytes = [System.IO.File]::ReadAllBytes($filePath.FullName)

$fileinfo = New-Object Microsoft.SharePoint.Client.FileCreationInformation
$fileinfo.Content = $fileBytes
$fileinfo.Url = $relativeFolderLocation + "/" + $filePath.Name
$fileinfo.Overwrite = $true

$ctx.Load($sourceFolder.Files.Add($fileinfo))
$ctx.ExecuteQuery()
Add-LogMessage -message "Adding file to document library"

Adding a web part to a page using CSOM / PowerShell

The following script adds a web part to a page using PowerShell and the Client Side Object Model (SharePoint 2013).

Prior to adding a web part to the page you need to:

  • Get the XML of the web part –  the easiest way is to export the web part and view the XML in Notepad. The XML will need to be stored as a variable in your script.

The basic steps:

  • Open a connection to the site collection
  • Get the page
  • Check out
  • Create the web part definition – using the XML from your exported web part
  • Add the web part to the page
  • Check in the page
#### CLIENT CONTEXT INSTANTIATED - $CTX ####
# My previous code (not included) deals with connecting to my Site Collection hosted on Office 365 

# URL of page where we want to add the webpart
$serverRelativeUrl = '/sites/tDEV/pages/Contact-Us.aspx'
#  Get the page
$oFile = $ctx.get_web().getFileByServerRelativeUrl($serverRelativeUrl)
# Load and print out the Title of the page
$ctx.Load($oFile)
$ctx.ExecuteQuery()
$oFile.Title

# CheckOut the page
$oFile.CheckOut()
$ctx.ExecuteQuery()
# Get the webpart manager
$limitedWebPartManager = $oFile.getLimitedWebPartManager("Shared")
# Import the web part using the web part XML, $wpxml contains the XML from the exported web part
$wpd = $limitedWebPartManager.ImportWebPart($wpxml)
# Using the web part definition we can add the webpart to the page - in the header zone.
$limitedWebPartManager.AddWebPart($wpd.WebPart, "Header", "1")
# Check in the page
$oFile.CheckIn("test", "MajorCheckIn")
$ctx.ExecuteQuery()

SharePoint Item Level Permissions using PowerShell and CSOM

I generally try to avoid using item level permissions, but I had a specific scenario where these were needed. Had I been using an on-premise solution I would have built a timer job to manage the permissions, however as Office 365 was being used I decided to build a PowerShell script using the Client Side Object Model – the script is executed every night.

  • The script iterates through a couple of lists each night a performs a couple of actions:
    Checks for new items
  •  Based on the list item data, find the column ‘EmployeeId’ and query active directory to find the User Principle Name (UPN) (EmployeID is stored against each user in AD).
  • If the above action finds a user from AD the script removes all permissions on the list item and sets unique permissions so only the employee and a management group have access to that list item.

The following code snippets assume a connection to SharePoint is open (ClientContext), and the current web is loaded into context.

To find the SharePoint group the following was used:

# Load in list of groups on the current web.
$groups = $web.SiteGroups
$ctx.Load($groups)
$ctx.ExecuteQuery()

# Find the group called HR. Note - using GetGroupByName was not working, therefore I had to iterate through the groups.

foreach($group in $groups)
{

if($group.Title -eq "hr")
{
$hrGrp = $group.Id
}
}

# Get the group and load into context to be used.
$spGrp = $groups.GetById($hrGrp)
$ctx.Load($spGrp);
$ctx.ExecuteQuery();

To set item level permission on each list item:

# Get the list by Title and load.
$list = $web.Lists.GetByTitle("MyList")
$ctx.Load($list)
$ctx.ExecuteQuery()
$listTitle = $list.Title

# Simple query - purely used to ensure all data is returned.
$camlQuery = New-Object microsoft.SharePoint.Client.CamlQuery
$camlQuery.ViewXml = "10000"
	
# Load in the items.
$collListItem = $list.GetItems($camlQuery)
$ctx.Load($collListItem)
$ctx.ExecuteQuery()

# Iterate through each item.
foreach ($item in $collListItem)
{	
	# reset variable to ensure no false positives.
	$upn = $null
	$user = $null
	$roleAssignment = $null	
	$continue = $false

	# Set a couple of variable, get the user from AD based on employee number.
	$recordId = $item.Id
	$upn = Get-Upn -eid $item["EmployeeID"]
	$continue = $false

	if($upn -ne $null)
	{
		$continue = $true
	}
	else
	{
		Add-LogMessage "ERROR: Missing employee number on record '$recordId' "
	}	

	if($continue)
	{
		# Break inheritance on the list item and remove existing permissons.
		$item.BreakRoleInheritance($false, $true)

		# Get the permissions role for 'Read' and 'Edit'.
		$reader = $web.RoleDefinitions.GetByName("Read");
		$Editor = $web.RoleDefinitions.GetByName("Edit");
		
		# Create a role assignment and apply the 'read' role.
		$roleAssignment = New-Object microsoft.SharePoint.Client.RoleDefinitionBindingCollection($ctx)
		$roleAssignment.Add($reader)
		
		# Create a role assignment for editors - applying the 'edit' role.
		$roleAssignmentEditor = New-Object microsoft.SharePoint.Client.RoleDefinitionBindingCollection($ctx)
		$roleAssignmentEditor.Add($Editor)

		# Ensure the user exists on the site level, using EnsureUser.
		$user = $ctx.Site.RootWeb.EnsureUser($upn)
		$ctx.Load($user)

		# Apply the two permission roles to the list item.
		# $user is a SharePoint user.
		$ctx.Load($item.RoleAssignments.Add($user, $roleAssignment))    
		# spGrp is the HR group returned in the above snippet.
		$ctx.Load($item.RoleAssignments.Add($spGrp, $roleAssignmentEditor))

		# Update field on the list item to show it has ben processed.
		$item["A001"] = "PROCESSED"
		$item.Update()
	}

	# Execute changes.
	$ctx.ExecuteQuery();
}