Using Google Apps Script to Power a Student Review Checklist

So as the end of the year approaches, I’ve been wanting to power my year review with some hard data. I could look back at tests, and at activities that we’ve done in class, but more often than not I find that the students actually know themselves if they can do a topic. So this year I’ve combined Google Classroom with a little bit of Apps Script. My first step was to create a checklist, (I’m lucky in Quebec that all of our topics are already broken down into a table), so I put all these topics and skills into a Google Sheet and then used a validation drop down next to the skill (Data > Validation). You can see the end result of that here:


Once I had done this, my next step was wanting to share it with my students, so I did that in Google Classroom, and set it as an assignment which they need to complete. I also set Classroom to make a copy for each student, as this is important for the next step.

I made a copy of my template and then I wrote a little bit of apps script. This script will go and look in a folder in Drive which you tell it, and then return all of the files  in that folder with their ID, Name and the e-mail address of the owner. Here is the code:

function onOpen() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var findFilesInFolder = [ {name: "Find Files in Folder", functionName: "search"}];
 ss.addMenu("Find Files in Folder", findFilesInFolder);
 function search() {
 var ui = SpreadsheetApp.getUi();
 var response = ui.prompt('I need the Folder ID to Search', 'May I know the Folder ID?', ui.ButtonSet.OK);
 // Process the user's response.
 if (response.getSelectedButton() == ui.Button.OK) {
 var folderId = response.getResponseText();}
 else {
 Logger.log('The user clicked the close button in the dialog\'s title bar.');
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getActiveSheet();
 var id = folderId;
 var folder = DriveApp.getFolderById(id);
 var files = folder.getFiles()
 var output = [];
 while (files.hasNext()) {
 var file =;
 var owner = file.getOwner().getEmail();
 var fileID = file.getId();
 var fileName = file.getName();
 sheet.getRange(2, 3, output.length, 3).setValues(output);

Once you’ve got this running then you will see a new menu item in your GoogleSheet ‘Find Files in Folder’


Clicking on that menu will prompt for a folder ID, you can choose the Folder ID of your assignment from Google Classroom by going to Google Drive > Classroom > Class > Assignment and then looking at the URL

We want to copy the bold part of the link, everything after folders/ to end end of the URL and then paste this in the prompt box.

You will now get all the files pasted into your sheet, I suggest you do this in a separate worksheet in your Google Sheet and then reference this information.

I used =transpose(range) to get my data in columns from rows

Then I used =importrange(ReferenceofFileID,RangeToImport) to import all the information from the students sheets. You can see the final file here:



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s