data studio, education, teachers, tutorial

Automatically update Google Forms questions with Form Ranger

Introduction

A while ago I created a system for the teachers and counselors at our school to collect social-emotional learning data. I asked teachers to input information through a Google form that was connected to a spreadsheet. I then use the spreadsheet to connect to Google Data Studio to display the information.

As an international school, we have a very transient student body. This means that we constantly have new students coming into the school as well as families withdrawing from our school and moving to another country. Keeping our rosters up to date for teachers is very important.

The Google Form that I use for this project is set up in a way that allows teachers to quickly find their class roster. First, the teacher selects their grade level. Next, they select their name and then the student’s name. Finally, they report notes on the student’s strengths and areas of concern based on the CASEL standards.

What about new students?

In general, the form works great. But what about when a new student comes into the school? How can we make sure the form is updated so that teachers can see their new student roster? Originally, this was done manually. I was usually notified by the classroom teacher when they tried to fill out the form and noticed the student list was not updated. This was frustrating for teachers.

I knew there must be a way to automate this process. That’s when I did some searching and came across the add-on for Google Forms called Form Ranger. Form Ranger is an add-on by New Visions Cloudlab. According to the website, you can use it to “automatically populate your form question choices from spreadsheet data.”

The Process

First, you’ll need to have a way of gathering your roster information in a spreadsheet. I am able to use an API to pull data packages on a timed schedule from Veracross, our SIS (student information system).

I then set up a workflow in a spreadsheet so that I could get each class roster into its own column. You only need a few formulas to do this. Finally, I use the add-on, Form Ranger, to automatically update the form questions with the student roster.

Set up the spreadsheet

I put the information from the SIS into a tab called SIS Info. On another tab, I gathered the teacher names in column A. This can easily be done with the UNIQUE formula. Just type in =UNIQUE( and select the column where the teacher names are located. In my case they are in column F. Therefore, my formula is:
=UNIQUE(‘SIS Info’!F:F)

I then take the teacher names and use the TRANSPOSE formula to put each teacher into their own column. The teacher’s name becomes the column header. In my example, I have 14 teachers, so my range is from A2 to A15. I place this formula in cell C1.
=TRANSPOSE(A2:A15)

Next, we need to get the students with the correct teacher. This is done using the FILTER formula. In order to accomplish this, I place the following formula in row two just below the teacher’s name.

=FILTER(‘SIS Info’!$A:$A,’SIS Info’!$F:$F =C1)

This particular formula is for the teacher in column C. I put the $ sign in front of the range ($A:$A) so that when I copy the formula across to the other columns, my filter range will stay the same.

Now, with this setup, any updates to the SIS tab will automatically update the teacher rosters.

Connecting to the form

When you are in your Google Form, navigate to the add-ons by clicking on the More button (three vertical dots) next to the SEND button in the upper right corner. Search for Form Ranger and install it. Once it is installed, you can then start the add-on by clicking on Start.

A small popup window will appear in the lower right corner. From there, choose the question you would like to use with Form Ranger. This is the “Choose a student” question in my form. Select “Populate from range” and another window will open up. From here, you’ll locate the spreadsheet that has your roster that you set up in the previous step.

Choose the sheet name, then the column header. Give that select a name for the range and then you should be good to go. Repeat the process for each question you would like populated from the spreadsheet.

Finally, choose whether to update the questions every hour or on every form submission. I have a lot of teachers using the form at the same time. Choosing every form submission tended to slow down the loading time. I ended up setting it to refresh every hour.

Why do this?

Although it is a lot of work to get it set up, the beauty of using Form Ranger is that it automatically updates the questions, or in my case, class rosters, whenever the spreadsheet is updated. I no longer have to be on the lookout for new students coming in or students leaving the school.

Teachers are also happier because their student list is updated when they need them. They don’t have to ask someone to fix it and then wait. They can accomplish their tasks efficiently. Saving teachers time and frustration is the main reason why I take the time to set up systems like this.

Do you have other ways to set up systems for teachers or other people in your school? Please let me know. I’m always interested in learning new ways to be more efficient.

Chris

Chris Smith is an academic data analyst in the international education field. Throughout his 25 years in education, he noticed schools and educators collecting data but struggling with being overwhelmed or not knowing what to do with the data. So, he began coaching, training, and consulting schools and educators to learn how to discover what data they actually need and how they can turn it into information that leads to insights and informed decision-making.

All Posts