SharePoint Online: Convert Folders to Metadata

Requirement: Convert folder structures to metadata in the SharePoint Online library.

How to convert folder structures to metadata in SharePoint Online?

We wanted to make use of SharePoint’s full potential by replacing the folder structure with metadata. In my case, I had a document library called “Migration,” and folders were created for each project in this library. So, All I wanted was: Change the folders to metadata so that I could use this metadata to sort/filter/search, etc.

The manual procedure to flatten the folders works like this:

1. Add a Choice column in the library and set its choices as the folder names in the library.
2. Tag each file with the folder (You can create a new view or change the existing to display files without folders and edit the files in “Quick Edit”).
3. Move the files outside the folders to the root of the document library using File Explorer.
4. Remove the folders from the library.

Convert Folders to Metadata using PowerShell

Let’s automate the above steps using PowerShell!

Please be careful and do some testing before running this script. In my case, files were placed just one folder level deep without any further subfolders – Make sure you have a similar structure. Otherwise, You must alter the script according to your folder structure. Also, If you have the same file name in two folders – There is a chance of conflict. If so, You may have to rename the files in the folders by appending the folder names to them.

#Parameters
$SiteURL= "https://crescent.sharepoint.com/sites/pmo"
$ListName = "Migration"
$MetadataColumnName = "Project"

#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Interactive

#Get All Items from the List
$List =  Get-PnPList -Identity $ListName
$RootFolderURL = $List.RootFolder.ServerRelativeUrl
$ListItems = Get-PnPListItem -List $ListName -PageSize 500
$Files = $ListItems | Where {$_.FileSystemObjectType -eq "File"}
$Folders = $ListItems | Where {$_.FileSystemObjectType -eq "Folder" -and $_.FieldValues.FileDirRef -eq $RootFolderURL}
$FolderNames = @($Folders | ForEach-Object { $_.FieldValues.FileLeafRef})

#Get the column from list
$Field = Get-PnPField -Identity $MetadataColumnName -List $ListName -ErrorAction SilentlyContinue
If($Field -eq $Null)
{    
    #Create the Coice Metadata Column
    Add-PnPField -List $ListName -DisplayName $MetadataColumnName -InternalName $MetadataColumnName -Type Choice -AddToDefaultView -Choices $FolderNames | Out-Null
}

#Loop through Each Folder and Update Metadata for Each File in the Folder
ForEach($Folder in $Folders)
{
    #Get All Files from the Folder
    $FolderFiles =  $Files | Where {$_.FieldValues.FileDirRef -Eq $Folder.FieldValues.FileRef}
    ForEach ($File in $FolderFiles)
    {
        #Get the File from List Item
        Set-PnPListItem -List $ListName -Identity $File.Id -Values @{$MetadataColumnName = $Folder.FieldValues.FileLeafRef } | Out-Null
        Write-host -f Yellow "Updated Metadata for File:"$File.FieldValues["FileRef"]

        #Move the File to Root Folder
        Move-PnPFile -SourceUrl $File.FieldValues.FileRef -TargetUrl "$RootFolderURL/$($File.FieldValues.FileLeafRef)" -Force
    }

    #Remove the folder
    Remove-PnPListItem -List $ListName -Identity $Folder.Id -Recycle -force
}

You can group by the specific metadata column to make all files of that sort easier to find. Please note, if your library or a folder has more than 5000+ files – You may encounter some kind of side effects. Here is the script result:

Wrapping up

In conclusion, converting folders to metadata in SharePoint Online can be a useful way to organize and manage content within your organization. By using metadata, you can add custom fields to your folders and use them to classify and filter your content, making it easier to find and use the information that you need. Overall, using metadata can help you better manage your content and make it more accessible and useful to your team.