Google Forms provide a convenient way to collect data. You can use it to take quizzes and surveys, as well as collect feedback and registration information. However, its capabilities are not just limited to these.
When you combine the data collection capabilities ofGoogle Forms with the analytical powers of Google Sheets, you get a powerful tool that’s highly convenient and efficient.
In this tutorial, we will show you how to connect Google Forms to Sheets. We will show you how to do this both ways:
- Creating a Google Sheets datasheet and connecting it to your Google form
- Creating a Google Form from a Google Sheets worksheet
Why Connect Google Forms to Sheets?
Google Forms already comes with a Responses tab, where you can see a summary of the responses received. Although this is quick and convenient, it does not contain as much analytical functionality as you might need.
To get full control over the response data, a better option would be to connect the form to a data processing application like Google Sheets.
For example, say you’ve collected registration information from attendees of an event. If there are a lot of attendees, you can directly have the data transferred to a spreadsheet, from where you can further process and analyze the information, get insights, create visualizations, and also make important decisions based on complex analytics.
Moreover, every time someone submits the form, the data can get automatically updated in the worksheet, so that your worksheet is always up-to-date.
Alternatively, say you have a shared worksheet that allows multiple people to enter data. In such cases, since the raw data is handled by so many people there is always the possibility of accidental deletions or inadvertent changes being made to the original dataset when adding new data.
A safer option would be to connect the sheet to a Google form that lets users enter the same data, but without having to touch the original datasheet. Moreover, the data can be directly validated while entering through the forms, making sure all the data in the worksheet is in the correct format.
In addition to this, the form can be made to look attractive with pictures, company branding colors, logos, etc., and may be made more user-friendly, so that non-Google Sheets users can also easily and quickly enter the required data.
The most important advantage of combining Google Forms with Google Sheets is that your data will remain in the sheet even when you delete the Google Form.
How to Connect Google Forms to Google Sheets
There are two ways to convert responses from Google Forms into Google Sheets. You can either create a Google Sheets worksheet from a Google form, or create a Google form from an existing worksheet.
Let us take a look at both these methods.
How to Link Google Form Responses to Google Sheets
Once you have a Google form designed and ready, you can connect it to a Google Sheets worksheet, so that every time a form is submitted the data (along with a timestamp) directly gets entered into the appropriate columns of the worksheet.
You can either link the Google form to an existing sheet or create a new one.
To connect the form to a worksheet, follow the steps shown below:
Step 1: Design your Google form, let’s say it looks like this:
Step 2: Click on the Responses tab of the created form.
Step 3: Click on the Google Sheets icon or the ellipsis next to it (followed by Select response destination).
Step 4: You will be asked if you want to create a new worksheet or choose an existing one to connect to your form. If creating a new spreadsheet, type a name for the sheet in the input field next to ‘Create a new spreadsheet’.
Step 5: If using an existing spreadsheet, check the radio button next to ‘Select existing spreadsheet’. Then click Select and choose the spreadsheet you want to connect to the form.
Please note that even if you choose an existing spreadsheet, Google Forms is going to create a new tab in the worksheet with fields corresponding to those in your form.
To look at the newly created sheet at any point, simply click on the Google Sheets icon (under the Responses tab of your form).
Test out the Google Forms-Sheets integration by clicking on Preview, filling out the form, and submitting it. Your form should instantly send all your responses to the connected worksheet.
How to Create a Google Form from a Spreadsheet
You can also go the other way round. You can create a Google form from columns in a Google Sheets worksheet.
To connect a form to a worksheet, follow the steps shown below:
- Open a new or existing worksheet in Google Sheets.
- Navigate to Tools->Create a new form from the main menu.
This will create a Form Responses sheet in your workbook and open the blank connected Google form in a new browser tab.
You can now start adding questions to the form. You will notice that as you add new questions to the form, the connected worksheet will get automatically updated with a corresponding column header in real-time.
Note: Even if you are using an existing worksheet to create a form, Google Sheets will automatically create a new tab called Form Responses 1 when you connect it to a Google Form.
If you create multiple forms connected to the same Google Sheets workbook, there will be separate response sheets created for each form response.
How to Filter Data Before Sending to Google Sheets
You might want to filter your form responses so that only specific data go into your worksheet depending on a given input. For example, say you have a signup form and you have a multiple-choice question asking if the user wants to sign up to your mailing system.
Based on the user’s answer to this question, you might want to filter the form’s responses to be sent to the Form Response sheet only if the user chooses a ‘Yes’.
You can use either the QUERY or FILTER function to accomplish this. Let us look at each of the two methods.
Using the QUERY Function to Filter Form Responses
The QUERY function in Google Sheets lets you use SQL language commands on your Google Sheets data. The syntax for this function is as follows:
QUERY(data, query, [headers])
- data is the range of cells that you want to run the query on
- query is the query that you want to run on your data. It is usually specified as an SQL query within double-quotes.
- headers is an optional parameter. It specifies the number of header rows at the top of the data (not relevant in this case).
So, if you want to filter data in columns (say) A:E of a sheet, such that it displays only those rows where column E has the value “Yes”, your QUERY formula will be as follows:
=QUERY(A:E, “SELECT * WHERE E=’Yes’”)
So let’s assume your form response that corresponds to whether the user wants to sign up or not is stored in column E of the ‘Form Responses 1’ sheet, as shown below:
To filter the sheet and display only those rows where Column E has a “Yes” value, follow the steps shown below:
Step 1: Open the sheet connected to your form.
Step 2: Press the ‘+’ sign next to the Form Responses 1 tab to create a new sheet in the same workbook.
Step 3: In cell A1 of this sheet, enter the following formula: =QUERY(‘Form Responses 1’!A:E, “SELECT * WHERE E=’Yes'”,1).
Step 4: Press the Return Key.
Once you’ve entered this formula into your response sheet, your form data should automatically get filtered in this new sheet to display only those entries where the user has opted to sign up (where column E has the value “Yes”).
Using the FILTER Function to Filter Form Responses
Alternatively, you can use the FILTER function to filter your response data. The syntax for this function is as follows:
- data is the range of cells that you want to filter
- condition is the condition you want satisfied for a row to be included in the result
So, if you want to filter data in columns (say) A:E of a sheet ‘Form Responses 1’, such that it displays only those rows where column E has the value “Yes”, your FILTER formula will be as follows:
=FILTER('Form Responses 1'!A1:E4,'Form Responses 1'!E1:E4="Yes")
Once you’ve entered this formula into cell A1 of your response sheet, your form data should automatically get filtered to display only those entries where the user has opted to sign up (where column E has the value “Yes”).
Can you link Google Forms to Google Sheets?
Yes, you can connect your Google form to a Google Sheets worksheet, so that every time a user submits a form, the data gets automatically updated in the sheet.
How do I insert a form into Google Sheets?
To insert a form into Google sheets, simply navigate to Tools->Create new form from the main menu.
Can Google Forms pull data from a spreadsheet?
No, Google forms cannot pull data from a spreadsheet.
Can you link multiple Google Forms to one spreadsheet?
Every time you connect a form to a Google Sheets workbook, a new worksheet is created to store the form response data. It is not possible to link multiple forms to a single spreadsheet by default, however, there are numerous add-ons available that can let you do that.
How do I filter responses from Google Forms to Google Sheets?
To filter responses from Google Forms to Google Sheets, you can use the QUERY or FILTER functions in the sheet to filter out rows that match a given criterion.
Related Reading: Top 11 Best Google Sheets Add-Ons
In this tutorial, we discussed two ways to connect Google Forms to Sheets. We also discussed how you can filter the responses obtained from your user form so that a sheet displays only those details that you need. Integrating Google Forms with Google sheets takes a small amount of time to put together, but once the connection has been set up, it can incredibly speed up your workflow.