Google Sheets脚本邮件预览/发送功能故障求助

This solution reverts to using your script project's internal HTML templates via HtmlService.createTemplateFromFile and fixes broken event listeners for real-time preview and email sending.

Key Fixes & Improvements #

• Reverted to Internal Templates: Removed all Google Drive dependencies, directly loading templates stored in your App Script project.
• Functional Event Listeners: Properly attached DOMContentLoaded, change, and click handlers to handle template selection and email sending.
• Robust Client-Server Communication: Used google.script.run with success/failure handlers to manage async calls, providing clear user feedback for errors and success states.
• Flexible Data Integration: Added configurable logic to pull sheet data and pass it to template variables (adjust to match your sheet structure).

Implementation Steps #

1. Update Server-Side Code (Code.gs) #

Replace your existing Code.gs with this code. Adjust template names, data ranges, and email subject to match your needs:

// Opens the email template sidebar in Google Sheets
function showEmailTemplateUI() {
  const html = HtmlService.createHtmlOutputFromFile('index')
    .setTitle('Email Template Sender');
  SpreadsheetApp.getUi().showSidebar(html);
}

// Returns list of internal template names (match your template file names)
function getTemplateNames() {
  return ['Template1', 'Template2', 'Template3'];
}

// Renders selected template with data from the active sheet
function renderTemplate(templateName) {
  try {
    // Fetch data from active sheet (adjust range as needed)
    const sheet = SpreadsheetApp.getActiveSheet();
    const dataRow = sheet.getRange('A2:B2').getValues()[0]; // Example: first data row
    
    // Load template and pass dynamic variables
    const template = HtmlService.createTemplateFromFile(templateName);
    template.recipientName = dataRow[0];
    template.messageContent = dataRow[1];
    // Add additional template variables here
    
    return {
      success: true,
      content: template.evaluate().getContent()
    };
  } catch (error) {
    return { success: false, message: error.message };
  }
}

// Sends email using the rendered template content
function sendEmail(templateName, recipientEmail) {
  try {
    const renderResult = renderTemplate(templateName);
    if (!renderResult.success) throw new Error(renderResult.message);
    
    const emailSubject = 'Your Custom Email Subject';
    
    MailApp.sendEmail({
      to: recipientEmail,
      subject: emailSubject,
      htmlBody: renderResult.content
    });
    
    return { success: true, message: 'Email sent successfully!' };
  } catch (error) {
    return { success: false, message: error.message };
  }
}

2. Create/Update UI HTML File (index.html) #

Create or replace your UI file with this code, which includes the dropdown, preview area, and all client-side logic:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <style>
      .container { padding: 16px; font-family: Arial, sans-serif; }
      .template-select { margin-bottom: 16px; padding: 8px; width: 100%; border-radius: 4px; border: 1px solid #ccc; }
      .input-group { margin-bottom: 16px; }
      .input-group label { display: block; margin-bottom: 4px; font-weight: bold; }
      .input-group input { width: 100%; padding: 8px; border-radius: 4px; border: 1px solid #ccc; }
      .preview-area { border: 1px solid #ddd; padding: 16px; margin: 16px 0; min-height: 200px; background-color: #f9f9f9; border-radius: 4px; }
      .send-btn { background-color: #4CAF50; color: white; padding: 10px 20px; border: none; border-radius: 4px; cursor: pointer; font-size: 14px; }
      .send-btn:hover { background-color: #45a049; }
      .send-btn:disabled { background-color: #cccccc; cursor: not-allowed; }
      .status-message { margin-top: 12px; padding: 8px; border-radius: 4px; }
      .error { background-color: #ffebee; color: #c62828; }
      .success { background-color: #e8f5e9; color: #2e7d32; }
    </style>
  </head>
  <body>
    <div class="container">
      <h3>Email Template Sender</h3>
      
      <!-- Template Selection Dropdown -->
      <select id="templateSelect" class="template-select">
        <option value="">Select a template...</option>
      </select>
      
      <!-- Recipient Email Input -->
      <div class="input-group">
        <label for="recipientEmail">Recipient Email:</label>
        <input type="email" id="recipientEmail" required>
      </div>
      
      <!-- Preview Section -->
      <div>
        <h4>Email Preview:</h4>
        <div id="previewArea" class="preview-area"></div>
      </div>
      
      <!-- Send Button -->
      <button id="sendButton" class="send-btn" disabled>Send Email</button>
      
      <!-- Status Message -->
      <div id="statusMessage"></div>
    </div>

    <script>
      // Initialize UI on page load
      document.addEventListener('DOMContentLoaded', () => {
        // Populate template dropdown
        google.script.run.withSuccessHandler(populateTemplateDropdown)
          .getTemplateNames();
        
        // Attach event listeners
        document.getElementById('templateSelect').addEventListener('change', loadTemplatePreview);
        document.getElementById('sendButton').addEventListener('click', sendEmail);
      });
      
      // Populate dropdown with available templates
      function populateTemplateDropdown(templateNames) {
        const selectElement = document.getElementById('templateSelect');
        templateNames.forEach(name => {
          const option = document.createElement('option');
          option.value = name;
          option.textContent = name;
          selectElement.appendChild(option);
        });
      }
      
      // Load preview when template is selected
      function loadTemplatePreview() {
        const templateName = this.value;
        const previewArea = document.getElementById('previewArea');
        const sendButton = document.getElementById('sendButton');
        const statusMessage = document.getElementById('statusMessage');
        
        if (!templateName) {
          previewArea.innerHTML = '';
          sendButton.disabled = true;
          statusMessage.textContent = '';
          statusMessage.className = '';
          return;
        }
        
        statusMessage.textContent = 'Loading preview...';
        statusMessage.className = '';
        
        google.script.run
          .withSuccessHandler(result => {
            if (result.success) {
              previewArea.innerHTML = result.content;
              sendButton.disabled = false;
              statusMessage.textContent = '';
            } else {
              previewArea.innerHTML = '';
              sendButton.disabled = true;
              statusMessage.textContent = `Error: ${result.message}`;
              statusMessage.className = 'status-message error';
            }
          })
          .withFailureHandler(error => {
            previewArea.innerHTML = '';
            sendButton.disabled = true;
            statusMessage.textContent = `Failed to load preview: ${error.message}`;
            statusMessage.className = 'status-message error';
          })
          .renderTemplate(templateName);
      }
      
      // Handle email send action
      function sendEmail() {
        const templateName = document.getElementById('templateSelect').value;
        const recipientEmail = document.getElementById('recipientEmail').value;
        const sendButton = document.getElementById('sendButton');
        const statusMessage = document.getElementById('statusMessage');
        
        sendButton.disabled = true;
        statusMessage.textContent = 'Sending email...';
        statusMessage.className = '';
        
        google.script.run
          .withSuccessHandler(result => {
            if (result.success) {
              statusMessage.textContent = result.message;
              statusMessage.className = 'status-message success';
            } else {
              statusMessage.textContent = `Error sending email: ${result.message}`;
              statusMessage.className = 'status-message error';
            }
            sendButton.disabled = false;
          })
          .withFailureHandler(error => {
            statusMessage.textContent = `Failed to send email: ${error.message}`;
            statusMessage.className = 'status-message error';
            sendButton.disabled = false;
          })
          .sendEmail(templateName, recipientEmail);
      }
    </script>
  </body>
</html>

3. Verify Template Files #

Ensure your 3 template files (e.g., Template1.html, Template2.html) exist in your App Script project. Use template variables like <?= recipientName ?> to insert dynamic data:

<!-- Example Template1.html -->
<p>Hi <?= recipientName ?>,</p>
<p><?= messageContent ?></p>
<p>Best regards,</p>
<p>Your Team</p>

4. Test the Workflow #

1. Run showEmailTemplateUI from the App Script editor to open the sidebar in Google Sheets.
2. Select a template: the preview area will load the filled content.
3. Enter a recipient email and click "Send Email": the email will be sent, with a success/error message displayed.

Troubleshooting Tips #

• Template Names: Ensure names in getTemplateNames exactly match your template file names (case-sensitive).
• Data Range: Adjust the range in renderTemplate to pull the correct data from your sheet.
• Permissions: Grant required permissions when running the script for the first time.
• Debugging: Use console.log() (client-side) or Logger.log() (server-side) to diagnose issues.

内容的提问来源于stack exchange，提问作者Abe.S