View SharePoint Invitation Status and Users within Permission Groups

[Anonymous]

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)
   • Visible to me via https://MYURL/Access%20Requests/pendingreq.aspx?mbypass=1
2. Show the users who have access, based on the permissions security group they have been assigned
• Visible to me via https://MYURL/_/_layouts/15/user.aspx

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

[v-jayw-msft]

Hi @Anonymous ,

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-Power-BI/td-p/32146/page/2 

Best Regards,

Jay

[Anonymous · 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

[Anonymous]

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