Using Google Apps Script for Education

Intro

Since I’ve started working in a school, I’ve realised that there is huge potential for automation. There are many ed-tech tools out there, but the field is still lacking in many areas and much of what is out there suffers from good intentions but bad implementations. This has got me curious.

Before embarking on any large-scale market-disrupting projects, I decided it was prudent to work off the back of an already successful platform, Google Apps for Education. Whilst I am no big fan of Google (more on this another time), I do appreciate the value its offering brings to the school environment, and we use its services extensively. As the school librarian and all-round helper, I have been drawn to the promise of Google Apps Script, and it’s been extremely useful to automate a variety of tasks I do on a regular basis.

In a series of posts I’ll be adding here, I’ll be writing up some tutorials on how to use Google Apps Script, mainly in the educational context. This is useful if you’re just wanting to understand how Google Apps Script works or if you’re looking at creating some add-ons for the various Google tools. Some of what I show could be done in other ways or with other tools, but understanding the power of something like Google Apps Script can be beneficial to others and is always the start for grander plans and solutions.

Automatically Generating a Series of Google Documents and Sharing them with students

You have a class full of students. You assign them work. Each one needs to have their own document and it needs to be shared with you. Google Classroom is probably the right tool to use in this case, but let’s see how things might look if we did it ourselves using a very simple Google Apps Script.

1. The Spreadsheet

It’s useful to start with a class list. If you have a class list in a spreadsheet, you can use it to create many different things relevant to your situation. Take the following one, for example:

In the list we have 10 students, with each student’s name and their email address. We’ll create a script that runs through this list, creates a new Google Document for each student and then shares it to each of them. A simple but powerful script.

2. The Code

To write a Google Apps Script, you need to open up the Script Editor. This is found under the Tools menu:

Once you click on this, you’ll be presented with an Integrated Development Environment (IDE) where you can do your coding.

A few important things in this window. The big blank space is where you write your code. The dropdown menu allows you to select the function you’d like to run - in this case “myFunction”, the “play” button to the left of this is the button you hit to run the code, and under the Help menu is the API reference, which gives information about all the different classes and methods available in Google Apps Script.

Add the following code, replacing “myFunction” with “createAndShareDocs”. This is the function we’ll run to perform our tasks.

01.function createAndShareDocs() {
02. 
03.  var ss = SpreadsheetApp.getActiveSpreadsheet();
04.  var sh = ss.getActiveSheet();
05.  var r = sh.getRange("A1"); // see API reference for different ways to reference ranges
06.
07.}

Line 3 is how we get a reference to our entire Spreadsheet and line 4 is how we get a reference to our currently active sheet, where the student list is found. Line 5, then, is where the actual reference to individual cells or ranges of cells is made. This will be used all the time and is how you extract and place data.

So let’s see what we are trying to achieve here. We want to run through each of our rows, and for each of the rows, we want to create a new document and then share that document with the student, using the email listed next to the student name. We’ll use the student name as our document name. Of course, this can be changed in any way you want.

See updated code below:

01.function createAndShareDocs() {
02.  
03.  var ss = SpreadsheetApp.getActiveSpreadsheet();
04.  var sh = ss.getActiveSheet();
05.  
06.  var rowIndex = 2;
07.  var studentRange = sh.getRange("A" + rowIndex + ":B" + rowIndex); // there are a few different ways you could do this
08.  while (!studentRange.isBlank()) { // a while loop to run until we hit a blank row
09.    
10.    createAndShareDoc(studentRange.getValues()[0]); // access the first position of the 2-dimensional array - it refers to the row
11.    
12.    rowIndex++; // move to the next row
13.    studentRange = sh.getRange("A" + rowIndex + ":B" + rowIndex);
14.  }
15.  
16.}
17.
18.function createAndShareDoc(studentDetails) {
19.  Logger.log(studentDetails);
20.  
21.  var studentName = studentDetails[0]; // first position of the array contains the name
22.  var studentEmail = studentDetails[1]; // second position of the array contains the email
23.  
24.  var newDoc = DocumentApp.create(studentName);
25.  newDoc.addEditor(studentEmail);
26.}

We now have two functions. The first one to execute, which runs through a loop and executes the second function once for each student.

Line 8 is where our loop starts. This looks at the current row and checks if it is blank. Inside each loop, we get the values of our selected range (the current row), run the secondary function and then increment our index in order to move to the next row.

The function that does the document creation and sharing is quite simple. First, we extract the student details on line 21 and line 22. Then we create a new document using the student name on line 24. Finally, we share this document with that same student using their email address on line 25.

And that’s it! We’ve used a class list of students and with very little code, we’ve created a document for each student and shared it with them. It would make sense to place all of these files in a specific folder and also to make use of an initial template document, but the basics are covered.