cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Oneill1
Resolver I
Resolver I

View SharePoint Invitation Status and Users within Permission Groups

Hi

 

I’m am looking to build a PowerBI report to minimise the noise I get from the other departments around if an external user been invited to one of our SharePoint sites and if they have accepted the invite.

 

High level – what I hope to achieve is

  1. Show the users and their email who have been sent an invite, the date sent (possible original and reminder)
  2. Show the users who have access, based on the permissions security group they have been assigned

 

I have tried using Source = OData.Feed("MYURL/_vti_bin/ListData.svc" and have been able to view the below 2 items, however not to the level of detail I require for the users to self-serve through a report.

 

The table ‘AccessRequests’

This contains who sent the reminder in this case me, with the original sender as ‘System Account’, but doesn’t contain the email or name the invitation has been sent to.

 

The table ‘UserInformationList’

This table shows me who has access, their email, but not which permission group they are part of

 

Any help greatly appreciated

1 ACCEPTED SOLUTION

Hi @v-jayw-msft 

 

No these weren’t the solution i was looking for, however they are very useful for another project I’m working on.

 

I have 2 areas of interest for this report - Who has invitations pending, and who currently has access and their permissions level.

 

I have found the solution using SharePoint REST Services and OData

 

https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/get-to-know-the-sharepoint-rest-service?t...

 

i.e. https://(mydomain).sharepoint.com/sites/(my site)/_vti_bin/ListData.svc

 

 

 

Item 1 - Access requests status (pending, revoked etc) - https://(mydomain).sharepoint.com/sites/(my site)/_api/web/lists/getbytitle('Access%20Requests')/items?$select=RequestedForUserId,RequestedForDisplayName,RequestedBy,RequestedByDisplayName,RequestedByUserId,ObjectRequestedTitleDisp,%20ApprovedByStringId,PermissionLevelRequested,RequestDate,Modified,Status,Expires,Created,RequestedForDisplayNameDisp,RequestedByDisplayNameDisp

 

 

 

Item 2 - Display the users who currently have access and their permissions settings https://(mydomain).sharepoint.com/sites/(my site)/_api/web/sitegroups

 

 

The status for item 1 for ease are as below 

 

Status Code

Pending         0

Approved       1

Accepted       2

Denied           3

Expired          4

Revoked        5

View solution in original post

3 REPLIES 3
v-jayw-msft
Community Support
Community Support

Hi @Oneill1 ,

 

Not sure if I understand you correctly, but you could get AccessRequests and UserInformationList table by connecting SharePoint List.

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-sharepoint-online-list 

https://whitepages.unlimitedviz.com/2018/01/using-power-bi-to-report-on-person-fields-in-sharepoint/ 

https://community.powerbi.com/t5/Desktop/How-to-display-an-assigned-user-in-a-sharepoint-list-in-Pow... 

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

Hi @v-jayw-msft 

 

No these weren’t the solution i was looking for, however they are very useful for another project I’m working on.

 

I have 2 areas of interest for this report - Who has invitations pending, and who currently has access and their permissions level.

 

I have found the solution using SharePoint REST Services and OData

 

https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/get-to-know-the-sharepoint-rest-service?t...

 

i.e. https://(mydomain).sharepoint.com/sites/(my site)/_vti_bin/ListData.svc

 

 

 

Item 1 - Access requests status (pending, revoked etc) - https://(mydomain).sharepoint.com/sites/(my site)/_api/web/lists/getbytitle('Access%20Requests')/items?$select=RequestedForUserId,RequestedForDisplayName,RequestedBy,RequestedByDisplayName,RequestedByUserId,ObjectRequestedTitleDisp,%20ApprovedByStringId,PermissionLevelRequested,RequestDate,Modified,Status,Expires,Created,RequestedForDisplayNameDisp,RequestedByDisplayNameDisp

 

 

 

Item 2 - Display the users who currently have access and their permissions settings https://(mydomain).sharepoint.com/sites/(my site)/_api/web/sitegroups

 

 

The status for item 1 for ease are as below 

 

Status Code

Pending         0

Approved       1

Accepted       2

Denied           3

Expired          4

Revoked        5

View solution in original post

Oneill1
Resolver I
Resolver I

I have moved this forward slightly. I can now view the invites sent using the below. So point 1 partially covered, i had to build a table to link for the permission group names.

 

If anyone has a cleaner way of doing this i would greatly appreciatte the direction.

 

https://(myURL).sharepoint.com/sites/(subsite)/_api/web/lists/getbytitle('Access%20Requests')/items?$select=RequestedForUserId,RequestedForDisplayName,RequestedBy,RequestedByDisplayName,RequestedByUserId,ObjectRequestedTitleDisp,%20ApprovedByStringId,PermissionLevelRequested,Conversation,RequestDate,ID,Modified,Status,Expires,Created,AuthorId,FileSystemObjectType,RequestedListId,RequestedListItemId,RequestedForDisplayNameDisp,RequestedByDisplayNameDisp,PermissionDisp

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors