Did you know that the standard SharePoint actions in Power Automate, such as Get items and Update item, can only manage a maximum of 12 lookup columns? Also, did you know that column types like Image can’t be utilized with the Create item and Update item actions?

In such cases, the Send HTTP Request to SharePoint action is handy to select, add, modify, and delete list data. It’s more complex than other SharePoint actions in Power Automate as it requires building REST commands like a developer would in a language like C#. In this blog series, I hope to make learning easier with simple explanations for non-developers and by giving clear demonstrations.

What is a HTTP request?  It allows us to build REST API commands for SharePoint. REST, short for Representational State Transfer, enables the remote manipulation of SharePoint data via REST-compatible technologies like Power Automate. It allows us to conduct Create, Read, Update, and Delete (CRUD) actions in our Flows using REST web protocols and Open Data Protocol (OData) syntax.

Real World Scenario

When a SharePoint list contains 12 person/lookup columns, the Get items action operates without issue. But if you add a 13th person/lookup column, an error occurs because the lookup threshold is surpassed. To overcome this limit, the Send an HTTP request to SharePoint action can be employed.

Error message: The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold.

Configuration

The Send an HTTP request to SharePoint action has five configuration sections used to complete the REST command.

1. Site Address this is the URL of the site that you are sending the command to.
2. Method is the action we are intending to take with the command. There are five methods to choose from:
   • DELETE will remove existing objects like a library, document or list item.
   • GET retrieves object information. We use GET to select the information from existing objects like document library metadata or folder contents.
   • PATCH will update an existing object. I’ve never used this method in my Flows and tend to use POST to perform updates.
   • POST will create new and update existing objects like a list item or updating document metadata.
   • PUT will update an existing object. Again, I never used this method and stick with POST. I’m sure there are valid use cases, but I’ve not come across them.
3. Uri (Uniform Resource Identifiers), which are the specific endpoints where our actions take place, like a site, library, list, document, or item.
4. Headers are an extra layer of information for each API request you make, containing meta-data related to the request and response. They consist of key–value pairs in plain-text string format divided by a colon.
   
   In the sample image below, a POST command is executed with the following headers and translate to:
   • Our application will communicate using JSON.
   • If a network proxy or firewall tries to block the POST command treat the action as a MERGE to bypass the network blocking.
   • If the source data changes during the action execution don’t execute the command.
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

In this very simple demo, I am replicating a Get items action that includes a basic OData filter query. I am using the Manually trigger a flow trigger followed by an Send an HTTP request to SharePoint action. The action is configured as follows:

1. Site Address set to my target site URL.
2. Method set to Get.
3. 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.
     
     Tip: Use the list’s Name instead of its value in the URL, as they might not match.
     
     
   • /items queries all items in the list.
   • ?Status eq ‘New’ filter all items with the Status column equal to “New”.
     
     Tip: The Uri can be manually executed in an authentication web browser session to SharePoint. Here is an example: https://contoso.sharepoint.com/sites/demo-site/_api/web/lists/getbytitle(‘Demo%20List’)/items?Status eq ‘New’
     
4. Headers set to: { “Accept”: “application/json; odata=nometadata” }
   • Application/json sets returning data or payload to JSON.
   • odata=nometadata remove all but the necessary payload information.
5. Save and test your Flow. If all goes well, your action will return a status code of 200 and the outputs will include data from yout list.

We can utilize the Compose and Parse JSON actions to make use of the output from the Send HTTP Request in SharePoint, specifically for the Update item action.

While it might appear too complex for a simple task, once the list exceeds 12 columns, this approach is necessary. In future blog entries, we’ll explore updating columns via the SharePoint REST API.

Thanks for reading!

NY

• Microsoft Lists
• Power Automate
• SharePoint