Getting individual question scores(numbers) from Google forms to a google spreadsheet

Question

I have a google form which is basically an assessment for students. Each question carries 1 point. When I connect my form to a specific spreadsheet, I get the total score of the student e.g 24/30

1. What I want to do: Along with the total score, we want to get each question's score to go to the spreadsheet. Here is what we are trying to have:

I have no idea what to do. Please guide. Thanks

Answer 1

You can try with Apps Script. I tested the following script that can give you an idea on how to achieve this.

const TARGET_SPREADSHEET_ID = "spreadsheetID"; //Change according to your needs
const TARGET_SHEET_NAME = "sheetName"; //Change according to your needs
const SOURCE_FORM_ID = "formID"; //Change according to your needs

//Run this function only one time, this will create a trigger that will run function "onFormSubmitTrigger" whenever a student submits a response.
function installOnFormSubmitTrigger() {
  const form = FormApp.openById(SOURCE_FORM_ID);
  ScriptApp.newTrigger("onFormSubmitTrigger")
    .forForm(form)
    .onFormSubmit()
    .create();
}

function onFormSubmitTrigger(e) {
  const targetSpreadsheet = SpreadsheetApp.openById(TARGET_SPREADSHEET_ID);
  const targetSheet = targetSpreadsheet.getSheetByName(TARGET_SHEET_NAME);
  
  //GETTING RESPONSES
  var itemResponses = e.response.getItemResponses();
  var responses = itemResponses.map(itemResponse => itemResponse.getResponse()); //Get responses
  var name = responses.splice(0,1); //extracting first element ("Full Name") of responses

  //GETTING SCORES
  var itemScores = e.response.getGradableItemResponses();
  var scores = itemScores.map(itemScore => itemScore.getScore()); // Get response score
  scores.splice(0,1); //removing score for the first element ("Full Name")
  var newArr = name.concat(scores); //combining name and scores in one array
  //newArr.splice (1,0,""); //you can add this line to insert blank between "Student Name" and "Q1"
  targetSheet.appendRow(newArr); //Append scores to the sheet
}

Test the script by submitting a form and the target sheet should show students names and the score for each question they answered. If you have any questions let me know.