Marcel
Marcel That's me: Marcel

Adding Guest Users to Azure AD from Excel with PowerShell

Adding Guest Users to Azure AD from Excel with PowerShell

Sharing access across different tenants in one of the key benefits of Azure AD. My customers appreciate that they can provide Azure-based solution to their cooperated users and to guest users as well. Cooperated users include users from the group and subsidiaries. They all can access resources with one identity – on-premises and in the cloud (Same-sign-on, single-sign-on).

Guest users can be deployed manually via the Azure portal, via PowerShell or with a connector to another system (like SAP HR).

Adding users from an Excel file can be done with PowerShell. To show an example I created an Excel file with some headers:

  • Company
  • Surname
  • Name
  • Mail
  • Job role

img

To add this user, we must archive these steps:

  • Login into Azure AD with appropriate rights
  • Enumerate through the excel table
  • Inviting the user
  • Adding the right properties (company, name, surname, …) to the invited user object

To do this I prepared this script:

$invocation = (Get-Variable MyInvocation).Value
$directorypath = Split-Path $invocation.MyCommand.Path
$directorypath

$ExcelFile=$directorypath+"\Users2Invite.xlsx" # Path to the excel file
$InviteRedirectURL="https://mycompany.com" # Redirect url after the first logon
$WorkSheetNum="Tabelle1" # Name of the table in the excel file

if (!$lastLogin) {
   # put your Azure AD tenant id here (Azure portal / Azure Active Directory / Properties / Directory ID)
   $global:lastLogin=Connect-AzureAD -TenantId "xxxxxxxxxxxxx-xxxxxxxx-xxxxxxxxxxx-xxxxxxxxxxx" 
}

$Excel = New-Object -ComObject Excel.Application

$WorkBook = $Excel.Workbooks.Open($ExcelFile)
$WorkSheet = $WorkBook.WorkSheets.Item($WorkSheetNum)

$RowNum = 2

While ($WorkSheet.Cells.Item($RowNum, 1).Text -ne "") {
  # Read the first line from sheet
  $Company = "Ext: "+$WorkSheet.Cells.Item($RowNum, 1).Text.trim()
  $Surname = $WorkSheet.Cells.Item($RowNum, 2).Text.trim()
  $Name = $WorkSheet.Cells.Item($RowNum, 3).Text.trim()
  $Mailadress = $WorkSheet.Cells.Item($RowNum, 4).Text.trim()
  $JobTitle = $WorkSheet.Cells.Item($RowNum, 5).Text.trim()
  $userFullName=$Name+", "+$Surname

  Write-Output("Adding user: "+$userFullName+" ("+$Mailadress+")")
  $invitation=New-AzureADMSInvitation -InvitedUserDisplayName $userFullName -InvitedUserEmailAddress $Mailadress -SendInvitationMessage $true -InviteRedirectURL $InviteRedirectURL
  $user = Get-AzureADUser -ObjectId $invitation.InvitedUser.Id
  Set-AzureADUser -ObjectId $invitation.InvitedUser.Id -Surname $Name
  Set-AzureADUser -ObjectId $invitation.InvitedUser.Id -GivenName $Surname
  Set-AzureADUser -ObjectId $invitation.InvitedUser.Id -JobTitle $JobTitle
  Set-AzureADUser -ObjectId $invitation.InvitedUser.Id -Department $Company
  Set-AzureADUser -ObjectId $invitation.InvitedUser.Id -Displayname $user.Displayname
  $RowNum++
}

If you run this script all users in the excel file will be invited to your Azure AD tenant.

Remark: The company field can not be written to an Azure AD user object (read-only). So I write the company to the department field.

First published on: https://www.sepago.de/blog/adding-guest-users-to-azure-ad-from-excel-with-powershell/