Best practices when querying SharePoint lists/libraries

1) Golden rule - Never iterate through the whole list to filter/check-on couple of items in the list. List operations are heavy. Filter the required items needed maybe through a CAML query.

2) Use the SPQuery class if your objective is to get items from a single list

3) In your CAML query set ViewFields to filter the fields which you need. In the same way you can use SPList.GetItemByIdSelectedFields() method to get selected fields. Setting ViewFields makes the query more efficient

4) Use the SPSiteDataQuery class to get results from multiple lists. Here you can specify the web and lists which you are targeting at.

Example:

SPSiteDataQuery myQuery = new SPSiteDataQuery();
myQuery.query = "Your CAML query goes here";
myQuery.Webs = @"<Webs Scope=""SiteCollection"" />"; //Returns data from all webs in the current site collection
//myQuery.Webs = @"<Webs Scope=""Recursive"" />"; // will return from current web and its child webs

myQuery.Lists = @"<Lists ServerTemplate=""101"" />"; //101 refers to document libraries
myQuery.ViewFields =  @"
                                            <FieldRef Name=""Field One"" />
                                            <FieldRef Name=""Field Two"" />
                                           ";

Saving lookup meta data values to library using Powershell - SharePoint

When you take an item in a list, the Lookup fields store its value in the following format
id;#string

when programmatically adding meta data to a list's column the lookup value has to be built before assigning to the item field.

In the below example I refer one lookup, get its value/string. Using the string, do a search for the lookup list item id in the lookup list. Then create the new lookup field. Finally update item


  $myLookupItem = [Microsoft.SharePoint.SPFieldLookupValue]($_["FieldOne_InternalName"])
  $newLookupId = GetLookupId $myLookupItem.LookupValue

  $newLookupItem = New-Object Microsoft.SharePoint.SPFieldLookupValue($newLookupId  ,$myLookupItem.LookupValue)

$_["FieldTwo_InternalName"] = $newLookupItem

$_.Update()
$_.File.CheckIn(" FieldTwo value updated", 1)

$_ is explained in an earlier post

Query SharePoint Libraries using CAML query and Powershell

$web = Get-SPWeb $siteUrl
$list = $web.Lists[$listName]

1) Query items in list

if($list -ne $null)
{

$caml = '<OrderBy><FieldRef Name="ID" Ascending="True" /></OrderBy><Where><And> <Geq> <FieldRef Name="ID" /><Value Type="Number">{0}</Value> </Geq><Leq><FieldRef Name="ID" /><Value Type="Number">{1}</Value></Leq> </And> </Where> ' -f $startValue,$endValue

$myQuery = new-object Microsoft.SharePoint.SPQuery
$myQuery.Query = $caml

$filteredItems = $list.GetItems($myQuery)

$filteredItems | ForEach-Object {

      #Assign meta data to variables
      $variableOne = $_["MetaDataOne_InternalName"]
}

}

2) Remove specific items from list

if($list -ne $null)
{

$caml='<Where> <Or> <Or> <Or> <Eq> <FieldRef Name="Title" /><Value Type="Text">{0}</Value> </Eq> <Eq> <FieldRef Name="Title" /><Value Type="Text">{1}</Value> </Eq> </Or> <Eq> <FieldRef Name="Title" /><Value Type="Text">{2}</Value> </Eq> </Or> <Eq> <FieldRef Name="Title" /><Value Type="Text">{3}</Value> </Eq> </Or> </Where> ' -f $itemOne, $itemTwo, $itemThree, $itemFour

$query=new-object Microsoft.SharePoint.SPQuery
$query.Query=$caml
$col=$list.GetItems($query)

Write-Host 'Number of items removed: ' $col.Count

$col | % {$list.GetItemById($_.Id).Delete()}

}

$web.Dispose()

for more info about CAML query syntax visit here

Access User Profile Service using Powershell

function GetInfo([string]$userAccount)
{

 $mailAttribute = "WorkEmail"

 $mail = ""

 $serviceContext = Get-SPServiceContext -Site $siteUrl
 $profileManager =  New-Object  Microsoft.Office.Server.UserProfiles.UserProfileManager($serviceContext);

if($profileManager.UserExists($userAccount))
{
        $userProfile = $profileManager.GetUserProfile($userAccount)
        $mail = $userProfile[$mailAttribute].Value
}

return $mail
}

Get SharePoint user group and user details using Powershell

$SPWebCollection = $SPSite.AllWebs

$rootSite = $SPSite.RootWeb

foreach($SPGroup in $rootSite.Groups)
{
foreach($user in $SPGroup.users)
{
$users = new-object psobject
$users | add-member noteproperty -name "Group Name" -value $SPGroup.Name
$users | add-member noteproperty -name "User" -value $user
$users | add-member noteproperty -name "Display Name" -value $user.DisplayName

$email = GetInfo  $user
$users | add-member noteproperty -name "Work Email" -value $email
$combinedusers =[Array]$combinedusers + $users
}
}

        #Create CSV file
$filelocation=$filePath+"\"+$rootSite+".csv"
$combinedUsers | export-csv -path $filelocation -notype
$combinedUsers = $null

Install and Configure Workflow Manager

The installation steps are as follows

Configuration