Title: Filter the tables in html part of Google App Script
Tags: javascript;html;google-apps-script
Question: I am 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 I select a year and filter it (for example if I select 2020) it says that the records are not available. I have included the link to my appscript and some images to explain my self better. Thank you so much.
Before filtering it
After filtering it
Code.gs
var url = &quot;https://docs.google.com/spreadsheets/d/1bM8l6JefFsPrlJnTWf56wOhnuSjdIwg3hMbY1tN1Zp8/edit#gid=1775459006&quot;;
var streetSheetName = &quot;JALAN SANGGUL 4&quot;;
function doGet(e) {
var streetSheetName = &quot;JALAN SANGGUL 4&quot;; // Added
PropertiesService.getScriptProperties().setProperty(&quot;streetSheetName&quot;, streetSheetName); // Added
return HtmlService.createHtmlOutputFromFile('WebAppLogin')
.setTitle(&quot;Resident Payment&quot;);
function checkLogin(username, password) {
var found_record = '';
var name = '';
var ss = SpreadsheetApp.openByUrl(url);
var webAppSheet = ss.getSheetByName(&quot;USERNAMES&quot;);
var getLastRow =  webAppSheet.getLastRow();
for(var i = 2; i <= getLastRow; i++) {
if(webAppSheet.getRange(i, 1).getValue().toUpperCase() == username.toUpperCase() &amp;&amp; webAppSheet.getRange(i, 7).getValue() == password) {
found_record = 'TRUE';
name = webAppSheet.getRange(i, 4).getValue().toUpperCase() + &quot; &quot; + webAppSheet.getRange(i, 5).getValue().toUpperCase();
streetSheetName = webAppSheet.getRange(i, 3).getValue().toUpperCase();
} else if (username.toUpperCase() == 'ADMIN' &amp;&amp; password == 'ADMINPASSWORD') {
found_record = 'TRUE';
name = webAppSheet.getRange(i, 4).getValue().toUpperCase() + &quot; &quot; + webAppSheet.getRange(i, 5).getValue().toUpperCase();
streetSheetName = webAppSheet.getRange(i, 3).getValue().toUpperCase();
}
PropertiesService.getScriptProperties().setProperty(&quot;streetSheetName&quot;, 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(&quot;Configuration&quot;);
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(&quot;USERNAMES&quot;);
var range = sheet.getRange(&quot;A2:A&quot;).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(&quot;USERNAMES&quot;);
var dataRangeValues = displaySheet.getDataRange().getValues();
if (username.toUpperCase() == 'ADMIN') {
dataRangeValues.shift();
filteredDataRangeValues = dataRangeValues;
} else {
filteredDataRangeValues = dataRangeValues.filter(row =&gt; row[0].toUpperCase() == username.toUpperCase());
return filteredDataRangeValues;
function GetPaymentRecords(userProperties,filter) {
var streetSheetName = PropertiesService.getScriptProperties().getProperty(&quot;streetSheetName&quot;); // Added
var transpose = m =&gt; m[0].map((_, i) =&gt; m.map(x =&gt; x[i]));
var resultArray = [];
var ss = SpreadsheetApp.openByUrl(url);
var displaySheet = ss.getSheetByName(streetSheetName);
var addressValues = displaySheet.getRange(&quot;B:C&quot;).getValues();
var paidMonthValues = displaySheet.getRange(1, 7, displaySheet.getLastRow(), displaySheet.getLastColumn() - 6).getValues();
//Logger.log(addressValues);
//Logger.log(transpose(paidMonthValues));
userProperties.forEach((v, i) =&gt; {
var userHouseNumber = v[1];
var userStreet = v[2];
var column = addressValues.reduce(function callbackFn(accumulator, currentValue, index, array) {
if (currentValue[0] == userHouseNumber &amp;&amp; currentValue[1] == userStreet) {
return index
} else {
return accumulator
}
}, '');
//Logger.log(column);
Logger.log(filter)
Logger.log(paidMonthValues);
if(filter==&quot;None&quot;){
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=&gt;{
Logger.log(element!=null)
return element != null;
return resultArray;
WebAppLogin.html
<!DOCTYPE html&gt;
<html&gt;
<head&gt;
<base target=&quot;_top&quot;&gt;
<link rel=&quot;stylesheet&quot; href=&quot;https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css&quot;
integrity=&quot;sha384-JcKb8q3iqJ61gNV9KGb8thSsNjpSL0n8PARn9HuZOnIxN0hoP+VmmDGMN5t9UJ0Z&quot; crossorigin=&quot;anonymous&quot;&gt;
<link rel=&quot;stylesheet&quot; href=&quot;https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css&quot;&gt;
<script src=&quot;https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js&quot;&gt;</script&gt;
<script src=&quot;https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js&quot;&gt;</script&gt;
<script&gt;
var username = &quot;&quot;; // Added
function GetRecords() {
var spin = &quot;<span class=\&quot;spinner-border spinner-border-sm\&quot; role=\&quot;status\&quot; aria-hidden=\&quot;true\&quot;&gt;</span&gt;&quot;;
spin += &quot; Loading...&quot;;
document.getElementById(&quot;LoginButton&quot;).innerHTML = spin;
username = document.getElementById(&quot;username&quot;).value; // Modified
var password = document.getElementById(&quot;password&quot;).value;
var password = document.getElementById(&quot;password&quot;).value;

google.script.run.withSuccessHandler(function(output) {
console.log(output);
var username = output[1];
var name = output[2];
if(output[0] == 'TRUE') {
document.getElementById(&quot;loginDisplay&quot;).style.display = &quot;none&quot;;
document.getElementById(&quot;dataDisplay&quot;).style.display = &quot;block&quot;;
document.getElementById(&quot;errorMessage&quot;).innerHTML = &quot;&quot;;
document.getElementById(&quot;currentUser&quot;).value = name; // CHANGE
google.script.run.withSuccessHandler(displayTable).GetRecords(username,&quot;None&quot;);
} else if(output[0] == 'FALSE') {
document.getElementById(&quot;firstLastName&quot;).innerHTML = &quot;&quot;;
document.getElementById(&quot;currentUser&quot;).value = &quot;&quot;;
document.getElementById(&quot;myFilter&quot;).innerHTML = &quot;&quot;;
document.getElementById(&quot;errorMessage&quot;).innerHTML = &quot;Failed to Login&quot;;
document.getElementById(&quot;LoginButton&quot;).innerHTML = &quot;Login&quot;;
}
}).checkLogin(username, password);
function filter(){
var filterStr = document.getElementById(&quot;filterYear&quot;).value;
var user = document.getElementById(&quot;currentUser&quot;).value;
google.script.run.withSuccessHandler(displayTable).GetRecords(user,filterStr);
function displayTable(result) {
var ar = result.data;
var filterString = result.filter;
ar = ar.sort((a, b) =&gt; new Date(a).getTime() &gt; new Date(b).getTime() ? -1 : 1).splice(-12); // <--- Added
var name = document.getElementById(&quot;currentUser&quot;).value;  // CHANGE
if(ar.length &gt; 0) {
var displayTable = '<table class=\&quot;table\&quot; id=\&quot;mainTable\&quot; &gt;';
displayTable += &quot;<tr&gt;&quot;;
displayTable += &quot;<th&gt;Month</th&gt;&quot;;
displayTable += &quot;<th&gt;House Number</th&gt;&quot;;
displayTable += &quot;<th&gt;Street</th&gt;&quot;;
displayTable += &quot;<th&gt;Payment Status</th&gt;&quot;;
displayTable += &quot;</tr&gt;&quot;;
ar.forEach(function(item, index) {
displayTable += &quot;<tr&gt;&quot;;
displayTable += &quot;<td&gt;&quot;+item[0]+&quot;</td&gt;&quot;;
displayTable += &quot;<td&gt;&quot;+item[1]+&quot;</td&gt;&quot;;
displayTable += &quot;<td&gt;&quot;+item[2]+&quot;</td&gt;&quot;;
displayTable += &quot;<td&gt;&quot;+item[3]+&quot;</td&gt;&quot;;
displayTable += &quot;</tr&gt;&quot;;
});
displayTable += &quot;</table&gt;&quot;;
} else {
var displayTable = &quot;<span style=\&quot;font-weight: bold\&quot; &gt;No Records Found</span&gt;&quot;;
}

var filter = '';
if(filterString.length &gt; 0) {
filter += '<label for=&quot;years&quot; style=&quot;font-size: 20px&quot;&gt;Select the Year</label&gt;<br&gt;<select class=&quot;form-control form-control-sm&quot; id=&quot;filterYear&quot; name=&quot;years&quot; required&gt;<option value=&quot;&quot; selected&gt;Choose...</option&gt;';

filterString.filter(String).forEach(str =&gt; {
filter += '<option value=&quot;'+str+'&quot;&gt;'+str+'</option&gt;';
});
filter += '</select&gt;<button class=&quot;btn btn-primary&quot; type=&quot;button&quot; id=&quot;FilterButton&quot; onclick=&quot;filter()&quot; &gt;Submit</button&gt;';
}

var today = new Date();
var year = today.getFullYear();
var month = today.getMonth();
if (!ar.some(([a,,,d]) =&gt; {
var t = new Date(a);
return year == t.getFullYear() &amp;&amp; month == t.getMonth() &amp;&amp; d.toUpperCase() == &quot;PAID&quot;;
})) {
document.getElementById(&quot;digitalgoods-030521182921-1&quot;).style.display = &quot;block&quot;;
}
document.getElementById(&quot;displayRecords&quot;).innerHTML = displayTable;
document.getElementById(&quot;firstLastName&quot;).innerHTML = &quot;USER: &quot; + name;
document.getElementById(&quot;myFilter&quot;).innerHTML = filter;
document.getElementById(&quot;LoginButton&quot;).innerHTML = &quot;Login&quot;;
document.getElementById(&quot;username&quot;).value = '';
document.getElementById(&quot;password&quot;).value = '';
//change the link according to ur webapp latest version
function LogOut(){
window.open(&quot;https://script.google.com/macros/s/AKfycbwKa4sQ441WUIqmU40laBP0mfiqNMiN-NghEvwUnJY/dev&quot;,'_top');

function changePassword(){
var result = confirm(&quot;Want to Change Password?&quot;);
if (result) {
var newPassword = document.getElementById(&quot;newPassword&quot;).value;
google.script.run.withSuccessHandler(() =&gt; alert('Password changed')).changePassword(username, newPassword);
</script&gt;
</head&gt;
<body&gt;
<h2&gt; Resident Payment Status Portal</h2&gt;
<div id=&quot;loginDisplay&quot; style=&quot;padding: 10px;&quot; &gt;
<div class=&quot;form-row&quot;&gt;
<div class=&quot;form-group col-md-3&quot;&gt;
<label&gt;User Name</label&gt;
<input type=&quot;text&quot; id=&quot;username&quot; class=&quot;form-control&quot; required/&gt;
</div&gt;
</div&gt;
<div class=&quot;form-row&quot;&gt;
<div class=&quot;form-group col-md-3&quot;&gt;
<label&gt;Password</label&gt;<br&gt;
<input type=&quot;password&quot; id=&quot;password&quot; class=&quot;form-control&quot; required/&gt;
</div&gt;
</div&gt;
<button class=&quot;btn btn-primary&quot; type=&quot;button&quot; id=&quot;LoginButton&quot; onclick=&quot;GetRecords()&quot; &gt;
Login
</button&gt;
<span id=&quot;errorMessage&quot; style=&quot;color: red&quot; &gt;</span&gt;
</div&gt;
<hr&gt;
<div style=&quot;display:none&quot; id=&quot;dataDisplay&quot;  &gt;
<div&gt;
<h2 id=&quot;firstLastName&quot;&gt;</h2&gt;
</div&gt;
<input type=&quot;hidden&quot; id=&quot;currentUser&quot; value=&quot;&quot;/&gt;
<div id =&quot;myFilter&quot; class=&quot;form-group&quot;&gt;</div&gt;
<div id=&quot;displayRecords&quot; style=&quot;padding: 10px;&quot; &gt;</div&gt;
<!----Paypal Button--------&gt;
<hr&gt;
<div id=&quot;digitalgoods-030521182921-1&quot; style=&quot;display: none;&quot;&gt;</div&gt;
<script&gt;(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: &quot;&quot;,landing_page: &quot;BILLING&quot;,shipping_preference: &quot;NO_SHIPPING&quot;,payment_method: {payee_preferred: &quot;UNRESTRICTED&quot;}},purchase_units: [{description: &quot;&quot;,soft_descriptor: &quot;digitalgoods&quot;,amount: {breakdown: {item_total: item_total,tax_total: tax_total},value: '50.00' },items: [{name: &quot;Monthly Fees&quot;,quantity: 1,description: &quot;&quot;,sku: &quot;1&quot;,unit_amount: item_total,tax: tax_total}]}]});},onApprove: function (data, actions) {return actions.order.capture().then(function (details) {div.innerHTML = &quot;Order completed. You\x27ll receive an email shortly!&quot;;});},onCancel: function (data) {},onError: function (err) {div.innerHTML = &quot;<pre&gt;&quot; + err.toString()}}).render(&quot;#digitalgoods-030521182921-1&quot;);},init = function () {window.digitalgoods = window.digitalgoods || [];window.digitalgoods.push(render);var file = &quot;https://www.paypal.com/sdk/js?client-id=AS-86gVX_DfakSkq6YZDJRdKZb4SMIziOd5c9DIKy4extQrpb0VFEprDleB_duKI4BJQQRewUdfliZEf\x26currency=MYR&quot;;var script = document.createElement(&quot;script&quot;);script.type = &quot;text/javascript&quot;;script.src = file;script.onload = function() {var i = window.digitalgoods.length;while (i--) {window.digitalgoods[i]();}};div.appendChild(script);};init();})(document.getElementById(&quot;digitalgoods-030521182921-1&quot;), &quot;MYR&quot;);</script&gt;
<!-----Change Password-----------&gt;
<div&gt;
<!--<button type=&quot;button&quot; class=&quot;btn btn-primary btn-md&quot; onclick=&quot;changePassword()&quot;&gt;Change Password</button&gt;--&gt;

<!-- Button trigger modal --&gt;
<button type=&quot;button&quot; class=&quot;btn btn-primary&quot; data-toggle=&quot;modal&quot; data-target=&quot;#exampleModalCenter&quot;&gt;
Change Password
</button&gt;
<!-- Modal --&gt;
<div class=&quot;modal fade&quot; id=&quot;exampleModalCenter&quot; tabindex=&quot;-1&quot; role=&quot;dialog&quot; aria-labelledby=&quot;exampleModalCenterTitle&quot; aria-hidden=&quot;true&quot;&gt;
<div class=&quot;modal-dialog modal-dialog-centered&quot; role=&quot;document&quot;&gt;
<div class=&quot;modal-content&quot;&gt;
<div class=&quot;modal-header&quot;&gt;
<h3 class=&quot;modal-title&quot; id=&quot;exampleModalLongTitle&quot;&gt;Change Password</h3&gt;
<button type=&quot;button&quot; class=&quot;close&quot; data-dismiss=&quot;modal&quot; aria-label=&quot;Close&quot;&gt;
<span aria-hidden=&quot;true&quot;&gt;&amp;times;</span&gt;
</button&gt;
</div&gt;
<div class=&quot;modal-body&quot;&gt;
<div class=&quot;form-group&quot;&gt;
<label&gt;Enter New Password</label&gt;<br&gt;
<input type=&quot;password&quot; id=&quot;newPassword&quot; class=&quot;form-control&quot; required/&gt;
</div&gt;
</div&gt;
<div class=&quot;modal-footer&quot;&gt;
<button type=&quot;button&quot; class=&quot;btn btn-secondary&quot; data-dismiss=&quot;modal&quot;&gt;Close</button&gt;
<button type=&quot;button&quot; class=&quot;btn btn-primary&quot; onclick=&quot;changePassword()&quot;&gt;Save changes</button&gt;
</div&gt;
</div&gt;
</div&gt;
</div&gt;
</div&gt;
<hr&gt;
<!-----Log Out-----------&gt;
<div&gt;
<button type=&quot;button&quot; class=&quot;btn btn-default btn-md&quot; onclick=&quot;LogOut()&quot;&gt;
<span class=&quot;glyphicon glyphicon-log-out&quot;&gt;</span&gt; Log out
</button&gt;
</div&gt;
</div&gt;
</body&gt;
</html&gt;

Here is the accepted answer: When I saw your script, I noticed that var user = document.getElementById(&quot;currentUser&quot;).value; is used at filter() in Javascript side,. And, at GetRecords in GAS side, it seems that the user name used at the login is required to be used. In this case, I thought that user of var user = document.getElementById(&quot;currentUser&quot;).value; might be different from the user name, and this might be the reason of your issue. So, how about the following modification?
function filter(){
var filterStr = document.getElementById(&quot;filterYear&quot;).value;
var user = document.getElementById(&quot;currentUser&quot;).value;
google.script.run.withSuccessHandler(displayTable).GetRecords(user,filterStr);
function filter(){
var filterStr = document.getElementById(&quot;filterYear&quot;).value;
google.script.run.withSuccessHandler(displayTable).GetRecords(username, filterStr);
username has already been declared as the global variable. I thought that this value might be required to be used for GetRecords at GAS side.
Comment for this answer: Owh bro thank you so much for being with me. You are such a life savior for me. Dont get me wrong that I am annoying you but I dont have any experience doing html and also google app script. So I am referring to your help to learn new things bro. Thank you so much. Here eveything is done for payment bro. Everything is working as I imagined bro. For last, I have a question about something to do with the admin part bro.
Comment for this answer: If you have time my mentor, https://stackoverflow.com/questions/67640822/adding-a-row-based-on-condition-using-google-app-sheet?noredirect=1#comment119562685_67640822 - can you help me on this. I swear bro, this is the last one, I would like to share the final output to you beacuse you have been there for me when I needed help. Thank you for everything
Comment for this answer: Okay bro. Hope you can understand it, if you have any question also you can ask me bro. I am quite stressed because the due date for my project is nearing so if I made you feel like I am annoying you really sorry about that bro.
Comment for this answer: Bro I had a question yesterday, Suddenly I couldn't able to log in into my account . I create a new post would you mind helping me on it please bro.
Comment for this answer: https://stackoverflow.com/questions/67640822/adding-a-row-based-on-condition-using-google-app-sheet - Link to the post
Comment for this answer: Sorry bro sorry. I know you have told me before about this really sorry bro. But I just don't know whether you can see that post or not. That is why I just want to reconfirm it bro
Comment for this answer: Bro really sorry for being annoying but your reply now means so much to me bro just because I dont know whether my comment is visible or not. Sorry for everything
Comment for this answer: @MATHAVAN A L KRISHNAN Thank you for replying. I'm glad your issue was resolved. About your new question, when I could correctly understand about it and could find the solution, I would like to propose an answer. When I couldn't find the solution, I apologize.
Comment for this answer: @MATHAVAN A L KRISHNAN About your new questions, I think that when we could find the solution of your new questions, we will answer it. So, I think that it is not required to make users remind to answer for your questions.


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 0, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2021:00:05/22/21', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<javascript><html><google-apps-script>', 'Title': 'Filter the tables in html part of Google App Script', 'ViewCount': 367, 'id': 67645366, 'user_AboutMe': None, 'user_AccountId': 20348066.0, 'user_DisplayName': 'MATHAVAN A L KRISHNAN', 'user_Reputation': 63.0, 'user_Views': 53.0, 'user_id': 14925369.0}}