The App is growing very large very fast, I have added a new class, updated an old one and added a new service, also a new function call allowing the user to run all of the services at once. Currently what bugs me is that triggers will not load any user interface. I mean it is not crucial to the execution of the script but it would be nice to let the user see that the app is working. My current solution for now is “telling” the user that the interface will close and sending a notification email after the app has executed. This also might be a problem since the business with triggers is least to say tricky. I have them working but under the same conditions the app takes something between 2 and 20 reruns to execute. That is 5 – 30 minutes, I can imagine how that can create all sorts of problems in the user-app interaction but unless I find a way to fix this all I can do is hope the user will read the “manual”.
Now to get to the code… As I said one class has been updated the “Class” class. (next update I am changing the name of this) The difference is not big it is reflected in one additional parameter for the constructor, a change in the period parsing and the “equals” method.
1 //class for courses, cours is defined by the day, name, room and teacher and the desired parsing function
2 function Class(cColumnHeader, cName, cRoom, cTeacher, parsingFunc) {
3
4 var period = parsing(parsingFunc, cColumnHeader);
5 var name = cName;
6 var room = cRoom;
7 var teacher = cTeacher;
8 var students = []; //holds list of students takiing the course
9 var equals = equals;
10 //setters and getters
11 this.addStudents = addStudents;
12 this.getStudents = getStudents;
13 this.getPeriod = getPeriod;
14 this.getName = getName;
15 this.getRoom = getRoom;
16 this.getTeacher = getTeacher;
17
18 function addStudents(i) {
19 students.push(i);
20 }
21
22 function getStudents() {
23 return students;
24 }
25
26
27 function getPeriod() {
28 return period;
29 }
30
31 function getName() {
32 return name;
33 }
34
35 function getRoom() {
36 return room;
37 }
38
39 function getTeacher() {
40 return teacher;
41 }
42
43 function getStudents() {
44 return students;
45 }
46
47 function parsing(func, head) {
48 if (func == "value") return [parsePeriodValue(head), head.match(/\d+$/)];
49 else return parsePeriod(head);
50 }
51
52 function parsePeriodValue(header) {
53
54 if (header.indexOf("M") != -1) return 0;
55
56 else if (header.indexOf("TU") != -1) return 1;
57
58 else if (header.indexOf("W") != -1) return 2;
59
60 else if (header.indexOf("TH") != -1) return 3;
61
62 else if (header.indexOf("F") != -1) return 4;
63
64 else return -1;
65 }
66 //changing the period from M1 (data sheet header) to Monday 1 etc
67 function parsePeriod(cColumnHeader) {
68
69 if (cColumnHeader.indexOf("M") != -1) return "Monday " + cColumnHeader.match(/\d+$/);
70
71 else if (cColumnHeader.indexOf("TU") != -1) return "Tuesday " + cColumnHeader.match(/\d+$/);
72
73 else if (cColumnHeader.indexOf("W") != -1) return "Wednesday " + cColumnHeader.match(/\d+$/);
74
75 else if (cColumnHeader.indexOf("TH") != -1) return "Thursday " + cColumnHeader.match(/\d+$/);
76
77 else if (cColumnHeader.indexOf("F") != -1) return "Friday " + cColumnHeader.match(/\d+$/);
78
79 else return "Could not parse, check input";
80 }
81
82 this.equalsPeriod = function (otherClass) {
83 var otherPeriod = otherClass.getPeriod();
84 if (typeof (period) == "object") {
85 if (period[0] == otherPeriod[0] && period[1][0] == otherPeriod[1][0]) return true;
86 } else if (typeof (period) == "string") {
87 return (period == otherPeriod);
88 }
89
90 return false;
91 }
92
93 //checks if 2 courses are equal
94 this.equals = function (otherClass) {
95 var periodEqual = this.equalsPeriod(otherClass);
96 if (periodEqual && name == otherClass.getName() && room == otherClass.getRoom() && teacher == otherClass.getTeacher()) return true;
97 return false;
98 }
99
100 this.toString = function () {
101 return period + " | " + name + " | " + room + " | " + teacher;
102 }
103 }
The new parameter in the constructor is “parsingFunc”, as the name sais it will give instructions on which parsing function to use for the created class. (the parsing function generates the period value using cColumnHeader. cColumnHeader has the form “M1” for “Monday 1” etc.)
Previously there was one parsing function, but doing the new service I had a need to get the value as an integer Monday 1 or M1 would return to 0… ; So I implemented parsePeriodValue. Now using this function it was very easy for me to get the exact coordinate where that particular course would be in the schedule. It looks like this [0, [1]]. That would mean Monday period 1. Problem was the rest of the script had no real use for such a period value, I had to keep both of them and that meant fixing the equals function. In this particular script the equals function has a main part. Without it there is no way the script would even run. And I know that for a fact because it did not work and the script did not run, at all. The fixing part thou was not that hard, all it took was checking the period equivalency separately. In the checking the trick was to decide on the type of the passed variable (object or string) and accordingly see if the values are equal. The code doing this is equals(otherClass) and equalsPeriod(otherClass).
As for the class I added it is class “Teacher”, a very net little piece of code.
1 function Teacher(tname) {
2
3 var name = tname;
4 var courses = [];
5
6 this.getName = function () {
7 return name;
8 }
9 /*before adding the new course to the list of courses this teacher is teaching it will check
10 *the list of previous eneries*/
11 this.addCourse = function (cColumnHeader, cName, cRoom, cTeacher, parsingFunc) {
12 var course = new Class(cColumnHeader, cName, cRoom, cTeacher, parsingFunc);
13 for (var i = 0; i < courses.length; i++) {
14 if (courses[i].equals(course)) {
15 return;
16 }
17 }
18 courses.push(course);
19 }
20 //returning a the course at position "index"
21 this.getCourse = function (index) {
22 return sourses[index];
23 }
24 //returning the list of all courses for this teacher
25 this.getCourses = function () {
26 return courses;
27 }
28
29
30 }
I don’t think this code needs any additional explanation.
createTeacherSchedules
This service has 3 main functions checkIndexData, getTeacherSchedule() and formatSchedule(). checkIndexData() is described in detail in the previous post, for this post I need to say that there has been a small modification to it, it now takes as a parameter the function which follows it allowing to create a trigger for that function. The second one will get and process the data from the data sheet. (example of data sheet can be seen in the previous post) The last one will take the data, format it to a nice tabular look and write the data in the required spreadsheet.
checkIndexData
1 //checks if the data the script has is accurate
2 function checkIndexData(folowFunction){
3 //data saved during execution of other parts of the script
4 var ss=getSpreadsheet("rosterScriptData", "rosterScript");
5 //this part of the script is concerned with teacher rosters, hence accesses the teacher indexes
6 var indexSheet=ss.getSheetByName("teacher index");
7 /*checking if the collection for teacher rosters is present
8 *this is for easier overview of the documents by the user*/
9 try{
10 var folder = DocsList.getFolder("Teacher Rosters");
11 }
12 catch(e){
13 var parent=DocsList.getFolder("rosterScript");
14 var folder= DocsList.createFolder("Teacher Rosters");
15 folder.addToFolder(parent);
16 }
17
18 if(!indexSheet){
19 indexSheet=ss.insertSheet("teacher index"); }
20
21 var indexData=indexSheet.getDataRange().getValues();
22
23 /*trying to open spreadsheets as indicated in the teacher index sheet
24 *if the data is not correct, or not present a new sheet will be created
25 *a failsafe if documents are deleted etc*/
26 for(var i=0; i<indexData.length; i++){
27
28 try{
29 var d= SpreadsheetApp.openById(indexData[i][1]);
30 }
31
32 catch(e){
33 var teacherSheet=getSpreadsheet(indexData[i][0], "Teacher Rosters");
34 indexData[i][1]=teacherSheet.getId();
35 }
36
37 }
38 //updating the indexing data of the script
39 indexSheet.clear();
40 indexSheet.getRange(1, 1, indexData.length, 2).setValues(indexData);
41 //this process takes a fair amount of time, the script will add a trigger close itself and rerun after a minute
42 var date=new Date();
43 date.setMinutes(date.getMinutes()+1);
44 ScriptApp.newTrigger(folowFunction).timeBased().inTimezone(SpreadsheetApp.getActive().getSpreadsheetTimeZone()).at(date).create();
45 ScriptProperties.setProperty("sheetName", "Monday 1");
46 }
getTeacherSchedule()
The function goes through the data sheet and makes object of type Teacher, at the same time adding courses to the teacher’s list. At the end giving a list of all the teachers in the school of which all have a list of classes they teach. The function uses getTeacher to make sure there are no duplicates in the list, deleteTrig() to delete the trigger made by checkIndexData. (the function itself will be able to execute in the 5 minute time frame) and notify to let the user know that the script has finished execution. notify is needed because function called by triggers will not load the GUI, the funct variable is the name of the service which was invoked by the user.
1 function getTeacher(list, teacher){
2
3 for(var i=0; i<list.length; i++)
4 if(list[i].getName()==teacher)
5 return i;
6 list.push(new Teacher(teacher));
7 return list.length-1
8 }
9
1 function notify(funct) {
2
3 var ss=SpreadsheetApp.getActive();
4 var owner=ss.getOwner().getEmail();
5 var message="The funct have been created, You can access them in the rosterScript collection";
6 message.replace("funct", funct);
7 GmailApp.sendEmail(owner, "Teacher Rosters", "The teacher rosters have been created, You can access them in the Teacher Roster collection");
8 }
1 function getTeacherSchedule() {
2 var begin=6;
3 var end=102;
4
5 var ss = SpreadsheetApp.getActive();
6 var sheet = ss.getSheets()[0];
7 //puts all the values from the spreadsheet into a 2D array
8 var data = sheet.getDataRange().getValues();
9 //eventually will hold the complete list of teachers
10 var listTeachers = [];
11
12
13 for (var i = begin; i <= end; i += 3) {
14 z = 0;
15
16 for (var j = 1; j < data.length; j++) {
17 /*making sure that free classes, lunch breaks or classes without assigned teachers are considered, speeding up the script
18 *the script processes data by "row" not by column*/
19 if ( (data[j][i + 2] != null && data[j][i + 2] != "") && data[j][i]!="Free") {
20
21 var index=getTeacher(listTeachers, data[j][i+2]);
22
23 listTeachers[index].addCourse(data[0][i], data[j][i], data[j][i + 1], data[j][i + 2], "value");
24
25 }//end outer if
26 }//end inner for
27 }//end outer for
28
29 formatSchedule(listTeachers);
30 deleteTrig();
31 notify("Teacher Schedule");
32 }
formatSchedule
This is a very simple function, it has no helper functions and has a nice straightforward flow. It will go teacher by teacher and create a 2D array with the schedule, take the teachers spreadsheet and write the data from the array.
1 function formatSchedule(teachers) {
2 //using those hard coded array for seting up the tabel
3 var col = [
4 ["Class 1"],
5 ["Room"],
6 ["Class 2"],
7 ["Room"],
8 ["Class 3"],
9 ["Room"],
10 ["Class 4"],
11 ["Room"],
12 ["Class 5"],
13 ["Room"],
14 ["Class 6"],
15 ["Room"]
16 ];
17 var row = [
18 ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"]
19 ];
20
21 //loop throug all the teachers in the list
22 for (var i = 0; i < teachers.length; i++) {
23 //array for the formated schedule
24 var schedule = [];
25 //initializing the array .setValues() needs a rectangular array
26 for (var j = 0; j < 16; j++) {
27 schedule[j] = [];
28 for (var k = 0; k < 6; k++)
29 schedule[j][k] = "";
30 }
31 //loading the list of courses of the current teacher
32 var courses = teachers[i].getCourses();
33
34 for (var j = 0; j < courses.length; j++) {
35 //gets period in [NUM1, [NUM2]] format
36 var coordinate = courses[j].getPeriod();
37 var y = coordinate[0],
38 x = coordinate[1][0];
39 x--;
40 x *= 2;
41 if (schedule[x][y] != "") {
42 /*it can happen that a teacher has different classes at the same time
43 *this line will make sure the existing data is not overwriten*/
44 schedule[x][y] += " / " + courses[j].getName();
45 schedule[x + 1][y] += " / " + courses[j].getRoom();
46 } else {
47 schedule[x][y] = courses[j].getName();
48 schedule[x + 1][y] = courses[j].getRoom();
49
50 }
51
52 }
53
54 var ss = getSpreadsheet(teachers[i].getName(), "Teacher Rosters");
55
56 //adding a "schedule" sheet to the teachers spreadsheet and writing the schedule
57 var sheet = ss.getSheetByName("schedule");
58 if (!sheet) {
59 sheet = ss.insertSheet("schedule");
60 sheet.getRange(2, 1, col.length, 1).setValues(col);
61 sheet.getRange(1, 2, row.length, 5).setValues(row);
62 }
63
64 sheet.getRange(2, 2, schedule.length, schedule[0].length).setValues(schedule);
65
66 }
67
68 }
That is the current progress on the app. Again if there is any feedback, opinions on how to make the app faster, better, more elegant leave a comment or write a message.
/*Off topic :
Working on it I have also formed an opinion on what would make Google Apps Script a bit better. An offline editor is the first thing. Although it can create a lot of problems concerning execution times it would really be great to be able to write on the go, use features (which don’t need a connection like create Spreadsheet ) without a limit would be a real plus for testing. If there is any chance for that happening tell me where to sign up.*/
Cheers
