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:
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 |
No comments:
Post a Comment