Analyzing Responses from Grid Items of Google Form using Google Apps Script

GasTips / spreadsheet
Google Apps Script / form / spreadsheet

Gists

This is a sample script for analyzing the responses from Grid Items of Google Form using Google Apps Script.

In this sample situation, all responses are retrieved from Grid Items of Google Form, and the average values of each row of each question are calculated. And, the result is put on the Spreadsheet.

Sample situation

Input: Sample Google Form

The sample Google Form is as follows.

Output: Sample Spreadsheet

The sample output is as follows.

The average values of each row of Grid Items of each question are calculated and put to the Spreadsheet.

Sample script

function myFunction() {
  const formId = "###"; // Please set your Form ID.
  const sheetName = "Sheet1"; // Please set the sheet name.

  // Retrieve responses from the grid items.
  const form = FormApp.openById(formId);
  const formResponses = form.getResponses();
  const obj = formResponses.reduce(
    (o, e) => {
      e.getItemResponses().forEach((f) => {
        const item = f.getItem();
        if (item.getType() == FormApp.ItemType.GRID) {
          const title = item.getTitle();
          o.res[title] = o.res[title] || {};
          const response = f.getResponse();
          const rows = item.asGridItem().getRows();
          o.head = [...o.head, ...rows];
          rows.forEach((g, k) => {
            const v = Number(response[k]) || 0;
            o.res[title][g] = o.res[title][g] ? o.res[title][g] + v : v;
          });
        }
      });
      return o;
    },
    { res: {}, head: [] }
  );

  // Create an array for putting the values to Spreadsheet.
  const header = [...new Set(obj.head)];
  const len = formResponses.length;
  const values = [
    ["Title of question", ...header],
    ...Object.entries(obj.res).map(([k1, v1]) => [
      k1,
      ...header.map((h) => (v1[h] ? v1[h] / len : null)),
    ]),
  ];

  // Put the values to Spreadsheet.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}

Reference

 Share!