Issues getting multiple user details from a person or group column in a SharePoint list with Powershell Invoke-WebRequest

Question

I have been using PowerShell to get some site and admin details out of a SharePoint list by using Invoke-WebRequest. In the list there are 3 person or group columns, two are for the primary and secondary admins where the last is for the sites admin group and lists its members.

The issue I'm encountering is while I can expand the primary and secondary admin fields to get there names/email addresses etc. If I use the exact same thing for the admin group it comes back with a 400 error. I suspect this is due to the admin column having its allow multiple selection ticked and so I'm not expanding it correctly.

This is the Uri that works for the 2 admin columns:

site url etc./_api/web/lists(guid''siteGUID'')/items?$select=*,Secondary_x0020_Contact/EMail,Secondary_x0020_Contact/FirstName,Secondary_x0020_Contact/LastName,Primary_x0020_Contact/EMail,Primary_x0020_Contact/FirstName,Primary_x0020_Contact/LastName&$expand=Primary_x0020_Contact,Secondary_x0020_Contact

This one returns the error when I add the 3rd person column to be expanded:

site url etc./_api/web/lists(guid''siteGUID'')/items?$select=*,Secondary_x0020_Contact/EMail,Secondary_x0020_Contact/FirstName,Secondary_x0020_Contact/LastName,Primary_x0020_Contact/EMail,Primary_x0020_Contact/FirstName,Primary_x0020_Contact/LastName,Admin_x0020_Group/EMail,Admin_x0020_Group/FirstName,Admin_x0020_Group/LastName&$expand=Primary_x0020_Contact,Secondary_x0020_Contact,Admin_x0020_Group

I have also tried to just expand the admin group with out the others with this uri but it also gave the same error:

site url etc./_api/web/lists(guid''siteGUID'')/items?$select=Admin_x0020_Group/EMail,Admin_x0020_Group/FirstName,Admin_x0020_Group/LastName&$expand=Admin_x0020_Group'

As for the header etc. I'm using the following:

$headers = @{accept = "application/json;odata=verbose"}
$response = Invoke-WebRequest -Uri $uri -Headers $headers  -UseDefaultCredentials

Any advice on how to get this working would be greatly appreciated.

Answer 1

Something you could try also is the following. Since there are several ways of using SharePoint Online. One of my favorite ways is by installing the SharePoint Online SDK. This comes with dll files which you can then add to your script. See below example script which creates a query on a certain subsite and then loops through all the results and writing out the 'Employees'-tab.

Add-Type –Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type –Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.UserProfiles.dll"

$UserName = "[email protected]"
$Password = "yourpassword"
$Url = "https://yoursite.sharepoint.com/subSite"
$SecurePassword = $Password | ConvertTo-SecureString -AsPlainText -Force
$context = New-Object Microsoft.SharePoint.Client.ClientContext($Url)
$context.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName, $SecurePassword)
$listTitle = "Title of the list containing the items you want to query"

$list = $Context.Web.Lists.GetByTitle($listTitle)
$fields = $list.Fields
$qry = [Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery(10000,'ID','Created','Modified','Title','Employees')
$items = $list.GetItems($qry)
$Context.Load($fields)
$Context.Load($items)
$Context.ExecuteQuery()

foreach($item in $items){
    $item.FieldValues['Employees']
}

What you see above is an example script which is able to query items from a list. In the foreach-loop I loop through all items found on the List and write out the employees tab. In my CreateAllItemsQuery I give the command to get the 'ID' the 'Title' etc. All of these values I can address after this.

Another way of querying your SharePoint Online environment is by using the webrequest:

Example to query permission information for a specific folder on a specific list which I generated by recreating this exact url when opening the folder sharing permissions:

$baseUri = "https://yoursite.sharepoint.com"
$clientId = 'YourClientId'
$clientSecret = 'YourclientSecret'
$resource = '00000002-0000-0fe2-ce00-000000000000/yoursite.sharepoint.com@7b60bad-9ab2-45d0-a2ac-c2c4ght4f7e'
$body = @{
    grant_type = 'client_credentials'
    client_id = $ClientId 
    client_secret = $ClientSecret
    resource = $Resource
}
$result = Invoke-WebRequest -Method Post -Uri 'https://accounts.accesscontrol.windows.net/7b40dcad-9fe2-44d0-a0cc-c2d32f008f7e/tokens/OAuth/2' -ContentType 'application/x-www-form-urlencoded' -Body $body
$accessToken = ($result.content | ConvertFrom-Json).access_token

$headers = @{
    accept = 'application/json;odata=verbose'
    authorization = "Bearer $accessToken"
}
$listId = "2BD249F3-1847-4972-835S-99266DC415"
$itemId = "2"

$uri = $baseUri + "/Lists(@a1)/GetItemById(@a2)/GetSharingInformation?@a1='{$listId}'" + "&@a2='$itemId'" + '&$Expand=permissionsInformation,pickerSettings'

$result = Invoke-WebRequest -Method Post -Uri $uri -Headers $headers
($result.content | ConvertFrom-Json).d.permissionsInformation.links.results.linkDetails.Invitations.results

As you can see there are multiple ways to interact with SharePoint Online. And basically the last option is mainly looking into the requests in Developer Tools (F12) and find the exact query SharePoint does itself and replicate that URL. The first one is with the help of dll files which requires additional installation.

As you can see there are loads of options. If you aren't able to find the correct url to perform a webrequest, the first option might be interesting. It takes a lot of trial and error but I hope this helps you.