Need help with another for each loop

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)

You want the actual excel document? How do you format that as code on here?

No. I would like a few columns and rows to understand the structure of your imput data and how you want to process it to get a certain output.

I can post a screenshot if that’s ok. Otherwise, i feel like it still wont be clear.
That way i can give details behind the variable needs.

From a screenshot I would need to type the data if I want to play with it. Just export it to a CSV and post A FEW rows of it … :man_shrugging:t4:

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…

Import-Excel "C:\Users\Documents\RemedyReports\Main\On Call Schedule.xlsx" -WorksheetName February -ImportColumns @(1,2,3,4,5,6,7,8,9) -StartRow 1 | export-csv c:\temp\oncall.csv -NoTypeInformation

I get the same output. and i am closing out my session and opening a new one just to make sure there’s nothing cached.

I took out the foreach statement, and added a line on the $excelName variable to basically look for the same thing and it works.

$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 -EndRow 21 | select @{n="Employee";e={$_."employee name"}}, @{n="Date";e={$_.$oncallDate}} | 
where {($_.employee -notlike "$exceldate*") -and ($_.employee -ne $null) -and ($_.Date -ne $null) } 

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.

Glad to hear that you’ve figured it out by yourself. :+1:t4:

And thanks for sharing

1 Like

Thanks Olaf. I know I wasn’t too helpful with the details because it just wasn’t importing the info very well, but glad I got it worked out too.

Continue doing what you do. I’ve learned a lot from reading your posts.