Estoy tratando de escribir un javascript (Googlescript) para recorrer una hoja de Google con tareas específicas asignadas a diferentes usuarios y tomar todas las filas asignadas a "UserA" y enviar un correo electrónico con la lista de todas las tareas asignadas a userA. Luego haz lo mismo con UserB
Las tareas no están ordenadas por usuario.
Las columnas en la hoja en orden son: Nombre, Descripción, Estado, Propietario, Fecha de vencimiento
Puedo enviar correos electrónicos, con el problema de la información, el problema es que si tiene X cantidad de tareas asignadas, recibe X cantidad de correos electrónicos. Lo cual es un problema si tienes más de 10 tareas....
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(); } }}
Esta versión muestra un bloque de texto básico en un cuadro de diálogo para cada correo electrónico para que pueda probarlo sin enviar correos electrónicos.
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') }Esta versión debería enviar correos electrónicos. Es posible que desee modificar un poco el mensaje.
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); }); }