Skip to content

Instantly share code, notes, and snippets.

@tanaikech
Created Oct 15, 2021
Embed
What would you like to do?
Putting All Response Values from Google Form to Google Spreadsheet using Google Apps Script

Putting All Response Values from Google Form to Google Spreadsheet using Google Apps Script

This is a sample script for putting all response values from Google Form to Google Spreadsheet using Google Apps Script.

Sample script

Please copy and paste the following script to the script editor of Google Spreadsheet and set the variables of formId and sheetName.

function myFunction() {
  const formId = "###"; // Please set the Google Form ID.
  const sheetName = "Sheet1"; // Please set the sheet name of sheet you want to put the values.

  // Retrieve all response values from Google Form.
  const form = FormApp.openById(formId);
  const headers = form.getItems().map((e) => e.getTitle());
  const values = form
    .getResponses()
    .map((f) =>
      f.getItemResponses().reduce((o, i) => {
        const r = i.getResponse();
        return Object.assign(o, {
          [i.getItem().getTitle()]: Array.isArray(r) ? r.join(",") : r,
        });
      }, {})
    )
    .map((o) => headers.map((t) => o[t] || ""));
  values.unshift(headers);

  // Put the values to Spreadsheet.
  SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName(sheetName)
    .getRange(1, 1, values.length, values[0].length)
    .setValues(values);
}
  • When this script is run, all response values are retrieved from Google Form and put them to the Spreadsheet.

Note

  • At Google Form, when the empty answer is submitted, the question has no value. By this, it is required to consider this. So at first, the titles are retrieved from the items, and the values are created using the item titles. I thought that this might be an important point.

References

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment