Script to send email based on value in a column - problem with script

0
votes

New to GAS. I have a Google Form feeding into a spreadsheet. After watching tutorial videos and reading other posts, I attempted to create a script that will send an email to an address in column 2, and send a different email based on either a yes or no in another column (column 25). It also includes another column (26) that I want to have the date populated into when the email is sent, ensuring that every time I run this script, there are no duplicates sent. I have debugged and nothing comes up as an error, but it's not working - I have not received an email. Help would be greatly appreciated! Here is an example spreadsheet: https://docs.google.com/spreadsheets/d/1AKaSOk1ZbnfKeadgB_mugnqplKzJJ3bDQ_KsRY0sMvQ/edit?usp=sharing

function sendEmail() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var responses = ss.getSheetByName("Field Trip Requests");

  var data = responses.getRange(2,1,responses.getLastRow()-1,25).getValues();

  data.forEach(function(row,i) {
    // variables
    var recipient = row[1];
    var destination = row[6];
    var approval = row[24];
    var emailSent = row[25];

    if(emailSent == ' ') {

      if(approval == "Y") {
        var body = "Your field trip request for " + 
          destination + 
            " has been approved! If you requested transportation, Najma will make arrangements and contact you if she requires more information." + 
              "<br><br>" + 
                "Cheers," + 
                  "<br><br>" + 
                    "Boaty McBoatface";

      }
      else if(approval == "N") {
        var body = "Your field trip request for " +
          destination +
            " has not been approved.  Please come and see me and we can chat!" +
              "<br><br>" +
                "Cheers," +
                  "<br><br>" +
                    "Boaty McBoatface";

      }

      var subject = "Your Field Trip Request";

      MailApp.sendEmail(recipient, subject, body)

      var d = new Date();

      responses.getRange(i + 1, 25).setValue(d);

    }

  })

}
2
emailgoogle-apps-scriptgoogle-sheets

2 Answers

1
votes

One problem of the script is that you it's using 1 based indexes when it should be using 0 based in

var recipient = row[1];
var destination = row[6];
var approval = row[24];
var emailSent = row[25];

The above is because JavaScript uses 0 based indexes for Array elements (as well as for other things), so intestead of the above use

var recipient = row[0];
var destination = row[5];
var approval = row[23];
var emailSent = row[24];

Another problem is the following condition:

emailSent == ' ' /* is emailSent equal to a blank space? */

it should be 

emailSent == '' /* is emailSent equal to an empty string? */

the above because getValues() returns an empty string for empty cells

0
votes

If this var data = responses.getRange(2,1,responses.getLastRow()-1,25).getValues(); is correct then there is no row[25] as shown below:

data.forEach(function(row,i) {
    // variables
    var recipient = row[1];
    var destination = row[6];
    var approval = row[24];
    var emailSent = row[25];

row starts at zero and ends at 24

row is an array

Perhaps this is what you wish:

function sendEmail() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName("Field Trip Requests");
  var data=sh.getRange(2,1,responses.getLastRow()-1,25).getValues();
  data.forEach(function(row,i) {
    var recipient=row[0];
    var destination=row[5];
    var approval=row[23];
    var emailSent=row[24];
    if(emailSent=='' &&  approval=='Y') {
      var body=Utilities.formatString('Your field trip request for %s has been approved! If you requested transportation, Najma will make arrangements and contact you if she requires more information. <br><br>Cheers<br><br>Boaty McBoatface',destination);
    }else if(emailSent=='' && approval == "N") {
      var body=Utilities.formatString('Your field trip request for %shas not been approved.  Please come and see me and we can chat!<br><br>Cheers,<br><br>Boaty McBoatface',destination);     
    }
    var subject="Your Field Trip Request";
    MailApp.sendEmail(recipient, subject, body);
    sh.getRange(i+1,25).setValue(Utilities.formatDate(new Date, Session.getScriptTimeZone(), "MM/dd/yyyy"));
  })  
}