PowerShell: using ConvertTo-Html multiple times, table render failure from CMD

Question

My script runs fine in ISE, but when scheduled in the Task Scheduler and run as "PowerShell -File //path/email.ps1" the first table fails to render.

$CommonPath = "${Base_Dir}\..\PowerShell_Tools\Common\Common.ps1"
Import-Module -Name $CommonPath -Force

$SQLserver = 'sql_srv'

$Yesterday =  get-date(get-date) -f MM/dd/yyyy

$To = "[email protected]"
$From = "[email protected]"
$email_Subject = "Status $Yesterday"

#CSS
$Style = @"
<div style="overflow-x:auto;">
<style>
table {
  border-collapse: collapse;
}

th, td {
  text-align: left;
  padding: 8px;
}

th {border-width: 1px;
    border-color: black;
    background-color: #E20074;
}

</style>
"@

#Query to populate a table of data health metrics
$query1 = `
"USE [Projects]
GO

SELECT FORMAT([Metric1] * 1.0 / [Total_Rows], 'P') [Metric1 Health]
      ,FORMAT([Metric2] * 1.0 / [Total_Rows], 'P') [Metric2 Health]
      ,FORMAT([Metric3] * 1.0 / [Total_Rows], 'P') [Metric3 Health]
      ,FORMAT([Metric4] * 1.0 / [Total_Rows], 'P') [Metric4 Health]
      ,FORMAT([Metric5] * 1.0 / [Total_Rows], 'P') [Metric5 Health]
  FROM [Projects].[dbo].[tbl_Status]
WHERE [Date_Of] = '$Yesterday'
"

Write-Output "$(Get-Date -Format yyyy-MM-dd.hh:mm:ss): Querying for data health metrics."
#Query the Projects db
$output1 = $(invoke-sqlcmd -serverinstance $SQLserver -database "Projects" -Query $query1) 

#Begin Transpose block
$transp_Output = New-Object System.Data.DataTable

$newcol = New-Object system.Data.DataColumn Metric,([string]); $transp_Output.columns.add($newcol)
$newcol = New-Object system.Data.DataColumn Health,([string]); $transp_Output.columns.add($newcol)

#Transpose 
foreach($col in $output.Table.Columns){
       $row = $transp_Output.NewRow()
       $row.Metric = $col.ColumnName
       $row.Health = $output.$($col.ColumnName)
       $transp_Output.Rows.Add($row) 
}
#End Transpose Block

#Query to populate a table of data health metrics
$query2 = `
"USE [Projects]
GO

SELECT FORMAT([Modified], 'MM/dd/yyyy') [Modified]
      ,[Editor]
      ,COUNT(*) [Count_Of]
  FROM [Projects].[dbo].[Cumulative]
WHERE Editor != 'Microsoft.SharePoint.Client.FieldUserValue'
  AND [Modified] > DATEADD(DAY, -8, GETDATE())
GROUP BY FORMAT([Modified], 'MM/dd/yyyy')
      ,[Editor]
ORDER BY FORMAT([Modified], 'MM/dd/yyyy') DESC
"

Write-Output "$(Get-Date -Format yyyy-MM-dd.hh:mm:ss): Querying for edit."
#Query the Projects db
$output2 = $(invoke-sqlcmd -serverinstance $SQLserver -database "Projects" -Query $query2) 

#Email output
#Build the tables from the powershell objects
$table1 = $transp_Output | Select * -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors | ConvertTo-Html -Fragment
$table2 = $Output2 | Select * -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors | ConvertTo-Html -Fragment

$body = "$style<p>Current data health</p>

<p>$table1</p>

<p><H2>Last 7 days of edits:</H2></p>
<p>$table2</p>

<p>This message was generated automatically.</p>"
    
Send-Email -To $To `
            -From $From `
            -Subject $email_Subject `
            -Body $body `
            -HTML $true

The results are that the HTML is duplicated for both tables, including CSS:

<p>Current data health</p>

<p><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <div
 style="overflow-x:auto;">
<style>
table {
  border-collapse: collapse;
}

th, td {
  text-align: left;
  padding: 8px;
}

th {border-width: 1px;
    border-color: black;
    background-color: #E20074;
}

</style> </head><body> <H2>Status 02/24/2022</H2> <table> <colgroup><col/><col/></colgroup> <tr><th>Metric</th><th>Health</th></tr> <tr><td>Metric1 Health</td><td>0.00 %</td></tr> <tr><td>Metric2 Health</td><td>8.06 %</td></tr> <tr><td>Metric3 Health</td><td>8.06 %</td></tr> <tr><td>Metric4 Health</td><td>4.84 %</td></tr>
 <tr><td>Metric5 Health</td><td>96.77 %</td></tr> </table> </body></html></p>
<p><H2>Last 7 days of edits:</H2> <table> <colgroup><col/><col/><col/></colgroup> <tr><th>Modified</th><th>Editor</th><th>Count_Of</th></tr> <tr><td>02/23/2022</td><td>n8.</td><td>1</td></tr> <tr><td>02/21/2022</td><td>n8.</td><td>1</td></tr> <tr><td>02/19/2022</td><td>n8.</td><td>32</td></tr> <tr><td>02/18/2022</td><td>n8.</td><td>63</td></tr> </table> </body></html></p>

<p>This message was generated automatically.</p>

This ends up rendering OK when I run it from the ISE but the 2nd table's HTML will override the first when the ps1 is run from command prompt. Do I need to load a library? I found this apparently relative topic. Alternatively, can I run with ISE libraries loaded?

So what I'm looking for is best practice on how to correctly generate HTML and CSS using PowerShell in the case where I have multiple tables to render: -Fragment — Santiago Squarzon
– Santiago Squarzon, Commented Feb 24, 2022 at 21:59
I've just been working on this and unfortunately I misdiagnosed my problem, will edit — n8.
– n8., Commented Feb 24, 2022 at 22:02

Santiago Squarzon · Accepted Answer · 2022-02-24 22:25:54Z

I don't have the data you're pulling from your DB, can only show you how to combine your tables to generate your report using ConvertTo-Html. Here is an example using the tables from your HTML.

$Style = @"
<div style="overflow-x:auto;">
<style>
table {
  border-collapse: collapse;
}

th, td {
  text-align: left;
  padding: 8px;
}

th {
  border-width: 1px;
  border-color: black;
  background-color: #E20074;
}

footer {
  left: 0;
  bottom: 0;
  width: 100%;
}
</style>
"@

[string]$table1 = @'
Metric  Health
Metric1 Health  0.00 %
Metric2 Health  8.06 %
Metric3 Health  8.06 %
Metric4 Health  4.84 %
Metric5 Health  96.77 %
'@ |
ConvertFrom-Csv -Delimiter "`t" |
ConvertTo-Html -Fragment

[string]$table2 = @'
Modified    Editor  Count_Of
02/23/2022  n8. 1
02/21/2022  n8. 1
02/19/2022  n8. 32
02/18/2022  n8. 63
'@ |
ConvertFrom-Csv -Delimiter "`t" |
ConvertTo-Html -Fragment

$h1 = '<h1>Current Data Health</h1>'
$h2 = '<h3>Status 02/24/2022</h3>'
$h3 = '<h1>Last 7 days of edits:</h1>'
$footer = '<footer>This message was generated automatically.</footer>'

$params = @{
    Head = $Style
    Body = $h1, $h2, $table1, $h3, $table2
    PostContent = $footer
}

ConvertTo-Html @params

Code above generates the following HTML:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
  <div style="overflow-x:auto;">
    <style>
      table {
        border-collapse: collapse;
      }
      
      th,
      td {
        text-align: left;
        padding: 8px;
      }
      
      th {
        border-width: 1px;
        border-color: black;
        background-color: #E20074;
      }
      
      footer {
        left: 0;
        bottom: 0;
        width: 100%;
      }
    </style>
</head>

<body>
  <h1>Current Data Health</h1>
  <h3>Status 02/24/2022</h3>
  <table>
    <colgroup>
      <col/>
      <col/>
    </colgroup>
    <tr>
      <th>Metric</th>
      <th>Health</th>
    </tr>
    <tr>
      <td>Metric1 Health</td>
      <td>0.00 %</td>
    </tr>
    <tr>
      <td>Metric2 Health</td>
      <td>8.06 %</td>
    </tr>
    <tr>
      <td>Metric3 Health</td>
      <td>8.06 %</td>
    </tr>
    <tr>
      <td>Metric4 Health</td>
      <td>4.84 %</td>
    </tr>
    <tr>
      <td>Metric5 Health</td>
      <td>96.77 %</td>
    </tr>
  </table>
  <h1>Last 7 days of edits:</h1>
  <table>
    <colgroup>
      <col/>
      <col/>
      <col/>
    </colgroup>
    <tr>
      <th>Modified</th>
      <th>Editor</th>
      <th>Count_Of</th>
    </tr>
    <tr>
      <td>02/23/2022</td>
      <td>n8.</td>
      <td>1</td>
    </tr>
    <tr>
      <td>02/21/2022</td>
      <td>n8.</td>
      <td>1</td>
    </tr>
    <tr>
      <td>02/19/2022</td>
      <td>n8.</td>
      <td>32</td>
    </tr>
    <tr>
      <td>02/18/2022</td>
      <td>n8.</td>
      <td>63</td>
    </tr>
  </table>
  <table>
  </table>
  <footer>This message was generated automatically.</footer>
</body>
</html>

I'm gonna go ahead and give you the victory here because the bug ended up being outside of my stated concerns. Up there in my Transpose block I'm loading values from a non-existent $output table instead of from $output1. $transp_Output persisted in memory from development, so I was getting good results from that but the table was empty when I ran from scratch. Thanks for throwing your time at it!
@n8. that explains why you were getting no results on powershell CLI but you did on ISE hehe

Collectives™ on Stack Overflow

PowerShell: using ConvertTo-Html multiple times, table render failure from CMD

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related