I am trying to write a javascript (Googlescript) to loop through a Google Sheet with Specific Tasks assigned to different users and grab all the rows assigned to "UserA" and send an email with the list of all the tasks assigned to userA. Then do the same with UserB
Tasks are not sorted by user.
Columns in the sheet in order are: Name, Description, Status, Owner, Due Date
I am able to send out Emails, with the information problem, the issue is that if you have X amount of Tasks assigned to you get X amount of emails. Which is a problem if you have 10+ tasks....
function sendEmails() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ITPM_Tasks");
var lastRow = sheet.getLastRow();
var startRow = 2; // First row of data to process
var numRows = lastRow - 1; // Number of rows to process
// Fetch the range of cells A:P
var dataRange = sheet.getRange(startRow, 1, numRows, 5);
// Fetch values for each row in the Range.
var data = dataRange.getValues();
for (var i = 0; i < data.length; ++i) {
var row = data[i];
var owner = row[3];
var status = row[2];
var desc = row[1];
var due = row[4];
var due_date = due.toDateString();
var message = 'Reminder: A task is assigned to you\n\n' + desc + '\nwith a due date of '
+ due_date + ' \n\nPlease complete and change status to completed or notify the ITPM Team \n\nThank You'
if (status != 'Complete') { // Prevents sending if status is not completed
var subject = 'Weekly Reminder: A task is assigned to you ';
MailApp.sendEmail(owner, subject, message);
SpreadsheetApp.flush();
}
}
}
This version displays a basic a block of text in a dialog for each email so that I could test it without sending emails
function sendEmails() {
let s = '';
const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
const lastRow = sh.getLastRow();
const startRow = 2; // First row of data to process
const numRows = lastRow - 1; // Number of rows to process
const rg = sh.getRange(startRow, 1, numRows, 5);
const vs = rg.getValues();
let oners = {pA:[]};
vs.forEach((r,i) => {
let [name,desc,status,owner,due] = r;
if(status != 'Complete') {
if(!oners.hasOwnProperty(owner)) {
oners[owner]=[];
oners[owner].push(r);
oners.pA.push(owner)
} else {
oners[owner].push(r);
}
}
});
let subject = 'Weekly Reminder: The following tasks are assigned to you.';
oners.pA.forEach(p => {
let msg = `Tasks:\n`
msg += `Owner:${oners[p][0][3]}\n`;
msg += `Subject: ${subject}\n`
oners[p].forEach((r,i) => {
let [name,desc,status,owner,due] = r;
msg += `Task- ${i+1}\n`;
msg += `Desc:${desc}\n`;
msg += `Due Date: ${due.toDateString()}\n`
});
msg += `Please complete and change status to completed or notify the ITPM Team \nThank You\n`;
//MailApp.sendEmail(owner, subject, msg);
msg += `\n\n***********************************\n\n`;
s+=msg;
});
SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(`<textarea cols="50" rows="12">${s}</textarea>`),'Simple Dialog')
}
This version should send emails. You may wish to doctor up the message a bit.
function sendEmails() {
let s = '';
const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
const lastRow = sh.getLastRow();
const startRow = 2; // First row of data to process
const numRows = lastRow - 1; // Number of rows to process
const rg = sh.getRange(startRow, 1, numRows, 5);
const vs = rg.getValues();
let oners = {pA:[]};
vs.forEach((r,i) => {
let [name,desc,status,owner,due] = r;
if(status != 'Complete') {
if(!oners.hasOwnProperty(owner)) {
oners[owner]=[];
oners[owner].push(r);
oners.pA.push(owner)
} else {
oners[owner].push(r);
}
}
});
let subject = 'Weekly Reminder: The following tasks are assigned to you.';
oners.pA.forEach(p => {
let msg = `Tasks:\n`
msg += `Owner:${oners[p][0][3]}\n`;
msg += `Subject: ${subject}\n`
oners[p].forEach((r,i) => {
let [name,desc,status,owner,due] = r;
msg += `Task- ${i+1}\n`;
msg += `Desc:${desc}\n`;
msg += `Due Date: ${due.toDateString()}\n`
});
msg += `Please complete and change status to completed or notify the ITPM Team \nThank You\n`;
MailApp.sendEmail(oners[p][0][3], subject, msg);
});
}