When students complete a Google Form to choose a group, how do you put students into groups? And how do you communicate that to students?

Every December, I create 17 (or so) groups for 190 middle school students and 13 more groups for 140 lower school students. Here are my workflows using Google Sheets. I’ll walk you through how I go from getting every students’ top three choices to handing out tickets for the event.

Why should students code?First things first, be sure you’ve read my post on why every student at Hillbrook spends time coding in a schoolwide event. That’s the why with a few details; this post focuses on the how, particularly in Google Sheets as I organize all the things.

Step 1: SignupsSince middle school and lower school students participate in Everyone Can Code on different days, there are different forms. Check out the MS form and LS form to see how they picked their top 3 choices.

This would be a good time to mention that I make a five minute video for LS students and embed it in the signup form. Keep in mind that younger students need extra support in reading, and extra info since some of them have NO IDEA what these robots and websites are.

Step 2: OrganizeThis makes way more sense if you’re following along with the workflow. Here’s a copy of the Google Sheet with fake student names. Open it in another tab and keep clicking back and forth as you read.

Google Forms stores entries on a spreadsheet. That Google Sheet has one sheet titled Form Responses. I never edit this data. My firs step is to create a new sheet (plus button at bottom) and customize the headings. The header is the question you ask and “Which session do you want to go to?” isn’t super helpful. So on this new sheet, creatively titled Query, I type new headers in row 1. The cells are simple so far: First Name, Class, Query 1st, Query 2nd, and Query 3rd.

Then, in cell A2, I type =QUERY(‘Form Responses 1’!B2:F160). That pulls the info from that original sheet; every time a student completes the form, it will appear here. We’re about to add some columns, and things get goofy quickly when a form is being completed and you add columns. So, =QUERY and then…

I added a few more columns. Columns like Query 1 are super useful, but they’re filled with way too many words like “Build a Computer with Piper: physical computing on a Raspberry Pi.” I just want it to say Piper. To do that, I created a few more columns: 1st choice, 2nd choice, and 3rd choice. Buckle up, cuz here’s where the formula gets insane. The goal of this is to basically do an automatic Find & Replace for each choice. Note: if you copy and paste this, smart quotes might break the formula. If it doesn’t work for you, either delete the quotes and manually type them or just manually type the whole darn thing. Here’s the code from cell F2:

*=IFS( (arrayformula(iferror(search("Jawas",C2)))),"Star Wars", (arrayformula(iferror(search("aRT",C2)))),"Sphero Art", (arrayformula(iferror(search("Hackathon",C2)))),"Hackathon", (arrayformula(iferror(search("code a dance party",C2)))),"Dance Party", (arrayformula(iferror(search("LittleBits",C2)))),"LittleBits", (arrayformula(iferror(search("An Unusual Discovery: Make a Cartoon w/ Code",C2)))),"Unusual Discovery", (arrayformula(iferror(search("Build a Computer with Piper: physical computing on a Raspberry Pi",C2)))),"Piper", (arrayformula(iferror(search("Light-up Holiday Card with Paper Circuitry",C2)))),"Paper Circuitry", (arrayformula(iferror(search("Osmo Coding Kit",C2)))),"Osmo", (arrayformula(iferror(search("Bloxels",C2)))),"Bloxels", (arrayformula(iferror(search("Dash-ketball",C2)))),"Dash-ketball", (arrayformula(iferror(search("Superhero",C2)))),"Superhero", (arrayformula(iferror(search("SonicPi",C2)))),"SonicPi", (arrayformula(iferror(search("Javascript 101",C2)))),"Javascript 101", (arrayformula(iferror(search("Sphero Maze",C2)))),"Sphero Maze", (arrayformula(iferror(search("Sphero Goes Green",C2)))),"Sphero Goes Green", (arrayformula(iferror(search("AI for Oceans",C2)))),"AI for Oceans", (arrayformula(iferror(search("Rock, Paper",C2)))),"Swift Playgrounds", (arrayformula(iferror(search("Chrome Music Labs",C2)))),"Chrome Music Labs", (arrayformula(iferror(search("Makey",C2)))),"MakeyMakey Poetry" )* From there, drag the bottom right corner to copy down the formula. In my case, I dragged it from F2 to F137, and also from F2 to H2, and then down to H137. After this super geeky formula, I finally have easy-to-read titles, automatically generated.

I wanted an even easier way to quickly identify what session a student requested. Enter… Conditional Formatting. I created 13 formats for cell background and text color so that I could quickly see who signed up for what.

At this point, I created another column–Grade level–and did the same IFS formula magic and Conditional Formatting craziness. Here’s what that looked like, starting in cell K2:

=IFS(
(arrayformula(iferror(search(“4″,B2)))),”4th grade”,
(arrayformula(iferror(search(“3″,B2)))),”3rd grade”,
(arrayformula(iferror(search(“2″,B2)))),”2nd grade”,
(arrayformula(iferror(search(“1″,B2)))),”1st grade”)

Easier to understand, right? Based on their answer in column B, I could automatically have an easy-to-read grade level column. Again, because this is on a new sheet I named Query, every time a student fills out the form, this info gets added to it. That’s rad.

I also added Conditional Formatting to column K, my grade level column. This time, it’s much simpler, with 4 shades of grey. The reasoning behind this is that I want to instantly see the age of students when I start creating mixed-age groupings. All of this so far is students sharing their top three picks (out of 13). The next step is for me to create groups and put students from grades 1-4 (or grades 5-8 for MS) into groups for their first session (1-2pm in our case) and their second session (2-3pm).

This new sheet, Query, is awesome, but there’s on big drawback–you can’t sort it. So…

Create a new sheet. I named mine “Work here (won’t pull query)“. Copy the entire sheet named Query and head over to the new sheet and click Edit > Paste Special > Paste Values Only. This no longer updates automatically, but it lets you sort, and that’s a super big deal for the next big step.

Step 3: Create Student GroupsOn this new sheet named Work here (won’t pull query), pull down row 1 so that it stays frozen when you sort. Next, go to column F, 1st Choice, grab the drop down triangle, and Sort Sheet A->Z. Your color coding should make it super obvious which sessions are popular for their first choice. You should have a max occupancy in mind for student groups; any time a group is less than max in the 1st choice column, copy and paste those students into the official group… the new fancy column… the one I called Session 1. These students now have a session to attend!

Since the sheet is now sorted by first preference, take a moment and look at grade level. Is the group heterogeneous, or did all the fourth graders sign up for it? Try to get as many students as possible into their first choice without overthinking things or worrying about overbooking a session. Because it’s time for…

Pivot tables! Select the entire page, go to Data > Pivot Table, and select Create New Sheet. Under Row, Click Add, and select Session 1 (check the box Show Totals). Then under Values, Click Add, and select Session 1 (ensure Summarize by CountA is selected). Be nice to yourself and give this sheet a helpful name like Count session 1. This pivot table gives you a real-time count of how many students are in each group. Rad.

Let’s do the same for Session 2… go back to your sheet named Work here (won’t pull query). Select the entire thing and create a Pivot Table. Same as before… Create New Sheet, Row > Add > Session 2, and Values > Add > Session 2. Name this Count Session 2. You should now have two more sheets. This is nice, but can be nicer. QUERY will be our friend again.

Create a new sheet. Name it something like Counts. Manually type the headings: Title, Session 1, and Session 2. Then in cell A2, type =QUERY(‘Count session 1’!A3:B14). If your sheet has a different name, don’t copy and paste this. That query grabs the names of each session and the count of session 1. You’ll need the count of session 2. For that, type =QUERY(‘Count session 2’!B3:B14). For good measure, add some Conditional Formatting to it–when a count goes above your max, make it red. If it’s in the sweet spot (max or a couple below), make it green. If it’s under, make it yellow. Now it’s just a matter of filling all groups to capacity and attempting to give students their top choices. It’s tricky, but between the color coding, easy-to-read session names, and real-time counts on your two sessions, it’s certainly doable.

Step 4: Communicate Sessions to StudentsYou know where every student should go, but nobody else knows. It’s time to let teachers and students know where to go, and what sessions they’ll be attending.

Middle school student workflow: FormMule if students have email, let them know their two sessions by email. FormMule

Lower school student workflow: if students don’t have email, print tickets for them. Autocrat and Google Slides