Google announced that Apps Script UI service will be discontinued next July. At my school some teachers are using Google sheets gradebooks together with app scripts code to publish the grades as a webpage, so that each student can see only his/her grades. If next school year this is not going to work any more, we need to find a new way of doing the same. Google suggests using Google App maker.
Since I am a teacher and not a developer, I have no idea of how to use the App maker. I am ready to learn, though, if it is the best way to solve the problem.
But before I use a lot of the time I don't really have in learning to use a new tool, I would really appreciate if you could tell me that what we are doing with Apps Script can be done using the Google App maker.
I would be really grateful if you could show me the way to start migrating the script code to App maker.
This is a sample of the Google sheet we use as Gradebook: https://docs.google.com/spreadsheets/d/1Id3-5o6JpNJWkHWUjOSWaJ3PLRveOdYoAynURxzNYFE/edit?usp=sharing
This is the code we use in the Google sheets script editor:
//Script-as-app template.
function doGet() {
var app = UiApp.createApplication();
return app;
}
function myClickHandler(e) {
var app = UiApp.getActiveApplication();
app.close();
return app;
}
var ss = SpreadsheetApp.openById('1Id3-5o6JpNJWkHWUjOSWaJ3PLRveOdYoAynURxzNYFE');//Put your spreadsheet key here
var sh1 = ss.getSheetByName('StudentGrades');//Meta sheet name
var logsheet = ss.getSheetByName('logsheet');
var data = sh1.getDataRange().getValues();
var user = Session.getEffectiveUser()
Logger.log(user)
function doGet() {
var app = UiApp.createApplication().setTitle('English Grades - Teacher: Anna C');//App title
if(!getcol(user)){
var warn = app.createLabel().setWidth('400').setText("Your results are not available or your email is not in the list. Please contact your teacher.");
// if user is not in the list, warning + return
app.add(warn)
return app
}
var grid = app.createGrid(data.length, 3).setWidth('900px').setBorderWidth(1).setCellPadding(6).setCellSpacing(6).setStyleAttribute('borderCollapse','collapse').setId('grid');
//Overall table styling
var text = app.createLabel(user).setWidth('250px');
var col = getcol(user)
grid.setWidget(0,1,text).setText(0, 0, 'ENGLISH RESULTS FOR');
for(n=1;n<data.length;++n){
grid.setText(n, 0, string(data[n][0]));
grid.setText(n, 1, string(data[n][1]));
grid.setText(n, 2, string(data[n][col]));
//FIRST ROW HEADER (Dark blue background) - row 0
grid.setRowStyleAttributes(0, {fontFamily: "Verdana, sans-serif",'textAlign':'left','fontWeight':'bold',color: "#fff",'background':'#384c80','border':'1px solid #c0c0c0'});
//COURSE-JUNE-SEPTEMBER MARKS (Dark red background) - rows 288,294,298
grid.setRowStyleAttributes(250, {fontFamily: "Verdana, sans-serif",'textAlign':'left','fontWeight':'bold',color: "#fff",'background':'#900202','border':'1px solid #c0c0c0'});
//TERMS-TERM MARKS (Orange background) - rows 5,105,202,289,291,293
grid.setRowStyleAttributes(5, {fontFamily: "Verdana, sans-serif",'textAlign':'left','fontWeight':'bold',color: "#fff",'background':'#ff953f','border':'1px solid #c0c0c0'});
//SUMMER-1ST T MARKS (Orange bold font)
grid.setRowStyleAttributes(20, {fontFamily: "Verdana, sans-serif",'textAlign':'left','fontWeight':'bold',color: "#ff953f",'background':'#fff','border':'1px solid #c0c0c0'});
//DIMENSIONS MARKS (Dark blue bold font)
grid.setRowStyleAttributes(54, {fontFamily: "Verdana, sans-serif",'textAlign':'left','fontWeight':'bold',color: "#384c80",'background':'#fff','border':'1px solid #c0c0c0'});
//Level B green exercises (Dark green font)
grid.setRowStyleAttributes(30, {fontFamily: "Verdana, sans-serif",'textAlign':'left','fontWeight':'normal',color: "#009A46",'background':'#fff','border':'1px solid #c0c0c0'});
//Level A red exercises (Red font)
grid.setRowStyleAttributes(45, {fontFamily: "Verdana, sans-serif",'textAlign':'left','fontWeight':'normal',color: "#ff0000",'background':'#fff','border':'1px solid #c0c0c0'});
}
app.add(grid);
return app
}
function string(value){
Logger.log(typeof(value))
if (typeof(value)=='string'){return value};// if string then don't do anything
if (typeof(value)=='number'){return Utilities.formatString('%.2f',value)};// if number then format with 2 decimal
if (typeof(value)=='object'){return Utilities.formatDate(value, Session.getTimeZone(), "MM-dd")};//object >> date in this case, format month/day
return 'error'
}
function getcol(mail){
if(data[0].toString().indexOf(mail.toString())!=-1){
for(zz=1;zz<data[0].length;++zz){
if(data[0][zz] == mail){var colindex=zz;break}
}
return colindex
}
return false
}
And this is what our students see after they log in with their gmail account. They see their results as a webpage. (I have created a sample student's mail so that I can show you what they see).
Go to https://script.google.com/a/jverdaguer.com/macros/s/AKfycbwGmZr-UVw1hAtkyQEM8mqax1UkiKEGrF1wCOE/exec and log in with the mail [email protected] and the password sstudent2018
