How to publish online school result using google sheet and apps script?
We will see in this blog how any school publishes its online result, here we will use apps script and google sheet for this and through that we will show how you can do this work easily. For this, first of all we will arrange data into google sheet, after that we will create apps script project and give refrences to the script which will generate the output for us which we can read from the roll number on the html page.
Here we are showing it to you as an example, you can design and arrange your sheet according to your requirment. After arrangement, you can give references to apps script to prepare it for the output data. From where your students can get their results easily in the HTML page. The sheet we have used here is only for the example, you should try it first and then redesign it according to your need.
To show the result correctly, first of all you should give reference to google sheet properly, after that you should give reference to apps script according to google sheet data, so that student result can be easily read in html page by id. You can do it according to your choice and after reference off, you can design it by keeping that data inside the table wherever you want, which can be shown as output in the HTML page. If you want, you can also keep the table inside the script or in html output body. In the example we have shown, we have arranged the table inside the script, from where the rsult send to the html output page.
Below I am giving it Code.gs, with the help of which we have read the result by Student's roll number.
Code.gs
function doGet(e){
return HtmlService.createTemplateFromFile("index")
.evaluate()
.setTitle("CodyLab");
}
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename).getContent();
}
function sub(v){
var ss = SpreadsheetApp.openById("sheet id");
var sheet = ss.getSheetByName("Sheet1");
var flag = 1 ;
var lr = sheet.getLastRow();
for(var i = 2;i <= lr;i++){
if(v.roll == sheet.getRange(i, 1).getValue()){
flag = 0;
var tt = sheet.getRange(1, 2).getValue();
var h1 = sheet.getRange(1, 3).getValue();
var h2 = sheet.getRange(1, 5).getValue();
var h3 = sheet.getRange(1, 6).getValue();
var h4 = sheet.getRange(1, 7).getValue();
var h5 = sheet.getRange(1, 8).getValue();
var h6 = sheet.getRange(1, 9).getValue();
var h7 = sheet.getRange(1, 10).getValue();
var b1 = sheet.getRange(i, 2).getValue();
var b2 = sheet.getRange(i, 3).getValue();
var b3 = sheet.getRange(i, 4).getValue();
var m1 = sheet.getRange(i, 5).getValue();
var m2 = sheet.getRange(i, 6).getValue();
var m3 = sheet.getRange(i, 7).getValue();
var m4 = sheet.getRange(i, 8).getValue();
var m5 = sheet.getRange(i, 9).getValue();
var m6 = sheet.getRange(i, 10).getValue();
var lo = "https://www.pngitem.com/pimgs/m/436-4365026_ibps-logo-hd-png-download-png-download-ibps.png";
var data ='<table class="tg"><thead><tr><th class="tg-0pky"><img src='+lo+' height="50px"width="50px"></th><th class="tg-0pky" colspan="5"><h4>'+tt+'</h4></th></tr></thead><tbody><tr><td class="tg-0pky">Roll No.</td><td class="tg-0pky">'+v.roll+'</td><td class="tg-0pky">Name:</td><td class="tg-0pky"colspan="3">'+b1+'</td></tr><tr><td class="tg-0pky"colspan="6">Results:</td></tr><tr><td class="tg-0pky">slr.</td><td class="tg-0pky">Competition</td><td class="tg-0pky">Maximum score</td><td class="tg-0pky">CutOff Score</td><td class="tg-0pky">Obtained Score</td><td class="tg-0pky">Remarks</td></tr><tr><td class="tg-0pky">1.</td><td class="tg-0pky">Reasoning</td><td class="tg-0pky">'+h2+'</td><td class="tg-0pky">11.50</td><td class="tg-0pky">'+m1+'</td><td class="tg-0pky">qualified</td></tr><tr><td class="tg-0pky">2.</td><td class="tg-0pky">General awareness</td><td class="tg-0pky">'+h3+'</td><td class="tg-0pky">17.10</td><td class="tg-0pky">'+m2+'</td><td class="tg-0pky">qualified</td></tr><tr><td class="tg-0pky">3.</td><td class="tg-0pky">Computer knowledge</td><td class="tg-0pky">'+h4+'</td><td class="tg-0pky">29.20</td><td class="tg-0pky">'+m3+'</td><td class="tg-0pky">qualified</td></tr><tr><td class="tg-0pky">4.</td><td class="tg-0pky">English</td><td class="tg-0pky">'+h5+'</td><td class="tg-0pky">23.50</td><td class="tg-0pky">'+m4+'</td><td class="tg-0pky">not qualified</td></tr><tr><td class="tg-0pky">5.</td><td class="tg-0pky">Hindi</td><td class="tg-0pky">'+h6+'</td><td class="tg-0pky">-</td><td class="tg-0pky">'+m5+'</td><td class="tg-0pky">-</td></tr><tr><td class="tg-0pky">6.</td><td class="tg-0pky">Quantitative aptitude</td><td class="tg-0pky">'+h7+'</td><td class="tg-0pky">30.10</td><td class="tg-0pky">'+m6+'</td><td class="tg-0pky">qualified</td></tr></tbody><tfooter><tr><td class="tg-0pky"colspan="2">Total</td><td class="tg-0pky">'+h1+'</td><td class="tg-0pky">111.4</td><td class="tg-0pky">'+b2+'</td><td class="tg-0pky"></td></tr></tfooter></table>*Not secured cutoff score of English language, whereever necessary.';
return data;
}
}
if(flag==1){
var data ="Roll not found.";
return data;
}
};
The code you are seeing above is the code.gs, you copy and paste the code inside your project Code.gs file and make save it, below I am giving the code of index.html, you paste that code in index.htm and make save it.
index.html
<!DOCTYPE html>
<html>
<title>CodyLab</title>
<base target="_top">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
<style>
.loader {margin-left:45%;
border: 2px solid #f3f3f3;
border-radius: 50%;
border-top: 2px solid #3498db;
width: 120px;
height: 120px;
-webkit-animation: spin 2s linear infinite; /* Safari */
animation: spin 2s linear infinite;
}
/* Safari */
@-webkit-keyframes spin {
0% { -webkit-transform: rotate(0deg); }
100% { -webkit-transform: rotate(360deg); }
}
@keyframes spin {
0% { transform: rotate(0deg); }
100% { transform: rotate(360deg); }
}
.tg {border-collapse:collapse;border-spacing:0;width:600px;}
.tg td{border-color:black;border-style:solid;border-width:1px;font-family:Arial, sans-serif;font-size:14px;}
.tg th{border-color:black;border-style:solid;border-width:1px;font-family:Arial, sans-serif;font-size:14px;}
.tg .tg-0pky{border-color:inherit;text-align:left;vertical-align:top}
</style>
<body>
<div id="lod" class="w3-modal">
<div class="w3-center">
<div class="loader"></div>
</div>
</div>
<?!= include('js'); ?>
<div class="w3-row w3-small"style="margin-top:20px;margin-left:25%;">
<h4 >RESULT OF ABCD (Online Examination-2022)</h4>
<form id="lab" onsubmit="event.preventDefault();subm(this)">
<div class="w3-col s8">
<div class="w3-col s1">Roll No.:</div>
<input type="number"name="roll"placeholder="Enter roll"class="w3-input w3-border w3-round-large w3-col s5"required>
<input type="submit"class="w3-button w3-blue w3-round-large w3-col s3 w3-margin-left">
</div>
</form>
</div>
<div id='PrintDiv'>
<div id="rs"class="w3-margin-top"style="margin-left:25%;"></div>
</div>
<input type="button" name="btnprint" value="Print" onclick="Print('PrintDiv')"/>
</body>
</html>
js.html
I have created a small file of JavaScript. Copy what is given below and paste it inside your project file js.html
<script>
function subm(v){
document.getElementById('lod').style.display='block';
google.script.run.withFailureHandler(fail)
.withSuccessHandler(pass)
.sub(v);}
function fail(v){
document.getElementById('lod').style.display='none';
document.getElementById("rs").innerHTML = v;}
function pass(v){
document.getElementById('lod').style.display='none';
document.getElementById("rs").innerHTML = v;
document.getElementById("lab").reset();}
</script>
<script>
function Print(DivID) {
var iPrint = document.getElementById(DivID).innerHTML;
var docprint=window.open("","");
docprint.document.open();
docprint.document.write('<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"');
docprint.document.write('<head><meta name="viewport"
content="width=device-width, initial-scale=1.0">');
docprint.document.write('<title>Print</title>');
docprint.document.write('<style type="text/css">@page {size:auto;margin:0.2em;}');
docprint.document.write('table,td{ border:1px dashed red;}</style>');
docprint.document.write('</head><center><body onLoad="self.print()">');
docprint.document.write(iPrint);
docprint.document.write('</body></center></html>');
docprint.document.close();
docprint.focus();
}
</script>
>>>TRY TO CHECK OUT , IF ANY ERROR FOUND. PLEASE LET ME KNOW BY COMMENT.
I'LL TRY MY LEVEL BEST TO FIX THE PROBLEM.
THANKS FOR VISITING CODYLAB
Have a nice day! -------------------------- -------------------------