Skip to content

Instantly share code, notes, and snippets.

@jameswomack

jameswomack/Code.gs

Last active March 10, 2025 06:00
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save jameswomack/ef83ef78790dc9d238d27cc1313778a7 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save jameswomack/ef83ef78790dc9d238d27cc1313778a7 to your computer and use it in GitHub Desktop.
Download ZIP
School-oriented AppScript Playground
Raw
README.md

School-oriented AppScript Playground for Google Sheets

createTopTenSheets

🚀 Executive Summary

The createTopTenSheets AppScript automates the process of analyzing and organizing student test scores from a Google Sheet. It extracts student data from a designated sheet, sorts the scores by grade, and generates new sheets containing the top 10 test scores for each grade (9th–12th).

📝 How It Works

  1. The script reads student data from a sheet named "All Students" starting at row 2, column B:

    const data = allStudentsSheet.getRange(2, 2, allStudentsSheet.getLastRow(), 3).getValues();

  2. It expects the following format:

    • Headers:
      • Name – Full name of the student (first and last name).
      • Grade – Grade level (9th–12th).
      • Test Score – Numeric test score (between 40–100).

  3. The script processes the data as follows:

    • Ignores the header row using JSON.stringify():
    const HEADER_ROW = JSON.stringify(["Name", "Grade", "Test Score"]);
    • Filters out rows with empty values:
    row.every(cell => cell)
    • Converts grade and score to numeric values:
    const grade = parseInt(row[ColumnIDs.GRADE], 10);
const score = Number(row[ColumnIDs.SCORE]);

  4. For each grade:

    • If a sheet for that grade already exists, it is deleted and recreated.
    • The script creates a new sheet named “9th Grade,” “10th Grade,” “11th Grade,” or “12th Grade.”
    • Headers are added to the new sheet.
    • Top 10 scores are added to the sheet.
    • Columns are auto-resized for better display.

📌 Example Output

If the script processes the following input:

Name Grade Test Score
Alex Johnson 9 88
Taylor Smith 10 92
Jordan Lee 9 95
Morgan Davis 9 91
Riley Brown 10 85
Jamie Miller 12 97
Cameron Wilson 11 93
Quinn Anderson 12 94
Peyton Thomas 11 86
Drew Martin 10 95
Sydney White 9 78
Avery Garcia 11 88

✅ Generated Output Sheets

"9th Grade" Sheet

Name Grade Test Score
Jordan Lee 9 95
Morgan Davis 9 91
Alex Johnson 9 88
Sydney White 9 78

"10th Grade" Sheet

Name Grade Test Score
Drew Martin 10 95
Taylor Smith 10 92
Riley Brown 10 85

"11th Grade" Sheet

Name Grade Test Score
Cameron Wilson 11 93
Avery Garcia 11 88
Peyton Thomas 11 86

"12th Grade" Sheet

Name Grade Test Score
Jamie Miller 12 97
Quinn Anderson 12 94

🎯 Why Use This Script

✅ Automates the process of sorting and organizing student data.
✅ Ensures consistent and reliable data handling.
✅ Eliminates manual work and human error.
✅ Provides a clear and organized view of top-performing students.

🚦 How to Run It

  1. Open your Google Sheet.
  2. Go to Extensions → Apps Script.
  3. Copy and paste the script.
  4. Save and authorize the script if prompted.
  5. Run the createTopTenSheets function from the Apps Script interface.

✅ Best Practices

  • Ensure that the "All Students" sheet is properly formatted with correct headers.
  • Keep the test scores within the expected range (40–100).
  • Remove any extra rows or columns that could interfere with data reading.
Raw
Code.gs
const ALL_STUDENTS_SHEET_NAME = 'All Students';
const ColumnIDs = {
NAME: 0,
GRADE: 1,
SCORE: 2
};
const HEADER_ROW = JSON.stringify(["Name", "Grade", "Test Score"]);
function createTopTenSheets() {
log('Script execution started');
const playgroundSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const allStudentsSheet = playgroundSpreadsheet.getSheetByName(ALL_STUDENTS_SHEET_NAME);
if (!allStudentsSheet) {
log(`Sheet "${ALL_STUDENTS_SHEET_NAME}" not found.`, 'ERROR');
return;
}
const data = allStudentsSheet.getRange(2, 2, allStudentsSheet.getLastRow(), 3).getValues();
if (!data || data.length === 0) {
log(`No data found in the "${ALL_STUDENTS_SHEET_NAME}" sheet.`, 'WARN');
return;
}
const grades = { 9: [], 10: [], 11: [], 12: [] };
const filteredData = data
.filter(row =>
JSON.stringify(row) !== HEADER_ROW &&
row.every(cell => cell) // Checks for truthy values
)
.map(row => {
const name = row[ColumnIDs.NAME];
const grade = parseInt(row[ColumnIDs.GRADE], 10);
const score = Number(row[ColumnIDs.SCORE]);
if (isNaN(grade) || isNaN(score)) {
log(`Invalid data: ${JSON.stringify(row)}`, 'WARN');
return null;
}
return { name, grade, score };
})
.filter(student => student && grades[student.grade] !== undefined);
if (filteredData.length === 0) {
log('No valid student data after filtering.', 'WARN');
return;
}
filteredData.forEach(student => grades[student.grade].push(student));
const createSheetName = grade => `${grade}th Grade`;
for (const grade in grades) {
if (grades[grade].length > 0) {
grades[grade].sort((a, b) => b.score - a.score);
grades[grade] = grades[grade].slice(0, 10);
try {
let currentGradeSheet = playgroundSpreadsheet.getSheetByName(createSheetName(grade));
if (currentGradeSheet) {
log(`Deleting existing sheet: ${createSheetName(grade)}`);
playgroundSpreadsheet.deleteSheet(currentGradeSheet);
}
log(`Creating new sheet: ${createSheetName(grade)}`);
currentGradeSheet = playgroundSpreadsheet.insertSheet(createSheetName(grade));
currentGradeSheet.getRange('A1:C1').setValues([['Name', 'Grade', 'Test Score']]);
const values = grades[grade].map(student => [student.name, student.grade, student.score]);
currentGradeSheet.getRange(2, 1, values.length, values[0].length).setValues(values);
log(`Writing ${values.length} rows to ${createSheetName(grade)}`);
currentGradeSheet.autoResizeColumns(1, 3);
log(`Resized columns for ${createSheetName(grade)}`);
} catch (error) {
log(`Error creating sheet for grade ${grade}: ${error.message}`, 'ERROR');
}
}
}
SpreadsheetApp.flush();
log('Script execution finished');
}
Raw
HOW_IT_WORKS.md

📝 How It Works

  1. Read student data from the sheet
    The script reads student data from a sheet named "All Students" starting at row 2, column B:
const data = allStudentsSheet.getRange(2, 2, allStudentsSheet.getLastRow() - 1, 3).getValues();
  1. Filter out empty rows
    Any row that’s missing a Name, Grade, or Test Score is ignored:
const filteredData = data.filter(row => row[0] && row[1] && row[2]);
  1. Separate data by grade
    The script stores data by grade (9th–12th) using a dictionary:
const grades = {
  9: [],
  10: [],
  11: [],
  12: []
};

filteredData.forEach(row => {
  const [name, grade, score] = row;
  const gradeInt = parseInt(grade, 10);
  if (grades[gradeInt] !== undefined) {
    grades[gradeInt].push({ name, grade: gradeInt, score });
  }
});
  1. Sort each grade by test score in descending order
    The script sorts scores and retains only the top 10:
grades[grade].sort((a, b) => b.score - a.score);
grades[grade] = grades[grade].slice(0, 10);
  1. Create a new sheet for each grade
    If a sheet for that grade already exists, it’s deleted and recreated:
let currentGradeSheet = playgroundSpreadsheet.getSheetByName(createSheetName(grade));
if (currentGradeSheet) {
  playgroundSpreadsheet.deleteSheet(currentGradeSheet);
}
currentGradeSheet = playgroundSpreadsheet.insertSheet(createSheetName(grade));
  1. Write headers and top 10 scores to the new sheet
    The script adds headers and populates the sheet with the sorted data:
currentGradeSheet.getRange('A1:C1').setValues([['Name', 'Grade', 'Test Score']]);
const values = grades[grade].map(student => [student.name, student.grade, student.score]);
currentGradeSheet.getRange(2, 1, values.length, values[0].length).setValues(values);
  1. Auto-resize columns for better display
    The script automatically adjusts column width:
currentGradeSheet.autoResizeColumns(1, 3);
  1. Flush the changes
    The script ensures all changes are applied:
SpreadsheetApp.flush();
Raw
Logger.gs
const LOG_PREFIX = '[FreakInTheSheets]';
function log(message, level = 'INFO') {
const timestamp = new Date().toISOString();
const logMessage = `${LOG_PREFIX} [${timestamp}] [${level}] ${message}`;
if (level === 'ERROR') {
console.error(logMessage); // Shows up in console during execution
} else if (level === 'WARN') {
console.warn(logMessage); // Shows up in console during execution
}
Logger.log(logMessage); // Persists to execution logs
}
@jameswomack
Copy link
Author

jameswomack commented Mar 10, 2025

Screenshot 2025-03-09 at 10 27 17 PM

@jameswomack
Copy link
Author

jameswomack commented Mar 10, 2025

Screenshot 2025-03-09 at 10 54 31 PM

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment