Home > Mobile >  Send Email to Task owners as long as task is not complete and Project is not backlogged
Send Email to Task owners as long as task is not complete and Project is not backlogged

Time:01-12

I have this script below, that emails the person a copy of all of their assigned tasks that are not complete. However, I would like it to skip all Tasks where the Main Projects status is "backlog" Here is my current code, and a link to a copy of the tables:

function sendEmails() {

  // Retrieve Project IDs, Project names and Project Status
 const project = sheetITPM.getRange("A2:J"   sheetITPM.getLastRow()).getValues().map(r => ({ id: r[0], name: r[1],  status: r[2]}));

//get Tasks Sheet and all rows needed
  let s = '';
  const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ITPM_Tasks");
  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, 3, numRows, 6);
  const vs = rg.getValues();
  let oners = {pA:[]};

  //function to NOT include projects with status: "backlog"

  //dont include tasks with status "Complete"
  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);
     }
    }
  });

//email subject and start message
  let subject = 'IT Board - Weekly Reminder: The following tasks are assigned to you.';
  oners.pA.forEach(p => {
     let msg = `These Tasks below are assigned to you:\n`

//task array 
    oners[p].forEach((r,i) => {
      let [name,desc,status,owner,due] = r;
        msg  = `Task - ${i 1}\n`;
        msg  = `Description: ${desc}\n`;
        msg  = `Due Date: ${due}\n\n`
    });

    msg  = `notify the IT Project Management Team of any updates\n\nThank You`;
    
    //send email to the task owner with their task array   subject   our message 
    MailApp.sendEmail(oners[p][0][3], subject, msg);
  });
}

Link to sheet: output

Output shows no nl7k28t in the tasks as that project is still in Backlog status.

  •  Tags:  
  • Related