How to Save Google Form Responses to Different Sheets Easily

Namrata Gogoi is an author covering Buying Guides and device-related tips and tricks. She loves learning and writing about gadgets and accessories that are functional, innovative, and have a positive impact on people's lives. Since 2016, she has covered smartphones, computer accessories, household electric appliances, and any product that helps bring convenience to one's life. Previously, she worked as an IT Analyst at TCS but found her calling at Guiding Tech. Learn about our Tech Review Board

Ashish, Computer Engineer by profession, has been writing about tech for 16 years, with a primary focus on Windows and Smartphones. He has a keen interest in writing about social media, as it constantly evolves, introducing new features to explore. Learn about our Tech Review Board

• Use the Query formula to filter out data from the primary sheet to multiple sheets.
• Where and Like clause allows you to filter comments further, but they do an exact match.
• Sheets only understands the yyyy-mm-dd format for dates.

How to use Query Function to Extract data from any sheet

This method uses Google Sheets’ Query function. For those unaware, this function uses simple SQL-like queries to sort through the table data according to your preference.

You can experiment with a lot, from mathematical formulas like Sum and Avg to clauses like Contains and Like. And, of course, you can select the data range where you want these conditions to be met and then save Google Form responses to different sheets.

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

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

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

Following are some of the conditions that need to be met,

• The query must either be enclosed in quotation marks.
• If not, the query must contain a reference to the cell values.
• The column values must be either Boolean, String, or Numeric.

This method requires an initial setup based on your form’s values. Once satisfied with the testing’s initial results, you can share the form with the public.

How To Use Google Sheets’ Query Function to Save Google Form Responses to Different Sheets

Step 1: Open Google Forms and create your form. We have created a form with multiple-choice questions (MCQ) because we want to separate the data by city name.

Step 2: Once you’ve completed this, go to the Responses tab and click on the three-dot menu beside the tiny Sheets icon.

Step 3: Select the response destination from the menu and click Create a new spreadsheet. Give the sheet an easy-to-remember name.

Once the link to the said spreadsheet has been established, open it via Google Sheets. You’ll notice that the first sheet’s name has changed to Form Responses 1.

Step 4: Now, open a second sheet and paste the following formula on the first cell,

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

As you can see, the sheet name goes first, followed by the cell range and your conditional query. After changing the condition in the query, repeat the process for all the other tabs.

In our case, the third and fourth sheets looked like this:

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

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

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

Tweaking the Query Further

The good thing is that the sheet handles naming the headers; you don’t have to worry about them.

You can also tweak the query further to suit the conditions of your form. For example, if you want to match a respondent’s answer to a string value, the ‘Where’ clause will replace the Contains or Like clause. And if you are 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'")

Also, 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 an exact match. So, even if someone has answered ‘goood’ in the response, the above query won’t pick it up.

Similarly, you can also play around with dates.

Note:

Sheets only understands the yyyy-mm-dd date format, so you might have to set the column format initially for better 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 more than one condition on your sheets. All you have to remember is to separate the two condition with either ‘Or’ or ‘And’.