cls

$domain = "contoso";
# Connection String Variables, including client specific ID and Tenant
$clientId = "c9b5eaab-40cf-4e97-98ba-822241c2088c"
#$dateTime = (Get-Date).toString("dd-MM-yyyy-hh-ss")
$invocation = (Get-Variable MyInvocation).Value
$directorypath = Split-Path $invocation.MyCommand.Path
# Set parameters
$csvPath = $directorypath + "\Sites.csv" # CSV should have a column 'SiteUrl'
$destiUrl = "https://$domain.sharepoint.com/sites/EDRM-SecretarysDepartment-BankRecordsManagement";
$destList = "OwnersMapping";

# Define users to exclude
$excludedUsers = @("surname, test", "Auckloo, Reshmee"")
# Output CSV for results
#$outputCsv = $directorypath + ".\transformSpSites_IntoTeams_Channels_files" + $dateTime + ".csv"
#$results = @()

$adminSiteURL = "https://$domain-Admin.SharePoint.com"
Connect-PnPOnline -Url $adminSiteURL -ClientId $clientId

# Read sites from CSV
$sites = Import-Csv -Path $csvPath
$destconn = Connect-PnPOnline -Url $destiUrl -ClientId $clientId -ReturnConnection

foreach ($site in $sites) {
    $siteUrl = $site.Url
    Write-Host "Connecting to site: $siteUrl"
    $siteconn = Connect-PnPOnline -Url $siteUrl -ClientId $clientId -ReturnConnection

    $TeamName = "";
    $ChannelName = "";
    # Get the site title (Team Name)
    $site = (Get-PnPSite -Includes GroupId, RelatedGroupId , RootWeb.Title -Connection $siteconn)
    Write-Host "Team Name (Site Title): $($site.RootWeb.Title)"

    If($site.GroupId -ne [Guid]::Empty )
    {
        $TeamName = $site.RootWeb.Title;
        $ChannelName = "General";
        $owners = (Get-PnPTeamsUser -Team $TeamName -Role Owner -ErrorAction SilentlyContinue| Where-Object { $excludedUsers -notcontains $_.DisplayName } | select-Object -ExpandProperty DisplayName) -join ";" ;
    }
    elseif($site.RelatedGroupId)
    {
        $TeamName = (Get-PnPMicrosoft365Group -Identity $site.RelatedGroupId).DisplayName;
        $ChannelName = $site.RootWeb.Title -Replace "$TeamName-","";
        $owners = (Get-PnPGroupMember -Group (Get-PnPGroup -AssociatedOwnerGroup -Connection $siteconn) -Connection $siteconn  | Where-Object { $excludedUsers -notcontains $_.Title }| select-object -ExpandProperty Title ) -join ";"
    }
    
    
        # Add item to SharePoint list
        Add-PnPListItem -List $destList -Values @{
            Title   = $TeamName
            Channel = $ChannelName
            Owners  = $owners
            SiteUrl = $siteUrl
        } -Connection $destconn
}

# Export results to CSV
#$results | Export-Csv -Path $outputCsv