Google Forms provides a convenient way to collect data. You can use it to take quizzes and surveys and collect feedback and registration information. However, its capabilities are not just limited to these.
When you connect Google Forms to Google Sheets, you can use the analytical powers of the latter to make powerful calculations from the gathered data.
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
Table of Contents
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. Linking a Google Form to Google Sheets alleviates this issue.
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 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 so many people handle the raw data, there is always the possibility of accidental deletions or unintentional 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 without touching the original datasheet.
Moreover, the data can be directly validated through the forms, ensuring all the data in the worksheet is in the correct format.
In addition, the form can 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 when you link a Google form to 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’s look at how to link Google forms to Google Sheets using both methods.
How to Send Form Responses to a Spreadsheet
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 a spreadsheet that already exists 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.
Once you have your responses, you can create a dashboard with them on Google sheets to tell a story.
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 form to spreadsheet responses created for each form response.
How to Unlink a Google Form from Google Sheets
If you want to unlink a form in Google Spreadsheets:
- Open a form in Google Forms.
- In the top left under “Responses,” click Summary.
- In the top right, click More
Unlink form . - Click Unlink.
Unlink form 
.How to Save or Delete a Google Form
You can delete individual responses or all the responses on a google form. Simply go to your google forms in summary and click the 3 dots. Select delete all responses if you want to delete all responses.
How to Remove Google Form Responses
If you want to delete individual responses:
- Go to summary under Responses
- Select Individual.
- Choose Previous Previous or Next Next to find the response you want to delete.
- Click Delete Delete and then OK.
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])
Here,
- 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:
FILTER(data, condition)
Here,
- 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”).
You can also use google sheets scripts to add these functions. If you’re new to Google Sheets Scripts, you can check out our beginner’s guide to Google Scripts to get familiar with the layout.
Frequently Asked Question
Can you link Google Forms and Google Sheets?
Yes, you can connect your Google form to Google Sheets, so that every time a user submits a form, the data gets automatically updated in the sheet.
Can Google Forms Pull Data From a Spreadsheet?
No, you can’t use a Google Form to pull data from a spreadsheet. But you can opened forms directly from Google Sheets by navigating to Tools > Create a new form.
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: 3 Easy Ways to Add Subscript and Superscript in Google Sheets
Wrapping Up the Google Forms Google Sheets Guide
In this tutorial, we discussed two ways to connect Google Forms to Google Sheets. We also discussed how you could filter the responses obtained from your user form so that a sheet displays only the details 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 speed up your workflow.
Want to become a pro in Google Forms and Sheets? Take our masterclass!
ACCESS GOOGLE FORMS & SHEETS MASTERCLASS
Related: How to Create a Survey in Google Forms [Step-by-Step Guide]
