Using Excel Macro to download userlist from SharePoint Online

Question

We have recently moved to SharePoint Online and I am auditing all the users in a particular group. Currently I am downloading each page using the "Get Data from Web" feature in excel and loading each page one at a time.

I tried using the Connection feature from Excel, but that fails. Reason below: The page link format changes based on the name of user from last page e.g. Page 1: https://<sharepoint>/sites/<Group>/_layouts/15/people.aspx?MembershipGroupId=161

Page 2: https://<sharepoint>/sites/<Group>/_layouts/15/people.aspx?Paged=TRUE&p_Title=<First_Name>%20<Last_Name>&p_ID=188&View=%7bXXXXX%2dXXXXX%2dXXXXX%2dXXXXX%2dXXXXX%7d&MembershipGroupId=161<group_id>&PageFirstRow=31

Now Page 3 will have a different First_Name and Last_Name. If I delete/add a few users, this value will change which is making it impossible to save connection on excel to get the list the next time.

This now means, I have do download each page (40+ in total) and then run my macro to compile a list and then perform my audits.

Hence I am looking for an alternate solution to be able to extract all the users details (Name, Title, Department) from the SharePoint probably by using a excel macro?

Answer 1

Instead of Excel macros, perhaps check into PowerShell. This can be complicated, but the folks who created the PnP project have written a lot of that code for you, so all you have to do is download their project and run a few commands to get group membership. See here to get the PnP packages (scroll down on that page to get to the instructions). Once that's installed, the following will connect to an Office 365 site, get the specified group, and export a list of the group's users into a csv file, which you could of course then open with Excel:

Connect-PnPOnline -Url https://somename.sharepoint.com/sites/somesite
$usercollection = Get-PnPGroup -Identity 'some group name' | select-object Users
#or, use the following to get one of the common groups:
#$usercollection = Get-PnPGroup -AssociatedMemberGroup | select-object Users
$usercollection.users | Export-Csv c:\temp\users.csv -NoTypeInformation

Or, instead of PowerShell, you can get the membership of a group via the following REST call:

https://somename.sharepoint.com/sites/somesite/_api/web/sitegroups/getbyname('some group name')/Users

As in, open Chrome, log into your SharePoint online site, and then in a new tab navigate to the above. You'll get the group members in an xml format. You can manually do the above in Excel 2016 by going to the data tab --> get data --> from other sources --> from odata feed. Paste in the previously tested url and click ok. then, in the authentication page, switch to "organizational", and specify your login info. then, just complete the wizard and click "load" get load the data into your workbook.

Sorry this isn't a complete answer, but perhaps this will get you started, as to do this in VBA you'd have to fetch the data via that REST interface programatically.