Send email to the new google form submission only-CodePudding

I'm new to the Google apps script. I wrote a script to send emails when there is a new submission from google forms using data and template from a spreadsheet. However, it sends an email to not just the new submission but also to all of the previous submissions. The whole script is quite long, so I only copy a short part of it. Is there any way to fix it?

Here is the link to the spreadsheet https://docs.google.com/spreadsheets/d/1fhuwEndIS3khg3W19jpQnBAaCp_MMrD_bfATrdf2-4I/edit?usp=sharing

Thank you.

function sendEmail() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Calculation");
var lr = ss.getLastRow();
for (var i = 3; i<=lr;i  ){

var currentEmail = ss.getRange(i, 1).getValue();
var currentName = ss.getRange(i, 3).getValue();
var currentScore1 = ss.getRange(i, 4).getValue();


 MailApp.sendEmail(
currentEmail,
subjectline,
"HTML",
{ htmlBody: messageBody }
);

}
}

CodePudding user response：

Instead of reading the values from the spreadsheet take advantage of the form submit event object. This event object has two properties including the form submission values, one is an Array of form submission values in the same order than the sheet columns, the other is an object having a property for each question each of them having an Array of values. Ref. https://developers.google.com/apps-script/guides/triggers/events

This shows the changes that need to done to your script:

function sendEmail(e) {

  var currentEmail = e.values[0];
  var currentName = e.values[2];
  var currentScore1 = e.values[3];


  MailApp.sendEmail(
   currentEmail,
   subjectline,
   "HTML",
   { htmlBody: messageBody }
  );

}