Google App Script Return Data from spreadsheet-CodePudding

I was testing a code to get my data from SpreadSheet of google with this code but its not returning my data. Its my script code part the send to a div in html called table-body

google script part cod

document.addEventListener('DOMContentLoaded', function(){
google.script.run.withSuccessHandler(generateTable).getTableData(); 
});

Function that generate table

function generateTable(dataArray){

  function getTableData(){
    var url = "YOUR URL OF SPREADSHEET;
    var ss = SpreadsheetApp.openByUrl(url);
    var ws = ss.getSheetByName("pag01");
    var data = ws.getRange(2, 1, ws.getLastRow()-1, 3).getValues();
    //Logger.log(data);
    return data; 
}

Create a table here

var tbody = document.getElementById("table-body");

  dataArray.forEach(function(r){
  var row = document.createElement("tr");
  var col1 = document.createElement("td");
  col1.textcontent = r[0];
  var col2 = document.createElement("td");
  col2.textcontent = r[1];
  var col3 = document.createElement("td");
  col3.textcontent = r[2];
  var col4 = document.createElement("td");
  col4.textcontent = r[3];

  row.appendChild(col1);
  row.appendChild(col2);
  row.appendChild(col3);
  row.appendChild(col4);
  tbody.appendChild(row);
});

CodePudding user response：

Here is an example of how to create an HTML table from a spreadsheet using google.script.run. I use templated HTML if I may add data on the server before I display the page. I also use Immediate-Invoked Function Expression (IIFE) (function() {})() if I'm going to request the data from the client to build the page after it has been initially displayed. And I always wrap my code in try {} catch(){} block.

Junk.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <div>
      <table id="table-body">
      </table>
    </div>
    <script>
      function generateTable(data) {
        try {
          let table = document.getElementById("table-body");
          data.forEach( values => {
              let row = document.createElement("tr");
              values.forEach( value => {
                  let cell = document.createElement("td");
                  let text = document.createTextNode(value);
                  cell.appendChild(text);
                  row.appendChild(cell);
                }
              );
              table.appendChild(row);
            }
          );
        }
        catch(err) {
          alert(err);
        }
      }
      (function() {
        try {
          google.script.run.withSuccessHandler(generateTable).getTableData();
        }
        catch(err) {
          alert(err);
        }
      })();
    </script>  

  </body>
</html>

Code.gs

function getTableData() {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spread.getSheetByName("Sheet1");
    let data = sheet.getDataRange().getDisplayValues();
    Logger.log(data);
    return data;
  }
  catch(err) {
    Logger.log(err);
  }
}

CodePudding user response：

run launchMyDialog()

GS:

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

function launchMyDialog() {
  let html = HtmlService.createHtmlOutputFromFile("ah1").setWidth(800);
  SpreadsheetApp.getUi().showModelessDialog(html,"Data Dialog");
}

HTML:

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
</head>
<body>
 <script>
  window.onload = function() {
    google.script.run
    .withSuccessHandler(function(vs){
      tableCreate(vs);
    })
    .getData(); 
  }  
  function tableCreate(vs) {
    var body = document.getElementsByTagName('body')[0];
    var tbl = document.createElement('table');
    tbl.style.width = '100%';
    tbl.setAttribute('border', '1');
    var tbdy = document.createElement('tbody');
    
    vs.forEach((r,i) => {
      var tr = document.createElement("tr");
      r.forEach((c,j) => {
        if(i == 0) {
          var th = document.createElement("th");
          var tc = document.createTextNode(c);
          th.appendChild(tc);
          tr.appendChild(th);
        } else {
          var td = document.createElement("td");
          var tc = document.createTextNode(c);
          td.appendChild(tc);
          tr.appendChild(td);
        }
      });
      tbdy.appendChild(tr);
    });
    tbl.appendChild(tbdy);
    body.appendChild(tbl)
  }
  console.log("Code");
 </script>
</body>
</html>

Demo: