1 The Perils of Sharing

In teaching an introduction to the trigonometry ratios, I decided to use Google Sheets to amalgamate measurements from the class. Each student was to draw a right-angled triangle and measure the lengths of the sides and one of the angles (I prescribed the allowed angles for better further comparison, also they were to draw the triangles using Geogebra). They recorded this data in a shared Google Sheet.

Although it worked, and we got lots of data, there were a few teething problems with using the shared sheet, the most common being students trying to edit the same row. There were also a few occasions where one student would (unintentionally, I'm sure) edit another student's readings.

So for the second lesson using this sheet, I did a bit of searching and found that it was possible to lock down students' editing rights to specific cells in the sheet. Giving each student two or three rows in which to record their data, and locking them out from any other students' rows, seemed a reasonable precaution to take.

The irritation being having to do this by hand for each student.

2 Enter the Scrypt

A not-very-chance remark from a colleague led me to the discovery that Google Stuff (i.e., Google Docs, Google Sheets, and all the other Google bits and bobs) is scriptable. What made it particularly intriguing was that:

1. The language was javascript, and

2. The entire Google ecosystem was scriptable.

The first meant that I didn't have to learn a completely new language. I'd done a bit of javascript already so was familiar with its basic syntax and, more importantly, knew where to look for help. It took me a while to realise the ramifications of the second, but over time I've come to discover that I can use Google Apps Scripts pretty much like a UNIX command line and am able to work with documents as well as within documents.

For this specific purpose, it means that I can use one spreadsheet as my source data for building a new spreadsheet with the editing rights that I want.

3 Use the Source, Luke

To use this script, you need to start with a list of your class's Google account email addresses and their names. However you get these, you want them in a spreadsheet. Although it wouldn't be hard to adapt the following to any way of having the data in the sheet, I'll assume that they are so that the names are in Column A and the ids in Column B.

Another thing that is particularly useful to know before beginning is that in the Google ecosystem, files and folders are best identified by id rather than name. When you open a file or folder in Google, the URL in the location bar in your browser will read something like:

 https://docs.google.com/spreadsheets/d/some long string of letters, numbers, and other characters/edit#gid=0

The id is that long string of letters, numbers, and other characters. At various times you'll need that id for a particular document. Easiest way to get it is to have the document open in your browser and cut-and-paste it from the location bar.

Lastly, sometimes when sharing a document with another person then that person gets sent an email about it. So when testing scripts like this one it is best to start with a dummy list of users. Using a list with Google accounts that you own is best.

4 That's the Way to Do It

Possibly. This may not be the most elegant method. In particular, there's no fancy UI (user interface). But as a "script kiddie", I find it easier to edit a few key variables at the top of the script than to enter them via a UI.

4.1 Getting Off the Drive

The best starting point for all mucking about with Google is Google Drive. So open up your Google Drive in your browser.

If you don't already have "Google Apps Script" in the "New" menu, click on "New", select "More", and then "Connect more Apps". In the search box, type "Google Apps Script" and click the "connect" button. (If there isn't a "connect" button and it just says "Rate it" then you already have it.)

Now under "New" and then "More" you should have an entry "Google Apps Script". Click on that to open a new script. This opens up a script editor in your browser. You can click on the Untitled project to rename it to something sensible. In the text editor it should read:

function myFunction() {

}

We can delete that as we'll define a new function.

You should also have your spreadsheet with the students' names and emails open so that you can get the id.

4.2 It's All About the Script

Here's the script (you can also download it). Copy and paste this into the text editor in the browser.

function shareCells() {
  var studentId = ""; // id of spreadsheet with students' names and emails
  var folderId = ""; // id of folder where final spreadsheet should be put
  var sheetName = "Data Sheet"; // title of spreadsheet
  var studentsCell = 'A2'; // upper-left cell of student details block
  var editCell = 'A3'; // upper-left cell of range to be made editable (first column will contain names and not be editable)
  var editRows = 2; // number of rows per student
  var editCols = 4; // number of columns per student

  // Spreadsheet with students' usernames
  var studentSheet = SpreadsheetApp.openById(studentId);
  // Top left cell of student data
  var studentUL = studentSheet.getRange(studentsCell);
  // Array of student data
  var students = studentUL.offset(0,0,studentSheet.getLastRow() - studentUL.getRow()+1,2).getValues();

  // Array of just user emails
  var emails = [];
  for (var i=0; i<students.length; i++) {
    emails.push(students[i][1]);
  }

  // Create new spreadsheet
  var ss = SpreadsheetApp.create(sheetName);
  // Get the corresponding file
  var sfile = DriveApp.getFileById(ss.getId());
  // Get the current folder containing the file (this will be the root of your Drive folder)
  var root = sfile.getParents().next();
  // Add the file to the folder where it should be put
  DriveApp.getFolderById(folderId).addFile(sfile);
  // Remove it from the original folder
  root.removeFile(sfile);

  // Now set the global permissions, make it so that the students can't reshare this file
  sfile.setShareableByEditors(false);
  // Add all students as editors
  ss.addEditors(emails);
  // NB sharing via the SpreadsheetApp means that notification emails aren't sent, if you want to send emails
  // comment out the above line and use the following one instead
  // sfile.addEditors(emails);

  // Get the first (and only) sheet
  var sheet = ss.getSheets()[0];
  // The initial protection seals off the whole sheet apart from the region that will be editable by the students
  var protection = sheet.protect().setDescription('Protect entire sheet');
  // Get the top left cell of the editable region
  var editUL = sheet.getRange(editCell);
  // Get the full editable range
  // The first column contains the names (and is not editable) so we offset by 1 horizontally
  var editRange = editUL.offset(0,1,editRows*students.length,editCols);
  // Our protected range is everything except this area
  protection.setUnprotectedRanges([editRange]);
  // Remove all students from being able to edit the protected range
  protection.removeEditors(emails);

  // Now we loop through the students, giving each access to a block
  var name, email, cell, range;
  for (var i=0; i<students.length; i++) {
    // Convenience storage for the student's name and email
    name = students[i][0];
    email = students[i][1];
    // Get the cell where the name will go
    cell = editUL.offset(i*editRows,0,1,1);
    // And put the name there
    cell.setValue(name);
    // Now get the range where they'll be able to edit
    range = cell.offset(0,1,editRows,editCols);
    // Add a protection to that range
    protection = range.protect();
    // Remove all students' edit rights
    protection.removeEditors(emails);
    // But add this student's rights
    protection.addEditor(email);
    // Stick a border around it to make it obvious where it is
    range.setBorder(true,true,true,true,false,false);
  }
}

The configuration details are at the start.

• studentId This is the id of the spreadsheet containing the students' details.

• folderId This is the id of the folder where the new spreadsheet should be created.

• sheetName This is the name of the new spreadsheet.

• studentsCell This is the cell at the top left of the students' data. You might want a header row, or the data might be part of a larger set of information. The first column of the data should contain the names, and the second the emails.

• editCell This is the cell at the top left of the region that will be editable. In actual fact, the first column will not be editable as it will contain the students' names.

• editRows This is the number of rows of a students' editable block.

• editCols This is the number of columns of a students' editable block.

4.3 I Invoke Thee, O Scrypt

Running the script is straightforward. Save the script by clicking on the usual file save icon. (You can see when a file needs saving by looking at its name above the code; if there is a red star next to it then it has unsaved changes.)

To the right of the icons is a function name. If that is not showing shareCells, click on the drop down arrow and select it from the list. You can then run the script by pressing the black triangle in the icon bar. Or you can select "Run" from the menu list.

The students can access the sheet via their Drives in the "Shared with me" folder.