Google scripts for Spreadsheets (If statements)

Question

I am hoping someone can help me with this issue. I am new to coding. I have a google spreadsheet with a script that pushes out an email. I am attempting to have the script ignore the rows where the script has already sent out an email on.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Email Menu')
    .addItem('Send Email', 'myFunction')
    .addSeparator()
    .addToUi();
};

function myFunction() {
  var msg = 'Email sent!';

  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();

  var EMAIL_SENT = "EMAIL_SENT";
  var startRow = 2;

  for (i=1; i<values.length;i++) {

    //sent = values[i][4]; //added this in to try and skip a row if "X" was in Sent Column

    //if (sent ==" ", ) //added this in to try and skip a row if "X" was in Send Column

    var email = values[i][0];
    var name = values[i][i];
    var subject = values[i][2];
    var body = values[i][3];
    var sent = values[i][5];

    if (sent != EMAIL_SENT){
      MailApp.sendEmail(email, subject, body);
      sheet.getRange(startRow + i,5).setValue(EMAIL_SENT);
      SpreadsheetApp.getUi().alert(msg);
    }
 }}

Google spreadsheet

sheet.getRange(startRow + i,5).setValue(EMAIL_SENT); should be sheet.getRange(startRow + i - 1, 6).setValue(EMAIL_SENT); according to the rest of the code. — Sangbok Lee
@Theref1997, if one of the solutions has helped you, please consider accepting the answer. Thanks. — jrook

jrook jrook · Accepted Answer · 2017-04-06T16:37:46

Implement the following changes in your for loop:

for (i=1; i<values.length;i++) {

  var email = values[i][0];  
  var name = values[i][1]; //not [i][i] !  
  var subject = values[i][2];
  var body = values[i][3];  
  var sent = values[i][4];

  if (sent !== EMAIL_SENT){
    sheet.getRange(i+1,5).setValue(EMAIL_SENT);
  }

}

Basically get rid of the startRow variable (if you are not using elsewhere) and just use (i+1,5) to place "EMAIL_SENT" in the right column. I have removed parts of your code for the sake of clarity.

Google scripts for Spreadsheets (If statements)

2 Answers