cls
# Connection String Variables, including client specific ID and Tenant
$clientId = "xxxxxxx"
#$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://contoso.sharepoint.com/sites/test";
$destList = "OwnersMapping";
# Define users to exclude
$excludedUsers = @("tom, roy", "mary, jane")
$excludedusersemails = @("tom.roy@contoso.co.uk", "mary.jane@contoso.co.uk")
# Output CSV for results
#$outputCsv = $directorypath + ".\transformSpSites_IntoTeams_Channels_files" + $dateTime + ".csv"
#$results = @()

$domain = "contoso";
$adminSiteURL = "https://$domain.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


# Normalize the site URL for comparison (case-insensitive, trim, remove trailing slash)
function Normalize-Url([string]$url) {
    if ([string]::IsNullOrWhiteSpace($url)) { return $null }
    $u = $url.Trim()
    if ($u.EndsWith("/")) { $u = $u.TrimEnd("/") }
    return $u.ToLowerInvariant()
}

$items = Get-PnPListItem -List $destList -PageSize 2000 -Fields "Team", "Channel", "SiteUrl" -Connection $destconn 

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

    $TeamName = "";
    $ChannelName = "";
    $owners= ""
    $ownersEmails= ""
    # Get the site title (Team Name)
    $siteUrlNorm = Normalize-Url $siteUrl
    # Safety: if siteUrl is missing, block the add and surface a helpful error
    if (-not $siteUrlNorm) {
        Write-Error "Site URL is empty or invalid. Cannot add item without 'Site'."
        return
    }
    # Filter existing items with Where-Object by normalized Site
$matches = $items | Where-Object {
    # PnP returns field values in FieldValues; fall back to indexer if needed
    $existingSite = $_.FieldValues["SiteUrl"]
    if (-not $existingSite) { $existingSite = $_["SiteUrl"] }

    $existingSiteNorm = Normalize-Url ([string]$existingSite)
    $existingSiteNorm -and ($existingSiteNorm -eq $siteUrlNorm)
}

if ($matches -and $matches.Count -gt 0) {
    Write-Host "Skipped: An item with Site '$siteUrl' already exists in '$destList'."
} else {
    $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 ";" ;
       $ownersEmails = (Get-PnPTeamsUser -Team $TeamName -Role Owner -ErrorAction SilentlyContinue| Where-Object { $excludedusersemails -notcontains $_.UserPrincipalName.ToLower() } | select-Object -ExpandProperty UserPrincipalName) -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 ";"
        $ownersEmails =  (Get-PnPGroupMember -Group (Get-PnPGroup -AssociatedOwnerGroup -Connection $siteconn) -Connection $siteconn  | Where-Object { $excludedusersemails -notcontains $_.Email }| select-object -ExpandProperty Email ) -join ";"
    }    
    # Add item to SharePoint list
        Add-PnPListItem -List $destList -Values @{
            Wave   = "5.1"
            Title = $TeamName
            Channel = $ChannelName
            Owners  = $owners
            OwnersEmail = $ownersEmails
            SiteUrl = $siteUrl
        } -Connection $destconn
    }
}