使用PowerShell将SharePoint列表用户添加至AD组时遇报错求助

我需要用PowerShell把SharePoint列表里的用户添加到Active Directory组，列表里存的是用户邮箱，计划截断@后的内容拿到用户名，再添加到AD组，但执行脚本时出现一系列错误，错误信息如下：

Connect-SPOService : Could not connect to SharePoint Online.
At line:1 char:1
+ Connect-SPOService -Url https://capsticks-admin.sharepoint.com/
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Connect-SPOService], InvalidOperationException
    + FullyQualifiedErrorId : System.InvalidOperationException,Microsoft.Online.SharePoint.PowerShell.ConnectSPOService
 
-credential : The term '-credential' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path 
was included, verify that the path is correct and try again.
At line:2 char:12
+            -credential SharepointOnline1@Internal.Capsticks.com
+            ~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (-credential:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException
 
Get-SPWeb : The term 'Get-SPWeb' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was 
included, verify that the path is correct and try again.
At line:8 char:16
+ $spSourceWeb = Get-SPWeb $SiteURL
+                ~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (Get-SPWeb:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException
 
Cannot index into a null array.
At line:9 char:1
+ $spSourceList = $spSourceWeb.Lists[$ListName]
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : NullArray
 
Cannot index into a null array.
At line:11 char:47
+ $spSourceItems = $spSourceList.Items | where {$_['ID'] -eq 1}
+                                               ~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : NullArray
 
Get-PnPListItem : The current connection holds no SharePoint context. Please use one of the Connect-PnPOnline commands which uses the -Url argument to connect.
At line:14 char:14
+ ... ListItems = Get-PnPListItem -List $ListName -Fields "Submitter Email" ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Get-PnPListItem], InvalidOperationException
    + FullyQualifiedErrorId : System.InvalidOperationException,PnP.PowerShell.Commands.Lists.GetListItem
 
Cannot index into a null array.
At line:22 char:2
+  $submitteremail = $ListItems.FieldValues["Approver Email"];
+  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : NullArray
 
You cannot call a method on a null-valued expression.
At line:24 char:2
+  $submitteremail = $submitteremail.TrimEnd("@","");
+  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull
 
Add-ADGroupMember : Cannot validate argument on parameter 'Members'. The argument is null or empty. Provide an argument that is not null or empty, and then try the 
command again.
At line:34 char:65
+ ... upMember -identity "Wireless Access - Users" -Members $submitteremail
+                                                           ~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Add-ADGroupMember], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.ActiveDirectory.Management.Commands.AddADGroupMember
 

Cannot index into a null array.
At line:38 char:2
+  $ListItem["Department"] = "IT"
+  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : NullArray

原PowerShell脚本 #

Connect-SPOService -Url https://ADUsers.sharepoint.com/ -credential Admin@outlook.com
           
#Config Variables
$SiteURL="https://sharepoint.com/sites/Intranet-IT/Lists/MimeCast%20Access%20Form%20Responses?env=WebViewList"
$ListName= "MimeCast Access Form Responses"

$spSourceWeb = Get-SPWeb $SiteURL
$spSourceList = $spSourceWeb.Lists[$ListName]

$spSourceItems = $spSourceList.Items | where {$_['ID'] -eq 1}

#sharepoint online pnp powershell get list items
$ListItems = Get-PnPListItem -List $ListName -Fields "Submitter Email" -PageSize 2000

#Loop through Items that have 1 in the Bit Column
$spSourceItems | ForEach-Object {
    Write-Host $_['Approved'] = 1
}

#Set Variables
 $submitteremail = $ListItems.FieldValues["Approver Email"];
 
 $submitteremail = $submitteremail.TrimEnd("@","")
 
 #Loop through Submitter emails
 Foreach ($item in $submitteremail)
 {
     #Get the List Item's Submitter email
    Write-host $Item["Submitter email"]
 }
 
 #Add username into AD Group 
 Add-ADGroupMember -identity "Wireless Access - Users" -Members $submitteremail
 
 
 #Set "Department" column value
 $ListItem["Department"] = "IT"

错误分析与修复方案 #

1. SharePoint连接问题 #

• 问题：混用SPO和PnP连接方式，Connect-SPOService无法适配PnP cmdlet；-credential参数格式错误，不能直接传邮箱字符串；Get-SPWeb是本地SharePoint cmdlet，不支持Online环境。
• 修复：统一使用Connect-PnPOnline连接，通过Get-Credential获取合法凭据；SiteURL只保留站点根地址，不要带列表查询参数：

  $cred = Get-Credential -Message "输入SharePoint管理员凭据"
  Connect-PnPOnline -Url "https://yourtenant-admin.sharepoint.com" -Credential $cred
  

2. 列表项访问错误 #

• 问题：$ListItems是多列表项集合，不能直接访问FieldValues；字段名不匹配（脚本写了"Approver Email"但实际要取"Submitter Email"）；TrimEnd用法错误。
• 修复：遍历$ListItems逐个处理，用Split("@")[0]更可靠提取用户名：

  foreach ($item in $ListItems) {
      $submitterEmail = $item["Submitter Email"]
      if ($submitterEmail) {
          $username = $submitterEmail.Split("@")[0]
      }
  }
  

3. AD组添加参数验证失败 #

• 问题：$submitteremail为空或格式错误，导致Add-ADGroupMember参数无效；未验证AD用户是否存在。
• 修复：添加AD用户前先验证存在性，确保参数有效：

  if (Get-ADUser -Filter {SamAccountName -eq $username}) {
      Add-ADGroupMember -Identity "Wireless Access - Users" -Members $username
  } else {
      Write-Warning "用户 $username 不存在于AD中"
  }
  

4. 列表项更新错误 #

• 问题：$ListItem未定义，直接赋值会报错。
• 修复：用Set-PnPListItem在遍历中更新字段：

  Set-PnPListItem -List $ListName -Identity $item.Id -Values @{"Department" = "IT"}
  

修正后的完整脚本 #

# 获取凭据并连接SharePoint Online
$cred = Get-Credential -Message "输入SharePoint管理员凭据"
Connect-PnPOnline -Url "https://yourtenant-admin.sharepoint.com" -Credential $cred

# 配置变量
$SiteURL = "https://sharepoint.com/sites/Intranet-IT"
$ListName = "MimeCast Access Form Responses"
$ADGroupName = "Wireless Access - Users"

# 获取列表中包含目标字段的项
$ListItems = Get-PnPListItem -List $ListName -Fields "Submitter Email", "Approved" -PageSize 2000

# 遍历处理每个符合条件的列表项
foreach ($item in $ListItems) {
    # 仅处理Approved状态为1的项
    if ($item["Approved"] -eq 1) {
        Write-Host "开始处理项ID: $($item.Id)"
        $submitterEmail = $item["Submitter Email"]
        
        if ($submitterEmail) {
            # 提取@符号前的用户名
            $username = $submitterEmail.Split("@")[0]
            
            try {
                # 验证AD用户存在并添加到组
                if (Get-ADUser -Filter {SamAccountName -eq $username} -ErrorAction Stop) {
                    Add-ADGroupMember -Identity $ADGroupName -Members $username -ErrorAction Stop
                    Write-Host "用户 $username 已成功添加到AD组 $ADGroupName"
                    
                    # 更新列表项的Department字段
                    Set-PnPListItem -List $ListName -Identity $item.Id -Values @{"Department" = "IT"} -ErrorAction Stop
                    Write-Host "已更新项ID: $($item.Id) 的Department字段为IT"
                }
            } catch {
                Write-Error "处理用户 $username 时出错: $_"
            }
        } else {
            Write-Warning "项ID: $($item.Id) 的Submitter Email字段为空，跳过处理"
        }
    }
}

# 断开SharePoint连接
Disconnect-PnPOnline

内容的提问来源于stack exchange，提问作者Kushal Rattu