Google Sheets邮件脚本优化：实现单次发送并标记已发送

Got it, let's tackle that annoying duplicate email problem you're facing. The key here is to add a check for the "Email Sent" marker in column H before sending any emails, and then set that marker right after sending to prevent repeats.

Here's the Modified Script (Basic Version) #

function sendEmails() {
  var sSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sSheet.getLastRow();
  
  // Start from row 2 (assuming row 1 is your header row—adjust if needed)
  for (var i = 2; i <= lastRow; i++) {
    const fColumnValue = sSheet.getRange(i, 6).getValue(); // Column F = index 6
    const hColumnValue = sSheet.getRange(i, 8).getValue(); // Column H = index 8
    
    // Only proceed if H doesn't have "Email Sent" AND F is greater than 0
    if (hColumnValue !== "Email Sent" && fColumnValue > 0) {
      // Customize these values to match your sheet's structure
      const recipient = sSheet.getRange(i, 2).getValue(); // Example: Column B has emails
      const emailSubject = "Alert: Value in Column F Exceeds 0";
      const emailBody = `Hi there,\n\nThe value in Column F for your row is ${fColumnValue}, which has triggered this notification.\n\nThanks!`;
      
      // Send the email
      MailApp.sendEmail(recipient, emailSubject, emailBody);
      
      // Mark the row as emailed in Column H
      sSheet.getRange(i, 8).setValue("Email Sent");
    }
  }
}

Key Improvements Explained #

• Duplicate Prevention Check: The if statement first verifies that Column H doesn't already have "Email Sent"—this ensures we never resend to the same row.
• Immediate Marker Update: Right after sending the email, we write "Email Sent" to Column H. This locks the row so future script runs skip it.
• Customizable Fields: Adjust the recipient column (currently Column B, index 2), subject, and body to fit your exact needs.

Optimized Version for Larger Sheets #

If you have a lot of rows, using getRange() in a loop can slow things down. Here's a more efficient version that batches data reads/writes:

function sendEmailsOptimized() {
  const sSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const allData = sSheet.getDataRange().getValues(); // Fetch all data at once
  const rowsToUpdate = []; // Track rows that need the "Email Sent" marker
  
  // Loop through rows (start at index 1 since index 0 is the header row)
  for (let i = 1; i < allData.length; i++) {
    const fValue = allData[i][5]; // Column F = array index 5
    const hValue = allData[i][7]; // Column H = array index 7
    
    if (hValue !== "Email Sent" && fValue > 0) {
      const recipient = allData[i][1]; // Column B = array index 1
      const subject = "Alert: Column F Value > 0";
      const body = `Hello,\n\nYour row has a value of ${fValue} in Column F, which has triggered this alert.\n\nBest regards,`;
      
      MailApp.sendEmail(recipient, subject, body);
      
      // Add row number (i+1, since array index starts at 0) to update list
      rowsToUpdate.push(i + 1);
    }
  }
  
  // Batch update Column H with "Email Sent" to minimize API calls
  rowsToUpdate.forEach(rowNum => {
    sSheet.getRange(rowNum, 8).setValue("Email Sent");
  });
}

Quick Testing Tip #

Before sending real emails, replace MailApp.sendEmail(...) with Logger.log(Would send email to ${recipient}) to verify the script is targeting the right rows without spamming anyone.

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