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

Question

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 class="row">
    <div class="row">
    <div class="input-field col s8">
      <i class="material-icons prefix">account_box</i>
      <input id="id_cpf" type="text" class="validate">
      <label for="tel">set the user ID</label>
    </div>
    <div class="input-field col s4">
      <a class="waves-effect waves-light btn-small" id="buscar"><i class="material-icons left">find_in_page</i>Buscar</a>
    </div>
    <!-- Adicionar Informações -->
    <div class="input-field col s6">
      <i class="material-icons prefix">account_circle</i>
      <input id="nome" type="text" class="validate">
      <label for="nome">Complete Name</label>
    </div>
    <div class="input-field col s6">
      <i class="material-icons prefix">offline_pin</i>
      <input id="status_geral" type="text" class="validate" 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 class="btn waves-effect waves-light" type="submit" id="btn">Send
  <i class="material-icons right">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!

Try putting your call to findData() inside of window.onload or DOM onreadyState. BTW there is no <form> tag in your form — Cooper
– Cooper, Commented Feb 10, 2022 at 16:29
Your findData() function has server side code inside of it that is never going to work. If you wish to communicate with the server you must use google.script.run look it up in the documentation. — Cooper
– Cooper, Commented Feb 10, 2022 at 16:37
Oh I see that you have scriptlets in your findData() function have you attempted to evaluate() the script server side prior to rendering? I would suggest that you start with a much simpler example and work it all the way through before tackling this one again. try working you way through this one first: stackoverflow.com/a/59585277/7215091 — Cooper
– Cooper, Commented Feb 10, 2022 at 16:40

TheWizEd · Accepted Answer · 2022-02-10 22:50:51Z

0

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

edited Feb 10, 2022 at 22:50

answered Feb 10, 2022 at 18:09

TheWizEd

8,6542 gold badges14 silver badges23 bronze badges

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

3 Comments

MohamedHavik Over a year ago

Can you help me? I replaced the function on appscript but seems like it didn't work :( docs.google.com/spreadsheets/d/…

TheWizEd Over a year ago

Did it ever work?

MohamedHavik Over a year ago

Yes, the way that I made worked, but it took too long to run. unfortunately with your code the button search doesn't return even M.toast message. seems like something is wrong on <script>. it's a real shame. If you can help me to find the problem I would be very grateful, but thank you very much for your help so far.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related