Google Sheets脚本开发求助：实现基于当日Removal Date自动发送邮箱移除提醒邮件

Looking at your code, there are a few key issues preventing it from working correctly. Let's fix those and get your reminder system up and running:

Key Issues in Original Code #

1. Incorrect Column Index: Your Removal Date is the 4th column, but array indexes start at 0—so it should be row[3] instead of row[4].
2. Date Comparison Mistake: Comparing removalDate === Date doesn't work (you're comparing a date object to the Date function itself). Plus, dates include time components, so we need to normalize both dates to check if they're the same day.
3. Filter Function Logic: The filter should return a boolean (true/false) to include rows, not the row itself.
4. Email Content: You were trying to use row[2] (Name) instead of row[1] (Email), and needed to handle multiple emails if there are multiple rows matching the date.

Corrected Code #

function SendEmail() {
  // Get today's date, normalized to midnight (strip time component)
  const presentDay = new Date();
  presentDay.setHours(0, 0, 0, 0);
  
  // Get all rows from the active sheet
  const rows = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
  
  // Filter rows where Removal Date matches today (skip header row if present)
  const filteredRows = rows.filter((row, index) => {
    // Skip header row (adjust if your sheet doesn't have a header)
    if (index === 0) return false;
    
    const removalDate = new Date(row[3]);
    // Normalize removal date to midnight
    removalDate.setHours(0, 0, 0, 0);
    
    // Check if dates are the same using timestamps
    return removalDate.getTime() === presentDay.getTime();
  });
  
  // Collect all emails from filtered rows
  const emailsToRemove = filteredRows.map(row => row[1]);
  
  Logger.log("Emails to remove today: " + emailsToRemove.join(", "));
  
  // Only send email if there are emails to remove
  if (emailsToRemove.length > 0) {
    const emailAddress = "staticEmail@gmail.com";
    const subject = "Members Need to Be Removed Today";
    const message = `The following email addresses need to be removed:\n\n${emailsToRemove.join("\n")}`;
    
    MailApp.sendEmail(emailAddress, subject, message);
    Logger.log("Reminder email sent successfully.");
  } else {
    Logger.log("No emails need removal today.");
  }
}

Breakdown of Changes #

• Date Normalization: By setting hours/minutes/seconds/milliseconds to 0 for both today's date and the removal date, we ensure we're comparing just the day/month/year, not the exact time.
• Header Row Handling: The filter skips the first row (assuming your sheet has a header like "Date Processed", "Email", etc.). If your sheet doesn't have a header, remove that if (index === 0) return false; line.
• Email Collection: We use map to extract just the email addresses from the filtered rows, making it easy to list them clearly in the email.
• Conditional Email Sending: We only send an email if there are actual emails to remove, so you don't get empty or unnecessary reminders.

Testing Tips #

1. Run the script manually first (click the play button in the script editor) to see if it logs the correct emails and sends a test email.
2. Check the Executions tab in the script editor to verify your daily trigger is running as expected.

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