0

We are using this below script to add new values to our choice column, but need the option to sort the SharePoint list column choice values ascending. There are over 150 values, and just putting new values on the end/bottom of the dropdown isn't user friendly.

Connect-PnPOnline -Url "https://MySite/sites/Sandbox" -UseWebLogin
$field = Get-PnPField -List aaa-f31e-4cc0-b193-bbb -Identity Schedule
[xml]$schemaXml = $field.SchemaXml

$varNewChoice = $schemaXml.CreateElement("CHOICE")
$varNewChoice.InnerText = "Thursday"
$schemaXml.Field.CHOICES.AppendChild($varNewChoice)

Set-PnPField -List aaa-f31e-4cc0-b193-bbb -Identity Schedule -Values @{SchemaXml=$schemaXml.OuterXml}

*** Update >> Was able to get this code to both add value and sort:

Connect-PnPOnline -Url "https://ourtenant.sharepoint.com/sites/Sandbox" -UseWebLogin
$field = Get-PnPField -List "Space Request Form" -Identity "Schedule"
$ctx = Get-PnPContext

$fieldChoice = [Microsoft.SharePoint.Client.ClientContext].GetMethod("CastTo").MakeGenericMethod([Microsoft.SharePoint.Client.FieldChoice]).Invoke($ctx, $field)

$ctx.Load($fieldChoice)
Invoke-PnPQuery

$choices = $fieldChoice.Choices
$choices += "Bentley"

$fieldChoice.Choices = $choices | Sort-Object 

$fieldChoice.UpdateAndPushChanges($true)
Invoke-PnPQuery

1 Answer 1

0

In the order, we enter the value in choice column, those get rendered that way. So, if you have more items to the choice column, for me the workaround would be - copy the previous choice column value to a excel and add your new entry over there, then apply sorting that excel column and finally copy the entire column and paste into the actual choice column in SharePoint (this will replace the the previous choice entries).

How to sort column value alphabetically in excel, refer to the below article :

How to alphabetize in Excel: sort columns and rows A-Z or Z-A

How to sort SharePoint choice column value using the PnP PowerShell?

Using the below code - we can sort the SharePoint choice column value.

#Connect to PNP Online
Connect-PnPOnline -Url "yoursiteurl"

$field = Get-PnPField -List "yourlist" -Identity "mychoice"

$ctx = Get-PnPContext

$fieldChoice = [Microsoft.SharePoint.Client.ClientContext].GetMethod("CastTo").MakeGenericMethod([Microsoft.SharePoint.Client.FieldChoice]).Invoke($ctx, $field)

$ctx.Load($fieldChoice)
Invoke-PnPQuery

$choices = $fieldChoice.Choices
$choices += "below"

$fieldChoice.Choices = $choices | Sort-Object 

$fieldChoice.UpdateAndPushChanges($true)
Invoke-PnPQuery

Reference:

PowerShell PnP sort SharePoint choice column

PowerShell PnP sort SharePoint choice column

2
  • Thank you for the reply. We're actually using a FLOW and it takes 22 minutes sometimes, so, now have this PnP to both add and sort values. Jun 19, 2020 at 2:36
  • Ok, got it, Please see the updated answer - it might be helpful.
    – SP 2022
    Jun 19, 2020 at 6:24

Your Answer

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

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