CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Brandon Ekbatani
Brandon Ekbatani

Reputation: 29

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

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: https://docs.google.com/spreadsheets/d/1aGojtDy9sDpGexiT_ixF_H_95GG_uiYeAkNUWzYkKSE/edit?usp=sharing

Upvotes: 0

Views: 54

Answers (1)

NightEye
NightEye

Reputation: 11214

You can try getting the list of IDs for backlog projects first and then using includes to check if the project name of the task belongs to the list of IDs. See code below:

Script:

  let oners = {pA:[]};

  // get backlog projects, then create an array containing the ids of those projects
  const backlogProjects = project.filter(x => x.status == 'Backlog').map(x => x.id);

  // dont include tasks with status "Complete"
  vs.forEach((r,i) => {
    let [name,desc,status,owner,due] = r;
    // check if project name matches the IDs present in the generated array above
    if(status != 'Complete' && !backlogProjects.includes(name)) {
     if(!oners.hasOwnProperty(owner)) {
       oners[owner]=[];
       oners[owner].push(r);
       oners.pA.push(owner)
     } else {
       oners[owner].push(r);
     }
    }
  });

Output:

output

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

Upvotes: 1

Related Questions