GAS请假申请工具优化：日期格式化与审批行迁移需求

问题背景 #

我是GAS新手，基于Google Forms和Google Sheets搭建了数字化请假申请系统，已实现审批时发送邮件的功能，但还有两个需求待解决：

• 将日期格式化为无额外时间的mm/dd/yyyy格式：用.toLocaleDateString("en-US")可实现，但空单元格会报错，需仅对已填充单元格生效（全天请假对应F、K列，迟到对应I列）
• 邮件发送后，将该行数据迁移至“approved”工作表

现有代码 #

function sendEmailedit(e){
if (e.range.columnStart != 12 || e.value != "TRUE") return;    
const rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,11).getValues();
let y = rData[0][10];
let late = rData[0][8]
let beg = rData[0][5];
let end = rData[0][6];
let ESLP = "https://docs.google.c"
let Subject = "Staff absence/time off request for";
let cc = "pn, td, ma"
let msg = "Your staff absence/late request for" + " " + beg + "-" + end + " " + late + " " +           "has been approved by ." + "<p>" +
 "<b><u>All Staff:</b></u>" + "<br>" +
 "Please add a banner reminder (all day) onto Google Calendar and include both es and es" + "       <p>" +
"<b><u>Teachers:</b></u>" + "<br>" +
"Please email your lesson plans to ma and pn by 8:00am the day you will be out" + "<p>" +
"<b><u>Sub plans must include:</b></u>" + "<br>" +
"classroom procedures, clear directions for the scholars/ substitute, and an agenda with time         stamps. You can use your own template or the" + " " + '<a href="' + ESLP + '">ESAT Sub Lesson     Plan</a>' + "<p>" +
"Thank you!";

Logger.log(msg);

GmailApp.sendEmail( y , "Staff absence/late request", msg,{
htmlBody: msg});
}

解决方案 #

1. 安全的日期格式化 #

新增工具函数，先判断内容是否为有效日期，再执行格式化，空值直接返回空字符串：

// 日期格式化工具函数：仅对有效日期生效，空值返回空字符串
function formatDate(date) {
  return date instanceof Date && !isNaN(date) ? date.toLocaleDateString("en-US") : "";
}

替换原代码中日期变量的赋值：

// 替换原日期变量定义
let late = formatDate(rData[0][8]);
let beg = formatDate(rData[0][5]);
let end = formatDate(rData[0][6]);

2. 邮件发送后迁移行数据 #

在邮件发送代码后添加迁移逻辑：获取当前工作表与目标工作表，复制当前行到目标表末尾，再删除原行：

// 邮件发送后执行行迁移
const activeSheet = e.source.getActiveSheet();
const targetSheet = e.source.getSheetByName("approved");
if (targetSheet) {
  // 复制当前行（含格式和值）到approved表末尾
  activeSheet.getRange(e.range.rowStart, 1, 1, activeSheet.getLastColumn())
    .copyTo(targetSheet.getRange(targetSheet.getLastRow() + 1, 1), SpreadsheetApp.CopyPasteType.PASTE_VALUES_AND_FORMATTING);
  // 删除原工作表中的当前行
  activeSheet.deleteRow(e.range.rowStart);
}

修改后的完整代码 #

// 日期格式化工具函数：仅对有效日期生效，空值返回空字符串
function formatDate(date) {
  return date instanceof Date && !isNaN(date) ? date.toLocaleDateString("en-US") : "";
}

function sendEmailedit(e){
  if (e.range.columnStart != 12 || e.value != "TRUE") return;    
  const activeSheet = e.source.getActiveSheet();
  const rData = activeSheet.getRange(e.range.rowStart,1,1,11).getValues();
  
  let y = rData[0][10];
  let late = formatDate(rData[0][8]);
  let beg = formatDate(rData[0][5]);
  let end = formatDate(rData[0][6]);
  let ESLP = "https://docs.google.c";
  let Subject = "Staff absence/time off request for";
  let cc = "pn, td, ma";
  
  let msg = "Your staff absence/late request for" + " " + beg + "-" + end + " " + late + " " + "has been approved by ." + "<p>" +
           "<b><u>All Staff:</b></u>" + "<br>" +
           "Please add a banner reminder (all day) onto Google Calendar and include both es and es" + "<p>" +
           "<b><u>Teachers:</b></u>" + "<br>" +
           "Please email your lesson plans to ma and pn by 8:00am the day you will be out" + "<p>" +
           "<b><u>Sub plans must include:</b></u>" + "<br>" +
           "classroom procedures, clear directions for the scholars/ substitute, and an agenda with time stamps. You can use your own template or the" + " " + '<a href="' + ESLP + '">ESAT Sub Lesson Plan</a>' + "<p>" +
           "Thank you!";

  Logger.log(msg);

  // 发送邮件（启用原代码定义的抄送参数）
  GmailApp.sendEmail(y, "Staff absence/late request", msg, {
    htmlBody: msg,
    cc: cc
  });

  // 迁移行到approved工作表
  const targetSheet = e.source.getSheetByName("approved");
  if (targetSheet) {
    activeSheet.getRange(e.range.rowStart, 1, 1, activeSheet.getLastColumn())
      .copyTo(targetSheet.getRange(targetSheet.getLastRow() + 1, 1), SpreadsheetApp.CopyPasteType.PASTE_VALUES_AND_FORMATTING);
    activeSheet.deleteRow(e.range.rowStart);
  }
}

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