Teacher Tech blog with Alice Keeler

Paperless Is Not a Pedagogy

Alice Keeler

Multiple Google Forms to One Spreadsheet

Multiple Google Forms to One Spreadsheet

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

Learn More about Google Forms from Teacher Tech

Multiple Forms One Spreadsheet

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…”
Google Forms 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.
Google Forms always create a new spreadsheet

 

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.
Add Sheet

 

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

  1. 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.

  2. 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:-)

  3. 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?

    1. 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.

  4. 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?

      1. 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.”

        1. 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.

  5. 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?

      1. 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!!

  6. 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?

  7. 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?

  8. 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?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

© 2024 All Rights Reserved.

💥 FREE OTIS WORKSHOP

Join Alice Keeler, Thursday Mar 21st or register to gain access to the recording.
Create a free OTIS account.

Join Alice Keeler for this session as we learn how to revolutionize your assessment strategies. We will dive into the essentials of crafting high-quality rubrics that go beyond traditional scoring methods to offer rich, meaningful feedback. We’ll learn about the components of an effective rubric and how to mathematically ensure the accuracy of your assessments.

Exit this pop up by pressing escape or clicking anywhere off the pop up.