Interacting with MS Office

There are many modules that allow you to interact with Office services, either on premises and on-line. You can use PowerShell to configure those services, as administrator, and access them as user. In the following, I will mostly cover Office online (which is becoming the norm) and interactions as a user.

SharePoint Services

Interesting modules

You should check and install the following modules:

• SharePointPnPPowerShellOnline from PSGallery. Check also their Git repo, and the instructions therein (I am using some of them below).

Connecting to your tenant

You can connect using the Connect-PnPOnline command:

Connect-PnPOnline –Url https://yoursite.sharepoint.com –Credentials (Get-Credential)
Connect-PnPOnline -Url https://yoursite.sharepoint.com/teams/team/subteam -UseWebLogin

I found the second version more convenient.

Listing the items in a document library

To get the list of items use Get-PnPListItem. Keep in mind that you need to control the request process for very large libraries:

$items = Get-PnPListItem -List "Shared Documents/My Library" -PageSize 500

For an item, you can see its name and full path using the $item.FieldValues property, e.g. $item.FieldValues.FileRef and $item.FieldValues.FileLeafRef.

Operations on items

To rename an item use Rename-PnPFile:

Rename-PnPFile -ServerRelativeUrl $item.FieldValues.FileRef -TargetFileName $newName -Force
Rename-PnPFile -ServerRelativeUrl $item.FieldValues.FileRef -TargetFileName $newName -OverwriteIfAlreadyExists -Force

To delete an item use Remove-PnPFile:

Remove-PnPFile -ServerRelativeUrl $item.FieldValues.FileRef -Force

Be careful that the system does not allow the deletion of files with very long file names (more than approximately 180 characters). I speculate that it fails to move the file to the recycling folder. Hence, for those files, you first need to rename the file, and then delete.