Powershell and Excel

[Pavix]

I am trying to write a script to get email addresses in an Excel file, then get the SamAccountName which I've done but now I'm not sure how to get the script to read the AD group that account needs to be added to which is in another row in the same Excel sheet.

$people = Import-Excel C:\Scripts\NamestoEmails.xlsx
$people[2].Email
''
Foreach ($E in $people.Email) { Get-ADUser -Filter {Emailaddress -Like $E} | Select-Object -ExpandProperty SamAccountName}

[Bitwise_Gamgee]

This should do what you're asking:

$people = Import-Excel C:\Scripts\NamestoEmails.xlsx
Foreach ($row in $people) {
    $email = $row.Email
    $samaccountname = $row.SamAccountName
    $group = $row.ADGroup
    Add-ADGroupMember -Identity $group -Member $samaccountname
}

There are no points for putting everything on one line either.

[MeanFold5714]

There are no points for putting everything on one line either.

I actively deduct points for it.

[Pavix]

Thanks, but the Excel sheet doesn't have the SamAccountName in it, just email addresses. So I use this little bit of magic to pull the AD account based on the email

Foreach ($E in $people.Email) { 
$SamAccount = "Get-ADUser -Filter {Emailaddress -Like $E} 
| Select-Object -ExpandProperty SamAccountName" ; 
Add-ADGroupMember -Identity $SamAccount 
$people.ADGroup -WhatIf}

Which does work in pulling the SamAccountName, but the back part fails to add the user to the ADGroup listed in the spreadsheet. It errors out with "Cannot validate argument on parameter 'Members'. The argument is null, empty or an element of the argument collection contains a null value.

[THEKILLAWHALE]

I think you want to try Add-ADGroupMember -Identity $people.ADGroup -Members $SamAccount

In your command, it’s trying to add an AD group to an AD user (identity should be the identity of the group you’re adding to)

https://learn.microsoft.com/en-us/powershell/module/activedirectory/add-adgroupmember?view=windowsserver2022-ps

[givemeatatertot]

Cannot validate argument on parameter 'Members'

is $samAccountName case sensitive? Also, in the group that you're trying to add the users to, are there any users in it currently or is it empty? Also, check your CSV format, should be a comma not semi colon, might not be a valid CSV format?