How To Create A Survey In Google Sheets

Google's G Suite is one of the most powerful productivity tools with a range of impressive apps. Google Forms is one of the most useful (and perhaps most overlooked) apps on the platform, especially if you are using Google Sheets.

When people think of Google Forms, they often think of surveys, pop quizzes, feedback forms, or even event registrations. Although this app is a fantastic tool for these purposes, Google Forms are also an extremely intelligent tool for gathering any kind of data.

Google Forms can streamline and optimize a number of tasks within your business operations. For internal purposes, you can use forms to collect supply requests, job applications, or vacation requests. For customers and clients, you can use forms for specific orders and checkouts.

Using Google Forms, you can collect a large amount of data which you can then connect to Google Sheets to manage and analyze. Let's take a look at how to connect Google Forms to Google Sheets, and how you can make the most of these tools when combined.

Why use Google Forms?

There are many benefits of using Google Forms – here are just a few:

Accurate data: if people type information directly in a form themselves, they are more likely to get it right (e.g. dates, spellings, phone numbers).
No need to copy-paste data: you can connect your Google Forms to Google Sheets.
Data integrity: Google Forms are a safer way to collect data than allowing multiple people to enter information in a spreadsheet.
User-friendly interface: it's easy to use by anybody, including inexperienced users.
Customizable design: you can add logos, photos, and videos.
Sharing functions: it has all the same sharing functions as Google Sheets and Docs.

If you need help designing your Google Form, check out this step-by-step video tutorial.

Link Google Forms to Google Sheets

You can use Google Forms as a standalone application; simply click on the responses tab to read all the responses directly. However, if you're collecting a lot of data from a lot of people, you should put that data in a spreadsheet, where it's ready to process and analyze.

Manually transferring data from Google Forms to Google Sheets is too time-consuming. Instead, you can connect Google Forms to Google Sheets to transfer the data automatically. This also means that when a respondent submits a form, the data simultaneously appears within the spreadsheet, too.

Here are some important things to note:

If you save your responses in Google Sheets, they will remain there even if you delete the data from the form.
If you change the fields within the form, these will automatically update in the sheet too. However, once a form has been submitted, any data within it will not change within the spreadsheet.

How to send form responses to a spreadsheet

Google Forms has a setting that allows you to send your form responses to Google Sheets. This can be a new or existing spreadsheet.

In your chosen form, click on the responses tab.
Click on the three dots button located next to the Google Sheets icon. Click on Select responses destination from the drop.
Here, you can select where your responses will be. You can either choose to Create a new spreadsheet or Select existing spreadsheet.
To test whether Google Forms has successfully integrated with your spreadsheet, you can click the Google Sheets icon which will immediately take you to your spreadsheet where the form responses are located.

google forms to google sheets

How to filter responses from Google Forms to Google Sheets

In order to make the most of the Google Forms and Google Sheets integration, it's important to know how to automatically sort, filter, and analyze your form responses in your spreadsheet.

Let's say you want to send form responses to different sheets based on the answer to a multiple-choice question. For this example, we want to filter the data into different spreadsheet tabs based on whatever color the respondent chooses: blue, red, green, or yellow.

You can use different formulas within Google Sheets to sort and filter your Google Forms responses successfully.

Use Query

The Query function uses SQL language to select specific data. In order to create a new tab for each color, simply follow this step by step:

Inside your form, go to the Responses tab and click on the three dots. Click Select response destination > Create a new spreadsheet.
Enter this new spreadsheet. Open a new tab. This will be the new location for one of the specific answers, in this case, Blue.
In the A1 cell of this new sheet, enter the following formula:

=QUERY('Form Responses 1'!A:B,"Select* Where B='Blue'",1)

The following are the variables in this formula that you can adjust to your own needs:

'Form Responses 1': change to the name of your general responses tab on spreadsheets
A:B: change to the cell range of data in your general responses tab
"Select" Where B =: change to the column of cells with your multiple-choice answers
'Blue': change to the name of the answer you want to filter

google forms to google sheets 1

Repeat this for the other tabs, with the other colors.

Use Filter

Alternatively, you can use the filter formula in Google Sheets to separate your form responses. Simply create a new tab and enter the following formula to the A1 cell:

=FILTER('Form Responses 1′!A1:B10,'Form Responses 1'!B1:B10="Blue")

Again, you can change the variables in this formula to suit your own needs.

google forms to google sheets 2

How to merge responses from different forms into one tab in Google Sheets

When you send data from multiple Google Forms into Google Sheets, it saves responses to each form in a separate tab. But what if you want to combine the responses of two forms together in one tab?

Let's say you're a sales manager overseeing a team of 10 sales reps. You create 10 different forms with the same questions to make sure each representative inputs their data correctly. Once your reps submit their forms, the results will enter your spreadsheet as 10 different tabs. But you want the results in one tab, ready to analyze the data.

Sheetgo's no-code automaton tool allows you to merge multiple tabs in one tab in just a few clicks of a button.

Here's how to do it

Install the Sheetgo add-on for Google Sheets.
Open the spreadsheet where you want your merged responses to be. In this example, we want to create our merged tab in the same file as the individual response tabs. Click on the add-ons menu > Sheetgo > Start.

google forms to google sheets 3 Sheetgo add on