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
Open Google Forms and create a new form.
Add these three required questions:
Add your assignment, quiz, or survey questions.
- Last name — Short answer
- First name — Short answer
- Class section — Dropdown
If appropriate, enable email collection under Settings.
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
Open your Google Form.
Select the Responses tab.
Click the Google Sheets icon or select More (⋮) → Select destination for responses.
Choose Select existing spreadsheet.
Locate your copy of the Auto-Sorting Spreadsheet template (you can create your own copy here).
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
Open your linked response spreadsheet.
Select Extensions → Apps Script.
Replace any starter code with the script provided below.
Click Save.
Step 4: Enable automatic sorting
In Apps Script, select the Triggers icon (alarm clock) in the left sidebar.
Click Add Trigger.
Select these settings:
sortOnFormSubmitDeployment: Head
Event source: From spreadsheet
Event type: On form submit
Click Save.
Complete Google's authorization prompts.
You only need to configure this trigger once per spreadsheet.
Step 5: Test the automation
Submit a few test responses using your Google Form.
Use different class sections and student names.
Open your linked spreadsheet.
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
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 |
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.
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.