Can’t remove a site column from a content type or list in SharePoint? Use PowerShell

There are particular site columns in SharePoint, which once added to a site content type or list cannot be removed again using the browser UI. An example of one of these columns in SharePoint Server 2010 is “Aliases”. If I add it to a content type and then click on the column name to remove it, you can see that the Remove button is not available.

This can cause much frustration and may even prompt you to completely delete the content type and start again – something that is usually impossible to do on a live environment.

Fortunately, we have PowerShell available to us for solving such problems. The script below will attach to the site containing your content type (also works for content type hubs), the content type itself, and then the link to the column within the content type. It then only takes a couple of lines to remove the column and update the content type.

The example below will remove the “Aliases” site column from the “Sales Document” site content type in http://portal:

#Attach to the web and content type
$web = Get-SPWeb http://portal
$ct = $web.ContentTypes["Sales Document"]

#Get link to the columnn from the web
$spFieldLink = New-Object Microsoft.SharePoint.SPFieldLink ($web.Fields["Aliases"])

#Remove the column from the content type and update
$ct.FieldLinks.Delete($spFieldLink.Id)
$ct.Update()

#Dispose of the web object
$web.Dispose()

Phew!

Note that any columns of this type deleted from a parent content type will not automatically disappear in the child content types below it. The easiest way to get around this is to use the same script above to remove the site column from child content types, too.

Unfortunately, you may also find that if the content types containing this site column were attached to lists, then the lists themselves will also have the column added to them – even after removing the column from all associated content types. If this is the case, you will not be able to remove the column in the list using the browser UI as the Remove button will be missing on the column administration page here also.

To resolve this, you can either decide to remove the column using PowerShell one list at a time, or if there is a way of defining a batch of lists, use a script to modify multiple lists in one go. The script below provides you with an example of how to walk through each document library of a specified name on each site of the site collection and delete the offending column. If you do decide to run a script like this, please ensure you have fully tested it in a development environment beforehand due to the potential damage that it could inflict:

#Delete column on a specified list in all sites of a site collection
$site = Get-SPSite http://portal
$site | Get-SPWeb -Limit all | ForEach-Object {
    #Specify list which contains the column
    $list = $_.Lists["Pages"]
    #Specify column to be deleted
    $field = $list.Fields["Aliases"]
    #Allow column to be deleted
    $field.AllowDeletion = $true
    #Delete the column
    $field.Delete()
    #Update the list
    $list.Update()
}
$site.Dispose()

Create SharePoint search keywords and best bets from a CSV file using PowerShell

If your SharePoint implementation comprises of different Search Centers across a number of site collections, managing a consistent set of keywords and best bets can be difficult through the browser UI. Each keyword can be time consuming to create, especially if it contains multiple best bets.

The script in this article allows you to use a CSV file to specify a set of keywords and best bets for a specified site collection. You can either append to any existing keywords already created in the site collection, or wipe out the current keywords and create new ones.

First, the CSV file should have the following header:

Keyword,Definition,Synonyms,BestBet1,Description1,Url1,BestBet2,Description2,Url2,BestBet3,Description3,Url3

I have specified three best bets in the example above, but you are free to add as many BestBet, Description, and Url columns as you like. Although hard to read on this page, here is an example of a CSV file with some example keywords included:

Keyword,Definition,Synonyms,BestBet1,Description1,Url1,BestBet2,Description2,Url2,BestBet3,Description3,Url3
PowerShell,Here are some links for the keyword 'PowerShell',Windows PowerShell;Scripting;PS;Scripts,Get-SPScripts,PowerShell scripts for SharePoint,http://get-spscripts.com,Hey Scripting Guy! Blog,Microsoft TechNet blog on scripting,http://blogs.technet.com/b/heyscriptingguy/default.aspx,Effective Windows PowerShell,Free e-Book on learning PowerShell,http://keithhill.spaces.live.com/blog/cns!5A8D2641E0963A97!6930.entry
SharePoint,Here are some links for the keyword 'SharePoint',SharePoint Server;SP2010;Foundation;Collaboration,PACSharePoint.com,SharePoint blog offering hints tips and solutions,http://pacsharepoint.com,Microsoft SharePoint Team Blog,The official blog of the Microsoft SharePoint Product Group,http://blogs.msdn.com/b/sharepoint/,,,

This CSV file will add two keywords – “PowerShell” and “SharePoint” – along with synonyms and best bets for each one. Note that where there is more than one synonym for each keyword, add them as semi-colon delimited values within the CSV column.

Now the script. Before you can add the keywords, you must run the script below in PowerShell, which will set up the Import-SearchKeywords function. You will need to replace “Search Service Application” with the name of your search service application proxy from the Manage Service Applications page in Central Administration:

function Import-SearchKeywords ($SiteUrl, $CSVFilePath, [switch]$RemoveOldKeywords)
{
    $ssap = Get-SPEnterpriseSearchServiceApplicationProxy -Identity "Search Service Application"
    $keywords = New-Object Microsoft.Office.Server.Search.Administration.Keywords($ssap, $SiteUrl)
    $allKeywords = $keywords.AllKeywords
    $date = Get-Date
   
    #Remove all previous keywords from the site collection if chosen
    if($RemoveOldKeywords) {
        $keywordsArray = @()
        $allKeywords | ForEach-Object {
            $keywordsArray = $keywordsArray + $_.Term
        }
        $keywordsArray | ForEach-Object {
            write-host "Deleting keyword:"$_
            $allKeywords[$_].Delete()
        }
    }
   
    #Import CSV file
    $csvData = Import-Csv $CSVFilePath | ForEach-Object {
        #Create keyword
        write-host "Importing keyword:"$_.Keyword
        $keyword = $allKeywords.Create($_.Keyword, $date.AddHours(-1))
        $keyword.Definition = $_.Definition
       
        #Create synonyms
        $synonymArray = @()
        $synonymArray = $_.Synonyms.Split(";")
        $synonymArray | ForEach-Object {
            write-host "Creating synonym"$_ "for keyword" $keyword.Term
            if (($_ -ne $null) -and ($_ -ne "")) { $keyword.Synonyms.Create($_) }
        }
       
        #Create best bets
        $bestBetColumnNumber = 1
        $bestBetColumn = "BestBet" + $bestBetColumnNumber
        $descriptionColumn = "Description" + $bestBetColumnNumber
        $urlColumn = "Url" + $bestBetColumnNumber
       
        while ($_.$bestBetColumn) {
            write-host "Creating best bet"$_.$bestBetColumn "for keyword" $keyword.Term
            $keyword.BestBets.Create($_.$bestBetColumn, $_.$descriptionColumn, $_.$urlColumn)
           
            $bestBetColumnNumber = $bestBetColumnNumber + 1
            $bestBetColumn = "BestBet" + $bestBetColumnNumber
            $descriptionColumn = "Description" + $bestBetColumnNumber
            $urlColumn = "Url" + $bestBetColumnNumber
        }
       
        #Update keyword with best bets
        $keyword.Update()
    }
}

Once you have your CSV file ready and the script above has been run, you have one of two options for importing the keywords and best bets.

To append the keywords in your CSV with those already present in the site collection, type the following command:

Import-SearchKeywords –SiteUrl <SiteCollectionURL> –CSVFilePath <CSVFilePathAndFileName>

To delete all existing keywords from the site collection and create a new set from scratch with the contents of your CSV file, type the same command but with the RemoveOldKeywords switch:

Import-SearchKeywords –SiteUrl <SiteCollectionURL> –CSVFilePath <CSVFilePathAndFileName> -RemoveOldKeywords

For example, to create an entirely new set of keywords in the “http://portal” site collection using my example “SearchKeywords.csv” file above, you would type:

Import-SearchKeywords -SiteUrl http://portal -CSVFilePath C:\Install\SearchKeywords.csv –RemoveOldKeywords

The “Manage Keywords” site collection administration page will now look as follows:

And the following results will appear when I type one of my keywords in the search center:

If you are looking to import FAST Search keywords and user contexts to SharePoint 2010 from a CSV file, take a look at this script posted on the Microsoft TechNet Script Center at http://gallery.technet.microsoft.com/ScriptCenter/en-us/9ad93d90-9ca7-4515-b81e-441eda0390c3.

Change SharePoint list settings using PowerShell - Part 2: Advanced Settings

This article follows on from Part 1 last week, where I detailed how to change the General and Versioning Settings of a SharePoint list or document library with PowerShell.

This part covers changing the Advanced Settings of a list. As a recap, you can use the following script to change the settings of a specifically named list on each site in the site collection. In the example below, we are changing the settings of a document library called “Shared Documents” on each site in the “http://portal” site collection:

#Change these variables to your site URL and list name
$site = Get-SPSite http://portal
$listName = "Shared Documents"

#Walk through each site in the site collection
$site | Get-SPWeb | ForEach-Object {

#Get the list in this site
$list = $_.Lists[$listName]

#Make the list changes

#Update the list
$list.Update()
}

#Dispose of the site object
$site.Dispose()

The table below shows the Advanced Settings we can change, with snapshots of the same settings from the list admin UI. To use any of these settings from the table, copy them into the script above, underneath the “#Make the list changes” line:

Document Template (document libraries only)	The following set of commands will upload a new document template from a path on your computer or network (UNC) path and change the document template setting on the list: #Upload new document template file from computer $newTemplateFile = Get-ChildItem "C:\LocalPath\Test Document.dotx" $templateFolderPath = $_.Url + "/" + $list.RootFolder.Url + "/Forms" $templateFolder = $_.GetFolder($templateFolderPath) $uploadPath = $templateFolder.Url + "/" + $newTemplateFile.Name $spNewTemplateFile = $templateFolder.Files.Add($uploadPath, $newTemplateFile.OpenRead(), $true) #Change list setting to the template file uploaded $list.DocumentTemplateUrl = $uploadPath Note: If you are running this script on a single site rather than in bulk on each site in the site collection, change the $_ references above to the variable you have assigned to the site – e.g., $web
Opening Documents in the Browser (document libraries only)	There are a couple of settings affecting these options. Use the following commands to set one of the first two options: #Open in the client application $list.DefaultItemOpen = "PreferClient” #Open in the browser $list.DefaultItemOpen = "Browser" If you want to set the “Use the server default” option, use the following command: $list.DefaultItemOpenUseListSetting = $false
Custom Send To Destination (document libraries only)	For this option you have to set a destination name and URL of the custom Send To location: $list.SendToLocationName = "Home Site" $list.SendToLocationUrl = "http://Portal/Shared Documents"
Item-level Permissions (lists only)	There are two options on this section, depending on whether you are looking to restrict user read or write access. Read access This setting uses a numerical value to determine which option is set: Read all items = 1 Read items that were created by the user = 2 For example, the following command would be used to configure this setting to “Read items that were created by the user”, as pictured above: $list.ReadSecurity = 2 Create and Edit access This setting also uses a numerical value to set the option desired: Create and edit all items = 1 Create items and edit items that were created by the user = 2 None = 4 For example, the following command would be used to configure this setting to “None”, as pictured above: $list.WriteSecurity = 4
E-Mail Notification (task and issue based lists only)	This command will enable the “Send e-mail when ownership is assigned” option: $list.EnableAssignToEmail = $true
Attachments (lists only)	The following command will disable attachments on the list: $list.EnableAttachments = $false
Folders	Use the following line to disable the “New Folder” command on list and library menus: $list.EnableFolderCreation = $false
Search	Careful with the wording on this one, as setting it to $true will set it to No in the UI, as pictured above: $list.NoCrawl = $true
Offline Client Availability	Similar to the way the Search option works above, setting this one to $true will set it to No in the UI, as pictured above: $list.ExcludeFromOfflineClient = $true
Site Assets Library (document libraries only)	This setting will switch the document library to be a site assets library: $list.IsSiteAssetsLibrary = $true
Datasheet	Careful with the wording on this one also, as setting it to $false will set it to Yes in the UI, as pictured above: $list.DisableGridEditing = $false
Dialogs	Similar to the way the Datasheet option works above, setting this one to $true will set it to No in the UI, as pictured above: $list.NavigateForFormsPages = $true

In Part 3 of this series, I’ll cover a few extra settings you can configure on a list with PowerShell, including some options hidden from the UI…

Create multiple SharePoint sites with permissions using a CSV file and PowerShell

When defining hierarchical structures, I personally prefer using an XML file, but there is still a place for the good old Comma-separated values (CSV) format. Firstly, it is easier to create and modify a CSV file – especially when using an application like Excel – and as the format has been around for a while, most people find it easier to read and understand. Despite its flaws, this must be the major reason why Microsoft decided to use CSV as the format for the Managed Metadata import file in SharePoint 2010.

Now we have the New-SPWeb PowerShell cmdlet introduced in SharePoint 2010, creating individual sites and sub-sites is easy enough to do without needing to visit the browser UI, but you still need to put in a bit of work if you are looking to create an entire site hierarchy using PowerShell.

To help you out, I have uploaded a “Create SharePoint Sites from CSV” PS1 script file to Codeplex for download. This script can be used to create multiple SharePoint sites in bulk from a CSV file and also set permissions on each site during creation.

It includes the following features:

• Choose any site from which to start creating sub-sites
• Create a full site hierarchy with sub-sites down to three levels from the start site
• Specify a site title, description, and URL name
• Specify whether to inherit parent site top navigation bar
• Specify whether to inherit parent site permissions
• If breaking permission inheritance, specify whether to add new permissions from scratch or by copying over permissions from the parent site and adding to those
• Assign site permissions to SharePoint groups, Active Directory users or Active Directory groups, specifying the permission level required
• Create the specified SharePoint group or Active Directory account in the site, if they do not already exist

You can download the script, documentation and an example CSV file at http://spcreatesitesfromcsv.codeplex.com. As always, I welcome any comments, suggestions, ideas or contributions for any new features if you have them.

Delete multiple SharePoint sites using PowerShell

It can be very time consuming in the SharePoint browser UI to delete a large structure of sub-sites below a particular site. This is because you are unable to delete a site that contains other sub-sites without deleting those sub-sites first, which in turn may contain other sub-sites!

The PowerShell script below allows you to choose a start site (i.e., the site from which you want to delete all sub-sites), walks through each sub-site and deletes it, starting from sites at the end of the hierarchy and working backwards until it returns back to the start site, which it will not delete.

First, run the PowerShell script below – Note: Doing this will not start deleting sites as you have to call the functions in the script with a separate command (see below).

function Delete-SPSubSites ($StartSite)
{
    Start-SPAssignment –Global
    #Get the starting site from which to delete all sub-sites
    $web = Get-SPWeb $StartSite
    #Run function to get all sub-sites below the start site
    Get-SPSubWebs -StartWeb $web
    Stop-SPAssignment –Global
}

function Get-SPSubWebs ($StartWeb)
{
    #Get all sub-sites below the start site
    $webs = $StartWeb.GetSubwebsForCurrentUser()
    #Run this routine if there are sub-sites present
    if ($webs.Count -gt 0)
    {
        #Walk through each sub-site
        foreach ($nextWeb in $webs)
        {
            #Check for sub-sites underneath this site
            Get-SPSubWebs -StartWeb $nextWeb
            #Run function to delete the site
            Delete-SPSubWeb -DeleteWeb $nextWeb
            $nextWeb.Dispose()
        }
    }
}

function Delete-SPSubWeb ($DeleteWeb)
{
    #Delete the site specified in the $DeleteWeb variable
    write-host "Deleting site"$DeleteWeb.Title"at"$DeleteWeb.Url
    $DeleteWeb.Delete()
    $DeleteWeb.Dispose()
}

Once you have run the script, you can use it to delete sub-sites by typing the following command in the PowerShell console:

Delete-SPSubSites -StartSite http://portal/team

The example above will delete all sub-sites below http://portal/team, but will not delete the http://portal/team site itself.

In case it’s not obvious (!), be very careful with the use of this script. Take a backup of your site collection first, and run on a development server before using this script in a live environment to ensure you delete the correct sites.

Change SharePoint list settings using PowerShell - Part 1: General and Versioning

Let’s say that you have 100 team sites created and being used with a document library called Shared Documents available in each site. You are then asked to change the versioning settings on each Shared Documents library in every one of those 100 sites to create major and minor versions. To resolve this requirement for any new document libraries and sites created in the future, you can wrap up the changes into list and site templates. However, doing this will not affect any of the 100 existing document libraries already being used.

Enter PowerShell. In part one of this series, I will show you how you can change the General and Versioning Settings of a list. First, we’ll change the settings of a single list in a single site. Whilst this is easy enough to do in the UI and unlikely to be used in a script, it will give you an idea of how the script works:

#Get the site and list objects
$web = Get-SPWeb http://portal/team
$list = $web.Lists["Shared Documents"]

#Make the list changes
$list.Title = "Documents"

#Update the list and dispose of the web object
$list.Update()
$web.Dispose()

The script above gets a document library called “Shared Documents” in the site “http://portal/team”, sets a new title for the document library to “Documents”, and updates the settings for the list. If there is more than one setting to change, we could have any number of settings defined under the “#Make the list changes” line and they will all be updated by the $list.Update() command.

The script below would certainly take a lot longer to do in the UI, as it walks through each site in the “http://portal” site collection, changing the title and description of the document library called “Shared Documents” in each site – all you have to do is tell the script the relevant site collection URL, list name, and which settings you want to change on each list with the name specified:

#Change these variables to your site URL and list name
$site = Get-SPSite http://portal
$listName = "Shared Documents"

#Walk through each site in the site collection
$site | Get-SPWeb | ForEach-Object {

#Get the list in this site
$list = $_.Lists[$listName]

#Make the list changes
$list.Title = "Documents"
$list.Description = "Use the Documents library to store shared files"

#Update the list
$list.Update()
}

#Dispose of the site object
$site.Dispose()

The table below shows the other General and Versioning Settings we can also change, with snapshots of the same settings from the list admin UI. To use any of these settings from the table, copy them into the script above, underneath the “#Make the list changes” line.

General Settings

List Name	The following command will change the list display name (note, this will not change the list URL): $list.Title = "Documents"
List Description	The following command will change the list description: $list.Description = "Use the Documents library to store shared files"
Navigation	To configure the list so that it shows on the Quick Launch (left-hand site navigation), use this command: $list.OnQuickLaunch = $true Change the $true to $false if you want to remove the list from being shown on the Quick Launch.

Versioning Settings

Content Approval	This command will change the list to require approval for all items: $list.EnableModeration = $true Change the $true to $false if you want to set content approval to No.
Document Version History (settings and wording can be different between document libraries and lists, as annotated in the script code)	There are a few settings on this section, which I have annotated in the commands below: #Create a version each time you edit an item in this list (lists) #Create major versions (document libraries) $list.EnableVersioning = $true #Create major and minor (draft) versions (document libraries only) $list.EnableMinorVersions = $true #Keep the following number of versions (lists) #Keep the following number of major versions (document libraries) $list.MajorVersionLimit = 5 #Keep drafts for the following number of approved versions (lists) #Keep drafts for the following number of major versions (document libraries) $list.MajorWithMinorVersionsLimit = 5
Draft Item Security	This setting uses a numerical value to determine which option is set: Any user who can read items = 0 Only users who can edit items = 1 Only users who can approve items (and the author of the item) = 2 For example, to configure this setting for “Only users who can edit items”, as pictured above, use this command: $list.DraftVersionVisibility = 1
Require Check Out (document libraries only)	This command will change the list to require documents to be checked out before they can be edited: $list.ForceCheckout = $true

In Part 2 of this series, I cover how to use PowerShell for configuring the Advanced Settings of a list.

Useful PowerShell for SharePoint links and resources #2

Every now and again I like to post a bunch of links and resources I have found recently on using PowerShell for SharePoint – mainly so that I don’t forget about them myself!

• PowerShell Reference for SharePoint 2010 - http://splabs.codeplex.com/releases/view/49118. Free eBook with over 2500 pages documenting all PowerShell cmdlets for SharePoint 2010.
• Set object caching user accounts with PowerShell - http://www.sharepointnutsandbolts.com/2010/09/set-object-caching-user-accounts-with.html. Great article by Chris O’Brien on why you need to configure object caching in SharePoint 2010 and how to do it using a PowerShell script.
• SharePoint 2010 PowerShell Feature Cmdlets - http://www.sharepointanalysthq.com/2010/09/sharepoint-2010-powershell-feature-cmdlets-2/. Nice overview of how to manage features using PowerShell.
• Set Site Collection Search Settings by PowerShell - http://mrhodes.net/2010/09/14/set-site-collection-search-settings-by-powershell/. Very handy as this is not easy to find.
• Finding site templates in SharePoint 2010 through PowerShell - http://secretsofsharepoint.com/cs/blogs/tips/archive/2010/09/09/finding-templates-in-sharepoint-2010-through-powershell.aspx. Very simple, but extremely useful.
• PowerShell articles for SharePoint 2010 on the Hey, Scripting Guy! blog - http://blogs.technet.com/b/heyscriptingguy/archive/tags/windows+powershell/sharepoint/. Keep your eye on this page. There are already a bunch of very useful scripts on here, which I have no doubt will be added to over the next few months.
• Getting an Inventory of All SharePoint Documents Using Windows PowerShell - http://stsadm.blogspot.com/2010/08/getting-inventory-of-all-sharepoint.html. Some useful techniques going on here – especially using PSObject to provide piping to Out-GridView and Export-Csv cmdlets.
• Find error messages with a Correlation ID token in SharePoint 2010 - http://www.zimmergren.net/archive/2010/09/03/sp-2010-find-error-messages-with-a-correlation-id-token-in-sharepoint-2010.aspx. Tobias Zimmergren provides us with a few ways of tracking correlation ID’s in SharePoint 2010 – one of them being PowerShell.

Bulk upload files with metadata into SharePoint using PowerShell: UPDATE

This is an update to an article that I wrote back in July, which contained the first version of a PowerShell script to copy multiple documents from a file system into a SharePoint 2010 document library, automatically tagging them with column values (metadata) during upload. Back then, the script had very limited functionality, but I have now added some new features, converted the script to a function, and uploaded the PS1 script file with documentation to Codeplex for download.

In summary, the script now includes the following features:

- Upload multiple files from a local folder or network share into SharePoint 2010 document libraries
- Files can be uploaded from a single folder or an entire folder hierarchy
- Retain the folder hierarchy from the source path into the destination document library
- Choose the root or a specific destination folder in the document library to copy the files into
- Support for adding values to the following column types in the manifest file:
      - Single line of text
      - Multiple lines of text
      - Choice – Single value (drop-down/radio button)
      - Choice – Multiple value (checkboxes)
      - Currency
      - Number
      - Yes/No (checkbox)
      - Person or Group – Single value
      - Person or Group – Multiple value
      - Hyperlink or Picture
      - Managed Metadata – Single value
      - Managed Metadata – Multiple value
- Automatic file check in after upload, if required
- Automatic file approval after upload, if required
- Option to overwrite existing files in the destination document library
- Option to flatten the structure when copying from a multi-level folder hierarchy to a single folder in a document library (i.e., merging sub-folders to a single location)

You can download the script and user guide from Codeplex at http://spfileupload.codeplex.com. One of the advantages of using PowerShell for this tool rather than managed code, is the ability for you to customise the script should you wish to add any custom features as required. I intend to continue adding extra features in the future, but also welcome any ideas or contributions to the Codeplex site if you have them.