Can multiple forms store their results in the same spreadsheet? [duplicate]

Question

I have multiple Google forms with matching questions. Is it possible to keep the forms separate but see their results in the same Google spreadsheet?

Answer 1

You can have them go to different sheets and then combine them in a third sheet with a formula like so:

=QUERY({IMPORTRANGE("XYZk0274gRlmluCTfMbzbMQWKiAeq1va77X4","Mar-Apr-May!A2:D6");IMPORTRANGE("XYZahJZHSlhMGLSW_xA6ZBqNmt1I0ADo4N4M","Jun-Jul-Aug!A2:D4")},"select * where Col1<>''")

Code source

Note that

• "XYZ…" is the key from a Sheet URL. You can also use the full URL. You will need to approve access between the sheets
• In "Mar-Apr-May!A2:D6" the part before the ! is the name of the sheet tab, and "A2:D6" is the range of cells to import.
• "select * where Col1<>''" is filtering out blank columns

Answer 2

Yes-ish

You can definitely have the responses from multiple Google forms go to the same spreadsheet however each form's results will be stored in a separate sheet (tab).

It is a simple matter to add another sheet that combines the form results.

Formulae

If your form results are in columns A to F of Form Responses 1 and Form Responses 2, use the following formula in another sheet to combine the results.

1(a). QUERY

=QUERY({'Form Responses 1'!A:F;
        'Form Responses 2'!A2:F},
        "where Col1 is not Null", 1)

• note that the first sheet starts at Row 1, but the second (third, fourth, etc.) start at Row 2 so the headings aren't duplicated.
• The sheet will dynamically update as form data is submitted.
• QUERY function - Google Docs Editors Help

1(b). QUERY w/ Order by

• Assuming the first column is the timestamp, you could sort the data in descending order by that column:

=QUERY({'Form Responses 1'!A:F;
        'Form Responses 2'!A2:F},
        "where Col1 is not Null
         Order by Col1 Desc", 1)

2(a). FILTER

=LET(array,{'Form Responses 1'!A:F;
            'Form Responses 2'!A2:F},
     FILTER(array, INDEX(array,,1)<>""))

2(b). FILTER w/ SORT

• If you plan to sort the data then with FILTER you should separate the header row from the SORT in the formula:

=LET(array,{'Form Responses 1'!A2:F;
            'Form Responses 2'!A2:F},
    {'Form Responses 1'!A1:F1;
     SORT(FILTER(array, INDEX(array,,1)<>""),1,0)})

Specify Existing Spreadsheet

1. Click the kebab More button on the Responses tab
   
2. From the More menu, choose Select destination for responses
   
3. Choose the option Select existing spreadsheet and then the Select button.
   
4. Select the Google spreadsheet where you want the responses. A new sheet will be inserted for each form.