如何让Google Script仅在第15列变更时发送邮件？

Got it, let's resolve this issue where your script sends emails for edits outside column 15. The main problem lies in how your if condition is structured—let's break it down and fix it step by step.

What's Wrong with the Original Code? #

Your current condition has two critical issues:

• The logic for checking the status value is broken: e.value == "Complete" || "In Progress" doesn't work as intended. Non-empty strings are truthy in JavaScript, so this part will always evaluate to true, triggering the email even for edits in other columns.
• The column check colIndex == actionCol is only tied to the last status value (Need Additional Support) due to operator precedence, not the entire status list.

Corrected Script #

Here's the revised code that only triggers emails when column 15 is edited to one of your target statuses:

function sendComplete(e) {
  var ss = e.source;
  var s = ss.getSheetByName('Action Items');
  var r = e.range;
  var actionCol = 15;
  var colIndex = r.getColumn(); // Modern replacement for deprecated getColumnIndex()
  
  // First check if the edited column is column 15 (exit early if not)
  if (colIndex === actionCol) {
    var targetStatuses = ["Complete", "In Progress", "On Hold", "Awaiting Parts", "Need Additional Support"];
    // Verify the new value is one of our target statuses
    if (targetStatuses.includes(e.value)) {
      var row = r.getRow(); // Modern replacement for deprecated getRowIndex()
      var userEmail = s.getRange(row, 20).getValue();
      var eventNumber = s.getRange(row, 1).getValue();
      
      var subject = "Status Change for Event #" + eventNumber;
      // Use template literals for cleaner, more readable email body
      var body = `Event: ${eventNumber}
Category: ${s.getRange(row, 4).getValue()}
Asset: ${s.getRange(row, 5).getValue()}
Sub-Section: ${s.getRange(row, 6).getValue()}
Component: ${s.getRange(row, 7).getValue()} // Fixed: You had column 6 here twice before—adjust if needed!
Problem: ${s.getRange(row, 8).getValue()}
Action Update: ${s.getRange(row, 9).getValue()}
Status: ${e.value}`;
      
      MailApp.sendEmail(userEmail, subject, body);
    }
  }
}

Key Changes Explained #

• Column Check First: We prioritize verifying the edited column is column 15. If not, the script exits immediately, avoiding unnecessary processing.
• Proper Status Validation: Using an array with includes() makes the status check cleaner and less error-prone than chaining multiple || comparisons.
• Updated Methods: Replaced deprecated getColumnIndex() and getRowIndex() with Google's recommended modern equivalents getColumn() and getRow().
• Fixed Column Typo: I noticed you used column 6 for both "Sub-Section" and "Component"—I adjusted the Component to column 7, but tweak this to match your actual sheet setup!
• Cleaner Body Format: Template literals (backticks `) make the email body easier to edit and read compared to string concatenation with +.

Quick Test Tips #

• Keep your trigger set to From Spreadsheet > On edit—no changes needed there.
• Test by editing column 15 to one of your target statuses to confirm emails send, then edit any other column to ensure no emails are triggered.

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