Updating a column in all rows in a Sharepoint List the easiest way

By | December 22, 2015

I have a large list in SharePoint where I was tasked to update a certain numeric field to add a certain amount based on a certain condition.  This made me think as I never had done this before, first thing came into mind was doing it in datasheet view with calculated field then copying and pasting the values back to the column like excel but that solution feels inelegant so I decided to do it in Powershell as it can give me more flexibility.

Lets say you have a list of all prices where you need to add an amount to the current price based on category, if it’s a service then you just add lets say $10.00 and if it’s a product you will add $20.00.  With this we also want to update only non expired prices.  Doing this the datasheet way would entail several filter definitions but using Powershell it would be straightforward.  So this is how I made it.

$web= Get-SPWeb  -Identity "http://YourSharepointSite/Prices"; 
$list = $web.Lists["PriceList"];     
$i = 0;
Foreach($item in $list.Items)
{
    $price = $item['Price'];
    if ((get-date $item['Expiry']) -gt (get-date 2015-12-31))
    {
        if ($item['Category'] -eq "Service")
        {
            $newPrice = $price + 10.00;
        }
        else
        {
            $newPrice = $price + 20.00;
        }
        
        $item['Price'] = $newPrice;
        $item.Update();
        
        Write-Host "$($item[ItemName']) --> $($price) | $($newPrice) | $($item['Category']) - $($item['Expiry'])";
        $i++;
    }
}
Write-Host "Total Items:" $i;
Write-Host "Process Completed";

So after coding this save it and run the saved file using the SharePoint 2103 Management Shell.

SP Management Shell

So for this example we just saved it on our desktop and the filename is YourSavedFile.ps1

PS C:\windows\system32> C:\Users\Raymund\Desktop\YourSavedFile.ps1
Recommended

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.