0

Is there a way through using a piece of code to update all the specific values of a column of SharePoint online to something else?

I am looking for an equivalent of the below SQL code for Sharepoint online

udate list1 set att1='Region2' where att1 contains 'RegionB' and att2='True' --Corected based on the feedback

Do we need to have a different piece of code for choice/multichoice column?

Does the code need to iterate through every single record if it is a multichoice option? I guess it only needs to replace one of the selected options which is the one that we are interested and should leave the rest intact?

If there is a way, I assume before doing this operation we need to turn all the flows of this list which are triggered by update/insert off, If this correct, Is there a way to avoid or shut down these flows through code or turning them off should be done manually?

1 Answer 1

0

For update choice Column with CSOM under condition, you can refer to this thread. It still works for current SharePoint Online and it runs without any issue in my end.

For multichoice column, you can refer to the following script I modify a bit on

#Login Information for script
$User = "[email protected]"
$Pass = "<PWD>"

$WebUrl = "https://domain.sharepoint.com/sites/SiteName"

#Connect to SharePoint Online service
Write-Host "Logging into SharePoint online service." -ForegroundColor Green

$Context = New-Object Microsoft.SharePoint.Client.ClientContext($WebUrl)
$Context.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($User, (ConvertTo-SecureString $Pass -AsPlainText -Force))

#Get the Necessary List
Write-Host "Getting the required list." -ForegroundColor Green
$List = $Context.Web.Lists.GetByTitle("<List Name>")

$Query = [Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery(100); 
$Items = $List.GetItems($Query);

$Context.Load($Items);
$Context.ExecuteQuery();
$new=@("Choice Value1","Choice Value2")
#Edit existing list items
foreach($item in $Items)
{
    if($item["<Field Name to compare>"] -eq "Value")
    {
        Write-Host "Changing export flags to Complete for Id=$($item.Id)." -ForegroundColor Green
        $item["<Name for the field need to update>"] = $new;
        $item.Update()
        $Context.ExecuteQuery();
    }
   #after the above code, if you have approval process
   # in place and you are sure that you are the only person who has changed 
   #this record, you can apply the below code and update the approval 
   #from Pending to Approved
  if($item["_ModerationStatus"] -eq "2")
   {
       Write-Host "Changing export flags to Complete for Id=$($item.Id)." -ForegroundColor Green
       $item["_ModerationStatus"] = "0";
       Write-Host 
       $item.Update()
       $Context.ExecuteQuery();
   }
}

And if you are worried about flows for auto-updating, just turn off all the associated flows on the current list.

3
  • for choice it works fine, thx a lot, but how can I iterate through a multichoice? The way you modified the choice to accommodate multichoice does not consider this. For example, I cannot updaye only "choice A" to "Choice a" of a "multichoice column" with two options "choice A, choice 2" by following this way? The only way, based on your approach is to consider all the possible permutations and put OR between them which is not efficient Apr 14, 2021 at 16:43
  • @MohsenSichani There is a variable $new. You can find the line $new=@("Choice Value1","Choice Value2"). In the parentheses you can add all the required choice values. Are you saying you want to put a multi choice column in the if condition?
    – Jerry_MSFT
    Apr 15, 2021 at 1:28
  • Yes, exactly, I should have been written "att1 contains 'RegionB'" rather than att1="RegionB". This line "$item["<Name for the field need to update>"] = $new;" replace all the selected item(s) with $new, but we do not know what is the value of $new before the for loop. Say if I have 5 options in a column, then I do not know what options a row has,so I cannot assign $new before iterating through each item of this row. if it has 'RegionB' then I only need to replace RegionB with something else, the rest of the selection options must remain intact. Hope it is clear now. Apr 15, 2021 at 2:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.