Student fee management system AppsScript

Automations and Fees management in apps script

This page is a part of Student registration form, Both projects work on same sheet. The projects are based on School management system.

This script is written in Google Apps Script, which is a cloud-based JavaScript platform for automating tasks in G Suite applications such as Google Sheets. The code opens a Google Sheet by its ID and retrieves data from multiple sheets (Diary, PData, and Ptra) within the workbook.

The getDetail function retrieves data based on the input "form.id". It searches the sheet named "alldatastore" for a match with "code" (constructed as "E" + form.id). If a match is found, the function retrieves various data from different columns in the Diary sheet and stores them in separate variables. The function also retrieves data from two other sheets (PData and Ptra) and creates a table "wr" with the retrieved data and returns it.

The code defines an apps script function called "pPro" that is used to process a form. The function performs several tasks including:

creating a unique transaction ID (tid) using a zero-padded number
extracting values from the form (ac, cl, sn, pa, ma, nt) and storing them in variables
getting the current date and time and storing them in variables (d, mtn, dt, mt, yt, th, tm, ts, stm, std)
appending a row to a sheet called "wsT" with the extracted values and calculated date/time
looking up the user's account balance in a sheet called "wsP" and updating the balance
sending a text message to the user's phone number with the updated balance.

adxt

Code.gs

function doGet(e) {

return HtmlService

.createHtmlOutputFromFile('index')

.setTitle("CodyLab Technology");

}

var wss = SpreadsheetApp.openById("----------------------DambC6Y2NdeDE-----")

var ws = wss.getSheetByName("alldatastore");

var wsP = wss.getSheetByName("Diary");

var wsT = wss.getSheetByName("PData");

var tws = wss.getSheetByName("Ptra");

function getDetail(form){

var code ="E"+form.id;

var flag=1;

var lr = wsP.getLastRow()+1;

var lr = ws.getLastRow()+1;

for(var i = 2;i <= lr;i++){

var rid = wsP.getRange(i, 1).getValue();

var rid = ws.getRange(i, 1).getValue();

if(rid == code){

flag=0;

var img = ws.getRange(i, 2).getValue();

var b1 = wsP.getRange(i, 7).getValue();

var b2 = wsP.getRange(i, 2).getValue();

var b3 = wsP.getRange(i, 3).getValue();

var b4 = wsP.getRange(i, 4).getValue();

var b5 = wsP.getRange(i, 35).getValue();

var b6 = wsP.getRange(i, 36).getValue();

var b7 = wsP.getRange(i, 37).getValue();

var b8 = wsP.getRange(i, 26).getValue();

var b9 = wsP.getRange(i, 27).getValue();

var b10 = wsP.getRange(i, 28).getValue();

var b11 = wsP.getRange(i, 29).getValue();

var b12 = wsP.getRange(i, 30).getValue();

var b13 = wsP.getRange(i, 31).getValue();

var b14 = wsP.getRange(i, 32).getValue();

var b15 = wsP.getRange(i, 33).getValue();

var b16 = wsP.getRange(i, 34).getValue();

var b17 = wsP.getRange(i, 9).getValue();

var b18 = wsP.getRange(i, 10).getValue();

var b19 = wsP.getRange(i, 11).getValue();

var b20 = wsP.getRange(i, 12).getValue();

var b21 = wsP.getRange(i, 13).getValue();

var b22 = wsP.getRange(i, 14).getValue();

var b23 = wsP.getRange(i, 17).getValue();

var b24 = wsP.getRange(i, 18).getValue();

var b25 = wsP.getRange(i, 23).getValue();

var b26 = wsP.getRange(i, 24).getValue();

var b27 = wsP.getRange(i, 25).getValue();

var tr = wsT.getDataRange().getValues();

var tt = tws.getDataRange().getValues();

var bp = wsP.getRange(i, 19).getValue();

var bs = wsP.getRange(i, 20).getValue();

var wr ='<table width=100% ><tr><th width=10% align=left>Tr.Id:</th><th width=20% align=left>Date & Time:</th><th width=10% align=left>Mode:</th><th width=10% align=left>Paid:</th><th width=10% align=left>Margin:</th><th width=40% align=left>Note:</th></tr>';

for(var i=0; i<tr.length ; i++){

var ico = tr[i][4];

if(code == ico){

var ds = tr[i][1];

var ts = tr[i][2];

wr+="<tr><td>"+tr[i][0]+"</td><td>"+ds.toLocaleDateString()+" , "+ts.toLocaleTimeString()+"</td><td>"+tr[i][3]+"</td><td>₹ "+tr[i][7]+"</td><td>₹ "+tr[i][8]+"</td><td>"+tr[i][9]+"</td></tr>";} }

var wt ='<table width=100% class=tbt><tr><th colspan=2 align=left class=yty>>>Previous year paid detail:</th><th class=yty id=pttp colspan=5></th></tr>';

for(var i=0; i<tt.length ; i++){

var tco = tt[i][4];

if(code == tco){

var tts = tt[i][1];

var tst = tt[i][2];

wt+="<tr><td width=10%>"+tt[i][0]+"</td><td width=20%>"+tts.toLocaleDateString()+" , "+tst.toLocaleTimeString()+"</td><td width=10%>"+tt[i][3]+"</td><td width=10% class=ptp>"+tt[i][7]+"</td><td width=10% class=ptm>"+tt[i][8]+"</td><td width=40%>"+tt[i][9]+"</td></tr>";} }

var result=[code,img,b1,b2,b3,b4,b5,b6,b7,b8,b9,b10,b11,b12,b13,b14,b15,b16,b17,b18,b19,b20,b21,b22,b23,b24,b25,b26,b27,wr,bs,bp,wt];

return result;

}}

if(flag==1){

var result=["Enrollment No."+code+" is not exist."];

return result;

} }

const zeroPad = (num, places) => String(num).padStart(places, '0')

function pPro(form) {

var aro = wsT.getLastRow()-1;

var tid ="P"+zeroPad(aro, 5);

var id = form.ac;

var cls = form.cl;

var nm = form.sn;

var pm = form.pa;

var mm = form.ma;

var nt = form.nt;

var d = new Date();

var month = ["January","February","March","April","May","June","July","August","September","October","November","December"];

var mtn = month[d.getMonth()];

var dt = d.getDate();

var mt = d.getMonth()+1;

var yt = d.getFullYear();

var th = d.getHours();

var tm = d.getMinutes();

var ts = d.getSeconds();

var stm = th+":"+tm+":"+ts;

var std = dt+"/"+mt+"/"+yt;

if(pm >0){ var pp = pm;}else{ var pp = "0";}

if(mm >0){ var mp = mm;var dis = "You also got remit of "+mm+" Rs.";}else{ var mp = "0";var dis = " ";}

wsT.appendRow([tid,std,stm,"Cash",id,nm,cls,pp,mp,nt]);

var lr = wsP.getLastRow()+1;

for(var i = 2;i <= lr;i++){

var rid = wsP.getRange(i, 1).getValue();

if(rid == id){

var bp = wsP.getRange(i, 13).getValue();

var bm = wsP.getRange(i, 14).getValue();

var br = wsP.getRange(i, 17).getValue();

var sr = wsP.getRange(i, 18).getValue();

var ml = wsP.getRange(i, 5).getValue();

var fn = wsP.getRange(i, 2).getValue();

var ntd = fn.split(" ");

var tr = wsT.getDataRange().getValues();

for(var i=0; i<tr.length ; i++){

var ico = tr[i][4];

if(id == ico){

var ds = tr[i][1];

var ts = tr[i][2];

wr+="<tr id=nob><td width=10%>"+tr[i][0]+"</td><td width=20%>"+ds.toLocaleDateString()+" , "+ts.toLocaleTimeString()+"</td><td width=10%>₹ "+tr[i][3]+"</td><td width=10%>₹ "+tr[i][7]+"</td><td width=10%>₹ "+tr[i][8]+"</td><td width=40%>"+tr[i][9]+"</td></tr>";} }

if(ml !=""){

var smsb ="Dear "+ntd[0]+", Just we got Rs."+pp+" from you. "+dis+" Your new "+sr+" balance is "+br+" Rs. bit.ly/eo0c";

var apikey ="";

var url = "https://www.fast2sms.com/dev/bulkV2?authorization="+apikey+"&sender_id=TXTIND&message="+smsb+"&route=v3&numbers="+ml+"";

var options = {

"async": true,

"crossDomain": true,

"method": "GET"

}

var response = UrlFetchApp.fetch(url, options);

var data = JSON.parse(response.getContentText())

var res= data.message;

}else{

var res= 'Mobile number not exist.';

}

var result = ["<b style=color:green;>Payment successfully made.</b> | "+res,bp,bm,br,sr,wr];

return result;

}}}

This is an HTML page with styling information, some JavaScript functions, and form elements. The page is designed to display student information after fetching the data from an external source with the help of Google Script and jQuery. The page contains form elements like input boxes and text areas and also has styles defined for different HTML elements like tables, buttons, and forms. Some of the JavaScript functions are used to manipulate the data being displayed, prevent form submissions, and show a loading message while fetching the data.

index.html

<html> <head> <base target="_top"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <script src="https://code.jquery.com/jquery-1.12.4.js"></script> <style> tr:nth-child(odd){background:#cece ;} tr:nth-child(even){background: #efe} td{border:0px solid lightpink;} .yty{border-top:1px solid white;} table,td{border-collapse:collapse;font-family:times;font-size:12px;border-radius:5px;border:yes;} td[class=b]{text-transform:uppercase;font-weight:bold;} input,textarea{font-family:arbic;font-size:12px;border:1px dashed green;} .tmn{margin-top:0.01em;} ay{ background: red; font-size: 12px; display: inline-block; color: white; padding: 2px 5px; cursor: pointer; border:1px solid black; border-radius: 10px; } img{border:none;} #box{ width: 250px; overflow: hidden; box-shadow: 0 0 10px green; border-radius: 10px; position: absolute; top: 20%; left: 50%; background: white; transform: translate(-50%, -50%); z-index: 9999; padding: 10px; text-align: center; display: none; }body{width:60%;margin-left:200px;margin-top:20px;} </style> <script> function cp() { var zd = (num, places) => String(num).padStart(places, '0') var pd = document.getElementById('iptv').value; document.getElementById('id').value = zd(pd, 6); } function preventFormSubmit() { var forms = document.querySelectorAll('form'); for (var i = 0; i < forms.length; i++) { forms[i].addEventListener('submit', function(event) { event.preventDefault(); }); } } window.addEventListener('load', preventFormSubmit); function SubmitID(form) { var idc = document.getElementById("id").value; if (idc === "") { document.getElementById('loader').innerHTML = "<b style=color:red;>Enter enrollment number"; }else{ document.getElementById('loader').innerHTML = 'Getting information...'; google.script.run.withFailureHandler(onFailure) .withSuccessHandler(feed) .getDetail(form);}} function feed(data) { var idc = document.getElementById("id").value; if ("E"+idc == data[0]) { document.getElementById('loader').innerHTML ="Detail of ID: <b style=color:green;>"+data[0]; document.getElementById('dih').innerHTML =data[0]; document.getElementById('ac').value =data[0]; document.getElementById('cl').value =data[2]; document.getElementById('img').src =data[1]; document.getElementById('cls').innerHTML ="ID NO: "+data[0]; document.getElementById('sname').innerHTML =data[3]+"  Class:"+data[2]; document.getElementById('sn').value =data[3]; document.getElementById('fn').innerHTML =data[4]; document.getElementById('vil').innerHTML =data[5]; document.getElementById('Jan').innerHTML =data[6]; document.getElementById('Feb').innerHTML =data[7]; document.getElementById('Mar').innerHTML =data[8]; document.getElementById('Apr').innerHTML =data[9]; document.getElementById('May').innerHTML =data[10]; document.getElementById('Jun').innerHTML =data[11]; document.getElementById('Jul').innerHTML =data[12]; document.getElementById('Aug').innerHTML =data[13]; document.getElementById('Sep').innerHTML =data[14]; document.getElementById('Oct').innerHTML =data[15]; document.getElementById('Nov').innerHTML =data[16]; document.getElementById('Dec').innerHTML =data[17]; document.getElementById('sf').innerHTML =data[18]; document.getElementById('tch').innerHTML =data[19]; document.getElementById('och').innerHTML =data[20]; document.getElementById('Monthly').innerHTML = data[21]; document.getElementById('ppd').innerHTML =data[22]; document.getElementById('pm').innerHTML =data[23]; document.getElementById('bs').innerHTML =data[25]+" Rs."+data[24]; document.getElementById('pa').value =data[24]; document.getElementById('anu').innerHTML =data[26]; document.getElementById('od').innerHTML =data[27]; document.getElementById('pyd').innerHTML =data[28]; document.getElementById('psm').innerHTML =data[30]; document.getElementById('msp').innerHTML =data[31]; document.getElementById("trd").innerHTML = data[29];; document.getElementById("ttd").innerHTML = data[32]; if (data[28]>0){ var spy = "Dues";}else{var spy = "Advance";}; document.getElementById('psy').innerHTML=spy; var tP = 0; $("td.ptp").each(function(i, td){ tP += parseInt($(td).text());}); var tm = 0; $("td.ptm").each(function(i, td){ tm += parseInt($(td).text());}); var pys = data[28]+tP+tm; $('#pttp').html("Had to pay:₹"+pys+"- Paid:₹"+tP+"- Discount:₹"+tm+"=₹"+data[28]+"(Added as PY"+spy+")"); }else{ document.getElementById('loader').innerHTML = "<b style=color:red;>"+data[0]; }} function onFailure(error){ document.getElementById('loader').innerHTML = error; }; window.addEventListener('load', preventFormSubmit); function pfun(form) { var idc = document.getElementById("ac").value; if (idc === "") { document.getElementById('loader').innerHTML = "<b style=color:red;>Fetch detail to proceed."; document.getElementById("box").style.display = "none"; }else{ document.getElementById('loader').innerHTML = 'Proceeding...'; document.getElementById("box").style.display = "none"; google.script.run.withFailureHandler(onFailure) .withSuccessHandler(pfeed) .pPro(form);}} function pfeed(data) { document.getElementById('pf').reset(); document.getElementById('loader').innerHTML =data[0]; document.getElementById('ppd').innerHTML =data[1]; document.getElementById('pm').innerHTML =data[2]; document.getElementById('bs').innerHTML =data[4]+" Rs."+data[3]; document.getElementById('pa').value =data[3]; document.getElementById('trd').innerHTML =data[5];} </script>

</head> <body > <div id='divid'> <table width=100% id=tb> <tr> <td width="230px"> <form onsubmit="SubmitID(this)"> <input type="number"onKeyUp="cp()"id="iptv"Placeholder="ID number"style="width:100px"> <input id="id"name="id"hidden> <input type="submit" value="Get Detail"style="background-color:green;color:white;border:1px dashed white;font-size: 12px; cursor: pointer;"></form> </td> <td id="loader"> </td> </tr> <tr> <td colspan=2> <table width=100%> <tr> <td rowspan=4 width=110px align=center><img id="img" height=110px width=105px> </td> <td id="cls" width=90px align="center"style="background-color:yellow;font-weight:bold;"></td> <td id="sname"class="b">.</td> <td rowspan=3 width=300px> <table width=100%> <tr> <td>Jan:<b id="Jan"></b></td> <td>Feb:<b id="Feb"></b></td> <td>Mar:<b id="Mar"></b></td> <td>Apr:<b id="Apr"></b></td> </tr> <tr> <td>May:<b id="May"></b></td> <td>Jun:<b id="Jun"></b></td> <td>Jul:<b id="Jul"></b></td> <td>Aug:<b id="Aug"></b></td> </tr> <tr> <td>Sep:<b id="Sep"></b></td> <td>Oct:<b id="Oct"></b></td> <td>Nov:<b id="Nov"></b></td> <td>Dec:<b id="Dec"></b></td> </tr> </table> </td> </tr> <tr> <td width=70px>F-Name:</td> <td class=b id="fn" width=200px></td> </tr> <tr><td>Village:</td> <td class=b id="vil"></td></tr> <tr><td colspan=3 align="center"> <table width=100% class=tmn> <tr class=it> <td width=25%>School-Fee:<b id="sf"></b></td> <td width=25%>Trans-Ch.:<b id="tch"></b></td> <td width=25%>OtherCh.:<b id="och"></b></td> <td width=25% style="background-color:yellow;">Monthly:<b id="Monthly"></b></td> </tr> <tr> <td >PY<i id=psy></i> Rs:<b id="pyd"></b></td> <td>Annual-Fee:<b id="anu"></b></td> <td>Other Dues:<b id="od"></b></td> <td>PM <b id="psm"></b>:<b id="msp"></b></td> </tr> <tr> <td colspan=2>Total till now:Paid Rs:<b id="ppd"></b>  & Margin Rs:<b id="pm"></b></td> <td style="background-color:yellow;"><b id="bs"></b></td> <td align=center style="background-color:red;color:white; cursor: pointer;" onclick="pop()">Proceed to Pay</td> </tr> </table> </td></tr> </table> </td> </tr> </table> <div id="box"> <i class="fas fa-check-circle"></i> <form id="pf" onsubmit="pfun(this)"> <table> <tr> <th colspan=2>Pay/Margin to <b id="dih"style="color:green;"></b></th> </tr> <tr> <td width=70px>Pay Rs: <input id="ac"name="ac"hidden> <input id="cl"name="cl"hidden> <input id="sn"name="sn"hidden></td> <td><input name="pa"id="pa"type="number" placeholder="Enter ammount"></td> </tr> <tr> <td width=70px>Discount:</td> <td ><input name="ma"type="number" placeholder="Enter ammount"></td> </tr> <tr> <td width=70px>Note:</td> <td ><textarea name="nt"id="nt"type="text" placeholder="Note"></textarea> </td> </tr> <tr> <td width=70px></td> <td align=center><input type="submit" value="Submit"onsubmit="pfun(this)"style="background-color:green;color:white;border-radius:5px;font-size: 10px;"> <a style="background-color:red;color:white;border:1px solid black;border-radius:5px;font-size: 12px; cursor: pointer;" onclick="pop()">  Cancel  </a></td> </tr> </table> </form> </div> <div id=trd></div> <div id=ttd></div></div> <center><input type="button" name="btnprint" value="Print" onclick="Print('divid')"></center>

This script is written in Google Apps Script (a JavaScript-based scripting language) and is used to manage fees in a Google Spreadsheet. The script opens a spreadsheet by its ID using SpreadsheetApp.openById() method, and accesses the "Diary" sheet using the getSheetByName() method.

It then creates a new date object using var d1 = new Date() and obtains the year, month, date, hours, and minutes from it. The script then uses if-else statements to check for different conditions, such as if it is the last month of the year or if the year and month match a specific value.

If the condition is met, the script performs different actions such as writing monthly or yearly fees to the sheet, updating the date and time of when the fees were added, clearing certain cells, and saving the previous year's transactions to another sheet. The fees are added to the sheet by copying values from specific columns and pasting them into other columns.

automate.gs //function to manage fee function writeFees() { var wsm = SpreadsheetApp.openByID("-------ambC6Y2NdeDE------------"); var ws = wsm.getSheetByName("Diary"); var d1 = new Date(); var tn = d1.toLocaleString([],{ hour: '2-digit', minute: '2-digit',second: '2-digit' }); var yd = d1.getFullYear(); var ly = d1.getFullYear()+1; var md = d1.getMonth()+1; var lm = d1.getMonth()+2; var dd = d1.getDate(); var dh = d1.getHours(); var dm = d1.getMinutes(); if(md == "12"){ var yv = d1.getFullYear()+1;}else{var yv = d1.getFullYear();} var ld = new Date(yv,lm, 0).getDate();//last date of next month. var qt = md+","+dd+","+dh+","+dm; //condition to match var t1 = lm+","+ld+","+dh+","+dm; //value1 var t2 = ly+","+md+","+dd+","+dh+","+dm;//value2 var con1 = ws.getRange(1,28).getValue(); var con2 = ws.getRange(1,27).getValue(); var qt1 = ws.getRange(1,30).getValue(); var qt2 = ws.getRange(1,31).getValue(); //condition for write monthly dues if(con1 === "Yes" && qt >= qt1){ var vTc = ws.getRange(3, 17, ws.getLastRow(), 1).getValues(); ws.getRange(3,ws.getLastColumn()-20,vTc.length, 1).setValues(vTc); var rt1 = d1.toDateString(); ws.getRange(1,23).setValue(rt1+", "+tn); ws.getRange(1,30).setValue(t1); ws.getRange(2,21).setValue("");//clear notes //condition for write yearly dues }if(con1 === "Yes" && yd+","+qt >= qt2){ //saving old records and cleaning for new entry var sw = wsm.getSheetByName("PData"); var tw = wsm.getSheetByName("Ptra"); var swr = sw.getDataRange(); var swv = swr.getValues(); var rl = swv.length; var cl = swv[0].length; var twr = tw.getRange(1,1,rl,cl); twr.setValues(swv);//save previous year transactions sw.getRange(3,1,rl,cl).clearContent();//clear sheet PData ws.getRange(3,26,rl,37).clearContent();// clear months fields for new entry ws.getRange(2,21).setValue("Annual fee added.");//add annual fee var py = d1.getFullYear()-1; var tyd = yd.toString().substr(-2); tw.getRange(1,1).setValue(py+"-"+tyd);//set section var vTcy = ws.getRange(3, 17, ws.getLastRow(), 1).getValues(); ws.getRange(3,ws.getLastColumn()-14,vTcy.length, 1).setValues(vTcy); var rt2 =d1.toDateString(); ws.getRange(1,24).setValue(rt2+", "+tn); ws.getRange(1, 31).setValue(t2); //condition for write fees }if(con2 === "Yes"){ var mc =[4,3,2,13,12,11,10,09,08,07,06,05]; var mn = mc[md-1];//month column as in sheet var vTcf = ws.getRange(3, 12, ws.getLastRow(), 1).getValues(); ws.getRange(3,ws.getLastColumn()-mn,vTcf.length,1).setValues(vTcf); var result =d1.toDateString(); ws.getRange(1,25).setValue(result+", "+tn); var range = ws.getRange(23,39);range.setNumberFormat('@STRING@'); }if(con2 === "No"){ var result ="Function is off."; ws.getRange(1,25).setValue(result); } }

Note: You have to add tringger of 5 minutes to automade your sheet. You have to use same sheet with same column length to work correctly.

LIVE DEMO

---------------------Have a nice day!-------------------

CodyLab

Student fee management system AppsScript

Automations and Fees management in apps script

This page is a part of Student registration form, Both projects work on same sheet. The projects are based on School management system.

Post a Comment

How to create a template for AppsScript project

Online school result using google sheet and apps script

School Management Software || Manage Classes

Create School Management System || Student list

Login through Email - Google web App