How can I set a form response destination to be a specific sheet in a spreadsheet?

Question

So I am sending out a mass amount of forms where each form is specifically designated for that specific responder (the context of the questions in the form changes for the user it is submitted for), that part is fine. But I want to record their response into a master spreadsheet where each user is represented by a sheet in that spreadsheet. I want to do something like

//activeRef is a Row object on another spreadsheet
//responseSS is an already existing spreadsheet
For each member - of which there are N - {
    responseSS.insertSheet(activeRef.refereeID);
    activeResponseSheet = responseSS.getSheets()[N];
    conflForm = FormApp.create('Conflict Declerations')
    conflForm.setDescription(DESCRIPTION)
             .setAllowResponseEdits(true)
             .setShowLinkToRespondAgain(false)
             .setDestination(FormApp.setDestinationType.SHEET, responseSS.getSheets()[N]);
}

Unfortunately, you can't set a destination to be a specific sheet like that, it has to be a spreadsheet from what I understand. I could have a specific spreadsheet for each form response but there will be dozens to hundreds of these - it would be more manageable to have each response recorded into one sheet in a master spreadsheet.

Is there a possible solution to this?

Thanks

you should look at that it may give you some ideas: http://stackoverflow.com/questions/21783279/updating-existing-data-on-google-spreadsheet-using-a-form/21785872#21785872 — Harold, Feb 14 '14 at 20:11
There is currently no way to have multiple forms submit directly to the same spreadsheet. I would recommend setting up a master spreadsheet and then using an =IMPORTRANGE (https://support.google.com/drive/answer/3093340?hl=en) function on each separate tab (one per form) within the master spreadsheet. This will pull in the data you need and updates automatically. To further collate your data, all into one tab, you could write a manual/triggered script that works through the tabs and copies all your info to one uber tab. Long, but it will do what you want. — Sam Scholefield, Feb 15 '14 at 01:10
Thanks, I'm trying to do something along those lines but I've run into another issue now. Because my forms are being dynamically created I can't think of how I would designate a formSubmit trigger for each (automatically). How do I get the code to react to a submission to a form without going into each forms linked spreadsheet manually to add the appropriate script. Maybe I am misunderstanding your IMPORTRANGE suggestion? — eclement, Feb 17 '14 at 21:21

score 0 · Answer 1 · answered Jun 26 '20 at 20:57

It's not possible to set a specific sheet to receive form responses because the spreadsheet-form link process creates a new sheet.

There are two alternatives

Use a formula (i.e. ={'Form Responses 1'!A2:G;'Form Responses 2'!A2:G;'Form Responses 3'!A2:G})
Use a on form submit installable trigger from a for the spreadsheet receiving the form responses. The event object include several properties that could be helpful like range, values, etc.
Use one on form submit installable trigger for each form. The event object includes several properties that could be helpful i.e. response

How can I set a form response destination to be a specific sheet in a spreadsheet?

1 Answers1