public
Created

Fast Tracking Feedback System v.2 Code

  • Download Gist
DocumentCreation.js
JavaScript
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271
// generate drafts of all documents
function generateAllDocs(){
var data = getRowsData(FEEDSHEET, FEEDSHEET.getRange(2,1,FEEDSHEET.getLastRow(),FEEDSHEET.getLastColumn()),1); // get all the feedback data
createDocFromSheet(data,2);
}
 
// generate drafts for slected documents
function generateSelectedDocs(){
var sel = DOC.getActiveSelection();
if (sel.getRow()==1){
Browser.msgBox("Selection should not include the header");
return;
}
var data = getRowsData(FEEDSHEET, FEEDSHEET.getRange(sel.getRow(),1,sel.getNumRows(),FEEDSHEET.getLastColumn()),1); // get all the feedback data
createDocFromSheet(data, sel.getRow());
}
 
// release all documents to students
function releaseAllDocs(){
var data = getRowsData(FEEDSHEET, FEEDSHEET.getRange(2,2,FEEDSHEET.getLastRow(),NAME_START_COL),1);
releaseDocsFromSheet(data, 2);
}
 
// release selected documents to students
function releaseAllDocsWithEmail(){
var data = getRowsData(FEEDSHEET, FEEDSHEET.getRange(2,2,FEEDSHEET.getLastRow(),NAME_START_COL),1);
releaseDocsFromSheet(data, 2, true);
}
 
function releaseSelectedDocs(){
var sel = DOC.getActiveSelection();
if (sel.getRow()==1){
Browser.msgBox("Selection should not include the header");
return;
}
var data = getRowsData(FEEDSHEET, FEEDSHEET.getRange(sel.getRow(),2,sel.getNumRows(),NAME_START_COL),1);
releaseDocsFromSheet(data, sel.getRow());
}
 
function releaseSelectedDocsWithEmail(){
var sel = DOC.getActiveSelection();
if (sel.getRow()==1){
Browser.msgBox("Selection should not include the header");
return;
}
var data = getRowsData(FEEDSHEET, FEEDSHEET.getRange(sel.getRow(),2,sel.getNumRows(),NAME_START_COL),1);
releaseDocsFromSheet(data, sel.getRow(),true);
}
 
function releaseDocsFromSheet(data, startRow,optEmailConfirm){
if (CRITHEADER==undefined){
Browser.msgBox("Please run the Setup first");
return;
} else {
if (optEmailConfirm != undefined){
var emailTemplate = INFOSHEET.getRange("A11").getValue();
}
for (i in data){
if(data[i].feedbackLink != "" && data[i].status == "Draft" && data[i].email != ""){
var file = DocsList.getFileById(data[i].feedbackLink);
file.addViewer(data[i].email);
var folder = DocsList.getFolder(ROOT_FOLDER+"/"+RELEASED_FOLDER);
file.addToFolder(folder);
var folder = DocsList.getFolder(ROOT_FOLDER+"/"+DRAFT_FOLDER);
file.removeFromFolder(folder);
try {
if (optEmailConfirm != undefined){
data[i].assignmentTitle = ASSIGN_TITLE;
data[i].url = "http://docs.google.com/a/student.loucoll.ac.uk/open?id="+data[i].feedbackLink;
var emailText = fillInTemplateFromObject(emailTemplate, data[i]);
var emailSubject = "Feedback for "+ASSIGN_TITLE;
MailApp.sendEmail(data[i].email, emailSubject, emailText, { htmlBody: wiki2html(emailText)});
}
} catch(e){
Browser.msgBox("There was a problem sending to "+data[i].email+ " Error :"+e);
}
FEEDSHEET.getRange("C"+(parseInt(i)+startRow)).setValue("Released");
}
}
DOC.toast("Finished releasing feedback sheets");
}
}
 
function createDocFromSheet(data, startRow){
if (CRITHEADER==undefined){
Browser.msgBox("Please run the Setup first");
return;
} else {
var templateid = TMP_DOC; // get template file id
var normHeaderString = NORMHEADER.join(",");
var FOLDER_NAME = DocsList.getFolder(ROOT_FOLDER+"/"+DRAFT_FOLDER); // folder name of where to put completed diaries
var file_prefix = ASSIGN_TITLE; // file name prefix
var course = COURSE;
var unit = UNIT;
var assignTitle = ASSIGN_TITLE;
// get all the criteria for this course/unit
var crit = getCachedCritEntry(course, unit);
var critString = ""
for (c in crit){
critString += c+": ";
}
var critSorted = getSortedCriteria(critString);
// Assessment Criteria Table Styling
var styleCrit1 = {};
styleCrit1[DocumentApp.Attribute.SPACING_AFTER] = 0;
styleCrit1[DocumentApp.Attribute.LINE_SPACING] = 1;
styleCrit1[DocumentApp.Attribute.FONT_SIZE] = 11;
var styleCrit2 = {};
styleCrit2[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] = DocumentApp.HorizontalAlignment.CENTER;
var styleCrit3 = {};
styleCrit3[DocumentApp.Attribute.BOLD] = true
var styleAssign = {};
styleAssign[DocumentApp.Attribute.SPACING_AFTER] = 0;
styleAssign[DocumentApp.Attribute.LINE_SPACING] = 1;
styleAssign[DocumentApp.Attribute.FONT_SIZE] = 11;
styleAssign[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] = DocumentApp.HorizontalAlignment.CENTER;
// temporay copy of template and customise it for this course/unit/assignment
// Get document template, copy it as a new temp doc, and save the Doc's id
var newTemplateId = DocsList.getFileById(templateid)
.makeCopy("TMP"+normalizeHeader(course)+"-"+normalizeHeader(unit))
.getId();
var tmpDoc = DocumentApp.openById(newTemplateId);
var tmpBody = tmpDoc.getActiveSection();
// handle criteria tables
var tmpBodyTables = tmpDoc.getTables();
var count = 0;
var assignTbl = tmpBodyTables[4].getRow(0);
// Assessment Criteria
for (i in critSorted){
var c = critSorted[i];
if (c != "course" && c != "unit"){
var colour = "#f3f3f3";
switch(c.substring(0,1))
{
case "p":
colour = PASS_COLR;
break;
case "m":
colour = MERIT_COLR;
break;
case "d":
colour = DIST_COLR;
break;
}
// add criteria to second table
// Assignment Criteria Table
if (count === 0){
var critBar = assignTbl.getCell(0).setText(c.toUpperCase());
} else {
var critBar = assignTbl.appendTableCell().setText(c.toUpperCase());
}
critBar.setBackgroundColor(colour).getChild(0).setAttributes(styleAssign);
if (normHeaderString.indexOf(c) != -1){ // if criteria is one of the sheet header columns add to table
var newRow = tmpBodyTables[2].appendTableRow();
var col1 = newRow.appendTableCell().setText(crit[c]).setBackgroundColor(colour);
col1.getChild(0).setAttributes(styleCrit1);
var col2 = newRow.appendTableCell().setText(c.toUpperCase()).setBackgroundColor(colour);
col2.getChild(0).setAttributes(styleCrit1).setAttributes(styleCrit2);
var col3 = newRow.appendTableCell().setText("{%"+c.toUpperCase()+"%}");
col3.getChild(0).setAttributes(styleCrit1).setAttributes(styleCrit2);
} else {
critBar.setBackgroundColor("#f3f3f3")
}
count ++;
}
}
tmpDoc.saveAndClose();
for (var i in data){ // setup loop for all the data
var row = data[i]; // get just one row of feedback
var email=row.email; // get email (email address of student)
// loop through common form data and add it to row values
//for (var a in commonData){
row["course"] = COURSE;
row["unit"] = UNIT;
row["assignmentTitle"] = ASSIGN_TITLE;
//}
// if feedback hasn't already been generated (checked by existence of document link) generate feedback
if (email && (row.feedbackLink =="" || row.feedbackLink == undefined)){
// Get document template, copy it as a new temp doc, and save the Doc's id
var copyId = DocsList.getFileById(newTemplateId)
.makeCopy(file_prefix+" - "+email)
.getId();
var copyDoc = DocumentApp.openById(copyId);
// add student as viewer - waiting for google to add addCommenter
// copyDoc.addViewer(email) ;
// move doc to tutors folder
var file = DocsList.getFileById(copyId);
var folder = DocsList.getFolder(ROOT_FOLDER+"/"+DRAFT_FOLDER);
file.addToFolder(folder);
// select the document body
var copyBody = copyDoc.getActiveSection();
// find edittable parts of the document
var keys = createKeys(copyDoc);
// loop through elements replacing text with values from spreadsheet
for (var j in keys) {
var text = keys[j].text;
var replacementText = ""; // set the default replacement text to blank
if (row[keys[j].id] != undefined){ // if column value is defined get text
//if (keys[j].id.slice(-4)=="Date"){
// replacementText = Utilities.formatDate(row[keys[j].id], "GMT", "dd/MM/yy");
//} else {
replacementText = row[keys[j].id];
//}
}
copyBody.replaceText('{%'+keys[j].text+'%}', replacementText); // replace text
}
copyDoc.saveAndClose();
// create a link to the document in the spreadsheet
FEEDSHEET.getRange("B"+(parseInt(i)+startRow)).setFormula('=HYPERLINK("'+file.getUrl()+'", "'+copyId+'")');
FEEDSHEET.getRange("C"+(parseInt(i)+startRow)).setValue("Draft");
//DOC.toast("Feedback generated for "+row.learnerName);
// you can do other things here like email a link to the docuemnt to the student
}
}
// Delete temporay assignment template
var tmpFile = DocsList.getFileById(newTemplateId);
tmpFile.setTrashed(true);
DOC.toast("Finished generating feedback sheets");
}
}
 
// From James Ferreira's Google Script: Enterprise Application Essentials
// function returns an array of unique form keys
function createKeys(templateFile){
var templateTxt = getTemplateText(templateFile);
var templateVars = templateTxt.match(/\{\%[^\%]+\%\}/g);
var templateKeys = [];
var oneEach = "";
for (var i in templateVars) {
var keyObject = {};
keyObject.text = templateVars[i].replace(/\{\%|\%\}/g, '');
keyObject.id = normalizeHeader(keyObject.text);
if (oneEach.match(keyObject.text) == null){
templateKeys.push(keyObject);
}
oneEach += " " + keyObject.text;
}
return templateKeys;
}
// From James Ferreira's Google Script: Enterprise Application Essentials
/*
* @returns body text from doc
*/
function getTemplateText(file){
var output = "";
for (var i = 0; i < file.getNumChildren(); i++) {
output += file.getChild(i).asText().getText();
}
return output;
}
EntryForm.js
JavaScript
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379
// function to create data entry form
function doForm() {
if (CRITHEADER==undefined){ // only do if setup has been run
Browser.msgBox("Please run the Setup first");
return
} else {
var app = UiApp.createApplication().setWidth(720).setHeight(560);//.setTitle("Feedback Entry Form");
app.add(app.loadComponent("entryForm")); // load entryForm from GUI Builder
// create a table for course/unit criteria
var assCritPanel = app.getElementById("assCritPanel");
var critTable = app.createFlexTable().setStyleAttribute("border","1px solid #ccc").setStyleAttribute("borderCollapse", "collapse").setCellPadding(3).setWidth("100%");
assCritPanel.add(critTable);
// set up some local variables
var formLabel = app.getElementById("formLabel");
var course = COURSE;
var unit = UNIT;
var assignTitle = ASSIGN_TITLE;
var count = 0;
// get unit/course criteria ids/descriptors
var crit = getCachedCritEntry(course, unit);
formLabel.setText(course+": "+unit);
// loop across each criteria id in spreadhseet
for (c in CRITHEADER){
if (CRITHEADER[c]!=""){
// create a criteria colour switch
var colour = "#f3f3f3";
switch(CRITHEADER[c].substring(0,1))
{
case "p":
colour = PASS_COLR;
break;
case "m":
colour = MERIT_COLR;
break;
case "d":
colour = DIST_COLR;
break;
}
// in criteria table set criteria descriptor, criteria id, Yes/No/NA/Err select list
critTable.setText(count, 0, crit[CRITHEADER[c]]).setStyleAttribute(count, 0, "background", colour).setStyleAttribute(count, 0, "border","1px solid #ccc");
critTable.setText(count, 1, CRITHEADER[c].toUpperCase()).setStyleAttribute(count, 1, "background", colour).setStyleAttribute(count, 1, "border","1px solid #ccc");
var critSelect = app.createListBox().setId(CRITHEADER[c]).setTabIndex(count+12).setName(CRITHEADER[c]);
critSelect.addItem("Y");
critSelect.addItem("N");
critSelect.addItem("NA");
critSelect.addItem("");
critTable.setWidget(count, 2, critSelect).setStyleAttribute(count, 2, "border","1px solid #ccc");
count ++;
}
}
// Create tabbed feedback text panel for: Tutor feedback, Areas for development, Strengths and English & Maths Skills
var feedbackPanel = app.getElementById("feedbackPanel");
var feedbackTab = app.createDecoratedTabPanel().setWidth("690px").setHeight("135px");
var tutorFeedback = app.createTextArea()
.setId("tutorFeedback")
.setName("tutorFeedback")
.setWidth("670px")
.setHeight("120px")
.setTabIndex(count+13);
var devFeedback = app.createTextArea()
.setId("areasForDevelopment")
.setName("areasForDevelopment")
.setWidth("670px")
.setHeight("120px")
.setTabIndex(count+14);
var strengthsFeedback = app.createTextArea()
.setId("strengths")
.setName("strengths")
.setWidth("670px")
.setHeight("120px")
.setTabIndex(count+15);
var litFeedback = app.createTextArea()
.setId("englishAndMathsSkills")
.setName("englishAndMathsSkills")
.setWidth("670px")
.setHeight("120px")
.setTabIndex(count+16);
//Add all the feedback panels to the tabpanel
feedbackTab.add(tutorFeedback, 'Tutor')
.add(devFeedback, 'Dev. Areas')
.add(strengthsFeedback, 'Strengths.')
.add(litFeedback, 'English/Maths');
feedbackTab.selectTab(0);
feedbackPanel.add(feedbackTab);
// add click handler to the student select dropdown
var learnerList = app.getElementById("rownum");
var listClickHandler = app.createServerClickHandler('getRecord');
listClickHandler.addCallbackElement(learnerList);
learnerList.addClickHandler(listClickHandler);
var listKeyHandler = app.createServerKeyHandler('getRecord');
learnerList.addKeyPressHandler(listKeyHandler);
// add client handler to loading image for when recorded is paged from student slect list
var loadingImg = app.getElementById("loadingImg");
var loading = app.createClientHandler()
.forTargets(loadingImg).setVisible(true);
learnerList.addChangeHandler(loading);
// populate student select list with names
learnerList.addItem("-Select Student-");
// make a select list of users
var rows = FEEDSHEET.getRange(2,NAME_START_COL,FEEDSHEET.getLastRow(),2).getValues();
var items=0;
for (i in rows){
if(rows[i][0] != undefined && rows[i][0]!=""){
learnerList.addItem(rows[i][0]+" ("+rows[i][1]+")",parseInt(i)+2);
items++;
}
}
if (items > 0) learnerList.setSelectedIndex(1);
var flowpanel = app.getElementById("flowpanel");
var emailField = app.createHidden().setId("email").setName("email");
flowpanel.add(emailField);
// add click handlers to record paging buttons
var firstBut = app.getElementById("firstBut").addClickHandler(loading);
var prevBut = app.getElementById("prevBut").addClickHandler(loading);
var nextBut = app.getElementById("nextBut").addClickHandler(loading);
var lastBut = app.getElementById("lastBut").addClickHandler(loading);
// select and set current record numbers text eg 1 of 20
var recCount = app.getElementById("recCount");
var recTotal = app.getElementById("recTotal");
recCount.setText("0");
recTotal.setText(items);
// record total number of records used later
ScriptProperties.setProperty("records", items);
// if no records disable next/last record buttons
if (items<=0){
nextBut.setEnabled(false);
lastBut.setEnabled(false);
} else {
getRecord(); // if there are records fill the form with the first one
}
firstBut.setEnabled(false);
prevBut.setEnabled(false);
// add server and client handlers to the save button
var saveBut = app.getElementById("saveBut");
var savingImg = app.getElementById("savingImg");
var saving = app.createClientHandler()
.forTargets(savingImg).setVisible(true)
.forEventSource().setEnabled(false);
saveBut.addClickHandler(saving);
if (items > 0) {
learnerList.setSelectedIndex(1);
} else {
saveBut.setEnabled(false);
}
DOC.show(app);
}
}
 
// function to get a student record and fill form with values
function getRecord(e){
var app = UiApp.getActiveApplication();
var totRec = parseInt(ScriptProperties.getProperty("records"));
// if e is undefined it's the first record else get the rownum from the student select list
if (e == undefined){
var rownum = 2;
} else {
var rownum = e.parameter.rownum;
}
if(rownum != "-Select Student-"){ // if student select is not the default do something
// get data for the student
var rowRange = FEEDSHEET.getRange(rownum, 1, 1, FEEDSHEET.getLastColumn()-1);
var recCount = app.getElementById("recCount").setText(rownum-1)
var row = getRowsData(FEEDSHEET,rowRange,1)[0]; // get the data
// for each of the spreadsheet column headers set the equivalent form value by matching normalised names
for (i in NORMHEADER){
if (NORMHEADER[i]!="timestamp" && NORMHEADER[i]!="feedbackLink" && NORMHEADER[i]!="status"){
// if the row data for a column heading is defined
if (row[NORMHEADER[i]] != undefined){
// if column header is less than 3 characters long its a criteria so handle as a select list entry
if (NORMHEADER[i].length<=3){
app.getElementById(NORMHEADER[i]).setSelectedIndex(getIndex(row[NORMHEADER[i]]));
} else if (NORMHEADER[i] == "email"){
// if its email column set value of hidden field
app.getElementById(NORMHEADER[i]).setValue(row[NORMHEADER[i]]);
} else {
// otherwise set text in textfield oor textarea
app.getElementById(NORMHEADER[i]).setText(row[NORMHEADER[i]]);
}
} else {
// if row data for column is not defined it means cell is empty (getRowsData only returns is not null values)
if (NORMHEADER[i].length<=3){
// if column header is less than 3 characters long its a criteria so handle as a select list entry and set to Yes
app.getElementById(NORMHEADER[i]).setSelectedIndex(3);
} else {
// else set as blank
app.getElementById(NORMHEADER[i]).setText("");
}
}
// if it's the assessor column get user email
if (NORMHEADER[i]=="assessor" && row[NORMHEADER[i]]==undefined){
app.getElementById(NORMHEADER[i]).setText(Session.getActiveUser().getEmail());
}
}
}
}
// handle the first/prev/next/last butons
var firstBut = app.getElementById("firstBut");
var prevBut = app.getElementById("prevBut");
var nextBut = app.getElementById("nextBut");
var lastBut = app.getElementById("lastBut");
// if first record disable first/prev button
if (rownum-1 <= 1){
firstBut.setEnabled(false);
prevBut.setEnabled(false);
if (totRec>1){
// if there are records enable next/last
nextBut.setEnabled(true);
lastBut.setEnabled(true);
}
}
// if records before or after enable all buttons
if(rownum-1 >1 && rownum-1 < totRec) {
firstBut.setEnabled(true);
prevBut.setEnabled(true)
nextBut.setEnabled(true);
lastBut.setEnabled(true);
}
// if last record disable next/last buttons
/*
if (rownum-1 >= totRec ){
firstBut.setEnabled(true);
prevBut.setEnabled(true);
nextBut.setEnabled(false);
lastBut.setEnabled(false)
}
*/
app.getElementById("loadingImg").setVisible(false);
return app;
}
 
// function to save entry form values
function setRecord(e){
var app = UiApp.getActiveApplication();
// get the row rumber of the students record
var rownum = parseInt(e.parameter.rownum-1);
// move the active cell to a1
var cell = FEEDSHEET.getRange('A1');
var col = 0;
// for each of the headers request the form field value
for (i in NORMHEADER){
var val =""
if (NORMHEADER[i] == "timestamp"){ // add a timestamp for the last edit
val = new Date();
} else {
val = alltrim(e.parameter[NORMHEADER[i]]); // trim whitespace from the form field value
}
if (val == undefined) val ="";
// start on left hand side and move accross the row filling in the values
if (col === 0 ||col>2){
cell.offset(rownum, col).setValue(val);
}
col++;
}
// if save and next record checkbox selected
if(e.parameter.saveAndNext=="true" || e.parameter.recTotal==e.parameter.recCount){
// then if last record close
if (e.parameter.recTotal==e.parameter.recCount){
app.close();
} else {
// otherwise page next record
recordNext(e);
}
}
// renable save button, hide loading/saving image
var saveBut = app.getElementById("saveBut").setEnabled(true);
app.getElementById("savingImg").setVisible(false);
return app;
}
 
// page first record
function recordFirst(e){
var app = UiApp.getActiveApplication();
var learnerList = app.getElementById("rownum");
e.parameter.rownum = 2;
learnerList.setSelectedIndex(1);
getRecord(e);
return app;
}
 
// page previous records
function recordPrev(e){
var app = UiApp.getActiveApplication();
var learnerList = app.getElementById("rownum");
var i = parseInt(e.parameter.recCount)-1;
e.parameter.rownum = i+1;
learnerList.setSelectedIndex(i);
getRecord(e);
return app;
}
 
// page next record
function recordNext(e){
var app = UiApp.getActiveApplication();
var learnerList = app.getElementById("rownum");
var i = parseInt(e.parameter.recCount)+1;
e.parameter.rownum = i+1;
learnerList.setSelectedIndex(i);
getRecord(e);
return app;
}
 
// page last record
function recordLast(e){
var app = UiApp.getActiveApplication();
var learnerList = app.getElementById("rownum");
var i = parseInt(e.parameter.recTotal);
e.parameter.rownum = i+1;
learnerList.setSelectedIndex(i);
getRecord(e);
return app;
}
 
// get select index number from Yes/No/NA
function getIndex(aValue){
if (aValue == "Y"){
return 0;
} else if (aValue == "N"){
return 1;
} else if (aValue == "NA"){
return 2;
} else {
return 3;
}
}
 
// function to get chached criteria ids/text or get values fro spreadsheet
function getCachedCritEntry(course,unit){
var cache = CacheService.getPublicCache();
var cached = cache.get("assessmentCriteria");
if (cached != null) {
return Utilities.jsonParse(cached);
}
var cuDoc = SpreadsheetApp.openById(CU_DOC);
var cuSheet = cuDoc.getSheetByName("All");
var critRow = getRowFromTwoCol(course+","+unit,1,cuSheet);
if (critRow){
var crit = getRowsData(cuSheet, cuSheet.getRange(critRow,1,1,cuSheet.getLastColumn()),1)[0];
cache.put("assessmentCriteria", Utilities.jsonStringify(crit), 3600); // cache for 60 minutes
return crit;
}
Browser.msgBox("Oops - Couldn't read assessment criteria");
}
 
function clearCachedCritEntry(){
var cache = CacheService.getPublicCache();
cache.remove("assessmentCriteria");
}
Google(c).js
JavaScript
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208
// The rest of this code is currently (c) Google Inc.
 
/// Replaces markers in a template string with values define in a JavaScript data object.
// Arguments:
// - template: string containing markers, for instance ${"Column name"}
// - data: JavaScript object with values to that will replace markers. For instance
// data.columnName will replace marker ${"Column name"}
// Returns a string without markers. If no data is found to replace a marker, it is
// simply removed.
function fillInTemplateFromObject(template, data) {
var email = template;
// Search for all the variables to be replaced, for instance ${"Column name"}
var templateVars = template.match(/\$\{\"[^\"]+\"\}/g);
 
// Replace variables from the template with the actual values from the data object.
// If no value is available, replace with the empty string.
for (var i = 0; i < templateVars.length; ++i) {
// normalizeHeader ignores ${"} so we can call it directly here.
var variableData = data[normalizeHeader(templateVars[i])];
email = email.replace(templateVars[i], variableData || "");
}
 
return email;
}
 
// setRowsData fills in one row of data per object defined in the objects Array.
// For every Column, it checks if data objects define a value for it.
// Arguments:
// - sheet: the Sheet Object where the data will be written
// - objects: an Array of Objects, each of which contains data for a row
// - optHeadersRange: a Range of cells where the column headers are defined. This
// defaults to the entire first row in sheet.
// - optFirstDataRowIndex: index of the first row where data should be written. This
// defaults to the row immediately below the headers.
function setRowsData(sheet, objects, optHeadersRange, optFirstDataRowIndex) {
var headersRange = optHeadersRange || sheet.getRange(1, 1, 1, sheet.getMaxColumns());
var firstDataRowIndex = optFirstDataRowIndex || headersRange.getRowIndex() + 1;
var headers = normalizeHeaders(headersRange.getValues()[0]);
 
var data = [];
for (var i = 0; i < objects.length; ++i) {
var values = []
for (j = 0; j < headers.length; ++j) {
var header = headers[j];
values.push(header.length > 0 && objects[i][header] ? objects[i][header] : "");
}
data.push(values);
}
 
var destinationRange = sheet.getRange(firstDataRowIndex, headersRange.getColumnIndex(),
objects.length, headers.length);
destinationRange.setValues(data);
}
// getRowsData iterates row by row in the input range and returns an array of objects.
// Each object contains all the data for a given row, indexed by its normalized column name.
// Arguments:
// - sheet: the sheet object that contains the data to be processed
// - range: the exact range of cells where the data is stored
// This argument is optional and it defaults to all the cells except those in the first row
// or all the cells below columnHeadersRowIndex (if defined).
// - columnHeadersRowIndex: specifies the row number where the column names are stored.
// This argument is optional and it defaults to the row immediately above range;
// Returns an Array of objects.
function getRowsData(sheet, range, columnHeadersRowIndex) {
//var headersIndex = columnHeadersRowIndex || range ? range.getRowIndex() - 1 : 1;
var headersIndex = columnHeadersRowIndex;
var dataRange = range ||
sheet.getRange(headersIndex + 1, 1, sheet.getMaxRows() - headersIndex, sheet.getMaxColumns());
var numColumns = dataRange.getEndColumn() - dataRange.getColumn() + 1;
var headersRange = sheet.getRange(headersIndex, dataRange.getColumn(), 1, numColumns);
var headers = headersRange.getValues()[0];
return getObjects(dataRange.getValues(), normalizeHeaders(headers));
}
// Given a JavaScript 2d Array, this function returns the transposed table.
// Arguments:
// - data: JavaScript 2d Array
// Returns a JavaScript 2d Array
// Example: arrayTranspose([[1,2,3],[4,5,6]]) returns [[1,4],[2,5],[3,6]].
function arrayTranspose(data) {
if (data.length == 0 || data[0].length == 0) {
return null;
}
 
var ret = [];
for (var i = 0; i < data[0].length; ++i) {
ret.push([]);
}
 
for (var i = 0; i < data.length; ++i) {
for (var j = 0; j < data[i].length; ++j) {
ret[j][i] = data[i][j];
}
}
 
return ret;
}
 
// getColumnsData iterates column by column in the input range and returns an array of objects.
// Each object contains all the data for a given column, indexed by its normalized row name.
// [Modified by mhawksey]
// Arguments:
// - sheet: the sheet object that contains the data to be processed
// - range: the exact range of cells where the data is stored
// This argument is optional and it defaults to all the cells except those in the first column
// or all the cells below rowHeadersIndex (if defined).
// - columnHeadersIndex: specifies the row number where the column names are stored.
// This argument is optional and it defaults to the row immediately above range;
// Returns an Array of objects.
function getColumnsData(sheet, range, columnHeadersIndex) {
var headersIndex = columnHeadersIndex || range ? range.getColumnIndex() - 1 : 1;
var dataRange = range ||
sheet.getRange(1, headersIndex + 1, sheet.getMaxRows(), sheet.getMaxColumns()- headersIndex);
var numRows = dataRange.getLastRow() - dataRange.getRow() + 1;
var headersRange = sheet.getRange(dataRange.getRow(),headersIndex,numRows,1);
var headers = arrayTranspose(headersRange.getValues())[0];
var normHeader = normalizeHeaders(headers);
return getObjects(arrayTranspose(dataRange.getValues()), normalizeHeaders(headers));
}
 
// For every row of data in data, generates an object that contains the data. Names of
// object fields are defined in keys.
// Arguments:
// - data: JavaScript 2d array
// - keys: Array of Strings that define the property names for the objects to create
function getObjects(data, keys) {
var objects = [];
for (var i = 0; i < data.length; ++i) {
var object = {};
var hasData = false;
for (var j = 0; j < data[i].length; ++j) {
var cellData = data[i][j];
if (isCellEmpty(cellData)) {
continue;
}
object[keys[j]] = cellData;
hasData = true;
}
if (hasData) {
objects.push(object);
}
}
return objects;
}
 
// Returns an Array of normalized Strings.
// Empty Strings are returned for all Strings that could not be successfully normalized.
// Arguments:
// - headers: Array of Strings to normalize
function normalizeHeaders(headers) {
var keys = [];
for (var i = 0; i < headers.length; ++i) {
keys.push(normalizeHeader(headers[i]));
}
return keys;
}
 
// Normalizes a string, by removing all alphanumeric characters and using mixed case
// to separate words. The output will always start with a lower case letter.
// This function is designed to produce JavaScript object property names.
// Arguments:
// - header: string to normalize
// Examples:
// "First Name" -> "firstName"
// "Market Cap (millions) -> "marketCapMillions
// "1 number at the beginning is ignored" -> "numberAtTheBeginningIsIgnored"
function normalizeHeader(header) {
var key = "";
var upperCase = false;
for (var i = 0; i < header.length; ++i) {
var letter = header[i];
if (letter == " " && key.length > 0) {
upperCase = true;
continue;
}
if (!isAlnum(letter)) { // I removed this because result identifiers have '_' in name
continue;
}
if (key.length == 0 && isDigit(letter)) {
continue; // first character must be a letter
}
if (upperCase) {
upperCase = false;
key += letter.toUpperCase();
} else {
key += letter.toLowerCase();
}
}
return key;
}
 
// Returns true if the cell where cellData was read from is empty.
// Arguments:
// - cellData: string
function isCellEmpty(cellData) {
return typeof(cellData) == "string" && cellData == "";
}
 
// Returns true if the character char is alphabetical, false otherwise.
function isAlnum(char) {
return char >= 'A' && char <= 'Z' ||
char >= 'a' && char <= 'z' ||
isDigit(char);
}
 
// Returns true if the character char is a digit, false otherwise.
function isDigit(char) {
return char >= '0' && char <= '9';
}
Setup.js
JavaScript
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301
function doSetup() {
clearCachedCritEntry(); // clear any cached criteria
// code to generate folder structure and move spreadsheet into right location
// + ROOT_FOLDER
// |- SPREADSHEET_FOLDER
// |- DRAFT_FOLDER
// |- RELEASED_FOLDER
var rootFolder = folderMakeReturn(ROOT_FOLDER); // get a the system route folder (if it deosn't existing make it
var spreadsheetFolder = folderMakeReturn(SPREADSHEET_FOLDER, rootFolder, ROOT_FOLDER+"/"+SPREADSHEET_FOLDER); // similar for spreadsheet folder
// move spreadsheet to spreadhsheet folder
var file = DocsList.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId());
file.addToFolder(spreadsheetFolder);
// create/get draft and release folders
var draftFolder = folderMakeReturn(DRAFT_FOLDER,rootFolder, ROOT_FOLDER+"/"+DRAFT_FOLDER);
var releaseFolder = folderMakeReturn(RELEASED_FOLDER,rootFolder, ROOT_FOLDER+"/"+RELEASED_FOLDER);
// Create application
var app = UiApp.createApplication().setWidth(575).setTitle("Fast Tracking Feedback Template System Setup - Step 1");
app.add(app.loadComponent("stepOne")); // load UI from GUI Builder
var flowpanel = app.getElementById("flowpanel");
var cuDoc = SpreadsheetApp.openById(CU_DOC); // set-up variable for spreadsheet of criteria
var courseListSheet = cuDoc.getSheetByName("Unique Courses"); // gets sheet of unique course names
var courseList = courseListSheet.getRange(2, 1, courseListSheet.getLastRow()).getValues(); // get all values
// set course/unit list boxes to 8 items high (not available via GUI)
var courseSelect = app.getElementById('courseSelect');
courseSelect.setVisibleItemCount(8);
var unitSelect = app.getElementById("unitSelect");
unitSelect.setVisibleItemCount(8);
// create click handlers on course/unit select lists
var courseSelectClickHandler = app.createServerClickHandler('getUnits');
courseSelectClickHandler.addCallbackElement(flowpanel);
courseSelect.addClickHandler(courseSelectClickHandler);
var courseSelectKeyHandler = app.createServerKeyHandler('getUnits');
courseSelect.addKeyPressHandler(courseSelectKeyHandler);
// add courses to select list
courseSelect.addItem("- Select Course -");
for (i in courseList){
if(courseList[i][0] != undefined && courseList[i][0] != ""){
courseSelect.addItem(courseList[i][0]);
}
}
// add client handler to 'Next' button
var but = app.getElementById("step2But");
var saving = app.createClientHandler()
.forEventSource().setEnabled(false);
but.addClickHandler(saving);
// show interface
DOC.show(app);
}
 
// function to see if folder exists in DocList and returns it
// (optional - if it doesn't exist then makes it)
function folderMakeReturn(folderName,optFolder,optFolderPath){
try {
if (optFolderPath != undefined){
var folder = DocsList.getFolder(optFolderPath);
} else {
var folder = DocsList.getFolder(folderName);
}
return folder;
} catch(e) {
if (optFolder == undefined) {
var folder = DocsList.createFolder(folderName);
} else {
var folder = optFolder.createFolder(folderName);
}
return folder;
}
}
 
// function to get all the units associated with a course
function getUnits(e){
var app = UiApp.getActiveApplication();
e.parameter.unitSelect = "";
checkStepTwo(e); // server side validation that course and units are selected - should switch this to client side validation
var cuDoc = SpreadsheetApp.openById(CU_DOC); // get the criteria spreadsheet
var unitListSheet = cuDoc.getSheetByName("All"); // get all the course/unit info
var unitList = unitListSheet.getRange(2, 1, unitListSheet.getLastRow(),2).getValues();
// get a filtered list of units for the selected course
var filteredUnits = [];
for (i in unitList){
if (e.parameter.courseSelect == unitList[i][0]){
filteredUnits.push([unitList[i][1],i]);
}
}
filteredUnits.sort();
var unitSelect = app.getElementById("unitSelect").clear();
var flowpanel = app.getElementById("flowpanel");
// set click handlers on the unit list
var unitSelectClickHandler = app.createServerClickHandler('checkStepTwo');
unitSelectClickHandler.addCallbackElement(flowpanel);
unitSelect.addClickHandler(unitSelectClickHandler);
var unitSelectKeyHandler = app.createServerKeyHandler('checkStepTwo');
unitSelect.addKeyPressHandler(unitSelectKeyHandler);
// add units to unit select list
unitSelect.addItem("- Select Unit -");
for (j in filteredUnits){
unitSelect.addItem(filteredUnits[j][0],filteredUnits[j][1]);
}
 
return app;
}
 
function checkStepTwo(e){
var app = UiApp.getActiveApplication();
var but = app.getElementById("step2But");
if ((e.parameter.courseSelect!=undefined || e.parameter.courseSelect != "- Select Course -") && (e.parameter.unitSelect != "" && e.parameter.unitSelect != undefined && e.parameter.unitSelect !="- Select Unit -")){
but.setEnabled(true);
} else {
but.setEnabled(false);
}
return app;
}
 
// function called in the GUI Builder stepOne Next button
function setAssessmentCriteria(e){
var app = UiApp.getActiveApplication().close(); // close current app window then create a new one
var app = UiApp.createApplication().setWidth(575).setTitle("Fast Tracking Feedback Template System Setup - Step 2");
app.add(app.loadComponent("stepTwo")); //load stepTwo from GUI Builder
var flowpanel = app.getElementById("flowpanel");
var cuDoc = SpreadsheetApp.openById(CU_DOC); // gets assessment criteria spreadsheet
var unitListSheet = cuDoc.getSheetByName("All");
// set range to selected unit/course
var unitListRange = unitListSheet.getRange(parseInt(e.parameter.unitSelect)+2,1,1,unitListSheet.getLastColumn());
var crit = getRowsData(unitListSheet,unitListRange,1)[0]; // get the criteria descriptions
var critList = app.createListBox(true).setWidth("97%").setHeight("170px").setStyleAttribute("margin", "3px").setStyleAttribute("borderColor", "LightGrey").setId("critList").setName("critList");
flowpanel.add(critList);
critList.setVisibleItemCount(7);
// add criteria and descriptions to a select list
for (i in crit){
if(i != "course" && i != "unit"){
critList.addItem(i.toUpperCase()+": "+crit[i],i.toUpperCase());
}
}
// add hidden fields storing course/unit selections
var course = app.createHidden("course", crit.course);
flowpanel.add(course);
var unit = app.createHidden("unit", crit.unit);
flowpanel.add(unit);
// add a done button
var but = app.createButton("Done").setHeight("20px").setWidth("50px").setStyleAttribute("margin", "3px").setStyleAttribute("marginLeft", "503px");
// create add server handlers
var ch = app.createServerClickHandler('submitSetup')
ch.addCallbackElement(flowpanel);
but.addClickHandler(ch);
// add client handler to disable button on first click
var handler = app.createClientHandler().forEventSource().setEnabled(false);
but.addClickHandler(handler);
flowpanel.add(but);
DOC.show(app);
}
 
function submitSetup(e){
var app = UiApp.getActiveApplication();
// Set course, unit and assignment title (functions in Init)
setCourse(e.parameter.course);
setUnit(e.parameter.unit);
setAssignment(e.parameter.assignmentTitle);
// get selected criteria and sort
var critValues = e.parameter.critList;
var critCom = getSortedCriteria(critValues);
// insert selected criteria into spreadsheet
var lastCol = FEEDSHEET.getLastColumn();
FEEDSHEET.insertColumns(CRIT_START_COL, critCom.length);
FEEDSHEET.getRange(1,CRIT_START_COL,1,critCom.length).setValues([critCom]);
// change column widths for criteria columns
for (var i=CRIT_START_COL; i<=FEEDSHEET.getLastColumn(); i++){
FEEDSHEET.setColumnWidth(i, 40);
}
// cache the slected criteria to use in entryForm
getCachedCritEntry(e.parameter.course,e.parameter.unit);
// Prompt to ask if student list should be imported
var importStudent = Browser.msgBox("Would you like to import a student list", Browser.Buttons.YES_NO);
if (importStudent=="yes"){
// if yes open import dialog
doEmailImport();
} else {
return app.close();
}
}
 
// function to create a student name/email import dialog
function doEmailImport(){
var app = UiApp.createApplication().setWidth(510).setTitle("Import Student Names and Emails");
app.add(app.loadComponent("importStudentList"));
var groupEmail = app.getElementById("groupEmail");
var label = app.getElementById("label");
var addButton = app.getElementById("addButton");
// Modified from http://googleappsdeveloper.blogspot.co.uk/2011/11/creating-more-responsive-applications.html
// Create a handler to call import student list function.
// only invoke 'import' if textfield has a valid email
var handler = app.createServerClickHandler('importGroupList')
.validateEmail(groupEmail)
.addCallbackElement(groupEmail);
// disable button when clicked
var saving = app.createClientHandler()
.forEventSource().setEnabled(false);
// Create handler to enable the button well all input is legal
var onValidInput = app.createClientHandler()
.validateEmail(groupEmail)
.forTargets(addButton).setEnabled(true)
.forTargets(label).setVisible(false);
// Create handler to mark invalid input in textBoxA and disable the button
var onInvalidInput1 = app.createClientHandler()
.validateNotEmail(groupEmail)
.forTargets(addButton).setEnabled(false)
.forTargets(groupEmail).setStyleAttribute("color", "Crimson")
.forTargets(label).setVisible(true);
// Create handler to mark the input in textBoxA as valid
var onValidInput1 = app.createClientHandler()
.validateEmail(groupEmail)
.forTargets(groupEmail).setStyleAttribute("color", "black");
// Add all the handlers to be called when the user types in the text boxes
 
groupEmail.addKeyUpHandler(onInvalidInput1);
groupEmail.addKeyUpHandler(onValidInput1);
groupEmail.addKeyUpHandler(onValidInput);
addButton.addClickHandler(handler);
addButton.addClickHandler(saving);
DOC.show(app);
}
 
// function to get a group mail list and insert names/emails into the spreadsheet
function importGroupList(e){
var app = UiApp.getActiveApplication();
var condFormat = FEEDSHEET.getRange(1,FEEDSHEET.getLastColumn()); // gets a cell with templated conditional formating
var data = listGroupMembers(e.parameter.groupEmail); //gets an array of student name/email
if (data != undefined){ // if data is returned insert into spreadsheet
FEEDSHEET.insertRows(2, data.length);
FEEDSHEET.getRange(2, NAME_START_COL, data.length, 2).setValues(data);
// apply conditional formating to inserted cells in the criteria columns
condFormat.copyFormatToRange(FEEDSHEET, CRIT_START_COL, FEEDSHEET.getLastColumn()-1, 2, data.length+1)
app.close();
}
return app;
}
 
// function to get array of [name,email] from a group email address
function listGroupMembers(groupEmail) {
// get spreadsheet of student names/emials
// (used because Group services doesn't have access to names yet)
var stu_doc = SpreadsheetApp.openById(STU_DOC);
var sheet = stu_doc.getSheetByName("Names");
try {
var group = GroupsApp.getGroupByEmail(groupEmail); // tries gets the group
var users = group.getUsers(); // gets all the users
var names = getNamesData(sheet,sheet.getRange(2,2, sheet.getMaxRows(), 2))[0]; // creates associative array of student id/name
var emails = [];
for (var i = 0; i < users.length; i++) {
// for each of the student emails match with a name or return Name not found
var id = users[i].getEmail().match(new RegExp("[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@"))[0];
id = "stu"+id.slice(0,-1);
emails.push([names[id]||"Name not found",users[i]]);
}
return emails
} catch(e) {
Browser.msgBox(e.message);
}
}
 
// function to allow user to rename assignment
function renameAssignment(){
var assignmentTitle = Browser.inputBox("The current assignment title is: '"+ASSIGN_TITLE+"'. If you would like to change it enter a new title in the box below:");
if (assignmentTitle != "cancel"){
setAssignment(assignmentTitle);
}
}
 
function getID(){
Logger.log('var doc = SpreadsheetApp.openById("'+SpreadsheetApp.getActiveSpreadsheet().getId()+'");');
}
_Init.js
JavaScript
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242
// Part of this code up to END OF (c) is:
/*
Copyright 2011 Martin Hawksey
 
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
 
http://www.apache.org/licenses/LICENSE-2.0
 
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/
var CU_DOC = "0Alq6_NgDnxLWdFV2X1BRTFZhTnlaQkNPekkyNzdsNkE"; // Critieria Spreadsheet ID
var STU_DOC = "0Alq6_NgDnxLWdEdqcHdUc1pPeDNlZkdZVVI3UlN4MGc"; // Student Lookup Spreadsheet ID
var TMP_DOC = "1TfP1nJHcRi4EH0j115XtC7jIA8a6d7x0_7FPN4H2qDA"; // Master Template ID
var NAME_START_COL = 4; // Column where learner name starts
var CRIT_START_COL = 15; // Column where the assignment feedback starts
 
var DOC = SpreadsheetApp.getActiveSpreadsheet();
var FEEDSHEET = DOC.getSheetByName("Feedback Table"); // get sheet of grades/feedback
var INFOSHEET = DOC.getSheetByName("Readme"); // get sheet with general assignment info
 
// Get course, unit and assignment title
var COURSE = INFOSHEET.getRange("B6").getValue();
var UNIT = INFOSHEET.getRange("B7").getValue();
var ASSIGN_TITLE = INFOSHEET.getRange("B8").getValue();
 
// Set course name
function setCourse(text){
INFOSHEET.getRange("B6").setValue(text);
COURSE = text;
}
 
// Set unit name
function setUnit(text){
INFOSHEET.getRange("B7").setValue(text);
UNIT = text;
}
 
// Set assignment title
function setAssignment(text){
INFOSHEET.getRange("B8").setValue(text);
ASSIGN_TITLE = text;
}
 
// If course and unit are set load spreadsheet header
if (COURSE != "" && UNIT != ""){
var HEADER = FEEDSHEET.getRange(1,1,1,FEEDSHEET.getLastColumn()-1).getValues()[0];
var CRITHEADER = normalizeHeaders(FEEDSHEET.getRange(1,CRIT_START_COL,1,FEEDSHEET.getLastColumn()-CRIT_START_COL).getValues()[0]);
var NORMHEADER = normalizeHeaders(HEADER);
}
 
// Set Pass, Merit and Distinction colours
var PASS_COLR = "#d9ead3";
var MERIT_COLR = "#fff2cc";
var DIST_COLR = "#f4cccc";
 
// Get user id
var USER_ID = Session.getActiveUser().getEmail().match(new RegExp("[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@"))[0];
USER_ID = USER_ID.slice(0,-1);
 
// Set names for working directories
var ROOT_FOLDER = "Fast Tracking Feedback "+USER_ID;
var SPREADSHEET_FOLDER = "Feedback Spreadsheets";
var DRAFT_FOLDER = "Draft Feedback";
var RELEASED_FOLDER = "Released Feedback";
 
// Create menu
function onOpen() {
var menuEntries = [];
menuEntries.push({name: "Setup Spreadsheet", functionName: "doSetup"});
menuEntries.push({name: "Import Student Names", functionName: "doEmailImport"});
menuEntries.push({name: "Rename Assignment", functionName: "renameAssignment"});
menuEntries.push({name: "Show Data Entry Form", functionName: "doForm"});
menuEntries.push(null);
menuEntries.push({name: "Generate Feedback Sheets for ALL", functionName: "generateAllDocs"});
menuEntries.push({name: "Generate Feedback Sheets for Selected", functionName: "generateSelectedDocs"});
menuEntries.push(null);
menuEntries.push({name: "Release Feedback Sheets for ALL", functionName: "releaseAllDocs"});
menuEntries.push({name: "Release Feedback Sheets for Selected", functionName: "releaseSelectedDocs"});
menuEntries.push(null);
menuEntries.push({name: "Release Feedback Sheets for ALL with Email", functionName: "releaseAllDocsWithEmail"});
menuEntries.push({name: "Release Feedback Sheets for Selected with Email", functionName: "releaseSelectedDocsWithEmail"});
SpreadsheetApp.getActiveSpreadsheet().addMenu("Fast Feedback System", menuEntries);
}
 
// function to get the row number based on a two column value
function getRowFromTwoCol(needle,column,sheet){
var data = sheet.getRange(1, column, sheet.getLastRow(), 2).getValues();
for (var i in data){
if (data[i].join(",")==needle){
return (parseInt(i)+1);
}
}
return false;
}
 
// function used to get associative array from the student id/names spreadsheet
function getNamesData(sheet, range, columnHeadersIndex) {
var headersIndex = columnHeadersIndex || range ? range.getColumnIndex() - 1 : 1;
var dataRange = range ||
sheet.getRange(1, headersIndex + 1, sheet.getMaxRows(), sheet.getMaxColumns()- headersIndex);
var numRows = dataRange.getLastRow() - dataRange.getRow() + 1;
var headersRange = sheet.getRange(dataRange.getRow(),headersIndex,numRows,1);
var headers = arrayTranspose(headersRange.getValues())[0];
var normHeader = studentIDsToString(headers);
return getObjects(arrayTranspose(dataRange.getValues()), studentIDsToString(headers));
}
 
// function to prefix an array with 'stu'
function studentIDsToString(anArray) {
var keys = [];
for (var i = 0; i < anArray.length; ++i) {
keys.push("stu"+anArray[i]);
}
return keys;
}
 
// function to trim whitespace
function alltrim(str) {
if (str != null) return str.replace(/^\s+|\s+$/g, '');
}
 
// function to return a sorted array of criteria codes
// sort P1-P99, M1-M99, D1-D99
function getSortedCriteria(critValues){
var critCom = [];
// Match P1-P99
var passCrit = critValues.match(new RegExp( "[P]{1}[0-9]{1,2}", "gi" ));
if (passCrit) {
passCrit.sort(sortAlphaNum);
critCom = critCom.concat(passCrit);
}
// Match M1-M99
var meritCrit = critValues.match(new RegExp( "[M]{1}[0-9]{1,2}", "gi" ));
if (meritCrit) {
meritCrit.sort(sortAlphaNum);
critCom = critCom.concat(meritCrit);
}
// Match D1-D99
var distCrit = critValues.match(new RegExp( "[D]{1}[0-9]{1,2}", "gi" ));
if (distCrit) {
distCrit.sort(sortAlphaNum);
critCom = critCom.concat(distCrit);
}
return critCom;
}
// http://stackoverflow.com/a/4340387
function sortAlphaNum(a,b) {
var charPart = [a.substring(0,1), b.substring(0,1)],
numPart = [a.substring(1)*1, b.substring(1)*1];
 
if(charPart[0] < charPart[1]) return -1;
else if(charPart[0] > charPart[1]) return 1;
else{ //(charPart[0] == charPart[1]){
if(numPart[0] < numPart[1]) return -1;
else if(numPart[0] > numPart[1]) return 1;
return 0;
}
}
 
/*
@author: remy sharp / http://remysharp.com
@url: http://remysharp.com/2008/04/01/wiki-to-html-using-javascript/
@license: Creative Commons License - ShareAlike http://creativecommons.org/licenses/by-sa/3.0/
@version: 1.0
Can extend String or be used stand alone - just change the flag at the top of the script.
*/
// utility function to check whether it's worth running through the wiki2html
// the regex beast...
function wiki2html(s) {
// lists need to be done using a function to allow for recusive calls
function list(str) {
return str.replace(/(?:(?:(?:^|\n)[\*#].*)+)/g, function (m) { // (?=[\*#])
var type = m.match(/(^|\n)#/) ? 'OL' : 'UL';
// strip first layer of list
m = m.replace(/(^|\n)[\*#][ ]{0,1}/g, "$1");
m = list(m);
return '<' + type + '><li>' + m.replace(/^\n/, '').split(/\n/).join('</li><li>') + '</li></' + type + '>';
});
}
return list(s
/* BLOCK ELEMENTS */
.replace(/(?:^|\n+)([^# =\*<].+)(?:\n+|$)/gm, function (m, l) {
if (l.match(/^\^+$/)) return l;
return "\n<p>" + l + "</p>\n";
})
 
.replace(/(?:^|\n)[ ]{2}(.*)+/g, function (m, l) { // blockquotes
if (l.match(/^\s+$/)) return m;
return '<blockquote>' + l + '</pre>';
})
.replace(/((?:^|\n)[ ]+.*)+/g, function (m) { // code
if (m.match(/^\s+$/)) return m;
return '<pre>' + m.replace(/(^|\n)[ ]+/g, "$1") + '</pre>';
})
 
.replace(/(?:^|\n)([=]+)(.*)\1/g, function (m, l, t) { // headings
return '<h' + l.length + '>' + t + '</h' + l.length + '>';
})
/* INLINE ELEMENTS */
.replace(/'''(.*?)'''/g, function (m, l) { // bold
return '<strong>' + l + '</strong>';
})
.replace(/''(.*?)''/g, function (m, l) { // italic
return '<em>' + l + '</em>';
})
//.replace(/[^\[](http[^\[\s]*)/g, function (m, l) { // normal link
//return '<a href="' + l + '">' + l + '</a>';
//})
.replace(/[\[]([http:|https:].*)[!\]]/g, function (m, l) { // external link
var p = l.replace(/[\[\]]/g, '').split(/ /);
var link = p.shift();
return '<a href="' + link + '">' + (p.length ? p.join(' ') : link) + '</a>';
})
.replace(/\[\[(.*?)\]\]/g, function (m, l) { // internal link or image
var p = l.split(/\|/);
var link = p.shift();
 
if (link.match(/^Image:(.*)/)) {
// no support for images - since it looks up the source from the wiki db :-(
return m;
} else {
return '<a href="' + link + '">' + (p.length ? p.join('|') : link) + '</a>';
}
})
);
}

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.