Google Apps Script表格数据获取失败：邮件通知显示undefined问题修复

问题背景 #

• 持有包含姓名、合同开始日期、合同结束日期的Google表格，需求为当合同结束日期距当前不足1个月时，自动发送Gmail通知
• 邮件可正常接收，但正文中的ValueA、ValueB、ValueC、ValueH部分显示undefined，部分显示[object Object]

原代码 #

function onChange(e) {
  try {
    if (!e || !e.source) {
      throw new Error("Event object or source is undefined.");
    }

    var sheet = e.source.getActiveSheet();
    var editedRange = e.range;
    var editedRow = editedRange.getRow();

    console.log("Sheet Name:", sheet.getName());
    console.log("Edited Row:", editedRow);

    if (sheet.getName() === '2023') {
      var valueInColumnI = sheet.getRange(editedRow, 9).getValue();
      console.log("Value in Column I:", valueInColumnI);

      if (valueInColumnI === 'OVERDUE') {
        var valueInColumnC = sheet.getRange(editedRow, 3).getValue();
        var valueInColumnH = sheet.getRange(editedRow, 8).getValue();
        var valueInColumnA = sheet.getRange(editedRow, 1).getValue();
        var valueInColumnB = sheet.getRange(editedRow, 2).getValue();

        console.log("Raw Value in Column C:", valueInColumnC, "Type:", typeof valueInColumnC);
        console.log("Raw Value in Column H:", valueInColumnH, "Type:", typeof valueInColumnH);
        console.log("Raw Value in Column A:", valueInColumnA, "Type:", typeof valueInColumnA);
        console.log("Raw Value in Column B:", valueInColumnB, "Type:", typeof valueInColumnB);

        var stringValueC = convertToString(valueInColumnC);
        var stringValueH = convertToString(valueInColumnH);
        var stringValueA = convertToString(valueInColumnA);
        var stringValueB = convertToString(valueInColumnB);

        console.log("Converted String Value in Column C:", stringValueC);
        console.log("Converted String Value in Column H:", stringValueH);
        console.log("Converted String Value in Column A:", stringValueA);
        console.log("Converted String Value in Column B:", stringValueB);

        sendEmailNotification(stringValueA, stringValueB, stringValueC, stringValueH);
      }
    }
  } catch (error) {
    console.error("Error in onChange:", error.message);
  }
}

function convertToString(value) {
  if (value === null || value === undefined) {
    return 'N/A';
  }
  if (Object.prototype.toString.call(value) === '[object Date]') {
    return Utilities.formatDate(value, Session.getScriptTimeZone(), 'MM/dd/yyyy');
  }
  if (typeof value === 'object') {
    return JSON.stringify(value); // Convert objects to JSON strings for better debugging
  }
  return String(value);
}

function sendEmailNotification(valueA, valueB, valueC, valueH) {
  var email = "myemail"; 
  var subject = "Contract Extend";

  var message = `Hello,

Please be informed of the following details:

- Contract: ${valueC}
- Due Date: ${valueH}
- Additional Info A: ${valueA}
- Additional Info B: ${valueB}

Please ensure to extend the contract '${valueC}' before '${valueH}'.

Best regards,
Your Team`;

  console.log("Email Message:", message);

  MailApp.sendEmail(email, subject, message);
}

问题原因 #

1. 触发事件不匹配：onChange仅在表格结构变更（如增删行列）时触发，单元格编辑不会触发，导致无法获取目标行数据
2. 逻辑与需求脱节：代码依赖列I的OVERDUE标记触发通知，但原始需求是监控合同结束日期距当前不足1个月的情况
3. 对象转换不完善：部分单元格数据（如富文本格式）被直接JSON序列化，出现[object Object]；空值判断不全导致undefined

修复后的完整代码 #

function checkContractDeadlines() {
  try {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('2023');
    if (!sheet) throw new Error("工作表'2023'不存在");

    var dataRange = sheet.getDataRange();
    var values = dataRange.getValues();
    var today = new Date();
    var oneMonthLater = new Date(today.setMonth(today.getMonth() + 1));

    // 遍历数据行（跳过表头，假设第1行为表头）
    for (var i = 1; i < values.length; i++) {
      var row = values[i];
      var contractEndDate = row[7]; // H列对应索引7（数组从0开始）
      var name = row[0]; // A列：姓名
      var contractStartDate = row[1]; // B列：合同开始日期
      var contractName = row[2]; // C列：合同名称

      // 校验日期有效性，且判断是否在未来1个月内
      if (contractEndDate instanceof Date && !isNaN(contractEndDate.getTime())) {
        if (contractEndDate <= oneMonthLater && contractEndDate >= new Date()) {
          var strName = convertToString(name);
          var strStartDate = convertToString(contractStartDate);
          var strContractName = convertToString(contractName);
          var strEndDate = convertToString(contractEndDate);

          sendEmailNotification(strName, strStartDate, strContractName, strEndDate);
        }
      }
    }
  } catch (error) {
    console.error("检查合同截止日期时出错：", error.message);
  }
}

function convertToString(value) {
  if (value === null || value === undefined || value === '') {
    return 'N/A';
  }
  if (Object.prototype.toString.call(value) === '[object Date]') {
    return Utilities.formatDate(value, Session.getScriptTimeZone(), 'yyyy-MM-dd');
  }
  // 优先调用对象原生toString方法，避免JSON序列化导致的异常格式
  if (typeof value === 'object' && value !== null) {
    return value.toString ? value.toString() : JSON.stringify(value);
  }
  return String(value);
}

function sendEmailNotification(valueA, valueB, valueC, valueH) {
  var email = "your-email@example.com"; 
  var subject = "合同即将到期提醒";

  var message = `您好，

以下合同即将到期，请及时处理：

- 合同名称：${valueC}
- 到期日期：${valueH}
- 联系人姓名：${valueA}
- 合同开始日期：${valueB}

请确保在${valueH}前完成合同续约。

此致，
您的团队`;

  console.log("邮件内容：", message);
  MailApp.sendEmail(email, subject, message);
}

关键修复说明 #

1. 替换触发逻辑：改用主动执行的checkContractDeadlines函数，可通过Google Apps Script的时间驱动触发器设置每日执行，贴合“监控到期日期”的需求
2. 修正数据判断：直接检查合同结束日期是否在未来1个月内，不再依赖列I的标记，匹配原始需求
3. 优化数据获取：一次性读取全表数据，减少getRange调用次数提升性能，同时修正列索引（数组从0开始）
4. 完善对象转换：增加空值判断，优先调用对象的toString方法，避免[object Object]格式异常
5. 适配中文场景：邮件内容改为中文，日期格式调整为国内常用的yyyy-MM-dd

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