Currently I am working on two app, "Rosters and Schedules" and "School Tour Reservation System". On the later more in another post.
Both apps provide a way to automatize their respective tasks.
The "Rosters and Schedules" app is somewhere in the middle of the developments process. Mentors for this project are Jesse Spevack and Andrew Stillman.
This project consists of two apps. One to be integrated in a spreadsheet the other in a site.
The spreadsheet app will upon installing and opening the sheet add the “Roster Script” menu. Currently having 2 options, “Create Roster” and “Create Schedules”.
After clicking on any of the options the script will start with the execution giving the user a GUI notification.
At this point the app is rigid and will not work unless the data is in the predefined format. (template) This is a issue I will try to fix in the future, along with a few other problems(e.g. the script sends notifications to all teachers in case of change instead of just those who’s classes are affected)
The data is processed column by column, allowing for faster execution time. Since the students are not (and can not) be sorted by the class they are taking at a particular period the script has to go over the list and "count" students in every class. If going row by row (student by student) it would have to got through the list of all classes. The app considers classes to be different not only by name, room and teacher but also by period and day. Meaning that Algebra Monday 1st period with Skywalker is not the same as Algebra Friday 3rd period with Skywalker. This presented another issue in the execution time, for every class the app finds it needs to check if the class has already been initialized. When the list of classes contains a large enough number it is time consuming to go through the list and check, that is why I used a trick, tracking the number of classes processed for every period then just looping for that period’s classes. (variable k)
My initial approach was to use classes for processing the data, creating a student and period class and corresponding objects. This approach turned out to be slow. And in this case execution time matters. The data sheet I used had 432 students all of them 33 classes a week producing a 432 x 107 cells sheet. So I decided to use a class for period and reference the students by row number as can be seen in the code below (line 19). I use the same approach later on in the code. This is a bit ugly but gets the job done, and in time…
1 function getData(begin, end) {
2
3 var k = 1;
4 var ss = SpreadsheetApp.getActive();
5 var sheet = ss.getSheets()[0];
6
7 var data = sheet.getDataRange().getValues();
8
9 var listClasses = [];
10 var i;
11 var z = 0;
12 //var day="Monday 1",dDay;
13 for (i = begin; i <= end; i += 3) {
14 z = 0;
15
16 for (var j = 1; j < data.length; j++) {
17
18 if (data[j][i + 2] != null && data[j][i + 2] != "") {
19 var newClass = new Class(data[0][i], data[j][i], data[j][i + 1], data[j][i + 2]);
20 var classN = findIndex(newClass, listClasses, z);
21
22 if (classN == -1) {
23 newClass.addStudents(j);
24 listClasses.push(newClass);
25 z++;
26
27 } else {
28 listClasses[classN].addStudents(j);
29
30 }
31
32 }
33 }
34 }
35
36
37 var roster = makeRoster(listClasses, ss, data);
38 pushRosters(roster);
39 }
40
The other important lesson for me was realizing the cost of opening spreadsheets. I had a table for tracking execution time/number of users. The numbers got very huge very fast until I cut down on opening spreadsheets and having multiple spreadsheets open at the same time. Retrieving the data “all at once” also helped. Using getDataRange.getValues() and storing it into a 2D array.
When talking about such amounts of output data, it gets very important to “trim” the size of every sheet. Although it may look like no app can ever populate more than 400 000 cells this one did, and not even at the end of the execution. I made sure that the roster sheets and the schedules have a sheet size just enough to house the corresponding data. The student schedules however also had to be divided into several spreadsheets. My dummy data creates 3 spreadsheets for the schedules. (I have 550 students and 33 periods)
I will post the complete code, with comments in the next post.
Cheers
