Forum Discussion

Matthew Ruser's avatar
Matthew Ruser
Copper Contributor
Jun 01, 2021

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

Resources