Google Script taking long time to open a form in Google HTML Service-CodePudding

I created a form with google HTML service in Google Sheets, so, when I type the user id, JavaScript from HTML <script> then executes the findData() function to find the user id row in the "database" sheet and it includes the values oninputs fields with document.getElementById("nome").value = variable;. The problem is that depending on how big the .getLastRow() inside `for()´ size is, the html takes a more time to load. Just to you guys have an idea in the last test I did, it took 2 minutes to open the application.

My HTML:

   <!DOCTYPE html>
   <html>
   <head>
   <!--Import Google Icon Font-->
   <link href="https://fonts.googleapis.com/icon?family=Material Icons" rel="stylesheet">
   <!-- Compiled and minified CSS -->
   <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/css/materialize.min.css">

  <!--Let browser know website is optimized for mobile-->
  <meta name="viewport" content="width=device-width, initial-scale=1.0"/>
</head>

<body>
    <div >
    <div >
    <div >
      <i >account_box</i>
      <input id="id_cpf" type="text" >
      <label for="tel">set the user ID</label>
    </div>
    <div >
      <a  id="buscar"><i >find_in_page</i>Buscar</a>
    </div>
    <!-- Adicionar Informações -->
    <div >
      <i >account_circle</i>
      <input id="nome" type="text" >
      <label for="nome">Complete Name</label>
    </div>
    <div >
      <i >offline_pin</i>
      <input id="status_geral" type="text"  list="ger">
          <datalist id="ger">
              <option value="">Selecione</option>
              <option value="HOMOLOGAÇÃO">HOMOLOGAÇÃO</option>
              <option value="COMPRA">COMPRA</option>
              <option value="CONCLUÍDO">CONCLUÍDO</option>
          </datalist>
      <label for="status_geral">Status</label>
    </div>
   
   <div>
   <button  type="submit" id="btn">Send
  <i >send</i>
 </button>
    </div>
  </div>
 </div><!--end row-->

</div>
  <!-- Compiled and minified JavaScript -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/js/materialize.min.js"> 
</script>
<script>
  var idBox = document.getElementById("id_cpf");
  var nomeBox = document.getElementById("nome");
  document.getElementById("buscar").addEventListener("click", findData);

  function findData(){
    var id=idBox.value;

    if(id.length==0){
    M.toast({html: 'you need to type a value on ID'})

    }else{
      <? for (var j = 1; j <=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Database").getLastRow() ; j  ) { ?>
     var idDb = <?= 
   SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Database").getRange('A' j).getValue() ?>;
     var nomeDb = <?= 
   SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Database").getRange('B' j).getValue() ?>;
     var statusDb = <?= 
   SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Database").getRange('C' j).getValue() ?>;
                                    
      if(idDb == id){
        document.getElementById("nome").value = nomeDb;
        document.getElementById("status_geral").value = statusDb; 
      }
    <? } ?>

    M.toast({html: 'Value Found!'  lastRow})
    } 

}
</script>
<style>
 
button{
top: 5px;
left: 15px;
 }
 </style>
 </body>

My gs code:

function ShowAddInformation2() {
var userform= HtmlService.createTemplateFromFile("AddInformation2").evaluate().setTitle("Add or change Information")
SpreadsheetApp.getUi().showModalDialog(userform,"Add or change Information")}

Data Entry Image

Below you can also find the spreadsheet link. Feel free to make any changes. Spreadsheet with data entry

I also tried a possibility to create a deploy with web app, but the code just doesn't work. You can see it on link Web App - Data Entry

So, sorry if I wasn't clear, English is not my first language.

If anyone can help with any possibility I will be very grateful!

CodePudding user response：

You could replace your <script> code with this. There is an extra closing curly barce } after findData.

function findData(){
  var id=idBox.value;

  if(id.length==0){
    M.toast({html: 'you need to type a value on ID'})

  }
  else {
    google.script.run.withSuccessHandler( function (results) {
      document.getElementById("nome").value = results[1];
      document.getElementById("status_geral").value = results[2]; 
      M.toast({html: 'Value Found!'  results[0]});
    }).findData(id);
  }
}

And add to your Code.gs. I edited the Code.gs portion to change the getRange(). Your data contains dates and those can not be returned to the HTML client without using JSON.

function findData(id) {
  try {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Database");
    var data = sheet.getRange(1,1,sheet.getLastRow(),3)().getValues();
    return data.find( function (row) { return row[0] === id; } );
  }
  catch(err) {
    console.log(err);
  }
}