If your campus or district collects course registrations via Google Forms, you’ve probably run into this problem: one person selects multiple courses, and they all land in a single cell, comma-separated. That makes it nearly impossible to count enrollments per course, filter by participant, or track completions accurately. I had this problem today and decided to use Claude AI to help me figure it out.

Here’s how we solved it using Google Sheets and a short Apps Script, no advanced coding required (because if there had been, I would have run away screaming in the opposite direction!)

The Problem

A Google Form lets respondents pick multiple courses from a checkbox list. The result in Google Sheets looks like this:

Name  Email  Courses Selected

Miguel Guhlin    mguhlin@email.org    Schoology Educator*, Adobe Photoshop, AI Tools for Educators

One row. Three courses. Useless for enrollment tracking.

What we need is this:

Name  Email  Course

Miguel Guhlin    mguhlin@email.org    Schoology Educator

Miguel Guhlin    mguhlin@email.org    Adobe Photoshop

Miguel Guhlin    mguhlin@email.org    AI Tools for Educators

One row per course, all other data repeated. That’s the target.

The Solution: Apps Script

Google Sheets has a built-in scripting environment called Apps Script. You don’t need to install anything — it’s already there.

Here’s the script we used:

function expandRegistrations() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const regSheet = ss.getSheetByName(“Registrations”);

  const enrollSheet = ss.getSheetByName(“Enrollment”);

  const data = regSheet.getDataRange().getValues();

  const headers = data[0];

  const output = [headers];

  for (let i = 1; i < data.length; i++) {

    const row = data[i];

    const coursesRaw = row[6]; // Column G

    if (!coursesRaw) continue;

    const courses = String(coursesRaw).split(“,”)

      .map(c => c.replace(/\*.*$/, “”).trim())

      .filter(c => c.length > 0);

    for (const course of courses) {

      const newRow = […row];

      newRow[6] = course;

      output.push(newRow);

    }

  }

  enrollSheet.clearContents();

  enrollSheet.getRange(1, 1, output.length, output[0].length)

    .setValues(output);

}

What this script does:

• Reads every row from the Registrations sheet
• Splits the Courses column on commas
• Strips any annotation text (like “* <— Mandatory”) from course names
• Writes one new row per course into the Enrollment sheet
• Repeats all other columns (name, email, campus, etc.) on each row

How to Set It Up

1. In your Google Sheet, click Extensions > Apps Script
2. Delete any placeholder code and paste in the script above
3. Click Save, then click Run
4. Authorize the script when prompted (it needs permission to read/write your sheet)
5. Switch to your Enrollment sheet — it should now show one row per course

Automating It: The Form Submit Trigger

Running the script manually works, but we want it to fire automatically every time someone submits the registration form. Here’s how to add that trigger:

First, add this function to your Apps Script:

function onFormSubmit(e) {

  expandRegistrations();

}

Then set up the trigger:

6. In Apps Script, click the clock icon (Triggers) in the left sidebar
7. Click + Add Trigger in the bottom right
8. Set Function to “onFormSubmit”
9. Set Event source to “From spreadsheet”
10. Set Event type to “On form submit”
11. Click Save and re-authorize if prompted

Now every new form submission will automatically expand into individual enrollment rows — no manual intervention needed.

One Important Note About Permissions

The trigger runs under the Google account of whoever created it — not whoever is viewing the sheet. That means it works for everyone: staff, admins, anyone who submits the form. The only time it stops working is if the account that created the trigger loses access to the spreadsheet or needs to re-authorize. If that happens, just open Apps Script and re-run the authorization flow.

Why This Matters

This setup turns a messy multi-select form response into clean, queryable enrollment data. Once each course is its own row, you can:

• Use COUNTIF to tally enrollments per course
• Filter and sort by campus, job title, or course name
• Feed the data into dashboards or reports without manual cleanup
• Track completion status per person per course

It’s a small script with a big return on investment — and once the trigger is in place, the whole thing runs itself.