Social Work Digital Portfolio – Google Docs Style


Image from page 589 of “Modern magic. : A practical treatise on the art of conjuring.” (1885) flickr photo by Internet Archive Book Images shared with no copyright restriction (Flickr Commons)

Our Social Work program has traditionally done large paper-based student portfolios. They wanted to move to something digital. That led to some conversations about Google Docs and our options there. They needed the ability to-

  • provision a set of folders and documents to individual students
  • allow the students to edit/add to the folders
  • stop editing rights at a certain date
  • make the student folder anonymous for faculty reviewers

The solution I ended up coming up with uses a Google Spreadsheet with some custom Google Script.

It’s based on a spreadsheet with column A being the student email and column B being the anonymous number or name.

You make a parent folder (Social Work 2017) and put the spreadsheet and the folder (student portfolio) you want to copy in the folder.

You add the student emails and secret IDs to the spreadsheet. The script is activated via custom-menu element imaginatively entitled ‘Share Files’ and it copies the student portfolio for each student email listed, names it with the addition of the secret ID, and gives the student editor rights to their particular folder and its contents. It also writes the URL to the spreadsheet in column C to provide an index to the content.

When the submission period has ended, clicking ‘Un-Share Files’ removes the student’s rights to edit/view etc. This could be refined in a few ways if desired.

The script to do this stuff is included in whole at the bottom (with comments) but I’ll breakdown a few of the pieces so it’ll make a bit more sense. I may be over-explaining but hopefully that’s a good thing.

Add Menu Elements

function onOpen() {
  SpreadsheetApp.getUi() 
      .createMenu('Social Work Portfolio')
      .addItem('Share Files', 'shareFiles')
      .addItem('Un-Share Files', 'unshareFiles')
      .addToUi();
}

This lets you add elements to the spreadsheet’s menu. It makes things far easier for sharing with a diverse audience.

It’s pretty straight forward- .createMenu makes our main title and the subsequent .addItem pieces add the dropdown elements.

With .addItem there are value pairs. The first element is the displayed text (Share Files) and the second piece references the function you created in the Google Script (shareFiles).

The final .addToUi actually integrates what you just made into the user interface (Ui).

The Copying

  var ss = SpreadsheetApp.getActiveSpreadsheet(); //getting data sheet & expecting col A = email address & col B = secret name, writes URL to col C
  var sheet = ss.getActiveSheet();
  var stuIds = sheet.getDataRange().getValues();

Step one is to get the data we need from the spreadsheet. Lines 1, 2, and 3 in the example above first recognize the active spreadsheet, the active sheet in that spreadsheet, and then values of cells with values. In this case, that’s going to be the student emails in column A and their secret IDs in column B.

  var num = stuIds.length;

The second line is going to take stuIds and count how many items it has. That determines how many times we will need to copy/share our folder structure.

for (i = 1; i < num; i++) {
    var src = DriveApp.getFoldersByName('student portfolio');//manually set the folder you want to copy
    var stuEmail = stuIds[i][0];
    var stuSecret = stuIds[i][1];
    var newStuFolder = folder.createFolder(stuSecret + ' Social Work Portfolio');    
    
    var stuFolderId = newStuFolder.getId();
    var stuFolderUrl = newStuFolder.getUrl();
    
    sheet.getRange('C'+[i+1]).setValue(stuFolderUrl);//writes folder URL to the spreadsheet
    sheet.getRange('D'+[i+1]).setValue(stuFolderId);
    
    newStuFolder.addEditor(stuEmail);//adds student as editor
    newStuFolder.setShareableByEditors(false);
    
    if (src.hasNext()){
      copyFolder(src.next(), newStuFolder);
    }      
  }  

This is a pretty typical for loop.1

Line two here gets a folder by name. It’s probably cleaner to do this via ID but for our purposes this works ok. You could also make it more user friendly by allowing this be defined via a specific cell on the spreadsheet.

Lines 3 and 4 are getting the student email and secret ID from the spreadsheet. i (our loop counter) is going to shift you down the rows with each loop and the second square bracket is effectively selecting the column with 0 = data from column A and 1 = data from column B.

The 5th line creates the new folder and names it with the student secret ID and then ‘ Social Work Portfolio.’

Now that it exists, we want the ID of that new student folder and we’ll also grab the direct URL to it (lines 7 and 8) and we’ll write those data to columns C and D respectively. I hid column C because it won’t be used by the social work group but we’ll use it later to un-share things.

The next line pair gives editor rights to the student via the email defined in the spreadsheet but also prevents them from further sharing it.

Finally, I use ctrlq.org’s folder copying function to copy all the folders and files into our new student folder.

Un-Sharing

function unshareFiles() {  
  var ss = SpreadsheetApp.getActiveSpreadsheet(); //getting data sheet & expecting col A = email address & col B = secret name, writes URL to col C
  var sheet = ss.getActiveSheet();
  var folderIds = sheet.getDataRange().getValues();
  var num = folderIds.length;
  
  for (i = 1; i < num; i++){
    var folderId = folderIds[i][3];
    var folder = DriveApp.getFolderById(folderId);
    var editors = folder.getEditors();
    
    folder.setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.NONE);  
    folder.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.NONE); 
    
    for (var a = 0; a < editors.length; a++) {
     folder.removeEditor(editors[a]);
    };
  }
}

Once again, we’re making sure we’re using the right sheet of the right spreadsheet.

In this case we just want the IDs of the folders that we wrote to column D earlier and we’re going to loop through them all removing the students (and anyone else- just in case) as editors and setting any other permission I could think of to protected.

function onOpen() {
  SpreadsheetApp.getUi() 
      .createMenu('Social Work Portfolio')
      .addItem('Share Files', 'shareFiles')
      .addItem('Un-Share Files', 'unshareFiles')
      .addToUi();
}


function shareFiles() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); //getting data sheet & expecting col A = email address & col B = secret name, writes URL to col C
  var sheet = ss.getActiveSheet();
  var stuIds = sheet.getDataRange().getValues();
  
  var ssId = ss.getId();
  var driveFile = DriveApp.getFileById(ssId); 
 
  var parentFolder = driveFile.getParents();
  var folder = DriveApp.getFolderById(parentFolder.next().getId()); 
  
  var num = stuIds.length;
  
  
  for (i = 1; i < num; i++) {
    var src = DriveApp.getFoldersByName('student portfolio');//manually set the folder you want to copy
    var stuEmail = stuIds[i][0];
    var stuSecret = stuIds[i][1];
    var newStuFolder = folder.createFolder(stuSecret + ' Social Work Portfolio');    
    
    var stuFolderId = newStuFolder.getId();
    var stuFolderUrl = newStuFolder.getUrl();
    
    sheet.getRange('C'+[i+1]).setValue(stuFolderUrl);//writes folder URL to the spreadsheet
    sheet.getRange('D'+[i+1]).setValue(stuFolderId);
    
    newStuFolder.addEditor(stuEmail);//adds student as editor
    newStuFolder.setShareableByEditors(false);
    
    if (src.hasNext()){
      copyFolder(src.next(), newStuFolder);
    }      
  }  
}


//https://ctrlq.org/code/19979-copy-folders-drive 

function copyFolder(source, target) {
 
  var folders = source.getFolders();
  var files   = source.getFiles();
  
  while(files.hasNext()) {
    var file = files.next();
    file.makeCopy(file.getName(), target);
  }
  
  while(folders.hasNext()) {
    var subFolder = folders.next();
    var folderName = subFolder.getName();
    var targetFolder = target.createFolder(folderName);
    copyFolder(subFolder, targetFolder);
  }  
  
}

function unshareFiles() {  
  var ss = SpreadsheetApp.getActiveSpreadsheet(); //getting data sheet & expecting col A = email address & col B = secret name, writes URL to col C
  var sheet = ss.getActiveSheet();
  var folderIds = sheet.getDataRange().getValues();
  var num = folderIds.length;
  
  for (i = 1; i < num; i++){
    var folderId = folderIds[i][3];
    var folder = DriveApp.getFolderById(folderId);
    var editors = folder.getEditors();
    
    folder.setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.NONE);  
    folder.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.NONE); 
    
    for (var a = 0; a < editors.length; a++) {
     folder.removeEditor(editors[a]);
    };
  }
}


1 Although this is making me think about even simple things more deeply.

2 thoughts on “Social Work Digital Portfolio – Google Docs Style

Comments are closed.