SharePoint 2013, updating lots of records, by script?

Question

I have a SharePoint list that has lots of columns and a priority column.

Now it used to be a high - medium - low column.

However last year they changed the ideas behind that, and we have now 1,2,3,4 as priority.

Its all working nice, but when I now filter on items the old values still appear. They should translate high=1 and medium = 2 and low = 4

The problem there are a lot of records, I need a way to automatically update those values. And only change them if they have the old high-medium-low status.

Update some code, as related to powershell scripting this Some how this code doesnt work i dont see whats wrong with it. Its just a first step to only show something so i see the lists is traversed

Add-PSSnapin Microsoft.SharePoint.PowerShell 
$webURL = "https://infodesk.company.com"
$listName = "Tickets"
$web = Get-SPWeb -site $webURL
$list = $web.Lists[$listName]
$items = $list.items
Foreach($item in $items)
{
  Echo $item
}

Answer 1

Using PowerShell it would go something like this, assuming you're replacing value in same field.

If you need to update other field with the new value, just update the field name inside the IF statements:

Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue
$webURL = "https://site.company.com"
$listName = "yourlistname"
$web = Get-SPWeb $webURL
$list = $web.Lists[$listName]
$items = $list.items

Foreach($item in $items)
{
    if($item["PriorityColumn"] -eq "high")
    {
        $item["PriorityColumn"] = "1"    
    }    
    elseif($item["PriorityColumn"] -eq "medium")
    {
        $item["PriorityColumn"] = "2"
    }
    elseif($item["PriorityColumn"] -eq "low")
    {
        $item["PriorityColumn"] = "4"
    }    

    $item.SystemUpdate($false)
}

$web.Dispose()

Answer 2

Thank to @Jussi palo, I got the basic idea for the script. I eventually ended up using the script below for some unknown reason the selection of items didn't work (maybe because it wasn't a standard SharePoint column?, well I can only guess.

if((Get-PSSnapin | Where {$_.Name -eq "Microsoft.SharePoint.PowerShell"}) -eq $null) {
Add-PSSnapin Microsoft.SharePoint.PowerShell;
}

#disable event firing
$myAss =    [Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint");
$type = $myAss.GetType("Microsoft.SharePoint.SPEventManager");
$prop = $type.GetProperty([string]"EventFiringDisabled",[System.Reflection.BindingFlags] ([System.Reflection.BindingFlags]::NonPublic -bor [System.Reflection.BindingFlags]::Static));
$prop.SetValue($null, $true, $null);

$sourceWebURL = "https://infodesk.company.com/IT"
$sourceListName = "Tickets"

$spSourceWeb = Get-SPWeb $sourceWebURL
$spSourceList = $spSourceWeb.Lists[$sourceListName]

$spSourceItems = $spSourceList.Items | where {$_['Priority'] -eq 'NORMAL'}

[int]$count = 0
ForEach($item in $spSourceItems)
{
  $status = $item['Priority']
  if($status = "NORMAL")
  {
    $count++
    Write-Host $count, $item['Priority'],$item['Title']

    #Unmark below to activate this script
    #$item["Priority"] = "3"
    #$item.SystemUpdate($false)  #this updates without changing modified
    #Write-Host $item['Priority'],$item['Title']
  }
}
# enable event firing
$prop.SetValue($null, $false, $null);

Answer 3

Below is the sample code to change the PriorityColumn (Your columns name) values from high to 1.

You can do same for other values like medium & low for by changing the parameters in query.

<script type="text/javascript">
    var collListItemToBeUpdated = "";
    var listItemToBeUpdated = "";
    function getItemsToBeUpdated() <--- this function called on button click to get ID's
    {
         var clientContext = new SP.ClientContext.get_current();
         var oList = clientContext.get_web().get_lists().getByTitle('rohit_list1');
         var camlQuery = new SP.CamlQuery();
         camlQuery.set_viewXml('<View><Query><Where>' +
            '<FieldRef Name=\'PriorityColumn\'/><Value Type=\'text\'>high</Value>' +
            '</Where></Query></View>');
         collListItemToBeUpdated = oList.getItems(camlQuery);
         clientContext.load(collListItemToBeUpdated);
         clientContext.executeQueryAsync(getItemsToBeUpdatedSuccess, getItemsToBeUpdatedFailed);
    }
    function getItemsToBeUpdatedSuccess()
    {
         ListItemToBeUpdated= collListItemToBeUpdated.getEnumerator();
         updateMultipleListItems();
    }
    function getItemsToBeUpdatedFailed(sender, args)
    {
        alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
    }

    function updateMultipleListItems() <--- this function called on successfully getting ID's
    {   
        var itemArray = [];
        var clientContext = SP.ClientContext.get_current();
        var oList = clientContext.get_web().get_lists().getByTitle('rohit_list1');

         while(ListItemToBeUpdated.moveNext())
         {
            var oItem = ListItemToBeUpdated.get_current();
            var oListItem = oList.getItemById(oItem.get_id());
            oListItem.set_item('PriorityColumn', '1');  
            oListItem.update();
            itemArray.push(oListItem);
            clientContext.load(itemArray[itemArray.length-1]);
         }  
        clientContext.executeQueryAsync(updateMultipleListItemsSuccess, updateMultipleListItemsFailed);
    }
    function updateMultipleListItemsSuccess() 
    {    
        alert('Items Updated');
    }

    function updateMultipleListItemsFailed(sender, args) 
    {    
        alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
    }
    </script>

Answer 4

Why do you want to use a script? Are there hundreds or thousands record that you need to update? If the amount of items is not that big it is a lot easier, especially of you are not a developer, to do it manually using Excel. Try doing the following:

• Create a view that only shows items with old priority values
• Export list the Excel
• Perform search and replace in your excel sheet
• Copy the updated values to SharePoint using the Quick Edit mode (also using the view you used for exporting). You can copy only the Priority column so other columns are not updated.