PowerShell Script to create Site Columns

Hello!

In many scenarios, like setting up a new SharePoint environment or upgrading to a new version or taxonomy conversions, developers are tasked with the creation of ten's to hundereds of site columns in a site collection/web.

So to make this easier for us, I came up with a powershell way to input data into a location and create site columns by reading the input data. The input location can be a sharepoint data list or a .csv file based on the requirement. (I personally prefer working with spreadsheets, but for other's convenience, I will include code for both)

My input spreadsheet looks something like this. You can add/remove any properties as per your requirement or comfort, but do not forget the change the code accordingly (If you use a data list, the list must be populated with the same columns and appropriate values)

Choice fields and managed metadata fields are treated as special cases in my script. The reason being, metadata columns require the intialization of the taxonomy session, term store, groups, sets etc. before the field can be created and choice columns require the declaration of a string collection to which the choice values can be added.

 

# Desription:
# This script is used to create site columns in a site collection
# The following types are created using this script
# Web UI Type                      SPFieldType
# Single line of text                             Text
# Multiple lines of text                        Note
# Choice (menu to choose from)         Choice
# Number (1, 1.0, 100)                        Number
# Currency ($, ¥, €)                             Currency
# Date and Time                                 DateTime
# Yes/No (check box)                         Boolean
# Person or Group                               User
# Hyperlink or Picture                         URL
# Managed Metadata                          Managed Metadata

#### Parameters ####

# To input data from a csv file

# Get the CSV File location which contains the input values. Change value in "" to match appropriate location
$inputFile = "C:\/*Folder lcoation*/\SiteColumnsToBeAdded.csv"

#Import values from the CSV
$siteColumnsToBeAdded = import-csv $inputFile

# To input Data from data list (Optional)

# Get the web in which the input data list is located
$listWeb = Get-SPWeb "/* URL of web where the list is located*/"

# Get the List which stores the input values. Change the value in "" to match appropriate list name
$inputList = $listWeb.Lists["Site Columns List"]

# Get the list items
$siteColumnsToBeAdded = $inputList.Items

# For each row in the csv file/each list item, create a new site column based on the name,description and other properties

foreach($siteColumn in $siteColumnsToBeAdded)
{

       # Get the Site Collection where the site columns will be created.
       $site = new-object Microsoft.SharePoint.SPSite "/* URL of Site collection*/"

       # Get the Root Web of the site collection. If the columns are created a a sub-web level use the cmdlet Get-SPWeb "" to get the url of the subweb.
       $web = $site.RootWeb #Get-SPWeb "/* URL of web*/"

       # Get the name of the site column from csv file
       $name = $siteColumn.Name

       # Check if the field does not exist already
       if($web.Fields[$name] -eq $null)
      {
            # Get the type of the field
            $type = $siteColumn.Type

            # To create a managed metadata field; do the following
            if($type -eq "Managed Metadata")
            {
                      # Get the Taxonomy session of your site collection
                      $session = new-object Microsoft.SharePoint.Taxonomy.TaxonomySession($site)

                      # Get the Metadata service used by the agency. Change value in "" to match appropriate name
                      $termStore = $session.TermStores["Test Managed Metadata Service"]

                      # Get the term store group which stores the term sets you want to retrieve. Change  value in "" to match appropriate name
                      $termStoreGroup = $termStore.Groups["Test Taxonomy"]

                      # Get the term set you want to associate with this field.
                      $termSet = $termStoreGroup.TermSets[$siteColumn.TermSet]

                      # In most cases, the anchor on the managed metadata field is set to a lower level under the root term of the term set. In such cases, specify the term in the spreadsheet and do the following

                      if($siteColumn.TermSet -ne $siteColumn.Term)
                      {
                                #Get all terms under term set
                                $terms = $termSet.GetAllTerms()

                               #Get the term to map the column to
                               $term = $terms | Where-Object {$_.Name -eq $siteColumn.Term}

                               #Get the GUID of the term to map the metadata column anchor to
                               $termID = $term.Id
                        }
                     else # In cases when you want to set the anchor at the root of the term set, leave the  value as blank. Empty guids will error out when you run the script but will accomplish what you need to do i.e. set the anchor at the root of the termset
                       {                                $termID = [System.GUID]::empty
                       }
                     # Create the new managed metadata field
                     $newSiteColumn = $web.Fields.CreateNewField("TaxonomyFieldType", $name)

                     # Update the properties of the new field.
                     $newSiteColumn.SspId = $termSet.TermStore.ID
                     $newSiteColumn.TermSetId = $termSet.Id
                     $newSiteColumn.AnchorId = $termID
                     $newSiteColumn.AllowMultipleValues = $siteColumn.AllowMultipleValues

                     # Add the the new column to the Site collection's Root web's Fields Collection
                     $web.Fields.Add($newSiteColumn)

                     # Update the web
                     $web.Update()
         }

         elseif($type -eq "Choice") # To create a choice field; do the following
        {
                    # Build a string array with the choice values separating the values at ","
                    $choiceFieldChoices = @($siteColumn.Choices.Split(","))

                    # Declare a new empty String collection
                     $stringColl = new-Object System.Collections.Specialized.StringCollection

                    # Add the choice fields from array to the string collection
                    $stringColl.AddRange($choiceFieldChoices)

                    # Create a new choice field and add it to the web using overload method
                      SPFieldCollection.Add method (String, SPFieldType, Boolean, Boolean, StringCollection)
                    $newSiteColumn = $web.Fields.Add($name,[Microsoft.SharePoint.SPFieldType]::$type, $siteColumn.Required, $false, $stringColl)

                    # Update the web
                    $web.Update()

         }
         else # For any other type of field; do the following
         {
                     # Create the new field and add it to the web
                     $newSiteColumn = $web.Fields.CreateNewField([Microsoft.SharePoint.SPFieldType]::$type, $name)
                     $web.Fields.Add($newSiteColumn)
                     $web.update()

           }
write-host "The following site column has been created:" $name

          # You will need to call a new instance of the created site column; direct use of the              $newSiteColumn will result in errors
          $sc = $null
          $sc = $web.Fields[$name]

          # Add or remove any properties here
          $sc.Description = $siteColumn.Description
          $sc.Group = $siteColumn.Group

        # Boolean values must be converted before they are assigned in PowerShell.
[boolean]$sc.ShowInNewForm = [System.Convert]::ToBoolean($siteColumn.ShowInNewForm)
[boolean]$sc.ShowInDisplayForm = [System.Convert]::ToBoolean($siteColumn.ShowInDisplayForm)
[boolean]$sc.ShowInEditForm = [System.Convert]::ToBoolean($siteColumn.ShowInEditForm)
[boolean]$sc.Hidden = [System.Convert]::ToBoolean($siteColumn.Hidden)
[boolean]$sc.Required = [System.Convert]::ToBoolean($siteColumn.Required)
           $sc.DefaultValue = $siteColumn.DefaultValue

          # Update the site column
          $sc.Update()

          # Update the web
          $web.Update()

    }
    else # If the site column already exists
    {
                write-host "The following site column already exists:" $name
     }
}

# Dispose the web and site ** Important **
$web.Dispose()
$site.Dispose()     

 

Output:

Errors/Notes:

1. If the term store values do not match, you might see some null value errors.

2. All the field types must be entered in the input as described in the beginning of the script. Else the script will fail.

3. When the term column is left empty with an intention to set the anchor to the root of term set, the following runtime error will showup. This is expected and can be safely ignored 

Sources:

1. Google

2. http://www.sharepointpals.com/post/Create-a-Site-Column-in-SharePoint-2010

3. http://msdn.microsoft.com/en-us/library/Microsoft.SharePoint.SPFieldCollection_methods(v=office.14).aspx
4. http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spfieldtype.aspx