基于Google Apps Script的邮件表格条件样式开发需求：单元格值为Not Approved时对应行背景设为红色

Got it, let's tackle this problem step by step. Here's the complete implementation for your Google Apps Script HTML email with conditional row styling for the second table:

1. HTML Template with Conditional Styling #

Save this as an HTML file in your Apps Script project (e.g., EmailTemplate.html):

<!DOCTYPE html>
<html>
<head>
  <style>
    table, th, td { 
      border: 1px solid black;
      border-collapse: collapse; /* Optional: Makes borders look cleaner */
      padding: 8px;
    }
    th {
      background-color: #f2f2f2; /* Light gray for headers to improve readability */
    }
  </style>
</head>
<body>
  <!-- First Table -->
  <h3>Equipment Specifications (Table 1)</h3>
  <table>
    <thead>
      <tr>
        <th>Spreader</th>
        <th>Hoisting Speed</th>
        <th>Stiffness Class</th>
        <th>Hoist Drive Class</th>
        <th>Lifetime Load Cycles</th>
        <th>Q Class Classification</th>
        <th>Gantry Acceleration</th>
        <th>Trolley Acceleration</th>
        <th>Lifting Capacity SWL Single Mode</th>
        <th>Lifting Capacity SWL Twin Mode</th>
        <th>Eccentricity Longitudinal Percentage</th>
        <th>Container Weight Single Mode</th>
      </tr>
    </thead>
    <tbody>
      <? firstTableData.forEach(row => { ?>
        <tr>
          <? row.forEach(cell => { ?>
            <td><?= cell ?></td>
          <? }) ?>
        </tr>
      <? }) ?>
    </tbody>
  </table>

  <br>

  <!-- Second Table with Conditional Red Background for "Not Approved" Rows -->
  <h3>Approval Status (Table 2)</h3>
  <table>
    <thead>
      <tr>
        <!-- Reuse same headers as first table, adjust if your second table has different fields -->
        <th>Spreader</th>
        <th>Hoisting Speed</th>
        <th>Stiffness Class</th>
        <th>Hoist Drive Class</th>
        <th>Lifetime Load Cycles</th>
        <th>Q Class Classification</th>
        <th>Gantry Acceleration</th>
        <th>Trolley Acceleration</th>
        <th>Lifting Capacity SWL Single Mode</th>
        <th>Lifting Capacity SWL Twin Mode</th>
        <th>Eccentricity Longitudinal Percentage</th>
        <th>Container Weight Single Mode</th>
      </tr>
    </thead>
    <tbody>
      <? secondTableData.forEach(row => { 
        // Check if any cell in the current row contains "Not Approved"
        const hasRejection = row.some(cell => cell === "Not Approved");
      ?>
        <!-- Add inline style only if the row has a "Not Approved" cell -->
        <tr <?= hasRejection ? 'style="background-color: #ffcccc;"' : '' ?>>
          <? row.forEach(cell => { ?>
            <td><?= cell ?></td>
          <? }) ?>
        </tr>
      <? }) ?>
    </tbody>
  </table>
</body>
</html>

2. Server-Side Google Apps Script #

Add this to your .gs file to populate data, render the template, and send the email:

function sendApprovalEmail() {
  // Replace with your actual first table data
  const firstTableData = [
    ["Model X", "12 m/min", "Class 3", "Drive Type 2", "150000", "Q2", "0.6 m/s²", "0.7 m/s²", "55t", "95t", "1.8%", "32t"],
    ["Model Y", "10 m/min", "Class 2", "Drive Type 1", "100000", "Q1", "0.5 m/s²", "0.6 m/s²", "50t", "90t", "2%", "30t"]
  ];

  // Replace with your actual second table data (includes "Not Approved" for testing)
  const secondTableData = [
    ["Model Z", "9 m/min", "Class 2", "Drive Type 1", "90000", "Not Approved", "0.4 m/s²", "0.5 m/s²", "45t", "80t", "2.2%", "28t"],
    ["Model X", "12 m/min", "Class 3", "Drive Type 2", "150000", "Q2", "0.6 m/s²", "0.7 m/s²", "55t", "95t", "1.8%", "32t"]
  ];

  // Render the HTML template with your data
  const template = HtmlService.createTemplateFromFile('EmailTemplate');
  template.firstTableData = firstTableData;
  template.secondTableData = secondTableData;
  const htmlEmailBody = template.evaluate().getContent();

  // Send the email
  GmailApp.sendEmail({
    to: 'your-recipient@example.com',
    subject: 'Equipment Specifications & Approval Status',
    htmlBody: htmlEmailBody,
    noReply: true // Optional: Use if you don't want replies to your personal account
  });
}

Key Notes to Adjust for Your Use Case: #

• Styling: You can change #ffcccc (light red) to any red shade you want (e.g., #ff0000 for bright red) in the inline style.
• Data Sources: Replace the sample firstTableData and secondTableData arrays with your actual data (e.g., data pulled from a Google Sheet using SpreadsheetApp).
• Template Syntax: The <? ?> tags run JavaScript logic, while <?= ?> safely outputs values (automatically escapes HTML characters to avoid formatting issues).

内容的提问来源于stack exchange，提问作者nautilus