How do I add Email Sent to a status column and exclude those rows when the script runs again?

Question

I am very new to scripting and am struggling to write a script for our youth league program's apparel fundraiser. The script, when triggered by clicking an image, will send an email to all of the recipients on a list, add 'email sent' to status column 'I' in all of the rows that the email sent to...then whenever the script is run again, only send emails to the new rows of data that haven't been emailed yet.

The script that I have inserts email sent, but if the script is run again it doesn't pay attention to the status column 'I' and I get duplicate emails. I've used tutorial videos to get me this far, but now I'm stuck. I've tried to get help from similar questions, but I'm not experienced enough to modify it to fit my needs. Here is a copy of the google sheet, the 'Send Emails' sheet has the info the script is running from. UPDATED:

https://docs.google.com/spreadsheets/d/1MBVhLj1A7Z_cpYxs_s5ae11ykJvTmS16E0jW4tGsNU4/edit?usp=sharing

function sendOrderEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.setActiveSheet(ss.getSheetByName("Send Emails"));
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;
  var dataRange = sheet.getRange("A2:I10");
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var rowData = data[i];
    var emailAddress = rowData[0];
    var recipient = rowData[1];
    var message1 = rowData[2];
    var parameter1 = rowData[3];
    var message2 = rowData[4];
    var message3 = rowData[5];
    var parameter2 = rowData[6];
    var message4 = rowData[7];
    var emailSent = rowData[9];
    var message = 'Hi ' + recipient + ',\n\n' + message1 + parameter1 + ' ' + message2 + ' ' + message3 + parameter2 + '. ' + message4 + '\n\n' + 'Tri-Valley Youth League Softball';
    var subject = 'Order Reference Number ' + parameter2;
    if (emailSent != "EMAIL_SENT" ) { // Prevents sending duplicates
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow + i, 9).setValue("EMAIL_SENT");
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}

Your data range is only defined as `A2:H10`. Thus, you cannot possibly obtain information about column I from this input. Consider `var dataRange = sheet.getDataRange();`, and then starting your `for` loop with array index `i=1` (since `i=0` will point to the header row). [Never use `for (var ... in ...)` to go through `Array`s btw.](https://stackoverflow.com/questions/500504/why-is-using-for-in-with-array-iteration-a-bad-idea) — tehhowch, Mar 14 '19 at 19:46
I updated the array index. When I try to update the [dataRange] to your suggestion [@tehhowch] it gives me error codes. All of the columns have formulas, so none of the rows are empty. Can I define a range with the [startRow] and then somehow use a count function to define [numRows] that could be used to define the data range? The ["EMAIL_SENT"] no longer skips lines so that part is working right, but the script still does not pay attention to the email sent column and sends duplicates if the script runs again. — Tri-Valley YL Softball, Mar 15 '19 at 11:47
You're using an assignment operator (`=`) in your if statement, rather than a comparison operator (`<`, `>`, `==`, `===`, `<=`, `>=`, `!=`, `!==`). So the if statement is always true in your case. — tehhowch, Mar 15 '19 at 12:15
Ok, I had an ! in there before like the sample had, but it didn't make any difference in receiving duplicates if the script runs again. I tried this to define the dataRange, but that made all of the message content change 'undefined': var startRow = 2; var numRows = sheet.getLastRow(); var dataRange = sheet.getRange(startRow, 1, numRows); — Tri-Valley YL Softball, Mar 15 '19 at 12:21
You need to specify the number of columns as well (otherwise it is just 1 column wide). `sheet.getLastColumn()` will work. Also, make sure you subtract the starting row offset from the number of rows (e.g. 40 rows total but starting with row 2? Only read 40 - `< # header rows>` = 39 rows) — tehhowch, Mar 15 '19 at 12:35
I tried != and !== ,but I still get an email for every row whenever the script runs whether it has EMAIL_SENT or is blank. — Tri-Valley YL Softball, Mar 15 '19 at 13:05
As noted by Cooper, array index 9 is spreadsheet column 10. Have you tried pausing your script in Debugging mode, to explore the current state of your worksheet as seen by the variables in your script? https://developers.google.com/apps-script/guides/support/troubleshooting#using_the_debugger_and_breakpoints — tehhowch, Mar 16 '19 at 00:07

Cooper · Accepted Answer · 2019-03-19T21:03:23.160

Try this:

function sendOrderEmails() {
  var ss=SpreadsheetApp.getActive();
  var sheet=ss.getSheetByName("Send Emails");
  var startRow=2;
  var dataRange=sheet.getRange("A2:J10");//Include column 10 in the range
  var data=dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var rowData = data[i];
    var emailAddress = rowData[0];
    var recipient = rowData[1];
    var message1 = rowData[2];
    var parameter1 = rowData[3];
    var message2 = rowData[4];
    var message3 = rowData[5];
    var parameter2 = rowData[6];
    var message4 = rowData[7];
    var emailSent = rowData[8];//This is column 9
    var message = 'Hi ' + recipient + ',\n\n' + message1 + parameter1 + ' ' + message2 + ' ' + message3 + parameter2 + '. ' + message4 + '\n\n' + 'Tri-Valley Youth League Softball';
    var subject = 'Order Reference Number ' + parameter2;
    if (emailSent != "EMAIL_SENT" ) { 
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow + i, 9).setValue("EMAIL_SENT");
    }
  }
}

I did this so that you can see the difference row and columns and data indices.

function rowsColumnsAndIndices() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rg=sh.getRange(1,1,26,26);
  var vA=rg.getValues();
  for(var i=0;i<vA.length;i++) {
    for(var j=0;j<vA[i].length;j++) {
      vA[i][j]=Utilities.formatString('r: %s, c: %s\ni: %s,j: %s', i+1,j+1,i,j);
    }
  }
  rg.setValues(vA);
}

Thanks, this is what I have now (aside from (sheet.getRange(startRow + i, 9 instead of 10 because I wanted the 'email_sent' to go into column 10, not 11). It no long skips rows inbetween "Email_Sent", but it still sends emails to all of the emails in the data range. It doesn't pay attention to the if statement. When I run it in debug mode, the only error that returns is this: Failed to send email: no recipient (line 21, file "Code"). I'm assuming that's because the range goes to row 10 and currently there are only 3 emails/rows of data. (this would change as orders are added though) — Tri-Valley YL Softball, Mar 19 '19 at 15:46
This `sheet.getRange(startRow + i, 10).setValue("EMAIL_SENT");` getRange(row,column); so it should column 10. The indices for data start at zero and rows start at one. So the column in this case is always i+1 because your range starts in column one. So that's probably your problem you're putting EMAIL_SENT in the wrong column. You should single step through you program and pay attention to the intermediate variables and watch the spreadsheet after every loop to insure that it's working correctly. — Cooper, Mar 19 '19 at 16:14
Thanks for the help.. I found my mistake..var emailSent should have been forData[8], not 9... I changed that and it doesn't send duplicates now. Thanks again! — Tri-Valley YL Softball, Mar 19 '19 at 20:57

How do I add Email Sent to a status column and exclude those rows when the script runs again?

1 Answers1