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:

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.

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

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.

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.

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

modery says:

May 6, 2021 at 6:04 am

Thanks Norm! Was looking for something like this for a small POC today, very helpful

LikeLike

Reply
1. Norm Young says:
  
  May 10, 2021 at 1:52 pm
  
  Thanks, Rene I appreciate feedback!
  
  NY
  
  LikeLike
  
  Reply
Rakesh says:

May 14, 2021 at 5:35 pm

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

LikeLike

Reply
1. Norm Young says:
  
  May 16, 2021 at 11:30 am
  
  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
  
  LikeLike
  
  Reply
  1. rsadhula says:
    
    May 16, 2021 at 11:36 am
    
    Thanks Norm, it worked now 🙂
    
    LikeLike
John Voh says:

June 10, 2021 at 8:36 am

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

LikeLike

Reply
1. Norm Young says:
  
  June 10, 2021 at 4:22 pm
  
  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
  
  LikeLike
  
  Reply
yarmouthcomms says:

June 10, 2021 at 5:55 pm

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?

LikeLike

Reply
1. Norm Young says:
  
  June 11, 2021 at 1:27 pm
  
  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
  
  LikeLike
  
  Reply
Edward says:

July 9, 2021 at 6:16 am

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?

LikeLike

Reply
1. Norm Young says:
  
  July 14, 2021 at 5:25 pm
  
  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
  
  LikeLike
  
  Reply
Himanshu says:

July 22, 2021 at 12:17 pm

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

LikeLike

Reply
icr0ss says:

July 22, 2021 at 12:32 pm

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

LikeLike

Reply
1. Norm Young says:
  
  July 22, 2021 at 10:10 pm
  
  Hi icr0ss,
  
  Are you trying to add a user to a SharePoint site, O365 Group or other?
  
  Thanks for reading!
  
  NY
  
  LikeLike
  
  Reply
Steve says:

December 10, 2021 at 4:03 pm

Hi Norm,

I am trying to do something similar to your original post. I am trying to add a user to a newly created item by looking them up from another list. The newly created item is a result of another flow when a user adds a document to a library. I’m not a developer, so I haven’t been able to get the http request calls to work.

LikeLike

Reply
1. Norm Young says:
  
  December 12, 2021 at 4:48 pm
  
  Hi Steve,
  
  Are you trying to grant item-level permissions to the user who added the document to the library or are you trying to add the user to a column in the list item that tracks the document added to the library?
  
  Thanks for reading!
  
  NY
  
  LikeLike
  
  Reply
Martín Gesteira says:

January 6, 2022 at 7:45 pm

Hi Norm! great work here!
In my case, i got the error “Unautorized”. Do I need some admin roles to use this flow? I’m the Sharepoint Site Owner, but I am not admin on the enviroment.

LikeLike

Reply
1. Norm Young says:
  
  January 6, 2022 at 9:45 pm
  
  Hi Martin,
  
  At what step are you getting the error?
  
  Thanks for reading!
  
  NY
  
  LikeLike
  
  Reply
  1. Martín Gesteira says:
    
    January 6, 2022 at 10:13 pm
    
    Hi Norm, thx for your answer.
    At the last step “Send HTTP request to SharePoint”
    Everything else went well.
    {
    “status”: 401,
    “message”: “404 NOT FOUND\r\nclientRequestId: d7354c4e-3144-4e28-af6c-d5b8e4a55c18\r\nserviceRequestId: 073a14a0-d01f-3000-a468-66b2a1c06cbb”,
    “source”: “https://nestle.sharepoint.com/teams/LATAM-ORActionRepository/%22api/web/SiteGroups(6)/users”,
    “errors”: []
    }
    
    LikeLike
Norm Young says:

January 7, 2022 at 1:01 pm

Hi Martin,

It looks like an unexpected character was added to the source code as %22. I believe that is a quote. Removing the quote and try again.

api/web/SiteGroups(@{items(‘Apply_to_each-_Add_User’)?[‘Id’]})/users

Error version: https://nestle.sharepoint.com/teams/LATAM-ORActionRepository/%22api/web/SiteGroups(6)/users
Correct version: https://nestle.sharepoint.com/teams/LATAMORActionRepository/api/web/SiteGroups(6)/users

I hope that helps.

NY

LikeLike

Reply
1. Martín Gesteira says:
  
  January 7, 2022 at 4:26 pm
  
  Hi Norm,
  Thank you very much for your answers. I corrected the mistake, It was indeed an extra quote over there.
  Sadly I still get an error =(
  {
  “status”: 401,
  “message”: “401 UNAUTHORIZED\r\nclientRequestId: 585343a6-37e7-47ed-a146-096e0d39343e\r\nserviceRequestId: 5c7814a0-109e-3000-a468-6d7fc4f0064a”,
  “source”: “https://nestle.sharepoint.com/teams/LATAM-ORActionRepository/api/web/SiteGroups(6)/users”,
  “errors”: []
  }
  
  LikeLike
  
  Reply
Norm Young says:

January 7, 2022 at 6:47 pm

Hi Martin, double-check the Body code. Metadata has 2 underscores.

LikeLike

Reply
1. Martín Gesteira says:
  
  January 7, 2022 at 8:42 pm
  
  Hi Norm, thanks again for trying to help me!
  This is the body, I think everything is correct. (I’m no using the conditios tho, it’s a flow to add user only)
  
  {
  “__metadata”:{
  “type”:”SP.User”
  },
  “LoginName”:”@{items(‘Apply_to_each_2’)?[‘Claims’]}”
  }
  
  This is the body on runtime:
  {
  “__metadata”:{
  “type”:”SP.User”
  },
  “LoginName”:”i:0#.f|membership|xxxxxxxxxx@py.nestle.com”
  }
  
  Just replaced my co-worker’s email with X’s
  
  LikeLike
  
  Reply
  1. Norm Young says:
    
    January 8, 2022 at 3:16 am
    
    Hi Martin,
    
    If I do a test run my runtime body is the same as yours (different claims info of course). What does the Uri look like at runtime?
    
    LikeLike
2. Martín Gesteira says:
  
  January 10, 2022 at 4:21 pm
  
  Hi again Norm!
  Hope you had a nice weekend!
  
  Uri looks like:
  api/web/SiteGroups(6)/users
  
  But then I double checked with your example and saw an “_”:
  
  So I added it to the uri on edit mode. And went to test again with:
  _api/web/SiteGroups(6)/users
  
  In this last run, there was no “unauthorized” but I got “BadRquest”:
  {
  “status”: 400,
  “message”: “Invalid JSON. The property name ” is not valid. The name of a property cannot be empty.\r\nclientRequestId: 24f8e397-929f-43ee-b9de-14ca9c091d64\r\nserviceRequestId: 5a6f15a0-702f-3000-a468-67e353730392″,
  “source”: “https://nestle.sharepoint.com/teams/LATAM-ORActionRepository/_api/web/SiteGroups(6)/users”,
  “errors”: [
  “-1”,
  “Microsoft.SharePoint.Client.InvalidClientQueryException”
  ]
  }
  
  LikeLike
  
  Reply
  1. Norm Young says:
    
    January 25, 2022 at 9:11 pm
    
    Hi Martin,
    
    Sorry for the delayed response. If this is still an issue what do you have listed in the Body?
    
    Norm
    
    LikeLike
  2. JF Theberge says:
    
    July 7, 2022 at 2:14 am
    
    Hello, Have you fix that 400 error. I have exactly the same thing, had 401 before and now 400…
    
    LikeLike
  3. Norm Young says:
    
    July 25, 2022 at 12:06 pm
    
    According to MariaJ, this worked: https://powerusers.microsoft.com/t5/Building-Flows/Send-HTTP-Request-to-Sharepoint-fails-with-Status-400-Bad/td-p/240938
    
    LikeLike
Guna says:

April 18, 2022 at 3:56 pm

hi for Remove scanario, if User Name not there, it flow is failed, instead of skip that flow error. any idea sir.

LikeLike

Reply
1. Norm Young says:
  
  April 19, 2022 at 12:26 pm
  
  Hi Guna,
  
  Add a Condition after the Compose to test the length of “id”. If it is zero then it does not exist and you can branch the remove user action accordingly.
  
  Thanks for reading!
  
  NY
  
  LikeLike
  
  Reply
MariaJ says:

May 4, 2022 at 1:11 pm

Hello Norm,
Thank you so much for sharing your expertise with the rest of the world!

Following your guide, I’m facing an issue: On the “Send an HTTP request to SharePoint – Add User” I’m getting the below error, could you point me in right directions to fix it ?

{
“status”: 400,
“message”: “The expression \”web/SiteGroups/(https:/xxxx.sharepoint.com/sites/xxxx/_api/Web/SiteGroups/GetById(4))/users\” is not valid.\r\nclientRequestId: 212a9395-1ce9-4f8e-94e0-f631c229048f\r\nserviceRequestId: 42153aa0-4063-1000-b040-b47c760837da”,
“source”: https://xxxxxxx.sharepoint.com/sites/MJCRMTeam/_api/web/SiteGroups/(https://xxxxxxxxx.sharepoint.com/sites/xxxxxx/_api/Web/SiteGroups/GetById(4))/users,
“errors”: [
“-1”,
“Microsoft.SharePoint.Client.InvalidClientQueryException”
]
}

LikeLike

Reply
1. MariaJ says:
  
  May 4, 2022 at 6:20 pm
  
  Hi Norm,
  I’ve found the solution for my 400 error here: https://powerusers.microsoft.com/t5/Building-Flows/Send-HTTP-Request-to-Sharepoint-fails-with-Status-400-Bad/td-p/240938
  
  Seems like when copy pasting code from a web pages into Flow it might cause some double or single quotes to enter Flow in the wrong format. Pasting the code to text editor and then t
  o Flow fixed it !
  
  LikeLike
  
  Reply
  1. Norm Young says:
    
    May 6, 2022 at 1:46 pm
    
    Hi Maria,
    
    Glad to hear that you found the solution. WordPress sucks for maintaining the proper formatting of single straight quotes.
    
    Thanks for reading!
    
    NY
    
    LikeLike
JF Theberge says:

July 6, 2022 at 4:14 pm

Hello, In the first steps, Running the script to get the body, that’s all I get as body
{
“d”: {
“results”: [ ]
}
}
the only difference with yours is that when I try to get , I only have listed as proposed variable
Thanks

LikeLike

Reply
1. Norm Young says:
  
  July 25, 2022 at 12:12 pm
  
  Hello JF,
  
  The Send an HTTP request action is very particular with syntax and formatting. Be sure that all quotes are straight quotes. You may want to try the https://developer.microsoft.com/en-us/graph/graph-explorer to confirm your syntax, permission etc.
  
  Thanks for reading!
  
  NY
  
  LikeLike
  
  Reply
Tom Scrace says:

July 7, 2022 at 9:59 pm

The “body” is missing information. This is all I’m getting:
{
“d”: {
“results”: []
}
}

LikeLike

Reply
1. Norm Young says:
  
  July 25, 2022 at 12:26 pm
  
  Hi Tom,
  
  Odd. I just tested and it continues to work fine.
  
  URI output: _api/web/SiteGroups?$filter=LoginName eq ‘SharePoint Demo Visitors’&$select=Id&$top=1
  
  body output: {
  “d”: {
  “results”: [
  {
  “__metadata”: {
  “id”: “https://normyoung.sharepoint.com/sites/SharePointDemo/_api/Web/SiteGroups/GetById(5)”,
  “uri”: “https://normyoung.sharepoint.com/sites/SharePointDemo/_api/Web/SiteGroups/GetById(5)”,
  “type”: “SP.Group”
  },
  “Id”: 5
  }
  ]
  }
  }
  
  Does the list entry still exist?
  
  Thanks for reading!
  
  NY
  
  LikeLike
  
  Reply
Olivia O says:

August 16, 2022 at 3:54 pm

Hi Norm,

What are the options if the user wouldn’t have access to a SharePoint list to write to? I have a SharePoint site I want to grant access to based on a flow triggered by a button in Power Apps (all users in my organization have access to the app but I can’t do the same for the SharePoint Site). The SharePoint lists I’m using require them to have access first before they can edit/ contribute.

Is there a way I can use the Send an HTTP request to SharePoint with the POST method? I haven’t found any great sources for what all needs to be included in my request. I’d also need to be able to manage their access level when automating with flow.

Thanks!
Olivia

LikeLike

Reply
1. Norm Young says:
  
  August 16, 2022 at 4:17 pm
  
  Hi Olivia,
  
  Can you use a service account to write to the list on their behalf? The SharePoint connection in Power Automate would have access but not the user pressing the button.
  
  Great question. I hope this helps.
  
  NY
  
  LikeLike
  
  Reply
Harry says:

September 23, 2022 at 1:14 pm

Hey Norm!

I followed each step exactly as described but also get no results in body output. Any ideas? Any help would be appreciated 🙂
I tried understanding the api call but it was a bit much…

HG

LikeLike

Reply
1. Norm Young says:
  
  October 15, 2022 at 7:21 pm
  
  Hi Harry,
  
  Sorry for the delayed response. What do you get in the Uri field after testing the flow? It should have translated to your site name.
  
  I hope this helps and thanks for reading!
  
  NY
  
  LikeLike
  
  Reply
  1. Hari Gouromichos says:
    
    October 17, 2022 at 8:46 am
    
    Hey Norm,
    
    I was able to solve the problem by using a different sp site. The URI was:
    _api/web/SiteGroups?$filter=LoginName eq ‘SharePoint Demo ‘&$select=Id&$top=1
    
    I tried changing the api call a bunch.
    
    I think the test sp site I created is either broken or configured wrong since I wasnt really able to interact with the permission configuration.
    Im using the sandbox tenant and was able to get expected results with the premade sites.
    
    Although I changed the whole thing around. Im now using a powerapp to decide the group and user.
    
    HG
    
    LikeLike
  2. Norm Young says:
    
    October 18, 2022 at 10:01 am
    
    Hi Hari,
    
    Interesting that you encountered this behavior. I wonder if your test site was not “modern” and had a custom set of permissions in place. Glad to hear that you up and running!
    
    NY
    
    LikeLike
Jennifer G says:

February 8, 2023 at 4:17 pm

I don’t have SiteName Value… so I am stuck right at the beginning…

LikeLike

Reply
1. Norm Young says:
  
  February 21, 2023 at 6:41 pm
  
  Hi Jennifer,
  
  You have to populate SiteName with a value for this to work. When referencing SiteName Value in varSiteName it will come in the form of dynamic output from the When an item is created trigger.
  
  Hope this helps and thanks for reading!
  
  NY
  
  LikeLike
  
  Reply