发送一封电子邮件给项目所有者w/所有相关的项目子任务& w/All Project

Question

我正试图为Appsheet应用程序编写Javascript/appscript。如果有人能帮忙的话，我想不出这点。

我有三张纸:项目子任务说明

我希望能够向项目所有者发送一封电子邮件，其中包括分配给该项目的所有任务的项目名称(通过PK/FK、项目名称进行的关系)以及分配给该项目的所有注释(与notes /FK、项目名称相同)，其中last_update列在当前24小时内。(即在过去24小时内对任何任务或笔记所作的任何改变或创作)

• 我提供了所有表、它们的列的示例和理想的示例输出。
• 到电子表格的公共链接：https://docs.google.com/spreadsheets/d/1FcOKVkhdjK-vGuFPWSR2ZreBYFI6EK7EnQj3yKPvttk/edit?usp=sharing

idealOutput

Answer 1

我相信你的目标如下。

• 您希望通过从3个工作表中检索值来创建电子邮件消息，如下所示。(以下图片来自您的问题。)

• 您希望使用Google脚本来实现这一点。
• 从您对Task order does not matter. Notes preferablly sorted in the email by most recent (not required)的评论中，我认为您可能希望只对"Notes“的值进行排序。

在本例中，下面的示例脚本如何？

示例脚本：

请将以下脚本复制并粘贴到电子表格的脚本编辑器中。还有，请查查单张的名字。请运行myFunction。

function myFunction() {
  // Retrieve 3 sheets.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [sheetITPM, sheetITPM_Tasks, sheetNotes] = ["ITPM", "ITPM_Tasks", "Notes"].map(e => ss.getSheetByName(e));

  // Retrieve IDs, names and emails.
  const values = sheetITPM.getRange("A2:J" + sheetITPM.getLastRow()).getValues().map(r => ({ id: r[0], name: r[1], email: r[9] }));

  // Retrieve Tasks.
  const tasks = sheetITPM_Tasks.getRange("A2:G" + sheetITPM_Tasks.getLastRow()).getValues().reduce((o, r) => (o[r[2]] = o[r[2]] ? [...o[r[2]], { description: r[3], status: r[4], dueDate: r[6] }] : [{ description: r[3], status: r[4], dueDate: r[6] }], o), {});

  // Retrieve Notes.
  const notes = sheetNotes.getRange("A2:E" + sheetNotes.getLastRow()).getValues().reduce((o, r) => (o[r[1]] = o[r[1]] ? [...o[r[1]], { note: r[2], date: r[4] }] : [{ note: r[2], date: r[4] }], o), {});
  Object.entries(notes).forEach(([, v]) => v.sort((a, b) => a.date.getTime() < b.date.getTime() ? 1 : -1)); // If you don't want to sort the values of "Notes", please remove this line.

  // Send emails.
  values.forEach(({ id, name, email }) => {
    const message = [
      `Here is the project update for: ${name}`,
      "",
      `Assigned Tasks:`,
      ...tasks[id].map(({ description, status, dueDate }, i) => [`Task ${i + 1}:`, description, status, dueDate, ""].join("\n")),
      "",
      `Project Notes:`,
      ...notes[id].map(({ note }, i) => [`Note ${i + 1}:`, note, ""].join("\n")),
    ].join("\n");
    MailApp.sendEmail({ to: email, subject: "Project update", body: message });
  });
}

• 当运行此脚本时，通过从3张表中检索值来发送电子邮件。电子邮件地址来自"ITPM“表格的"J”栏。

注意：

• 此示例脚本用于示例电子表格。因此，当电子表格的结构从示例电子表格中更改时，可能无法使用此脚本。所以请小心点。测试此脚本时，请使用此示例电子表格.

参考文献：

• 减少()
• forEach()
• sendEmail(信息)

添加：

OP有个新问题，

它是否可能只包括说明和任务，他们的专栏上一次更新是在24小时内现在？

示例脚本：

function myFunction() {
  // Retrieve 3 sheets.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [sheetITPM, sheetITPM_Tasks, sheetNotes] = ["ITPM", "ITPM_Tasks", "Notes"].map(e => ss.getSheetByName(e));

  // Retrieve IDs, names and emails.
  const values = sheetITPM.getRange("A2:J" + sheetITPM.getLastRow()).getValues().map(r => ({ id: r[0], name: r[1], email: r[9] }));

  // Retrieve Tasks.
  const tasks = sheetITPM_Tasks.getRange("A2:G" + sheetITPM_Tasks.getLastRow()).getValues().reduce((o, r) => (o[r[2]] = o[r[2]] ? [...o[r[2]], { description: r[3], status: r[4], dueDate: r[6] }] : [{ description: r[3], status: r[4], dueDate: r[6] }], o), {});

  // Retrieve Notes.
  const notes = sheetNotes.getRange("A2:E" + sheetNotes.getLastRow()).getValues().reduce((o, r) => (o[r[1]] = o[r[1]] ? [...o[r[1]], { note: r[2], date: r[4] }] : [{ note: r[2], date: r[4] }], o), {});
  Object.entries(notes).forEach(([, v]) => v.sort((a, b) => a.date.getTime() < b.date.getTime() ? 1 : -1)); // If you don't want to sort the values of "Notes", please remove this line.

  // By OP's new question, I added the following script.
  const now = new Date().getTime();
  const before24h = now - (24 * 60 * 60 * 1000);
  Object.entries(tasks).forEach(([k, v]) => {
    tasks[k] = v.filter(({dueDate}) => {
      const temp = dueDate.getTime();
      return now < temp && temp > before24h;
    });
  });
  Object.entries(notes).forEach(([k, v]) => {
    notes[k] = v.filter(({date}) => {
      const temp = date.getTime();
      return now < temp && temp > before24h;
    });
  });

  // Send emails.
  values.forEach(({ id, name, email }) => {
    const message = [
      `Here is the project update for: ${name}`,
      "",
      `Assigned Tasks:`,
      ...tasks[id].map(({ description, status, dueDate }, i) => [`Task ${i + 1}:`, description, status, dueDate, ""].join("\n")),
      "",
      `Project Notes:`,
      ...notes[id].map(({ note }, i) => [`Note ${i + 1}:`, note, ""].join("\n")),
    ].join("\n");
    MailApp.sendEmail({ to: email, subject: "Project update", body: message });
  });
}

• 但是，关于您的新问题，当使用示例电子表格时，在本例中，tasks和notes不是值。请小心这个。