Sheets Apps Script - Loop rows to send as email

Question

I'm sending an email from sheets using the following code, but I have an issue...

I can't move to the next iteration where the sent column = "Yes" ie: only want to include rows where the sent column = ""

Thanks in advance for your assistance.

function sendEmail() {

 //setup function
 var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 var StartRow = 2;
 var RowRange = ActiveSheet.getLastRow() - StartRow + 1;
 var WholeRange = ActiveSheet.getRange(StartRow,1,RowRange,11);
 var AllValues = WholeRange.getValues();

 var message = "";
  
  //iterate loop
 for (i in AllValues) {

  //set current row
 var CurrentRow = AllValues[i];

  //define column to check if sent
 const EmailSent = CurrentRow[11];
   
  //if row has been sent, then continue to next iteration
   if (EmailSent == "Yes") 
     continue;
   
  //set HTML template for information
  message +=
      "<p><b>Date of Works: </b>" + CurrentRow[0] + "</p>" +
      "<p><b>Customer: </b>" + CurrentRow[1] + "</p>" +
      "<p><b>Project No: </b>" + CurrentRow[2] + "</p>" +
      "<p><b>Project: </b>" + CurrentRow[3] + "</p>" +
      "<p><b>Location: </b>" + CurrentRow[4] + "</p>" +
      "<p><b>Site Contact: </b>" + CurrentRow[5] + "</p>" +
      "<p><b>Starting Point: </b>" + CurrentRow[6] + "</p>" +
      "<p><b>Start Time: </b>" + CurrentRow[7] + "</p>" +
      "<p><b>Truck Size: </b>" + CurrentRow[8] + "</p><br><br>";
   
   //set the row to look at
   var setRow = parseInt(i) + StartRow;

   
  //mark row once Sent
  ActiveSheet.getRange(setRow, 11).setValue("Yes");
}

  //define who to send email to 
  var SendTo = "[email protected]"
 
  //set subject line
  var Subject = "#6 Test - 2 - Selected Rows";


  //send the actual email  
  MailApp.sendEmail({
      to: SendTo,
      cc: "",
      subject: Subject,
      htmlBody: message,
});
}

If the CurrentRow 11 = yes, then skip current row and move to next. — Nish1585
– Nish1585, Commented Dec 3, 2017 at 22:38
I'd ideally like to filter a table, then have only the visible range sent as a formatted table in the body of an email. When I couldn't get that to work, this seemed the next best alternative? — Nish1585
– Nish1585, Commented Dec 3, 2017 at 23:02

Cooper · Accepted Answer · 2017-11-29 05:23:26Z

Try This:

function sendEmail(){
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var startRow=2;
  var rg=ss.getDataRange();
  var vA=rg.getValues();
  var message="";
  for(var i=1;i<vA.length;i++){
    message +="<p><b>Date of Works: </b>" + vA[i][0] + "</p>" + "<p><b>Customer: </b>" + vA[i][1] + "</p>" + "<p><b>Project No: </b>" + vA[i][2] + "</p>" + "<p><b>Project: </b>" + vA[i][3] + "</p>" + "<p><b>Location: </b>" + vA[i][4] + "</p>" + "<p><b>Site Contact: </b>" + vA[i][5] + "</p>" + "<p><b>Starting Point: </b>" + vA[i][6] + "</p>" + "<p><b>Start Time: </b>" + vA[i][7] + "</p>" + "<p><b>Truck Size: </b>" + vA[i][8] + "</p><br><br>";
    var SendTo="[email protected]"
    var Subject="#6 Test - 2 - Selected Rows";
    if(vA[i][11]!='Yes'){
      MailApp.sendEmail({to: SendTo,cc: "",subject: Subject,htmlBody: message,});
      sh.getRange(i+1,12).setValue('Yes');
    }
  }
}

Nish1585 · Accepted Answer · 2018-06-01 00:04:19Z

I ended up using a different approach...

In the spreadsheet, I have a 'control cell' which uses data validation to list the companies I want to email.

Under that, I run a query to only draw the data for that company into a table, based on the control cell.

The following script loops through a list of companies, updating the control cell and changing the table, then sending the relevant table to each company individually, using another function.

Hopefully, this helps a few of you!

function bulkbookingsdraft() {

  // loop through table booking all listed companies

  var app = SpreadsheetApp;
  var sheet = app.getActiveSpreadsheet().getActiveSheet();
  var companies = sheet.getDataRange();
  var lastRow = companies.getLastRow();
  var searchRange = sheet.getRange(3,9, lastRow-1,9);
  var cmdbox = sheet.getRange(1, 5)

  // Get array of values in the searchRange - the list of companies
  var rangeValues = searchRange.getValues();

  // Loop through array and update control cell
    for (r = 1 ; r < lastRow - 1; r++){
     var company = sheet.getRange(r+2,9).getValue()        

      cmdbox.setValue(company);
      sendEmail_draft();     

   }
}

Collectives™ on Stack Overflow

Sheets Apps Script - Loop rows to send as email

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related