How to create a banking model webapp using apps script and google sheet?

Creating a small banking model webapp in apps script

Dear user, We are excited to introduce our new app, built using Google's App Script. App Script is a powerful tool that allows us to create and manage web applications without the need for complicated coding. In this article we are going to show a banking model webapp that able to create holders and manage holders balance without making any mistake. And also able to deposit/withdraw amount to the holders account. But keep attention this is only a demo. This is not a real account. If you are trying to create webapp like given example, let us follow the way to create;

First create a spreadsheet of three sheets. Name these sheets as holders, deposit and whithdraw. See the following spreadsheet for Example;

Spreadsheet

Code.gs is a file in Google App Script that contains the main script for your web application. It serves as the backbone of your app and is responsible for the overall functionality and behavior of your app. In other words, it is where you write the code that defines how your app works.

Code.gs

function doGet(e){

return HtmlService.createTemplateFromFile("index")

.evaluate()

.setTitle("CodyLab mini bank");

}

function include(filename) {

return HtmlService.createHtmlOutputFromFile(filename).getContent();

}

var wss = SpreadsheetApp.openById(" sheet id")

var sn = wss.getSheetByName("holders");

var z = (num, places) => String(num).padStart(places, '0');

function tget(){

var tv = sn.getDataRange().getValues();

var val ='<table class="w3-table-all w3-small"id="tabl"><tr class="w3-red"><th>Joining Date</th><th>HolderID</th><th>Holder Name</th><th>Contact No.</th><th>Total Deposit</th><th>Withdraw</th><th>Charges</th><th>Balance</th><th>Status</th></tr>';

for(var i=1;i<tv.length;i++){

val+='<tr><td>'+tv[i][0]+'</td><td>'+tv[i][1]+'</td><td>'+tv[i][2]+'</td><td>'+tv[i][9]+'</td><td>'+tv[i][3]+'</td><td>'+tv[i][4]+'</td><td>'+tv[i][6]+'</td><td>'+tv[i][7]+'</td><td>'+tv[i][8]+'</td></tr>';

}

return val+'</table>';

}

function addholder(v){

var l = sn.getLastRow()+1;

var flag = 1 ;

for(var i = 1;i <= l;i++){

if(sn.getRange(i, 2).getValue() == v.hid){// checking if condidate already exist.

flag = 0;

var data = "Condidate already exist";

return data;

}

if(flag==1){

var dt = Utilities.formatDate(new Date(),Intl.DateTimeFormat().resolvedOptions().timeZone, 'yyyy-MM-dd HH:mm:ss');

var f1 = '=SUMIF(deposit!D:D,B'+l+',deposit!E:E)';

var f2 = '=SUMIF(withdraw!D:D,B'+l+',withdraw!E:E)';

var f3 = '=MINUS(D'+l+',E'+l+')';

var f4 = '=SUMIF(withdraw!D:D,B'+l+',withdraw!F:F)';

var f5 = '=MINUS(F'+l+',G'+l+')';

var f6 = '=IFs(H'+l+'>0,"To give",H'+l+'<0,"To take",H'+l+'=0,"final")';

sn.appendRow([dt,v.hid,v.fullname,f1,f2,f3,f4,f5,f6,v.mob,v.email]);

sn.getRange(l,1).setNumberFormat('@STRING@');

var data = 'Holder successfully added.';

return data;

}

};

function deposit(v){

var dn = wss.getSheetByName("deposit");

var l = dn.getLastRow();

var id = "D"+z(l,5);

if(v.hid!=""){

var dt = Utilities.formatDate(new Date(),Intl.DateTimeFormat().resolvedOptions().timeZone, 'yyyy-MM-dd HH:mm:ss');

var d = dt.split(" ");

dn.appendRow([id,d[0],d[1],v.hid,v.damount,v.note]);

var data = 'Deposit successfull.';

return data;

}else{

var data = 'Something went wrong!';

return data;

}}

function withdraw(v){

var dn = wss.getSheetByName("withdraw");

var z = (num, places) => String(num).padStart(places, '0');

var l = dn.getLastRow();

var id = "W"+z(l,5);

if(v.hid!=""){

var dt = Utilities.formatDate(new Date(),Intl.DateTimeFormat().resolvedOptions().timeZone, 'yyyy-MM-dd HH:mm:ss');

var d = dt.split(" ");

dn.appendRow([id,d[0],d[1],v.hid,v.wamount,v.camount,v.note]);

var data = 'Withdraw successfully.';

return data;

}else{

var data = 'Something went wrong!';

return data;

}}

The index.html file is responsible for creating the layout and structure of your app. It defines the overall design, including the location and size of elements such as text, images, and buttons. It also includes links to other files such as CSS and JavaScript that are used to style and add functionality to your app.

index.html

<!DOCTYPE html>

<html>

<head>

<?!= include('js'); ?>

<?!= include('css'); ?>

</head>

<?!= include('deposit'); ?>

<?!= include('withdraw'); ?>

<?!= include('addholder'); ?>

<i class="fa fa-user-plus w3-padding w3-teal w3-hover-gray"onclick="adh(this);"> ADD HOLDERS</i>

<i class="fa fa-plus w3-padding w3-green w3-hover-gray"onclick="dep(this);"> DEPOSIT</i>

<i class="fa fa-minus w3-padding w3-red w3-hover-gray"onclick="wit()(this);"> WITHDRAW</i>

</div>

</div>

</body>

</html>

adtxt

The js.html is a file that contains JavaScript code that is used to add interactivity and dynamic behavior to a web application. JavaScript is a programming language that is commonly used to create interactive, responsive, and dynamic web pages.

In an App Script web application, the js.html file is typically linked to from the main HTML file (index.html) using a script tag. The JavaScript code in the js.html file can then be used to manipulate the Document Object Model (DOM) of the web page, allowing you to change the layout and content of the page dynamically based on user input or other events.

For example, you can use JavaScript to create event listeners that respond to user actions such as clicking a button or hovering over an element. You can also use JavaScript to make requests to external web services, retrieve data, and update the web page dynamically without requiring a page refresh.

js.html

function addo(v){

if(document.getElementById('hida').value==""

||document.getElementById('fullname').value==""){

document.getElementById('info').innerHTML ="Something is wrong!";

myin();

}else{

document.getElementById('info').innerHTML ="Submitting...";

document.getElementById('pp1').style.display='none';

document.getElementById('info').className = "show";

google.script.run.withFailureHandler(failure)

.withSuccessHandler(inf1)

.addholder(v);}};

function wito(v){

document.getElementById('info').innerHTML ="Making withdraw...";

document.getElementById('pp2').style.display='none';

document.getElementById('info').className = "show";

google.script.run.withFailureHandler(failure)

.withSuccessHandler(inf1)

.withdraw(v);}

function depo(v){

document.getElementById('info').innerHTML ="Making deposit...";

document.getElementById('pp3').style.display='none';

document.getElementById('info').className = "show";

google.script.run.withFailureHandler(failure)

.withSuccessHandler(inf1)

.deposit(v);}

adtxt

function inf1(x){

document.getElementById('info').innerHTML = x;

document.getElementById('lab').reset();

tg();myin();}

function failure(x){

document.getElementById('info').innerHTML = x;

document.getElementById('lab').reset();

tg();myin();}

function myin(){

var x = document.getElementById("info");

x.className = "show";

setTimeout(function(){

x.className = x.className.replace("show", "");},2000);

}

</script>

adtxt

function tg(){

document.getElementById('irefresh').classList.add('fa-spin');

google.script.run.withSuccessHandler(showData).tget();};

function showData(data){

document.getElementById('itable').innerHTML =data;

document.getElementById('irefresh').classList.remove('fa-spin');

var table = document.getElementById("tabl");

var pids = '<option value=""hidden>SELECT HOLDER ID</option>';

for (var i = 1; i < table.rows.length; i++) {

pids += "<option>" +table.rows[i].cells[1].innerText+ "</option>";}

document.getElementById("sel1").innerHTML = pids;

document.getElementById("sel2").innerHTML = pids;

}

</script>

function searchTable(){

var input, filter, found, table, tr, td, i, j;

input = document.getElementById("sinput");

filter = input.value.toUpperCase();

table = document.getElementById("tabl");

tr = table.getElementsByTagName("tr");

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

td = tr[i].getElementsByTagName("td");

for (j = 0; j < td.length; j++) {

if (td[j].innerHTML.toUpperCase().indexOf(filter) > -1) {

found = true;

}

if (found) {

tr[i].style.display = "";

found = false;

} else {

tr[i].style.display = "none";

}

</script>

function dep(){

document.getElementById('pp3').style.display='block';

}

function wit(){

document.getElementById('pp2').style.display='block';

}

function adh(){

document.getElementById('pp1').style.display='block';

}

function lowerCase(elem){

var temp = elem.value;

elem.value = temp.toLowerCase();};

</script>

adtxt

The css.html file is an essential part of any App Script web application, and it plays a crucial role in determining how your app looks and feels. It allows you to create a visually appealing and consistent design for your web pages. It also helps to separate the presentation of the web page from its structure and content, making it easier to maintain and update your web application.

css.html

<style>

input[type="search"]{border:1px solid green;outline:none;}

tr{text-transform:capitalize;}

html,body,h1,h2,h3,h4,h5{font-family:times;}

.w3-bar-block .w3-bar-item {padding: 12px;}

input[type=text], select {

border: 1px solid #ccc;

border-radius: 4px;text-transform:uppercase;

box-sizing: border-box;}

.w3-modal-content{

border-radius: 5px;width:400px;

background-color: #f2f2f2;

padding: 20px;}

#info{

visibility: hidden;

min-width: 250px;

margin-left: -125px;

background-color: gray;

color: #fff;

text-align: center;

border-radius: 2px;

padding: 10px;

position: fixed;

z-index: 1;

left: 40%;

top: 20%;

font-size: 17px;

}

#info.show {

visibility: visible;

-webkit-animation: fadein 0.5s, fadeout 0.5s 2.5s;

animation: fadein 0.5s, fadeout 0.5s 2.5s;

}

.icon {

width: 22px; height: 22px;

border-radius: 50%;

transform: rotate(45deg);

}

.icon::before, .icon::after { position: absolute; content: ''; background-color: #fff; }

.icon.icon-success { background: green; }

.icon.icon-success:before { width: 3px; height: 9px; top: 6px; left: 11px; }

.icon.icon-success:after { width: 3px; height: 3px; top: 12px; left: 8px; }

.icon.icon-failure { background: lightcoral; }

.icon.icon-failure::before { width: 3px; height: 12px; top: 5px; left: 10px; }

.icon.icon-failure::after { width: 12px; height: 3px; top: 10px; left: 5px; }</style>

</style>

This is an HTML file that displays a modal form for adding a "Holder Detail". The form has fields for entering a "Holder ID", "Holder name", "Mobile Number", and "Email ID". The form is submitted using a "Submit" button and can be closed using a "Close" button. The form uses the W3.CSS framework for styling and layout.

addholder.html

<legend>Holder Detail:</legend>

<lavel>Holder ID:*</lavel>

</div>

<lavel>Holder name:*</lavel>

</div>

<lavel>Mobile Number:</lavel>

</div>

<lavel>Email ID:</lavel>

</div>

</form>

Close</button>

</div>

</fieldset>

</div>

dec

withdraw.html

<option>SELECT HOLDER ID:*</option></select>

</div>

<lavel>Enter Withdraw Amount:*</lavel>

</div>

<lavel>Charge Amount:</lavel>

</div>

</div>

</form>

Close</button>

</div>

This is an HTML file that displays a modal form for withdrawing a specified amount from a selected "Holder ID". The form has fields for selecting a "Holder ID" from a dropdown list, entering a "Withdraw Amount" and "Charge Amount", and a "Note" field. The form is submitted using a "Withdraw" button and can be closed using a "Close" button. The form uses the W3.CSS framework for styling and layout.

deposit.html

<option>SELECT HOLDER ID:*</option></select>

</div>

<lavel>Enter Deposit Amount:*</lavel>

</div>

</div>

</form>

Close</button>

</div>

LIVE DEMO

>>>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

Have a nice day!

-------------------------- -------------------------

CodyLab

How to create a banking model webapp using apps script and google sheet?

Creating a small banking model webapp in apps script

Post a Comment

School Management Software || Manage Classes

Create School Management System || Student list

Login system - google Apps script - CodyLab

How to create a template for AppsScript project

Create School Management System || Admission form code.gs