I have this code (thank you, Tanaike). The problem I am having with it. Is it sending emails to the project owners even if there is no task update and no note update within 24 hours. So they're getting blank emails. What I am trying to do, is so IF there is NO task updates AND NO Updates - dont send the email

Public link to spreadsheet:

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

  // Retrieve Project 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 All Tasks.
  const tasks = sheetITPM_Tasks.getRange("A2:H"   sheetITPM_Tasks.getLastRow()).getValues().reduce((o, r) => (o[r[2]] = o[r[2]] ? [...o[r[2]], { description: r[3], status: r[4], owner: r[5], dueDate: r[6], t_lastupdate: r[7] }] : [{ description: r[3], status: r[4], owner: r[5], dueDate: r[6], t_lastupdate: r[7] }], o), {});

  // Retrieve All Notes.
  const notes = sheetNotes.getRange("A2:F"   sheetNotes.getLastRow()).getValues().reduce((o, r) => (o[r[1]] = o[r[1]] ? [...o[r[1]], { note: r[2], author: r[3], date: r[4], n_lastupdate: r[5] }] 
  : [{ note: r[2], author: r[3], date: r[4], n_lastupdate: r[5] }], o), {});

  Object.entries(notes).forEach(([, v]) => v.sort((a, b) => a.n_lastupdate.getTime() < b.n_lastupdate.getTime() ? 1 : -1)); //Sort Notes by latest update 

  //This determines if the tasks lastest update column is within 25 hours of the current time
  const now = new Date().getTime();
  const before24h = now - (25 * 60 * 60 * 1000);
  Object.entries(tasks).forEach(([k, v]) => {
    tasks[k] = v.filter(({t_lastupdate}) => {
      const temp = t_lastupdate.getTime();
      return now > temp && temp > before24h;
    });
  });

  //This determiens if the notes lastest update column is within 25 hours of the current time
  Object.entries(notes).forEach(([k, v]) => {
    notes[k] = v.filter(({n_lastupdate}) => {
      const temp = n_lastupdate.getTime();
      return now > temp && temp > before24h;
    });
  });


  // Send our emails to project owners with our email format 
  values.forEach(({ id, name, email }) => {
    const message = [
      `Here is the 24 hour project update for Project: \n${name}`,
      "",
      `Assigned Tasks:`,
      ...tasks[id].map(({ description, status, owner, dueDate }, i) => [`Task ${i   1}:`, `Task Description: ${description}`,`Task Owner: ${owner}`,`Task Status: ${status}`,
      `Task Due Date: ${dueDate}`, ""].join("\n")),
      
      `Project Notes:`,
      ...notes[id].map(({ note,author }, i) => [`Note ${i   1}: ${note}`,`Author: ${author}`, ""].join("\n")),
    ].join("\n");
    MailApp.sendEmail({ to: email, subject: "Project Update", body: message });
  });
}

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>

CodePudding user response：

I believe your goal is as follows.

• You don't want to send the email when both values of tasks and notes have no values.

In this case, how about the following modification?

From:

values.forEach(({ id, name, email }) => {
  const message = [

To:

values.forEach(({ id, name, email }) => {
  if (tasks[id].length == 0 && notes[id].length == 0) return; // Added
  const message = [

• By this modification, when both values of tasks and notes have no values, no email is sent.

Note:

• If you don't want to send the email when one of tasks and notes has no values, please modify as follows.

    if (tasks[id].length == 0 || notes[id].length == 0) return;