Managing SharePoint Online Files with PowerShell

PowerShell Text Purple hero

Managing user content inside of SharePoint Online is the least favorite work of every administrator I know. While moving one file from Document Library A to Document Library B is not exactly hard, using a mouse to move hundreds of files based on a metadata value is just plain terrible. When that type of job comes up, the only good solution is to use a third-party tool. Not anymore. Thanks to the might of PowerShell and the Patterns and Practices (PNP) PowerShell, you can now write your own script to do the work for you. You can be the office hero.

 

 

What You Need

To do this magic without a third-party tool, you need to install the PNP PowerShell. If you are not familiar with these awesome and free tools, then check out my previous article. It will give you some context and walk you through installing PNP. Watching this should only take you 5 minutes. After that, you just need access to the content.

With the official SharePoint Online Management Shell, you have to be a global administrator in Office 365 to make things work. You connect to the tenant and then work down from there. With the PNP PowerShell, you connect at the site collection level and all of the cmdlets are just making web API calls. If you have permissions to copy a file in the browser, you have access with the PNP PowerShell. Pretty cool.

The Magical PowerShell You Came For

In this example, we will look at ways to move, copy, and remove files from SharePoint Online document libraries. If you are more of the watch Shane do it type instead of the reading about it type, you can watch Move SharePoint Online Files with PowerShell.

Get Connected

  1. Open PowerShell by right-clicking on the icon and select Run as Administrator.
  2. At the prompt type $me = Get-Credentials.
  3. A Windows Security box will pop up. Type the username and password of the SharePoint Online user who has access. Click OK. This step does not validate your password. If you mess up here, you will not find out until the next step. If you do mess up, you can start over at step 2 without issue. (I may be telling you this because I just screwed up.)
  4. At the prompt type Connect-PNPOnline -url https://MOD746156.sharepoint.com -credential $me. Remember to use your site collection, not mine for the -URL option.

This will get you connected but it is a pain in the backside. For better password management options, check out Managing Usernames and Passwords with PowerShell for SharePoint Online. This is how the cool kids are doing it.

Create Some Content with Your Browser

No, I am not going to write you instructions for creating two document libraries using your browser. But, I need you to go to the site collection you connected to with your browser and create two document libraries. OldHome and NewHome should be the names. Upload three files to OldHome. Once you are done, we will move the files from OldHome to NewHome. I named my three files Document 1.docx, Document 2.docx, and Document 3.docx because I am very creative. It is important to remember that the files have to have words in them. SharePoint does not like blank files.

Things You Need to Know About Files

There are countless file properties you now have access to with PowerShell but we do not have countless time together. Let’s look at a few key things you need to know for this exercise. After we are done, you can go exploring on your own. Tweet me the fun things you find.

Time to run some PowerShell:

Get-PnPListItem OldHome

This will show you the files you uploaded. It is normal for the Title to be blank. You can manually add one. You will need to know a valid ID for the next step. If you are following along, 1 will be valid. If you are using existing document libraries, then you may not have a 1. When you upload your first document, it is id 1. If you delete that file, then upload it again, it will be assigned id 2. SharePoint does not reuse IDs. It is possible that you will not have an id 1.

Now assign one of the documents to a variable and look at the properties:

$cow = get-pnplistitem -id 1 -List OldHome 
$cow.FieldValues

So much fun there. You can see that they hide the actual filename in FileLeafRef. FileRef contains the full path to the file. This is all information you will need to automate a solution.

Move a Single File

This is not very useful. It is worth noting that the Move cmdlet is available. We do not use this in the script because we are going to introduce some fun with metadata in the next article. It is easier to copy the file, then the metadata, and then delete the original. But that is for another day.

$Source = $cow.FieldValues.FileRef
$TargetLib = "/NewHome/"
$Target = $TargetLib + $cow.FieldValues.FileLeafRef
Move-PnPFile -ServerRelativeUrl $Source -TargetUrl $Target -Force

Breaking that down should be pretty easy but here are the details:

  • $Source is the file we want to move. We are using the FileRef property because it contains the full path to the file that Move-PNPFile needs.
  • $TargetLib is the path of the document library we want to move the file to. You need both of the / in quotes. I made this a separate variable to make the script easier to update later.
  • $Target is now the full path and filename you want to move the file to. It is derived by building out the string. If you wanted to change the filename to something like ShaneIsAwesome.docx, you could easily modify the string. Just an idea.
  • Move-PnPFile is pretty straightforward. We can give thanks to the variables. -Force just keeps you from having to confirm the move.

Copy and Delete All of the Files

This is the same as a move but uses different cmdlets. We covered the reason for this method earlier, which is that we can do even cooler things later. If you want to replace the Copy and Remove cmdlets with Move, have at it. It will not hurt my feelings.

$Items = Get-PNPListItem -List "OldHome"
$TargetLib = "/NewHome/"
foreach ($Item in $Items)
{
$source = $item.FieldValues.FileRef
$target = $TargetLib + $item.FieldValues.FileLeafRef
Copy-PnPFile -SourceUrl $source -TargetUrl $target -Force -ErrorAction Stop
Remove-PNPFile -ServerRelativeUrl $source -Force
}

Here are a couple of notes from the above PowerShell. Most of it has been covered already:

  • Everything there is standalone. Running this block without the previous blocks will work.
  • On the Copy-PnPFile line, the last option is -ErrorAction Stop. If there is an error, this will cause the script to terminate. If the copy fails, the file will not get deleted.
  • Remove-PnPFile does not put items in the recycle bin. Deleting with PowerShell is forever. Be careful.

Awesome! All of your files have been moved from one document library to the other.

Putting a Bow on It

While this example is not exactly exciting, it does provide a great foundation. From here, you could build out the script to only move files based on a specific metadata value or copy the files and the metadata. This would allow you to retain the modify date and user. The sky is the limit and that is the beauty of PowerShell.

The idea for this script actually came from a customer request. Michael, from New York, worked with Bold Zebras to sort a very large document library into many smaller document libraries based on some of the metadata. With his script, we ended up with Try Catch blocks, some CAML, and a bunch of other chaos. I decided not to overrun this article with all of that. If you ever need help with scripts like this, we are always happy to help.

 

Shane

Microsoft Cloud YouTube Channel