I'm trying to create a table with multi-pages using spreadsheet data as follow:
In the code.gs, I get the data from the spreadsheet like this:
function getData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet1");
var data = sheet.getRange(2, 1, sheet.getLastRow()-1, 3).getValues();
return data;
}
Then in the HTML file, the above data is generated in the table like this:
<table id="myTable">
<thead>
<tr class="header">
<th>First Name</th>
<th>Second Name</th>
<th>Third Name</th>
</tr>
</thead>
<tbody id="table-body">
</tbody>
</table>
<script src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/js/materialize.min.js"></script>
<script>
document.addEventListener('DOMContentLoaded',function(){ generateTableBody(data); });
google.script.run.withSuccessHandler(generateTableBody).getData();
function generateTableBody(dataArray) {
var table = document.getElementById("myTable");
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];
row.appendChild(col1);
row.appendChild(col2);
row.appendChild(col3);
tbody.appendChild(row);
});
}
</script>
Next, I added the below script to the HTML file in order to create the pages out of the data at which max. 10 rows per each page:
<script>
$('#myTable').each(function() {
var currentPage = 0;
var numPerPage = 10;
var $table = $(this);
$table.bind('repaginate', function() {
$table.find('tbody tr').hide().slice(currentPage * numPerPage, (currentPage + 1) * (numPerPage-1)).show();
});
$table.trigger('repaginate');
var numRows = $table.find('tbody tr').length;
var numPages = Math.ceil(numRows / numPerPage);
var $pager = $('<div class="pager"></div>');
for (var page = 0; page < numPages; page++) {
$('<span class="page-number"></span>').text(page + 1).bind('click', {
newPage: page
}, function(event) {
currentPage = event.data['newPage'];
$table.trigger('repaginate');
$(this).addClass('active').siblings().removeClass('active');
}).appendTo($pager).addClass('clickable');
}
$pager.insertBefore($table).find('span.page-number:first').addClass('active');
});
</script>
At this point, I have 2 issues:
this part $table.bind('repaginate', function() { $table.find('tbody tr').hide().slice(currentPage * numPerPage, (currentPage + 1) * (numPerPage-1)).show();}); is supposed to return all the rows under the table body, hide all, then slice to show only the first 10 rows in the first page but it does not work (and my guessing is that because the generateTableBody(dataArray) function is completed after the query function was completed and so $table.find('tbody tr') returned nothing first then the generateTableBody(dataArray) returned all the rows down to the interface instead of just the first 10 rows
Based on that, I added this part $("tbody#table-body").find("tr:gt(0)").hide().slice(0, 9).show(); to the bottom of the generateTableBody(dataArray) function and the issue was solved, but
The second issue was var numRows = $table.find('tbody tr').length; also returned nothing most probably for the same reason, so I tried to populate the number of rows like that:
In the code.ge I added:
function fetchLastRow(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet1");
var lr = sheet.getLastRow()-1;
return lr;
}
And in the HTML file I added a hidden input to read the lr returned like this:
<input type="hidden" id="numRows">
<script>
window.addEventListener('load', populate());
function populate(){ google.script.run.withSuccessHandler(displayValue).fetchLastRow(); }
function displayValue(lr){ document.getElementById("numRows").value=lr; }
</script>
I was able to read the number of rows successfully in the hidden input, so the next step was that:
var numRows = document.getElementById('numRows').value;
or
var numRows = document.getElementById('numRows').getValue();
or
var numRows = document.getElementById('numRows').innerHTML;
but (in all cases) the var numRows = 0 (I think that because of the window.addEventListener('load', populate()); is also completed after the query)
So I think if I was able to run the query function at last (after generating table data and reading the max active rows in the spreadsheet), that will solve the two issues explained.
