Category Archives: Sharepoint Online

Automation, EntraID, Logic Apps, Office 365, Sharepoint Online

Populating Sharepoint Choice Column with Entra Group Names

Leave a comment

If you want to allow users in Sharepoint to select e.g. security groups or teams from a dropdown in a List and don’t want to manually keep that list of choices up to date….this is for you!

I’ve used Power Automate Flow for this specific scenario, but Logic Apps will of course work just as well.

First, define some variables and retrieve all the groups you want to show up in the Choice column:

Then, create a string with all the group’s names using a simple loop:

Then use ‘Send an HTTP request to Sharepoint’ to retrieve current columns (fields) defined in the list if you don’t know the GUID yet. This step is optional and uses GET to the _api/web/Lists/GetById(”)/Fields method.

Finally, use another Send an HTTP request to Sharepoint to Patch the column definition of the Choice column with the new group names.

Note we’re using PATCH for the _api/Web/Lists(guid”)/Fields(guid”) method and that I’m removing the trailing comma (,) from the data we’re patching in.

Also note that if you’re not using multiple choice but single choice you’ll need to adjust the SP.FieldMultiChoice and 15 values.

Data Factory, EntraID, Identity, Sharepoint Online

Sharepoint Online and Azure Datafactory using Managed Identity

Leave a comment

Let’s face it, Microsoft’s documentation on using Sharepoint as a data source (or sink) in ADF is pretty bad. And it doesn’t even describe how to use the Managed Identity of ADF, who still wants to register separate app credentials? Noooooo you don’t.

So here’s an example how to use Managed Identity to read a json file from a given SpO site, using minimal permissions given to ADF.

  1. Enable MI in datafactory, I’m assuming you know how to do this.
  2. Give ADF Sites.Selected Graph permissions, e.g. like this:
Param(
    [Parameter(Mandatory=$true)][String]$displayName="{NAMEOFADFINSTANCE}",
    [Parameter(Mandatory=$true)][String]$role="Sites.Selected"
)
Connect-AzureAD 
$Msi = (Get-AzureADServicePrincipal -Filter "displayName eq '$displayName'")
Start-Sleep -Seconds 10
$baseSPN = Get-AzureADServicePrincipal -Filter "appId eq '00000003-0000-0000-c000-000000000000'"
$AppRole = $baseSPN.AppRoles | Where-Object {$_.Value -eq $role -and $_.AllowedMemberTypes -contains "Application"}
New-AzureAdServiceAppRoleAssignment -ObjectId $Msi.ObjectId -PrincipalId $Msi.ObjectId -ResourceId $baseSPN.ObjectId -Id $AppRole.Id
$Msi.AppId
  1. Go to https://yourtenant.sharepoint.com/sites/yoursite/_api/site/id and copy the Edm Guid:
get sharepoint site ID / guid
  1. Go to https://developer.microsoft.com/en-us/graph/graph-explorer. Log in at the top right using a user with sufficient permissions, set the mode to POST, add the EDM guid in the URL and create the request body as follows (the id in the body can be found back in step 2, $Msi.AppId)
authorize managed identity on sharepoint site
  1. Create a REST linked service in ADF with Managed Identity auth:
create graph rest linked identity
  1. Create data source in ADF of type REST
  1. Replace the “uri” with the direct url of the file you want to read (or use an alternative method). Example uri:
    https://graph.microsoft.com/v1.0/sites/ee614b6d-de15-4101-1c12-14e4cd3186a9/drive/root:/General/Input_files/test.json:/content
Azure, Logic Apps, Sharepoint Online

Failed to retrieve dynamic inputs in a logic app

Leave a comment

For those unfortunate enough to also have an admin rename/move their Sharepoint site….you’ll be seeing errors in your logic app until you fix the URL. Sharing for those googling:

Failed to retrieve dynamic inputs. Error details: 'Resource provider 'Microsoft.Web' returned a redirect response with status code '308'. This response was blocked due to security concerns.'

Error executing the api <list url>. More diagnostic information: x-ms-client-request-id is xyz.

Unable to initialize operation

Unable to initialize operation details for swagger based operation - Get_item. Error details - Incomplete information for operation 'Get_item'

-2147024891, System.UnauthorizedAccessExceptionAccess is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))

Microsoft Teams, Office 365, OneDrive for Business, OneDriveMapper, Sharepoint Online

OnedriveMapper V5 required update!

Leave a comment

Microsoft is planning to modify the Office 365 sign in process slightly by September 15th or October 1st and has kindly supplied advance notice and support for OnedriveMapper, but only for V3.29+ and V5.15+

So if you’re on a lower version -> update asap!

The new login interruption looks like this:

OnedriveMapper V5 @ GitHub

OnedriveMapper V3 @ GitHub