Showing posts with label Google Apps Script. Show all posts
Showing posts with label Google Apps Script. Show all posts

Wednesday, November 18, 2026

Automatically Sort Google Form Responses by Class and Student Name every time a Google Form is submitted

A free Google Sheets automation for teachers

I give all my tests in Google Forms, and most of my tests have 4-7 different Google forms to chunk content. This means that while I'm giving a test, it's a lot of work to see who has finished each section. I am always switching between Google Sheets using the Sort range function to see who has finished a that section of the test, and who still needs to finish. Yesterday I was wishing that it would sort itself as answers were submitted, and it turns out you can use an Apps Script for that!  Disclaimer: As I've said before, I don't know how to write scripts, so ChatGPT wrote this for me and I tested it before trying it on a live Google Sheet.

This free Google Apps Script automatically sorts your response spreadsheet by class section, last name, and first name whenever a student submits a form.

You can also sort responses manually whenever you want by using the custom "Student Sorting" menu.

Step 1: Create your Google Form

  1. Open Google Forms and create a new form.

  2. Add these three required questions:

  3. Add your assignment, quiz, or survey questions.

  • Last name — Short answer
  • First name — Short answer
  • Class section — Dropdown
  1. If appropriate, enable email collection under Settings.

  2. For a graded quiz, enable Make this a quiz under Settings and configure your answer keys.

Use the exact question titles shown above so the script can recognize them.

Step 2: Connect the form to your spreadsheet

  1. Open your Google Form.

  2. Select the Responses tab.

  3. Click the Google Sheets icon or select More (⋮) → Select destination for responses.

  4. Choose Select existing spreadsheet.

  5. Locate your copy of the Auto-Sorting Spreadsheet template (you can create your own copy here).

  6. Click Select.

Google Forms will create a new response tab in the selected spreadsheet. This is normal! The form does not automatically write into a preexisting worksheet just because that worksheet contains matching column headings.

Important: Use the new response tab created by Google Forms. The sorting script identifies columns by their headings, so their physical positions do not matter.

Step 3: Install the sorting script

  1. Open your linked response spreadsheet.

  2. Select Extensions → Apps Script.

  3. Replace any starter code with the script provided below.

  4. Click Save.

Google Apps Script — Auto-Sort Form Responses
/**
 * Google Forms Auto-Sorting Script
 *
 * Sorts the original Google Forms response tab by:
 * 1. Class section (A-Z), if present
 * 2. Last name (A-Z)
 * 3. First name (A-Z)
 *
 * All response data stays together.
 */

// Leave blank to sort the submitted/active sheet.
const RESPONSE_SHEET_NAME = "";

const HEADERS = {
  classSection: [
    "class section", "class", "clase", "section"
  ],
  lastName: [
    "last name", "lastname",
    "apellido", "apellidos", "apellidas"
  ],
  firstName: [
    "first name", "firstname",
    "nombre", "llamas", "te llamas"
  ]
};

// Add a manual sorting menu.
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("Student Sorting")
    .addItem("Sort responses now", "sortResponses")
    .addToUi();
}

// Runs automatically after a form submission.
// Requires an installable spreadsheet trigger.
function sortOnFormSubmit(e) {
  const sheet = RESPONSE_SHEET_NAME
    ? e.source.getSheetByName(RESPONSE_SHEET_NAME)
    : e.range.getSheet();

  if (!sheet) {
    throw new Error("Response sheet not found.");
  }

  sortSheet_(sheet);
}

// Runs when "Sort responses now" is selected.
function sortResponses() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const sheet = RESPONSE_SHEET_NAME
    ? ss.getSheetByName(RESPONSE_SHEET_NAME)
    : ss.getActiveSheet();

  if (!sheet) {
    throw new Error("Response sheet not found.");
  }

  sortSheet_(sheet);
}

// Sort complete rows using column headings.
function sortSheet_(sheet) {
  const lastRow = sheet.getLastRow();
  const lastCol = sheet.getLastColumn();

  if (lastRow <= 2 || lastCol === 0) return;

  const headers = sheet
    .getRange(1, 1, 1, lastCol)
    .getDisplayValues()[0]
    .map(normalizeHeader_);

  function findColumn(aliases) {
    const normalized = aliases.map(normalizeHeader_);

    const index = headers.findIndex(
      h => normalized.includes(h)
    );

    return index === -1 ? null : index + 1;
  }

  const classCol = findColumn(HEADERS.classSection);
  const lastNameCol = findColumn(HEADERS.lastName);
  const firstNameCol = findColumn(HEADERS.firstName);

  if (!lastNameCol || !firstNameCol) {
    throw new Error(
      "Required columns not found: " +
      "Last name and First name."
    );
  }

  const sortColumns = [];

  if (classCol) {
    sortColumns.push({
      column: classCol,
      ascending: true
    });
  }

  sortColumns.push(
    { column: lastNameCol, ascending: true },
    { column: firstNameCol, ascending: true }
  );

  // Preserve the header row and sort all
  // response columns together.
  sheet
    .getRange(2, 1, lastRow - 1, lastCol)
    .sort(sortColumns);
}

// Normalize headings for flexible matching.
function normalizeHeader_(value) {
  return String(value)
    .trim()
    .toLowerCase()
    .normalize("NFD")
    .replace(/[\u0300-\u036f]/g, "")
    .replace(/\s+/g, " ");
}
Step 4: Enable automatic sorting

  1. In Apps Script, select the Triggers icon (alarm clock) in the left sidebar.

  2. Click Add Trigger.

  3. Select these settings:

Function to run: sortOnFormSubmit
Deployment: Head
Event source: From spreadsheet
Event type: On form submit
  1. Click Save.

  2. Complete Google's authorization prompts.

You only need to configure this trigger once per spreadsheet.

Step 5: Test the automation

  1. Submit a few test responses using your Google Form.

  2. Use different class sections and student names.

  3. Open your linked spreadsheet.

  4. Verify that the rows are sorted in this order:

    • Class section (A–Z)

    • Last name (A–Z)

    • First name (A–Z)

All other response information, including timestamps, email addresses, scores, and answers, stays with the correct student.

Automatic Google Forms Sorting

Organize student responses automatically by class section and name.

BEFORE: Responses in submission order (not real student names)

Last name First name Class section
García Ana Spanish 3
Smith Ben Spanish 2
López Carlos Spanish 2
Adams Diana Spanish 3
↓
Google Apps Script automatically sorts the responses
Class section → Last name → First name

AFTER: Responses organized automatically

Last name First name Class section
López Carlos Spanish 2
Smith Ben Spanish 2
Adams Diana Spanish 3
García Ana Spanish 3

✓ Entire response rows stay together, including timestamps, scores, email addresses, and answers.

Step 6: Sort manually whenever needed

Reload the spreadsheet after installing the script.

A new menu called Student Sorting will appear. Select Sort responses now to sort the active response tab.

This is useful after correcting student names, changing class sections, or importing older responses.

Tips

  • Keep the column headings consistent.

  • Do not manually sort individual columns. Always sort complete rows.

  • If your form does not collect email addresses or scores, the script still works.

  • For student privacy, do not publish actual student responses or make completed response spreadsheets publicly accessible.

  • Make a backup before applying the script to a spreadsheet containing important data.