MailApp.sendEmail script not working

[Penelope Barrett]

Hi All

I'm having some trouble getting my script to work. It's been a while since I've put together a system using sheets, forms and scripts. The script seemed to work fine from my account but now it's stopped working.

I'm not sure if the issue is ownership related. Originally I created the sheet, form and script but changed ownership so that the emails would not come from my account. 

Previous places I worked at had an account specifically dedicated to systems so ownership was not an issue but I'm unsure of what the best option is. We are looking at the option of an alias or maybe a collaborative inbox. https://support.google.com/a/answer/167430?hl=en%E2%80%8B​

 

I have made sure there is a trigger set to on form submit from the owner also and had them authorise the script.

Any advice re above or the script are much appreciated.

Thanks
PB

function sendEmail() {

  

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet1 = ss.getSheetByName("FORM");

  var lastRow1 = sheet1.getLastRow(); 

  var response = sheet1.getRange(lastRow1,16,1).getValue(); 

  var emailSent = sheet1.getRange(lastRow1,29,1).getValue();

  var name = sheet1.getRange(lastRow1,3).getValue();

  var sheet2 = ss.getSheetByName("EMPLOYEE LIST");

  var lastRow2 = sheet2.getLastRow();

  var emailRange = sheet2.getRange(2,8,lastRow2,1).getValues();

  

  for (var i = 0; i < emailRange.length; ++i) {

    var row = emailRange[i];

    var emailAddress = row[0]  

    

    if (emailAddress.match('@')  === null){

       continue;  

    };

  

    if (emailSent != "EMAIL_SENT"){

      continue;

    };

    

    if (response != " "){

    

      var message = name + " " + response; 

      var subject = 'Notification of Staff absence';

   

     MailApp.sendEmail(emailAddress, subject, message);

      sheet1.getRange(lastRow1,29).setValue("EMAIL_SENT");

      SpreadsheetApp.flush();

  }

  }

}

[Martin Hawksey]

When the person you want to send emails from runs the function in the script editor are any error messages thrown?

[Penelope Barrett]

No. It work once as expected. Further form submissions that should have triggered the script and sent an email didn't work.

There were no errors thrown.

[Martin Hawksey]

If you add a trigger from your own account does it work?