Fixing the student progress report system

It turns out that the approach I described in an earlier post simply does not work. I didn’t mention in that post that I developed it with the help of an interaction with ChatGPT. Turns out, trusting ChatGPT led me astray: it is technically impossible to use Google Sheets and Apps Script in the way I thought I could. Mea culpa.

Today, I stood up a new solution. Now, each student gets a UUID which is sent as a custom parameter to my script, and this returns for them a view of their row of the grading sheet. This variation was also developed in concert with ChatGPT, but one major difference is that this one verifiably works. The AI interaction was helpful in quickly identifying how to use Apps Script to generate UUIds and read http parameters.

In this post, I am going to try to reconstruct the steps I followed to get all the pieces working. It assumes I already have a grading spreadsheet set up that has all the students’ university emails in it.

1. Create a function in the Apps Script to generate a UUID. It looks like this: function UUID() { return Utilities.getUuid(); }
2. In the sheet, call this function for each student to give each a UUID. Then, copy and paste those values back in as plain text values.
3. Create another column to construct the URL from that UUID. For example, have the stub URL in a cell, then concatenate “&code=” with the UUID after it.
4. Export the sheet in .xlsx.
5. From Word, start a new document, then from the “Mailings” menu, start a new mail merge. Note that this cannot be done in Office online: it requires running Word locally.
6. Use “Select Recipients” to link the Excel file as a data source.
7. Type the message, using the “Insert Merge Field” tool to put the student’s URL in place. “Preview results” is a toggle button (surprise!) that can be used to check that it is merging as expected.
8. Choose “Finish and Merge” to generate all the emails, which will not actually send yet.
9. Open Outlook, which will step through and send each email.

Incidentally, this is my first blog post that I have written in Markdown and then turned into HTML with pandoc ( pandoc post.md -o post.html). It has worked great and is a much better writing experience for this kind of post.