Google Spreadsheet Trigger Mail only on NEW data to the sheet

Question

Ok so I use this script: https://developers.google.com/apps-script/articles/mail_merge

And I have set the trigger to trigger when the sheet updates. It does but then it also sends email to all the old emails in the spreadshet. So for instance when I fill in the form and put user1 in the Email Address it will only send email to user1. When I fill in the form once more, I fill in user2, then both user 1 and user2 gets email, I do only want the user2 to get email.

If I would fill the form for the third time I would only like it to send a email to user3.

Here is what my funciton looks like:

 function sendEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheets()[0];
  var dataRange = dataSheet.getRange(2, 1, dataSheet.getMaxRows() - 1, 4);

  var templateSheet = ss.getSheets()[1];
  var emailTemplate = templateSheet.getRange("A1").getValue();

I try this:

  function sendEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheets()[0];
  var lastRow = dataSheet.getMaxRows() - 1;
  var dataRange = dataSheet.getRange(lastRow, 1, lastRow, 4);

  var templateSheet = ss.getSheets()[1];
  var emailTemplate = templateSheet.getRange("A1").getValue();

It sends nothing, Do I do anything wrong =) ?

possible duplicate of [Need a script to send an email without resending to previously recorded senders](http://stackoverflow.com/questions/10693065/need-a-script-to-send-an-email-without-resending-to-previously-recorded-senders) — Mogsdad, Sep 19 '13 at 17:08
See these questions as well: [setting up script to include google docs form data in email notification](http://stackoverflow.com/questions/13683060/setting-up-script-to-include-google-docs-form-data-in-email-notification/17396794#17396794), [Send email if certain value is written in Google Forms](http://stackoverflow.com/questions/17498186/send-email-if-certain-value-is-written-in-google-forms), [Spreadsheet Email Trigger](http://stackoverflow.com/a/13871541/1677912). There are lots of examples of Form Submission Trigger functions that send emails. — Mogsdad, Sep 19 '13 at 17:15

patt0 · Answer 1 · 2013-09-19T10:18:27.777

I believe the answer lies in the SendEmails method that goes down the entire list of entries in the form, sending a mail to each of them. To only send an email to the last person to entered information in the form you will need to modify that function.

You have a number of options, you can change the input range to be only the last row such as :

var lastRow = dataSheet.getLastRow();
var dataRange = dataSheet.getRange(lastRow, 1, lastRow, 4);

This should work just fine, second you could remove the loop, which now runs only once, but I suggest you can leave it in for now in case you want to send email to your entire list subsequently using the menu in the sheet.

Expanding on that you could have a boolean parameter in your function SendMails(onlyLast) that would do the switch for you, this way you have the best of both world.

function sendEmails(onlyLast) {
...
var startRow = 2;
var endRow = dataSheet.getLastRow() - 1;
if (onlyLast)
  startRow = endRow;
var dataRange = dataSheet.getRange(startRow, 1, endRow, 4);

Let me know how that works out for you.

Google Spreadsheet Trigger Mail only on NEW data to the sheet

1 Answers1