Home / Questions / 99483
Accepted
Juan Rafael
Asked: 2020-09-03 21:57:21 +0800 CST 2020-09-03 21:57:21 +0800 CST

Button that executes a Google Apps Script

  • 772

I have a script that reads the information from a spreadsheet, and displays it in HTML. I try to adapt a button to it that activates the updating of the information when changes are made in the spreadsheet. I only manage to execute the part of window.location.reload(true);. But the data is not updated.

These are the codes:

function doGet() {
  return HtmlService
      .createTemplateFromFile('Index')
      .evaluate()
      .setSandboxMode(HtmlService.SandboxMode.IFRAME);
}

function getData() {
  return SpreadsheetApp
      .openById('IDdelaspreadsheet')
      .getActiveSheet()
      .getDataRange()
      .getValues();
}

function onSubmit(e){

  doGet();
  getData();
}

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">

  </head>
  <body>
    <? var data = getData(); ?>
    <table>
      <? for (var i = 0; i < data.length; i++) { ?>
        <tr>
          <? for (var j = 0; j < data[i].length; j++) { ?>
            <td><?= data[i][j] ?></td>
          <? } ?>
        </tr>
      <? } ?>
    </table>

    // no es del código original. Aquí trato de incluir el botón
     <form>
    <input onclick="MostrarResultados()" type="button" value="Mostrar" />
    </form>
    <script type="text/javascript">
    function MostrarResultados() {
    google.script.run.onSubmit();
    window.location.reload(true);
    }
    </script>
    // hasta aquí la parte que inserto, no es del código original
 </body>
</html>
aplicación-web

1 Answers

  1. Best Answer

    The code has several problems.

    1. google.script.run.onSubmit();On the client side, it calls a server-side function, but there is no "callback" function that waits for the response from the server.
    2. onSubmit(e)on the server side calls doGet(e), but this is a function that is reserved to be called via the application's URL.
    3. window.location.reload(true);In response from Erick Koleda dated 2013, to a question about the use of this code, HTMLService restricts access to the window object. I'm not entirely sure how it affects when used HtmlService.SandboxMode.IFRAME. To achieve the same I use another approach which I describe in the next point.
    4. Considering that in the best practices document (in English) it is indicated not to use templates to obtain data, which is what the OP's code does, but to obtain them asynchronously, below is an example of a web application that obtains data from a spreadsheet and updates it at the click of a button.

    Web application example

    When the web application is loaded, the data is loaded using window.onloadcalling the function actualizarTabla()which in turn calls google.script.runusing withSuccessHandlerso that once the server responds with the spreadsheet data in HTML table format, that data is passed to the element of the page in which we want to show them. To update the data, the button calls the same function.

    Code.gs

    /*
 * Devuelve página web.
 */
function doGet(e) {
  return HtmlService.createHtmlOutputFromFile('Index')
    .setSandboxMode(HtmlService.SandboxMode.IFRAME);
}
/*
 * Obtiene los datos de la hoja de cálculo especificada por ID.
 */
function getData() {
  return SpreadsheetApp
      .openById('IdDeLaHojaDeCalculo')
      .getActiveSheet()
      .getDataRange()
      .getValues();
}
/*
 * Convierte los datos en una tabla HTML
 */
function crearTabla(){
  var data = getData();
  var tabla = '<table>\n';
  for(var i = 0; i < data.length; i++){
    tabla += '<tr>';
    for(var j = 0; j < data[0].length; j++){
      tabla += '<td>' + data[i][j] + '</td>'
    }
    tabla += '</tr>\n';
  }
  tabla += '</table>';
  return tabla;
}

    Index.html

    <!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script type="text/javascript">
    window.onload = actualizarTabla();

    function actualizarTabla(){
      google.script.run
        .withSuccessHandler(mostrarTabla)
        .crearTabla();
    }
    function mostrarTabla(tabla){
      document.getElementById("tabla").innerHTML = tabla;
    }
    </script>

  </head>
  <body>
    <button onclick="actualizarTabla();">Actualizar</button>
    <div id="tabla">Cargando...</div>
  </body>
</html>

    References:

    • 2