Filter the tables in html part of Google App 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 = "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;
      var user = document.getElementById("currentUser").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) => 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>

  <h2> Resident Payment Status Portal</h2>

  <div id="loginDisplay" style="padding: 10px;" >

    <div class="form-row">
      <div class="form-group col-md-3">
      <label>User Name</label>
      <input type="text" id="username" class="form-control" required/>
      </div>
    </div>

    <div class="form-row">
      <div class="form-group col-md-3">
      <label>Password</label><br>
      <input type="password" id="password" class="form-control" required/>
      </div>
    </div>

    <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 id ="myFilter" class="form-group"></div>
    <div id="displayRecords" style="padding: 10px;" ></div>

  <!----Paypal Button-------->
    <hr>
    <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. You\x27ll 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_duKI4BJQQRewUdfliZEf\x26currency=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>
  
  <!-----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">&times;</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>

Tanaike · Accepted Answer · 2021-05-22 01:44:13Z

3

When I saw your script, I noticed that var user = document.getElementById("currentUser").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("currentUser").value; might be different from the user name, and this might be the reason of your issue. So, how about the following modification?

From:

function filter(){
  var filterStr = document.getElementById("filterYear").value;
  var user = document.getElementById("currentUser").value;
  google.script.run.withSuccessHandler(displayTable).GetRecords(user,filterStr);
}

To:

function filter(){
  var filterStr = document.getElementById("filterYear").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.

answered May 22, 2021 at 1:44

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

MATHAVAN A L KRISHNAN Over a year ago

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.

MATHAVAN A L KRISHNAN Over a year ago

If you have time my mentor, stackoverflow.com/questions/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

Tanaike Over a year ago

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

MATHAVAN A L KRISHNAN Over a year ago

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.

MATHAVAN A L KRISHNAN Over a year ago

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.

|

Collectives™ on Stack Overflow

Filter the tables in html part of Google App Script

1 Answer 1

From:

To:

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

From:

To:

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related