Hello,
I’m trying to automate a simple message to go out at the beginning of each week to tell people who is on call. I’m getting stuck at combining the 2 results of an excel and selecting who is on call.
Forgive the crudeness of the script. It’s just the hoops I’m going through to get the output I want from the excel. Just working with what I have since we are using a calendar in an excel table.
$excelDate = get-date -UFormat %B
$oncallDate = get-date -format "dd"
$oncallDate = $oncallDate.ToString().Replace("0", "")
$excelName = Import-Excel "C:\Users\Documents\RemedyReports\Main\On Call Schedule.xlsx" -WorksheetName $excelDate -StartRow 4 | select @{n="Employee";e={$_."employee name"}}, @{n="Date";e={$_.$oncallDate}} |
where {($_.employee -notlike "February*") -and ($_.employee -ne $null) }
foreach ($user in ($excelName).Employee) {
if (($excelName).Date -ne $null) {
Write-Host "$user is on call"
} elseif (($excelName).Date -eq $null) {
Write-Host "$user is not on call"
}
}
The variables as is are getting me to $excelName variable output as below;
PS C:\Users\> $excelName
Employee Date
-------- ----
User1
User2 O
User3
User4 O
User5
User6
User7
User8
User9 O
User10
User11
The output gives me the employee name and grabs the O that’s in the excel column for when that person is on call.
What’s not working is the output of the foreach command.
I am getting the below output;
User1 is on call
User2 is on call
User3 is on call
User4 is on call
User5 is on call
User6 is on call
User7 is on call
User8 is on call
User9 is on call
User10 is on call
User11 is on call
I am probably missing something simple, and if so, I’m attributing it to lack of sleep from writing scripts this week. Any help would be appreciated.
I actually didn’t get your logic. Could you share a (sanitized) CSV representation of your Excel data and the expected output? (both formatted as code please)
I’m just going to have to tell you, because exporting isn’t doing anything justice. if seeing this and giving the reasons makes you think what code you would like to see, i can try to pull it.
I can’t import row 3, or any of the columns because there are duplicate days in the row, and duplicate "O"s in the columns to make the output of a variable usable.
So my rationale is the script.
$excelDate is to get the worksheet name since that can be dynamic.
$oncallDate is to get the number of the day. In which case this is only going to run on monday. so the variable gets the day, returns the employee names and the entries on that day. I excluded February in the Employee column because it shows up in “employee name” column.
There’s a lot of configurations going on in the excel that almost make it impossible to be importable and exportable. So the script was the solution i came up with to narrow it down to the info i needed.
For reference on the previous post about the outputs.
If i run;
Import-Excel "C:\Users\Documents\RemedyReports\Main\On Call Schedule.xlsx" -WorksheetName February | export-csv c:\temp\oncall.csv -NoTypeInformation
I get everything in column 1(A) and that’s it.
OnCall and Ticket Queue Schedule
--------------------------------
February
Employee Name
User
User
User
User
User
User
User
User
User
User
User
February Total
If i try to import multiple columns, start at row 1…
where the addition was the -and ($_.Date -ne $null) at the end of the Where statement.
that gave me the below output;
Employee Date
-------- ----
User1 O
User2 O
User3 O
So it returned the 3 users i needed who would be on call.
I then added | select -ExpandProperty Employee after the where statement to get just the users name so i can email that list out now.