Google-sheets – Grading a Google Spreadsheet by answers given in a Google Form

google sheetsgoogle-apps-script

I'm currently working in a school and students answered a 50 question form on ICT Ability. The results of this form have been put into a spreadsheet and I have to order the names in terms of ability.

I know how to do each row to check if the answer was correct but I'm wondering if I put in a row with the correct answers, is there a way to rank each student in a percentage with how many answered the same as the row?

Sample data:

Name    Q1      Q2      Q3      Q4 
Answer  No      True    False   Portait  
John    No      True    False   Portait  
Jenny   Yes     True    True    Landscape 
April   Yes     False   True    Landscape

Sample result:

Name    %
John    100
April   25
Jenny   0

Best Answer

With the folowing piece of code you can calculate the grades. @Tom, thanks for reviewing the question.

Code

function myGrades(range) {
  var output = [];

  // header
  output.push(["Name", "Score"]);

  // set up the proper matrix
  for(var j=2, jLen=range.length; j<jLen; j++) {
    var name = range[j][0], score = 0;
    for(var l=1, lLen=range[0].length; l<lLen; l++) {
      if(range[j][l] == range[1][l]) {
        score++;
      }
    }    
    // calculate the grade
    var grade = Math.round(score/((range[0].length-1))*100);
    output.push([name, grade + "%"]);    
  }
  return output;        
}

Usage

=myGrades(DATA!A1:E5)

Add the code under Tools > Script editor from the spreadsheet menu. In the script editor, paste the code and press the save button.

Example

I've created an example file for you: Grading Answers

Related Solutions

Google-sheets – Run a custom script more than 20 times in Google Spreadsheet

A couple of days of looking at your code and I think I may have a solution, but it's a little bit of a kludge.

You can create a function that returns a 2D array which will fill the cells below and to the right of the original cell. So I would try something like this: (sample sheet here)

= getSheetNames(2)

where your student sheets are after the second sheet, and use this custom function:

function getSheetNames(after) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  if (arguments.length === 0) {
    after = 0;
  }
  var allSheets = ss.getSheets();
  var allSheetNames = [];
  var sheetCount = allSheets.length;

  for (var i = after; i < sheetCount; i++) {
    //as you're putting them all in one row, you don't use array wrappers
    allSheetNames.push(allSheets[i].getName());
    //because it's merged need to add extra cell
    allSheetNames.push('');
  }
  return allSheetNames;
}

Edit - as you are using one row and inserting into merged cells - I have made some changes

Google Sheets: Sum Row Without Overwriting Formula When Google Forms Adds New Row

Please try:

=ArrayFormula(G2:G+H2:H+I2:I+J2:J)