As mentioned somewhere in the first post this “app” is in fact two apps. On is to be integrated in the data spreadsheet and another in a site. This post is about the one which is a part of a site.
The function of this app is to get data from sheets created by the spreadsheet-app and display the data on a site. The main function is to display individual schedules for students and teacher, additionally the administration can see all the schedules for both teachers and students. The app uses Session service to determine who the visitor is and take the corresponding action.
I think this part is easier shown in terms of user cases, and that is how I will present it here. Bot before that let me again show the data format used by this app.
1) Student schedules are in the format as in this picture:
As you can see the spreadsheets are referred to according to the student e-mails not names, and student schedules are not kept in personal spreadsheets but grouped into large spreadsheets. The schedule formatting is simple, the data is simply written into the spreadsheet, this was an acceptable solution since those spreadsheets are not meant for direct viewing. You can read more about that here: The road so far.
2) Teacher schedules are as in this picture:
Every teacher has his own spreadsheet, the spreadsheet has multiple sheets containing teacher rosters and his schedule. This schedule is somewhat formatted since the spreadsheets are shared with the teachers and can be viewed directly. The names of spreadsheets correspond to teacher names. More on this: The road so far.
3) During the execution certain data pieces are saved by the script to be used later in order to make the script simpler and faster. This spreadsheet is called rosterScriptData and it’s soul purpose is to be used by the script. This spreadsheet, as for now, contains 2 sheets, one is the “teacher index” the other is “student index”. It holds names, emails and id’s of spreadsheets where the teacher/student schedule is. In addition teachers with “admin” privileges are flagged in that spreadsheet.
Case 1: Student and teacher visit
When a student or teacher visit the site the display is like below
The code will be at the end of this post, but what happens is :
The script will get the visitor email(Session service) and search for that e-mail in the rosterScriptData spreadsheet, get the corresponding spreadsheet id, take the data from that spreadsheet and display it. If the visitor is not in the database it will show a message explaining it.
Case 2: Teacher flagged as admin visit
Again the script will go through the previous steps, but if admin status is detected additionally two list boxes and a button are added. There is no real need for two boxes but I figured it would be easier to find for whom you are looking for if you only have to go through a list of students or teachers and no both at once.
What happens if the button is pressed, the script will check on which list box has been chosen , that is if the picked element is different from “Select Student” or “Select Teacher”. It will then behave as if that person has opened the site and overwrite the current display.
The code
In this case the code is all in one place, I will show it in order of (potential) execution. Note that in some cases not the whole function is displayed but only parts. In this case the whole function will be showed at some point bellow.
1 function doGet(e) {
2
3 var app = UiApp.createApplication();
4
5
6 var user = Session.getUser().getUserLoginId();
7 if (!user) app.add(app.createLabel("User not recognized!"));
8
9 //getSheet will find the the sheet asociated with this email
10 var sheet = getSheet(user);
11 ...
I think that this is as simple as it gets, the user mail is retrieved and the information is then passed to the getSheet procedure.
getSheet has 2 helper function, getProfessor and getStudent, those are very much alike the difference is due to data storage differences.
1 function getStudent(ss, user) {
2
3 var indexSheet = ss.getSheetByName("student index");
4 indexSheet = indexSheet.getDataRange().getValues();
5
6 for (var i = 0; i < indexSheet.length; i++) {
7
8 if (indexSheet[i][1] == user) {
9
10 return SpreadsheetApp.openById(indexSheet[i][2]).getSheetByName(user);
11
12 }
13
14 }
15
16 return null;
17 }
ss is passed to the function where ss is a reference to the rosterScriptData spreadsheet.user is containing the data on the current visitor, that is the current visitors email. As this function is only interested in students it will retrieve the data from the “student index” sheet and check on which student e-mail is equal to the current visitor’s email and return the sheet containing his schedule information. If there is no student with this email the function returns null.
1 function getProfessor(ss, user) {
2
3 var indexSheet = ss.getSheetByName("teacher index");
4 indexSheet = indexSheet.getDataRange().getValues();
5
6 for (var i = 0; i < indexSheet.length; i++) {
7
8 if (indexSheet[i][2] == user) {
9 if(indexSheet[i][3])
10 ScriptProperties.setProperty("isAdmin", "true");
11 else
12 ScriptProperties.setProperty("isAdmin", "false");
13 return SpreadsheetApp.openById(indexSheet[i][1]).getSheetByName("schedule");
14
15 }
16
17 }
18
19 return null;
20 }
As I said this is more or less the same as with getStudent with the small difference in which columns of the spreadsheet are checked for the email data and which contain a reference to the spreadsheet id. Another addition is that this function will check if the current user is an admin. The assumption is that a teacher can be a admin but a student can’t.
1 //function will first search for a student if the sheet reference is null it will search for a teacher
2 function getSheet(user) {
3
4 var ss = getSpreadsheet("rosterScriptData", "rosterScript");
5
6 var sheet = getStudent(ss, user);
7 if (sheet != null) {
8 ScriptProperties.setProperty("user", "student");
9
10 return sheet;
11 }
12
13
14 sheet = getProfessor(ss, user);
15 if (sheet != null) {
16 ScriptProperties.setProperty("user", "professor");
17 return sheet;
18 }
19
20 return sheet;
21 }
The function will first invoke getStudent, if the sheet variable is still null afterwards (meaning this email does not correspond to any student) it will try to fin the email among teachers. If the sheet reference is again null the script will assume that the visitor is not in the database and will display a informational message.
Back to doGet
1 function doGet(e) {
2
3 var app = UiApp.createApplication();
4
5
6 var user = Session.getUser().getUserLoginId();
7 if (!user) app.add(app.createLabel("User not recognized!"));
8
9 //getSheet will find the the sheet asociated with this email
10 var sheet = getSheet(user);
11
12 if (sheet == null) {
13 app.add(app.createLabel("There is no schedule for " + user));
14 return app;
15 }
16
17 //the layout of teacher and student schedules
18 //is different thus the script has to know who is displayed
19 var whoIs = ScriptProperties.getProperty("user");
20
21 //this will add the actual schedule to the app
22 showGrid(app,sheet,whoIs);
23 ...
Now that the script has the sheet with the visitors data it will remember if the visitor is a student or a teacher. As you could see above the schedules are in different formats thus the output of this script needs to have a different format. The grid with this data is going to be made in showGrid.
1 //will fetch the schedule from a sheet and display it in a grid
2 function showGrid(app,sheet,whoIs){
3
4 //standard headers
5 var header = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"];
6
7 var gridPanel = app.createVerticalPanel().setId("gridPanel");
8 gridPanel.setSize("900", "700");
9 var grid;
10
11 //the grid has smaller grids in it's cells
12 var grids = [];
13
14
15 //procedure for student display
16 if (whoIs == "student") {
17
18 var data = sheet.getDataRange().getValues();
19 //student schedules have 3 fileds for every course,
20 //course name, room and teacher, here this should
21 //be displayed in one cell that is why the dimensions
22 grid = app.createGrid(data.length / 3 + 2, data[0].length)
23 .setStyleAttribute('border', '1px solid black')
24 .setBorderWidth(1).setWidth("900px");
25 //setting up standard headers
26 for (var i = 0; i < 5; i++)
27 grid.setText(0, i, header[i]);
28
29 //this will take the data format it and put it in the grid
30 for (var i = 0; i < data.length; i += 3) {
31 grids[i] = [];
32
33 for (var j = 0; j < data[i].length; j++) {
34
35 grids[i][j] = app.createGrid(3, 1);
36 //if there is data for a course it will have at least a course name
37 grids[i][j].setText(0, 0, data[i][j]);
38 //some courses (e.g. Office hours have only a name thus the check, if there was no data on the room there will be no data on the teacher
39 if (i + 1 < data.length) {
40 grids[i][j].setText(1, 0, data[i + 1][j]);
41
42 if (i + 2 < data.length) grids[i][j]
43 .setText(2, 0, data[i + 2][j]);
44 }
45 //placing the child grid in the schedule grid
46 grid.setWidget(i / 3 + 1, j, grids[i][j]);
47 }
48
49 }
50 }
51 //this is the procedure for the teacher layout
52 else {
53
54 var data = sheet
55 .getRange(2, 2, sheet.getLastRow(), sheet.getLastColumn())
56 .getValues();
57 grid = app.createGrid( (data.length+1)/2, data[0].length-1)
58 .setStyleAttribute('border', '1px solid black')
59 .setBorderWidth(1).setWidth("900px");
60
61
62 for (var i = 0; i < 5; i++)
63 grid.setText(0, i, header[i]);
64
65 for (var i = 0; i < data.length; i += 2) {
66
67 grids[i] = [];
68 for (var j = 0; j < data[i].length; j++) {
69
70 if (data[i][j] != "") {
71 grids[i][j] = app.createGrid(2, 1);
72 grids[i][j].setText(0, 0, data[i][j]);
73 if (i + 1 < data.length) {
74 grids[i][j].setText(1, 0, data[i + 1][j]);
75 }
76 grid.setWidget(i / 2 + 1, j, grids[i][j]);
77 }
78 }
79
80 }
81 }
82 gridPanel.setStyleAttribute("background", color)
83 .setStyleAttribute('top', '0')
84 .setStyleAttribute('left', '0')
85 .setStyleAttribute('zIndex', '0')
86 .setStyleAttribute('position', 'fixed');
87
88 gridPanel.add(grid);
89 app.add(gridPanel);
90 return app;
91
92
93 }
This is a bit longer function but then again is is what the script is all about, displaying the data. Again the layout is different for teachers and students and the script uses if-else to do it. Most of the code is simple. Figure out who the visitor is, make a grid with according size and initialize the headers (day names). Then make "smaller” grids to host data on course name, room and teacher. Do this for every course in the week and put the smaller grids in the parent grid. What is ugly about this code is that when an admin requests to see a new schedule the data in the grid is not changed but the whole grid panel is overwritten. There might be a more “elegant” solution to this, but there surely is no faster and before all simpler. However to achieve this I had to “fix” the position and size of the grid and the grid panel. I also had to set the background color. That would be likely to create a problem but for now there is a global variable color (it is global because that way I can put it at the top of the code) where potential users can insert the color they would like the script to have. Later on this will be part of a onInstall function making it a bit easier.
Again back to doGet
1 function doGet(e) {
2
3 var app = UiApp.createApplication();
4
5
6 var user = Session.getUser().getUserLoginId();
7 if (!user) app.add(app.createLabel("User not recognized!"));
8
9 //getSheet will find the the sheet asociated with this email
10 var sheet = getSheet(user);
11
12 if (sheet == null) {
13 app.add(app.createLabel("There is no schedule for " + user));
14 return app;
15 }
16
17 //the layout of teacher and student schedules
18 //is different thus the script has to know who is displayed
19 var whoIs = ScriptProperties.getProperty("user");
20
21 //this will add the actual schedule to the app
22 showGrid(app,sheet,whoIs);
23
24 //and add the additional list boxes if the user is flaged as admin
25 if(ScriptProperties.getProperty("isAdmin")=="true"){
26 addAdminInterface(app);
27 }
28
29
30 return app;
31 }
And the last thing to do before displaying the initial app to the visitor is check whether the visitor is an admin or not. If not the app is simply displayed. If the user is an admin the script has to do some additional work.
1 function addAdminInterface(app){
2
3 var panel=app.createVerticalPanel().setId("adminPanel")
4 .setStyleAttribute('top', '0').setStyleAttribute('left', '900')
5 .setStyleAttribute('zIndex', '0').setStyleAttribute('position', 'fixed');
6 var studentList = app.createListBox().setId("studentList").setName("studentList");
7 var professorList = app.createListBox().setId("professorList")
8 .setName("professorList").setStyleAttribute('left', '1009')
9 .setStyleAttribute('zIndex', '0').setStyleAttribute('position', 'fixed');
10
11 var ss = getSpreadsheet("rosterScriptData", "rosterScript");
12 var indexSheet = ss.getSheetByName("student index");
13
14 //fetching student names and puting them on the studentsList
15 var list=indexSheet.getRange(1,1,indexSheet.getLastRow(), 1).getValues();
16 studentList.addItem("Select Student");
17 for(var i=0; i<list.length; i++){
18 studentList.addItem(list[i][0]);
19 }
20 //fetching teacher names and puting them on the list
21 indexSheet=ss.getSheetByName("teacher index");
22 list=indexSheet.getRange(1,1,indexSheet.getLastRow(), 1).getValues();
23 professorList.addItem("Select Teacher");
24 for(var i=0; i<list.length; i++){
25 professorList.addItem(list[i][0]);
26 }
27
28 //adding a button to the "admin" intercafe
29 var button = app.createButton('Get Schedule');
30 var handler = app.createServerClickHandler('click').addCallbackElement(panel);
31 button.addClickHandler(handler);
32
33 panel.add(studentList);
34 panel.add(professorList);
35 panel.add(button);
36 app.add(panel);
37 }
In brief the 2 list boxes are added in this function. Data from the rosterScriptData is read and inserted into the boxes, first for the student box then for the teacher box. A button for submission is added and the server handler (or what to do when the button is pressed) is assigned to the button.
When this is done the app is finally displayed to the user. And all of this is done fast enough.
What happens when the admin requests to see some schedule?
Well in some ways the script will behave just as if a new user came. The same procedure is done but with different functions. Originally the schedule would be searched for by email, here it is done by mail. After acquiring the correct sheet the display is the same as if a new user came.
1 function click(e){
2
3 var app=UiApp.getActiveApplication();
4 // app.getElementById("grid").setVisible(false);
5 var request = e.parameter.studentList;
6
7 //if this is ture the user requested a student's schedule
8 if(request!="Select Student")
9 var sheet=getStudentSheet(request);
10 //othervise a teacher shcedule is requested
11 else{
12
13 request= e.parameter.professorList;
14 //if the above condition and this are false just the button has been clicked and there is no work to be done
15 if(request != "Select Teacher")
16 var sheet=getProfessorSheet(request);
17 else
18 return app;
19 }
20 //in this case we are not interested in who the user is but who the requested person is
21 var whoIs=ScriptProperties.getProperty("request");
22 showGrid(app,sheet,whoIs);
23
24
25 return app;
26
27 }
The script will first check if the request has been made from the student list. If so the getStudentSheet procedure is invoked, this procedure will eventually return a reference to the sheet with the schedule data. Otherwise the script will try the teacher list, invoke getProfessorSheet again getting a reference to the sheet with the schedule data. If however the request has not been made from either of those the app will not do anything.
After the sheet reference has been made the script will check who the person is who’s schedule is to be displayed (teacher or student) and ultimately create the new grid with this data.
1 function getStudentSheet(user) {
2 var ss = getSpreadsheet("rosterScriptData", "rosterScript");
3 var indexSheet = ss.getSheetByName("student index");
4 indexSheet = indexSheet.getDataRange().getValues();
5
6 for (var i = 0; i < indexSheet.length; i++) {
7
8 if (indexSheet[i][0] == user) {
9 ScriptProperties.setProperty("request", "student");
10 return SpreadsheetApp.openById(indexSheet[i][2]).getSheetByName(indexSheet[i][1]);
11
12 }
13
14 }
15
16 return null;
17 }
Again this procedure is very similar to the original getStudent the only difference is that it is not checking the mail column from the “student index” sheet but the name column, and it is not setting script properties for the visitor but script properties “request”. The same will apply for the getProfessorSheet method below.
1 function getProfessorSheet(user) {
2 var ss = getSpreadsheet("rosterScriptData", "rosterScript");
3 var indexSheet = ss.getSheetByName("teacher index");
4 indexSheet = indexSheet.getDataRange().getValues();
5
6 for (var i = 0; i < indexSheet.length; i++) {
7
8 if (indexSheet[i][0] == user) {
9 ScriptProperties.setProperty("request", "professor");
10 return SpreadsheetApp.openById(indexSheet[i][1])
11 .getSheetByName("schedule");
12
13 }
14
15 }
16
17 return null;
18 }
The complete code for this app is in this file : Site App for Roster.
The work in this script is slowly coming to an end. In the next few days I will go back to and try to improve the code for both parts. In the hangouts with my mentors we have come to see that some “security” checks in the script are not necessary , that some are missing. As with many things during the actual work I have learned new “tricks” which could be used to improve parts of the script. As for “actual” and “visible” work there is an important part coming up… The script is very rough, it will work only with the data format it currently uses. There are several ways to change that, one of them is to make the attempt of making the script “smart’ so it can search for data in spreadsheets which are not in the supported format. But that approach could be dangerous, there is no guarantee that the script will always find what it needs. The other approach, the one I will go for, is to make a plugin for the script. The plug in would be able to recognize (for now) one of the major data formats (for this kind of data) and arrange the data in a format it can be used. As soon as there is something to blog about it will be here.
For a preview of the project so far: project tracker
Cheers
