使用脚本触发Google Sheets自动邮件时出错，寻求解决方法

Hey there! Let's work through this auto-email trigger issue for your Google Sheet. I'll share a reliable script setup and walk you through the most common pitfalls that might be causing errors.

First, replace any existing script with this tested version (open your Sheet, go to Extensions > Apps Script to access the editor):

function onEdit(e) {
  // Grab the edited cell's range and parent sheet
  const editedRange = e.range;
  const targetSheet = editedRange.getSheet();

  // Only run logic if the edit is in Column D (4th column) and not the header row
  if (editedRange.getColumn() === 4 && editedRange.getRow() > 1) {
    const newStatus = editedRange.getValue();
    // Skip if the status was cleared (avoids triggering on blank initial state)
    if (!newStatus) return;

    // Pull corresponding name (Column A) and email (Column B) for the edited row
    const userName = targetSheet.getRange(editedRange.getRow(), 1).getValue();
    const userEmail = targetSheet.getRange(editedRange.getRow(), 2).getValue();

    // Make sure we have a valid email before proceeding
    if (!userEmail) {
      SpreadsheetApp.getUi().alert(`No email address found for ${userName} — email won't be sent.`);
      return;
    }

    // Customize these values to match your needs
    const senderDisplayName = "Your Team Name"; // The name recipients will see
    const emailSubject = `Your Status Has Been Updated: ${newStatus}`;
    const emailBody = `Hi ${userName},\n\nJust a quick note to let you know your status has been updated to: ${newStatus}\n\nLet us know if you have questions!\n\nBest,\n${senderDisplayName}`;

    try {
      // Send the email with proper sender formatting
      MailApp.sendEmail({
        to: userEmail,
        subject: emailSubject,
        body: emailBody,
        name: senderDisplayName
      });
      // Log success for debugging (check via View > Logs in the script editor)
      console.log(`Email sent successfully to ${userEmail} (${userName})`);
    } catch (error) {
      // Show a user-friendly alert if something goes wrong
      SpreadsheetApp.getUi().alert(`Failed to send email to ${userEmail}: ${error.message}`);
      console.error("Email error details:", error);
    }
  }
}

This script includes safeguards to avoid unnecessary triggers (like blank statuses) and error handling to help you diagnose issues quickly.

Most errors stem from setup oversights — check these first:

• Ensure the Trigger is Configured Correctly:
  While onEdit is a "simple trigger," sometimes manual deployment is more reliable. Go to the script editor's Triggers tab (alarm icon), click Add Trigger, and set:

  • Choose function: onEdit
  • Choose deployment type: Head
  • Event source: From spreadsheet
  • Event type: On edit
    Save and authorize the trigger when prompted.

• Grant Required Permissions:
  The first time you run the script (either via editing the Sheet or manually clicking "Run" in the script editor), you'll need to grant access to send emails and edit your Sheet. Google may flag it as "unsafe" — click Advanced > Go to [Your Script Name] to complete authorization (this is safe for your own script).

• Validate Email Addresses:
  Double-check Column B for typos, extra spaces, or invalid formats (e.g., miike477@gmail.cm instead of .com). The script checks for empty emails, but invalid formats will still cause delivery failures.

• Check Email Quotas:
  Free Google Workspace accounts have a daily limit of 100 sent emails. If you're hitting this cap, you'll see errors in the script logs. You can view your quota in the script editor via Resources > Quotas.

• Test with a Simulated Edit:
  If you want to debug without editing the Sheet, add this test function to the script editor and run it:

  function testOnEdit() {
    const testSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    // Simulate editing row 2, Column D (adjust row number as needed)
    const testRange = testSheet.getRange(2, 4);
    onEdit({ range: testRange });
  }
  

After setting up the script and trigger:

1. Edit a row's Column D to select A, B, or C
2. Check the corresponding email inbox (don't forget spam folders!)
3. If no email arrives, check the script editor's Logs (View > Logs) for error messages — this will point you to the exact issue.

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