My aim is to display data table from Google sheet on my web app in a table.
Script is only displaying last data column from dataArray.
Could anyone please help, why this code is only able to append last data column and not all.
Code.gs
function getTableDataTV(){
const ws = SpreadsheetApp.openById("").getSheetByName("Sheet2");
const data = ws.getRange (1,1,ws.getLastRow(), 9).getDisplayValues();
return data
HTML
<html>
<head>
<script>
document.addEventListener("DOMContentLoaded",function(){
google.script.run.withSuccessHandler(generateTable).getTableDataTV();
});
function generateTable(dataArray){
var tabledb = document.getElementById("ptable");
console.log(dataArray);
dataArray.forEach(function(item, index){
if(index == 0){
var theaddb = document.createElement("thead");
var rhead = document.createElement("tr");
var th = document.createElement("th");
console.log(dataArray[0].length);
for(var i=0;i<(dataArray[0].length);i++){
th.textContent = item[i];
rhead.appendChild(th);
}
theaddb.appendChild(rhead);
tabledb.appendChild(theaddb);
}
else if(index > 0)
{
var tbody = document.createElement("tbody");
var rbody = document.createElement("tr");
var td = document.createElement("td");
for(var i=0;i<(dataArray[0].length);i++){
td.textContent = item[i];
rbody.appendChild(td);
}
tbody.appendChild(rbody);
tabledb.appendChild(tbody);
}
});
}
</script>
</head>
<body>
<div class="col s12">
<table id = "ptable">
</table>
</div>
</body>
</html>
console.log(dataArray);
0: Array(9) [ "DATE", "TITLE 1", "TITLE 2", … ]
1: Array(9) [ "2021-11-01", "3094392", "6338933", … ]
2: Array(9) [ "2021-11-02", "3841183", "6731805", … ]
3: Array(9) [ "2021-11-03", "5267740", "7713644", … ]
4: Array(9) [ "2021-11-04", "3144660", "6177727", … ]
5: Array(9) [ "2021-11-05", "3677444", "8649906", … ]
the issue here is that you declared the td element once outside the for loop then keep changing and appending the same element.
try modify your code like this:
for(var i=0;i<(dataArray[0].length);i++){
var td = document.createElement("td");
td.textContent = item[I];
rbody.appendChild(td);
}
In your situation, how about modifying generateTable as follows?
function generateTable(dataArray) {
var tabledb = document.getElementById("ptable");
var tbody = document.createElement("tbody");
dataArray.forEach(function (item, index) {
var len = item.length;
if (index == 0) {
var theaddb = document.createElement("thead");
var rhead = document.createElement("tr");
for (var i = 0; i < len; i++) {
var th = document.createElement("th");
th.textContent = item[i];
rhead.appendChild(th);
}
theaddb.appendChild(rhead);
tabledb.appendChild(theaddb);
} else {
var rbody = document.createElement("tr");
for (var i = 0; i < len; i++) {
var td = document.createElement("td");
td.textContent = item[i];
rbody.appendChild(td);
}
tbody.appendChild(rbody);
}
});
tabledb.appendChild(tbody);
}
var th = document.createElement("th"); and var td = document.createElement("td"); are included in the for loop.tabledb.appendChild(tbody) is moved to out side of the loop.