求助：Google Apps Script日程邮件日期显示为1899年问题

• 场景：使用Google Sheets日程表结合AppScript开发预订邮件通知功能
• 异常：当前年份为2024年，但邮件通知中始终显示预订时间为1899年
• 已尝试操作：将日期格式化字符串中的MM、DD、YY改为小写mm、dd、yy，问题未解决

原代码如下：

// Function to send email with image and booking details
function sendEmailWithImage(Username, Timeslot, Day) {
  var imageObject = {};
  var successImageLoading = true;
  var sheet = SpreadsheetApp.getActive().getSheetByName('Schedule');
  var emailAddress = "coolvibes1989@gmail.com"; // Your email
  var subject = "Presenter Booked";

  // Use try-catch to handle errors while loading the image
  try {
    imageObject['myImage1'] = DriveApp.getFileById('1oin8reV7pvZZ9kewuYYw-z4lAFf233YI').getAs('image/png');
  } catch (error) {
    successImageLoading = false;
  }

  // Ensure Day is a Date object
  if (!(Day instanceof Date)) {
    Logger.log("Day is not a valid Date object: " + Day);
    return; // Exit the function if Day is invalid
  }

  // Convert the Day and Timeslot into a human-readable format
  var dayFormatted = Utilities.formatDate(Day, Session.getScriptTimeZone(), "mm dd yy");  // e.g., "Saturday, Oct 07 2023"
  var timeFormatted = Timeslot; // Assuming Timeslot is already in a readable format; adjust as needed

  // Create HTML content for the email
  var htmlStartString = "<html><head><style type='text/css'> table {border-collapse: collapse; display: block;} th {border: 1px solid black; background-color:blue; color: white;} td {border: 1px solid black;} #body a {color: inherit !important; text-decoration: none !important; font-size: inherit !important; font-family: inherit !important; font-weight: inherit !important; line-height: inherit !important;}</style></head><body id='body'>";
  var htmlEndString = "</body></html>";

  // Message content with Username, Timeslot, and Day
  var message = `Slot Booked! Thank you ${Username} for booking. Your slot is scheduled for ${dayFormatted}, ${timeFormatted}.`;

  var emailBody = `${htmlStartString}<p>${message}</p>`;

  // Include image in the email body if image loading is successful
  if (successImageLoading) {
    emailBody += `<p><img src='cid:myImage1' style='width:400px; height:auto;' ></p>`;
  }

  emailBody += htmlEndString;

  // Debugging log
  Logger.log(emailBody);  // This will show the email body in the Logs for debugging

  // Send email
  MailApp.sendEmail({
    to: emailAddress,
    subject: subject,
    htmlBody: emailBody,
    inlineImages: (successImageLoading ? imageObject : null)
  });
}

// Trigger function for On Change event
function onChange(e) {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Schedule');
  var range = e.range;
  var row = range.getRow();
  var col = range.getColumn();

  if (col >= 3 && col <= 9) { // Check if the change is within the schedule columns (C to H)
    var Username = sheet.getRange(row, col).getValue(); // Get the presenter's name
    var Timeslot = sheet.getRange(row, 2).getValue(); // Get the time from column B
    var Day = sheet.getRange(4, col).getValue(); // Get the day from row 4 (header)

    // Convert Day if it's not already a Date object
    if (typeof Day === "string") {
      Day = new Date(Day); // Convert string to Date
    }

    if (Username) {
      // Call the sendEmailWithImage function with the Username, Timeslot, and Day
      sendEmailWithImage(Username, Timeslot, Day);
    }
  }
}

1. 日期格式化模板错误：代码中使用"mm dd yy"作为格式化规则，其中mm代表分钟而非月份，正确的月份占位符应为MM；yy仅取年份后两位，易引发歧义，且无法完整显示2024这类四位年份。
2. 无效日期对象未被拦截：从日程表第4行获取的Day若为纯文本（如仅显示星期的字符串），通过new Date(Day)转换会生成无效日期对象，而Google Apps Script的Utilities.formatDate处理无效日期时，默认返回1899年12月30日（对应Excel/Sheets的日期起始基准）。
3. 日期校验逻辑不全：sendEmailWithImage中仅校验Day是否为Date对象，但未校验其是否为有效日期，导致无效日期被格式化输出。

1. 修正日期格式化模板 #

将sendEmailWithImage函数中的日期格式化代码修改为：

// 若需要显示星期，可改为 "EEEE, MM dd yyyy"，例如"星期三, 05 22 2024"
var dayFormatted = Utilities.formatDate(Day, Session.getScriptTimeZone(), "MM dd yyyy");

2. 优化日期转换与校验逻辑 #

在onChange函数中，新增无效日期的判断逻辑，避免错误日期流入后续流程：

var Day = sheet.getRange(4, col).getValue();
// Convert Day if it's not already a Date object
if (typeof Day === "string") {
  const parsedDate = new Date(Day);
  // 校验转换后的日期是否有效
  if (isNaN(parsedDate.getTime())) {
    Logger.log("无法解析日期字符串: " + Day);
    return;
  }
  Day = parsedDate;
}

// 新增无效日期全局校验
if (!(Day instanceof Date) || isNaN(Day.getTime())) {
  Logger.log("无效日期对象: " + Day);
  return;
}

3. 确保日程表单元格格式正确 #

打开对应的Google Sheets日程表，选中第4行的日期列（C至H列），设置单元格格式为日期而非纯文本，确保AppScript能直接获取到合法的Date对象。

完整修正后的代码 #

// Function to send email with image and booking details
function sendEmailWithImage(Username, Timeslot, Day) {
  var imageObject = {};
  var successImageLoading = true;
  var sheet = SpreadsheetApp.getActive().getSheetByName('Schedule');
  var emailAddress = "coolvibes1989@gmail.com"; // Your email
  var subject = "Presenter Booked";

  // Use try-catch to handle errors while loading the image
  try {
    imageObject['myImage1'] = DriveApp.getFileById('1oin8reV7pvZZ9kewuYYw-z4lAFf233YI').getAs('image/png');
  } catch (error) {
    successImageLoading = false;
  }

  // Ensure Day is a valid Date object
  if (!(Day instanceof Date) || isNaN(Day.getTime())) {
    Logger.log("Day is not a valid Date object: " + Day);
    return; // Exit the function if Day is invalid
  }

  // Convert the Day and Timeslot into a human-readable format
  var dayFormatted = Utilities.formatDate(Day, Session.getScriptTimeZone(), "MM dd yyyy");
  var timeFormatted = Timeslot; // Assuming Timeslot is already in a readable format; adjust as needed

  // Create HTML content for the email
  var htmlStartString = "<html><head><style type='text/css'> table {border-collapse: collapse; display: block;} th {border: 1px solid black; background-color:blue; color: white;} td {border: 1px solid black;} #body a {color: inherit !important; text-decoration: none !important; font-size: inherit !important; font-family: inherit !important; font-weight: inherit !important; line-height: inherit !important;}</style></head><body id='body'>";
  var htmlEndString = "</body></html>";

  // Message content with Username, Timeslot, and Day
  var message = `Slot Booked! Thank you ${Username} for booking. Your slot is scheduled for ${dayFormatted}, ${timeFormatted}.`;

  var emailBody = `${htmlStartString}<p>${message}</p>`;

  // Include image in the email body if image loading is successful
  if (successImageLoading) {
    emailBody += `<p><img src='cid:myImage1' style='width:400px; height:auto;' ></p>`;
  }

  emailBody += htmlEndString;

  // Debugging log
  Logger.log(emailBody);  // This will show the email body in the Logs for debugging

  // Send email
  MailApp.sendEmail({
    to: emailAddress,
    subject: subject,
    htmlBody: emailBody,
    inlineImages: (successImageLoading ? imageObject : null)
  });
}

// Trigger function for On Change event
function onChange(e) {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Schedule');
  var range = e.range;
  var row = range.getRow();
  var col = range.getColumn();

  if (col >= 3 && col <= 9) { // Check if the change is within the schedule columns (C to H)
    var Username = sheet.getRange(row, col).getValue(); // Get the presenter's name
    var Timeslot = sheet.getRange(row, 2).getValue(); // Get the time from column B
    var Day = sheet.getRange(4, col).getValue(); // Get the day from row 4 (header)

    // Convert Day if it's not already a Date object
    if (typeof Day === "string") {
      const parsedDate = new Date(Day);
      if (isNaN(parsedDate.getTime())) {
        Logger.log("无法解析日期字符串: " + Day);
        return;
      }
      Day = parsedDate;
    }

    // 新增无效日期校验
    if (!(Day instanceof Date) || isNaN(Day.getTime())) {
      Logger.log("无效日期: " + Day);
      return;
    }

    if (Username) {
      // Call the sendEmailWithImage function with the Username, Timeslot, and Day
      sendEmailWithImage(Username, Timeslot, Day);
    }
  }
}

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