Personalized Assessment Feedback via Google Sheets

One of the hardest parts of giving assessments to students isn't administering the test: it's what happens after the test.

  • How can you let students know how they did?

  • Is it enough to tell them they got a B, or they scored a 3/4?

  • What information could we provide to students that would help them actually review their test with purpose?

If you have ever wondered about these questions and many others like them, you may be interested in this Google Spreadsheet. This spreadsheet will send each student a personalized email with a description of how many points they earned on each question on an assessment, along with their total score and any associated letter grades or mastery scores.

The email follows this basic format:

Hi {name},

Thank you for submitting {SheetName}. Please review the following feedback on how you performed on the assessment.

Question {#} assessed you on the following learning target: {LearningTarget}

You scored {score} out of {possible} possible points.

If you did not score full points, you can prepare for reassessment by reviewing the following information: {feedback}

...repeat as necessary

You scored a total of {earned} out of {total} points, which corresponds to a mastery score of {masteryScore}. If you have any additional questions, please visit me during office hours or reply to this email.

Sincerely,

Mr. Matthew Hamilton

Geometry Teacher

Animo Watts College Preparatory Academy


Please watch the video to see how the spreadsheet works. The template is completely customizable, so feel free to experiment with it and make it your own! If you try it out, send me an email and let me know! I'd love to hear how it worked for you. You can make your own copy of the spreadsheet below if the links above didn't work. Good luck!

***NOTICE***
This spreadsheet is currently in beta, which means that it is ready for public use, but it may experience issues depending on how it is used and what edits have been made. For example, please verify that all email addresses are valid before running the script. If you experience any issues, please send me an email and let me know. Thank you!