Company logo
  • Jobs
  • Bootcamp
  • About Us
  • For professionals
    • Home
    • Jobs
    • Courses
    • Questions
    • Teachers
    • Bootcamp
  • For business
    • Home
    • Our process
    • Plans
    • Assessments
    • Payroll
    • Blog
    • Calculator

0

35
Views
Loop through Googlesheet and grab all rows matching a specific critiera in a user column

I am trying to write a javascript (Googlescript) to loop through a Google Sheet with Specific Tasks assigned to different users and grab all the rows assigned to "UserA" and send an email with the list of all the tasks assigned to userA. Then do the same with UserB

  • Tasks are not sorted by user.

  • Columns in the sheet in order are: Name, Description, Status, Owner, Due Date

  • I am able to send out Emails, with the information problem, the issue is that if you have X amount of Tasks assigned to you get X amount of emails. Which is a problem if you have 10+ tasks....

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

    }

7 months ago · Juan Pablo Isaza
1 answers
Answer question

0

Sending all the tasks for each person in one email

This version displays a basic a block of text in a dialog for each email so that I could test it without sending emails

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')
}

This version should send emails. You may wish to doctor up the message a bit.

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);
  });
}
7 months 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 job Plans Our process Sales
Legal
Terms and conditions Privacy policy
© 2023 PeakU Inc. All Rights Reserved.