Google Forms as Multiple Choice Quiz - How to Provide Results

Question

I've built a google form for a multiple choice quiz, with a linked spreadsheet for results, which works very well. I have a specific problem, which is that I'd like to present the user's results to them (i.e. how many answers they got right/wrong). The approach I've taken so far is:

• create an extra sheet on the spreadsheet with a formula to calculate the number of correct answers for each response. This gives me two columns "Full Name" and "Scores"
• embed the form into a google site
• create a google apps script to read the results sheet and display output
• embed the above into the same site below the form as an Apps Script Gadget

Currently I am able to display all of the results recorded so far. See here:

https://sites.google.com/site/mcqtest123/home

The script looks like:

// Script-as-app template.
function doGet() {
  var app = UiApp.createApplication();

  var title = app.createLabel("Survey Results").setStyleAttribute("fontSize","16px");
  app.add(title);

  //readRows(app);
  calculateScores(app);

  return app;
};

function calculateScores(app) {
  var sheet = SpreadsheetApp.openById("0AlNR-ou0QtandFFzX1JCU1VRdTl0NVBRNTFjOUFhd1E");
  var responseSheet = sheet.getSheetByName("Form Responses");
  var allData = responseSheet.getDataRange().getValues();

  var correct = allData[1];
  var responses = allData.slice(2);

  //Logger.log("Timestamp, name, score");
  Logger.log("Name, Score");
  for (var i = 0; i < responses.length; i++) {
    var timestamp = responses[i][0];
    var name = responses[i][1];
    var score = 0;
    for (var j = 2; j < correct.length; j++) {
      if(responses[i][j] == correct[j]) {
        score += 1;
      }
    }  
    //var output = timestamp + ", " + name + ", " + score + "/" + correct.length
    var output = name + ", " + score + "/" + correct.length
    print(app, output);
  }
};

function print(app, line) {
  Logger.log(line);
  app.add(app.createLabel(line));
};

So this leaves two problems:

1. When the page loads, it loads the scores for all the respondents. I'd like to be able to present only the score for the person who filled out the form.

2. The scores don't get updated when the form is completed - only when the page is refreshed.

For problem 1), I wondered if there was some way to access the data in the form iframe (e.g. using document.getElementById('targetFrame'), except that google scripts don't seem to have access to the document model) to only display results of the person whose full name matches the name in the form (of course you could then view someone else's results if you know what they'd put as their full name, but without using the timestamp I don't see away round this).

For problem 2), I wondered if there was some way to trigger the script when the responses sheet was updated. However when I go to the spreadsheet and Tools->Script Manager I get the message "No scripts found", so I don't know how to add this trigger.

Answer 1

If you make your own form using HtmlService or UiApp and then that POSTing to your script to populate the spreadsheet, then you can generate a UID in a hidden field and use this to determine the results someone needs to see.

This will be the results as instant feedback to their answers to the quiz. To see these at a later date, you could then also add a bookmarkable link that also included that UID as a parameter. So your doGet() would look for a e.parameters.uid for example.

From Google Forms as they stand I am not so sure. you could potentially, with the new form styles, offer a pre-filled field with such a UID, but the route from form submission to your webapp is again unclear.