Communicate ongoing evaluations via email from an online spreadsheet

[Originally published in French]

As much as possible, I practice ongoing or continuous assessment of students’ work (ongoing feedback). The aim is to accompany students in the regular improvement of their work, from the draft to the final version.
N.B. : I do not give in to the fetishism of the “finished” work, not forgetting Borges’ warning that the idea of a finished work is “fatigue or superstition”…

It is, therefore, a “conversational” assessment, as students can respond. This allows feedback to be provided during the task itself. This supports student learning, as Alice Keeler reminds us:

This approach is now facilitated by digital applications, including online word processing, which allows collaboration and the addition of comments. I use Google Docs in GSuite and Word online in O365 (education versions). GSuite also allows direct mail operations directly online from the GSheets spreadsheet. In its current version, 0365 does not allow it (this would however be possible via SharePoint, associated with Microsoft Flow).

Here is an example of a fairly simple device, which I use very regularly.

Collection of urls of students’ works


The easiest way to collect students’ online work seems to me to use a form (Google Forms) in which each student will paste the link to his work (from Google Drive, by copying the shareable link). A GSuite for Education account will be used (a personal account would not legally collect students’ personal data).

Students’ answers fill out a spreadsheet, which can automatically collect email addresses (however, you may ask to manually enter the name and class, via fields or drop-down lists). Microsoft Forms (for education) also automatically collects first and last names.

N.B. it is possible at this stage to include self-assessment questions in the form.

You can also ask Google Forms to save the answers in a tab of an existing spreadsheet, so as not to multiply the documents and gather the information.

Assessing Student Work from the Spreadsheet

A somewhat tedious gesture at this stage is to check that each student in the class has responded correctly. This can be done manually by comparing the list of responding students with the list of students in the class.

N.B.: We could also automate the process with the VLOOKUP function dynamically associating the url of each work (from the responses tab) with the name of each student (in the tab with the class roster).

You can then create (or paste from a template) the columns used for the assessment. Each column will correspond to a specific criterion, skill or assessment point. The cells will contain remarks, descriptors, scores, etc.

To fill the cells, you can use drop-down menus to select typical items and remarks. The best way to do this is to create a specific tab containing lists of remarks and descriptors. The drop-down menus will be fed from these lists (menu: Data > data validation). Each modification of one of these lists (in the specific tab) will then be reflected forward in the drop-down menus of the evaluation tabs.

Example of typical remark lists (in French):


Example of using drop-down menus for the assessment (in French):


Communication of assessments by mailings

Evaluations can then be communicated to students in mailings, which can be activated directly online from the spreadsheet. Several solutions are available, free or paid. I present here the use of Yet Another Mail Merge (add-on).

YAMM will associate, for each student, the content of the cells of his lines to an email template in Gmail.

Example of using YAMM (in French):


Towards conversational assessment

Students can then improve their work and inform the teacher, who can continue iteration as needed. The teacher can make regular and personalized reminders (possibly omitting students who have already reached a satisfactory level: it will be enough to exclude them from the direct mail).


The scheme presented here is functional — but I hope to improve it through our exchanges!

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:


Vous commentez à l'aide de votre compte Déconnexion /  Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion /  Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion /  Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion /  Changer )


Connexion à %s