In previous posts, we explored using the Send HTTP Request to SharePoint action in Power Automate for tasks like retrieving items with an OData filter query and updating a text column. Now, we’ll discuss how to update choice columns in SharePoint Lists using this action. Although the standard Update item action usually works, you’ll need the Send HTTP Request to SharePoint action if you encounter the “The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold” error. 

Configuration

The configuration for updating a choice column is similar to what we’ve seen before but requires attention to detail in the request body.

1. Site Address: This is the URL of the site to which you are sending the command.
2. Method: We will use POST to perform the update.
3. Uri: This is the endpoint where our actions take place, such as a site, library, list, document, or item.
4. Headers: These provide additional information for the request, such as the data format, the HTTP method, and concurrency control.
5. Body: These are the details of the action that will be taken in SharePoint, such as the name of a new library, the content type of a new document, or the value of a list column.

Demo

Our Flow employs a Manually trigger a flow trigger and is configured as follows:

1. Add an Initialize variable action, setting Name to “varChoice” and Type to “String”. Leave Value blank for now.
   
   Context: This action is optional but recommended to help with dev, support and so on.
   
2. Add an Set variable action, set Name to “varChoice” and Value to match the desired choice column value(s) in the following format:
   
   “Value 1” when the choice column only allows a single selection
   “Value 1”, “Value 2”, “Value 3” when the choice column allows multiple selections
   
   Note: I’m using an array of possible values selected at random in my Flow for testing purposes. Work is required to properly construct the string containing your choices. The Format data by examples feature in Power Automate is a great way of doing this!
3. Add a Send an HTTP request to SharePoint action.
   
   The action is configured as follows:
   • Site Address set to my target site URL.
   • Method set to Get.
   • Uri set to: _api/web/lists/getbytitle(‘Demo List’)/items?Status eq ‘New’.
     
     The Uri is broken down as follows:
     • _api/web connects to the site specified in the Site Address.
     • /lists queries all lists on the site.
     • /getbytitle(‘Demo List’) filters to a specific list name “Demo List” and retrieves its metadata. Change this value to match your list name.
       
       Tip: Use the list’s Name instead of its value in the URL, as they might not match. See example image below.
       
     • /items queries all items in the list.
     • ?Status eq ‘New’ filters all items with the Status column equal to “New”.
   • Headers set to: {“Accept”: “application/json; odata=nometadata”}.
     
     The Header is broken down as follows:
     • application/json will treat our Flow as a JSON based application.
     • odata=nometadata will only include the necessary metadata in the JSON output.
4. Add a Compose action and set the Inputs to “Body” from the previous Send an HTTP request to SharePoint – Get items action.
   
   The Compose action captures the output from the Send HTTP request to SharePoint action. When combined with a Parse JSON action we will have data that we can reference later in our Flow.
   
5. Save and test your Flow.
6. Click Show raw outputs in the OUTPUTS section of the Compose action and copy the output.
   
7. Add a Parse JSON action and configure as follows:
   • Set Content to “Outputs” from the previous Compose – Get items action.
   • Click Use sample payload to generate schema link.
   • Paste the copied OUTPUTS from the previous Compose action into the “Enter or past a sample JSON payload” window and then click Done.
     
     In the image above we can see our list schema and the attributes that will be available to use from this point forward in the Flow.
8. Save and test your Flow.
9. Add another Send an HTTP request to SharePoint action and set the configuration as follows:
   • Site Address set to my target site URL.
   • Method set to POST.
   • Uri set to: _api/web/lists/getbytitle(‘Demo List’)/items(Body Id) from the previous Parse JSON action.
     
     Note 1: Our Send HTTP request to SharePoint will be placed inside of a For each loop because “Body Id” is coming from an array generated in our first Send HTTP request to SharePoint that performed our get items action.
     
     Note 2: Selecting “Body Id” will change to Id after adding it reflecting the underlying column name. This is normal behavior.
     
     The Uri is broken down as follows:
     • _api/web connects to the site specified in the Site Address.
     • /lists queries all lists on the site.
     • /getbytitle(‘Demo List’) filters to a specific list name “Demo List” and retrieves its metadata.
     • /items(Body Id) queries all items in the list and filters to the matching Id column.
       
   • Headers to:
     {
     “Content-Type”: “application/json;odata=verbose”,
     “X-HTTP-Method”: “MERGE”,
     “IF-MATCH”: “*”
     }
     
     Headers are broken down as follows:
     • “Content-Type”: “application/json;odata=verbose” sets the returning payload to JSON and includes all associated metadata.
     • “X-HTTP-Method”: “MERGE” mimics a MERGE method if a network proxy or firewall tries to block the POST command.
     • “IF-MATCH”: “*” will stop command execution if the source data changes during the action.
   • Body to:
     { ‘__metadata’: { ‘type’: ‘SP.Data.DemoListListItem’ }, ‘Status’: { ‘__metadata’: { ‘type’: ‘Collection(Edm.String)’ }, ‘results’: [varChoice] } }
     
     Body is broken down as follows:
     • ‘__metadata’: {‘type’:’SP.Data.DemoListListItem’}
       • SP.Data is the method we are invoking in the REST API.
       • DemoList is the “internal” list name that we are updating.
         
         Tip: The internal list name can be found using a similar SharePoint REST API command from a web browser:
         
         https://normyoung.sharepoint.com/sites/demo-site/_api/web/lists/getbytitle(‘Demo List’)
         
         Look for <d:ListItemEntityTypeFullName> in the results.
         
       • ListItem sends the command to the list item resource type.
       • ‘Status’: { ‘__metadata’: { ‘type’: ‘Collection(Edm.String)’ }, ‘results’: [varChoice] } is the “internal” column name and new value that we are updating.
         
10. Save and test your Flow. If everything works as expected, you should see that the Choice column has been updated to choice(s) stored in our string variable.
    

Handling Errors

To manage potential errors, such as NULL choice values, consider adding a Condition action inside of the For each loop, ensuring that varChoice “is not equal to” NULL. Place the Send HTTP request to SharePoint in the True branch of the check Condition.

This article explained how to use the Send HTTP Request to SharePoint action in Power Automate to update a choice column in a SharePoint List item. This action is versatile for various operations on SharePoint data via the REST API. Our next post will cover updating a person column in a SharePoint list.

Thanks for reading!
NY

• Microsoft Lists
• Power Automate
• SharePoint