Forum Discussion
Matthew Ruser
Jun 01, 2021Copper Contributor
How to get Variable Data to columns of a CSV - SharePoint Online
Hi,
I've been having an issue with getting data from variables to a CSV file. I can run this script and get output data to the screen without issue, but I cannot get that data piped or written to rows of my CSV file. I just keep getting a CSV file with headers and blank data. I've tried using Export-CSV, Add-Content and now I'm trying to use an Array and piping that to the CSV. PLEASE HELP! I've spent hours and days trying to get this to work.
Background:
I've left the cmlets and comments of the commands I tried but are not working.
I'm running the PowerShell script against SharePoint Online and all site collections which returns:
- $Site.URL = The URL of each Site Collection
- $Group = The SharePoint groups that have "Full Control" at that site collection
- $User = The members of users email in that group What does the script do
My CSV file has 3 columns
- GroupName (A1)
- URL (B1)
- Users (C1)
$AdminCenterURL = "https:\\mytentant-admin.sharepoint.com"
$FilePath = "C:\Scripts\PS\SharePoint\Reports\"
$CSVReport = "Group_UserReport.csv"
$CSV_OutFile = $FilePath + $CSVReport
Add-Content -Path $CSV_OutFile -Value '"GroupName","URL","Users"'
#Connect to SharePoint Online
Connect-SPOService -url $AdminCenterURL -Credential (Get-Credential)
#Get all Site collections
$Sites = Get-SPOSite -Limit All
#Loop through site collections
ForEach($Site in $Sites)
{
Write-host -f Cyan "Searching site: $($Site.URL)"
#Get all Groups from the site permissions
$SiteGroups = Get-SPOSiteGroup -Site $Site | Where-Object { $_.Roles -ne $NULL -and $_.Users -ne $NULL}
#Get Group info and members that have site owners permissions
ForEach ($Group in $SiteGroups)
{
If($Group.Roles.Contains("Full Control"))
{
Write-Host -f Yellow $Group.Title
If(0 -lt $Group.Users.Count)
{
#Get each member of the group
ForEach($User in $Group.Users | Where-Object{$_.contains("@")}) #Exclude system Users
{
#This line does not add any content to the CSV File (blank file with headers)
#Add-Content -Path $CSV_OutFile -Value '$Group.Title, $Site.URL, $User'
write-host -f Green $User
}
}
else
{
# I've try this line and there are no entries add to the CVS file (blank file with just headers
#Add-Content -Path $CSV_OutFile -Value $Group.Title, $Site.URL, $User # this line does not work and writes nothing to the file
# This line does not add anything to the CSV again no data just a file with headers.
#Export-Csv -Path $CSV_OutFile -OutVariable $Group.Title, $Site.URL, $User -NoTypeInformation -Append
# I added this Array to see if I can get data because the line above is blank
$Group_user_info = @([PSCustomObject]@{
GroupName = $Group.Title
URL = $Site.Url
User = $User
})
# This next line does not seem to contain data either.
#$Group_user_info | Export-Csv -Path $CSV_OutFile -NoTypeInformation -Append
# I retried this line again and tried to add the Array data to the
Add-Content -Path $CSV_OutFile -Value $Group_user_info[0], $Group_user_info[1], $Group_user_info[2]
}
}
}
}
No RepliesBe the first to reply