开发Google Script实现Spreadsheet行级到期日邮件提醒功能

Got it, let's build this vendor expiry reminder script step by step. This implementation will handle row-by-row validation of expiry dates and send personalized reminder emails automatically.

Spreadsheet Structure Assumptions #

First, let's assume your sheet has these columns (adjust indices in the code if yours differ):

• Column A: Vendor Name
• Column B: Expiry Date (formatted as a date in Google Sheets)
• Column C: Vendor Email
• Column D (optional): Status (to mark if a reminder was sent)

Full Script Code #

function sendExpiryReminders() {
  // Replace "Vendors" with your actual sheet name
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Vendors");
  if (!sheet) {
    console.error("Sheet not found! Double-check the sheet name in the code.");
    return;
  }
  
  // Fetch all data from the sheet
  const data = sheet.getDataRange().getValues();
  const today = new Date();
  // Normalize today's date to midnight to avoid time-related comparison issues
  today.setHours(0, 0, 0, 0);
  
  // Loop through each row (skip the header row at index 0)
  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    const vendorName = row[0];
    const expiryDate = new Date(row[1]);
    const vendorEmail = row[2];
    
    // Skip rows with missing email or invalid expiry date
    if (!vendorEmail || isNaN(expiryDate.getTime())) {
      console.log(`Skipping row ${i+1}: Missing email or invalid expiry date`);
      continue;
    }
    
    // Normalize expiry date to midnight for accurate comparison
    expiryDate.setHours(0, 0, 0, 0);
    
    // Check if expiry date is before today
    if (expiryDate < today) {
      // Customize your email content here
      const emailSubject = `Urgent: Your Contract with Our Team Has Expired`;
      const emailBody = `Hi ${vendorName},\n\nWe're reaching out to remind you that your contract with us expired on ${expiryDate.toLocaleDateString()}.\n\nPlease contact our team at your earliest convenience to discuss renewal options.\n\nBest,\nThe [Your Team Name] Team`;
      
      try {
        // Send the email
        MailApp.sendEmail(vendorEmail, emailSubject, emailBody);
        console.log(`Successfully sent reminder to ${vendorName} (${vendorEmail})`);
        
        // Optional: Mark the row as notified (update column D)
        sheet.getRange(i+1, 4).setValue("Notified");
        sheet.getRange(i+1, 4).setBackground("#d4edda"); // Light green background for visibility
      } catch (error) {
        console.error(`Failed to send email to ${vendorEmail}: ${error.message}`);
        // Optional: Mark row as failed
        sheet.getRange(i+1, 4).setValue("Failed to send");
        sheet.getRange(i+1, 4).setBackground("#f8d7da"); // Light red background
      }
    }
  }
}

Key Features Explained #

• Sheet Validation: Checks if the specified sheet exists to avoid errors.
• Date Normalization: Sets both today's date and expiry dates to midnight to ensure accurate comparisons (no false negatives because of time differences like 3pm today vs 10am expiry).
• Error Handling: Skips rows with missing emails or invalid dates, and logs errors if email sending fails.
• Status Tracking: Optional feature to mark rows as notified or failed, with color coding for easy visibility.

Automate the Script #

To make this run automatically (e.g., daily at 9 AM):

1. In the Google Apps Script editor, click Edit > Current project's triggers.
2. Click Add trigger in the bottom right.
3. Configure the trigger:
   • Choose function: sendExpiryReminders
   • Choose event source: Time-driven
   • Choose type of time based trigger: Day timer
   • Select a time window (e.g., 9am to 10am)
4. Click Save and authorize the trigger when prompted.

Important Notes #

• Permissions: The first time you run the script, you'll need to grant it permission to access your spreadsheet and send emails via your Google account.
• Testing: Test with a small sample row first to ensure the email content and logic work as expected.
• Customization: Adjust the email subject/body, sheet name, and column indices to match your specific needs.

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