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.
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.
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.
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.
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.
In the OTHER spreadsheets that contain Form data copy the URL.
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.
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”
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 this process for each of the Google Forms data you wish to compile in the same master spreadsheet.