Google Apps Script Trigger On Form Submit - Don't send out for previous rows

Question

I've written the code below to send notification e-mails to specific individuals based on if a certain string is found in a cell. I want to know if I set the script to run on a "form submit" trigger if it will go through the full spreadsheet again (thus sending duplicate e-mails) or if it will just start with the new row that is added to the spreadsheet when a new Google form entry is submitted.

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("[email protected]", MustangString, MustangString);
  MailApp.sendEmail("[email protected]", 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("[email protected]", 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("[email protected]", DGroupString, DGroupString);
  MailApp.sendEmail("[email protected]", 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("[email protected]", "test 2", "New Application for D-Group");
  Logger.log(DiversityString);
  }
} 

}

Well I've tested it out and it appears to go through the whole spreadsheet again. How can I modify this script so that it will only run on the most recent form submission? — Tong

iJay iJay · Accepted Answer · 2016-01-09T06:25:27

Don't go through whole sheet again, instead you can check last row of the response sheet.(the latest response you get)

 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();

Now remove the for loop. you can get last submitted record as below.

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

Now you can go through your if statements and send emails.

Cheers!

Google Apps Script Trigger On Form Submit - Don't send out for previous rows

1 Answers