Google Sheets生日邮件脚本时区及空行问题求助

我来帮你搞定这两个头疼的问题，咱们一步步拆解：

问题1：时区导致生日日期解析成前一天22:00 #

这个问题的核心是硬编码时区和表格实际时区不匹配。Google Sheets里的日期本质是UTC时间戳，当你用Utilities.formatDate时，如果硬写UTC+2，可能会和表格设置的时区（比如夏令时切换时的CEST/CET）产生偏移，导致日期被解析成前一天。

解决方案：使用表格自带的时区 #

把硬编码的时区替换成电子表格的时区，这样不管表格时区怎么调整，脚本都会自动适配：

// 获取表格的时区（代替硬编码的UTC+2）
var sheetTimeZone = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();

// 用表格时区格式化生日和当前日期
var startday = Utilities.formatDate(row[2], sheetTimeZone, "MM-dd");
var today = Utilities.formatDate(new Date(), sheetTimeZone, "MM-dd");

这样就能保证生日日期的解析和表格里显示的完全一致，不会出现时区偏移导致的日期错位。

问题2：空行/空单元格报错&优化数据获取 #

原来的脚本一次性获取1000行1000列的范围，包含大量空数据，而且没做空值判断，自然会报错。咱们从数据获取和空值校验两方面优化：

1. 动态获取有效数据范围 #

不要固定写死行数和列数，用getLastRow()和getLastColumn()获取表格里实际有数据的范围，避免处理空行空列：

var lastRow = sheet.getLastRow(); // 获取最后一行有数据的行号
var lastCol = sheet.getLastColumn(); // 获取最后一列有数据的列号
var dataRange = sheet.getRange(startRow, 1, lastRow - startRow + 1, lastCol);
var data = dataRange.getValues();

2. 循环前做空值校验 #

在处理每一行之前，先检查姓名、邮箱、生日这些关键字段是否为空，为空就跳过当前行，避免报错：

// 遍历数组用普通for循环更安全（不要用for...in，会遍历原型属性）
for (var i = 0; i < data.length; i++) {
  var row = data[i];
  var name = row[0];
  var emailAddress = row[1];
  var birthday = row[2];

  // 关键字段为空，直接跳过这一行
  if (!name || !emailAddress || !birthday) {
    continue;
  }

  // 后续的生日判断和发邮件逻辑...
}

优化后的完整脚本 #

把上面的修改整合起来，最终的脚本如下：

function sendBirthdayEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2; // 数据起始行（跳过表头）
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();
  var dataRange = sheet.getRange(startRow, 1, lastRow - startRow + 1, lastCol);
  var data = dataRange.getValues();

  // 获取表格时区，统一格式化日期
  var sheetTimeZone = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();
  var today = Utilities.formatDate(new Date(), sheetTimeZone, "MM-dd");
  var currentYear = Utilities.formatDate(new Date(), sheetTimeZone, "yyyy");

  for (var i = 0; i < data.length; i++) {
    var row = data[i];
    var name = row[0];
    var emailAddress = row[1];
    var birthday = row[2];

    // 跳过空行或关键字段为空的行
    if (!name || !emailAddress || !birthday) {
      continue;
    }

    var birthDayMonth = Utilities.formatDate(birthday, sheetTimeZone, "MM-dd");
    if (today === birthDayMonth) {
      var birthYear = Utilities.formatDate(birthday, sheetTimeZone, "yyyy");
      var age = currentYear - birthYear;
      var message = `Woop woop, ${name}!<br>Today is your ${age} year(s) birthday!`;
      var subject = "Your birthday!";

      MailApp.sendEmail(emailAddress, subject, message, {
        htmlBody: message,
        replyTo: "birthday@example.com",
        name: "Birthday wishes",
        bcc: "myself@example.com"
      });
    }
  }
}

另外补充两个小细节：

• 把currentYear这类重复计算的变量提到循环外面，提升脚本运行效率；
• 用模板字符串（`...`）代替字符串拼接，让代码更易读。

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