Developing an Exceptional Circumstances system

Outline
One of my first big Google Apps Script projects was in the form of an Exceptional Circumstances system for a specific department. This is where students could request an extension to a module assessment which is then considered by several members of staff; comments and decisions also need to be recorded. In many cases extensions to several modules may be requested. In addition to a narrative for the reason for the request students may also need to be able to upload evidence documents.

Solution - Overview

• Students complete requests using a Google form.
• Request data populates row in an associated spreadsheet (standard with forms).
• Apps Script generates a Google doc (based on a template) from the submitted data, including links to evidence. This is automatically triggered when the form is submitted.
• The Url of the Google doc is written to an extra column on the Responses sheet as part of the script.
• Staff can add comments and narrative directly to the document (document history will log all updates).

Solution - Google Form (link)
This is the only part of the system students see and is used to collect the necessary information for the extension request. It collects details such as name, student number and evidence upload (as this is assumed to be generic for the one or more modules they submit). For example purposes I have reduced the form to two module submission which will be shown depending on if the student wants to include another module (in reality there were six).

Solution - Spreadsheet (link)
This is where the results of the form submissions are saved and where staff can easily access the Google doc for each student (added by the below apps script - which is embedded in this spreadsheet). It also includes a config sheet which holds a number of IDs that the script will need to reference - they have been included here so it is easier/safer for the user to update as required rather than accessing the script directly.

Solution - Apps Script
Takes the form responses and generates a document from a template (link) containing the request details. This document contains links to uploaded evidence, with space also for comments/recommendations by staff.

Apps Script - Breakdown
This section will take you through the code itself (embedded below) - even though I have already included comments directly in it to help you follow through. Firstly there are a number of previous blog posts I have written which will cover some aspects in greater detail:

• Copy a Doc and add paragraphs
• Email on Form submit
• Split file upload Url
• Set column Url link to Doc

The script starts by getting a number of items including the relevant sheets and document template (to copy). It then creates a Heading 1 style ('ParagraphHeading') which will be used for formatting the document later.

I then get the submitted form data (see Email on Form submit for further detail). At this point if a student has uploaded multiple evidence documents is it necessary to 'split' the Urls otherwise they will be presented as one long string - this is done by separating with a comma and space.

A new folder is created ('createFolder') on Google Drive with the name of the student, that will contain their uploaded evidence. Now the code loops through each piece of evidence to split the Url (see Split file upload Url for further detail). It can then 'getFileById' to look for where it currently exists ('getParents') - it does this before adding it to the new folder so it will not be removed from here too. It then goes through each parent to 'removeFile' so ultimately it now only exists in the new folder that has been created.

Now that I have all of the data I can make a copy of the document template and start editing the body (see Copy a Doc and add paragraphs for further detail). The body tags are replaced with the student's information, whilst a loop goes through adding the evidence Urls to a table (also making them clickable - 'setLinkUrl'). Another loop goes through adding the module information. Then the previously created Heading 1 style formats a section in the doc for staff comments ('setAttributes') which uses a loop based on the value entered in the config sheet for the number of comment boxes to create.

Finally, the script creates a link to this new document back on the spreadsheet (see Set column Url link to Doc for further detail). With all of this being activated by an On form submit trigger to run the function.

Here is an example of what part of the final document would look like:

Exceptional Circumstances example document

	function onFormSubmit(e) {
	// ******************************* get sheet data ******************************
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	var formResponse = ss.getSheetByName('Form Responses 3');
	var numCols = formResponse.getLastColumn();
	var configArray = ss.getSheetByName('config').getDataRange().getValues();
	Logger.log(configArray);
	// *************************** end of get sheet data ***************************
	// ******************************* get documents *******************************
	// get 'Request TEMPLATE' doc
	var docID = configArray[2][1];
	Logger.log('DocID: ' + docID);
	var templateDoc = DriveApp.getFileById(docID);
	// get destination folder
	var folderID = configArray[3][1];
	Logger.log('FolderID: ' + folderID);
	var destinationFolder = DriveApp.getFolderById(folderID);
	// create style based on 'Heading 1' for headings in Doc
	var style = {};
	style[DocumentApp.Attribute.HEADING] = DocumentApp.ParagraphHeading.HEADING1;
	// *************************** end of get documents ****************************
	// ******************************* get main form data ******************************
	var formData = e.values; // get row data as array
	var theRow = e.range.getRow(); // this is the row the Form data is written to
	Logger.log(formData);
	// get specific form fields
	var emailAddress = formData[1];
	Logger.log(emailAddress);
	var forename = formData[2];
	Logger.log(forename);
	var surname = formData[3];
	Logger.log(surname);
	var fullName = forename + ' ' + surname;
	Logger.log(fullName);
	var studentNumber = formData[4];
	Logger.log(studentNumber);
	var outline = formData[5];
	Logger.log(outline);
	var evidence = formData[6];
	Logger.log(evidence);
	var individEvidence = evidence.split(', '); // split up evidence URLs from single string
	// *************************** end of get main form data ***************************
	// ***************************** collate uploaded files ****************************
	var evidenceFolderID = configArray[5][1];
	var evidenceNewFolder = DriveApp.getFolderById(evidenceFolderID).createFolder(fullName);
	var createdFolderID = evidenceNewFolder.getId();
	Logger.log('Newly created folder ID is: ' + createdFolderID);
	for (var h=0; h<individEvidence.length; h++) {
	var split = individEvidence[h].split('='); // split URL to get just ID
	var uploadID = split[1]; // URL is second part of split
	Logger.log('Upload file ID is: ' + uploadID);
	var file = DriveApp.getFileById(uploadID);
	Logger.log('File is: ' + file);
	// get locations of where file currently exists
	var parents = file.getParents();
	// add file to new folder location (so is ignored from previously collated 'parents' and hence won't be removed from here)
	DriveApp.getFolderById(createdFolderID).addFile(file);
	// once file has moved, remove it from any other locations so only 1 copy left
	while (parents.hasNext()) {
	var parent = parents.next();
	parent.removeFile(file);
	}
	}
	// ********************* end of collate uploaded files ****************************
	// ******************************* edit document *******************************
	// copy template doc with student name
	var newDocURL = templateDoc.makeCopy(fullName, destinationFolder).getUrl();
	Logger.log('newDocURL: ' + newDocURL);
	var newDoc = DocumentApp.openByUrl(newDocURL); // get newly created doc
	var docBody = newDoc.getBody(); // get doc body
	// edit doc tags
	docBody.replaceText('<<Name>>', fullName);
	docBody.replaceText('<<studentNo>>', studentNumber);
	docBody.replaceText('<<email>>', emailAddress);
	docBody.replaceText('<<description>>', outline);
	// loop through each piece of evidence and append to table ******************
	var allTables = docBody.getTables(); // get all tables in doc
	var evidenceTable = allTables[1]; // get evidence table
	var tc = evidenceTable.getCell(0, 0); // get current cell
	// append paragraph for each evidence link and make clickable in doc
	for (var i=0; i<individEvidence.length; i++) {
	tc.appendParagraph(individEvidence[i]).setLinkUrl(individEvidence[i]);
	}// end of loop through each piece of evidence and append to table ***********
	tc.getChild(0).removeFromParent(); // remove first empty paragraph from table cell created by previous 'appendParagraph'
	// loop through module info and append to doc ********************************
	for (var j=7; j<numCols; j=j+5) {
	if (formData[j] != '') {
	var moduleTitle = formData[j];
	var moduleCode = formData[j+1];
	var deadline = formData[j+2];
	var extension = formData[j+3];
	docBody.appendParagraph('*******************************************************');
	docBody.appendParagraph('');
	docBody.appendParagraph('Module title: ' + moduleTitle);
	docBody.appendParagraph('Module code: ' + moduleCode);
	docBody.appendParagraph('Deadline: ' + deadline);
	docBody.appendParagraph('Extension: ' + extension);
	docBody.appendParagraph('');
	}
	}// end of loop through module info and append to doc ************************
	docBody.appendParagraph('*******************************************************');
	docBody.appendParagraph('Commentary').setAttributes(style); // create 'Commentary' heading
	// loop through creating user comment table **********************************
	var numComments = configArray[4][1]; // grabs value from 'config' allowing user to adjust in future
	Logger.log(numComments);
	for (var k=0; k<numComments; k++) {
	var commentTable = docBody.appendTable([['Name']]); // create new table for user comments with first row of text
	commentTable.getCell(0, 0).appendParagraph('Date');
	commentTable.getCell(0, 0).appendParagraph('Comments');
	commentTable.getCell(0, 0).appendParagraph('Recommendation');
	}// end of loop through creating user comment table ***************************
	docBody.appendParagraph('Final Decision').setAttributes(style); // create 'Final Decision' heading
	var decisionTable = docBody.appendTable([['Name']]); // create new table for user comments with first row of text
	decisionTable.getCell(0, 0).appendParagraph('Date');
	decisionTable.getCell(0, 0).appendParagraph('Comments');
	decisionTable.getCell(0, 0).appendParagraph('Decision');
	// ************************** end of edit documents ****************************
	// set 'URL' column with link to newly created doc
	formResponse.getRange(theRow, numCols).setFormula('=HYPERLINK("' + newDocURL + '","' + fullName + '")');
	}

view raw Exceptional Circumstances system.gs hosted with ❤ by GitHub