Business
Jobs
  • About Us
  • Solutions
    • Job Postings
      Post your job and receive qualified candidates in 48h.
    • Candidate Assessments
      500+ technical and psychological tests, plus anti-fraud.
    • Headhunting
      Tailor-made executive search from start to finish.
    • Payroll + EOR
      Payroll dispersal and EOR across 15+ LATAM countries.
  • Pricing
  • Jobs

0

139
Views
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

about 4 years ago · Juan Pablo Isaza
1 answers
Answer question

0

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.

about 4 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 Sales
Legal
Terms and conditions Privacy policy
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Show me some job opportunities
There's an error!