Wednesday 20 March 2013

Clear (Delete) the value of a taxonomy field using PowerShell

[Update, 3/July/2013]: I've converted this PowerShell into a script which can be downloaded from the Microsoft TechNet Gallery, here: http://gallery.technet.microsoft.com/sharepoint/Update-or-Clear-Metadata-94959cb3

The script makes it easy to clear or update the metadata values of a metadata column.

E.g.

Import-Module C:\Scripts\Update-MetadataField.ps1
$listitems = (Get-SPWeb http://devmy101).Lists["mylistwithdatainit"].Items 
Clear-MetadataField -items $listitems -MetadataFieldInternalName metadataField -ClearAllItems

[Original Article]

Yesterday I got stumped working out how to clear the value of taxonomy field when helping someone on the Microsoft forums. I could clear the value of the field using the TaxonomyField.ParseAndSetValue(item, value) method of the field, setting the value to $null, but it only worked if the list item belonged to a list. If the item was in a document library, the value wouldn't be cleared. Eventually (after a little persistence), I worked it out, and thought I'd share it here.
The proper way to clear a taxonomy field (which seems obvious upon reflection!), is to get an empty taxonomy value, using the Taxonomy.GetFieldValue method, and then pass it to the TaxonomyField.SetFieldValue method.

The only other difference between working with lists and document libraries, is the way in which we retrieve the list item.

Working with Lists
$web = get-spweb http://my
$list = $web.Lists["my list"]
$item = $list.items[1] #Get an item that currently has a value set in the Taxonomy (Managed Metadata) field
$taxField = $item.Fields["metadataField"] -as [Microsoft.SharePoint.Taxonomy.TaxonomyField] 
$taxFieldValue = $taxField.GetFieldValue("");
$taxField.SetFieldValue($item,$taxFieldValue)
$item.Update()

Working with Document Libraries
$web = get-spweb http://my
$list = $web.Lists["document library"] -as [Microsoft.SharePoint.SPDocumentLibrary]
$item = $list.items[1] #Get an item that currently has a value set in the Taxonomy (Managed Metadata) field
$url = [String]::Format("{0}{1}",$web.Url, $item.File.Url)
$file = $web.GetFile($url)
if($file.CheckOutStatus -eq "None")
{
$file.CheckOut()
$fileItem = $file.Item
$taxField = $fileItem.Fields["metadataField"] -as [Microsoft.SharePoint.Taxonomy.TaxonomyField]
$taxFieldValue = $taxField.GetFieldValue("");
$taxField.SetFieldValue($fileItem,$taxFieldValue)
$fileItem.Update()
$file.CheckIn("Updated Taxonomy Field Value")
}
else
{
$msg = [String]::Format("This file, {0}, is checked out and cannot be edited at the moment",$file.Name)
Write-Host $msg -ForegroundColor DarkYellow
}

Other tips...

Getting an item by it's ID
$item = $list.GetItemById(12)

Looping through the list
$web = get-spweb http://my
$list = $web.Lists["my list"]
$items = $list.items
$taxField = $list.Fields["metadataField"] -as [Microsoft.SharePoint.Taxonomy.TaxonomyField] 
$taxFieldValue = $taxField.GetFieldValue("");
foreach($item in $items)
{
$taxField.SetFieldValue($item,$taxFieldValue)
$item.Update()
}

Tuesday 19 March 2013

Adding all users in an Active Directory group into a SharePoint group

Depending on what your strategy is for managing users in SharePoint groups, you might want to add all the users in an Active Directory group into a SharePoint group, without adding the actually Active Directory group itself.

Well, rather than adding them one at a time via the GUI (yawn), why not fire up PowerShell and tap out a few lines of code that will do it for you!

Before you do this, you need to have install RSAT and enabled the Active Directory module for PowerShell on one of your SharePoint servers.

Install RSAT (if you haven't already). Download it from Microsoft here: Remote Server Administration Tools

Once RSAT is install, open PowerShell and run the following command:
import-module ActiveDirectory

Now, the easy bit, run some code to add all the users from an Active Directory group into the SharePoint group.
$adGroupName = "my ad group";
$sharePointGroupName = "Home Members";
$sharePointWebUrl = "http://myweb/subweb";
$currentDomain = (Get-ADDomain).Name
$adGroupMembers = Get-ADGroupMember $adGroupName
$web = get-spweb $sharePointWebUrl
$sharePointGroup = $web.Groups[$sharePointGroupName]

foreach($u in $adGroupMembers){
$userInfo = Get-ADUser $u -Properties "mail"
$sharePointGroup.AddUser([String]::Format("{0}\{1}", $currentDomain, $userInfo.SamAccountName), $userInfo.mail, $u.name, $null);
Write-Host "Added user"$u.Name"to the"$sharePointGroup.Name"group" -foregroundcolor Green
} 



Thursday 14 March 2013

Reading, Updating and Adding Custom or Built-in Office Document Properties

I really love PowerShell (though I regularly curse a lot when I'm debugging it). It's great for all those tasks where you don't want to do something manually, can't afford the time to write program, but can spend a few minutes knocking out a script!

This post is about writing custom and built-in properties to office documents, and writing an entry for each template to a database.

My brief was to update approximately 1000 templates;
  • Read the built-in comments property, and check if it has an existing version string, in the format v#
  • Update the built-in author and company properties, and clear the comments property
  • Add the template to a database table, using a stored procedure that returns the new unique ID for the template (unique row ID)
  • Create two new custom properties that will store the template unique id and version
The script contains three functions, one for adding the template to the database, Add-TemplateToDatabase, one for returning an physical office location id (based on the templates file path), Get-OfficeCode, and the main function that processes each template, Update-Template.

Finally, we get a collection of templates, loop through them, sending each template to the Update-Template function based on the file type.

Before looking at the full script, here's the bits that do the reading and writing of properties:

Reading the built-in comments property:

$wordApplication = New-Object -ComObject word.application
$document = $application.documents.open("c:\document.docx");
$binding = "System.Reflection.BindingFlags" -as [type];        
$builtinProperties = $document.BuiltInDocumentProperties
$pComments = "Comments"
[Array]$commentsArgs = $pComments
$builtinPropertiesType = $builtinProperties.GetType()
#Get the Property item for the Comments property
$commentsProp = $builtinPropertiesType.InvokeMember("Item", $binding::GetProperty, $null, $builtinProperties, $commentsArgs)
#Get the value of the Comments property, so we can check if it contains a version string.
$commentsPropValue  = [System.__ComObject].InvokeMember("value",$binding::GetProperty,$null,$commentsProp,$null);
$document.Saved = $false;
$document.save();
$wordApplication.Quit();

Writing a new value to the built-in comments property

$wordApplication = New-Object -ComObject word.application
$document = $application.documents.open("c:\document.docx");
$binding = "System.Reflection.BindingFlags" -as [type];        
$builtinProperties = $document.BuiltInDocumentProperties
$pComments = "Comments"
$pCommentsValue = "Add this comment to the comments property";
[Array]$commentsArgs = $pComments
$builtinPropertiesType = $builtinProperties.GetType()
#Get the Property items for each of the three builtin properties we're using
$commentsProp = $builtinPropertiesType.InvokeMember("Item", $binding::GetProperty, $null, $builtinProperties, $commentsArgs)
[Array]$commentsArgs = $pCommentsValue;
$builtinPropertiesType.InvokeMember("Value", $binding::SetProperty,$null, $commentsProp, $commentsArgs)
$document.Saved = $false;
$document.save(); 
$wordApplication.Quit();

Creating / Updating a custom property

$wordApplication = New-Object -ComObject word.application
$document = $application.documents.open("c:\document.docx");
$binding = "System.Reflection.BindingFlags" -as [type];   
$customProperties = $document.CustomDocumentProperties
$typeCustomProperties = $customProperties.GetType()
#Define the two new properties and their values
$cpUid = "templateuid"
$cpUidValue = [int]$newUid;
$cpVersion = "templateversion"
$cpVersionValue = $currentversion;       
[array]$arrayArgsC = $cpUid,$false, 1, $cpUidValue;
[array]$arrayArgsD = $cpVersion,$false, 1, $cpVersionValue       
Try
{
    #Add the properties to the template
    $typeCustomProperties.InvokeMember("add", $binding::InvokeMethod,$null,$customProperties,$arrayArgsC) | out-null
    $typeCustomProperties.InvokeMember("add", $binding::InvokeMethod,$null,$customProperties,$arrayArgsD) | out-null
}
Catch [system.exception]
{
    #If the property already existed, get the property, delete the property, then readd it.
    $propertyObject1 = $typeCustomProperties.InvokeMember("Item", $binding::GetProperty,$null,$customProperties,$cpUid)
    $propertyObject2 = $typeCustomProperties.InvokeMember("Item", $binding::GetProperty,$null,$customProperties,$cpVersion)
    $typeCustomProperties.InvokeMember("Delete", $binding::InvokeMethod,$null,$propertyObject1,$null)
    $typeCustomProperties.InvokeMember("Delete", $binding::InvokeMethod,$null,$propertyObject2,$null)
    $typeCustomProperties.InvokeMember("add", $binding::InvokeMethod,$null,$customProperties,$arrayArgsC) |  Out-Null
    $typeCustomProperties.InvokeMember("add", $binding::InvokeMethod,$null,$customProperties,$arrayArgsD) |  Out-Null
} 
$document.Saved = $false;
$document.save();
$wordApplication.Quit();
 
And the full Script:

function Add-TemplateToDatabase
{
  Param(
    [String]$FileName,
    [String]$FilePath,
    [Int]$TemplateVersion,
    [Int]$OfficeUid  
  )  
  
  #Connect to the database
  $conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=sqlserver; Initial Catalog=database; Integrated Security=SSPI")
  $conn.Open();
  $newRecordId = -1;
  $a = $null
  try
  {
    #Create the SQL command, and assign it with the parameters
    $cmd = $conn.CreateCommand()
    $cmd.CommandType = "StoredProcedure"
    $cmd.CommandText ="spOI_AddNewTemplate"
    $cmd.Parameters.AddWithValue("@FileName", [String]$FileName) | out-null;
    $cmd.Parameters.AddWithValue("@FilePath", [String]$FilePath) | out-null;    
    $cmd.Parameters.Add("@CurrentVersion", [System.Data.SqlDbType]"Int") | out-null;
    $cmd.Parameters["@CurrentVersion"].Value = $TemplateVersion;      
    $cmd.Parameters.Add("@OfficeId", [System.Data.SqlDbType]"Int") | out-null;
    $cmd.Parameters["@OfficeId"].Value = $OfficeUid;  
    $cmd.Parameters.Add("@Uid", [System.Data.SqlDbType]"Int")  | out-null;
    $cmd.Parameters["@Uid"].Direction = [System.Data.ParameterDirection]"Output";
    #Execute the query
    $output = $cmd.ExecuteNonQuery();
    #Extract the new UID for the template from the output parameter, @Uid
    $op = $cmd.Parameters.Item("@Uid");
    $newRecordId = $op.SqlValue.Value;
    $cmd.Dispose();  
  }
  catch [System.Exception]{
    $a = $_;
  }
  finally{
    $conn.Close();  
  }  
  #Return the templates new database Uid
  return $newRecordId;
}

function Get-OfficeCode
{
  Param(
    [String]$FilePath
  )

  if($FilePath.EndsWith("\") -eq $false)
  {
    $FilePath = [String]::Format("{0}\", $FilePath);
  }  
  if($FilePath.Contains("UK"))
  {
    return 1;
  }
  if($FilePath.Contains("DE"))
  {
    return 2;
  }
  if($FilePath.Contains("FR"))
  {
    return 3;
  }  
}

function Update-Template
{
  Param(
    [Parameter(Mandatory=$true)]$application,
    [Parameter(Mandatory=$true)]$file    
  )
  
  $document = $null;
  switch ($application.ToString())
  {
    "Microsoft.Office.Interop.Excel.ApplicationClass"
      {$document = $application.Workbooks.Open($file.FullName);}
    "Microsoft.Office.Interop.PowerPoint.ApplicationClass"
      {$document = $application.Presentations.Open($file.FullName);}
    "Microsoft.Office.Interop.Word.ApplicationClass"
      {$document = $application.documents.open($file.FullName);}      
    default
      {return "Application not supported. Use either Excel.Application, PowerPoint.Application or Word.Application."; }
  }
  
  try{
    $binding = "System.Reflection.BindingFlags" -as [type];    
    $builtinProperties = $document.BuiltInDocumentProperties
    $pComments = "Comments"
    #After getting comments, we're going to clear out the comments property, so we set the value to ""
    $pCommentsValue = "";
    $pAuthor = "Author";
    $pAuthorValue = "Ince & Co";
    $pCompany = "Company";
    $pCompanyValue = "Ince & Co";
    [Array]$commentsArgs = $pComments
    [Array]$authorArgs = $pAuthor;    
    [Array]$companyArgs = $pCompany;    
    $builtinPropertiesType = $builtinProperties.GetType()
    #Get the Property items for each of the three builtin properties we're using
    $commentsProp = $builtinPropertiesType.InvokeMember("Item", $binding::GetProperty, $null, $builtinProperties, $commentsArgs)
    $authorProp = $builtinPropertiesType.InvokeMember("Item", $binding::GetProperty, $null, $builtinProperties, $authorArgs)
    $companyProp = $builtinPropertiesType.InvokeMember("Item", $binding::GetProperty, $null, $builtinProperties, $companyArgs)
    #Get the value of the Comments property, so we can check if it contains a version string.
    $commentsPropValue  = [System.__ComObject].InvokeMember("value",$binding::GetProperty,$null,$commentsProp,$null);
    #Set the default value for the templates version.
    $currentversion = 1;
    #If the comments section is not null or empty, look for the version string, strip off the "v" and cast the resulting string to an int.
    if(![String]::IsNullOrEmpty($commentsPropValue))
    {
      Write-Host "Document"$file.Name"comments property is: $commentsPropValue" -foregroundcolor Yellow;
      if($commentsPropValue.Trim().StartsWith("v"))
      {
        try
        {
          $currentversion = ([Int32]::Parse($commentsPropValue.Trim().SubString(1)))++;
          Write-Host "Document"$file.Name" had a custom version, $currentversion, that will be used." -foregroundcolor DarkYellow;
        }
        catch
        {
          Write-Host "Error in Document"$file.Name": couldn't convert version to an integer $commentsPropValue" -foregroundcolor Red;
          $currentversion = 1;
        }
      }
    }  
    
    #
    [Array]$commentsArgs = $pCommentsValue;
    [Array]$authorArgs = $pAuthorValue;
    [Array]$companyArgs = $pCompanyValue;    
    try
    {
      $builtinPropertiesType.InvokeMember("Value", $binding::SetProperty,$null, $commentsProp, $commentsArgs)
      $builtinPropertiesType.InvokeMember("Value", $binding::SetProperty,$null, $authorProp, $authorArgs)
      $builtinPropertiesType.InvokeMember("Value", $binding::SetProperty,$null, $companyProp, $companyArgs)
    }
    catch
    {    
    Write-Host "Error writing company and author properties";
    }
    
    #The templates folder stucture contains a directory that indicates a offices a template is used in. Here we get the office Location string derived from the template path
    $officecode = Get-OfficeCode -FilePath $file.Directory.FullName;
    $newUid = -1;    
    #Insert a record of the template into the database, and store the return value (which is the unique id of the template created when it was added to the database)
    $newUid = Add-TemplateToDatabase -FileName $file.Name -FilePath $file.Directory.FullName -TemplateVersion $currentversion -OfficeUid $officecode;
    Write-Host $newUid.ToString();
    $message = [String]::Format("Template {0} added to the database with UID: {1}", $file.Name, $newUid.ToString());
    Write-Host $message -foregroundcolor green;
    #return;
    
    #Now that we have the templates UID, and version, we can write them to two new custom properties of the documents
    #Get the custom properties
    $customProperties = $document.CustomDocumentProperties
    $typeCustomProperties = $customProperties.GetType()
    #Define the two new properties and their values
    $cpUid = "templateuid"
    $cpUidValue = [int]$newUid;
    $cpVersion = "templateversion"
    $cpVersionValue = $currentversion;    
    [array]$arrayArgsC = $cpUid,$false, 1, $cpUidValue;
    [array]$arrayArgsD = $cpVersion,$false, 1, $cpVersionValue    
    
    Try
    {
      #Add the properties to the template
      $typeCustomProperties.InvokeMember("add", $binding::InvokeMethod,$null,$customProperties,$arrayArgsC) | out-null
      $typeCustomProperties.InvokeMember("add", $binding::InvokeMethod,$null,$customProperties,$arrayArgsD) | out-null
    }
    Catch [system.exception]
    {
      #If the property already existed, get the property, delete the property, then readd it.
      $propertyObject1 = $typeCustomProperties.InvokeMember("Item", $binding::GetProperty,$null,$customProperties,$cpUid)
      $propertyObject2 = $typeCustomProperties.InvokeMember("Item", $binding::GetProperty,$null,$customProperties,$cpVersion)
      $typeCustomProperties.InvokeMember("Delete", $binding::InvokeMethod,$null,$propertyObject1,$null)
      $typeCustomProperties.InvokeMember("Delete", $binding::InvokeMethod,$null,$propertyObject2,$null)
      $typeCustomProperties.InvokeMember("add", $binding::InvokeMethod,$null,$customProperties,$arrayArgsC) |  Out-Null
      $typeCustomProperties.InvokeMember("add", $binding::InvokeMethod,$null,$customProperties,$arrayArgsD) |  Out-Null
    } 
    $document.Saved = $false;
    $document.save();  
    Write-Host "Document"$file.FullName"successfully updated, with version number $uid" -foregroundcolor Green;
  }
  catch
  {
    Write-Host "Error:$_" -ForegroundColor Red;
  }  
  $document.Close();
  $document = $null;
}

#Initialise all of the application objects
$wordApplication = New-Object -ComObject word.application
$powerpointApplication = New-Object -ComObject powerpoint.application
$excelApplication = New-Object -ComObject excel.application
#Get a list of templates, then pass them to the Update-Template function
$items = Get-ChildItem -Path C:\MyBigListOfTemplates -Recurse

#Loop through each of the items and call Update-Template, passing in the appropriate application object.
foreach ($i in $items){
  if($i.PSIsContainer){continue;}
  if($i.Name.ToLower().EndsWith("xlsx") -or $i.Name.ToLower().EndsWith("xltm") -or $i.Name.ToLower().EndsWith("xltx") -or $i.Name.ToLower().EndsWith("xls"))
  {
    Update-Template -application $excelApplication -file $i
  }
  if($i.Name.ToLower().EndsWith("potx") -or $i.Name.ToLower().EndsWith("pot"))
  {
    Update-Template -application $powerpointApplication -file $i
  }
  if($i.Name.ToLower().EndsWith("dotm") -or $i.Name.ToLower().EndsWith("dotx") -or $i.Name.ToLower().EndsWith("docx") -or $i.Name.ToLower().EndsWith("dot") -or $i.Name.ToLower().EndsWith("doc"))
  {
    Update-Template -application $wordApplication -file $i
  }
}
$wordApplication.Quit();
$powerpointApplication.Quit();
$excelApplication.Quit();


And out of interest / completeness, the stored procedure (that returns the Uid) looks like this:

CREATE PROCEDURE [dbo].[spOI_AddNewTemplate]   
  @FileName nchar(256), 
  @FilePath nchar(512),
  @CurrentVersion int = 1,
  @OfficeId int,
  @Uid int OUTPUT
AS
BEGIN  
  SET NOCOUNT ON;    
  Declare @tblOIInsertedId table (Id int not null)
  Insert into OfficeIntegrationTemplateVersions  
  ([FileName], FilePath, CurrentVersion,VersioningEnabled,Active,QuickPrintEnabled,FooterJpg,OfficeId)
  Output inserted.TemplateUid into @tblOIInsertedId(Id)
  values(@FileName,@FilePath,@CurrentVersion,1,1,0,0,@OfficeId)    
  Select @Uid = (Select Top(1) Id From @tblOIInsertedId) 
END