Student Comment Generator

Harnessing Automation for Fair & Efficient Student Feedback

In the educational landscape, providing individualized and fair feedback to students is a cornerstone of effective teaching. Yet, the task of crafting comments can be a daunting challenge for educators, particularly when faced with the sheer volume of evaluations required. The? Student Comment Generator supports the creation of student feedback based on set performance metrics.

Simplifying Feedback through Automation

The Student Comment Generator is a custom Google Apps Script designed to automate the process of writing student feedback comments in Google Sheets.?

It works by analyzing performance metrics from specified columns and using predefined phrases to compose “tailored” comments for each student.

The tool is designed with both efficiency and fairness in mind. Automating the initial draft of student comments, it offers a consistent framework that ensures each student is evaluated against the same criteria. This not only saves valuable time for educators but also fosters a more equitable assessment environment.

Instructions

Setting Up Your Sheet: Begin by entering each student's first name in column B and selecting the appropriate gender pronoun in column A from a dropdown list. This step primes the tool with the necessary personalization details for each comment.

Defining Performance Metrics: In row 1, cells D1 through K1, define up to eight performance metrics such as "Summative completion" or "Participation." The student may be assessed on some of these metrics, and they will drive the content of the automated comments:

I teach MYP Design, and we use performance metrics such as 'overall completion', 'design cycle knowledge', and 'original & creative work'.

Crafting Performance Descriptors: For each metric, write corresponding low, middle, and high descriptors in columns N through U. These descriptors will be used to construct comments reflecting each student's performance. The descriptors text should include the placeholder [Name], and placeholder pronouns ‘He’, ‘he’, ‘His’, ‘his’, and ‘him’, and ‘himself’ instead of actual student names and specific gender pronouns. The script includes a function to replace the placeholders [Name], ‘He’, ‘he’, ‘His’, ‘his’, and ‘him’, and ‘himself’ in the phrases with actual student names and appropriate gender pronouns.? One of the intricacies of the tool lies in its handling of language, particularly gender pronouns. To accommodate the automation process and ensure clarity in substitutions, template comments are designed with masculine pronouns. This is not a reflection of gender bias but a practical decision based on linguistic clarity — 'him' and 'his' clearly map to 'her', whereas mapping 'her' to 'his' or 'him' is ambiguous. This choice ensures precision and correctness when customizing comments for students of any gender, whereas the placeholder ‘her’ would require more context to determine whether it should be replaced with ‘his’ vs. ‘him’ to match student gender pronouns.?

Generating Comments: With a simple click in cell L1, comments tailored to each student's performance will populate in column C. You can also add an optional custom final sentence for each student in column L for that personal touch.

Continuity for New Classes: To replicate the setup for new classes, duplicate the sheet and replace names, genders, and metrics accordingly.

Key Components: Understanding the Scripts Mechanics

• Data Retrieval: The script activates the Google Sheet and determines the range where student data and performance metrics are stored. Columns D to K are designated for performance metrics, with each level—Low, Middle, and High—representing different areas of student performance.
• Comment construction: For each student, the script dynamically assembles a comment by sequentially appending the appropriate phrases, based on the performance level in each metric; It retrieves pre-defined phrases corresponding to each performance level and appends them into a coherent comment. Phrases corresponding to performance levels are stored separately in Columns N to U. When the 'generateComments' button is clicked, the script matches each student's performance level from Columns D to K with the appropriate phrases from Columns N to U, crafting a comment that reflects their achievements and areas for growth.

• Rich Text Styling: As the comment for each student is built, the script also applies rich text styling. The color of each sentence within the comment corresponds to the performance level it represents, enhancing readability and emphasis.

• Final Output: The fully constructed and styled comments are then written back to the Google Sheet, each aligned with its respective student. The script ensures that the comments are visually coherent and aligned with the data they are based on.

Handling Incomplete Data

• Flexibility with Performance Metrics: The Student Comment Generator script is designed to accommodate varying amounts of performance metrics. Users are not required to fill all 8 performance metrics for each student; the script intelligently ignores empty columns, ensuring smooth operation even if some metrics are missing.
• Dealing with Partial Evaluations: In situations where educators might not evaluate certain students on all metrics, or if they fail to set every dropdown menu to "Low", "Middle", or "High", the script is equipped to handle these cases gracefully. If a metric is left unevaluated for a student, the script will simply omit that part of the comment, ensuring that the remaining evaluations are still processed and comments generated accordingly.Note: It is important to ensure that every metric column has a valid value (Low, Middle, High, or empty) to avoid errors during script execution.

Extending Functionality

Adding More Performance Metric Columns: Expanding the tool to accommodate additional performance metrics involves several steps and requires a deeper understanding of the script:

• Update the Google Sheet: Add new columns for the additional metrics and extend the data validation (dropdown menus) to these columns.
• Adjust the Phrase Range: Move the corresponding phrases for the new performance levels (Low, Middle, High) to align with the newly added columns.
• Modify the Script:

1. Update performanceLevelEndColumn in the script to reflect the new end column index.
2. If you've added the new metrics beyond the original phrase range ("N2:U4"), update the phrase range in the script to include these new phrases.
3. Ensure that the logic for constructing comments (commentParts array) and applying styles accommodates the additional columns.

Caution: Extending the functionality for more metrics requires careful adjustments to maintain the script's integrity. It's recommended for users with a basic understanding of Google Apps Script.

Robustness and Customization: The script is robust enough to handle varying scenarios and can be customized to fit different assessment structures. However, significant changes or additions might require a deeper dive into the script's logic and structure.

Controversy and the Human Element

The use of automated tools in education can stir debate. Critics might argue that automation could depersonalize the feedback process. However, the Student Comment Generator tool is intended to create a starting point for comments, which educators can then review and personalize. It ensures that comments retain a consistent structure, while the final sentence or any additional feedback remains open for customization. This balance between automation and human oversight ensures that the tool enhances the educator's workflow without compromising the personal touch so crucial in student feedback.

Navigating App Verification and Maintaining Trust

As with many custom scripts developed for Google Sheets, the Student Comment Generator tool starts as an unverified app. Google's verification process is a rigorous one, and not all apps undergo this procedure. However, the label "unverified" does not inherently signify a security risk. It simply means that the app has not been through Google's official verification process.

When educators make their own copy of the tool, it becomes a new project, inheriting the unverified status anew. Despite this, users can trust in the transparency and safety of the script. We encourage educators to examine the code themselves and run the tool within their domain to maintain control over their data and privacy.

Granting Permissions with Understanding

When you start using the Student Comment Generator, Google's security measures will prompt you with a few steps to ensure that you are authorizing a script that has not been verified by Google. This is a standard part of the process for scripts that automate tasks within Google Sheets:

• Unverified App Prompt: You'll see a message stating "Google hasn't verified this app." This is normal for custom scripts. Click "Advanced" to see more options.
• Proceed Safely: After clicking "Advanced," choose "Go to Student Comment Creator (unsafe)." The label "unsafe" is automatically applied to scripts that haven't been through the Google verification process, but as long as you trust the source or have verified the code yourself, you can proceed safely.
• Choose Your Account: Select the Google account you'd like the script to access. This allows the script to integrate with your Google Sheets.
• Permission to Run: Lastly, grant the necessary permissions by clicking "Allow." This step is crucial for the script to operate, enabling it to read and modify your spreadsheet data to generate comments.

As this tool is in its beta phase, I invite your feedback and suggestions.

The Promise of Productivity

The Student Comment Generator tool is designed with both efficiency and fairness in mind. Automating the initial draft of student comments offers a consistent framework that ensures each student is evaluated against the same criteria. This not only saves valuable time for educators but also fosters a more equitable assessment environment.

Complete Code:

// Student Comment Generator Script by Avi Megiddo



// This function is executed when the spreadsheet is opened. 
// It creates the menu 'Comment Tools' and menu items.
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Create a new menu item called 'Comment Tools'.
  ui.createMenu('Comment Tools')
    .addItem('Generate Comments', 'generateComments')
    .addItem('Validate Gender Data', 'validateGenderDropDownData')
    .addItem('Reset Sheet', 'resetSheet')
    .addToUi(); // Add to the user interface.
}


// This script automates the creation of student feedback based on performance metrics in a Google Sheet.
function generateComments() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // Start processing from the second row
  var endRow = sheet.getLastRow();
  var dataRange = sheet.getRange(startRow, 1, endRow - startRow + 1, sheet.getLastColumn());
  var data = dataRange.getValues();

  // Define the columns where performance levels are stored
  var performanceLevelStartColumn = 3;  // Starting from Column C (3rd column)
  var performanceLevelEndColumn = 10;   // Ending at Column K (10th column)

  // Range for predefined phrases based on performance levels
  var phrasesRange = sheet.getRange("N2:U4");
  var phrases = phrasesRange.getValues();

  // Loop through each row to generate comments
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var commentParts = [];  // Array to hold comment segments

    // Assemble comment based on performance metrics
    for (var j = performanceLevelStartColumn; j <= performanceLevelEndColumn; j++) {
      var performanceLevelIndex = j - performanceLevelStartColumn;
      var level = row[j];

      // Construct the comment with placeholders
      for (var j = performanceLevelStartColumn; j <= performanceLevelEndColumn; j++) {
        var performanceLevelIndex = j - performanceLevelStartColumn;
        var level = row[j];

        // Skip the metric if it's not evaluated
        if (level !== "Low" && level !== "Middle" && level !== "High") {
          continue;
        }

        var sentence = phrases[level === "Low" ? 0 : level === "Middle" ? 1 : 2][performanceLevelIndex];
        if (sentence) {
          commentParts.push({ sentence: sentence, level: level });
        }
      }


      // Replace placeholders with actual names and gender pronouns
      var commentText = "";
      commentParts.forEach(function (part) {
        commentText += replaceNameAndGender(row[1], row[0], part.sentence) + " ";
      });

      // Append any final sentences from column L (index 11)
      var lastSentence = row[11];
      if (lastSentence) {
        commentText += replaceNameAndGender(row[1], row[0], lastSentence);
      }

      // Write the comment back to the sheet in column C
      var commentCell = sheet.getRange(startRow + i, 3);
      commentCell.setValue(commentText);

      // Apply rich text styling for readability
      var richTextValue = commentCell.getRichTextValue();
      var richTextBuilder = richTextValue.copy();
      var currentPos = 0;

      // Style each part of the comment based on performance level
      commentParts.forEach(function (part) {
        var textStyle = getStyleForLevel(part.level);
        var sentence = replaceNameAndGender(row[1], row[0], part.sentence) + " ";
        var startPos = commentText.indexOf(sentence, currentPos);
        var endPos = startPos + sentence.length;
        richTextBuilder.setTextStyle(startPos, endPos, textStyle);
        currentPos = endPos;
      });

      // Update the cell with the styled text
      commentCell.setRichTextValue(richTextBuilder.build());
    }
  }
}

// Define text styles for each performance level
function getStyleForLevel(level) {
  var textStyle = SpreadsheetApp.newTextStyle();
  if (level === "Low") {
    textStyle.setForegroundColor("#cc0000");  // Apply dark red for low performance
  } else if (level === "Middle") {
    textStyle.setForegroundColor("#e1ad01");  // Apply dark yellow for middle performance
  } else if (level === "High") {
    textStyle.setForegroundColor("#34a853");  // Apply dark green for high performance
  }
  return textStyle.build();
}


// Replace placeholders with actual names and appropriate gender pronouns
function replaceNameAndGender(name, gender, comment) {
  var nameRegex = new RegExp("\\[name\\]", "gi");
  comment = comment.replace(nameRegex, name);

  // Adjust pronouns based on gender
  if (gender === "She") {
    // Replace masculine pronouns with feminine
    comment = comment.replace(/\bHe\b/g, "She").replace(/\bhe\b/g, "she").replace(/\bhim\b/g, "her")
      .replace(/\bHis\b/g, "Her").replace(/\bhis\b/g, "her").replace(/\bhimself\b/g, "herself");
  } else if (gender === "He") {
    // Keep masculine pronouns
    comment = comment.replace(/\bShe\b/g, "He").replace(/\bshe\b/g, "he").replace(/\bherself\b/g, "himself");
  } else if (gender === "They") {
    // Replace with neutral or plural pronouns
    comment = comment.replace(/\bShe\b/g, name).replace(/\bshe\b/g, name).replace(/\bhim\b/g, name)
      .replace(/\bHe\b/g, name).replace(/\bhe\b/g, name).replace(/\bHis\b/g, "Their").replace(/\bhis\b/g, "their")
      .replace(/\bhimself\b/g, "themselves");
  }

  return comment;
}

// this function is necessary if you paste existing gender data in the form of 'f' for female and 'm' for male.

function validateGenderDropDownData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange("A2:A" + sheet.getLastRow()); // Assuming genders start at A2
  var values = range.getValues();

  // Text colors for each gender
  var colorHe = "#0a53a8"; // Blue
  var colorShe = "#b10202"; // Red
  var colorThey = "#5a3286"; // Purple

  // Dropdown options
  var dropdownValues = ["He", "She", "They"];

  // Loop through all cells in the column
  for (var i = 0; i < values.length; i++) {
    var cell = sheet.getRange(i + 2, 1); // Adjust for zero index and header
    var gender = values[i][0].toLowerCase();

    // Convert 'm' to 'He' and 'f' to 'She'
    if (gender === 'm') {
      cell.setValue('He').setFontColor(colorHe);
    } else if (gender === 'f') {
      cell.setValue('She').setFontColor(colorShe);
    } else if (gender === 'they') {
      cell.setValue('They').setFontColor(colorThey);
    }

    // Set text alignment
    cell.setHorizontalAlignment("center").setVerticalAlignment("middle");

    // Restore the dropdown menu
    var validationRule = SpreadsheetApp.newDataValidation().requireValueInList(dropdownValues, true).build();
    cell.setDataValidation(validationRule);
  }
}



function resetSheet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  var rangeToClear = sheet.getRange("C2:K" + lastRow);
  var dataValidationRange = sheet.getRange("A2:A" + lastRow);
  var rangeToCenter = sheet.getRange("D2:K" + lastRow);

  // Clear content while preserving data validation and formatting
  rangeToClear.clearContent();

  // Reapply any data validations that may have been removed by clearContent()
  var validations = dataValidationRange.getDataValidations();
  for (var i = 0; i < validations.length; i++) {
    if (validations[i][0]) {
      // Restore data validation
      dataValidationRange.getCell(i + 1, 1).setDataValidation(validations[i][0]);
    }
  }

  // Reapply text alignment for columns D through K
  rangeToCenter.setHorizontalAlignment("center");

  // Reapply vertical alignment and font size/color if needed
  rangeToClear.setVerticalAlignment("middle")
               .setFontSize(10)
               .setFontColor("#000000"); // Default black color, adjust as necessary
}


        

? Avi Megiddo 2023 CC BY-NC