In the previous blog post, we learned how to use the Send HTTP Request to SharePoint action in Power Automate to get items from a SharePoint list. In this post, we will see how to use the same action to update an existing single line of text column in a list item.

This Flow pattern is an edge case. You can usually use the standard Update item action, but if you encounter the “The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold” error, you need to use the Send HTTP Request to SharePoint action.

Configuration

Our Send an HTTP request to SharePoint action will have the following configuration:

1. Site Address is the URL of the site we are sending the command to.
2. Method is the action we are intending to take with the command and has the following options: DELETE, GET, PATCH, POST and PUT. We will use POST to perform the update.
3. Uri is the endpoint where our actions take place, like a site, library, list, document, or item.
4. Headers provides additional information for the request, such as the data format, the HTTP method and concurrency control.
5. Body are the details of the action that will be taken in SharePoint like the name of a new library, content type of a new document or the value of a list column.

Demo

Our Flow uses a Manually trigger a flow trigger and has the following configuration:

1. Add an Initialize variable action and set Name to “varString”; Type to “String” and Value as desired.
   
   Context: This action is optional as the command we issue in the Body of the Send an HTTP request to SharePoint can accept static and dynamic text. Using variables for string manipulation in Flows can aid in development, testing, debugging and supportability.
   
   Tip: Keep the Value string length at or under the maximum column character length or you will get “BadGateway” & “Invalid text value. A text field contains invalid data. Please check the value and try again” errors. More on this later in the post.
   
2. 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.
3. Add an 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.
   
4. Save and test your Flow.
5. Click Show raw outputs in the OUTPUTS section of the Compose action and copy the output.
   
6. 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.
7. Save and test your Flow.
8. 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.
       
       Tip: See JSON Light support in REST SharePoint API released for OData payload levels explanations and examples.
     • “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’}, ‘SingleLineText’: ‘varString‘ }
     
     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.
     • ‘SingleLineText’: ‘varString’ is the “internal” column name and new value that we are updating.
       
9. Save and test your Flow.  If everything works as expected, you should see that the Single Line of Text columns has been updated to string stored in our string variable.
   
   

Earlier, I noted that using a string longer than the column width results in a “BadGateway” / “Invalid text value” error. To handle this, either truncate the string to fit or check its length before updating.

To truncate the string, add a new variable, set the value to “Expression” and use the substring (string, start index, length) function with the following breakdown:

• String is the text that we want to reduce in length, in this example it is stored in the variable “varString”.
• Start index is the where the function will start counting the length of the string. The starting number is 0.
• Length is how many characters we will include in the function and should match the underlying column size.

Update the Send HTTP request to SharePoint to use the new variable “varNewString”.

Checking the length of the string prior to attempting the update gives us an opportunity intervene in the user experience and prompt to resubmit with a smaller string. To do so, add a new Condition action after the existing Parse JSON action and configure as follows:

• Set Choose a value to an “Expression”; set the expression to length(variables(‘varString’)); Where variables(‘varString’) comes from “Dynamic content”
  
  
• Move the existing For each loop that contains our Send an HTTP request to SharePoint – Update item action to the “True” branch of the condition.
  
• Optionally, add a message action to the user in the “False” branch to manage the user experience. In the example image below, I am using the Teams Post message in a chat or channel action.
  

In this blog post, we learned how to use the Send HTTP Request to SharePoint action in Power Automate to update an existing single line of text column in a SharePoint list item. This is a powerful and flexible action that can be used to perform various operations on SharePoint data using the REST API. In the next blog post, we will explore how to use this action to update a choice column in a SharePoint list.

Thanks for reading!

NY

• Microsoft Lists
• Power Automate
• SharePoint