Create gForms and consolidate all answers-CodePudding

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