CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-apps-script-editor
Keanu Stevenson
Keanu Stevenson

Reputation: 3

Can not get MailApp.sendEmail to work in google app script after a long function

I have write a code that takes an entry from the original form sheet then matches it to the second sheet to find the row it resides on. Then I am using that row to pull the email that is in the cell in the column to the right.

The code also takes three entries from the form sheet (employee name, salon information and effective date) and replaces the values from another sheet to form the 'body'

When I try to debug the code, all the variables are correct (email, subject, body) but I am not receiving the email

I am very new to Google App Script and I know it must have something to do with my handling of the for/if statement and storing the email as a variable but I am at a loss at this point.

/*
 script that fires on sheet edit(form entry)
 reads the salon details from the 'Salon Information' column of the most recent form entry, last row
 function then matches the 'Salon Information' to the "GM Match:sheet" and pulls the GM email.
 final action is taking 'Employee Name','Salon Information' and 'Effective Date' and replacing 
 the values in the "Email Sheet:Sheet" and asigning it to the body 
*/

function sendAutomatedEmail() {

  //setting active spreadsheet
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1").activate();
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();  // Get main Database Sheet
  var emailsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email Sheet");  // Get Sheet with the Email Content
  var lrow = sh.getLastRow();   // User which last filled the form

  var eename = sh.getRange(lrow, 2).getValue();     // Get name value. (Row - Last Row, Column - 2)
  var salon = sh.getRange(lrow, 3).getValue();    // Get salon value. (Row - Last Row, Column - 3)
  var effdate = sh.getRange(lrow, 4).getValue();   // Get effective termination date value. (Row - Last Row, Column - 4)
  var subject = "Employee termination notification";

  var body = emailsheet.getRange(1, 1).getValue();   // Get Email Content
  /* 
  Replace variable 'name' with terminated employee's name
  Replace variable 'salon' with salon information
  Replace variable 'effdate' with effective date
  */
  body = body.replace("{eename}", eename).replace("{salon}", salon).replace("{effdate}",effdate);  


  var gmsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("GM Match"); //Get sheet with GM email and salon match
  var data = gmsheet.getDataRange().getValues();
  var sname = salon                         //reassigning variable for salon
  for(var i = 0; i < data.length; i++){     //for loop for data in "GM Match" sheet
    var row = i + 1 //adding a row variable to be called later
    var email = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("GM Match").getRange(row, 2).getValue();
    if(data[i][0] == sname){                //[0] because column A
      Logger.log((i+1))
      return i;
    }
  }

  MailApp.sendEmail(email, subject, body);

}

Upvotes: 0

Views: 79

Answers (3)

Iamblichus
Iamblichus

Reputation: 19309

You are using return if the condition data[i][0] == sname is true, that is, if the salon name matches the data in GM Match, which (if I understood you correctly) is precisely when you want to send the email.

The return keyword ends the function execution, so if any row in GM Match matches the salon data coming from the other sheet, the function will stop and the email will not be sent.

So in this case, you should change this:

    if(data[i][0] == sname){                //[0] because column A
      Logger.log((i+1))
      return i;
    }
  }

  MailApp.sendEmail(email, subject, body);

To this:

    if(data[i][0] == sname) {
      MailApp.sendEmail(email, subject, body);
    }

I hope this is of any help.

Upvotes: 0

SRosa
SRosa

Reputation: 33

Check to see if it is authorized by the scopes in your code, the

https://www.googleapis.com/auth/script.send_mail

scope must be included in order to send emails by that method.

Upvotes: 1

Cooper
Cooper

Reputation: 64032

Do you really want this:

if(data[i][0]==sname){ return i; }

Whenever it's true you send no email.

Upvotes: 1

Related Questions