Search code examples
javascripthtmlgoogle-apps-scriptweb-applications

Passing variable from Code.gs to html in Google App Script

I am a beginner to Google App Script. I am developing a payment system where after logging in the user should able to view their fist name and last name. I have done the coding but I dont know why its not working for me. I have attached images and my coding to explain myself better. Thank you so much. enter image description here enter image description here

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');
}

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 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>
    function GetRecords() {
      var spin = "<span class=\"spinner-border spinner-border-sm\" role=\"status\" aria-hidden=\"true\"></span>";
      spin += " Loading...";
      document.getElementById("LoginButton").innerHTML = spin;

      var username = document.getElementById("username").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 = username;
          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 username = document.getElementById("currentUser").value;
      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">Years</label><br><select class="form-control form-control-sm" id="filterYear" name="years" required><option value="" selected>Choose...</option>';
        
        filterString.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) {
        google.script.run
          .withSuccessHandler(updateButton)
          .getEmail()
        alert('Password changed');
      }
    }
    </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>
Solution

  • It looks like you are getting confused with variable names.

    This part:

    if(output[0] == 'TRUE') {
    document.getElementById("loginDisplay").style.display = "none";
    document.getElementById("dataDisplay").style.display = "block";  
    document.getElementById("errorMessage").innerHTML = "";
    document.getElementById("currentUser").value = username;
    google.script.run.withSuccessHandler(displayTable).GetRecords(username,"None");

    Should change to:

    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");

    and this part:

    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 username = document.getElementById("currentUser").value;

    Should change to:

    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

    Just in case you are not aware. You cannot call variables outside of their function scope.

    function myfunc(){
    var name = "LEE"
}

console.log(name) // Uncaught ReferenceError: name is not defined