CODEWITHSUNDEEP
javascriptgoogle-apps-script
Fragosojp
Fragosojp

Reputation: 151

problem with loop in google web script app

Good night, how are you?

I created a form, that before the information is inserted in the spreadsheets, the data goes to an HTML table, where you can delete or edit it before sending the data, so far so good,

the problem that I tried to create a loop to go clicking several times until all the data in the table ends, the problem that when I run the loop there is an error that all the data cannot go to the spreadsheet.

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <meta name="viewport" content="width=device-width, initial-scale=1.0"/>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/css/materialize.min.css">
    <link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">
  </head>

  <body id="pagina">

<br>  
    
  <div class= "container">

<div class="row">

    <div class="input-field col s12 l4">
           <input id="saiDesc" type="text" class="autocomplete" class="validate" required>
           <!--<label for="saiDesc" class="active">DESCRIÇÃO DO PRODUTO</label>-->
           <label>PRODUTO</label><label class="validation-error hide" id="fullNameValidationError">This field is required.</label>
    </div>
  
    <div class="input-field col s6 l1">
           <input id="saiCod" type="text" class="validate" required>
           <label for="saiCod">CÓDIGO</label>     
    </div>
    
    <div class="input-field col s6 l1">
           <input id="saiQtd" type="text" class="validate" required>
           <label for="saiQtd">QUANTIDADE</label> 
    </div>
   
    <div class="input-field col s6 l1">
            <input id="saiVlr" type="text" class="validate" required>
            <label for="saiVlr">VALOR</label>
    </div>
    
    <div class="input-field col s6 l1">
            <input id="saiTotal" type="text" class="validate" required>
            <label for="saiTotal">VALOR TOTAL</label>
    </div>
    
    <div class="input-field col s12 l4">
            <input id="saiObs" type="text" class="validate" required>
            <label for="saiObs">OBSERVAÇÃO</label>
    </div>
   
   </div> <!-- Fecha Row -->
   <div class="row">
   <div class="input-field col s6 l1">
            <input disabled id="saiTotalizador" type="text">
            <label for="saiTotalizador">TOTAL</label>
    </div>
 
     <div class="center-align">
       
       <button id="teste" onclick="onFormSubmit(); sum();" class="left waves-effect waves-light btn red darken-2"><i class="material-icons left">add</i>ADD</button>
        <button id="teste" onclick="preencher();" class="center waves-effect waves-light btn blue darken-2"><i class="material-icons left">add</i>preencher</button>
       
       <button id="registrar2" class="right waves-effect waves-light btn blue-grey darken-2"><i class="material-icons left">send</i>REGISTER ALL</button>

     </div> <!-- Fecha Row -->
     
   </div>

  <hr>

                    <!--<div  class="form-action-buttons">
                        <input type="submit" onclick="onFormSubmit();"value="Enviar">
                    </div>-->
          
            <td>
                <table class="list" id="employeeList">
                    <thead>
                        <tr>
                        
                            <th>PRODUTO</th>
                            <th>CÓD INT.</th>
                            <th>QUANT.</th>
                            <th>VALOR<br/>UNIT.</th>
                            <th>VALOR<br/>TOTAL</th>
                            <th>OBS.</th>
                            <th></th>
                            
                        </tr>
                    </thead>
                    <tbody>

                    </tbody>
                </table>


 </div> <!-- Fecha Conatainer -->
 
   <script src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/js/materialize.min.js"></script> 
   
   <script>
   
   var selectedRow = null
   
    function onFormSubmit() {
    
    
    if (validate()) {
        var formData = readFormData();
        if (selectedRow == null)
            insertNewRecord(formData);
        else
            updateRecord(formData);
        resetForm();
    }
}

function readFormData() {
    var formData = {};
    formData["saiDesc"] = document.getElementById("saiDesc").value;
    formData["saiCod"] = document.getElementById("saiCod").value;
    formData["saiQtd"] = document.getElementById("saiQtd").value;
    formData["saiVlr"] = document.getElementById("saiVlr").value;
    formData["saiTotal"] = document.getElementById("saiTotal").value;
    formData["saiObs"] = document.getElementById("saiObs").value;
    return formData;
}

function insertNewRecord(data) {
    var table = document.getElementById("employeeList").getElementsByTagName('tbody')[0];
    var newRow = table.insertRow(table.length);
    cell1 = newRow.insertCell(0);
    cell1.innerHTML = data.saiDesc;
    cell2 = newRow.insertCell(1);
    cell2.innerHTML = data.saiCod;
    cell3 = newRow.insertCell(2);
    cell3.innerHTML = data.saiQtd;
    cell4 = newRow.insertCell(3);
    cell4.innerHTML = data.saiVlr;
    cell5 = newRow.insertCell(4);
    cell5.innerHTML = data.saiTotal;
    cell6 = newRow.insertCell(5);
    cell6.innerHTML = data.saiObs;
    
    cell6 = newRow.insertCell(6);
    cell6.innerHTML = `<a onClick="onEdit(this)" id="testeEdit" class="btn-floating blue"><i class="material-icons left">edit</i></a>
                       <a onClick="onDelete(this)" id="testedelete" class="btn-floating red" ><i class="material-icons left">delete</i></a>`;
}

function resetForm() {
    document.getElementById("saiDesc").value = "";
    document.getElementById("saiCod").value = "";
    document.getElementById("saiQtd").value = "";
    document.getElementById("saiVlr").value = "";
    document.getElementById("saiTotal").value = "";
    document.getElementById("saiObs").value = "";
    selectedRow = null;
}

function onEdit(td) {
    selectedRow = td.parentElement.parentElement;
    document.getElementById("saiDesc").value = selectedRow.cells[0].innerHTML;
    document.getElementById("saiCod").value = selectedRow.cells[1].innerHTML;
    document.getElementById("saiQtd").value = selectedRow.cells[2].innerHTML;
    document.getElementById("saiVlr").value = selectedRow.cells[3].innerHTML;
    document.getElementById("saiTotal").value = selectedRow.cells[4].innerHTML;
    document.getElementById("saiObs").value = selectedRow.cells[5].innerHTML;
    M.updateTextFields();
}
function updateRecord(formData) {
    selectedRow.cells[0].innerHTML = formData.saiDesc;
    selectedRow.cells[1].innerHTML = formData.saiCod;
    selectedRow.cells[2].innerHTML = formData.saiQtd;
    selectedRow.cells[3].innerHTML = formData.saiVlr;
    selectedRow.cells[4].innerHTML = formData.saiTotal;
    selectedRow.cells[5].innerHTML = formData.saiObs;
     
    
}

function onDelete(td) {
   // if (confirm('Are you sure to delete this record ?')) {
        row = td.parentElement.parentElement;
        document.getElementById("employeeList").deleteRow(row.rowIndex);
        resetForm();
    //}
}
function validate() {
    isValid = true;
    if (document.getElementById("saiDesc").value == "") {
        isValid = false;
        document.getElementById("fullNameValidationError").classList.remove("hide");
    } else {
        isValid = true;
        if (!document.getElementById("fullNameValidationError").classList.contains("hide"))
            document.getElementById("fullNameValidationError").classList.add("hide");
    }
    return isValid;
}



function sum() {
        var table = document.getElementById("employeeList"); 
        var sumVal = 0;
        for(var i = 1; i < table.rows.length; i++)

            sumVal = sumVal + parseFloat(table.rows[i].cells[4].innerHTML.replace(",", "."));
            
   document.getElementById("saiTotalizador").value = sumVal;
   M.updateTextFields();
   
}
      
   function preencher(){
   
       document.getElementById("saiDesc").value = Math.floor((Math.random() * 10) + 1);
       document.getElementById("saiCod").value = Math.floor((Math.random() * 10) + 1);
       document.getElementById("saiQtd").value = Math.floor((Math.random() * 10) + 1);
       document.getElementById("saiVlr").value = Math.floor((Math.random() * 10) + 1);
       document.getElementById("saiTotal").value = Math.floor((Math.random() * 10) + 1);
       document.getElementById("saiObs").value = Math.floor((Math.random() * 10) + 1);
       M.updateTextFields();
   
   }
   
   document.getElementById("registrar2").addEventListener("click",registrarTudo2);

function registrarTudo2(){
   
   var linhas = employeeList.querySelectorAll("tr").length-1; 
    for (var i = 0; i < linhas; i ++){
   
      document.getElementById("testeEdit").click();
    
      var userInfo = {};
      
      userInfo.saiDesc = document.getElementById("saiDesc").value;
      userInfo.saiCod = document.getElementById("saiCod").value;
      userInfo.saiQtd = document.getElementById("saiQtd").value;
      userInfo.saiVlr = document.getElementById("saiVlr").value;
      userInfo.saiTotal = document.getElementById("saiTotal").value;
      userInfo.saiObs = document.getElementById("saiObs").value;
      
      google.script.run.registrar(userInfo);
  
      document.getElementById("testedelete").click();
      
      };
      };
   </script>
  </body>
</html>

Gas:

function registrar(userInfo){
  
  var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1XVICy3RPRRUPtyXtgSi9Ab7iaOOODM6zkJ3Fq4T-h_M/edit#gid=0");
  var ws = ss.getSheetByName("Página1");
  
  
  ws.appendRow([userInfo.saiDesc,
                userInfo.saiCod,
                userInfo.saiQtd,
                userInfo.saiVlr,
                userInfo.saiTotal,
                userInfo.saiObs
               ]);
  
 
}

note: if I take the loop and click 2x, 3x, 15x, the register button runs the script right.

just with the loop it gives the error.

Upvotes: 1

Views: 112

Answers (1)

Tanaike
Tanaike

Reputation: 201378

How about this modification?

Modification points:

I think that the reason of your issue is that google.script.run is run with the asynchronous process. But in your case, the method of appendRow is used in the for loop at Google Apps side. In this case, the process cost will be high. So in this answer, I would like to propose the following flow.

  1. Retrieve all values of userInfo and put them to an array.
  2. Send the array to Google Apps Script side using google.script.run.
  3. At Google Apps Script side, convert the array for putting to Spreadsheet and put the values.

When above points are reflected to your script, it becomes as follows.

Modified script:

HTML&Javascript side:

Please modify registrarTudo2() as follows.

function registrarTudo2(){
  var values = [];  // Added
  var linhas = employeeList.querySelectorAll("tr").length-1; 
  for (var i = 0; i < linhas; i ++){
    document.getElementById("testeEdit").click();
    var userInfo = {};
    userInfo.saiDesc = document.getElementById("saiDesc").value;
    userInfo.saiCod = document.getElementById("saiCod").value;
    userInfo.saiQtd = document.getElementById("saiQtd").value;
    userInfo.saiVlr = document.getElementById("saiVlr").value;
    userInfo.saiTotal = document.getElementById("saiTotal").value;
    userInfo.saiObs = document.getElementById("saiObs").value;
    values.push(userInfo);  // Added
    document.getElementById("testedelete").click();
  }
  google.script.run.registrar(values);  // Added
}
Google Apps Script side:

Please modify registrar() as follows.

function registrar(values){
  var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/###/edit#gid=0");
  var ws = ss.getSheetByName("Página1");
  var v = values.map(userInfo => [userInfo.saiDesc,userInfo.saiCod,userInfo.saiQtd,userInfo.saiVlr,userInfo.saiTotal,userInfo.saiObs]);  // Added
  ws.getRange(ws.getLastRow() + 1, 1, v.length, v[0].length).setValues(v);  // Added
}
  • Please set the URL of your Spreadsheet to SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/###/edit#gid=0").

References:

Upvotes: 2

Related Questions