Add and remove users to a SharePoint site using Microsoft Lists and Power Automate

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:

ColumnType
UserPerson.
ActionChoice with “Grant” and “Revoke” as the lookup values.
GroupChoice with “Visitors”, “Members” and “Owners” as the lookup values. This coincides with the default site security groups and can customized as you see fit.
SiteNameSingle line of text.
Users lists

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

Build an automated Flow

Set the trigger to your target site and list name.

When an item is created

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.

Initialize variable

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" }
Send an HTTP request to SharePoint - Get Group ID

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.

Parse JSON - Group ID

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

Condition - Grant Access

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.
Condition - Grant Access - If yes branch

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

Condition - Revoke Access

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" }“.
Send an HTTP request to SharePoint - Get User Id
  • 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.
Compose - User Id

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" }“.
Send an HTTP request to SharePoint - Remove User

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

14 thoughts on “Add and remove users to a SharePoint site using Microsoft Lists and Power Automate

  1. Hi Norm,
    When we try to use this expression @{body(‘Send_an_HTTP_request_to_SharePoint_-_Get_User_Id’)[‘d’][‘id’]} under compose, we are getting below error:
    Correct to include a valid reference to ‘Send_an_HTTP_request_to_SharePoint_-_Get_User_Id’ for the input parameter(s) of action ‘Compose’.

    Like

    1. Hi Rakesh,

      Make sure the previous “Send an HTTP request to SharePoint” action named the same as my example (
      “Send an HTTP request to SharePoint – Get_User_Id”).

      Thanks for reading!

      NY

      Like

  2. Thanks for sharing. I originally wanted to work around the fact that SPO groups do not allow to add groups in a group while other systems accept this. In my case, I have to add a user to several groups, including one that contains all groups. I thought using a list to add users in each ad hoc group using your method would be a good workaround however I am not confident to spend less time to creates this PA and list than add a newcomer to several groups. I wish Microsoft will finally upgrade user groups management to have the features add a user or a group in a group, sort users by a column, allow viewing some additional columns or mask the user description column or add the feature sort. Then User group will be as Lists and more user-friendly. I bookmark your method in case I get the courage to bang my head and go through all Microsoft’s gymkhana

    Like

    1. Hi John,

      I can appreciate your frustration, identity management and provisioning are always complicated in the real world. This post was targeted at sites where the site owner was non-technical and the requirements straightforward.

      Thanks for reading!

      NY

      Like

  3. Hi Norm,
    I am not very experienced in Microsoft Flow, but I could not find SiteName in the Dynamic Content list while trying to set up Initialize Variable varSiteName. Any suggestion?

    Like

    1. Hi yarmouthcomms,

      That value is populated in the Users list and in the case of this post the site is called “SharePoint Demo”.

      I hope that helps.

      NY

      Like

  4. Hi Norm, I’m trying to build something like what you have outlined in this post but for external users of the organisation. so the ‘User’ column would be the email address instead with the type being a single line of text. Can you please assist with what changes would be required in this instance?

    Like

    1. Hi Edward,

      That is a great use case and should make for a good future blog post. More research is required on my end to know if the existing API support external users. Keep a look out for a future post and thanks for reading!

      NY

      Like

  5. Hi Norm,
    I am trying to trigger the flow whenever an entry is made in my SQL table.
    I need to add the new users into their respective security groups. When the entry is made, one of the columns contain a HubIDs which is string type column and contains IDs which are a foreign key from Hub table which has the ids and names of the hubs(security groups) that the user is supposed to be a part of. These are not actual security group IDs. So I need to add the new user into the security groups that are assigned against his name in SQL table. I’m not much familiar with REST api and json, hence having a hard time trying to reach the output. And I couldn’t find anything related to such a problem statement. Yours is the closest solution I have found yet.
    If possible could you help me out?
    Thanks

    Like

  6. Hi Norm,
    I have to create a SQL trigger whenever an item is added to the Users table to perform an action when the new user is entered, they should be directly added to their respective security group. Users table consists of Name, Email and SecurityGroupId in which the user needs to be added. The name of the security groups are in another table and securityGroupId acts as connection between these 2 tables. So I need to use the securityGroupId and find the name of the sharepoint security group where the new user is to be added. I’m very new to sharepoint as well as automate. Could you help me out a little here?

    Thanks

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s