https://www.pbainbridge.co.uk/2018/12/developing-exceptional-circumstances.html

Exceptional Circumstances system.gs

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 + '")');
  
}