I am a beginner to appscript. I am developing a payment system where user can see their payment history and can pay their payment. But for now after I have made some changes to my code, after that the content and headings wont get aligned together, so Im now seeking for some help from you guys. I have included the link to my appscript and some images to explain my self better. Thank you so much
Code.gs
var url = "https://docs.google.com/spreadsheets/d/1bM8l6JefFsPrlJnTWf56wOhnuSjdIwg3hMbY1tN1Zp8/edit#gid=1775459006"; var streetSheetName = "JALAN SANGGUL 4"; function doGet(e) { var streetSheetName = "JALAN SANGGUL 4"; // Added PropertiesService.getScriptProperties().setProperty("streetSheetName", streetSheetName); // Added return HtmlService.createHtmlOutputFromFile('WebAppLogin') .setTitle("Resident Payment"); } function checkLogin(username, password) { var found_record = ''; var name = ''; var ss = SpreadsheetApp.openByUrl(url); var webAppSheet = ss.getSheetByName("USERNAMES"); var getLastRow = webAppSheet.getLastRow(); for(var i = 2; i <= getLastRow; i++) { if(webAppSheet.getRange(i, 1).getValue().toUpperCase() == username.toUpperCase() && webAppSheet.getRange(i, 7).getValue() == password) { found_record = 'TRUE'; name = webAppSheet.getRange(i, 4).getValue().toUpperCase() + " " + webAppSheet.getRange(i, 5).getValue().toUpperCase(); streetSheetName = webAppSheet.getRange(i, 3).getValue().toUpperCase(); } else if (username.toUpperCase() == 'ADMIN' && password == 'ADMINPASSWORD') { found_record = 'TRUE'; name = webAppSheet.getRange(i, 4).getValue().toUpperCase() + " " + webAppSheet.getRange(i, 5).getValue().toUpperCase(); streetSheetName = webAppSheet.getRange(i, 3).getValue().toUpperCase(); } } PropertiesService.getScriptProperties().setProperty("streetSheetName", streetSheetName); // Added if(found_record == '') { found_record = 'FALSE'; } return [found_record, username,name]; } function GetRecords(username,filter) { var filteredDataRangeValues = GetUsernameAssociatedProperties(username); var resultArray = GetPaymentRecords(filteredDataRangeValues,filter); var resultFilter = getYears(); result = { data: resultArray, filter: resultFilter }; return result; } function getYears() { var ss= SpreadsheetApp.openByUrl(url); var yearSheet = ss.getSheetByName("Configuration"); var getLastRow = yearSheet.getLastRow(); var return_array = []; for(var i = 2; i <= getLastRow; i++) { if(return_array.indexOf(yearSheet.getRange(i, 2).getDisplayValue()) === -1) { return_array.push(yearSheet.getRange(i, 2).getDisplayValue()); } } return return_array; } function changePassword(username, newPassword) { var sheet = SpreadsheetApp.openByUrl(url).getSheetByName("USERNAMES"); var range = sheet.getRange("A2:A").createTextFinder(username).matchEntireCell(true).findNext(); if (range) { range.offset(0, 6).setValue(newPassword); } } function GetUsernameAssociatedProperties(username) { var filteredDataRangeValues = ''; var ss = SpreadsheetApp.openByUrl(url); var displaySheet = ss.getSheetByName("USERNAMES"); var dataRangeValues = displaySheet.getDataRange().getValues(); if (username.toUpperCase() == 'ADMIN') { dataRangeValues.shift(); filteredDataRangeValues = dataRangeValues; } else { filteredDataRangeValues = dataRangeValues.filter(row => row[0].toUpperCase() == username.toUpperCase()); } return filteredDataRangeValues; } function GetPaymentRecords(userProperties,filter) { var streetSheetName = PropertiesService.getScriptProperties().getProperty("streetSheetName"); // Added var transpose = m => m[0].map((_, i) => m.map(x => x[i])); var resultArray = []; var ss = SpreadsheetApp.openByUrl(url); var displaySheet = ss.getSheetByName(streetSheetName); var addressValues = displaySheet.getRange("B:C").getValues(); var paidMonthValues = displaySheet.getRange(1, 7, displaySheet.getLastRow(), displaySheet.getLastColumn() - 6).getValues(); //Logger.log(addressValues); //Logger.log(transpose(paidMonthValues)); userProperties.forEach((v, i) => { var userHouseNumber = v[1]; var userStreet = v[2]; var column = addressValues.reduce(function callbackFn(accumulator, currentValue, index, array) { if (currentValue[0] == userHouseNumber && currentValue[1] == userStreet) { return index } else { return accumulator } }, ''); //Logger.log(column); Logger.log(filter) Logger.log(paidMonthValues); if(filter=="None"){ var result = transpose(paidMonthValues).map(function callbackFn(element, index, array) { return [element[0], userHouseNumber, userStreet, element[column] || ''] }); }else{ var result = transpose(paidMonthValues).map(function callbackFn(element, index, array) { if(element[0].includes(filter))return [element[0], userHouseNumber, userStreet, element[column] || ''] }); } resultArray = resultArray.concat(result); //Logger.log(resultArray); }) //Remove null elements resultArray = resultArray.filter(element=>{ Logger.log(element!=null) return element != null; }); return resultArray; }
WebAppLogin.html
<!DOCTYPE html> <html> <head> <base target="_top"> <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" integrity="sha384-JcKb8q3iqJ61gNV9KGb8thSsNjpSL0n8PARn9HuZOnIxN0hoP+VmmDGMN5t9UJ0Z" crossorigin="anonymous"> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script> <script> var username = ""; // Added function GetRecords() { var spin = "<span class="spinner-border spinner-border-sm" role="status" aria-hidden="true"></span>"; spin += " Loading..."; document.getElementById("LoginButton").innerHTML = spin; username = document.getElementById("username").value; // Modified var password = document.getElementById("password").value; var password = document.getElementById("password").value; google.script.run.withSuccessHandler(function(output) { console.log(output); var username = output[1]; var name = output[2]; if(output[0] == 'TRUE') { document.getElementById("loginDisplay").style.display = "none"; document.getElementById("dataDisplay").style.display = "block"; document.getElementById("errorMessage").innerHTML = ""; document.getElementById("currentUser").value = name; // CHANGE google.script.run.withSuccessHandler(displayTable).GetRecords(username,"None"); } else if(output[0] == 'FALSE') { document.getElementById("firstLastName").innerHTML = ""; document.getElementById("currentUser").value = ""; document.getElementById("myFilter").innerHTML = ""; document.getElementById("errorMessage").innerHTML = "Failed to Login"; document.getElementById("LoginButton").innerHTML = "Login"; } }).checkLogin(username, password); } function filter(){ var filterStr = document.getElementById("filterYear").value; google.script.run.withSuccessHandler(displayTable).GetRecords(username, filterStr); } function displayTable(result) { var ar = result.data; var filterString = result.filter; ar = ar.sort((a, b) => new Date(a).getTime() > new Date(b).getTime() ? -1 : 1).splice(-12); // <--- Added var name = document.getElementById("currentUser").value; // CHANGE if(ar.length > 0) { var displayTable = '<table class="table" id="mainTable">'; displayTable += "<tr>"; displayTable += "<th>Month</th>"; displayTable += "<th>House Number</th>"; displayTable += "<th>Street</th>"; displayTable += "<th>Payment Status</th>"; displayTable += "</tr>"; ar.forEach(function(item, index) { displayTable += "<tr>"; displayTable += "<td>"+item[0]+"</td>"; displayTable += "<td>"+item[1]+"</td>"; displayTable += "<td>"+item[2]+"</td>"; displayTable += "<td>"+item[3]+"</td>"; displayTable += "</tr>"; }); displayTable += "</table>"; } else { var displayTable = "<span style="font-weight: bold" >No Records Found</span>"; } var filter = ''; if(filterString.length > 0) { filter += '<label for="years" style="font-size: 20px">Select the Year</label><br><select class="form-control form-control-sm" id="filterYear" name="years" required><option value="" selected>Choose...</option>'; filterString.filter(String).forEach(str => { filter += '<option value="'+str+'">'+str+'</option>'; }); filter += '</select><button class="btn btn-primary" type="button" id="FilterButton" onclick="filter()" >Submit</button>'; } var today = new Date(); var year = today.getFullYear(); var month = today.getMonth(); if (!ar.some(([a,,,d]) => { var t = new Date(a); return year == t.getFullYear() && month == t.getMonth() && d.toUpperCase() == "PAID"; })) { document.getElementById("digitalgoods-030521182921-1").style.display = "block"; } document.getElementById("displayRecords").innerHTML = displayTable; document.getElementById("firstLastName").innerHTML = "USER: " + name; document.getElementById("myFilter").innerHTML = filter; document.getElementById("LoginButton").innerHTML = "Login"; document.getElementById("username").value = ''; document.getElementById("password").value = ''; } //change the link according to ur webapp latest version function LogOut(){ window.open("https://script.google.com/macros/s/AKfycbwKa4sQ441WUIqmU40laBP0mfiqNMiN-NghEvwUnJY/dev",'_top'); } function changePassword(){ var result = confirm("Want to Change Password?"); if (result) { var newPassword = document.getElementById("newPassword").value; google.script.run.withSuccessHandler(() => alert('Password changed')).changePassword(username, newPassword); } } </script> </head> <body> <style> h2 {text-align: center;} div {text-align: center;} .col-centered{margin: 0 auto;float: none;} </style> <h2> Resident Payment Status Portal</h2> </div> <div id="loginDisplay" style="padding: 10px"> <div class="form-row"> <div class="form-group col-md-3" style="margin:0 auto"> <label>User Name</label> <input type="text" id="username" class="form-control" required/> </div> </div> <hr> <div class="form-row"> <div class="form-group col-md-3" style="margin:0 auto"> <label>Password</label><br> <input type="password" id="password" class="form-control" required/> </div> </div> <hr> <button class="btn btn-primary" type="button" id="LoginButton" onclick="GetRecords()" > Login </button> <span id="errorMessage" style="color: red" ></span> </div> <hr> <div style="display:none" id="dataDisplay"> <div> <h2 id="firstLastName"></h2> </div> <input type="hidden" id="currentUser" value=""/> <div style="width:1200px;margin:0 auto;"> <div id ="myFilter" class="form-group"></div> <hr> <div id="displayRecords" style="padding: 10px;" ></div> <!----Paypal Button--------> <hr> <div style="width:500px;margin:0 auto;"> <div id="digitalgoods-030521182921-1" style="display: none;"></div> <script>(function (div, currency) {var item_total = {currency_code: currency,value: '50.00',},tax_total = {currency_code: currency,value: '0.00' },render = function () {window.paypal.Buttons({createOrder: function (data, actions) {return actions.order.create({application_context: {brand_name: "",landing_page: "BILLING",shipping_preference: "NO_SHIPPING",payment_method: {payee_preferred: "UNRESTRICTED"}},purchase_units: [{description: "",soft_descriptor: "digitalgoods",amount: {breakdown: {item_total: item_total,tax_total: tax_total},value: '50.00' },items: [{name: "Monthly Fees",quantity: 1,description: "",sku: "1",unit_amount: item_total,tax: tax_total}]}]});},onApprove: function (data, actions) {return actions.order.capture().then(function (details) {div.innerHTML = "Order completed. Youx27ll receive an email shortly!";});},onCancel: function (data) {},onError: function (err) {div.innerHTML = "<pre>" + err.toString()}}).render("#digitalgoods-030521182921-1");},init = function () {window.digitalgoods = window.digitalgoods || [];window.digitalgoods.push(render);var file = "https://www.paypal.com/sdk/js?client-id=AS-86gVX_DfakSkq6YZDJRdKZb4SMIziOd5c9DIKy4extQrpb0VFEprDleB_duKI4BJQQRewUdfliZEfx26currency=MYR";var script = document.createElement("script");script.type = "text/javascript";script.src = file;script.onload = function() {var i = window.digitalgoods.length;while (i--) {window.digitalgoods[i]();}};div.appendChild(script);};init();})(document.getElementById("digitalgoods-030521182921-1"), "MYR");</script> </div> <!-----Change Password-----------> <div> <!--<button type="button" class="btn btn-primary btn-md" onclick="changePassword()">Change Password</button>--> <!-- Button trigger modal --> <button type="button" class="btn btn-primary" data-toggle="modal" data-target="#exampleModalCenter"> Change Password </button> <!-- Modal --> <div class="modal fade" id="exampleModalCenter" tabindex="-1" role="dialog" aria-labelledby="exampleModalCenterTitle" aria-hidden="true"> <div class="modal-dialog modal-dialog-centered" role="document"> <div class="modal-content"> <div class="modal-header"> <h3 class="modal-title" id="exampleModalLongTitle">Change Password</h3> <button type="button" class="close" data-dismiss="modal" aria-label="Close"> <span aria-hidden="true">×</span> </button> </div> <div class="modal-body"> <div class="form-group"> <label>Enter New Password</label><br> <input type="password" id="newPassword" class="form-control" required/> </div> </div> <div class="modal-footer"> <button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button> <button type="button" class="btn btn-primary" onclick="changePassword()">Save changes</button> </div> </div> </div> </div> </div> <hr> <!-----Log Out-----------> <div> <button type="button" class="btn btn-default btn-md" onclick="LogOut()"> <span class="glyphicon glyphicon-log-out"></span> Log out </button> </div> </div> </body> </html>
Advertisement
Answer
Can you put this code in WebAppLogin.html
<!DOCTYPE html> <html> <head> </style> <base target="_top"> <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" integrity="sha384-JcKb8q3iqJ61gNV9KGb8thSsNjpSL0n8PARn9HuZOnIxN0hoP+VmmDGMN5t9UJ0Z" crossorigin="anonymous"> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script> <script> var username = ""; // Added function GetRecords() { var spin = "<span class="spinner-border spinner-border-sm" role="status" aria-hidden="true"></span>"; spin += " Loading..."; document.getElementById("LoginButton").innerHTML = spin; username = document.getElementById("username").value; // Modified var password = document.getElementById("password").value; var password = document.getElementById("password").value; google.script.run.withSuccessHandler(function(output) { console.log(output); var username = output[1]; var name = output[2]; if(output[0] == 'TRUE') { document.getElementById("loginDisplay").style.display = "none"; document.getElementById("dataDisplay").style.display = "block"; document.getElementById("errorMessage").innerHTML = ""; document.getElementById("currentUser").value = name; // CHANGE google.script.run.withSuccessHandler(displayTable).GetRecords(username,"None"); } else if(output[0] == 'FALSE') { document.getElementById("firstLastName").innerHTML = ""; document.getElementById("currentUser").value = ""; document.getElementById("myFilter").innerHTML = ""; document.getElementById("errorMessage").innerHTML = "Failed to Login"; document.getElementById("LoginButton").innerHTML = "Login"; } }).checkLogin(username, password); } function filter(){ var filterStr = document.getElementById("filterYear").value; google.script.run.withSuccessHandler(displayTable).GetRecords(username, filterStr); } function displayTable(result) { var ar = result.data; var filterString = result.filter; ar = ar.sort((a, b) => new Date(a).getTime() > new Date(b).getTime() ? -1 : 1).splice(-12); // <--- Added var name = document.getElementById("currentUser").value; // CHANGE if(ar.length > 0) { var displayTable = '<table class="table" id="mainTable">'; displayTable += "<tr>"; displayTable += '<th style="text-align: center;">Month</th>'; displayTable += '<th style="text-align: center;">House Number</th>'; displayTable += '<th style="text-align: center;">Street</th>'; displayTable += '<th style="text-align: center;">Payment Status</th>'; displayTable += "</tr>"; ar.forEach(function(item, index) { displayTable += "<tr>"; displayTable += "<td>"+item[0]+"</td>"; displayTable += "<td>"+item[1]+"</td>"; displayTable += "<td>"+item[2]+"</td>"; displayTable += "<td>"+item[3]+"</td>"; displayTable += "</tr>"; }); displayTable += "</table>"; } else { var displayTable = "<span style="font-weight: bold" >No Records Found</span>"; } var filter = ''; if(filterString.length > 0) { filter += '<label for="years" style="font-size: 20px">Select the Year</label><br><select class="form-control form-control-sm" id="filterYear" name="years" required><option value="" selected>Choose...</option>'; filterString.filter(String).forEach(str => { filter += '<option value="'+str+'">'+str+'</option>'; }); filter += '</select><button class="btn btn-primary" type="button" id="FilterButton" onclick="filter()" >Submit</button>'; } var today = new Date(); var year = today.getFullYear(); var month = today.getMonth(); if (!ar.some(([a,,,d]) => { var t = new Date(a); return year == t.getFullYear() && month == t.getMonth() && d.toUpperCase() == "PAID"; })) { document.getElementById("digitalgoods-030521182921-1").style.display = "block"; } document.getElementById("displayRecords").innerHTML = displayTable; document.getElementById("firstLastName").innerHTML = "USER: " + name; document.getElementById("myFilter").innerHTML = filter; document.getElementById("LoginButton").innerHTML = "Login"; document.getElementById("username").value = ''; document.getElementById("password").value = ''; } //change the link according to ur webapp latest version function LogOut(){ window.open("https://script.google.com/macros/s/AKfycbwKa4sQ441WUIqmU40laBP0mfiqNMiN-NghEvwUnJY/dev",'_top'); } function changePassword(){ var result = confirm("Want to Change Password?"); if (result) { var newPassword = document.getElementById("newPassword").value; google.script.run.withSuccessHandler(() => alert('Password changed')).changePassword(username, newPassword); } } </script> </head> <body> <style> h2 {text-align: center;} div {text-align: center;} .col-centered{margin: 0 auto;float: none;} </style> <h2> Resident Payment Status Portal</h2> </div> <div id="loginDisplay" style="padding: 10px"> <div class="form-row"> <div class="form-group col-md-3" style="margin:0 auto"> <label>User Name</label> <input type="text" id="username" class="form-control" required/> </div> </div> <hr> <div class="form-row"> <div class="form-group col-md-3" style="margin:0 auto"> <label>Password</label><br> <input type="password" id="password" class="form-control" required/> </div> </div> <hr> <button class="btn btn-primary" type="button" id="LoginButton" onclick="GetRecords()" > Login </button> <span id="errorMessage" style="color: red" ></span> </div> <hr> <div style="display:none" id="dataDisplay"> <div> <h2 id="firstLastName"></h2> </div> <input type="hidden" id="currentUser" value=""/> <div style="width:1200px;margin:0 auto;"> <div id ="myFilter" class="form-group"></div> <hr> <div id="displayRecords" style="padding: 10px;" ></div> <!----Paypal Button--------> <hr> <div style="width:500px;margin:0 auto;"> <div id="digitalgoods-030521182921-1" style="display: none;"></div> <script>(function (div, currency) {var item_total = {currency_code: currency,value: '50.00',},tax_total = {currency_code: currency,value: '0.00' },render = function () {window.paypal.Buttons({createOrder: function (data, actions) {return actions.order.create({application_context: {brand_name: "",landing_page: "BILLING",shipping_preference: "NO_SHIPPING",payment_method: {payee_preferred: "UNRESTRICTED"}},purchase_units: [{description: "",soft_descriptor: "digitalgoods",amount: {breakdown: {item_total: item_total,tax_total: tax_total},value: '50.00' },items: [{name: "Monthly Fees",quantity: 1,description: "",sku: "1",unit_amount: item_total,tax: tax_total}]}]});},onApprove: function (data, actions) {return actions.order.capture().then(function (details) {div.innerHTML = "Order completed. Youx27ll receive an email shortly!";});},onCancel: function (data) {},onError: function (err) {div.innerHTML = "<pre>" + err.toString()}}).render("#digitalgoods-030521182921-1");},init = function () {window.digitalgoods = window.digitalgoods || [];window.digitalgoods.push(render);var file = "https://www.paypal.com/sdk/js?client-id=AS-86gVX_DfakSkq6YZDJRdKZb4SMIziOd5c9DIKy4extQrpb0VFEprDleB_duKI4BJQQRewUdfliZEfx26currency=MYR";var script = document.createElement("script");script.type = "text/javascript";script.src = file;script.onload = function() {var i = window.digitalgoods.length;while (i--) {window.digitalgoods[i]();}};div.appendChild(script);};init();})(document.getElementById("digitalgoods-030521182921-1"), "MYR");</script> </div> <!-----Change Password-----------> <div> <!--<button type="button" class="btn btn-primary btn-md" onclick="changePassword()">Change Password</button>--> <!-- Button trigger modal --> <button type="button" class="btn btn-primary" data-toggle="modal" data-target="#exampleModalCenter"> Change Password </button> <!-- Modal --> <div class="modal fade" id="exampleModalCenter" tabindex="-1" role="dialog" aria-labelledby="exampleModalCenterTitle" aria-hidden="true"> <div class="modal-dialog modal-dialog-centered" role="document"> <div class="modal-content"> <div class="modal-header"> <h3 class="modal-title" id="exampleModalLongTitle">Change Password</h3> <button type="button" class="close" data-dismiss="modal" aria-label="Close"> <span aria-hidden="true">×</span> </button> </div> <div class="modal-body"> <div class="form-group"> <label>Enter New Password</label><br> <input type="password" id="newPassword" class="form-control" required/> </div> </div> <div class="modal-footer"> <button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button> <button type="button" class="btn btn-primary" onclick="changePassword()">Save changes</button> </div> </div> </div> </div> </div> <hr> <!-----Log Out-----------> <div> <button type="button" class="btn btn-default btn-md" onclick="LogOut()"> <span class="glyphicon glyphicon-log-out"></span> Log out </button> </div> </div> </body> </html>
Happy to help.
Thanks.