Google sheets email script sending multiple emails

Question

I am using a Google Form to amalgamate multiple messages into one daily email sent using scripts and a daily timed trigger (code copied from here and my version below).

It all works with one little bug. I get about 2/3rds of the emails also appearing in my inbox and filling it up each day.

So as an example one email address is [email protected] and they get the email, but in my inbox ([email protected]) is an email sent to [email protected] but not a forwarded message or a reply.

Any ideas how to stop this?

    /**
     * Sends emails with data from the current spreadsheet.
     */
    function sendEmails() {
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Email Info');
      var startRow = 1; // First row of data to process
      var numRows = 18; // Number of rows to process
      // Fetch the range of cells A2:B3
      var dataRange = sheet.getRange(startRow, 1, numRows, 18);
      // Fetch values for each row in the Range.
      var data = dataRange.getValues();
      for (var i in data) {
        var row = data[i];
        var emailAddress = row[0]; // First column
        var message = row[1]; // Second column
        var subject = 'Tech Support Handover';
       MailApp.sendEmail(emailAddress, subject, message);
      }
    }

Hello @MattT, unless you have your email address in the Spreadsheet as well, the code provided by you should send the email only to the emails gathered from the spreadsheet. Are any of the emails from the spreadsheet aliases of your actual email address? Cheers! — ale13

Cooper Cooper · Accepted Answer · 2020-02-04T21:51:11

Add one more column and call it sent.

function sendEmails() {
  var sh=SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Email Info');
  var data=sh.getRange(1,1,18,19).getValues();
  for (var i=0;i<data.length;i++) {
    var row=data[i];
    var emailAddress=data[i][0];
    var message=data[i][1];
    var sent=data[i][18];//column 19  Added one more column
    var subject='Tech Support Handover';
    if(sent!='SENT') {
      MailApp.sendEmail(emailAddress, subject, message);
      sh.getRange(i+1,19).setValue('SENT');
    }
  }
}

Google sheets email script sending multiple emails

1 Answers