• Jobs
  • About Us
  • professionals
    • Home
    • Jobs
    • Courses and challenges
  • business
    • Home
    • Post vacancy
    • Our process
    • Pricing
    • Assessments
    • Payroll
    • Blog
    • Sales
    • Salary Calculator

0

70
Views
Recorra Googlesheet y tome todas las filas que coincidan con un criterio específico en una columna de usuario

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(); } }

    }

almost 3 years ago · Juan Pablo Isaza
1 answers
Answer question

0

Envío de todas las tareas de cada persona en un solo correo electrónico

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); }); }
almost 3 years ago · Juan Pablo Isaza Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Our process Sales
Legal
Terms and conditions Privacy policy
© 2025 PeakU Inc. All Rights Reserved.

Andres GPT

Recommend me some offers
I have an error