Google Sheet - HTML App Script : Trying to get DataTable to use Individual column searching (select
Google Sheet - HTML App Script : Trying to get DataTable to use Individual column searching (select
TommTFG
Posts: 3Questions: 2Answers: 0
I have followed an example code by BPWEBS to enable me to output information in a Google Sheets database to a HTML page.
It is using Datatable and I can replicate the base simple code provided in the example from BPWEBS and it works.
I am trying to update the code to take advantage of The ability to filter the database via a selection for each row. Provide by the API option: Individual column searching (select inputs)
I added the code in the portions of the code in the example, to the Java Script. Below the Columns: [ ] section. When in Test deployment The Database shows, the column dropdown are populated with the Unique Selections as the code is requesting. , but when I select one of the option in the column to filter. it does not filter The table with the selected option. it just sits there and does not re Draw the table. Is there something I am over looking or missing in the code I have mashed together below?
Code.gs
/*
# CREATED BY: BPWEBS.COM
# URL: https://www.bpwebs.com
*/
function doGet() {
return HtmlService.createTemplateFromFile('Index').evaluate();
}
//GET DATA FROM GOOGLE SHEET AND RETURN AS AN ARRAY
function getData() {
var spreadSheetId = "Place Sheets ID here"; //CHANGE
var dataRange = "Data!B2:N"; //CHANGE
var range = Sheets.Spreadsheets.Values.get(spreadSheetId, dataRange);
var values = range.values;
return values;
}
//INCLUDE JAVASCRIPT AND CSS FILES
//REF: https://developers.google.com/apps-script/guides/html/best-practices#separate_html_css_and_javascript
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename)
.getContent();
}
//Ref: https://datatables.net/forums/discussion/comment/145428/#Comment_145428
//Ref: https://datatables.net/examples/styling/bootstrap4
HTML
<!DOCTYPE html>
<html>
<head>
<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.10.23/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.23/js/dataTables.bootstrap4.min.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">
<?!= include('JavaScript'); ?><!--INCLUDE JavaScript.html FILE-->
</head>
<body>
<div class="container-fluid px-5">
<br>
<div class="row">
<table id="data-table" class="table table-striped table-sm table-hover table-bordered">
<!-- TABLE DATA IS ADDED BY THE showData() JAVASCRIPT FUNCTION ABOVE -->
<tfoot>
<tr>
<th>ISA Name</th>
<th>Agent Name</th>
<th>Ref%</th>
<th>Client Name</th>
<th>Client Number</th>
<th>Update</th>
<th>Status</th>
<th>Sent</th>
<th>met</th>
<th>Offer</th>
<th>Sold</th>
<th>Re-Engage</th>
</tr>
</tfoot>
</table>
</div>
</div>
</body>
</html>
JavaScript
<script>
/*
*THIS FUNCTION CALL THE getData() FUNCTION IN THE Code.gs FILE,
*AND PASS RETURNED DATA TO showData() FUNCTION
*/
google.script.run.withSuccessHandler(showData).getData();
//THIS FUNCTION GENERATE THE DATA TABLE FROM THE DATA ARRAY
function showData(dataArray){
$(document).ready(function(){
$('#data-table').DataTable({
data: dataArray,
//CHANGE THE TABLE HEADINGS BELOW TO MATCH WITH YOUR SELECTED DATA RANGE
columns: [
{ title: 'ISA Name' },
{ title: 'Agent Name' },
{ title: 'Ref%' },
{ title: 'Client Name' },
{ title: 'Client Number' },
{ title: 'Update' },
{ title: 'Status'},
{ title: 'Sent'},
{ title: 'met'},
{ title: 'Offer'},
{ title: 'Sold'},
{ title: 'Re-Engage'},
],
//THIS IS WHERE I ADDED THE EXAMPLE CODE
initComplete: function () {
this.api()
.columns()
.every(function () {
let column = this;
// Create select element
let select = document.createElement('select');
select.add(new Option(''));
column.footer().replaceChildren(select);
// Apply listener for user change in value
select.addEventListener('change', function () {
var val = DataTable.util.escapeRegex(select.value);
column
.search(val ? '^' + val + '$' : '', true, false)
.draw();
});
// Add list of options
column
.data()
.unique()
.sort()
.each(function (d, j) {
select.add(new Option(d));
});
});
}
});
});
}
</script>
Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem:
This question has an accepted answers - jump to answer
Answers
Your code looks good so I placed it in this test case:
https://live.datatables.net/cisevubo/1/edit
And it works.
Do you get errors in the browser's console?
I would put a break point in the event listener to see if it is firing when choosing a selection then step through to see what happens.
If you still need help please post a link to your page or a test case replicating the issue so we can help debug.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case
Kevin
I figured it out. The script src's where out of date. I updated them and it work prefect.