Home > front end >  Create gForms and consolidate all answers
Create gForms and consolidate all answers

Time:01-13

I have to create a survey where I use a multiple choice grid with rows and columns such as:

| Item | Rate 1 | Rate 2 | Rate 3 |
| ---- | ------ | ------ | ------ |
| Item1 | x |  |  |
| Item6 |  | x |  |
| Item9 |  | x |  |

I need to send the form to 100 users. I need to create a different form for each user because the number of rows (items) will change based on the users. I do NOT want to create a web app with HTML. I would like to use the Google Forms.

I am planning to use this script for each user and modify it to dynamically change the rows based on the user.

var form = FormApp.create('Title');
form.setDescription("Please use the following criteria for rating:\n - **1**: explanation..... ");
form.setCollectEmail(true);
var item = form.addGridItem();
item.setTitle('Indicate your rating')
    .setRows(['Item 1', 'Item 2', 'Item 3']) //I will change here and I know how to do it
    .setColumns(['1', '2', '3'])
    .setRequired(true);
var item = form.addParagraphTextItem();
item.setTitle('Provide additional explanation');

The issue is that I also need to collect the answers and consolidate them for each item. There will be 100 forms with same columns, but different rows.

How can I access the answer easily? Is there as way to set the form to send the answers to another script/gSheet?

One way to do it is to use the Class FormResponse such as .getResponse(). But I would need to run it for each of the created form every x hours to get the latest responses and then consolidate in a Google sheet.

Is there a way to avoid a cron job which keep monitor the answers? For example if I could collect the answers only when the are submitted.

CodePudding user response:

There is a way to consolidate all answers (aka responses) from different google forms into one spreadsheet. This can be done by adding the following code after creating the form:

var form = FormApp.create('Title');
form.setDestination(FormApp.DestinationType.SPREADSHEET, "your gSheet ID here");

It will create a new sheet called "Form Responses 1",2,3,4,... for each form linked to the Spreadsheet.

Once the data is all in one spreadsheet, I can either:

  • periodically loop each sheet and send the consolidated data in one single sheet
  • OR, use onEdit(e), detect which sheet was edited by the form submission (e.range.getSheet()), extract the response and send the new response to a single sheet

Thanks to @tanaike for the direction

  • Related