HTML form to search and edit data in Google Sheets

Navigation
1

I have a form in html to edit or search data in a sheet, everything works but after clicking the edit button or when I try to do two searches in a row the page disappears and I have to manually reload. I would like to know how to do that after clicking to edit the form or the page is reloaded or something similar or if there is an error in the code, I leave the codes and a link to the form I hope you can help me thanks in advance.

Form

link

INDEX.HTML 

<script src="//code.jquery.com/jquery-1.10.2.js"></script>
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css">


<script>

  function buscaInformacion(){
   google.script.run.withSuccessHandler(getLlegada).getLlegadaSS(document.forms[0]);
   google.script.run.withSuccessHandler(getSalida).getSalidaSS(document.forms[0]);
 }

  function editaInformacion(){
   google.script.run.setLlegadaSS(document.forms[0]);
   google.script.run.setSalidaSS(document.forms[0]);
 }

  function getLlegada(llegada){
   var txtLlegada = document.getElementById('llegada');
   txtLlegada.value = llegada;
 }

  function getSalida(salida){
   var txtSalida = document.getElementById('salida');
   txtSalida.value = salida;
 }

  $(function() {
   $( "#buscar" ).click(buscaInformacion);
   $( "#editar" ).click(editaInformacion);
 });

 </script>

<html>
<body bgcolor="e3e5e8">

<br><br>

<div align="center" >

<form id="datosPersona" name="datosPersona" autocomplete="off">
<input type="text" id="id" name="id" autofocus="autofocus" required>

<br><br><br>

<label for="llegada">Llegada a cliente</label>
<input type="date" id="llegada" name="llegada" required>

<br><br>

<label for="salida">Salida de cliente</label>
<input type="date" id="salida" name="salida" >

<br><br><br>
<button id="buscar">Buscar</button>
<button id="editar">Editar</button>

</form>
</div>
</body>
</html>

MAIN.GS 

// Llamar al HTML

function doGet() {
    return HtmlService.createHtmlOutputFromFile('Index');
}


// Buscar y regresar el numero de fila

function buscaReturnRow(sId){

   var hojaCalculo = SpreadsheetApp.openById("1eQBeHPVHjbiDVPRHC8rqRy1WvXC7HYPpxIwUcpylUDw");
   var hojaDatos = hojaCalculo.getSheetByName('BD');
   var numColumns = hojaDatos.getLastColumn();
   var ultimaFila = hojaDatos.getLastRow();
   var sw = 0;
   var row = hojaDatos.getRange(1, 1, ultimaFila, numColumns).getValues();

        for (var i = 1; i < row.length; i++) {   
           for (var col = 0; col < row[i].length; col++) {
                  var id = row[i][2].toString();
                  if (sId == id){                     
                     var indice = i+1;
                     sw = 1;
                  }
           }
        }  
  if (sw == 1){
     return indice;
  }
}



// Buscar y regresar la ia informacion de una fila y columna

  function buscaEnSheet(sId, columna){

   var hojaCalculo = SpreadsheetApp.openById("1eQBeHPVHjbiDVPRHC8rqRy1WvXC7HYPpxIwUcpylUDw");
   var hojaDatos = hojaCalculo.getSheetByName('BD');
   var numColumns = hojaDatos.getLastColumn();
   var ultimaFila = hojaDatos.getLastRow();
   var sw = 0;
   var row = hojaDatos.getRange(1, 1, ultimaFila, numColumns).getValues();

        for (var i = 1; i < row.length; i++) {   
           for (var col = 0; col < row[i].length; col++) {
                  var id = row[i][2].toString();
                  if (sId == id){                     
                     var indice = i+1;
                     sw = 1;
                  }
           }
        }  
  if (sw == 1){
    var info = hojaDatos.getRange(indice, columna).getValue();
    return info;
  }
}



// Buscar y regresar el valor de llegada

  function getLlegadaSS(e){
  var sId = e.id;
  return  buscaEnSheet(sId, 5);
}


// Buscar y regresar el valor de salida

  function getSalidaSS(e){
  var sId = e.id;
  return  buscaEnSheet(sId, 6);
}



// Editar

function setLlegadaSS(e){
  var sId = e.id;
  var sLlegada = e.llegada;
  var row =  buscaReturnRow(sId);
  var hojaCalculo = SpreadsheetApp.openById("1eQBeHPVHjbiDVPRHC8rqRy1WvXC7HYPpxIwUcpylUDw");
  var hojaDatos = hojaCalculo.getSheetByName('BD');
  hojaDatos.getRange(row, 5).setValue(sLlegada);
}

function setSalidaSS(e){
  var sId = e.id;
  var sSalida = e.salida;
  var row =  buscaReturnRow(sId);
  var hojaCalculo = SpreadsheetApp.openById("1eQBeHPVHjbiDVPRHC8rqRy1WvXC7HYPpxIwUcpylUDw");
  var hojaDatos = hojaCalculo.getSheetByName('BD');
  hojaDatos.getRange(row, 6).setValue(sSalida);
}
    
asked by Irvin Perez 06.06.2018 в 18:18
source

1 answer

0

If you have not already done so, it is advisable that you review the guides in the official documentation. Although they are in English, they have several code examples that can help you. In this particular case, review

link

The index.html code should be highlighted 

// Prevent forms from submitting.
function preventFormSubmit() {
  var forms = document.querySelectorAll('form');
  for (var i = 0; i < forms.length; i++) {
    forms[i].addEventListener('submit', function(event) {
      event.preventDefault();
    });
  }
}
window.addEventListener('load', preventFormSubmit);

In turn where we can point out that the trick is in

event.preventDefault ()

    
answered by 07.06.2018 в 02:10
How to use variables with Apollo Graphql Copy row from a table with jquery