PowerShell导出Office 365组数据至CSV：添加自定义变量与列名

原脚本存在的问题 #

1. 导出逻辑错误：Export-CSV语句放在ForEach循环外部，只会导出最后一个组的数据，无法覆盖所有目标组
2. 自定义字段映射错误：Select语句中直接写$CheckDate、$Members这类变量，PowerShell会将变量当前值当作属性名，而非将变量值作为对应属性的内容
3. 路径笔误：第二个Remove-Item使用了错误的路径$CSVPath，应该是$CSVPath2

修正后的完整脚本 #

$CSVPath = "C:\AllGroupNoMembers.csv"
$CSVPath2 = "C:\AllGroupNoOwner.csv"
$TodaysDate = (Get-Date)

# 连接Exchange Online
Connect-ExchangeOnline -ShowBanner:$False
 
# 若CSV文件存在则删除
If(Test-Path $CSVPath) { Remove-Item $CSVPath}
If(Test-Path $CSVPath2) { Remove-Item $CSVPath2}

# 初始化数组存储所有组的结果
$groupResults = @()

# 查询无所有者的M365组
$o365GroupsNO=Get-UnifiedGroup | Where-Object {-Not $_.ManagedBy}
ForEach ($Group in $o365GroupsNO)
{
    $CheckDate=(Get-MailboxFolderStatistics -Identity $Group.DisplayName -IncludeOldestAndNewestItems -FolderScope Inbox).NewestItemReceivedDate
    $Members=(Get-UnifiedGroupLinks -Identity $Group.DisplayName -LinkType Members) -join '; '

    # 屏幕输出（保留原有逻辑）
    Write-Host "Group Name:" $Group.DisplayName -ForegroundColor Green
    Write-Host "Owner:" $Group.ManagedByDetails -ForegroundColor Red
    Write-Host "Name:" $Group.DisplayName -ForegroundColor Yellow
    Write-Host "Access:" $Group.AccessType -ForegroundColor Yellow
    Write-Host "Created:" $Group.WhenCreated -ForegroundColor White
    Write-Host "Changed:" $Group.WhenChanged -ForegroundColor White
    Write-Host "Newest Inbox Traffic:" $CheckDate -ForegroundColor White
    Write-Host "Group Member Count:" $Group.GroupMemberCount -ForegroundColor Gray
    Write-Host "Group Members:" $Members -ForegroundColor Gray
    Write-Host "Ext Group Member Count:" $Group.GroupExternalMemberCount -ForegroundColor Gray
    Write-Host "Dynamic Membership:" $Group.IsMembershipDynamic -ForegroundColor Gray
    Write-Host "SharePoint URL:" $Group.SharePointSiteUrl -ForegroundColor Gray
    Write-Host "SharePoint Document URL:" $Group.SharePointDocumentsUrl -ForegroundColor Gray
    Write-Host "Notes:" $Group.Notes -ForegroundColor Gray

    # 创建自定义对象，映射所有需要导出的字段并设置友好列名
    $groupInfo = [PSCustomObject]@{
        "DisplayName" = $Group.DisplayName
        "AccessType" = $Group.AccessType
        "WhenCreated" = $Group.WhenCreated
        "WhenChanged" = $Group.WhenChanged
        "Newest Inbox Traffic" = $CheckDate  # 自定义列名代替$CheckDate
        "GroupOwnerCount" = $Group.GroupOwnerCount
        "GroupMemberCount" = $Group.GroupMemberCount
        "Group Members" = $Members  # 自定义列名代替$Members
        "GroupExternalMemberCount" = $Group.GroupExternalMemberCount
        "IsMembershipDynamic" = $Group.IsMembershipDynamic
        "SharePointSiteUrl" = $Group.SharePointSiteUrl
        "SharePointDocumentsUrl" = $Group.SharePointDocumentsUrl
        "Notes" = $Group.Notes
    }

    # 将当前组的对象加入结果数组
    $groupResults += $groupInfo
}

# 一次性导出所有结果到CSV文件
$groupResults | Export-CSV $CSVPath2 -NoTypeInformation -Force

# 断开Exchange Online连接
Disconnect-ExchangeOnline -Confirm:$False

关键改进说明 #

1. 数组收集结果：用数组$groupResults统一存储所有组的自定义对象，最后一次性导出，比循环内逐行追加更高效且避免文件锁定问题
2. 自定义对象映射：通过[PSCustomObject]直接定义需要导出的字段，可自由设置友好列名，确保CSV文件中的列名符合Excel展示需求
3. 修正路径错误：调整Remove-Item的目标路径，避免误删无关文件
4. 导出逻辑调整：将导出操作移至循环外部，确保所有目标组的数据都能被正确写入CSV

内容的提问来源于stack exchange，提问作者Michael Frost