During the COVID pandemic high school exams were cancelled in many countries. The UK government required schools to use Centre Assessed Grades (CAGs) instead. A British international school in Qatar asked me to help them generate their CAG reports based on spreadsheets of grades kept by the teachers.
The school used Google for Education, so I chose Google Apps Script to process the spreadsheets and generate PDF reports.
The teachers were given a spreadsheet template to follow, to make it easy for a computer program to read the grades. Unfortunately many teachers changed the template, for example by adding extra rows and columns, so I started by writing several functions to check the layout and format of the teachers' spreadsheets. Also the way the grades were entered needed to be checked, for example some teachers used the % symbol inconsistently - sometimes entering 90% and sometimes entering .90% - so I also wrote functions to check the consistency of the way the grades were entered by the teachers.
When the spreadsheets were in a consistent format, I made a Google Doc template to combine the grades together. I wrote functions which worked through a list of students, making a copy of the template for each student and entering all their grades from all the spreadsheets. Some students had more than 10 grades and some students only had 1 or 2, so the Google Docs needed to be flexible with tables with different numbers of rows. When all the Google Docs were ready, I wrote another function to convert them all into PDF documents. (This took a long time, and actually Google crashed worldwide that day - I sometimes wonder if it was something to do with my hard-working Google Apps Script!)
When the PDF documents were all ready, I wrote a final function to email them with a friendly explanation to all students and parents. I was anxious when that script was running, but the large majority of the students and parents were very happy with the CAG reports.
A few students appealed some of the grades, and in some cases their grades were changed, so I wrote a new function to reset and run the whole process again just for specific students.
I learned a lot that year about the limits of Google Apps Script.
The reports sent to students and parents looked very professional.
The next year when the school asked me to generate the Centre Assessed Grade reports, I was more confident that the script could keep running and recover by itself if there were errors from the Google servers. I created a trigger which would run the script every 5 minutes, and the script itself would stop after 4 minutes. I was anxious when I clicked "run" but after a few hours everything was processed perfectly! I was ready to go for future years, but thankfully the pandemic ended and exams started once again!
