My Journal

To streamline a routine work at SPG, I used Microsoft Copilot for this. This was the prompt I used:

write a powershell script that takes a list from an excel sheet column, and match every value on that list with another excel sheet to update some values of it in the matching rows.

I was asked to install a new module: Install-Module ImportExcel -Scope CurrentUser

PS: This doesn't update anything on sharepoint. So I actually would need to copy the sharepoint file to PC to be the target file and then I copy paste the PowerShell updated column from the local file to the sharepoint file (not replacing the entire file).

And I got this code from M365 Copilot Chat:

#Requires -Modules ImportExcel

param(
    [Parameter(Mandatory)] [string] $ListFile,
    [Parameter(Mandatory)] [string] $ListSheet,
    [Parameter(Mandatory)] [string] $ListKeyColumn,

    [Parameter(Mandatory)] [string] $TargetFile,
    [Parameter(Mandatory)] [string] $TargetSheet,
    [Parameter(Mandatory)] [string] $TargetKeyColumn
)

# ---- Settings: columns to update in the target (constants) ----
$ConstantUpdates = @{
    "Status"    = "Disabled"
    "UpdatedOn" = (Get-Date).ToString("yyyy-MM-dd")
}

# ---- Safety: create a backup first ----
$backup = "$TargetFile.bak_{0:yyyyMMdd_HHmmss}" -f (Get-Date)
Copy-Item $TargetFile $backup -Force
Write-Host "Backup created: $backup"

# ---- Read list keys ----
$listRows = Import-Excel -Path $ListFile -WorksheetName $ListSheet
if (-not $listRows) { throw "List sheet returned no rows." }

$keys = $listRows |
    ForEach-Object { $_.$ListKeyColumn } |
    Where-Object { $_ -ne $null -and "$_".Trim() -ne "" } |
    ForEach-Object { "$_".Trim() } |
    Select-Object -Unique

if ($keys.Count -eq 0) { throw "No keys found in column '$ListKeyColumn'." }

# Build a fast lookup set (case-insensitive by default)
$keySet = [System.Collections.Generic.HashSet[string]]::new([System.StringComparer]::OrdinalIgnoreCase)
$null = $keys | ForEach-Object { $keySet.Add($_) }

# ---- Open target workbook and update in place ----
$pkg = Open-ExcelPackage -Path $TargetFile
try {
    $ws = $pkg.Workbook.Worksheets[$TargetSheet]
    if (-not $ws) { throw "Target sheet '$TargetSheet' not found in '$TargetFile'." }

    $endRow = $ws.Dimension.End.Row
    $endCol = $ws.Dimension.End.Column

    # Map headers (row 1) to column indexes
    $headerToCol = @{}
    for ($c = 1; $c -le $endCol; $c++) {
        $h = $ws.Cells[1, $c].Text.Trim()
        if ($h) { $headerToCol[$h] = $c }
    }

    if (-not $headerToCol.ContainsKey($TargetKeyColumn)) {
        throw "Target key column '$TargetKeyColumn' not found in header row."
    }

    # Ensure update columns exist
    foreach ($colName in $ConstantUpdates.Keys) {
        if (-not $headerToCol.ContainsKey($colName)) {
            throw "Update column '$colName' not found in target header row."
        }
    }

    $keyColIndex = $headerToCol[$TargetKeyColumn]

    $matched = 0
    $updated = 0

    for ($r = 2; $r -le $endRow; $r++) {
        $key = $ws.Cells[$r, $keyColIndex].Text.Trim()
        if (-not $key) { continue }

        if ($keySet.Contains($key)) {
            $matched++

            foreach ($colName in $ConstantUpdates.Keys) {
                $colIndex = $headerToCol[$colName]
                $newValue = $ConstantUpdates[$colName]

                # Only write if different
                $current = $ws.Cells[$r, $colIndex].Text
                if ($current -ne "$newValue") {
                    $ws.Cells[$r, $colIndex].Value = $newValue
                    $updated++
                }
            }
        }
    }

    Close-ExcelPackage -ExcelPackage $pkg -Save

    Write-Host "Matches found: $matched"
    Write-Host "Cells updated: $updated"
}
catch {
    # If anything fails, close without saving to avoid partial writes
    Close-ExcelPackage -ExcelPackage $pkg -NoSave
    throw
}

And I only needed to made a few modifications:

#Requires -Modules ImportExcel

$ListFile = "C:\Users\original.xlsx"
$ListSheet = "sheet1"
$ListKeyColumn = "User principal name"

$TargetFile = "C:\Users\target.xlsx"
$TargetSheet = "Users"
$TargetKeyColumn = "Email"



# ---- Settings: columns to update in the target (constants) ----
$ConstantUpdates = @{
    # "Status"    = "Disabled"
    "DOT" = (Get-Date).ToString("d")
}

# ---- Safety: create a backup first ----
#$backup = "$TargetFile.bak_{0:yyyyMMdd_HHmmss}" -f (Get-Date)
#Copy-Item $TargetFile $backup -Force
#Write-Host "Backup created: $backup"

# ---- Read list keys ----
$listRows = Import-Excel -Path $ListFile -WorksheetName $ListSheet
if (-not $listRows) { throw "List sheet returned no rows." }

$keys = $listRows |
    ForEach-Object { $_.$ListKeyColumn } |
    Where-Object { $_ -ne $null -and "$_".Trim() -ne "" } |
    ForEach-Object { "$_".Trim() } |
    Select-Object -Unique

if ($keys.Count -eq 0) { throw "No keys found in column '$ListKeyColumn'." }

# Build a fast lookup set (case-insensitive by default)
$keySet = [System.Collections.Generic.HashSet[string]]::new([System.StringComparer]::OrdinalIgnoreCase)
$null = $keys | ForEach-Object { $keySet.Add($_) }

# ---- Open target workbook and update in place ----
$pkg = Open-ExcelPackage -Path $TargetFile
try {
    $ws = $pkg.Workbook.Worksheets[$TargetSheet]
    if (-not $ws) { throw "Target sheet '$TargetSheet' not found in '$TargetFile'." }

    $endRow = $ws.Dimension.End.Row
    $endCol = $ws.Dimension.End.Column

    # Map headers (row 1) to column indexes
    $headerToCol = @{}
    for ($c = 1; $c -le $endCol; $c++) {
        $h = $ws.Cells[1, $c].Text.Trim()
        if ($h) { $headerToCol[$h] = $c }
    }

    if (-not $headerToCol.ContainsKey($TargetKeyColumn)) {
        throw "Target key column '$TargetKeyColumn' not found in header row."
    }

    # Ensure update columns exist
    foreach ($colName in $ConstantUpdates.Keys) {
        if (-not $headerToCol.ContainsKey($colName)) {
            throw "Update column '$colName' not found in target header row."
        }
    }

    $keyColIndex = $headerToCol[$TargetKeyColumn]

    $matched = 0
    $updated = 0

    for ($r = 2; $r -le $endRow; $r++) {
        $key = $ws.Cells[$r, $keyColIndex].Text.Trim()
        if (-not $key) { continue }

        if ($keySet.Contains($key)) {
            $matched++

            foreach ($colName in $ConstantUpdates.Keys) {
                $colIndex = $headerToCol[$colName]
                $newValue = $ConstantUpdates[$colName]

                # Only write if different
                $current = $ws.Cells[$r, $colIndex].Text
                if ([string]::IsNullOrWhiteSpace($current) -and ($current -ne "$newValue")) {
                    $ws.Cells[$r, $colIndex].Value = [datetime]($newValue)
                    $ws.Cells[$r, $colIndex].Style.HorizontalAlignment = "Right"
                    $updated++
                }
            }
        }
    }

    Close-ExcelPackage -ExcelPackage $pkg

    Write-Host "Matches found: $matched"
    Write-Host "Cells updated: $updated"
}
catch {
    # If anything fails, close without saving to avoid partial writes
    Close-ExcelPackage -ExcelPackage $pkg -NoSave
    throw
}