merge two google forms data into the one spreadsheet

Question

I want to create application which takes two different google forms data into the one spreadsheet and having two different sheets there on a single spreadsheet with the help of google apps script.

I've searched over a lot but hadn't find any satisfactory solution. Can anyone tell me that is that possible to acheive with google-apps-scripts?

Sorry, I don't have any piece of code this time.

Solution 1

It is impossible to insert data of two Google Forms directly to a single spreadsheet using GAS without involving another two spreadsheets, because now the Forms are able to deploy data only to own spreadsheet and GAS has no any service to access to the Forms.

There is a workaround which merges the Forms spreadsheets to a single spreadsheet using the ImportRange function (see bellow paragraph) but seems it is not suitable for you.

The spreadsheets have the ImportRange function which permits to import a source spreadsheet range to a destination spreadsheet, for instance, there is a task to import spreadsheet_1 columns A, B, C of Sheet1 and spreadsheet_2 columns B, C of the Sheet2 to spreadsheet_0 columns F, G, H, I and J. To achieve it is necessary to place to the spreadsheet_0 cell F1 the =ImportRange("spreadsheet_1_key", "Sheet1!A:C") formula and the cell I1 should contain the =ImportRange("spreadsheet_2_key", "Sheet2!B:C") formula.

Solution 2

For anyone who ends up at this question, looking for some general knowledge, you can change the form destination. In the form editor, choose RESPONSES, CHANGE RESPONSE DESTINATION. The form responses can be sent to another spreadsheet. I'm not sure if this is a capability that didn't exist when this question was first asked, but for anyone doing a general search, you might end up here.

Comments

[AdamL]

Just to add, there is an "out-of-the-box" solution in the Script gallery: ahab's VMerge custom function. You can invoke =VMERGE(ImportRange(...);ImportRange(...)). After installing the script, you can have a look at the code and perhaps ask q's from there.

[Viral Shah]

See I know about this ImportRange(...); Senario is I've Spreadsheet1 in this two sheets sheet1 and sheet2 and I want to add googleform1 data into the sheet1 and googleform2 data into the sheet2.. can it be possible via google apps script?

[Viral Shah]

anyone tell me how about this idea to use scriptdb for database instead spreadsheets? developers.google.com/apps-script/scriptdb

[megabyte1024]

@ViralShah, I updated my answer. About usage of the ScriptDB instead of spreadsheets. Briefly, it is possible to use. The details depends on your task. For instance if the task requires more than 50000 "rows", then the ScriptDB is not suitable solution for this task.

[Viral Shah]

I've requirements possibly for 400-500 rows only in my working application ... I'm creating the same application like googleappsdeveloper.blogspot.in/2012/04/… so, ScriptDB is the best solution over?

[megabyte1024]

@ViralShah Please mention that a ScriptDB has no tables. i.e if your application has the total number of "rows" in all "tables" strictly less than 50000, than it is possible to use the ScriptDB in your application. But there are other limitations described here. Check if your application meets the limitations.

[megabyte1024]

@ViralShah Another issue, is the ScriptDB data belongs to the script and not a logged user or a site, i.e. if you have a script storing data of 100 sites which produce 500 rows, then the ScriptDB is not a perfect solution.