Unknown Status in Google App Script Project Executions

Question

I have an Google form that field crews fill out and submit. The attached javascript emails the contents of the form as emails to everyone in the office.

The execution times are super long. One has 19496 reported seconds, when Gsuite should automatically terminate any script at 5 minutes. Some have "Unknown" status in the Google app script execution log and 0 seconds.

Is the Gsuite quota being used up? Is there an error in my script?

The user running the script trigger also receives a bounce back email, even through all the emails are going through, and the Google sheet is receiving the response from the Google Form normally.

I tried adding "if(e.values && !e.values[1]){return;}" to the top and "return;" at the bottom. It didn't seem to change the issue.

I edited the google app script below to remove the real list of the email addresses, and shortened up the report. The purpose of the Google Form is to provide a real summary of their days work instead of just "Job is done" in an email. So, they fill out a list of 15 questions instead.

function myFunction(e){

// Set values for event "e" from Response form, each number being a column in the spreadsheet 
    var value1 = e.values[1];
    var value2 = e.values[2];
    var value3 = e.values[3];
    var value4 = e.values[4];
    var value5 = e.values[5];


  // Build subject and message for email that will be sent out
    var subject1 = value5 + " Job #" + value2 + " " + value3 + " Job Report Submitted " + value1 + "    -oOo-";
        var message_html = "<b>Date:</b> " + value1 + "<br>" + 
                   "<b>Job Number:</b> " + value2 + "<br>" +
                     "<b>Site Name:</b> " + value3 + "<br>" +
                     "<b>Client:</b> " + value4 + "<br>" +
                       "<b>Crew Chief:</b> " + value5 + "<br>";            


     // Send email to chief, of what the chief submitted through the Response form
     var chiefemail = "[email protected]"; //setting leo email as the default - but this should not be used based on below 
     var chiefname  = "Leo E.";

     if (value5 == "Bryan N.") {
                                    chiefemail = "[email protected]";
                                    chiefname  = "Brian N";}
     else if (value5 == "Carl B.") {
                                    chiefemail = "[email protected]";
                                    chiefname = "Carl B";
                                   }
     else if (value5 == "Clay W.") {
                                    chiefemail = "[email protected]";
                                    chiefname = "Clay W";
                                     }
     else if (value5 == "Dakota P."){
                                    chiefemail = "[email protected]";
                                    chiefname = "Dakota P";
                                     }



 // Send emails to all office staff:     

   var EmailList = "[email protected]," + chiefemail; 


       MailApp.sendEmail({
                          to: EmailList,
                          subject: subject1,
                          htmlBody: message_html,
                          name: chiefname,
                          replyTo: chiefemail
                           });


}

I want the script to terminate, and I don't want to receive bounce back emails. Help!

Answer 1

I think it may be possible that your experiencing what I call spurious onFormSubmit triggers and I might try something like this.

function myFunction(e){
  if(e.values && e.values[1] && e.values[2] && e.values[3] && e.values[4] && e.values[5]) {
    var value1 = e.values[1];
    var value2 = e.values[2];
    var value3 = e.values[3];
    var value4 = e.values[4];
    var value5 = e.values[5];
    var subject1 = value5 + " Job #" + value2 + " " + value3 + " Job Report Submitted " + value1 + "    -oOo-";
    var message_html = "<b>Date:</b> " + value1 + "<br>" + 
      "<b>Job Number:</b> " + value2 + "<br>" +
        "<b>Site Name:</b> " + value3 + "<br>" +
          "<b>Client:</b> " + value4 + "<br>" +
            "<b>Crew Chief:</b> " + value5 + "<br>";            
    var chiefemail = "[email protected]"; //setting leo email as the default - but this should not be used based on below 
    var chiefname  = "Leo E.";
    if (value5 == "Bryan N.") {
      chiefemail = "[email protected]";
      chiefname  = "Brian N";}
    else if (value5 == "Carl B.") {
      chiefemail = "[email protected]";
      chiefname = "Carl B";
    }
    else if (value5 == "Clay W.") {
      chiefemail = "[email protected]";
      chiefname = "Clay W";
    }
    else if (value5 == "Dakota P."){
      chiefemail = "[email protected]";
      chiefname = "Dakota P";
    }
    var EmailList = "[email protected]," + chiefemail; 
    MailApp.sendEmail({
      to: EmailList,
      subject: subject1,
      htmlBody: message_html,
      name: chiefname,
      replyTo: chiefemail
    });
  }
}

Your read more about it here. You can also check you stack driver log to see whats going on.