Harness the power of Google Forms
G Suite is a powerful set of online productivity tools and Google Forms is one of the most useful (and often overlooked) apps in the platform, especially if you’re working with Google Sheets.
You probably think of Google Forms as a survey app for polls, quizzes, event registration, feedback and so on — and it does that brilliantly. But Google Forms are indispensible for gathering any kind of data.
For internal company purposes, you can use forms for collecting supply requests, job applications, IT or vacation requests. For customers and clients, you can use forms for orders and checkout.
If you want to gather any kind of information and put it in a spreadsheet, you should definitely consider using a form. Google Forms and Google Sheets are perfect partners.
Why use Google Forms?
- 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.
- Safer than allowing multiple people to enter information in a spreadsheet.
- User-friendly interface.
- Customizable design — add logos, photos and videos.
- 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
Google Forms is a standalone product and you can view all responses directly in the app: just go to the Responses tab to read them. But if you’re collecting information from a lot of people, you need to put that data into a spreadsheet, where it’s ready to process and analyze.
You can do this automatically, by connecting your Google Forms to Google Sheets. This is far quicker than manually transferring and consolidating information. Whenever a respondent submits a form, the data will automatically appear in your spreadsheet.
Some things to note:
If you save your reponses in Google Sheets, they will remain there even if they are deleted from the form.
If you change the fields in the form, this will be reflected in the sheet. (But note that once a response is submitted in the form, it appears immediately in the sheet and will not be changed).
How to send form responses to a spreadsheet
Inside the Google Form, change the settings to have the form responses sent to a Google Sheet. This can be a new or existing spreadsheet.
Go to Responses. Click on the 3 dots next to the Google Sheets icon. Click Select response destination, then choose between Create a new spreadsheet or Select existing spreadsheet.
How to filter responses from Google Forms into Google Sheets
Manually sorting and analyzing responses takes time, so you can automate this process by using formulas in Google Sheets.
Here we’ll show you how to send form responses to different sheets, based on the answer to a multiple-choice question.
In this example, respondents are asked to select a color. You want to filter the data into different tabs (sheets) based on which color they select:
Query function uses SQL language to select specific data.
- Inside your form, go to the Responses tab and click on the three dots. Click Select response destination > Create a new spreadsheet.
- Open the spreadsheet. Open a second tab(sheet) and enter this formula into the first cell A1:
- =query(‘Form Responses 1’!A:E,”Select * Where D=’Blue'”, 1)
- Repeat this for the other tabs, with the other colors.
You can also use Google Sheets’ filter function to separate your Form responses.
For example, =filter(‘Form Responses 1′!A1:E7,’Form Responses 1’!D1:D7=”Blue”)
How to merge responses from different forms into one tab (sheet)
If you send data from multiple Google Forms to one Google Sheet, the responses are automatically saved in separate tabs (sheets).
What if you want to consolidate the responses from different forms into 1 tab (sheet) ?
Imagine that you’re a sales manager, overseeing a team of 10 sales reps. Your staff use Google Forms to submit their results. You don’t want to use one standardized form for everyone because of the risk of errors (such as the rep selecting the wrong name from the dropdown menu) so you use a different form for each member of the team. The results flow into your spreadsheet in 10 different tabs, but that’s not practical for analysis — you want all the data in one tab, ready for processing and analysis.
You can merge multiple tabs into one tab using Sheetgo, the no-code automation tool for spreadsheets.
Here’s how to do it:
- Install the Sheetgo add-on for Google Sheets.
- From inside your destination/master sheet (containing your Google Forms responses) go to menu > Add-ons > Sheetgo > Start.
- Sheetgo opens on the right-hand side of your screen. Hover over the green + button and click Export.
- Choose your source spreadsheet from Google Drive (in this case, it’s the spreadsheet you are already working in!)
- Select your source tab (containing the responses from one of your forms).
- Select your destination spreadsheet. This could be the spreadsheet that you are currently working in (if you want to consolidate multiple tabs into one tab in this same spreadsheet). Or, if you want to merge all the data into a tab in another spreadsheet, just select that spreadsheet and tab as your destination.
- Under Settings, change the connection name and under Frequency, select how often you want it updating.
- Click Save connection.
You’ve created a consolidate connection with Sheetgo. You will see a new tab appear in your spreadsheet — the data will now flow automatically from your Google Forms into your Sheet, and then into your destination 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 Sheet to another, and start transferring data between them automatically.