Search code examples
javascripthtmlgoogle-apps-scriptgoogle-sheetsweb-applications

Get data from a spreadsheet and put it in HTML through functions

I'm trying to put the data from a google spreadsheet in an html, I saw some examples in the documentation (https://developers.google.com/apps-script/guides/html/templates) and I found this functionality very interesting. I wanted to know if there's a way to get the same result as the documentation, but doing it through functions instead of putting the code directly in the html.

I tried the below code, but it returns the error:

Uncaught ReferenceError: getData is not defined at HTMLButtonElement.createTable.

Script.gs

 function doGet(e) {
      var template = HtmlService.createTemplateFromFile('HTML');
      template.variablename = "";
      var html=template.evaluate();
      return html;
    }
 function getData() {
   return SpreadsheetApp.openById("SPREADSHEETID")
          .getSheetByName("NAME")
          .getDataRange()
          .getValues();
    }

HTML

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <h1>SPREADSHEET</h1>
  <body>
    <button id="DT" class="submit">SPREADSHEET</button>
     <?!= include('javascript'); ?>
  </body>
</html>

Javascript.html

<script>
     document.getElementById("DT").addEventListener("click",createTable);
      function createTable() {
       var data=getData();
        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>"
          } 
</script>
Solution

  • enter image description here### Templated Html

    gs:

    function launchADialog() {
  let t = HtmlService.createTemplateFromFile("ah2");
  t.lr = SpreadsheetApp.getActive().getSheetByName("Sheet0").getLastRow();
  let html = t.evaluate();
  SpreadsheetApp.getUi().showModelessDialog(html,"Dialog");
}

function getMyData() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  return sh.getDataRange().getValues();
}

    html:

    <!DOCTYPE html>
<html>
<head>
  <base target="_top">
</head>
<body>
  <div id="tabledata">
       <? var vs = getMyData(); ?>
       <table>
         <? vs.forEach((r,i)=>{ ?>
           <tr>
           <? r.forEach((c,j)=>{ ?>
             <? if(i == 0) { ?>
            <th style="padding:2px 5px;font-weight:bold;border:1px solid black;"><?= c ?> </th>           
           <? } else { ?>
             <td style="padding:2px 5px;border:1px solid black;"><?= vs[i][j] ?> </td>
           <? } ?>
         <?  }); ?>
           </tr>
         <? }); ?>
       </table>
     </div>
     <h3> Number of rows: <?= lr ?> </h3>
</body>
</html>

    enter image description here

    Here's another way to do the same thing:

    function displayData() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  let html = '<style>td,th{border:1px solid black)</style><table>';
  sh.getDataRange().getValues().forEach((r, i) => {
    html += '</tr>';
    r.forEach((c, j) => {
      if (i == 0) {
        html += `<th>${c}</th>`;
      } else {
        html += `<td>${c}</td>`;
      }
    })
  html += '</tr>';
  })
  html += `</table><br /> <p> The table has ${sh.getLastRow()} rows` ;
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html),"Test Dialog");
}

    And another way:

    gs:

    function getMyData() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  let html = '<style>td,th{border:1px solid black;)</style><table>';
  sh.getDataRange().getValues().forEach((r, i) => {
    html += '</tr>';
    r.forEach((c, j) => {
      if (i == 0) {
        html += `<th>${c}</th>`;
      } else {
        html += `<td>${c}</td>`;
      }
    })
  html += '</tr>';
  });
  html += `</table><br /> <p> The table has ${sh.getLastRow()} rows` ;
  //Logger.log(html)
  return html;
}

    html:

    <!DOCTYPE html>
<html>
<head>
  <base target="_top">
</head>
<body>
  <div id="here"><div>;
  <script>
    window.onload = function() {
      google.script.run
      .withSuccessHandler( function(hl)  {
        document.getElementById("here").innerHTML = hl;
      })
      .getMyData();
    }
  </script>
</body>
</html>