How to Save Google Form Responses to Different Sheets Easily

Google Forms is perhaps one of the most popular services for collecting surveys and it's easy to understand why. From the addition of a conditional logic to downloading files, Forms is a boon for investigators and teachers. However, as you must know, it is difficult to sort and analyze each answer of the sheet, especially if you have a lot of answers.

Would not it be easier to save each answer from Google Form in different sheets automatically depending on the answer?

Well, we are almost in 2020 and it would be disappointing to have to do all sorting and analysis manually. Thanks to the simple formulas and functions of Google Sheets, it's easy to save all Form responses to individual sheets.

All you have to do is determine how you want to separate the answers. Once you have determined the conditions (one or more conditions), the following steps become a child's game.

Let's start.

Method 1: Use a query

This method uses the query function of Google Sheets. For those who do not know it, this function uses simple SQL type queries to sort the table data according to your preferences.

Mathematical formulas such as Sum and Avg with the use of clauses like Contains and Like, you can experiment a lot. And of course, you can select the range of data where you want these conditions to be met.

Once done, you can put the answers on different sheets.

So, if we have to find the average of column A, the query will look something like,

 =Query(A1:A12,"select avg(A)")

Some of the conditions to be fulfilled are as follows.

• The query must be enclosed in quotation marks.
• Otherwise, the query must contain a reference to cell values.
• Column values ​​must be Boolean, String, or Numeric.

This method requires only an initial configuration based on the values ​​of your form. Once you are satisfied with the first results of your tests, you can then share the form with the public.

How to use the sheets query feature to save responses to a Google form

Step 1: Open Google Forms and create your form. In our case, we have created a form with multiple-choice questions (MCQs), because we want to separate the data by the name of the city.

Once done, go to the Answers tab and click on the three-dot menu next to the small Leaves icon.

Choose Select Response Destination from the menu, and then click Create New Spreadsheet. Give the sheet a name that is easy to remember.

Once the link to the spreadsheet has been established, open it via Google Sheets. At this point, you will notice that the name of the first sheet has been replaced by Form Responses 1.

2nd step: Open a second sheet and paste the following formula into the first cell,

 =query('Form Responses 1'!A:E,"Select * Where D='Mumbai'")

As you can see, the sheet name appears first, followed by the range of cells, and then your conditional query. After changing the condition in the query, repeat the same for all other tabs.

In our case, the third and fourth leaves looked like

 =query('Form Responses 1'!A:E,"Select * Where D='Delhi'")

 =query('Form Responses 1'!A:E,"Select * Where D='Kolkata'")

Step 4: Now, all you have to do is submit some test answers and see the magic unfold.

Professional advice: If you just want to choose the value of a column, replace * with the name of the column.

Refine the query further

The good thing is that the sheet manages the naming of headers and you do not have to worry about it.

You can also refine the query to fit the conditions of your form. For example, if you want to match a respondent's answer to a string value, the Where clause will be replaced by the Contains or Like clause. And if you're comparing string values, it's always wise to convert the string using the Upper function.

 =query('Form Responses 1'!A:E,"Select * Where E Contains 'good'") =query('Form Responses 1'!A:E,"Select * Where E like 'good'")

In addition, Google Sheet queries are not as advanced as SQL statements. On the one hand, you can not use the% & #39; to match the responses to the given string value. This only works for an exact match. Thus, even if a person answered "good note" in the answer, the above request will not detect it.

Similarly, you can also play with dates.

Note: Scroll right to view or select the full formula below.

Sheets include only the date format "yyyy-mm-dd". You may need to set the starting column format for best results.

 =query(Form Responses 1'!A:D,"select C, B where B > date '"&TEXT(DATEVALUE("1/1/2000"),"yyyy-mm-dd")&"'",1)

You can also add several conditions to your sheets. All you need to remember is to separate the two conditions with "Or" or "And".

Method 2: Filter

Another function that you can use is the filter, for a single condition and can be used to get specific results.

For example, the following items filter lines 1 to 7 when the value of columns D1 through D2 is Mumbai.

 =filter('Form Responses 1'!A1:E7,'Form Responses 1'!D1:D7="Mumbai") 

However, this formula may fail if you are looking for an automatic form submission in which the number of rows may exceed your expectations.

Work intelligently

This lets you easily separate responses from Google Forms to different sheets. The best thing to do is that the master data will always appear on the first sheet that you can reference later.

The only limitation is that you will not be able to copy the contents of a sheet directly via the "Copy To" function. In this case, Sheets copies the formula instead of the content in the new worksheet, making the new worksheet useless.

The good news is that you can still copy and paste the content manually.

Then: Do you have to give up Google Forms for Jot Forms? Read the next article to find out which service is right for you.