In this blog post I am going to show how it is possible to add and remove users to a SharePoint site using Microsoft Lists and Power Automate. I’ll be using the Send an HTTP request to SharePoint action throughout the Flow. “Send an HTTP request to SharePoint” constructs and executes SharePoint REST API calls. I’m not a developer, so a lot of my HTTP request calls are the result of “Googling” and banging my head against the wall.
Our site, called “SharePoint Demo”, has a List called “Users” with the following column definitions:
| Column | Type |
| User | Person. |
| Action | Choice with “Grant” and “Revoke” as the lookup values. |
| Group | Choice with “Visitors”, “Members” and “Owners” as the lookup values. This coincides with the default site security groups and can customized as you see fit. |
| SiteName | Single line of text. |
At a high-level our Flow triggers from new or edits to the Users list and either adds or removed the user from the SharePoint security group. Security group is based on a combination of “SiteName” and “Group”. For example “SharePoint Demo Visitors”.
Create a new Flow from the Users list > Automate > Power Automate > See your Flows > Create new > Automated from blank. Provide a Flow name, i.e. “SharePoint – Add/Remove Users”, select the SharePoint “When an item is created or modified” trigger and click “Create”.
Set the trigger to your target site and list name.
Insert a new “Initialize variable” step. Set Name to “varSiteName”, Type to “String” and Value to “SiteName Value” from the “When an item is created or modified” trigger.
Insert a new “Send an HTTP request to SharePoint” step. Set:
- Site Address to your target site.
- Method to “GET”.
- Uri to
"_api/web/SiteGroups?$filter=LoginName eq '@{concat(variables('varSiteName'), ' ', triggerOutputs()?['body/Group/Value'])}'&$select=Id&$top=1". - Headers to
{ "accept ": "application/json;odata=verbose", "content-type": "application/json;odata=verbose" }
Save your Flow and test by adding a new entry to Users list.
Copy the “body” from the OUTPUTS section of the “Send an HTTP request to SharePoint – Get Group ID” step.
Insert a new “Parse JSON” step. Set Content to “body” from the “HTTP request to SharePoint – Get Group ID” step. Click “Generate from sample” and paste in the “body” from the OUTPUTS section of the “Send an HTTP request to SharePoint – Get Group ID” step.
Insert a new “Condition” step. Set Choose a value to “Action value” from the “When an item is created or modified” trigger, “is equal to”, “Grant“.
In the “If yes” branch, insert a new “Send an HTTP request to SharePoint” step. Set:
- Site Address to your target site.
- Method to “POST”.
- Uri to
"api/web/SiteGroups(@{items('Apply_to_each-_Add_User')?['Id']})/users". Where “Id” is from the Parse JSON action. Power Automate will create an “Apply to each” action automatically. - Headers to “
{ "accept ": "application/json;odata=verbose", "content-type": "application/json;odata=verbose" }“. - Body to “{ “__metadata”: { “type”:”SP.User” }, “LoginName”:”@{triggerOutputs()?[‘body/User/Claims’]}” }”. Where “User Claims” is from the “When an item is created or modified” trigger.
Insert a new “Condition” step. Set Choose a value to “Action value” from the “When an item is created or modified” trigger, “is equal to”, “Revoke“.
In the “If yes” branch, insert a new “Send an HTTP request to SharePoint” step. Set:
- Site Address to your target site.
- Method to “GET”.
- Uri to
"api/web/SiteGroups(@{items('Apply_to_each-_Remove_User')?['Id']})/users/GetByEmail('@{triggerOutputs()?['body/User/Email']}')". Where “Id” is from the Parse JSON action and “User Email” is from the “When an item is created or modified” trigger. - Headers to “
{ "accept ": "application/json;odata=verbose", "content-type": "application/json;odata=verbose" }“.
- Insert a new Compose action and set Inputs to “Body” to the following expression “
@{body('Send_an_HTTP_request_to_SharePoint_-_Get_User_Id')['d']['id']}“. Hint: “Send an HTTP request to SharePoint – Get User Id” step.
Insert a new “Send an HTTP request to SharePoint” step. Set:
- Site Address to your target site.
- Method to “POST”.
- Uri to
"api/web/SiteGroups(@{items('Apply_to_each-Remove_User')?['Id']})/users/removeById(@{outputs('Compose-_User_Id')})". Where “Id” is from the Parse JSON action and “Outputs” is from the “Compose – User Id” trigger. - Headers to “
{ "accept ": "application/json;odata=verbose", "content-type": "application/json;odata=verbose" }“.
Test by creating new or modifying entries in the Users list.
I like this Flow pattern for automatically provisioning site security on behalf of non-technical site owners where the security groups may not be so straight forward. Adding approval workflows would be a natural next step with this Flow.
Thanks for reading.
NY
