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

Question

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.

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>

So you attached images to explain yourself better but there is no explanation in the images and no further explanation in the question. So what variables to you want to send and where do you want them to go? Also keep in mind that if you are using templated html then you can pass a lot of data into an html file server side with three different kinds of scriplets but once the html is rendered the only way to get data into the html is for the html to request it. You can request it with a google.script.run and have the server return it to a withSuccessHandler. — Cooper, May 21 '21 at 03:32
@MetaMan Sorry Bro basically if code something like this ``document.getElementById("firstLastName").innerHTML = "USER: " + username;`` it is working but I put ``document.getElementById("firstLastName").innerHTML = "USER: " + name;`` it is not working bro. Hope you get what is the problem — , May 21 '21 at 04:00
How is it "not working" do you get any error messages? What is the behavior you want? I think you need to isolate the problem and provide a [Minimal Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) — iansedano, May 21 '21 at 07:11
@iansedano The error is that its just not showing the first name and last name bro. Could you help me with it? — , May 21 '21 at 08:47
@iansedano Owh okay sure bro https://docs.google.com/spreadsheets/d/1bM8l6JefFsPrlJnTWf56wOhnuSjdIwg3hMbY1tN1Zp8/edit#gid=0 - Link to google sheet https://script.google.com/macros/s/AKfycbyF4uuxQXtMU4EIF5MpvtvIkfLeQyNskVlcahJYGZG9aAhgZQKt6c3uUv6SiD46w4-L/exec - Link to web app https://script.google.com/d/1DdRKqUX__-ZITUgTZanQ_A7hUL1kcc0TZOeFmn58wYsX_o_7cqNExnYo/edit?usp=sharing - Link to appscript — , May 21 '21 at 11:17

score 1 · Accepted Answer · answered May 21 '21 at 13:00

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

Bro I did tried using the modification bro. But I have problem in that. After logging in if I select the year and click submit it shows records not found bro. Would you mind helping me with it? — , May 21 '21 at 16:43
https://stackoverflow.com/questions/67646952/how-to-set-the-range-until-last-row-of-a-column-in-sheet-using-google-app-script Bro would you help me solve the question on this post — , May 22 '21 at 07:44

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

1 Answers1

It looks like you are getting confused with variable names.

Linked