How to show the transaction between given date range - JavaScript, AppsScript and html.
To get the transaction history between given date rage, we have used following AppsScript and html code. This AppsScript code read out the matches values from Google sheet data that comes under given criteria(date range).
Let's first see the example sheet data arrangements and references. Example sheet data contains students fee paid details. Each entry has an unique transaction id. Example sheet contains data from 01/04/2021 to 31/03/2022.
Column A : Transaction ID - Entry id that fetches the details of the transaction.
Column B : Transaction Date - Date of entry when the transaction has been created.
Column C : Transaction Time - Time of entry when the transaction has been created.
Column D : Transaction Mode - Mode of transaction means Cash or Online source.
Column E : Customer ID - Id of customer to sum total value or run other function.
Column F : Customer Name - Name of customer to recognize or conform.
Column G : Class - Class of customer to analyse the earning data.
Column H : Paid amount - Amount that particular customer paid.
Column I : Discount amount - Amount that particular customer got as discount.
Column J : Note - A short description if Operator wants to set for further use.
Note:- You should add following columns also for deep report.
Column K :Operator Id - Id of the person who has logged in to create the transaction.
Column L : Remain balance - Total remain value of customer after paid or discount.
Code.gs is following that reads and fetches data from Google spreadsheet. You should read this code carefully to understand its refrences and settiongs.
Code.gs
function doGet() {
var h;
h = HtmlService.createTemplateFromFile('index').evaluate();
h.setTitle("Transaction history example - CodyLab");
return h;
}
function ccForm(form) {
var ws = SpreadsheetApp.openById("1LdPKdEmkWNbn6qgiPItH7qnw9EZA6N1iuV0xqg4ckWc").getSheetByName("pData");
var tr = ws.getDataRange().getValues();
var sv1 = form.s1;
var v1s = sv1.split("/");
var sv2 = form.s2;
var v2s = sv2.split("/");
var dp1 = v1s[1]+"/"+v1s[0]+"/"+v1s[2];
var dp2 = v2s[1]+"/"+v2s[0]+"/"+v2s[2];
var d1 = v1s[2]+v1s[0]+v1s[1];
var d2 = v2s[2]+v2s[0]+v2s[1];
var wr ="<table border=1 class=tabl id=tid width=100%><tbody><tr><th width=50px>Tran.Id</th><th width=70px>Date</th><th width=60px>Mode</th><th width=50px>Enroll:</th><th width=40px>Class:</th><th width=150px>Name:</th><th width=50px>Paid:</th><th width=50px>Margin:</th><th width=350px>Note:</th></tr>";
for(var i=2; i<tr.length ; i++){
var dt =tr[i][1];
var dv = dt.toLocaleDateString();
var vd = dv.split("/");
var sv = vd[0]+","+vd[1]+","+vd[2];
var dm = sv.split(",");
var dp = dm[0]+"/"+dm[1]+"/"+dm[2];
var d3 = dm[2]+dm[1]+dm[0];
if(d3 >= d1 && d3 <= d2){
wr +='<tr><td><b>'+tr[i][0]+'</b></td><td>'+dp+'</td><td>'+tr[i][3]+'</td><td>'+tr[i][4]+'</td><td align=center>'+tr[i][6]+'</td><td>'+tr[i][5]+'</td><td align=center class=tdp>'+tr[i][7]+'</td><td align=center class=tdm>'+tr[i][8]+'</td><td align=center>'+tr[i][9]+'</td></tr>';}
var trv = wr;
if(wr === "<table border=1 class=tabl id=tid width=100%><tbody><tr><th width=50px>Tran.Id</th><th width=70px>Date</th><th width=60px>Mode</th><th width=50px>Enroll:</th><th width=40px>Class:</th><th width=150px>Name:</th><th width=50px>Paid:</th><th width=50px>Margin:</th><th width=350px>Note:</th></tr>"){
var trv = ["<table id=tid><tr><td><b style=color:red>No transaction found in given date range.</b></td></tr></table>"];}};
return trv;
};
Now add html in your project. make copy of following html code and paste it into index.html file and save.
index.html
<!DOCTYPE html>
<html>
<head><script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
<link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
<script>
$( function(){ $( "#datepicker" ).datepicker();$( "#datepicker1" ).datepicker();} );
</script>
<base target="_top">
<style>
.scroll{overflow: auto;height:455px;}
select,option{font-family:times;font-size:12px;width:20%;}
.tabl{ border: solid 1px #cece;border-collapse: collapse; font-family:times;font-size:12px;}
td{text-transform:capitalize;border-top:1px solid transparent;}
input{
width: 92px;
height: 11px;
font-family: times;font-size:12px;
}
button{
font-family: times;font-size:11px;
}
tr:nth-child(even) {
background: #cece;
}
table{font-family:times;font-size:12px;}
</style>
</head>
<script>
function cp() {
document.getElementById('nbtn').click();
return nmpwb();}
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 nmpwb(form) {
var nel1 = document.getElementById('datepicker1').value;
if(nel1 === ""){
document.getElementById('linfo').innerHTML = "";
}else{
document.getElementById('linfo').innerHTML = "Working on...";
google.script.run
.withSuccessHandler(nmpwms)
.ccForm(form);}}
function nmpwms(rdata) {
document.getElementById('linfo').innerHTML =rdata;
var tP = 0;
$("td.tdp").each(function(i, td) {
tP += parseInt($(td).text());});
var tm = 0;
$("td.tdm").each(function(i, td) {
tm += parseInt($(td).text());});
count = ($('table#tid tr:last').index());
$('#ct').html(" Total trasaction:"+count+", Earnning:₹"+tP+" and Discount:₹"+tm+".");
};
</script>
<script language="javascript">
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-collapse: collapse;font-size:12px;text-transform:capitalize;}');
docprint.document.write('input{display:none;}');
docprint.document.write('table{ page-break-inside:auto }tr{ page-break-inside:avoid; page-break-after:auto}thead{ display:table-header-group }tfoot { display:table-footer-group }table{border-collapse: collapse;} #tt{margin-bottom:0.2em;margin-top:0.2em;}</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>
<body>
<form onsubmit="nmpwb(this)"id="anform"align="center">
<table width=100%><tr><td>Trasaction from:<input type="text"name="s1" id="datepicker">
To:<input type="text" name="s2"id="datepicker1"onchange="cp(this)" ></td><td id="ct"></td>
<td><button name="btnprint" onclick="Print('divid')">Print</button></td></tr></table>
<input type=submit onclick="nmpwb(this)"id="nbtn"hidden>
</form>
<div id='divid'>
<div id="linfo"class="scroll"></div></div>
</body>
</html>
>>>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! -------------------------- -------------------------
Donate me through - PayPal or RozorPay or Paytm