Fast Tracking Feedback System v.2 Code
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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>'; | |
} | |
}) | |
); | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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; | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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"); | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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'; | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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()+'");'); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment