Google Script not Finishing Successfully

Question

I recently adapted a short piece of script to automatically send an e-mail to me when a specific response appears (the word FAIL) in a 'Google docs' spreadsheet, that collects responses from volunteers using a 'Google Form' to record their weekly checks on life-saving equipment.

The email on FAIL is working well, having tested it with a few FAIL responses. However, as the form 'owner' I am receiving notifications from Google, for each normal PASS response submitted, telling me that the script failed to finish successfully.

8/4/15 10:57 AM SendGoogleForm ReferenceError: "found" is not defined. (line 35, file "Code") formSubmit

It finishes successfully if a FAIL response is submitted, so I suspect that, due to my inexperience with scripts, I have not defined what should happen when found is 'not true'.

It's probably glaringly obvious to a more experienced script writer but none of the things I have tried with 'Else' seem to work.

Any suggestions would be greatly appreciated.

function Initialize() {

var triggers = ScriptApp.getProjectTriggers();

for (var i in triggers)
    ScriptApp.deleteTrigger(triggers[i]);

ScriptApp.newTrigger("SendGoogleForm")
    .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
    .onFormSubmit().create();

}

function SendGoogleForm(e) {

if (MailApp.getRemainingDailyQuota() < 1) return;

// Recipients email address
var email = "[email protected]";

// Subject for Google Form email notification
var subject = "Weekly Checks - FAIL Submitted";

var s = SpreadsheetApp.getActiveSheet();
var columns = s.getRange(1, 1, 1, s.getLastColumn()).getValues()[0];
var message = "A check form has been submitted containing a ** FAIL ** response:  ";
// Look for a 'FAIL' response
var key = columns[keys];
for ( var keys in columns ) {
  if ( e.namedValues[columns[keys]] == 'FAIL')  {
    found = true; 
  }
}

// Only include form fields that are not blank
if (found) 
    for (var keys in columns) {
    var key = columns[keys];
    if (e.namedValues[key] && (e.namedValues[key] !== "")) {
        message += key + ' : ' + e.namedValues[key] + "\n\n";
    }
}

MailApp.sendEmail(email, subject, message);

}

allanquartz allanquartz · Accepted Answer · 2015-08-10T13:04:47

Your found variable is null whenever a non-"FAIL" occurs in your script and I think your if(found) is creating the error message.

You could Initialise your variable:

var found = false;

before you start testing it. That way, your if(found) will be false and it will skip down to your send email code.

Google Script not Finishing Successfully

2 Answers