Using Power Automate to build and execute SharePoint REST API Queries

While the buzz around Copilot and its transformative potential continues to grow, I believe it’s still important not to forget the core capabilities that might not grab the headlines in the same way today. So, in today’s post I will show how we can interact with the data in SharePoint by using REST API to perform complex automation and specific tasks not covered by standard Power Automate actions.

In Power Automate we can use “Send and HTTP request to SharePoint” in scenarios where you need to perform CRUD operations(create, read, update and delete) on SharePoint that are not available in the SharePoint connector such as apply retention policies, create or manage SharePoint groups, automate tasks in Term store, automate site provisioning with specific configurations, etc.

Method

We can use these methods (REST endpoints) to perform CRUD operations against SharePoint:

Create: Select POST to create resources as lists, sites, etc.
Read: Select GET to return a resource
Update: Select PUT and PATCH to modify existing SharePoint objects.
Delete: Select DELETE to remove existing SharePoint objects.

Uri

We need to construct the URI endpoint that point to the SharePoint resource we want to access:
To access a specific site collection start to build the endpoint with:

https://{site_url}/_api/site

To access a specific site, use the following:

https://{site_url}/_api/web

… And specify parameters

Note: To get the GUID of a SharePoint list or library, navigate to list settings and in the URL you will find the GUID as below:

When the flow in done running, we get the confirmation that the list exists and information about the list is returned.

To make the outputs selectable, we can use Parse JSON action

Two use case scenarios

• Use Case 1: Advanced List Management

Imagine you need to update a SharePoint list with more than 5,000 items, but you encounter limitations with the standard “Update item” or “Get items” actions due to item view threshold limits.

Solution: By using “Send an HTTP request to SharePoint,” you can construct a REST API call to update list items based on complex queries, bypassing the threshold limit and efficiently managing large lists.

Note: Schema was generated from the previous run by copying the HTTP response

• Use Case 2: Advanced List Management
  If we are dealing with file versioning, such as retrieving versions of a file or restoring a previous version, the URI might look like this:

_api/web/GetFileByServerRelativeUrl('/sites/YourSite/Shared Documents/yourfile.docx')/versions

_api/web/GetFileByServerRelativeUrl('/sites/YourSite/Shared Documents/yourfile.docx')/versions/restoreByLabel(versionLabel)


BONUS

While I was writing this article, I noticed something new among the actions. I’ll try it out and come back with my findings 🙂

Conclusion

“Send an HTTP request to SharePoint” is like a treasure chest full of more advanced capabilities that you should use when you can’t perform tasks on SharePoint with the standard actions available.