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
}