Google-sheets – How to report which checkboxes were left unchecked on a Google Forms submission

google sheetsgoogle-apps-scriptgoogle-forms

I have a Google form that stores its responses in a Google spreadsheet. It's set up so that whenever someone submits the form, I receive an email report of the responses. There are many places in the form where I want to know which checkboxes were left unchecked.

For example:

Question – Which doors did you lock?

Answer (checkboxes) – 1) Front Door, 2) Back Door, 3) Side Entrance

If someone selects "Front Door" and "Back Door" but leaves "Side Entrance" unselected, I would like the email notification to say, "Unlocked: Side Entrance."

Google Forms seems to automatically save a comma-delineated string containing all of the checked checkboxes. My current code searches for each possible selection (as a string) then prints the items that were not found within the list of checked checkboxes. This approach gets cumbersome very quickly when there are a lot of checkboxes or when the checkboxes change.

Is it possible to directly get the values of all checkboxes that were left unchecked? If not, is it possible automatically (i.e. no need to change code when adding/removing/changing checkboxes) get the values of all checkboxes then output the difference between all checkboxes and checked checkboxes?

Here's how I'd script the email notification for the above doors example. Please tell me there's a better way! 

var emailNotice = "";
var doors = e.values[1]
var allDoors = "Front Door, Back Door, Side Entrance" 
    if (doors != allDoors) {
        var doorsMessage = "";    
        if (doors.search("Front Door") != -1) {doorsMessage += "Front Door, ";}
        if (doors.search("Back Door") != -1) {doorsMessage += "Back Door, ";}
        if (doors.search("Side Entrance") != -1) {doorsMessage += "Side Entrance, ";}
        var doorsLength = doorsMessage.length;
        if (doorsLength >= 2) {doorsMessage = doorsMessage.substr(0, doorsLength-2);}
        emailNotice += "Unlocked: " + doorsMessage;
     }

Best Answer

Still don't have a solution for how to directly get values of unchecked checkboxes, but I was at least able to write a function that compares the correct answer to the submission.

This splits the correct answer into an array then compares each item to the submission:

function checkAnswer(answer, key) {
var kArray = key.split(', ');
var nArray = [];
for (i=0; i<kArray.length; i++) {
    if (answer.indexOf(kArray[i]) == -1) {
        nArray.push(kArray[i]);
    }
}
return nArray.join(", ");
}

Then I could call it every time I needed to report the boxes left unchecked. For example, with the doors question:

if (doors != correctDoors) {
  var doorsMessage = "Unlocked: " + checkAnswer(doors, correctDoors);
  report.push(doorsMessage);
}

At the end, I'll compile the report with:

report.join("\n");

And voila! The best I could do...

Ultimately, I'm still looking for how to directly get the unchecked boxes if anyone has a solution.

Related Topic