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.
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.
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
Repeat this for the other tabs, with the other colors.
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.
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.
3. The Sheetgo tool should appear on the right-hand side of your screen. Under the Import data section, press Select data.
4. As you want to merge multiple tabs, select Multiple files from the Select source data section.
5. Input the file and tab names for each form response.
6. Under the Destination section, create a new tab for your consolidated data. Click Finish and save.
7. Your form responses should now be under one tab.
That’s how to link Google Forms to Google Sheets
Want to do more with your form response data once it’s in Google Sheets? A Google Form is an ideal input source for an entirely automated workflow.
Read our post on How to connect one Google Sheets file to another to transfer data between them automatically.
Editor’s note: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.