Google app script - Auto generated mail based on cell value

Question

I am trying to send auto generated mail based on text change in my Gdoc, My requirement is to send mail if cell has value "SEND ALERT MAIL" else If cell has value "NO ALERT MAILS NEEDED" just add some text in status column (Gdoc).

only condition is send respective row data only in mail, but script is sending 13 mails irrespective of condition.

Please help !

code..

function sendEmail() 
{
  var sheet = SpreadsheetApp.getActiveSheet();
  var Totalcol=sheet.getLastColumn()-4;
  var StartRow =2;
  var LastCol = sheet.getLastColumn()-1;
  var LastRow = sheet.getLastRow();
  var Indic = sheet.getRange(StartRow,LastCol).getDisplayValue();

  for (var i = 0; i <= LastRow; i++) 
   {
     var row = LastRow[i];
     var dataRange = sheet.getRange(StartRow, 1, 1, Totalcol);
     var Offer = dataRange.getValues(); 
     if(Indic=="SEND ALERT MAIL")
     {
       MailApp.sendEmail("[email protected]", "Offer Alert", Offer);
     }
     else if(Indic=="NO ALERT MAILS NEEDED")
     {
       var cell = sheet.getRange("H2");
       cell.setValue("Alert date not reached !");
     }
   }
}

Abdul Salam Abdul Salam · Accepted Answer · 2017-05-18T11:42:15

I have modified my sheet and code for better readability, while loop not working as expected.

My Logic is very simple, Just mail Msg1,Msg2 values to mail address in 1st column IF "Indicator Msg" column has value "SEND ALERT MAIL" and fill some text in "Status" column IF "Indicator Msg" column value "DO NOTHING".

Gdoc screenshot

function sendEmails2() 
{
  var sheet = SpreadsheetApp.getActiveSheet();
  var VLastCol = sheet.getLastColumn();
  var startRow = 2;  // First row of data to process
  var LastRow = sheet.getLastRow();
  var LastCol = sheet.getLastColumn()-1;
  var dataRange = sheet.getRange(startRow, 1, LastRow, LastCol)
  var data = dataRange.getValues();
  var Indic = sheet.getRange(startRow,LastCol).getDisplayValue();

  //for (var i = 0; i <= LastRow; ++i)
  do
  { 
    //var row = data[i];
    startRow=startRow+1;
    var emailAddress = sheet.getRange(startRow,1).getDisplayValue(); // First column
    var message = sheet.getRange(startRow,2).getDisplayValue();      // Second column
    var message1 = sheet.getRange(startRow,3).getDisplayValue();     // Third column

    if(Indic=="SEND ALERT MAIL")
    {
    var subject = "Sending emails from a Spreadsheet";
    MailApp.sendEmail(emailAddress, subject, message +' '+ message1);
    }
    //SpreadsheetApp.flush();
    else if(Indic != "SEND ALERT MAIL")
    {
    var cell = sheet.getRange(startRow,VLastCol);
    cell.setValue("Date not reached")
    }
    //var val = sheet.getRange(startRow,LastCol).getDisplayValue();
  }
  while(Indic == null)
}

Google app script - Auto generated mail based on cell value

1 Answers