Google Form Quizzes - Find Max Score - Apps Script

If you have ever created a quiz for mastery, you may have discovered you can have students take the quiz as many times as they want.  As an educator, you can use the number of retakes a evidence of growth or opportunities to provide overall performance gains while also providing short penalties to discourage frequent and rapid retakes.  For example, students can take the quiz as many times as they wish, but for each time they take the quiz, the score is reduced by 2.5%. If you have this information in a spreadsheet, you are golden.

In this tutorial, we will create an App Script file to filter out Google Form scores to quickly upload the marks to our SIS or online Gradebook.

Problem:

If using multiple quizzes in a year where students can retake quizzes, reimplementing formulas to filter the data can be a pain.  

Solution & Skills:

We will create one Google Spreadsheet where future forms can be pointed.  We will use Apps Script to pull and format the scores, creating a new sheet with clean clear scores we can then upload to our SIS/online gradebook. 

Skills:

✔ Programmatically create a dropdown menu;

✔ Write formulas programmatically and insert into a Google Sheet;

✔ Hide columns and rows to provide a cleaner look to your sheet.

Script - Custom Menu

Creating a custom menu to run your script, even for testing, can give you a quick way to monitor and manage your script.  Let's get building.

Step 1: Create an onOpen function.

function onOpen() {
  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
      .createMenu('Clean Scores🦸‍♀️')
      .addItem('Max Scores', 'showScoreAlert')
      .addToUi();
}

Step 2: Create Alert

We want to access the user interface with alter that warns the user before initiating.  To do this, we need to call the ui.alert and ensure the user has the option to cancel.  We also want to make sure the user is on the sheet with the Google Form Quiz results.

function showScoreAlert() {
  var ui = SpreadsheetApp.getUi();

  var result = ui.alert(
     'Get Max Quiz Score',
     'Make sure you are on the Form Response sheet you want to reference.\n\n'
     +'Are you sure want to proceed?',
      ui.ButtonSet.YES_NO);

  // Process the user's response.
  if (result == ui.Button.YES) {
    // User clicked "Yes".
    var formResult = SpreadsheetApp.getActiveSpreadsheet().getSheetName();
    getMax(formResult);
    ui.alert('Confirmation received.');
  } else {
    // User clicked "No" or X in the title bar.
    ui.alert('Process Cancelled');
  }
}

Alerts don't contain the HTML formatting.  However, we can use the Unicode escape character "\n" to create line breaks. Notice, if the "yes" button is clicked, there is a variable "formResult" that captures the SheetName.  That variable is then passed as we call the function "getMax(formResult)".  We better create the getMax function.

Script - Create Filtered Grades

Step 3: Create getMax Function

For this function, I want to call a global variable.  A global variable is available for use for any function within the program.  I prefer to keep my gloabal variables at the top of my script. You can also assign the value of global variables within the functions, but I assigned my at the top to quickly change if needed in the future.

Now, let's type out the function, and then I'll go over the script.

  //Globals
  var letter = "E";  // letter = last column to pull from form...I pull the students names
  

//Gets the Max Score from passed sheet (formResponse)
//Creates new sheet, inserts formulas, hides formula rows and columns
function getMax(formResponse) {
  
  var cs = SpreadsheetApp.getActiveSpreadsheet().insertSheet();
  var formula = "=query('"+formResponse+"'!B2:"+letter+","
  +'"select B, max(C) group by B",-1)';
  var arrayFormula = '=arrayFormula(iferror(if(ISBLANK($A$1:$A),"",'
  +"split(VLOOKUP($A$1:$A,'"+formResponse+"'!$B$2:$F,3,false)&"
  +'" "&'
  +"VLOOKUP($A$1:$A,'"+formResponse+"'!$B$2:$F,4,false)&"
  +'"|"&$B$1:$B,"|")),""))';
  cs.getRange(1,1).setFormula(formula);
  cs.getRange(1,3).setFormula(arrayFormula);
  cs.hideColumns(1,2);  
  cs.hideRows(1,2);
}  

For the function, getMax(), we passed the form's sheet name as formResponse.  We then created a variable that creates a sheet (cs) by inserting into the active spreadsheet.  

The query function and arrayFormula are formatted and held in variables.  We then set the formulas into our newly created sheet.  Finally, we hide the columns and rows to only reveal the full names of the students are their max score.

How to write formulas programmatically

A lot of formulas have double and single quotes, and that presents a problem since the formula we insert needs to be text based.  It is good practice to place the formulas in a separate variable and partition it at logical places if it is a complex formula. (Learn to write query and array formulas)

Query Example:

=query('Form Responses 1'!B2:E,"select B, max(C) group by B",-1)

Programmatically:

      var formula = "=query('"+formResponse+"'!B2:"+letter+","
  		    +'"select B, max(C) group by B",-1)';
    

Notice how double quotes are used for the beginning of the formula, but single quotes (apostrophes) are used on the bottom. The beginning of the formula requires single quotes to declare the sheet name. Since my sheet name is passed as an object, the end quotes are use along with join symbol (+). That process is continued to add the global variable for the letter, which references the last column.  For the second line a join symbol is added, but this time single quotes are used since the query selector requires double quotes.

arrayFormula Example: 😲

=arrayFormula(iferror(if(ISBLANK($A$1:$A),"",split(VLOOKUP($A$1:$A,'Form Responses 1'!$B$2:$F,3,false)&" "&VLOOKUP($A$1:$A,'Form Responses 1'!$B$2:$F,4,false)&"|"&$B$1:$B,"|")),"")) 

Programmatically:

    var arrayFormula = '=arrayFormula(iferror(if(ISBLANK($A$1:$A),"",'
                        +"split(VLOOKUP($A$1:$A,'"+formResponse+"'!$B$2:$F,3,false)&"
                        +'" "&'
                        +"VLOOKUP($A$1:$A,'"+formResponse+"'!$B$2:$F,4,false)&"
                        +'"|"&$B$1:$B,"|")),""))';
  

The key is looking for the clearest places to divide the formula, using the fewest quotes.

Ways to Tweak

If you are interested in seeing how may times a student accessed the quiz, add "count(B)" to the selection part of the query and change the setting of the arrayFormula to range(1,4).  If you want to add the period of your students, add a VLookup that indexes the column with the period number.

If you are looking to post your grades to a SIS or online gradebook, try using an import tool from the vendor or a third party service like GradeTransferer.