Google is always making changes to their products. This blog posts reflects an older version of Google Forms. While some of the ideas may be relevant the screenshots are definitely outdated.
Multiple Forms to One Google Sheet
The magic of Google Forms is the ability to view all of your Form data in a spreadsheet. I use Google Forms a considerable amount in my classroom. Google Forms basically allows me to be paperless. Any data I need to collect, including student work, I utilize a Google Form. This means that I have multiple spreadsheets that I need to access when I need data. It would be nice if I could have all of my Google Forms data in one spreadsheet.
Import Range
To have multiple Google Forms populate to one spreadsheet you can use the =importrange() function. What this will do is allow you to mirror the data from one spreadsheet to another. This is more than just copying the data, it will reflect the current data in another spreadsheet. When changes are made, the mirrored data will update in the other spreadsheet.
Create a Form
When you create a Form it should be automatically connected to a spreadsheet. If you have never done this, you will need to set your Form to always create a spreadsheet. This is a one time action and highly advised.
Using the “Responses” menu select “Choose response destination…”
By default the check box for “Always create a new spreadsheet” is selected. Click create to connect the Form to a spreadsheet. If you have done this previously, you do not need to do it on subsequent Forms.
Master Spreadsheet
You will want to have a master spreadsheet that displays the data from multiple Google Forms. This could be the spreadsheet created by one of the Google Forms or you can utilize a blank spreadsheet.
Create Tabs
In your master spreadsheet create a sheet for each Forms data you want to import. To create a new sheet click on the plus icon in the bottom left of the spreadsheet. You can double click on the tab to rename it.
Copy URL
In the OTHER spreadsheets that contain Form data copy the URL.
Import Range
Back in the master spreadsheet locate the tab you intend to display the other Forms spreadsheet data. In cell A1 type the formula =importrange(“URL”,”Range”). You will need to replace the URL with the URL from the other spreadsheet.
Identify Tab Name
On the other spreadsheet you will need to know the name of the tab. It is probably “Form Responses 1” unless you have changed the name. You will need to utilize this tab name in the range of the importrange formula.
Range!
In the importrange formula you will need to have the range of data you are drawing from in quotations. The format is the name of the tab and then an exclamation mark and the range of the data. For example: “Form Responses 1!A1:D36”
Example
On the master spreadsheet on the tab you wish to import data to your formula should look something like this:
=importrange(“https://docs.google.com/spreadsheets/d/1KRKR-pxt1HTEEXeLwPTgGP4gt69nggcCV6TIwUJfC34/edit#gid=540243635″,”Form Responses 1!A1:D”)
By omitting the the row number in the last column you wish to import then all rows will be imported. This is important if your Form continues to accept data. In other words have your range of imported data look like A1:D instead of A1:D36.
Repeat
Repeat this process for each of the Google Forms data you wish to compile in the same master spreadsheet.
21 thoughts on “Multiple Google Forms to One Spreadsheet”
I think you placed : instead of ! in your example? or I am just confused??
This is a great idea that I use myself. It’s important to point out that the data in the master sheet is VIEW ONLY. You still need to edit the data in the original sheets.
Very good point! You can still run pivot tables and other reports off of the imported data.
This can be done more easily!
In a form you can disconnect from the present spreadsheet and connect to another spreadsheet.
This way you can collect the results from moe forms into one spreadsheet!
I have tested this of course and it works as a charm:-)
I’ve not tried it, but in the third picture it looks like you could just set the destination of the responses spreadsheet to be a new tab in an existing spreadsheet i.e. the master spreadsheet?
You can not specify the tab.
If you choose new tab in an existing spreadsheet, it will create a new tab. You can then name it whatever you want. Works in a similar fashion to the importrange formula.
I’m having trouble getting this to work. I get an error message when I input the link and range. It says “formula parse error.” Do you know how to fix that?
It can be fussy sometimes. Is your original spreadsheet private? If so, you have to give yourself permission.
HI Alice we are struggling too – does this have to be on a Google classroom setup or can you do it on any gmail account? We are also getting the “formula parse error.”
You have to have viewing permissions from the other doc to make that work. You can also connect multiple sheets to one spreadsheet. That is a new features in the new Google Sheets.
I have a form that I ask students to fill out monthly. The form does not change each month so how do I separate the September data from October and November etc. when it is collected in the Google sheet? Do I need to make separate tabs or do I need to make a new form for each month?
Ask a question about which month so that the data can be filtered.
Oh I already did that! Great! So I guess all the data will still go into the same sheet but I’ll just have to look at the column for month to see which month right? Thanks!!
Can you run Flubaroo on the Master Sheet?
Sure you can
Once multiple forms are linked to one sheet using the importrange function, how do I get the Summary of Responses to show updated charts from the compiled data, as opposed to the first set of data on the sheet?
You have to write your own formulas
Alice – How do I get two spreadsheets to automatically dump in to the master spreadsheet? The steps above work well for one url, but what about when there are two urls?
You can use an =importrange() possibly
When I combine the spreadsheets, will it group the data about each student together. For example if in all of the spreadsheets, the first column is the students’ names, can I have it ‘match’ the names from additional spreadsheets and ensure that data it’s bringing into new columns goes in the correct row for that student?