Power Automate Get sharePoint site all users

[Anonymous]

Hi Team,

Looking for REST API to get all Site Users

In shortly: we would like to get all the users from an SP site. it doesn`t matter about the role (permission level). hence via UI we used to below URL to get all the site Users. is this right or is my assumption wrong?

https://site365.sharepoint.com/sites/contoso/_layouts/15/people.aspx?MembershipGroupId=0

If yes, what is the REST API to get all the site users?

Thanks,

[Expiscornovus · Accepted solution]

Hi @Anonymous,

Yes, that would be the all the site users.

There is a UserCollection resource which you can retrieve via the REST API in a Send an HTTP request to SharePoint action:

https://docs.microsoft.com/en-us/previous-versions/office/developer/sharepoint-rest-reference/dn531432(v=office.15)?redirectedfrom=MSDN#usercollection-resource

Btw, that REST API method will probably list a couple of additional (service) accounts as well, like SHAREPOINT\system & nt service\spsearch which you don't see in the interface.

[M1rzA_V]

Can you export this to csv/excel list or something? I would like to have an output table with users/groups.... 

I tried searching for users v2 and Sending an HTTP request methods to get all users in a table but somehow I can't get it. 

[Expiscornovus]

Hi @M1rzA_V,

Yes, you could use the response in a Select and/or Create CSV Html Table actions. After that you could use a Create File action to create such a file.

[corinne37]

Please, can you show how can we have the list of users of a sharepoint site on a excel table?

i don't find the solution

[Ducktorres]

After the http action to get all users, take and use the "create file" action directly to create a new file in sharepoint somewhere. Name the filename something.json and in the data add the body output from the http action containing all results.

Run the powerautomate flow and the file should be created with all information within the file.

Now download the json file from Sharepoint (not mandatory but easier to explain).

Open excel

- Choose "Data" - "Get Data" - From File (Json) 

- point to the json file

- now powerquery will open. 

- Choose "into table"

- Expand the values

- Expand the value.results list

- Expand the value.result records

Now all columns for all the users will be available in powerquery, you should see the resulting table. To get it to excel now click top right button "Close & Load" - now it should be in excel.

Extra: If you connect directly to the json file in SharePoint instead and use all steps above you can just hit the refresh button in excel and then every time your powerautomate overwrites the json you will get the table with all users without more job. 

was this at all understandable? 😉

//Claudio