表单提交时 Google Apps 脚本触发器 - 不发送前几行

Question

我已经编写了下面的代码，根据在单元格中是否找到某个字符串向特定的个人发送通知电子邮件。我想知道我是否将脚本设置为在"表单提交"触发器上运行，它是否会再次浏览完整的电子表格（从而发送重复的电子邮件），或者在提交新的谷歌表单条目时，它是否只从添加到电子表格的新行开始。

function notifySponsors() {
  Logger.clear();
  var s = SpreadsheetApp.getActiveSheet();
  var data = s.getDataRange().getValues();
  var data_len = data.length;
  //define variables with each organization
  var mustang = "Camp Mustang Counselor";
  var chapel = "Chapel Tech Team";
  var dgroup = "D-Group Leader";
  var diversity = "Diversity Club";
  var journalism = "Journalism";
  var nhs = "National Honor Society Officer";
  var robotics = "Robotics Club";
  var stuco = "Student Council";
  var ambassador = "Student Ambassador";
  //Send e-mail to specific sponsors to notify that someone has submitted an application
  for(var i=0; i<data_len; i++) {
    var datastring = data[i][5].toString();
    //E-mail Camp Mustang Sponsors
if(datastring.match(mustang)== mustang) {
  var MustangString = data[i][1].toString()+" has submitted a Camp Mustang application";
  MailApp.sendEmail("[e-mail]", MustangString, MustangString);
  MailApp.sendEmail("[e-mail]", MustangString, MustangString);
  Logger.log(MustangString);
  }
//E-mail Chapel Sponsors
if(datastring.match(chapel)== chapel) {
  var ChapelString = data[i][1].toString()+" has submitted a Chapel Team application";
  MailApp.sendEmail("[e-mail]", ChapelString, ChapelString);
  Logger.log(ChapelString);
  }
//E-mail D-Group Sponsors
if(datastring.match(dgroup)== dgroup) {
  var DGroupString = data[i][1].toString()+" has submitted a D-Group Leader application";
  MailApp.sendEmail("[e-mail]", DGroupString, DGroupString);
  MailApp.sendEmail("[e-mail]", DGroupString, DGroupString);
  Logger.log(DGroupString);
  }
//E-mail Diversity Club Sponsors
if(datastring.match(diversity)== diversity) {
  var DiversityString = data[i][1].toString()+" has submitted a Diversity application";
  MailApp.sendEmail("[e-mail]", "test 2", "New Application for D-Group");
  Logger.log(DiversityString);
  }
} 
}

Answer 1

不要再浏览整个表单，而是可以检查响应表单的最后一行。（你得到的最新回复）

 var sheet = SpreadsheetApp.getActiveSheet();
 var lastrow = sheet.getLastRow(); 
  //imagining number of column in the value range is 10
 var lastRowValues = sheet.getRange(lastrow, 1, 1, 10).getValues();

现在移除for loop。您可以获得最后提交的记录如下。

var datastring = lastRowValues[0][5].toString();

现在，您可以浏览if statements并发送电子邮件。

干杯！