GOOGLE SCRIPT SEND EMAIL WHEN CONDITION MET

Question

Hi I am doing some codes in google script but the output that I am expecting did not happen.

Here is the code that i came up with hope you can help me solve this problem,

function sendEmail() {

var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var lr = activeSheet.getLastRow();

var dRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("March Cycle").getRange("I6")

var rData = dRange.getValue();

var templateTxt = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template").getRange(1,1).getValue();


for (var i = 6;i<=lr;i++) {

if(rData = "Touch Course Completed") {

var frstname = activeSheet.getRange(i,4).getValue();
var lstname = activeSheet.getRange(i,3).getValue();
var gradelvl = activeSheet.getRange(i,5).getValue();

var msgbody = templateTxt.replace("{name of student}",(frstname + " " + lstname)).replace("{Gr Lvl}",gradelvl);

MailApp.sendEmail("email add","Test Email",templateTxt);
}

Logger.log(msgbody);
}
}'

below is the data of picture that i wanted to automate.

enter image description here Thank you

Answer 1

There are a couple of mistakes you have on your code. Let me show you in my comments on this working code.

Code:

function sendEmail() {
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var templateTxt = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template").getRange(1, 1).getValue();
  var lr = activeSheet.getLastRow();
  // Compile list of students with "Touch Course Completed" in array before sending
  var msgbody = [];

  // You need to get the range of all data on column I for it to be optimized
  var dRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("March Cycle").getRange("I6:I"+lr);
  var rData = dRange.getValues();
  // rData is equal to [[Touch Course Completed],[Touch Course Completed],[Not completed]]

  // Loop all column I data
  rData.forEach(function (data, i){
    // Since rData is 2D array, the cell should be accessed by data[0]
    if (data[0] == "Touch Course Completed") {
      // i is the index of the rData array per loop, so 0 is equivalent to row 6
      // We need to offset it to get the correct row
      var frstname = activeSheet.getRange(i + 6, 4).getValue();
      var lstname = activeSheet.getRange(i + 6, 3).getValue();
      var gradelvl = activeSheet.getRange(i + 6, 5).getValue();

      // My idea here is optional, but I prefer sending it on 1 email instead of separate per student
      // That way, we do it faster and more efficient (note that there are quota/limits on sending mail thus doing this is better)
      // But if you need it separate, then do what you did in your script
      // I push all message first, then send as bulk outside the loop with join
      msgbody.push(templateTxt.replace("{name of student}", (frstname + " " + lstname)).replace("{Gr Lvl}", gradelvl));
    }
  });
  // Send an email only IF there is a "Touch Course Complete" student
  if(msgbody)
    MailApp.sendEmail("email", "Test Email", msgbody.join("\n"));
} 

March Cycle:

Template:

Email:

Note:

• Note that the earlier tests did send the email separately. If you need them separate then send it every loop (no need for arrays). But if not, then the code above should be better.

EDIT:

To only send the last row, there can be 2 approach that comes to mind:

1. Add a column for the identification of the data if it was already sent
   • You will need to add a column.
   • If new data doesn't contain "Touch Course Completed", it will not send anything.
   • If you added multiple rows that contains "Touch Course Completed", all of them will be sent.
2. Get the last row of msgbody
   • You will not need to add a column.
   • If new data doesn't contain "Touch Course Completed", it will send the last row with "Touch Course Completed" that was already sent before.
   • This will not send multiple rows if ever you added more than 1 "Touch Course Completed"

First approach:

function sendEmail() {
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var templateTxt = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template").getRange(1, 1).getValue();
  var lr = activeSheet.getLastRow();
  var msgbody = [];
  // Get all headers, check if there is "Already Sent" header
  var lc = activeSheet.getLastColumn();
  var headers = activeSheet.getRange(1, 1, 1, lc).getValues();
  var sentColumn;
  if(!headers[0].includes("Already Sent")){
    // If not found, add header "Already Sent" right to the last column
    sentColumn = lc + 1;
    activeSheet.getRange(1, sentColumn).setValue("Already Sent");
  }
  else {
    // If found, get column number of existing "Already Sent" header
    sentColumn = headers[0].indexOf("Already Sent") + 1;
  }

  var dRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("March Cycle").getRange("I6:I"+lr);
  var rData = dRange.getValues();
  rData.forEach(function (data, i){
    if (data[0] == "Touch Course Completed") {
      var frstname = activeSheet.getRange(i + 6, 4).getValue();
      var lstname = activeSheet.getRange(i + 6, 3).getValue();
      var gradelvl = activeSheet.getRange(i + 6, 5).getValue();
      // Check if column is already populated with "Y"
      var isSent = activeSheet.getRange(i + 6, sentColumn).getValue();
      if(isSent != "Y"){
        // If column value is not "Y", then add row to the email to be sent, also put "Y" on the column after
        msgbody.push(templateTxt.replace("{name of student}", (frstname + " " + lstname)).replace("{Gr Lvl}", gradelvl));
        activeSheet.getRange(i + 6, sentColumn).setValue("Y");
      }
    }
  });
  // Modified condition for checking array
  if(msgbody.length > 0)
    MailApp.sendEmail("email", "Test Email", msgbody.join("\n"));
} 

Sample data:

Output:

Email:

Note:

• The script will automatically add/locate the header, so no need to adjust your sheet manually.
• But you can still initialize a column to become "Already Sent" by writing the header name on the first row of the column and write "Y" to those rows you don't want to be sent anymore.