Appsheet场景下用Apps Script发送含24小时更新的项目子任务笔记邮件方法

核心逻辑 #

基于三个表的主键外键关联，筛选出近24小时更新的子任务和笔记，按项目聚合后发送给对应项目所有者。

完整实现代码 #

function sendDailyProjectUpdates() {
  // 1. 获取表格和所有数据
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const projectSheet = ss.getSheetByName('Project');
  const subtaskSheet = ss.getSheetByName('Subtasks');
  const notesSheet = ss.getSheetByName('Notes');
  // 跳过表头行，获取所有有效数据
  const projects = projectSheet.getDataRange().getValues().slice(1);
  const subtasks = subtaskSheet.getDataRange().getValues().slice(1);
  const notes = notesSheet.getDataRange().getValues().slice(1);
  // 2. 计算24小时前的时间戳，作为筛选阈值
  const oneDayAgo = Date.now() - 24 * 60 * 60 * 1000;
  // 3. 初始化项目数据容器，关联项目ID、名称、所有者邮箱
  const projectMap = {};
  projects.forEach(projRow => {
    // 注意：列索引从0开始计数，可根据你实际的表列顺序调整
    const projId = projRow[0]; // 第一列为Project ID（主键）
    const projName = projRow[1]; // 第二列为项目名称
    const ownerEmail = projRow[2]; // 第三列为项目所有者邮箱
    projectMap[projId] = {
      name: projName,
      email: ownerEmail,
      recentSubtasks: [],
      recentNotes: []
    };
  });
  // 4. 筛选近24小时更新的子任务，关联到对应项目
  subtasks.forEach(subtaskRow => {
    const parentProjId = subtaskRow[1]; // 第二列为Parent Project ID（外键）
    const updateTime = new Date(subtaskRow[3]).getTime(); // 第四列为last_update
    if (updateTime >= oneDayAgo && projectMap[parentProjId]) {
      projectMap[parentProjId].recentSubtasks.push({
        title: subtaskRow[2], // 第三列为子任务标题
        status: subtaskRow[4], // 第五列为子任务状态
        updateTimeStr: new Date(subtaskRow[3]).toLocaleString()
      });
    }
  });
  // 5. 筛选近24小时更新的笔记，关联到对应项目
  notes.forEach(noteRow => {
    const parentProjId = noteRow[1]; // 第二列为Parent Project ID（外键）
    const updateTime = new Date(noteRow[3]).getTime(); // 第四列为last_update
    if (updateTime >= oneDayAgo && projectMap[parentProjId]) {
      projectMap[parentProjId].recentNotes.push({
        content: noteRow[2], // 第三列为笔记内容
        updateTimeStr: new Date(noteRow[3]).toLocaleString()
      });
    }
  });
  // 6. 遍历项目，有更新内容的发送邮件
  Object.values(projectMap).forEach(proj => {
    // 无更新内容跳过
    if (proj.recentSubtasks.length === 0 && proj.recentNotes.length === 0) return;
    // 生成HTML格式邮件内容
    let emailHtml = `<h2>项目「${proj.name}」最近24小时更新汇总</h2>`;
    if (proj.recentSubtasks.length) {
      emailHtml += `<h3>更新的子任务：</h3><ul>`;
      proj.recentSubtasks.forEach(task => {
        emailHtml += `<li>${task.title} | 状态：${task.status} | 更新时间：${task.updateTimeStr}</li>`;
      });
      emailHtml += `</ul>`;
    }
    if (proj.recentNotes.length) {
      emailHtml += `<h3>更新的笔记：</h3><ul>`;
      proj.recentNotes.forEach(note => {
        emailHtml += `<li>${note.content} | 更新时间：${note.updateTimeStr}</li>`;
      });
      emailHtml += `</ul>`;
    }
    // 发送邮件
    MailApp.sendEmail({
      to: proj.email,
      subject: `【每日更新】项目${proj.name}有新动态`,
      htmlBody: emailHtml
    });
  });
}

配置说明 #

• 列索引需要根据你实际表格的列顺序调整，索引从0开始计数，比如第一列是0，第二列是1以此类推
• 邮件的标题、内容排版可以根据需求修改HTML模板部分的代码
• 如需定时发送，可以在Apps Script编辑器的「触发器」中添加定时触发规则，设置每日固定时间运行该函数

调试建议 #

• 首次运行前可以将代码末尾的MailApp.sendEmail替换为Logger.log(emailHtml)，运行后查看日志输出的内容是否符合预期，确认数据正确后再恢复发送逻辑
• 若出现日期格式解析错误，可调整new Date()的参数适配你表中last_update列的日期格式
• 首次运行需要授权脚本访问电子表格和Gmail的权限，按照提示完成授权即可

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