Export your SharePoint Access Request List

Posted by Beau Cameron on 09.24.16
Beau Cameron
Find me on:

Sometimes administrators need to access the SharePoint Access Request list outside of the SharePoint Page.

For those who don't know, whenever a user asks for permission to view your SharePoint site, whether it be access to a file, a team site or folders, that request gets stored in your environment at this location.

https://YourSharePointSite/Access%20Requests/pendingreq.aspx

On this page, you'll find a few dataview webparts that are filtered by "Pending", "External Requests" and "History".  

It is a pretty handy page for administrators who want to know who has access, when they were given access or when their access was removed. However, from the SharePoint interface you have no access to any properties on the list. You'll notice there is no ribbon! Also if you try to access the list directly at https://yoursharepointsite.com/Lists/Access%20Requests it is going to return a 404 not found!

Seems like Microsoft completely locks you out of this list...but that's when APIs come in handy! You can access this list by using the REST api and by using RSS Feeds. So I am going to show you how to connect Microsoft Excel to your Access Request list in SharePoint.


Open a blank worksheet in Excel.

Navigate to the Data tab in the ribbon and select "From Other Sources" and choose "OData data feed".

BlogPostExcel.png

 

The next window is going to ask you to choose the file or location to recieve the OData call, followed by your credentials. Modify and paste the following REST API query into the location field.

https://YourSharePointSite/_api/web/lists/getbytitle('Access%20Requests')/items?$select=RequestedBy,ObjectRequestedTitleDisp,%20ApprovedByStringId,PermissionLevelRequested,Conversation,RequestDate,ID,Modified,Created,AuthorId,FileSystemObjectType

Click next and the following window will ask you to choose what table to connect to. You'll want to select "Items" (it will be the only table here).

ExcelSecondStep.png

  

On the next window, you can rename your connection to be something more appropriate. Then click finish.


Finally, another window will popup called "Import Data". This screen is where you can choose to import the data into different type of Excel objects. For this, I'm just going to import into a table and click "Ok".

 

ImportData.png

 

VOILA!

    Result.png

 
Now what? Now that we have the data, it's time to figure out what all the data means. 

Status

  • 0=Pending
  • 1=Approved
  • 2=Accepted
  • 3=Denied
  • 4=Expired
  • 5=Revoked

FileSystemObjectType

  • -1 Enumeration whose values specify whether the object is invalid.
  • 0 File
  • 1 Folder
  • 2 Web

PermissionLevelRequested

  • 1073741829 Full Control
  • 1073741826 Read
  • 1073741827 Contribute

 BUT I DON'T WANT TO USE REST! WHAT ABOUT RSS FEEDS?

So you want to use RSS Feeds for this? But, you don't have access to the settings page for the Access Requests list. Or do you? Having developed SharePoint Add-ins the last 2 years, there is a little trick we use to get to settings pages for libaries (this page is hidden by microsoft in add-ins).  Here is what you need to know.

Go to your access request page but instead of navigating to pendingreq.aspx, change your url to oldreq.aspx. This page is the history of your access requests, except it allows you to change the view of the webpart! So why is that cool? Well, when the page loads, click "Modify this view".

 

History.png

 

On the view page, you will click "Settings" on the top left.

settings.png

 

VOILA! Settings page!

settingspage.png

 

You'll notice somethign interesting. First, the URL for the list. It says /_layouts/error.aspx. That is because Microsoft doesn't want use to have direct access to this list. Second, the RSS settings link. From here, we'll be able to configure the RSS feed for us to consume.

When you click the RSS Settings link you will be asked to create your RSS settings. 

RSSFEED.png

 

For this example, I am just going to throw in some numbers for the Max Items to include and the Maximum days to include. You can choose whatever values you want here, I am not sure there is any limit to the number of items you can select.  Click "Ok"

You will be redirected back to the settings page. Now it's time to consume your RSS Feed. In SharePoint, the RSS feed url is called listfeed.aspx. If you go to the top of your browser search bar, you'll notice you are on listedit.aspx. Just modify your URL to be listfeed.aspx instead and hit enter! 

Now you have an RSS feed for your Access Request list, which you can consume anywhere (say Excel!?).

rssfeedresult.png

 

Or you could use the old webservices using listdata.svc by hitting the following url https://yoursharepointsite/_vti_bin/listdata.svc/AccessRequests

I hope you found this blog post helpful! I'd love to hear your feedback. 

Learn more about Business Applications >

Topics: SharePoint 2013, Strategy, Administration, Development

Subscribe Here!

Recent Posts

Posts by Tag

See all