Datatables Footer CallBack not working
Datatables Footer CallBack not working
NunoMarques
Posts: 3Questions: 2Answers: 0
Hi everyone!
I am using a google sheet to pull data to a datatable.
The code is working but i would like to pull a footer callback to give me sums of current page and totals.
Unfortunatelly this is not working and i can´t find how to put it working.
Another issue is to put information of number of pages and page selector on the same row.
Here is my google sheet with data:
The web page with the footer issue is this one:
The html code is this:
(''')
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no">
<base target="_top">
<!--INCLUDE REQUIRED EXTERNAL JAVASCRIPT AND CSS LIBRARIES-->
<script src="https://code.jquery.com/jquery-3.5.1.js"></script>
<script src="https://cdn.datatables.net/1.13.1/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/fixedheader/3.2.4/js/dataTables.fixedHeader.min.js"></script>
<script src="https://cdn.datatables.net/1.13.1/js/dataTables.bootstrap4.min.js"></script>
<script src="https://cdn.datatables.net/buttons/2.3.2/js/dataTables.buttons.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script>
<script src="https://cdn.datatables.net/buttons/2.3.2/js/buttons.html5.min.js"></script>
<script src="https://cdn.datatables.net/buttons/2.3.2/js/buttons.print.min.js"></script>
<script src="cdn.datatables.net/plug-ins/1.12.1/api/sum().js"></script>
<link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.5.2/css/bootstrap.css">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.23/css/dataTables.bootstrap4.min.css">
<link rel="stylesheet" href="https://cdn.korzh.com/metroui/v4/css/metro-all.min.css">
<style>
.container-small {
max-width:1400px;
margin:0 auto;
}
</style>
<?!= include('JavaScript'); ?> <!--INCLUDE JavaScript.html FILE-->
</head>
<body>
<div class="container-small">
<!-- content here -->
<div class = "grid">
<div class="row">
<div class="cell d-flex flex-row"><h2>Search Table</h2></div>
<div class="cell flex-align-self-center"></div>
<div class="cell d-flex flex-row-r">
<a href="<?= ScriptApp.getService().getUrl(); ?>?" align="right" class="button primary outline">Home</a>
</div>
</div>
</div>
</div>
<div class="container-small">
<table id="data-table" class="table table-striped table-sm table-hover" style="width:100%">
<!-- TABLE DATA IS ADDED BY THE showData() JAVASCRIPT FUNCTION ABOVE -->
</table>
</div>
</body>
</html>
(''')
The javascript file is:
(''')
google.script.run.withSuccessHandler(showData).getData();
function showData(dataArray){
$(document).ready(function(){
$('#data-table').DataTable({
footerCallback: function (row, data, start, end, display) {
var api = this.api();
// Remove the formatting to get integer data for summation
var intVal = function (i) {
return typeof i === 'string' ? i.replace(/[\$,]/g, '') * 1 : typeof i === 'number' ? i : 0;
};
// Total over all pages
total = api
.column(4)
.data()
.reduce(function (a, b) {
return intVal(a) + intVal(b);
}, 0);
// Total over this page
pageTotal = api
.column(4, { page: 'current' })
.data()
.reduce(function (a, b) {
return intVal(a) + intVal(b);
}, 0);
// Update footer
$(api.column(4).footer()).html('$' + pageTotal + ' ( $' + total + ' total)');
},
dom: '<"container-fluid"<"row"<"col"B><"col"l><"col"f>>>rtip',
buttons: [
'excel', 'pdf', 'print'
],
//scrollY: '70vh',
//scrollCollapse: true,
//paging: false,
data: dataArray,
//CHANGE THE TABLE HEADINGS BELOW TO MATCH WITH YOUR SELECTED DATA RANGE
columns: [
{"title":"Data"},
{"title":"Full Name"},
{"title":"EPIS"},
{"title":"Project"},
{
"title":"Values", render: $.fn.dataTable.render.number('.',',', 0, 'kr')
},
{"title":"Notes"}
]
});
});
}
(''')
The script to get data to the table:
(''')
function loadTable(){
const htmlService = HtmlService.createTemplateFromFile("table")
const html = htmlService.evaluate().addMetaTag("viewport","width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no")
return html
}
//Get data from data sheet to datatable
function getData(){
var spreadSheetId = "1aRRiUgFpIO6Ga07sU7bv9iXBslHLOn88Lq8sDUdHF6U"; // Please set your Spreadsheet ID.
var sheet = SpreadsheetApp.openById(spreadSheetId).getSheetByName("DATA"); //Name of sheet
var values = sheet.getRange("A2:F" + sheet.getLastRow()).getDisplayValues(); //Insert range
return values;
}
(''')
Sorry about the long question.
Thank you for all the help.
Answers
This example here shows a working footer that sums the colums. If that doesn't help, we're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.
Cheers,
Colin
You need to add a
tfootelement to thetabletag. Either add it directly in HTML or use Javascript/jQuery to add it before the Datatable is initialized. Thecolumns.titleoption does not create a footer.Your
domoption looks like it might need some adjustment:Comparing to the default dom option for BS4 the
rtipis outside the rowdivused in the default setting.If you still need help then please build a test case that replicates the issues you want help debugging.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case
Kevin
Thank you for your tips.
Footer callback is already working.
I fixed dom code
dom: '<"container-fluid"<"row"<"col"B><"col"l><"col"f>>>tr<"container-fluid"<"row"<"col"i><"col"p>>>'
and i added tfoot like you recomended
<tr>
<th colspan="4" style="text-align:right">Total:</th>
<th></th>
</tr>
But when i enabled it, the sum result brakes the format of datatables and sum totals are not aligned with 4th column. How can i put it aligned with this column?
It would be great if we could align it to this column on left and go over the 5th column.
Ps: I've tryed to introduce this in a case but, since this table import data from a google sheet, it didn´t get any results. Sorry by that
You can use a DOM sourced table or Javascript data, like this example to provide fake data for the test case. It looks like the problem is in the footer HTML. You can right click on the footer and use inspect to see the HTML for the footer. We will need to see this issue in a test case to help resolve.
Use standard CSS to align the text in the cell. Maybe use
colspanto combine column 4 and 5.Kevin