google form- export individual question scores to sheets

Question

I am trying to get the scores for each question from a graded google quiz in to the corresponding sheet. There will be open ended questions that will need to be hand graded on a 1-3 point scale and the answers will vary greatly. I want to be able to hand grade them on google forms and then export just the scores. The scores and questions would be fine too!

This is what the form produces

This is what I am hoping to do.

Example form

Example Sheet

Thank you!

I tried running a different script, but could not get it to work at all. I also tried using the flubaroo addon

Answer 1

In your situation, how about the following sample script?

Sample script:

Unfortunately, in the case of your URL of the Google Form of https://docs.google.com/forms/d/e/###/viewform?usp=sharing, ### is not Google Form ID. So, in this sample script, the Google Form ID is retrieved from your Spreadsheet.

Before you use this script, please set the destination sheet name to dstSheetName. In this sample, the result values are put to a sheet in the same Spreadsheet of your provided Spreadsheet.

function sample() {
  const dstSheetName = "Sheet1"; // Please set the destination sheet name.

  const header1 = ["Timestamp", "Score"]; // This is from your question.
  const header2 = ["Name", "Q1 score", "Q2 score"]; // This is from your question.
  const spreadsheetId = "1YV6yMD4qVOy0Nn5lSXlYNNCLqJTMEo6j0lDnrqYByVE"; // This is from your question.

  // Retrieve values from Google Form.
  const ss = SpreadsheetApp.openById(spreadsheetId);
  const form = FormApp.openByUrl(ss.getSheets()[0].getFormUrl());
  const score = form.getItems().reduce((n, item) => {
    const type = item.getType();
    if (type == FormApp.ItemType.MULTIPLE_CHOICE) {
      n += item.asMultipleChoiceItem().getPoints();
    } else if (type == FormApp.ItemType.TEXT) {
      n += item.asTextItem().getPoints();
    }
    return n;
  }, 0);
  const values = [[...header1, ...header2], ...form.getResponses().map(r => {
    const itemResponses = r.getGradableItemResponses();
    const temp = header2.map((_, i) => {
      const res = itemResponses[i];
      return i == 0 ? res.getResponse() : res.getScore();
    });
    const total = temp.reduce((n, e) => {
      if (!isNaN(e)) n += e;
      return n;
    }, 0);
    return [r.getTimestamp(), `${total} / ${score}`, ...temp];
  })];

  // Put values to Spreadsheet.
  const sheet = ss.getSheetByName(dstSheetName);
  sheet.clearContents().getRange(1, 1, values.length, values[0].length).setValues(values);
}

• When this script is run, I confirmed that your expected result is obtained.

• I noticed that in your expecrted result image, the score of "Maggie" is "4 / 4". It seems that each score is 1 and 0. But, when this script is run, each score is 1 and 3. Please be careful about this.

Note:

• This sample script is for your provided Google Form. So, please test this script on your provided Google Form and Google Spreadsheet. When you change the Google Form, please modify this script. Please be careful about this.

References:

• getResponses()
• getGradableItemResponses()
• getScore()