Creating and managing site columns in a SharePoint library can be a tedious task, especially when dealing with a large number of fields. This blog post will walk you through a PowerShell script that automates this process by creating site columns based on the fields of an existing list. We’ll break down the script into smaller pieces to help you understand each part.
Script Overview
Synopsis: This script connects to a SharePoint site, retrieves a specified list, and creates site columns based on the fields of that list. It ensures that each field has a corresponding site column, moves the values from the original library column to the new site column, and optionally removes the original field.
Parameters:
siteUrl: The URL of the SharePoint site.libraryName: The name of the SharePoint library.
Requirements:
- SharePoint PnP PowerShell module.
- Necessary permissions to connect to the SharePoint site and modify the library.
Breaking Down the Script
- Define the Parameters and Connect to SharePoint
$siteUrl = "https://yoursharepointsite"
$libraryName = "Documents"
# Connect to the SharePoint site
Connect-PnPOnline -Url $siteUrl -UseWebLoginThis section defines the SharePoint site URL and library name, and then connects to the SharePoint site using the PnP PowerShell module.
- Retrieve the List
# Get the list
$list = Get-PnPList -Identity $libraryNameHere, the script retrieves the specified SharePoint list using its identity (name).
- Define System Fields to Skip
# Array to Skip OOB Fields
$SystemFlds = @(
"Compliance Asset Id","Body","Expires","ID","Content Type","Modified","Created","Created By","Modified By","Version","Attachments","Edit","Type",
"Item Child Count","Folder", "Child Count","App Created By","App Modified By", "Name","Checked Out To","Check In Comment","File Size","Source Version (Converted Document)",
"Source Name (Converted Document)","Location","Start Time","End Time","Description","All Day Event","Recurrence","Attendees","Category","Resources","Free/Busy",
"Check Double Booking","Enterprise Keywords", "Last Updated","Parent Item Editor","Parent Item ID","Last Reply By","Question","Best Response","Best Response Id",
"Is Featured Discussion","E-Mail Sender","Replies","Folder Child Count","Discussion Subject","Reply","Post","Threading","Posted By", "Due Date","Assigned To","File Received",
"Number Of Setups","Notes/Comments","Task_Status","Is Approval Required","Approver","Approver Comments","Approval Date","Documents", "Order","Role","Person or Group",
"Location", "Predecessors","Priority","Task Status","% Complete","Start Date","Completed","Related Items", "Background Image Location","Link Location","Launch Behavior",
"Background Image Cluster Horizontal Start","Background Image Cluster Vertical Start", "First Name","Full Name","Email Address","Company","Job Title","Business Phone",
"Home Phone","Mobile Number","Fax Number","Address","City","State/Province","ZIP/Postal Code","Country/Region","Web Page","Notes","Name","Order","Role", "Color Tag",
"Label setting", "Retention label", "Retention Label Applied", "Label applied by", "Item is a Record" ,"Comment Count","Like Count","Sensitivity", "Copy Source","Title"
)The script defines an array of system fields to skip, ensuring that these default fields are not processed.
- Process Each Field
Get-PnPField -List $list | Where {$_.Hidden -eq $false -and $SystemFlds -notcontains $_.Title } | ForEach-Object {
# Check if a corresponding site column already exists
$InternalNameSite = "site_" + $_.InternalName
$existingColumn = Get-PnPField -Identity $InternalNameSite -ErrorAction SilentlyContinue
if ($existingColumn -eq $null) {
Write-Host "Creating site column for field: $($_.Title)"
# Determine the field type and create the appropriate site column
switch ($_.TypeAsString) {
"Text" {
Add-PnPField -DisplayName $_.Title -InternalName $InternalNameSite -Group "A_StadAntwerpen" -Type Text
}
"Number" {
Add-PnPField -DisplayName $_.Title -InternalName $InternalNameSite -Group "A_StadAntwerpen" -Type Number
}
"Choice" {
$choices = Get-PnPFieldChoice -List $list -Identity $_.InternalName
Add-PnPField -DisplayName $_.Title -InternalName $InternalNameSite -Group "A_StadAntwerpen" -Type Choice -AddToDefaultView -Choices $choices.Choices
}
"Lookup" {
Add-PnPField -DisplayName $_.Title -InternalName $InternalNameSite -Group "A_StadAntwerpen" -Type Lookup
}
"ManagedMetadata" {
$termStoreId = $_.TermStoreId
$termSetId = $_.TermSetId
Add-PnPField -DisplayName $_.Title -InternalName $InternalNameSite -Group "A_StadAntwerpen" -Type TaxonomyFieldType -TermSetId $termSetId -TermStoreId $termStoreId
}
default {
Write-Host "Field type $($_.TypeAsString) not handled"
}
}
# Move column values to the new site column
$listItems = Get-PnPListItem -List $list
foreach ($item in $listItems) {
$sourceValue = $item[$_.Title]
Set-PnPListItem -List $list -Identity $item.Id -Values @{$InternalNameSite = $sourceValue}
}
# Remove the original field from the library if needed
if ($_.CanBeDeleted) {
Remove-PnPField -List $list -Identity $_.InternalName
}
} else {
Write-Host "Site column already exists for field: $($_.Title)"
}
}This section:
- Retrieves all fields from the list, excluding hidden and system fields.
- Checks if a corresponding site column already exists.
- Creates a new site column if it doesn’t exist, handling different field types appropriately.
- Moves data from the original field to the new site column.
- Optionally removes the original field if it can be deleted.
Conclusion
This PowerShell script simplifies the process of creating site columns in a SharePoint library by automating the creation, data transfer, and cleanup tasks. By using this script, you can ensure consistency and save time when managing large SharePoint lists. Make sure you have the necessary permissions and the SharePoint PnP PowerShell module installed before running the script.
Full script:
<#
.SYNOPSIS
This script creates site columns in a SharePoint library based on the fields of the list.
.DESCRIPTION
The script connects to a SharePoint site, retrieves a specified list, and loops through each field in the list.
For each field, it checks if a corresponding site column already exists.
If not, it creates a site column with the same display name and internal name as the field.
The script then adds the site column to the library and moves the values from the library column to the new site column.
Finally, it removes the original field from the library if needed.
.PARAMETER siteUrl
The URL of the SharePoint site.
.PARAMETER libraryName
The name of the SharePoint library.
.EXAMPLE
.\CreateSiteColumn.ps1 -siteUrl "https://yourtenant.sharepoint.com/sites/yoursite" -libraryName "YourLibrary"
Connects to the specified SharePoint site, retrieves the specified library, and creates site columns based on the fields of the list.
.NOTES
- This script requires the SharePoint PnP PowerShell module.
- You need to have the necessary permissions to connect to the SharePoint site and modify the library.
#>
# Define the library and site URLs
$siteUrl = "https://TENANT.sharepoint.com/"
$libraryName = "Documents"
# Connect to the SharePoint site
Connect-PnPOnline -Url $siteUrl -UseWebLogin
# Get the list
$list = Get-PnPList -Identity $libraryName
#Arry to Skip OOB Fields
$SystemFlds = @(
"Compliance Asset Id","Body","Expires","ID","Content Type","Modified","Created","Created By","Modified By","Version","Attachments","Edit","Type","Item Child Count","Folder", "Child Count","App Created By","App Modified By", "Name","Checked Out To","Check In Comment","File Size","Source Version (Converted Document)","Source Name (Converted Document)","Location","Start Time","End Time","Description","All Day Event","Recurrence","Attendees","Category","Resources","Free/Busy","Check Double Booking","Enterprise Keywords", "Last Updated","Parent Item Editor","Parent Item ID","Last Reply By","Question","Best Response","Best Response Id", "Is Featured Discussion","E-Mail Sender","Replies","Folder Child Count","Discussion Subject","Reply","Post","Threading","Posted By", "Due Date","Assigned To","File Received","Number Of Setups","Notes/Comments","Task_Status","Is Approval Required","Approver","Approver Comments","Approval Date","Documents", "Order","Role","Person or Group","Location", "Predecessors","Priority","Task Status","% Complete","Start Date","Completed","Related Items", "Background Image Location","Link Location","Launch Behavior","Background Image Cluster Horizontal Start","Background Image Cluster Vertical Start", "First Name","Full Name","Email Address","Company","Job Title","Business Phone","Home Phone","Mobile Number","Fax Number","Address","City","State/Province","ZIP/Postal Code","Country/Region","Web Page","Notes","Name","Order","Role", "Color Tag", "Label setting", "Retention label", "Retention Label Applied", "Label applied by", "Item is a Record" ,"Comment Count","Like Count","Sensitivity", "Copy Source","Title"
)
Get-PnPField -List $list | Where {$_.Hidden -eq $false -and $SystemFlds -notcontains $_.Title } | ForEach-Object {
# Check if a corresponding site column already exists
$InternalNameSite = "site_" + $_.InternalName
$existingColumn = Get-PnPField -Identity $InternalNameSite -ErrorAction SilentlyContinue
if ($existingColumn -eq $null) {
Write-Host "Creating site column for field: $($_.Title)"
# Create a new site column with the same display name and internal name as the field
$newColumn = "";
$Type = $_.TypeAsString
$DisplayName = $_.Title
$InternalName = $_.InternalName
# Determine the field type and create the appropriate site column
switch ($Type) {
"Text" {
$newColumn = Add-PnPField -DisplayName $DisplayName -InternalName $InternalNameSite -Group "A_StadAntwerpen" -Type Text
}
"Number" {
$newColumn = Add-PnPField -DisplayName $DisplayName -InternalName $InternalNameSite -Group "A_StadAntwerpen" -Type Number
}
"Choice" {
$choices = Get-PnPFieldChoice -List $list -Identity $InternalName
$newColumn = Add-PnPField -DisplayName $DisplayName -InternalName $InternalNameSite -Group "A_StadAntwerpen" -Type Choice -AddToDefaultView -Choices $choices.Choices
}
"Lookup" {
$newColumn = Add-PnPField -DisplayName $DisplayName -InternalName $InternalNameSite -Group "A_StadAntwerpen" -Type Lookup
#Set Field Properties
$lookupList = Get-PnPField -List $list -Identity $InternalName | Select-Object -ExpandProperty LookupList
Set-PnPField -List $list -Identity $InternalNameSite -Values @{LookupList=(Get-PnPList $lookupList).Id.ToString(); LookupField=$LookupField}
}
"TaxonomyFieldType" {
$termStoreId = $field.TermStoreId
$termSetId = $field.TermSetId
$newColumn = Add-PnPField -DisplayName $DisplayName -InternalName $InternalNameSite -Group "A_StadAntwerpen" -Type TaxonomyFieldType -TermSetId $termSetId -TermStoreId $termStoreId
}
default {
Write-Host "Field type $($field.TypeAsString) not handled"
}
}
Add-PnPField -List $list -Field $newColumn -ErrorAction Stop
# Get all list items
$listItems = Get-PnPListItem -List $list
# Iterate through each list item
foreach ($item in $listItems) {
# Move column values to the new site column
$sourceValue = $item[$existingColumn.Title]
Set-PnPListItem -List $list -Identity $item.Id -Values @{$InternalNameSite = $sourceValue}
}
# Remove the original field from the library if needed
if ($_.CanBeDeleted) {
Remove-PnPField -List $list -Identity $_.InternalName
}
} else {
Write-Host "Site column already exists for field: $($_.Title)"
}
}