By Avi Megiddo and ChatGPT

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. By 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.” These metrics 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 (thank you to my colleague YJ Choi for some UI/UX ideas). 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 Script’s 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. By 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.

Complete Code:

Student Comment Generator by Avi Megiddo code.gs

© Avi Megiddo 2023 CC BY-NC