Sending auto Email based on given data

Question

I was using the below script to send an auto emails through Sheet "Reference" whenever any data is filled by Google form into sheet "Data".

I do not know how to make a loop to do this task that whenever any data is updated on sheet "Data" from Google form then automatically an email has been sent to the mail available in "Reference" sheet Col"G" after sending the email to that particular entity, code should mention in "Reference" sheet Col"N" as Mail sent.

"Reference" sheet Col"O" is Notification Text "Reference" sheet Col"M" is subject "Reference" sheet Col"G" is such email where code will send above mentioned data

Code should not sent email again on those ID's in "Reference" sheet Col"N" where on "Email Sent" i mentioned.

function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname= sheet.getSheetByName("Reference");
  var startRow = 2; // First row of data to process
  var numRows = sheetname.getLastRow("Reference"); // Number of rows to process
  // Fetch the range of cells A2:B3
  var dataRange = sheetname.getRange(startRow, 1, numRows, 15);
  // 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 emailAddress = row[6]; // First column
    var message = row[13]; // Second column
    var emailSent = row[14]; // Third column
    if (emailSent !== EMAIL_SENT && row[12] == "Mail Send"  && row[6] != "") { // Prevents sending duplicates
      var subject = row[11];
      MailApp.sendEmail(emailAddress, subject, message);
      sheetname.getRange(startRow + i, 15).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}

Any help would be highly appreciated

Sidney Cardoso dos Santos Sidney Cardoso dos Santos · Accepted Answer · 2020-12-14T14:45:15

First excuse the English

For this you can use the code below and disregard the Reference tab

Do not forget to create the trigger so that it is triggered after the form is sent.

function Send_email() { 

  var INITIALline = 2; 
  var columnSEND = 11;  
  var STATUScolumn = 12;
  var textCONDITION = "Submit";
  var textSENT = "Sent"
    
  var tab = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data"); 
  var interval = tab.getRange(INITIALline,1,tab.getLastRow()-INITIALline+1,STATUScolumn);
  var dice = interval.getValues();
  var yousent = false;
  var email,subject,message;
  
  for (var i=0; i<dice.length; ++i) {
    if((dice[i][columnSEND-1]==textCONDITION) && (dice[i][STATUScolumn-1]!=textSENT)){
    
      var email    = dice[i][6]
    
      subject = dice[i][7]+" | Your CASE ID IS | "+dice[i][0];
            
      var message = "<font size='3' face='Comfortaa'>Dear "+dice[i][3]+",<br/><br/>"+
      
                     "Thanks for connecting with us. This is your case id "+dice[i][0]+"..<br/><br/>"+
                     
                     "<i>Thanks & Regards</i><br/>"+
                     "<b>Team</b>";
      
      MailApp.sendEmail(email, subject, message,{ htmlBody: message});
      tab.getRange(INITIALline+i,STATUScolumn).setValue(textSENT);
      yousent = true;
      SpreadsheetApp.flush();
    }
  }
 }

Sending auto Email based on given data

1 Answers