How to generate bulk students' Ids - Google apps script
Hi friends! in this article, we are going to see how we can generate bulk ids using apps script, google sheet and JavaScript. This type of function is used to complete long time work in a short while. We set this type of code for office managing purpose to generate the ids, results and other type of works' report. In following example we have used function for students' id generating. For this project we have used google sheet as data source.
First I have created an Id card model in html table that looks alike-
Example Sheet......
In above sheet we have arrange data as following:
- Column A contains students' id number.
- Column B contains students' image data.
- Column C contains students' name.
- Column D contains student's father's name.
- Column E contains students' DOB.
- Column F contains students' Age.
- Column G contains student's class in which he reads.
- Column H contains student's class section in which he reads.
- Column I contains student's address.
- Column J contains student's contact number.
Note:- I have used a formula in column F to get the student's Age.
This is the formula to get the age of the student =int(YEARFRAC(E2,today()))
I have pasted this formula in every cells of column F. DOB available on column E.
Now move to Google apps script (Gas) platform to create your app.
Make a copy of following code and paste it into Code.gs file and save it. Don't forget to change Google spreadsheet id with your own Google sheet id where you have stored students' data.
Code.gs
function doGet(e) {
return HtmlService.createTemplateFromFile('index').evaluate();
}
function idList(clv,sec){
var ws = SpreadsheetApp.openById("sheet id").getSheetByName("data");
var tr = ws.getDataRange().getValues();
var idcard = "";
for(var i= 1; i<tr.length ; i++){
if(sec == "ALL") { var sv = tr[i][7]!='';}else{ var sv = tr[i][7] == sec;};
if(clv == tr[i][6] && sv){
var logo = "https://encrypted-tbn0.gstatic.com/images?q=tbn:ANd9GcSa3vlz-5bPgXxTx1w1EyEOk61OGnvhmdPYBsi8-2Gws3o_sBqjQbM6&usqp=CAE&s"; //logo url
idcard+="<table id=tb height='181px' style='margin:5px;border-collapse:collapse;'class='stidc w3-col s3'><tr><td height=30px width=30px><img src="+logo+" width=30px height=30px></td><th colspan=4><font style=font-size:14px;> YOUR SCHOOL NAME</font><br><sup style=font-size:6px;>Address: </sup><font style=font-size:6px;> Your address goes here</font></th></tr><tr><th colspan=2>"+tr[i][0]+"</b></th><td align=center><b>"+tr[i][6]+" - "+tr[i][7]+"</b></td><td align=center>"+tr[i][4].toLocaleDateString()+" ( Age: "+tr[i][5]+")</td></tr><tr><td colspan=2 rowspan=4><img src='"+tr[i][1]+"' height=86px width=74px></td><td width=80px>Name:</td><td class=b>"+tr[i][2]+"</td></tr><tr><td>S/O name:</td><td class=b>"+tr[i][3]+"</td></tr><tr><td>Village:</td><td class=b>"+tr[i][8]+"</td></tr><tr><td>Mobile No:</td><td class=b>+91 "+tr[i][9]+"</td></tr><tr><th colspan=4><font size=1>For validation of card,Please visit: www.codylab.blogspot.com</font></th></tr></table>";
}
};
return idcard;
}
I have inserted html table inside the code. You have two change or modify according to your requirments. I have also highlighted the table area where you have need to make changes. Let's move to next step.
index.html
<!DOCTYPE html>
<html>
<title>Print multiple Id in one click</title>
<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>
table{font-size:12px;width:100%;border: 1px solid #CCC;font-family:times;}
td{padding: 0px; margin: 0px; border: 1px solid #CCC;}
th{ background-color: #232C8B; color: #FFF; font-weight: bold;}
.b{font-weight:bold;text-transform:capitalize;}
</style>
<body>
<div class="w3-row-padding w3-border-bottom">
<div class="w3-col s5">
<select id="clv"onchange="iList()"class="w3-select w3-col s8">
<option value="">CLASS</option>
<option value="I">I</option>
<option value="II">II</option>
<option value="III">III</option>
<option value="IV">IV</option>
<option value="V">V</optio>
</select>
<select id="sec" onchange="iList()"class="w3-select w3-col s4">
<option value="ALL">All</option>
<option value="A">A</option>
<option value="B">B</option>
<option value="C">C</option>
<option value="D">D</option>
</select>
</div>
<div class="w3-col s1 w3-center">
<i id="spin" class="fa fa-refresh fa-0"onclick="iList()" style="font-size:20px"></i>
</div>
<div class="w3-col s4 w3-left">
<i id="info"></i>
</div>
<div class="w3-col s1 w3-right">
<input type="button" name="btnprint" value="Print"onclick="pPrint('result')"class="w3-btn w3-teal w3-hover-red">
</div>
</div>
<div style="overflow: auto;height:520px;">
<div id="result"align="center"></div></div>
<script>
var spn = document.getElementById('spin');
var info = document.getElementById('info');
var resu = document.getElementById('result');
function iList() {
var cl = document.getElementById('clv');
var clv = cl.options[cl.selectedIndex].value;
var sv = document.getElementById('sec');
var sec = sv.options[sv.selectedIndex].value;
if(clv === ""){
info.innerHTML = "";
}else{
function fail(error){
resu.innerHTML = "<span style='color:red'>"+error+"</span>";
spn.classList.remove('fa-spin');
info.innerHTML = "";
};
function pass(list){
resu.innerHTML = list;
spn.classList.remove('fa-spin');
var rc =document.getElementById('tb').rows.length-1;
info.innerHTML = rc+" Records found.";
};
resu.innerHTML = "";
spn.classList.add('fa-spin');
info.innerHTML = "Working on...";
google.script.run.withFailureHandler(fail)
.withSuccessHandler(pass)
.idList(clv,sec);}};
</script>
</body>
</html>
| CodyLab Blogger CodyLab Blogger |
Have a nice day!
-------------------------- -------------------------