ReportGen

Algemeen

Voor het genereren van een rapport wordt hier gebruik gemaakt van data vergaard uit verschillende bronnen.
Het voorbeeld wat hier gegeven wordt geeft je een aantal mogelijkheden en de aanpak ervan.

/**

* ReportGen(repsheet,templatesheet,gradesheet)

* @param {string} repsheet, sheetname of report sheet

* @param {string} templatesheet, sheet with template definition

* @param {string} gradesheet, sheet with grades of specific student

* @return {array} REP, 2D array of report Title,Grade

*

* Generate report, based on the Template page and aggregating CourseWork items of a specific user to cluster scores

* aggregating fields is done with "+++" separator at the lowes level

* Hierarchical aggregation is done with the key structure syntax "/" separating different levels.

* get #ERROR if two successive empty levels are defined in the template.

*

* Aggregation principle:

* Max score out of all attemps in one Coursework item

* Average when multiple Coursworks are aggregated for one title item (courswork +++)

* Max when multiple Coursworks are aggregated for one title item (courswork &&&)

* Sub levels average aggregation of all lower levels. (key /)

*

*/

function ReportGen(repsheet,templatesheet,gradesheet) {

try {

// Script run interactive-> Alerts will work, otherwise not

var DisplayAlerts = true;

try {

var ui = SpreadsheetApp.getUi();

}

catch (err) {

DisplayAlerts = false;

}

//Lock access to vars/sheet from triggers

LockService.getScriptLock().waitLock(300000) //max 6min

InitSheet_(repsheet)

// Get template into object

let template = TableToObject(templatesheet)

// Get grade into object

let grade = TableToObject(gradesheet)

//create report object

let report = {}

//crossref keys for score calculation Courseworks

let crossref = {}

// Create linktable to grade fields of items.

let itemlink = {}

//Create object keys based on template and aggregation of Coursework

//template key format is structure of objects within objects

//template Coursework is aggregation of lowest level array's in object

for (k = 0; k < template.Key.length; k++) {

//split key field

let textsplit = template.Key[k].split("/")

let courseworksplit = []

courseworksplit = template.CourseWork[k].split("+++")

let aggregatesplit=[]

aggregatesplit = template.CourseWork[k].split("&&&")

// drill down to last level

if (textsplit.length == 0 || textsplit[0] == "") {

return

} else {

let tempobj = report

for (l = 0; l < textsplit.length; l++) {

// create object within object at all levels

if (!tempobj[textsplit[l]]) { tempobj[textsplit[l]] = {} }

tempobj = tempobj[textsplit[l]]

}

// linktable for titel template to report item structure at last level

itemlink[template.Titel[k]] = tempobj

// create array's of Coureswork within report object at last level

if ((courseworksplit.length != 1 || courseworksplit[0] != "") && aggregatesplit.length <=1) {

for (c = 0; c < courseworksplit.length; c++) {

//create report array object within last level

if (!tempobj[courseworksplit[c]]) { tempobj[courseworksplit[c]] = [] }

//create xref to report array object for Coureswork items

if (!crossref[courseworksplit[c]]) { crossref[courseworksplit[c]] = tempobj[courseworksplit[c]] }

else{

//if existing in xref, than report object linking to xref

tempobj[courseworksplit[c]]= crossref[courseworksplit[c]]

}

//create aggregate prop in super object to instruct aggregation type

if(!tempobj.opcode){tempobj.opcode=""}

tempobj.opcode="avg"

}

//Logger.log(template.Titel[k])

}

if (aggregatesplit.length > 1) {

for (c = 0; c < aggregatesplit.length; c++) {

//create report array object within last level

if (!tempobj[aggregatesplit[c]]) { tempobj[aggregatesplit[c]] = [] }

//create xref to report array object for Coureswork items

if (!crossref[aggregatesplit[c]]) { crossref[aggregatesplit[c]] = tempobj[aggregatesplit[c]] }

else{

//if existing in xref, than report object linking to xref

tempobj[aggregatesplit[c]]= crossref[aggregatesplit[c]]

}

//create aggregate prop in super object to instruct aggregation type

if(!tempobj.opcode){tempobj.opcode=""}

tempobj.opcode="max"

}

//Logger.log(template.Titel[k])

}

}

}

// fill up report array fields of coursework from Grade sheet scores.

// array length -> number of multiple attempts of same Grade item

for (r = 0; r < grade["courseworkTitle/Quizresponse"].length; r++) {

let courseworkT = grade["courseworkTitle/Quizresponse"][r]

let objkey = crossref[courseworkT]

if (typeof objkey != "undefined") {

objkey.push(grade["Grade/score"][r])

}

}

//calculate aggregated grade value for every item in the form template

// if no coursework is assigned to a subkey, the result will be #N/A, also for the main key

// this indicates a faulty template

// using class Grade to drill down to lowest level and aggregate

// Create 2D array for sheet output

//

let REP = [[]]

REP.shift()

// student info

REP.push([grade.studentFullName[2],grade.studentEmail[2],""])

// Table header

REP.push(["Titel","Omschrijving","Grade"])

let items = Object.keys(itemlink)

for (let k = 0; k < items.length; k++) {

Logger.log(items[k])

let gr = new Grade(itemlink[items[k]])

itemlink[items[k]].grade = gr.grade()

REP.push([items[k], template.Omschrijving[k],itemlink[items[k]].grade])

}

//create Sheet output from 2D array

let Sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(repsheet)

Sheet.getDataRange().clearContent()

Sheet.getRange(1, 1, REP.length, REP[0].length).setValues(REP)

SpreadsheetApp.flush();

LockService.getScriptLock().releaseLock()

return REP

} catch (e) {

SpreadsheetApp.flush();

LockService.getScriptLock().releaseLock()

if (DisplayAlerts) { ui.alert(e); }

Logger.log(e)

return false

}

}

De code kan verdeeld worden in een aantal blokken:

Om een raport te kunnen maken heb je scores nodig, die we vinden in de grade sheet en een template om een rapport te maken, wat je kan vinden in de template sheet.

De uitkomst wordt terug geplaatst in de repsheet.