Wouldn’t it be easier if you could save each Google Form response to different sheets automatically based on the answer? Well, it’s almost 2020, and it would be a bummer if you have to do all the sorting and analyzing manually. Thanks to Google Sheets’ easy formulas and function, it’s a simple task to save all the Forms responses to individual sheets. All you need to do is figure out how would you like to have the responses separated. Once you’ve figured out the conditions (single condition or multiple conditions), the next steps are quite a breeze. Let’s get started.
Method 1: Using Query
This method uses the Query function of Google Sheets. For those unaware, this function uses simple SQL-like queries to sort through the table data as per your preference. From mathematical formulas like Sum and Avg to using clauses like Contains and Like, you can experiment with a lot. And of course, you can select the data range where you want these conditions to be met. Once done, you can put the responses on different sheets. So, if we have to to find the average of column A, the query will look something like, 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 just requires an initial setup based on your form’s values. Once you are satisfied with the initial results of your testing, you can then share the form with the public.
How to Use Sheets’ Query Function to Save Google Form Responses
Step 1: Open Google Forms and create your form. In our case, we have created a form with multiple-choice questions (MCQ) as we want to separate the data as per the city name. Once done, head over to the Responses tab and click on the three-dot menu beside the tiny Sheets icon. Pick Select response destination from the menu and click on 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. At this point, you’ll notice that the first sheet’s name has changed to Form Responses 1. Step 2: Now, open a second sheet, and paste the following formula on the first cell, As you can see, the sheet name goes first, then the cell range, and then your conditional query. Having changed the condition in the query, repeat the same for all the other tabs. In our case, the third and fourth sheet looked like, Step 4: Now, all you have to do is 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 and you don’t have to worry about them. You can also tweak the query further to suit your form’s conditions. For example, if you want to match a respondent’s answer to a string value, the ‘Where’ clause will be replaced by ‘Contains’ or ‘Like’ clause. And if you are comparing string values, it’s always wise to convert the string using the Upper function. Also, Google Sheet’s queries are not as advanced as SQL statements. For one, 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. Sheets only understands the ‘yyyy-mm-dd’ date format, so you might have to set the format of the column initially for better results. 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’.
Method 2: Filter
Another alternative function which you can use is Filter, for simple condition and are usable for getting specific results. For example, the following will filter through the Row 1 through row 7 when the value of column D1 through D2 has the name Mumbai. However, this formula might fail if you’re looking for automatic form submission where the number of rows might exceed your expectations.
Work Smart
So, that is how you can separate Google Forms responses to different sheets easily. The best thing is that the master data will always be on the first sheet for you to reference later. The only limitation is that you won’t be able to copy the content of a sheet via the ‘Copy to’ function directly. In this case, Sheets copies the formula instead of the content into the new spreadsheet, which in turn, renders the new spreadsheet useless. The good news is that you can still copy and paste the content manually. Next up: Should you ditch Google Forms for Jot Forms? Read the following article to find which service is built for you. The above article may contain affiliate links which help support Guiding Tech. However, it does not affect our editorial integrity. The content remains unbiased and authentic.
