Send a Single Email to Project Owner w/All Related Project Subtasks & w/All Project Notes-CodePudding

I am trying to write a Javascript/appscript for an Appsheet app. I cant figure this out, if someone could please help.

I have Three Sheets: Project Subtasks Notes

I would like to be able to send the Project owner an email, that includes the project name all of the tasks assigned to that project (relationship via PK/FK ID,Project Name) and all of the notes assigned to that project (same relationship as Notes PK/FK ID,Project Name) where the last_update column is within 24 hours of right now. (I.E any changes or creations made in the last 24 hours to any task or note)

I have provided a sample of all the tables, their columns, and an ideal sample output
Public link to my spreadsheet: https://docs.google.com/spreadsheets/d/1FcOKVkhdjK-vGuFPWSR2ZreBYFI6EK7EnQj3yKPvttk/edit?usp=sharing

idealOutput

CodePudding user response：

I believe your goal is as follows.

You want to create the email message by retrieving the values from 3 sheets as follows. (The following image is from your question.)
You want to achieve this using Google Apps Script.
From your comment of Task order does not matter. Notes preferablly sorted in the email by most recent (not required), I thought that you might want to sort the values of only "Notes".

In this case, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet. And, please check the sheet names. And please run 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 });
  });
}

When this script is run, the emails are sent by retrieving the values from 3 sheets. The email addresses are used from the column "J" of "ITPM" sheet.

Note:

This sample script is for your sample Spreadsheet. So when the structure of the Spreadsheet is changed from your sample Spreadsheet, this script might not be able to be used. So please be careful about this. When you test this script, please use your this sample Spreadsheet.

References:

Added:

OP's following new question,

Is it possible to have it only include the notes and tasks where their column last update is within 24 hours of right now?

Sample script:

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 });
  });
}

But, about your new question, when your sample Spreadsheet is used, in this case, tasks and notes are no values. Please be careful this.