Google脚本,当某列显示“是”时发送电子邮件

找到答案:)

var EMAIL_SENT = "EMAIL_SENT";

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 3;  
  var numRows = 13;   
  // Fetch the range of cells A3:I17
  var dataRange = sheet.getRange(startRow, 1, numRows, 25)
  var data = dataRange.getValues();
  for (i = 0; i < data.length; ++i) {          //collects data for all values in rows and columns specified in 5,6, and 8
    var row = data[i];                         //gives an index to data in each row
    if(row[3]=="YES") {                        //ensures that it says "YES" in the e-mail column
      var emailSent = row[24];                 //checks value of emailSent row
      if(emailSent != EMAIL_SENT) {            //avoids sending duplicates if an e-mail is sent
        var emailAddress = row[0];             //collects parent e-mail address
        var name = row[2]                      //collects student name
        var list1 = [];                        //creates an empty list for missing assignments
        for (j = 15; j < 20; ++j) {            //adds assignments to list if cells are not blank
          if(row[j] != "") {
            list1.push(row[j]);              
          }
        }
        texta = ""                             //creates an empty textbox for the list of assignments
        if(list1.length>0) {                   //creates a textbox for assignments as a bulleted list if list is not empty
          text1 = "";
          for (k=0; k<list1.length; ++k) {
            text1 += " • " +list1[k]+ "\n";
          }
          texta += "The second part of " +name+ "'s Unit 8 Project for Geometry is due next Tuesday, April 3rd. This includes sections 8-1, 8-2, 8-3, 8-4, and the interview/research portion. By this date, students are expected to have received feedback from me on all of their assignments and revised their work for a summative grade. Unfortunately, " 
          +name+ " still has not submitted all of these assignments to receive feedback from me. The following assignments are currently missing: \n" +text1;
        }
        var list2 = [];                       //creates a second empty list for missing assignments - IP
        for (j = 20; j < 24; ++j) {           //adds assignments to list if cells are not blank
          if(row[j] != "") {
            list2.push(row[j]);               
          }
        }
        textb = ""                            //creates an empty textbox for list of missing IP
        if(list2.length>0) {                  //adds assignments to textbox as a bulleted list if list is not empty
          text2 = "";
          for (k=0; k<list2.length; ++k) {
            text2 += " • " +list2[k]+ "\n";
          }
          textb += "\n\n It is also important that the independent practice be completed as well to make sure that students grasp a full understanding of what we are learning in the project. Currently " +name+ " is missing the following independent practice for these lessons: \n" 
          +text2+ ""; 
        }
        var message = "Hello, \n\n " +texta+ "" +textb+ "\n\n Please check with "              //message body for e-mail
        +name+ " to make sure these assignments are completed as soon as possible. Let me know if you have any questions about what needs to be completed for next week. Thank you!"; 
        var subject =  "" +name+ "'s Unit 8 Project Missing Work";                            //subject line for e-mail
        MailApp.sendEmail(emailAddress, subject, message);                                    //sends e-mail with given e-mail address, subject, and message
        sheet.getRange(startRow + i, 25).setValue(EMAIL_SENT);                                //updates spreadsheet once an e-mail is sent
        SpreadsheetApp.flush();                                                               //adds code to spreadsheet before continuing
      }
    }
  }
}
var spreadsheet = SpreadsheetApp.getActive();                                                 //creates a send e-mails button in spreadsheet
var menuItems = [
  {name: 'Send Emails', functionName: 'sendEmails'}
];
spreadsheet.addMenu('Send Emails', menuItems);