Using Power Automate to build and execute SharePoint REST API Queries
Tags: powerautomate, powerplatform, sharepoint, powerapps
Published: 2024-03-10 17:56:38

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:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cyt0wslygucpx652y8e9.png)

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



![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/urxed0mci4v3foaijeq4.png)

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:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x5ujpxipyaqyldbmcvnv.jpg)

When the flow in done running, we get the confirmation that the list exists and information about the list is returned.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r4mz0glptfgxup3zylbi.jpg)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0xsh22tjnodjt4up6dh8.png)

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


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/88r6d4ptvi08g8qf7ofr.png)

## 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.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/53bg1b65fqkf2ko258c5.png)
_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 :)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3t806481hz6g1rjnd5n9.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6p1d6q3wnlvkw1o2jnwf.png)

## 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.