I have code here that when it runs, it emails the Project Owner the project name, and all related tasks & notes for all projects. It checks to see if the LastUpdate is within 24 hours of the current time.
(Thank you @Tanaike)
Ideally what I would like, is in Appsheet when I press a button it makes a webhook/api call reference to the single project based on an API call/webhook? so it doesnt send an email for every single project. Just a single project from referenced row from a webhook/api/appsheet button.
Here is the Code:
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:H" + 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], t_lastUpdate: r[7] }] : [{ description: r[3], status: r[4], dueDate: r[6], t_lastUpdate: r[7] }], o), {});
// Retrieve 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], date: r[4], n_lastUpdate: r[5]}] : [{ note: r[2], date: r[4], n_lastUpdate: r[5] }], o), {});
Object.entries(notes).forEach(([, v]) => v.sort((a, b) => a.date.getTime() < b.date.getTime() ? 1 : -1)); // Sort Notes by Date field
// Check if task last update within 24 hours
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 = dueDate.getTime();
return now > temp && temp > before24h;
});
});
Object.entries(notes).forEach(([k, v]) => {
notes[k] = v.filter(({n_lastUpdate}) => {
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, t_lastUpdate }, i) => [`Task ${i + 1}:`, description, status, dueDate, t_lastUpdate, ""].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 });
});
}
