如何用Apps Script关联多工作表数据向项目所有者发送含子任务备注的邮件

实现思路 #

• 预读取子任务表、备注表的全量数据，以项目ID为键建立映射索引，匹配时无需重复遍历表格
• 遍历任务表数据时按项目所有者分组，同一所有者名下的所有项目自动归集
• 每个项目匹配对应子任务、备注后组装邮件内容发送

修改后完整代码 #

function sendProjectEmails() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // 读取子任务表，建立项目ID-子任务列表映射
  const subtaskSh = ss.getSheetByName("Subtasks");
  const subtaskRows = subtaskSh.getDataRange().getValues().slice(1); // 跳过表头
  const subtaskMap = {};
  subtaskRows.forEach(row => {
    const projectId = row[0]; // 此处0对应子任务表第1列是项目ID，可根据实际调整
    if (!subtaskMap[projectId]) subtaskMap[projectId] = [];
    // 仅保留未完成子任务，不需要可删除if判断
    if (row[2] !== 'Complete') subtaskMap[projectId].push(row[1]);
  });
  // 读取备注表，建立项目ID-备注列表映射
  const noteSh = ss.getSheetByName("Notes");
  const noteRows = noteSh.getDataRange().getValues().slice(1);
  const noteMap = {};
  noteRows.forEach(row => {
    const projectId = row[0]; // 此处0对应备注表第1列是项目ID，可根据实际调整
    if (!noteMap[projectId]) noteMap[projectId] = [];
    noteMap[projectId].push(`${row[2].toDateString()}: ${row[1]}`); // 备注时间+内容，可调整格式
  });
  // 读取任务表数据
  const taskSh = ss.getSheetByName("ITPM_Tasks");
  const taskRows = taskSh.getDataRange().getValues().slice(1);
  const ownerMap = {}; // 存储格式：{所有者邮箱: [{项目ID, 项目名, 任务列表, 子任务列表, 备注列表}]}
  taskRows.forEach(row => {
    const projectId = row[0]; // 任务表第1列是项目ID
    const projectName = row[1]; // 任务表第2列是项目名
    const taskDesc = row[3]; // 任务表第4列是任务描述
    const status = row[4]; // 任务表第5列是任务状态
    const ownerEmail = row[5]; // 任务表第6列是项目所有者邮箱
    const dueDate = row[6]; // 任务表第7列是截止日期
    // 仅处理未完成的任务
    if (status === 'Complete') return;
    // 初始化所有者数据
    if (!ownerMap[ownerEmail]) ownerMap[ownerEmail] = {};
    // 初始化所有者名下的项目数据，避免重复添加同一项目
    if (!ownerMap[ownerEmail][projectId]) {
      ownerMap[ownerEmail][projectId] = {
        name: projectName,
        tasks: [],
        subtasks: subtaskMap[projectId] || [],
        notes: noteMap[projectId] || []
      }
    }
    // 加入当前任务
    ownerMap[ownerEmail][projectId].tasks.push(`${taskDesc}（截止日期：${dueDate.toDateString()}）`);
  });
  // 批量发送邮件
  const subject = '每周项目汇总：您负责的项目最新进展提醒';
  Object.keys(ownerMap).forEach(ownerEmail => {
    const projects = Object.values(ownerMap[ownerEmail]);
    let msg = `您好，以下是您当前负责的所有未完成项目汇总：\n\n`;
    projects.forEach((project, idx) => {
      msg += `=== 项目${idx+1}：${project.name} ===\n`;
      msg += `【任务列表】\n`;
      project.tasks.length ? project.tasks.forEach((t, i) => msg += `${i+1}. ${t}\n`) : msg += `无\n`;
      msg += `\n【子任务列表】\n`;
      project.subtasks.length ? project.subtasks.forEach((st, i) => msg += `${i+1}. ${st}\n`) : msg += `无\n`;
      msg += `\n【备注列表】\n`;
      project.notes.length ? project.notes.forEach((n, i) => msg += `${i+1}. ${n}\n`) : msg += `无\n`;
      msg += `\n-------------------------\n\n`;
    });
    msg += `请及时跟进项目进展，如有疑问可联系管理员。`;
    MailApp.sendEmail(ownerEmail, subject, msg);
  });
}

注意事项 #

• 代码中所有列索引均从0开始计数，请根据你实际表格的列顺序调整对应索引值
• 可根据需求删除子任务的完成状态过滤逻辑，展示所有子任务
• 邮件主题、正文文案可自行修改匹配使用场景，也可将邮件改为HTML格式优化展示效果

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