Use app scripts to pick email from sheets and send data as html table-CodePudding

I am trying to pick email of the specific row and sending data to them as html table. i am getting an error of not find the recipient.

I am new guy to coding so any small help would be useful.enter image description here

enter image description here

I am trying to pull the email id from the sheet and I person should get only one row data as separate mails

function expiredjobs() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Oct-22");
  var startRow = 2; // First row of data to process
  var numRows = sheet.getLastRow(); // Number of rows to process
  var numColu = sheet.getLastColumn(); // Number of colums to process 

  var dataRange = sheet.getRange(startRow, 1, numRows, numColu);
  var data = dataRange.getValues()
  
  var bodyEmail = "Dear sir, <br/>  Kindly provide the recommendation for the below mentioned near miss, also provide the supporting document to close the incident. <br/> "  

  var table = "<html><body><br><table border=1>"
  var colVal = "";
  for (var i = 1; i < 2;   i) 
  {
      table = table   "<tr>"
       for (var colNo = 1; colNo <=11; colNo  ) 
       {
        colVal = sheet.getRange(i , colNo).getDisplayValue();
        table = table   "<th>"   colVal   "</th>";
        }        
    table = table   "</tr>"
  }

  for (var i = 2; i < data.length;   i)
  {
    var email = sheet.getRange(i,13).getValue().toString();
    var status = sheet.getRange(i,12).getValue().toString();
    var itemno = sheet.getRange(i,1).getValues().flat().filter(String).pop();
    Logger.log(email)
    Logger.log(status)  
    if(status === "Open")
    {
    Logger.log("1");
        table = table   "<tr>"
       for (var colNo = 1; colNo <=11; colNo  ) 
       {
        colVal = sheet.getRange(i , colNo).getDisplayValue();
        table = table   "<td>"   colVal   "</td>";
        }        
    table = table   "</tr>"
    }
  }
  Logger.log("done");
  var le = table.length
  Logger.log(le);
  if (le > 200)
  {
    var subject =
        'ITS Ref No. '   itemno   ' | '    'Report Status- OPEN';
    bodyEmail=bodyEmail   table
    MailApp.sendEmail(email,subject,"",{htmlBody:bodyEmail});
  }
}

CodePudding user response：

I've refactored your script a bit and it should only send emails to people with row status Open and has an email.

function expiredjobs() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Oct-22");
  var startRow = 2; // First row of data to process
  var numRows = sheet.getLastRow(); // Number of rows to process
  var numColu = sheet.getLastColumn(); // Number of colums to process 

  var dataRange = sheet.getRange(startRow, 1, numRows, numColu);
  var data = dataRange.getValues()
  
  var bodyEmail = "Dear sir, <br/>  Kindly provide the recommendation for the below mentioned near miss, also provide the supporting document to close the incident. <br/> "  

  var table = "<html><body><br><table border=1>"
  var colVal = "";

  table = table   "<tr>"
  for (var colNo = 1; colNo <=11; colNo  ) 
  {
    colVal = sheet.getRange(1 , colNo).getDisplayValue();
    table = table   "<th>"   colVal   "</th>";
  }        
  table = table   "</tr>"
  
  //use data values to get the text
  for (var i = 0; i < data.length; i  )
  {
    var email = data[i][12];
    var status =  data[i][11];
    var itemno =  data[i][0];
    Logger.log(email)
    Logger.log(status)
    Logger.log(itemno)

    if(status === "Open" && email != null)
    {
      table = table   "<tr>"
      for (var colNo = 0; colNo <=10; colNo  ) 
      {
        colVal = data[i][colNo];
        table = table   "<td>"   colVal   "</td>";
      }        
      
      table = table   "</tr>"
      Logger.log(table)
      var subject =
        'ITS Ref No. '   itemno   ' | '    'Report Status- OPEN';
      bodyEmail=bodyEmail   table
      Logger.log("sending email: "   email);
      MailApp.sendEmail(email,subject,"",{htmlBody:bodyEmail});
    }
  }
}

CodePudding user response：

To make it clearer than comments. You error Exception : Failed to send email : no recipient is probably caused by empty mail column (in your Gsheet).

To correct :


//insert this before sendMail() fonction
if (email == "") {
  Logger.log('email empty');
  return;
}

MailApp.sendEmail(email,subject,"",{htmlBody:bodyEmail});