To streamline a routine work at SPG, I used Microsoft Copilot for this. This was the prompt I used:<\/p>\n\n\n\n
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.<\/code><\/pre>\n\n\n\nI was asked to install a new module: Install-Module ImportExcel -Scope CurrentUser<\/em><\/p>\n\n\n\nPS: 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).<\/p>\n\n\n\n
And I got this code from M365 Copilot Chat:<\/p>\n\n\n\n
\n#Requires -Modules ImportExcel\n\nparam(\n [Parameter(Mandatory)] [string] $ListFile,\n [Parameter(Mandatory)] [string] $ListSheet,\n [Parameter(Mandatory)] [string] $ListKeyColumn,\n\n [Parameter(Mandatory)] [string] $TargetFile,\n [Parameter(Mandatory)] [string] $TargetSheet,\n [Parameter(Mandatory)] [string] $TargetKeyColumn\n)\n\n# ---- Settings: columns to update in the target (constants) ----\n$ConstantUpdates = @{\n \"Status\" = \"Disabled\"\n \"UpdatedOn\" = (Get-Date).ToString(\"yyyy-MM-dd\")\n}\n\n# ---- Safety: create a backup first ----\n$backup = \"$TargetFile.bak_{0:yyyyMMdd_HHmmss}\" -f (Get-Date)\nCopy-Item $TargetFile $backup -Force\nWrite-Host \"Backup created: $backup\"\n\n# ---- Read list keys ----\n$listRows = Import-Excel -Path $ListFile -WorksheetName $ListSheet\nif (-not $listRows) { throw \"List sheet returned no rows.\" }\n\n$keys = $listRows |\n ForEach-Object { $_.$ListKeyColumn } |\n Where-Object { $_ -ne $null -and \"$_\".Trim() -ne \"\" } |\n ForEach-Object { \"$_\".Trim() } |\n Select-Object -Unique\n\nif ($keys.Count -eq 0) { throw \"No keys found in column '$ListKeyColumn'.\" }\n\n# Build a fast lookup set (case-insensitive by default)\n$keySet = [System.Collections.Generic.HashSet[string]]::new([System.StringComparer]::OrdinalIgnoreCase)\n$null = $keys | ForEach-Object { $keySet.Add($_) }\n\n# ---- Open target workbook and update in place ----\n$pkg = Open-ExcelPackage -Path $TargetFile\ntry {\n $ws = $pkg.Workbook.Worksheets[$TargetSheet]\n if (-not $ws) { throw \"Target sheet '$TargetSheet' not found in '$TargetFile'.\" }\n\n $endRow = $ws.Dimension.End.Row\n $endCol = $ws.Dimension.End.Column\n\n # Map headers (row 1) to column indexes\n $headerToCol = @{}\n for ($c = 1; $c -le $endCol; $c++) {\n $h = $ws.Cells[1, $c].Text.Trim()\n if ($h) { $headerToCol[$h] = $c }\n }\n\n if (-not $headerToCol.ContainsKey($TargetKeyColumn)) {\n throw \"Target key column '$TargetKeyColumn' not found in header row.\"\n }\n\n # Ensure update columns exist\n foreach ($colName in $ConstantUpdates.Keys) {\n if (-not $headerToCol.ContainsKey($colName)) {\n throw \"Update column '$colName' not found in target header row.\"\n }\n }\n\n $keyColIndex = $headerToCol[$TargetKeyColumn]\n\n $matched = 0\n $updated = 0\n\n for ($r = 2; $r -le $endRow; $r++) {\n $key = $ws.Cells[$r, $keyColIndex].Text.Trim()\n if (-not $key) { continue }\n\n if ($keySet.Contains($key)) {\n $matched++\n\n foreach ($colName in $ConstantUpdates.Keys) {\n $colIndex = $headerToCol[$colName]\n $newValue = $ConstantUpdates[$colName]\n\n # Only write if different\n $current = $ws.Cells[$r, $colIndex].Text\n if ($current -ne \"$newValue\") {\n $ws.Cells[$r, $colIndex].Value = $newValue\n $updated++\n }\n }\n }\n }\n\n Close-ExcelPackage -ExcelPackage $pkg -Save\n\n Write-Host \"Matches found: $matched\"\n Write-Host \"Cells updated: $updated\"\n}\ncatch {\n # If anything fails, close without saving to avoid partial writes\n Close-ExcelPackage -ExcelPackage $pkg -NoSave\n throw\n}\n<\/code><\/pre>\n\n\n\nAnd I only needed to made a few modifications:<\/p>\n\n\n\n
\n#Requires -Modules ImportExcel\n\n$ListFile = \"C:\\Users\\original.xlsx\"\n$ListSheet = \"sheet1\"\n$ListKeyColumn = \"User principal name\"\n\n$TargetFile = \"C:\\Users\\target.xlsx\"\n$TargetSheet = \"Users\"\n$TargetKeyColumn = \"Email\"\n\n\n\n# ---- Settings: columns to update in the target (constants) ----\n$ConstantUpdates = @{\n # \"Status\" = \"Disabled\"\n \"DOT\" = (Get-Date).ToString(\"d\")\n}\n\n# ---- Safety: create a backup first ----\n#$backup = \"$TargetFile.bak_{0:yyyyMMdd_HHmmss}\" -f (Get-Date)\n#Copy-Item $TargetFile $backup -Force\n#Write-Host \"Backup created: $backup\"\n\n# ---- Read list keys ----\n$listRows = Import-Excel -Path $ListFile -WorksheetName $ListSheet\nif (-not $listRows) { throw \"List sheet returned no rows.\" }\n\n$keys = $listRows |\n ForEach-Object { $_.$ListKeyColumn } |\n Where-Object { $_ -ne $null -and \"$_\".Trim() -ne \"\" } |\n ForEach-Object { \"$_\".Trim() } |\n Select-Object -Unique\n\nif ($keys.Count -eq 0) { throw \"No keys found in column '$ListKeyColumn'.\" }\n\n# Build a fast lookup set (case-insensitive by default)\n$keySet = [System.Collections.Generic.HashSet[string]]::new([System.StringComparer]::OrdinalIgnoreCase)\n$null = $keys | ForEach-Object { $keySet.Add($_) }\n\n# ---- Open target workbook and update in place ----\n$pkg = Open-ExcelPackage -Path $TargetFile\ntry {\n $ws = $pkg.Workbook.Worksheets[$TargetSheet]\n if (-not $ws) { throw \"Target sheet '$TargetSheet' not found in '$TargetFile'.\" }\n\n $endRow = $ws.Dimension.End.Row\n $endCol = $ws.Dimension.End.Column\n\n # Map headers (row 1) to column indexes\n $headerToCol = @{}\n for ($c = 1; $c -le $endCol; $c++) {\n $h = $ws.Cells[1, $c].Text.Trim()\n if ($h) { $headerToCol[$h] = $c }\n }\n\n if (-not $headerToCol.ContainsKey($TargetKeyColumn)) {\n throw \"Target key column '$TargetKeyColumn' not found in header row.\"\n }\n\n # Ensure update columns exist\n foreach ($colName in $ConstantUpdates.Keys) {\n if (-not $headerToCol.ContainsKey($colName)) {\n throw \"Update column '$colName' not found in target header row.\"\n }\n }\n\n $keyColIndex = $headerToCol[$TargetKeyColumn]\n\n $matched = 0\n $updated = 0\n\n for ($r = 2; $r -le $endRow; $r++) {\n $key = $ws.Cells[$r, $keyColIndex].Text.Trim()\n if (-not $key) { continue }\n\n if ($keySet.Contains($key)) {\n $matched++\n\n foreach ($colName in $ConstantUpdates.Keys) {\n $colIndex = $headerToCol[$colName]\n $newValue = $ConstantUpdates[$colName]\n\n # Only write if different\n $current = $ws.Cells[$r, $colIndex].Text\n if ([string]::IsNullOrWhiteSpace($current) -and ($current -ne \"$newValue\")) {\n $ws.Cells[$r, $colIndex].Value = [datetime]($newValue)\n $ws.Cells[$r, $colIndex].Style.HorizontalAlignment = \"Right\"\n $updated++\n }\n }\n }\n }\n\n Close-ExcelPackage -ExcelPackage $pkg\n\n Write-Host \"Matches found: $matched\"\n Write-Host \"Cells updated: $updated\"\n}\ncatch {\n # If anything fails, close without saving to avoid partial writes\n Close-ExcelPackage -ExcelPackage $pkg -NoSave\n throw\n}<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"To streamline a routine work at SPG, I used Microsoft Copilot for this. This was the prompt I used: I was asked to install a new module: Install-Module ImportExcel -Scope CurrentUser PS: This doesn't update anything on sharepoint. So I … Continue reading