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: https://docs.google.com/spreadsheets/d/1FcOKVkhdjK-vGuFPWSR2ZreBYFI6EK7EnQj3yKPvttk/edit?usp=sharing
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 });
});
}