Google Form to Script to Make Folder, Document, Share it etc.

My click bait titles are so powerful!

This isn’t anything special but it does show a couple of Google Script patterns that we reuse all the time. Folders are made in other folders! Documents are made in those folders. Things are shared. Ownership is assigned! So many things.

The following script takes a form submission and then . . .

  • makes a folder in a particular folder, shares it with particular people, sets the ownership
  • makes a document within that sub-folder, shares it with particular people, sets the ownership
  • adds them as a viewer on a different folder
  • deals with some issues VCU has with students having both @vcu.edu and @mymail.vcu.edu email addresses but without them behaving as if they are the same in Google

Details are in the comments. The trigger is on form submission.

function onFormSubmission(){
   var sheet = SpreadsheetApp.getActiveSheet();//get the sheet where we are writing the form data
   var rows = sheet.getDataRange();//get the entries
   var lastRow = rows.getLastRow();//get the most recent entry  
   var email = sheet.getRange(lastRow,5).getValue();//get the email column 
   var atSymbol = email.search('@');//find the @ symbol in the email
   var cleanEmail = email.substring(0,atSymbol) //chop off the first portion of the email
   var lastName = sheet.getRange(lastRow,3).getValue();//get their last name from the SS
   addStudentToResourceFolder(cleanEmail);
   makeStudentFolder(cleanEmail, lastName);
}


function addStudentToResourceFolder(emailClean){
  var id = 'YOUR_FOLDER_ID';//base resource folder id
  var folder = DriveApp.getFolderById(id);//get that folder
  folder.addViewers([emailClean + '@vcu.edu', emailClean + '@mymail.vcu.edu']);//add student email versions as viewer
}

function makeStudentFolder(emailClean, lastName){
  var holderId = '18KRAbz6ag3v5IiCc31Acs7pkz076Vds1';//get the top level holder folder 
  var draftId = createFolderBasic(holderId, lastName+'_Essay Drafts');//create new sub folder
  var draftFolder = DriveApp.getFolderById(draftId);//get that sub folder
  draftFolder.setOwner('SOMEONE@vcu.edu');//set the owner to someone who is not me
  draftFolder.addEditors([emailClean + '@vcu.edu', emailClean + '@mymail.vcu.edu']);//add student as editor
  var document = DocumentApp.create(lastName +' essay draft'); //create document
  DriveApp.getFileById(document.getId()).addEditors([emailClean + '@vcu.edu', emailClean + '@mymail.vcu.edu']); //add editors
  DriveApp.getFolderById(draftId).addFile( DriveApp.getFileById(document.getId()) );//put doc in folder
   DriveApp.getFileById(document.getId()).setOwner('SOMEONE@vcu.edu');//set the owner of teh doc
}

function createFolderBasic(folderID, folderName) {
  var folder = DriveApp.getFolderById(folderID);
  var newFolder = folder.createFolder(folderName);
  return newFolder.getId();
};