Google Apps Script：实现带抄送/密送的邮件发送并取消EMAIL_SENT写入

Got it, let's adjust your script to add CC and BCC functionality while removing that EMAIL_SENT marker you no longer need. Here's the revised code, plus a breakdown of what changed:

function sendEmail() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2; // Assuming row 1 is your header row
  var numRows = sheet.getLastRow() - startRow + 1; // Calculate exact rows with data (avoids empty rows)
  
  // Adjust the column count here based on your sheet structure
  // We're using columns 1-4: Name (A), To (B), CC (C), BCC (D)
  var dataRange = sheet.getRange(startRow, 1, numRows, 4);
  var data = dataRange.getValues();
  
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var name = row[0].split(",")[1].trim(); // Clean up extra spaces from the name split
    var emailAddress = row[1];
    var ccAddresses = row[2]; // Supports multiple addresses separated by commas
    var bccAddresses = row[3]; // Same for BCC
    
    // Skip rows where there's no recipient email to avoid errors
    if (!emailAddress) continue;
    
    // Customize your email subject and body here
    var subject = `Update for ${name}`;
    var body = `Hi ${name},\n\nThis is your personalized email content. Feel free to edit this!`;
    
    // Send email with optional CC/BCC using advanced parameters
    MailApp.sendEmail({
      to: emailAddress,
      cc: ccAddresses || "", // Pass empty string if no CC exists
      bcc: bccAddresses || "", // Same for BCC
      subject: subject,
      body: body
    });
    
    // Removed all code related to writing the EMAIL_SENT marker
  }
}

Key Updates Explained: #

• CC/BCC Integration:
  • We're pulling CC addresses from column C (row[2]) and BCC from column D (row[3]) – tweak these indices if your sheet uses different columns (remember, arrays are 0-indexed, so column A is row[0]).
  • Used the advanced parameter syntax for MailApp.sendEmail() which makes it straightforward to include optional fields. If a row has no CC/BCC entries, we pass an empty string, which the method handles without issues.
• Removed EMAIL_SENT Marker:
  • Completely deleted the EMAIL_SENT variable and all code that would write this value back to your sheet. No more post-save markers cluttering up your data!
• Quality-of-Life Fixes:
  • Fixed the numRows calculation to only process rows with actual data (the original code could have included empty rows at the bottom).
  • Added trim() to the name extraction to clean up extra spaces from the comma split.
  • Added a check to skip rows without a recipient email, preventing unnecessary errors.

Just make sure to adjust the column indices and email content to match your actual spreadsheet setup!

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