ag-frontend-v2

A lightweight utility for pulling data (e.g. grades) from Google Sheets and displaying it to students in a Gradescope autograder.

An earlier version of this utility exists at https://github.com/cs161-staff/ag-frontend. This version is a complete rewrite.

Usage

1. Create a new spreadsheet in Google Sheets, and create a credentials.json file in this repo to access that spreadsheet.

   • Follow this gspread guide to create the credentials.json file.
   • Don't forget to share your spreadsheet with the service account email.

2. In main.py, change the SHEET_NAME to the name of your spreadsheet.

   • You can also optionally change the name of tabs and columns (as referenced in later steps) by updating the relevant config variables.

3. Create a tab named Roster.

   • There must be a header row, followed by one row per student, containing the data you want to display for each student.
   • There must be a column named SID with each student's numeric student ID.
   • For columns with data you want to display, the header name must be alphanumeric (underscores are allowed). This is because the script uses Python's string template class.

4. Create a tab named Template.

   • In cell A1, write a JSON template that indicates how Gradescope should display the output. See Gradescope's autograder documentation for how to format your JSON. I'd suggest doing this in a separate text editor and then copy-pasting into the cell.
   • All cells besides A1 will be ignored by the script.
   • For fields where you want to substitute student data from the sheet, write $header_name in place of actual data. Be careful with data types, e.g. numerical data should have no quotes like "score": $total_score, and string data needs quotes like "output": "$total_score",. These placeholders will be matched up against the header fields and substituted with student-specific values using Python's string template class.

5. Zip up all the files in this repo (not the folder itself; see the top screenshot of Gradescope's autograder documentation), including the credentials.json from step 1. Upload the resulting autograder.zip file to Gradescope.

   • Optional, but recommended: On Gradescope, click on Settings in the left sidebar, scroll down to Ignored Files, and put *.* in the text box. The student submission is totally ignored by this script either way, but this step ensures that whatever junk file the student uploaded is not stored on Gradescope.

6. Place an offering at the shrine of EvanBot. Enjoy the (hopefully) working autograder.

   • The student can trigger a run of the autograder by making a submission to the autograder. The file(s) uploaded by the student are totally ignored by the script, so they can upload whatever they want.
   • Staff can trigger a run of the autograder by pressing "Re-run autograder" on a student's existing submission.
   • Each run of the autograder pulls fresh data from Google Sheets; there's no caching and no dependency between autograder runs.
   • If the autograder fails to run (e.g. misconfigured, rate limit), the student will see a generic Gradescope "autograder failed to run" message, because this script only outputs error messages to be visible on staff-side, not student-side.

Running Locally

To run the script locally: uv run main.py 3031234567, replacing the number with the SID you want to pass into the script.

This repo and the autograder it outputs use the uv package manager, but everything is in native Python, so you could probably also get away with running something like python3 main.py 3031234567. You'll just have to install the packages yourself.

How It Works

This script was built to be 1) very simple, and 2) very customizable. Some intentional design choices:

• Juggling nested directories on Gradescope autograders is annoying, so this entire repo is flat with no directories. You can just zip up the files and instantly get a working autograder.
• The Gradescope autograder's JSON format is entirely exposed to the user, so anything that Gradescope is capable of displaying is available to you. Creating the JSON template is probably a little annoying, but it's a one-time investment at setup. (Plus, you can quickly debug by changing the JSON template in Google Sheets and then clicking the "Re-run autograder" button.) Putting the JSON template in a Google Sheets cell is also slightly awkward, but it puts all data into a single Google Sheets spreadsheet which is nice.
• Unlike previous versions of this script, there is no intermediate caching layer. This runs the risk of timeouts, but it also makes the entire autograder self-contained, without reliance on any other server.

How the autograder actually works:

1. When you upload the autograder to Gradescope, it first runs setup.sh to install dependencies.
   • For this script, setup.sh just installs the uv package manager, which will in turn handle all the Python dependencies.
   • pyproject.toml, .python-version, and uv.lock are all files auto-generated by uv.
2. Every time a student submits to the autograder, Gradescope runs run_autograder.
   • For this script, run_autograder pulls the student ID from the submission metadata file created by Gradescope, and then passes the SID to the main.py script.
   • This relies on your roster on Gradescope (click "Roster" in the left sidebar on the course page) correctly including student IDs for each student. If a student ID is not found, either you didn't list the SID on Google Sheets, or the student's SID in the Gradescope roster is wrong.
3. run_autograder now calls out to main.py to do all the hard work.
   • This is mostly documented in main.py itself. The main interesting part is that we read the JSON template in cell A1 as a raw string, and then use Python's string template class to substitute all $-strings with the corresponding field in the student data record.
   • Note that the JSON prior to substitution might be invalid, because some $-strings might be unquoted so that they can become numbers later, e.g. "score": $total_score. However, the JSON after the substitution must be valid, and the script has a check for this.
4. After the substitution is done, main.py finishes by writing the final JSON results to the directory where the Gradescope harness checks for results. This gets displayed to students.

Future Work

In normal usage, it should be pretty hard to hit the Google Sheets API usage limits, and if a student does hit the limit, they can just try again in a few minutes. However, if a staff member uses the "Re-run autograder on all submissions" feature on Gradescope, then it's pretty likely that the concurrently-running autograders will exceed the limit. This can be fixed with an exponential backoff on timeout in the script, but it's not implemented yet.

The most annoying part of this is probably creating the JSON template, so having a library of pre-made JSON templates in this README that can be easily customized would probably be nice. Some example Google Sheets would also be pretty nice.

Maybe some of the errors like "SID not found" or "rate limit" should result in meaningful output being piped to the student? Currently all errors are staff-side only, and the student always sees a generic "autograder failed to run" message.

Nit: In run_autograder, I'm unsure why I had to use the absolute path /root/.local/bin/uv run ... instead of just writing uv run ... (which would complain that uv was not found). Probably some quirk with adding uv to PATH that I'm not too bothered to check out right now.