Notes before you proceed:
- the code is not perfect or at its final stage
- not al features of the script are completed
- I refer to course-school class and class as an abstraction in programming
- any suggestions are welcome
- I divided my code by 2 criteria, by the time I wrote a certain part and in logical groups. Since I want to present the code as readable as possible I will not present it in logical groups (functions used by more than one function are grouped as common) but will first show any helper functions and then the code of the function. I will not show the same code more than once. My code is fully commented, most of the comments serve only for representing the code on this blog.
- extracting a “master” roster sheet
- extracting student schedules
- extracting personalized roster sheets for teachers
- the user interface
Extracting a “master” roster sheet
In order to show this part first I need to show the class called Class-this is in fact course
The code:
1 //class for courses, course is defined by the day, name, room and teacher
2 function Class(cColumnHeader, cName, cRoom, cTeacher) {
3
4 var period = parsePeriod(cColumnHeader);
5 var parsePeriod = parsePeriod;
6 var name = cName;
7 var room = cRoom;
8 var teacher = cTeacher;
9 var students = []; //holds list of students taking the course
10 var equals = equals;
11 //setters and getters
12 this.addStudents = addStudents;
13 this.getStudents = getStudents;
14 this.getPeriod = getPeriod;
15 this.getName = getName;
16 this.getRoom = getRoom;
17 this.getTeacher = getTeacher;
18
19 function addStudents(i) {
20 students.push(i);
21 }
22
23 function getStudents() {
24 return students;
25 }
26
27
28 function getPeriod() {
29 return period;
30 }
31
32 function getName() {
33 return name;
34 }
35
36 function getRoom() {
37 return room;
38 }
39
40 function getTeacher() {
41 return teacher;
42 }
43
44 function getStudents() {
45 return students;
46 }
47 //changing the period from M1 (data sheet header) to Monday 1 etc
48 function parsePeriod(cColumnHeader) {
49
50 if (cColumnHeader.indexOf("M") != -1) return "Monday " + cColumnHeader.match(/\d+$/);
51
52 else if (cColumnHeader.indexOf("TU") != -1) return "Tuesday " + cColumnHeader.match(/\d+$/);
53
54 else if (cColumnHeader.indexOf("W") != -1) return "Wednesday " + cColumnHeader.match(/\d+$/);
55
56 else if (cColumnHeader.indexOf("TH") != -1) return "Thursday " + cColumnHeader.match(/\d+$/);
57
58 else if (cColumnHeader.indexOf("F") != -1) return "Friday " + cColumnHeader.match(/\d+$/);
59
60 else return "Could not parse, check input";
61 }
62 //checks if 2 courses are equal
63 this.equals = function (otherClass) {
64 if (period == otherClass.getPeriod() && name == otherClass.getName() && room == otherClass.getRoom() && teacher == otherClass.getTeacher()) return true;
65 return false;
66 }
67
68 this.toString = function () {
69 return period + " " + name + " " + room + " " + teacher;
70 }
71 }
This is your basic OOP class, getters,setters and a few private functions, the comments in the code are explanatory enough.
Extracting the rosters is divided into 2 parts, one that will extract the data from the spreadsheet and create a 2D array of the rosters, the other will take out parts of that array and write the data to according sheets in a spreadsheet. Part on starts with function getData(int, int) and uses makeRosters(object). Part 2 has only one function pushRosters(object).
getData(int, int)
getData(int, int) will take in 2 parameters the first day and last day it should read. (take a look at the Data link for reference), this is made to provide easier user control later on.
getData has 1 helper function, findIndex(class, list, begin). class is an object representing the class currently used, list is a list of classes created so far, begin is an integer holding the value of courses created for a particular day. (the app loops through data day by day, not period by period= row by row not column by column. Tracking the number of courses created in a day has appreciably speeded up the execution of getData.
1 //finding index of passed course, if the course is not present it will return -1
2 function findIndex(class, list, begin) {
3
4 var l = list.length;
5 //begin is the passed variable k, number of courses of a particular day
6 begin = l - begin;
7 //if there are not courses for some day no need to go further
8 if (l < 1)
9 return -1;
10
11 for (var i = begin; i < l; i++)
12 //returns eventual index of the course
13 if (class.equals(list[i])) {
14 return i;
15 }
16
17 return -1;
18 }
19
20
1 /*function will extract the relevant data from the spreadsheet
2 *this getData will make a list of all the courses in the week
3 *a course is defined by name, room, teacher and day of week*/
4 function getData(begin, end) {
5
6 var ss = SpreadsheetApp.getActive();
7 var sheet = ss.getSheets()[0];
8 //puts all the values from the spreadsheet into a 2D array
9 var data = sheet.getDataRange().getValues();
10 //eventually will hold the complete list of courses
11 var listClasses = [];
12 /* z will memorize the number of courses added to the list on some day
13 *since a course is defined also by its time (day)
14 *this will speed up the process of checking if a class is already in the list*/
15 var z = 0;
16 for (var i = begin; i <= end; i += 3) {
17 z = 0;
18
19 for (var j = 1; j < data.length; j++) {
20 /*making sure that free classes, lunch breaks or classes without assigned teachers are considered, speeding up the script
21 *the script processes data by "row" not by column*/
22 if ( (data[j][i + 2] != null && data[j][i + 2] != "") || data[j][i]!="Free") {
23
24 var newClass = new Class(data[0][i], data[j][i], data[j][i + 1], data[j][i + 2]);
25 //checking if the course exists or should be added to the list
26 var classN = findIndex(newClass, listClasses, z);
27
28 //in case of new course the current student is added, and the course then added to the list of courses
29 if (classN == -1) {
30 newClass.addStudents(j);
31 listClasses.push(newClass);
32 z++; //increase number of courses for some day
33
34 }//end if
35 else {
36 //if the course is in the list just the student is added, more precise the row in which the student data is stored
37 listClasses[classN].addStudents(j);
38 }//end else
39 }//end outer if
40 }//end inner for
41 }//end outer for
42
43 //makeRoster will only create a 2D array with ALL the rosters for a week
44 var roster = makeRoster(listClasses, data);
45 //push rosters will take the above 2D array and sort out data to the proper sheets, Monday 1, Monday 2 etc
46 pushRosters(roster);
47 }
48
At the beginning I had a class “student” that was added to a list of students taking the class with all the student data contained in the student object, it seamed to speed up the script when I used just passing the row number and later on extracting the student data when needed. Ways of implementing that class are currently considered and might be added in future versions of the app.
After all the data is extracted the script will create a 2D array containing the actual rosters. The function which does this is makeRoster(classes, ss, data). classes is the listClasses object from getData, data is a 2D array containing the values of the original spreadsheet cells.
makeRoster has 2 helper functions: initializeRoster(roster) where roster is the 2D array which will hold the roster data and addTeacher(name, teacher).
1 //preparing the roster headers
2 function initializeRoster(roster) {
3
4 var headers = ["First", "Last", "OSI", "Advisor", "Grade", "Class", "Room", "Teacher"];
5 roster[0] = [];
6 for (var i in headers)
7 roster[0][i] = headers[i];
8
9 return roster;
10 }
addTeacher(name, teacher) , name is the name of the currently processed teacher, teacher is a list of previously processed teachers, this function will check if a teacher is already on the list, if not add him to the list. This list is used later on in the script but is tracked here in order to achieve some level of efficiency (rather loop once than twice).
1 //checking if teacher is in the list of teachers, adds him if not
2 function addTeacher(name, teacher){
3
4 for(var i=0; i<teacher.length; i++)
5 if(teacher[i][0]==name)
6 return;
7
8 teacher.push([name]);
9 }
10
1 /*makes a 2D array of courses in the week
2 *additionally will make and store a list of teachers, later needed by the script, saving one looping through the data later on
3 *list of teacher names is saved in a new spreadsheet called rosterScriptData which will hold more data needed by the script*/
4 function makeRoster(classes, data) {
5 var roster = [];
6 //function will opet rosterScriptData in rosterScript collection, or create it if it does not exist
7 var teachers = [];
8 //prepare headers
9 roster = initializeRoster(roster);
10
11 //the loop and filling of the roster array data go asymmetric, k is used to reference the roster array
12 var k = 1;
13
14 /*needed for adding data on which day of the week is currently processed
15 *again the array will hold data for all courses in the week*/
16 var monday1 = false;
17
18 //looping through the list of classes in order to prepare the array
19 for (var i = 0; i < classes.length; i++) {
20
21 /*checking if the day header needs to be changed, and making sure there is a previos day if current
22 *day is Monday 1 there is no previous day*/
23 if (i > 0)
24 var previousN = classes[i - 1].getPeriod();
25 else
26 var previousN = "Monday 1";
27
28 var current = classes[i].getPeriod();
29
30 if (previousN != current || monday1 == false) {
31 monday1 = true;
32 roster[k] = [8];
33 roster[k][0] = current;
34 //in order for the array to have a square form this line is needed
35 roster[k][7] = current;
36 k++;
37 }
38
39 /*giving a listing of all the students taking some course
40 *to be precise the list of references to students taking a class
41 *the reference is the student's row number in the master data sheet*/
42 var students = classes[i].getStudents();
43 //looping through the students list and adding their data to the roster sheet
44 for (var j = 0; j < students.length; j++) {
45
46 roster[k] = [];
47 var z = students[j];
48 roster[k][0] = data[z][0];
49 roster[k][1] = data[z][1];
50 roster[k][2] = data[z][2];
51 roster[k][3] = data[z][3];
52 roster[k][4] = data[z][4];
53 roster[k][5] = classes[i].getName();
54 roster[k][6] = classes[i].getRoom();
55 roster[k][7] = classes[i].getTeacher();
56 k++;
57 }
58 //at the end adding the information about the number of students taking a course
59 roster[k] = [];
60 roster[k][7] = classes[i].getTeacher();
61 roster[k][8] = " count: " + students.length;
62
63 //adding the current teacher to the list of teachers, function will check if the teacher is in the list
64 addTeacher(classes[i].getTeacher(), teachers);
65 k++;
66 }
67
68 //filling eventual "gaps" in the array with null strings
69 for (var i = 0; i < roster.length; i++) {
70 for (var j = 0; j < 9; j++) {
71 if (!roster[i][j]) roster[i][j] = "";
72 }
73 }
74 var ss=getSpreadsheet("rosterScriptData", "rosterScript");
75 //open the teacher index sheet or insert the sheet if it does not exist
76 var sheet=ss.getSheetByName("teacher index");
77
78 if(!sheet){
79 ss.insertSheet("teacher index");
80 sheet=ss.getSheetByName("teacher index");
81 }
82 var teacherSheet=sheet.getDataRange().getValues();
83
84 //sorting the teachers list, will cut execution time later on
85 teachers.sort();
86 //writing to the teacher index sheet
87 sheet.getRange(1, 1, teachers.length, 1).setValues(teachers);
88 sheet.deleteRow(1);
89
90 //return the created and filled 2D array
91 return roster;
92 }
Lines 46-52 make use of the row reference of a student to get the required data. The rest of the code is straight forward and explained in the comments.
pushRosters(object)
pushRosters takes as parameter the array created in makeRosters and extract certain parts of it to write them in according sheets. To se the product of this please open: Roster Sheets
pushRosters has 1 helper function : getSpreadsheet(name, folderName), name is the name of the file to be opened, folderName is the name of the folder/collection the file is in.
1 function getSpreadsheet(name, folderName) {
2 //opens the collection
3 try{
4 var folder = DocsList.getFolder(folderName);
5 }
6 //creates colection
7 catch(e){
8 var folder= DocsList.createFolder(folderName);
9 }
10 //all the documents the script uses are spreadsheets, less files to loop through
11 var file = folder.getFilesByType("spreadsheet");
12 for (var i = 0; i < file.length; i++) {
13 //when the required file is found it is returned
14 if (file[i].getName() == name) return SpreadsheetApp.open(file[i]);
15 }
16 //if the file was not found the script will create it
17 var ss=SpreadsheetApp.create(name);
18 var fileId=ss.getId();
19 //put file into coresponding collection
20 DocsList.getFileById(fileId).addToFolder(folder);
21 //return file
22 return ss;
23 }
The function will try to open a collection if the collection is not present it will create it. Afterwards acquire all the files in the collection and search for a the required one. If the file is not found it will be created. This function is used in a few functions of the app.
1 //this function will extract data from a passed 2D array and write it to the according sheet
2 function pushRosters(data) {
3 //open spreadsheet
4 var ss = getSpreadsheet("Roster Sheets","rosterScript");
5
6 var sheetNames = [];
7 var sheetStart = [];
8
9 /*these are basically days of the week in which courses are held
10 *sheet Start will save the data where one day is over and the next starts*/
11 for (var i = 0; i < data.length; i++) {
12 if (data[i][1] == "" && data[i][0] != "") {
13 sheetNames.push(data[i][0]);
14 sheetStart.push(i + 1);
15 }
16 }
17 //going day by day and creating rosters for the day
18 for (var i = 0; i < sheetNames.length; i++) {
19 //will save data for formatting required cells
20 var boldAndColor = [];
21 //open current day-sheet
22 sheet = ss.getSheetByName(sheetNames[i]);
23 //if sheet is not present make it
24 if (!sheet) {
25 ss.insertSheet(sheetNames[i]);
26 sheet = ss.getSheetByName(sheetNames[i]);
27 }
28
29
30 var roster = [];
31 //the 2D array has the data for all days of the week, this indicates where a day starts and ends
32 var range = sheetStart[i + 1] - sheetStart[i];
33
34 if (!sheetStart[i + 1])
35 range = data.length + 1 - sheetStart[i];
36 range--;
37
38 /*the 2D array provided to the script and the roster array for a particular day are
39 *asymmetric, k keeps track of the roster array*/
40 var k = 0;
41 for (var j = sheetStart[i]; j < sheetStart[i] + range; j++) {
42 roster[k] = [];
43 for (var z = 0; z < 9; z++) {
44 roster[k][z] = data[j][z];
45 }//end inner for
46 //saving references on which cells need to be bolded and color changed
47 if (roster[k][4] == "")
48 boldAndColor.push(k + 2);
49
50 k++;
51 }//end outer for
52 //clear previous data if any
53 sheet.clear();
54 //writing data to the sheet
55 sheet.getRange(2, 1, range, 9).setValues(roster);
56 //changig the font weight and background color of certain cells
57 for (var j = 0; j < boldAndColor.length; j++) {
58
59 sheet.getRange(boldAndColor[j], 8).setFontWeight("bold").setBackground("#32CD32");
60 sheet.getRange(boldAndColor[j], 9).setFontWeight("bold").setBackground("#32CD32");
61 }
62 //trimming unnecessary cells, should prevent exceeding the spreadsheet limit
63 if (sheet.getLastColumn() + 1 < sheet.getMaxColumns())
64 sheet.deleteColumns(sheet.getLastColumn() + 1, sheet.getMaxColumns() - sheet.getLastColumn());
65 if (sheet.getLastRow() + 1 < sheet.getMaxRows()) sheet.deleteRows(sheet.getLastRow() + 1, sheet.getMaxRows() - sheet.getLastRow());
66 //sorting the data will speed up other functions of this app
67 sheet.sort(8);
68 //format headers of the sheet
69 formatSheetHeader(sheet);
70 }//end outer for
71 //this detail will just delete the default first sheet of the spreadsheet
72 sheet=ss.getSheets()[0];
73 if(sheet.getName()=='Sheet1'){
74 sheet.activate();
75 ss.deleteActiveSheet();
76 }
77
78 }
With this the first part of the app is concluded and the output is like in Roster Sheets. The teacher indexes can be reviewed here: script data.
Extracting student schedules
Note that the output of this part is not meant to be viewed directly from the spreadsheets but from a site featuring a Google app. The app will get the viewers email and search the corresponding sheet then display it in the site. This part has only one main function getScheduleData(begin, end), begin and end indicate which days of the week are to be considered. getScheduleData has one helper function not discussed above,
parsePeriod(header), header is a string containing the actual data sheet header for the given iteration.
pushRoster(object)
1 function parsePeriodValue(header) {
2
3 if (header.indexOf("M") != -1) return 0;
4
5 else if (header.indexOf("TU") != -1) return 1;
6
7 else if (header.indexOf("W") != -1) return 2;
8
9 else if (header.indexOf("TH") != -1) return 3;
10
11 else if (header.indexOf("F") != -1) return 4;
12
13 else return -1;
14 }
1 //getts the data and creates student schedules
2 function getScheduleData(begin, end) {
3 //reading the data from the spreadsheet
4 var ss = SpreadsheetApp.getActive();
5 var sheet = ss.getSheets()[0];
6
7 var data = sheet.getDataRange().getValues();
8 /*one spreadsheet is not enough to host all the schedules
9 *this help divide them into sequential spreadsheets*/
10
11 var name = "Student Schedule ";
12 var spreadSheetNumber = 1;
13
14 /*later on the script will have to acces particular schedules
15 *the script will store here only names and the id of the spreadsheet
16 *the required spreadsheet is in for faster access*/
17 var rosterData=getSpreadsheet("rosterScriptData", "rosterScript");
18 var indexSheet = rosterData.getSheetByName("student index");
19
20 if (!indexSheet)
21 indexSheet = rosterData.insertSheet("student index");
22 var indexSheetData = [];
23
24 //spreadsheet that will host the students schedule
25 var ss1 = getSpreadsheet(name + spreadSheetNumber,"rosterScript");
26 var id = ss1.getId();
27 //looping through the data sheets, this time by column not by row
28 for (var i = 1; i < data.length; i++) {
29 //will host a students schedule
30 var schedule = [];
31 //the schedule array is assimetric to the data array, these variables index the schedule array
32 var row = 0,
33 column = 0;
34 //initialyzing the schedule array, making sure at the end it can be written to the sheet
35 for (var z = 0; z < 21; z++) {
36 schedule[z] = [5];
37 for (var j = 0; j < 5; j++)
38 schedule[z][j] = "";
39
40 }
41
42 for (var j = begin; j <= end; j += 3, row += 3) {
43 //this parsePeriod is different then the others it is not concerned with the period, only with the day
44 column = parsePeriodValue(data[0][j]);
45 //moving to the next day
46 if (column != parsePeriodValue(data[0][j - 1]))
47 row = 0;
48
49 schedule[row][column] = data[i][j];
50 schedule[row + 1][column] = data[i][j + 1];
51 schedule[row + 2][column] = data[i][j + 2];
52
53 }//end for
54 /*199 schedules can be in one spreadsheet without exceding the cells limit
55 *this if will open a new spreadsheet if that number is reacher*/
56 if (i % 199 == 0) {
57 spreadSheetNumber++;
58 ss1 = getSpreadsheet(name + spreadSheetNumber, "rosterScript");
59 id = ss1.getId();
60 }
61 //keeping track on which student's schedule is in which spreadsheet
62 indexSheetData[i - 1] = [];
63 indexSheetData[i - 1][0] = data[i][5];
64 indexSheetData[i - 1][1] = id;
65
66 /*the schedule sheets are named by the students mail addres
67 *students are suposed to access their schedule through a site
68 *Session's getUser is used to make sure that a student can see only
69 *his schedule*/
70 var sSheet = ss1.getSheetByName(data[i][5]);
71
72 if (!sSheet) {
73 sSheet = ss1.insertSheet(data[i][5]);
74 var lastRow = schedule.length + 2,
75 lastColumn = schedule[0].length + 2;
76 var maxRow = sSheet.getMaxRows();
77 var maxColumn = sSheet.getMaxColumns();
78 //deleting unecesary cells (some addition space is left)
79 if (lastColumn < maxColumn) sSheet.deleteColumns(lastColumn, maxColumn - (lastColumn - 1));
80 if (lastRow < maxRow) sSheet.deleteRows(lastRow, maxRow - (lastRow - 1));
81 }
82
83
84 //writing the student schedule to the responding sheet
85 sSheet.getRange(1, 1, schedule.length, 5).setValues(schedule);
86
87
88 }
89 //writting the indexing data to the required sheet
90 indexSheet.getRange(1, 1, indexSheetData.length, 2).setValues(indexSheetData);
91 }
The output of this part is as can be viewed: Student Schedule 1, Student Schedule 2 and Student Schedule 3.
Extracting personalized teacher rosters
This part of the script was the hardest to write but also the one where I learned a great bit.
The script has to create N spreadsheets, where N is the number of teachers in a school. Every of those spreadsheets has M sheets, where M is the number of periods a teacher has per week. My worst case scenarios for M is 33, teachers are 39 producing a total of 1287 SpreadsheetApp calls. That is if the spreadsheets exist and contain the corresponding sheets. In the first run of the script the number of calls is doubled = 2574 . This makes the script very slow and as most Google Apps Script users know execution time is limited. That is why this part of the app has a build in “reviver”. The app will first check if all the spreadsheets are present and if all the references to the spreadsheet are correct. The references are a little trick I use to boost execution time. After checking the spreadsheets, or creating them if they are missing the app will create a new time trigger set for one minute later and “kill itself”.
1 //checks if the data the script has is accurate
2 function checkIndexData(){
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("invokeCreateTeacherRosters").timeBased().inTimezone(SpreadsheetApp.getActive().getSpreadsheetTimeZone()).at(date).create();
45 ScriptProperties.setProperty("sheetName", "Monday 1");
46 }
The trigger will start the function invokeCreateTeacherRosters(). This function will get the sheetName properties from the script, if it is null set it to Monday 1 and start creating the teacher rosters. sheetName serves as a checkpoint if the script has to restart more than once it will update this property and on the re-run just continue from the last point. invokeCreateTeacherRosters will eventually call createTeacherRosters(checkPoint).
createTeacherRosters(checkPoint)
The checkpoint saves the next period in the processing queue, initial call will have checkPoint=Monday 1. This function extracts data from the “master” roster sheet to make individual roster sheets for teachers. Helper functions inside are findId(list, string), copyArray(array, begin, end) , deleteTrig() and notify(). findId uses the previously created data pairing teacher names and spreadsheets id’s to find the id of the currently processed teacher. copyArray will copy the data from the master roster sheet in a given range (begin – end). deleteTrig() will delete all the triggers the script made in order to successfully finish executing. notify() send a notification email that all the rosters have been created.
1 //find the spreadsheet associated with a teacher
2 function findId(teacherIndex,teacherName){
3 for(var i=0; i<teacherIndex.length; i++)
4 if(teacherIndex[i][0]==teacherName)
5 return teacherIndex[i][1];
6
7 return -1;
8 }
1 //copy data from one array to another in a given range
2 function copyArray(data,begin,end){
3 var teacherData=[];
4
5 for(var i=begin; i<=end; i++){
6 teacherData[i-begin]=[];
7 for(var j=0; j<9; j++){
8 teacherData[i-begin][j]=data[i][j];
9 }
10 }
11 return teacherData;
12 }
1 //deleting programmatically created triggers
2 function deleteTrig(){
3
4 var trig=ScriptApp.getScriptTriggers();
5
6 for(var i=0; i<trig.length; i++)
7 ScriptApp.deleteTrigger(trig[i]);
8 }
9
1 function notify() {
2
3 var ss=SpreadsheetApp.getActive();
4 var owner=ss.getOwner().getEmail();
5
6 Logger.log(owner);
7 GmailApp.sendEmail(owner, "Teacher Rosters", "The teacher rosters have been created, You can access them in the Teacher Roster collection");
8 }
The function which handles the actual data in this part is createTeacherRosters(checkPoint)
1 function createTeacherRosters(checkPoint){
2 //both t1 and t2 will contain time stamps
3 var t1=new Date();
4 var t2;
5 /*prevT will keep track on how much time the previous iteration took
6 *currentT will keep track on the current iteration
7 *maxT is the limit this function has*/
8 var prevT=0;
9 var currentT;
10 var maxT=270;
11 //the teacher rosters will be made using the "master" roster sheet
12 var ss=getSpreadsheet("Roster Sheets","rosterScript");
13 var sheets=ss.getSheets();
14 var rosterData=getSpreadsheet("rosterScriptData", "rosterScript");
15
16 /*this sheet was made during while creating the "master" roster sheet
17 *references to every teacher's sheet have been made/updated in the checkIndexData()*/
18 var teacherIndex=rosterData.getSheetByName("teacher index");
19 if(!teacherIndex){
20 teacherIndex=rosterData.insertSheet("teacher index");
21 }
22
23 teacherIndex=teacherIndex.getDataRange().getValues();
24 /*the script will turn off and on again, in order to continue at the previous point
25 *it has to loop through previous parts, begin will have data on where to start actual work*/
26 var start=0;
27 while(sheets[start].getName()!=checkPoint){
28 start++;
29 }
30 //limit is used while testing to change the execution borders easier
31 var limit=sheets.length;
32 for(var i=start; i< limit; i++){
33 //gets the current day
34 var sheetName=sheets[i].getName();
35 //gets all the teachers rosters for a particular day
36 var data=sheets[i].getDataRange().getValues();
37 //here the app will divide the data for all teachers into personal groups
38 for(var j=1; j<data.length-1; j++){
39 var begin=j;//where the currents teacher data starts
40 var end;//where the data for current teacher will end
41 var teacherName=data[begin][7];
42 //finding the spreadsheet ide for the current teacher
43 var spreadsheetId=findId(teacherIndex, teacherName);
44 //opening the spreadsheet
45
46 if(spreadsheetId!=-1){
47 var teacherSpreadsheet=SpreadsheetApp.openById(spreadsheetId);
48 //opening the required sheet
49 var teacherSheet = teacherSpreadsheet.getSheetByName(sheetName);
50 //if the sheet is not present it will be created and headers will be formatted
51 if (!teacherSheet) {
52 teacherSpreadsheet.insertSheet(sheetName);
53 teacherSheet = teacherSpreadsheet.getSheetByName(sheetName);
54 formatSheetHeader(teacherSheet);
55 }
56 //while the data is associated with the current teacher loop in order to get the data range
57 while(j<data.length && data[j][7]==teacherName){
58 j++;
59 if(j==data.lenght-1)
60 break;
61 }
62 j--;
63 end=j;
64
65 //copy data from the master roster sheet in the given range, begin to end
66 var teacherData=copyArray(data,begin,end);
67 //write the data on the teachers sheet
68 teacherSheet.getRange(2,1,teacherData.length,9).setValues(teacherData);
69 }
70 }
71 t2=new Date();
72 //updating previous and current time
73 prevT=currentT;
74 currentT=(t2-t1)/1000;
75 //total time is currentT-previosT, t1 is initialized at the beginning of execution
76 var time=currentT-prevT;
77 /*this is a loose prediction on what total time will be after the next iteration
78 *if the time exceeds the maximum allowed time the script will create a time trigger
79 *save the next sheet name in the Script properties, and close execution
80 start itself again in a minute*/
81 if(currentT+time>=maxT && i<limit-1){
82 var date=new Date();
83
84 date.setMinutes(date.getMinutes()+1);
85 var name=sheets[i+1].getName();
86 //saving the checkPoint
87 ScriptProperties.setProperty("sheetName", name);
88 //adding new trigger
89 ScriptApp.newTrigger("invokeCreateTeacherRosters").timeBased().inTimezone(SpreadsheetApp.getActive().getSpreadsheetTimeZone()).at(date).create();
90 return;
91 break;
92 }
93 //after all the sheets are processed all the made triggers are deleted
94 if(i==limit-1){
95 deleteTrig();
96 //setting checkPoint to be first sheet, properties can not be deleted
97 ScriptProperties.setProperty("sheetName","null");
98 notify();
99 }
100 }
101 }
Most of the code is straight forward. It will get the data, loop “idle” until it reaches the checkpoint and afterwards process the data. The script finds the range in which a teachers data is, copies it to a different 2D array and imports it into the corresponding spreadsheet. After the process is done the user gets a notification email. One of the created rosters can be seen here.
However I think that this part of the code is interesting.
1 t2=new Date();
2 //updating previous and current time
3 prevT=currentT;
4 currentT=(t2-t1)/1000;
5 //total time is currentT-previosT, t1 is initialized at the beginning of execution
6 var time=currentT-prevT;
7 /*this is a loose prediction on what total time will be after the next iteration
8 *if the time exceeds the maximum allowed time the script will create a time trigger
9 *save the next sheet name in the Script properties, and close execution
10 start itself again in a minute*/
11 if(currentT+time>=maxT && i<limit-1){
12 var date=new Date();
13
14 date.setMinutes(date.getMinutes()+1);
15 var name=sheets[i+1].getName();
16 //saving the checkPoint
17 ScriptProperties.setProperty("sheetName", name);
18 //adding new trigger
19 ScriptApp.newTrigger("invokeCreateTeacherRosters").timeBased().inTimezone(SpreadsheetApp.getActive().getSpreadsheetTimeZone()).at(date).create();
20 return;
21 break;
22 }
23 //after all the sheets are processed all the made triggers are deleted
24 if(i==limit-1){
25 deleteTrig();
26 //setting checkPoint to be first sheet, properties can not be deleted
27 ScriptProperties.setProperty("sheetName","null");
28 notify();
29 }
After each iteration of the outer loop it checks the time the iteration took and “predicts” the how much time from the beginning of execution will the next iteration take if the time exceeds some maximum defined time a new trigger is made, the next sheet to process is saved and the execution stops. After processing all the data the triggers are deleted.
This function will also keep track if there has been a change to the roster if so the teacher is notified by email.
User interface
As for now the user interface has 4 parts:
- adding a menu to the spreadsheet
- “running” the master roster creation
- “running” the schedule creation
- “running” the teacher roster creation
Adding a menu is very similar to the example function from the documentation.
The rest 3 functions are “capsules” currently only displaying a notification that the data is being processed. Those will later on be added to the real user interface All of the functions have the same structure except of course which function they call. They use the same helper function spinner(app), app is the active user interface application.
1 function spinner(app) {
2
3 var proccessing = app.createLabel("Proccessing");
4
5 var imagePanel = app.createVerticalPanel();
6 imagePanel.setId("imagePanel");
7 var image = app.createImage("http://i39.tinypic.com/2jcww76.jpg");
8 image.setStyleAttribute('top', '110').setStyleAttribute('left', '220').setStyleAttribute('position', 'fixed');
9
10 imagePanel.setStyleAttribute('top', '120').setStyleAttribute('left', '210').setStyleAttribute('position', 'fixed');
11
12 imagePanel.add(image);
13 imagePanel.add(proccessing);
14
15 app.add(imagePanel);
16 ss = SpreadsheetApp.getActive();
17 ss.show(app);
18 }
1 function createRosters() {
2 var app = UiApp.createApplication();
3 spinner(app);
4 getData(6, 102);
5 app.close();
6 ss.show(app);
7 }
Now
The next steps are as planned:
- Creating teacher schedules
- Allowing administrators to see all the student schedules on the website
- A very important goal later on is to make the script more generic, that is provide options to accept data that is not in the current format
The script however might need a good code scrubbing after the first step.
