How to Save Google Form Responses to Different Sheets Easily

How to Save Google Form Responses to Different Sheets Easily

Google Forms is a popular service for collecting surveys, and it’s easy to see why. From conditional logic to file uploads, Forms is a boon for surveyors and teachers. But sorting and analyzing responses on the sheet can be tedious, especially with a large number of responses.

How to Save Google Form Responses to Different Sheets Easily

Wouldn’t it be easier to automatically save each Google Form response to different sheets based on the answer? It’s almost 2020, and manually sorting and analyzing would be a bummer. With Google Sheets’ easy formulas and functions, saving all the Form responses to individual sheets is simple. Just decide how you want the responses separated (single or multiple conditions) and follow the easy steps. Let’s get started.

Method 1: Using Query

This method utilizes the Query function of Google Sheets. It uses SQL-like queries to sort and filter table data according to your preferences. You can use various mathematical formulas like Sum and Avg, as well as clauses like Contains and Like. You can also specify the data range for the conditions. Once the query is executed, the results can be displayed on separate sheets. For example, to find the average of column A, you would use the following query: =Query(A1:A12,”select avg(A)”). Here are some conditions to keep in mind:

– The query should be enclosed in quotation marks.

See also  How to Customize and Use Quick Tap Gestures on Android

– If not enclosed, the query should reference the cell values.

The column values must be Boolean, String, or Numeric. This method requires an initial setup based on your form’s values. Once you are satisfied with the test results, you can share the form publicly.

Step 1: Open Google Forms and create a form. In our case, we created a form with multiple-choice questions (MCQ) to separate data by city.

After creating the form, go to the Responses tab and click on the three-dot menu next to the Sheets icon.

How to Save Google Form Responses to Different Sheets Easily

Select response destination from the menu and create a new spreadsheet. Give the sheet a memorable name.

How to Save Google Form Responses to Different Sheets Easily

Once the link to the spreadsheet is established, open it in Google Sheets. Notice that the first sheet’s name has changed to Form Responses 1.

Step 2: Open a second sheet and paste this formula in the first cell:

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

The sheet name comes first, followed by the cell range, and then your conditional query. Repeat this process for all other tabs.

How to Save Google Form Responses to Different Sheets Easily

In our case, the third and fourth sheet were:

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

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

Step 4: Now, submit a couple of test responses and see the magic unfold.

How to Save Google Form Responses to Different Sheets Easily

Pro Tip:

To make your writing more concise and impactful, eliminate redundant words or phrases. Look out for repetitive ideas, redundant adjectives, or any phrasing that can be simplified without losing meaning. Enhance readability and clarity in your writing, while maintaining the original tone and meaning.

Tweaking the Query Further

The sheet handles naming the headers, so you don’t have to worry about them.

See also  How to Remove Copilot From Microsoft Edge

You can further tweak the query to fit your form’s conditions. For instance, if you want to match a respondent’s answer to a string value, replace the ‘Where’ clause with ‘Contains’ or ‘Like’. When comparing string values, it’s advisable to use the Upper function.

Google Sheet’s queries are not as advanced as SQL statements. You can’t use the ‘%’ parameter to match answers with the given string value; it only works for exact matches. Therefore, if someone responded with ‘goood’, the above query won’t include it.

How to Save Google Form Responses to Different Sheets Easily

Similarly, you can play with dates. Please note that Sheets only understands the ‘yyyy-mm-dd’ date format, so set the format of the column initially for better results. Use the formula below to query the data. You can add multiple conditions using ‘Or’ or ‘And’.

Method 2: Filter

Another alternative function you can use is Filter. It is suitable for simple conditions and retrieving specific results. For instance, the following code filters rows 1 through 7, returning only the ones with the name “Mumbai” in column D:

=filter(‘Form Responses 1′!A1:E7,’Form Responses 1’!D1:D7=”Mumbai”)

How to Save Google Form Responses to Different Sheets Easily

This formula may fail for automatic form submission if the number of rows exceeds your expectations.

Work Smart

So, you can separate Google Forms responses to different sheets easily. The master data will always be on the first sheet for your reference. The only limitation is that you can’t directly copy the content of a sheet using the ‘Copy to’ function. In this case, Sheets copies the formula instead of the content, rendering the new spreadsheet useless. However, you can still manually copy and paste the content. Should you ditch Google Forms for Jot Forms? Read the following article to find out which service is right for you.

Leave a Comment