Virtual Results Day

This summer, in the face of uncertainty about how COVID-19 might affect in-person results days, we wanted to ensure our students would have the opportunity to access their qualification results online. That meant we needed a digital solution that could quickly, reliably, and securely share many thousands of results slips.

All Outwood students have a school Google account, conveniently including access to a document-sharing platform: Google Drive.

We used Drive and some simple scripts to create a straightforward yet effective solution that leveraged our existing infrastructure.

Our approach

Since individually sharing thousands of files through Drive’s web interface is impractical, we needed automation.

Google includes functionality to interact with GSuite programmatically in the form of GSuite APIs. They also provide SDKs to simplify working with the APIs for many programming languages, and have baked GSuite interfaces into their Apps Script JavaScript platform.

However, for many everyday tasks, a straightforward way to interact with the GSuite APIs already exists: the CLI frontend GAM. GAM can automate various tasks, from creating user accounts or managing groups, to sharing files. To get a sense of what GAM can do, check out the GAM Cheat Sheet

Automating file sharing

Let’s look at how we used GAM to share files at scale. We’ll start with the basic commands and then scale up to handle thousands of results slips.

Prerequisites

You’ll need to have GAM installed and configured. That could be on your local machine or Google Cloud Shell. It requires:

• A super admin account on your GSuite domain
• Some familiarity with command line shells (e.g. Bash/Zsh on Mac/Linux or Windows PowerShell)

To orchestrate file sharing, you’ll also need a way to associate file names with student emails in a spreadsheet uniquely.

Getting started with GAM and Drive

Before we dive into bulk operations, let’s look at how GAM works by sharing a single file.

We can use GAM’s Drive commands to share files.

Let’s have the account document.owner@school.com share a file with some.student@school.com.

To identify the file we want to share, we need its document ID. Everything stored in Drive has a unique document ID. When working on a document, it’s the long alphanumeric section of the URL in your web browser’s address bar:

https://docs.google.com/spreadsheets/d/6edd541c602111Ed789123/edit

With that information, we can run the following command:

gam user "document.owner@school.com" \
  add drivefileacl "6edd541c602111Ed789123" \
    user "some.student@school.com" \
    role reader

This tells GAM to:

1. Act as the user document.owner@school.com. This account should have permission to share the file.
2. Add an access control list entry (ACL) for the Drive file with the ID 6edd541c602111Ed789123
   • For the user some.student@school.com
   • Assign them the “reader” role (the Viewer permission)

By default, no notification email is sent to the user. If you’d like to trigger a Google Drive notification email, add the sendemail flag:

gam user "document.owner@school.com" \
  add drivefileacl "6edd541c602111Ed789123" \
    user "some.student@school.com" \
    role reader \
    sendemail

You can also add emailmessage "My message" to customise the notification.

Scaling up with bulk operations

Rather than sharing an individual file, we can use GAM’s bulk operations to share a list of files at once. We can modify the previous command to operate on values from the rows of a CSV file.

With files.csv:

documentId,email
6edd541c602111Ed789123,some.student@school.com
a5170eef951f36E4789123,other.student@school.com

We can use the command:

gam csv files.csv \
  gam user "document.owner@school.com" \
    add drivefileacl "~documentId" \
      user "~email" \
      role reader

This runs the sharing command for each row of CSV data—GAM can automatically processes hundreds or thousands of files in this way.

• ~documentId is substituted with the value from the documentId column in the CSV file.
• ~email is substituted with the value from the email column in the CSV file.

Putting it all together

Now that we’ve looked at the GAM command for sharing a file, let’s look at how to use it to automate sharing results slips.

First, we need to construct suitable CSV data to share our results slips with students. We structured ours like this:

fileName,documentId,email,firstName,lastName,yearGroup
arthur-01234-results.pdf,0BC44fBc0eeF951f,arthur.student@school.com,Arthur,Aardvark,11
leah-01235-results.pdf,356927B60eEf951F,leah.student@school.com,Leah,Lemur,11

We included extra columns with the student’s name and year group to generate nicer email messages, but only document ID and email address are strictly necessary.

Constructing CSV data

Each school’s exam officer prepared PDF results slips and uploaded them to a folder in Google Drive. They included each student’s admission number in the file name, allowing us to reference the student uniquely.

Their next step was to create a Sheet with the results slip file names paired with their document IDs. We used a bit of Apps Script magic to help handle this for all files in a folder 🪄

function listFiles(folderId) {
  const sheet = SpreadsheetApp.getActiveSheet();
  const folder = DriveApp.getFolderById(folderId);

  sheet.appendRow(["fileName", "documentId"]);

  let contents = folder.getFiles();

  while (contents.hasNext()) {
    let file = contents.next();

    sheet.appendRow([file.getName(), file.getId()]);
  }
}

If you’d prefer something premade to do this, check out extensions like Drive explorer or Files cabinet.¹

From there, to add student emails and names to the Sheet, we:

1. Extracted admission numbers from the file names (using REGEXEXTRACT())
2. Used the admission numbers to lookup student name, email and year group (using =VLOOKUP())

Sharing and emailing

With our CSV files prepared, we created a script containing the GAM commands we would run on results day.

We opted for our script to use a two-step process rather than use Drive’s built-in notifications:

1. Sharing files in Drive (without a sharing notification)
2. Sending custom email messages

This allowed us some extra flexibility, as we could send emails from addresses that did not have access to the Drive files.

First, we shared the documents in Drive to make them accessible to our students.

# exams.officer@school.com should have permission to re-share the files
gam csv school.csv \
  gam user exams.officer@school.com \
    add drivefileacl "~documentId" \
    user "~email" role reader

Then, we sent an email message to our students.

gam csv school.csv gam user "~email" insertemail \
  from "Results 2020 <results2020@school.com>" \
  to "~email" \
  subject "Your qualification results" \
  message "Dear ~~firstName~~ \n\n ... https://drive.google.com/open?id=~~documentId~~ ..." \
  labels INBOX,UNREAD,IMPORTANT

• ~~firstName~~ and ~~documentId~~ are placeholders to insert values from the CSV data within a longer piece of text.
• \n is a new line
• You can include HTML tags in the message, like anchor tags for nicely formatted links.
• You can easily generate links to content in Google Drive from a document ID by constructing URLs like https://drive.google.com/open?id=[document ID].
• In this instance, we used the insertemail command rather than the usual sendemail command, as it’s more efficient for the high volume of individual messages.²

Testing & documentation

We wanted to be confident that the script and process as a whole would work as expected.

Before the summer break, we:

• Tested the script functionality using dummy results slips and student accounts.
• Documented the process exams officers would follow “on the day”.
• Tested the whole process end-to-end.

Our results day

With the CSV files generated the previous day, we ran scripts containing GAM commands on the morning of results day and then sat back to watch (intensely!) as our students received their results.

After a couple of hours, the process completed successfully 🎉

We found that Drive + GAM offered a solution that was:

• Familiar to students, who are already used to using their Google accounts for school work.
• Cost-neutral, using our existing infrastructure.
• Easy to implement.
• Secure, with access to results slips kept to exams staff and the student; with a clear audit trail in Drive.

If you’re a GSuite super admin, and have not yet used GAM, take a look!