School-oriented AppScript Playground

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.

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');
}

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();

---

2. 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]);

---

3. 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 });
  }
});

---

4. 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);

---

5. 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));

---

6. 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);

---

7. Auto-resize columns for better display
   The script automatically adjusts column width:

currentGradeSheet.autoResizeColumns(1, 3);

---

8. Flush the changes
   The script ensures all changes are applied:

SpreadsheetApp.flush();

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
}