Today i will explain how to avoid high Memory Usage using DataTables in Server-Side Processing Mode. Also, we will add an Advanced Filter
This Post is similar to my other one named “DataTables in Server Side Mode (Advanced Filter with PHP)“, but the difference is that here we will use Google Apps Script (for a web app) and Google Spreadsheets (as a database).
Server-side processing
There are times when reading data from the DOM is simply too slow or unwieldy, particularly when dealing with many thousands or millions of data rows. To address this DataTables’ server-side processing feature provides a method to let all the “heavy lifting” be done by a database engine on the server-side (they are after all highly optimised for exactly this use case!), and then have that information drawn in the user’s web-browser. Consequently, you can display tables consisting of millions of rows with ease.
When using server-side processing, DataTables will make an Ajax request to the server for each draw of the information on the page (i.e. when paging, ordering, searching, etc.). DataTables will send a number of variables to the server to allow it to perform the required processing and then return the data in the format required by DataTables. [Extracted from DataTables.net]
Fully Working DEMO for Test Purposes: Click here to TEST the Web App
| Spreadsheet (Db) | Script for the Web App (Standalone) |
 |
 |
Download Source Files: Click to go to Github Repository
PART 1: SPREADSHEET
FILE: DataTablesServerSide_db (Google Spreadsheet / Our Database)
In this file we have a table with some “data” that later will be displayed in a webpage (using DataTables). This “data” is stored in a google spreadsheet instead of using a database like MySQL, also in the Second Part of this Post you can see how we can display all values from this sheet in a web app (html page), using Server Side Processing.
The Server Side Processing Mode, let us to display only the information required for a specified page. Here the Pagination is very important, and also, this Pagination functionallity is used with the Advanced Filter Functionallity in order to display the data in right way (avoiding high memory rate).
FILE: DataTablesServerSide_db/code.gs (This code is inside the spreadsheet)
In order to put the following code in the right location, you need to go to “Tools” -> “Script Editor” (In the spreadsheet)
function doPost(e) {
var result = validation(e);
// else, use page parameter to pick an html file from the script
//return HtmlService.createTemplateFromFile(e.parameter['page']).evaluate();
//var result = data();
var total = count_total();
var filtered = (result[0].list_1).length; // deberia ser el conteo de los filtrados por longitud de mostrados
return ContentService
.createTextOutput(JSON.stringify({"draw":null, "recordsTotal":total,"recordsFiltered":filtered, "data":result[0].list_2}))
.setMimeType(ContentService.MimeType.JSON);
}
function data(){
// la estructura de la tabla es id, email, first_name, last_name, employee_id, status
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
var lastRow = sheet.getLastRow();
var range = 'A2:F'+lastRow;
/* aqui se aplican los filtros, la data que se retorna ya debe estar filtrada "setFilter()"
o con toda la data obtenida se hacen if con las condiciones de los input
*/
var data = sheet.getRange(range).getValues();
//var json = JSON.stringify(data);
//Logger.log(data);
return data;
}
function validation(e){
//validar los 15 campos
var parameters = e.parameter;
var result = data();
var start = parameters.start; //verificar si se envia
var draw = parameters.draw; //verificar si se envia
var length = parameters.length; //verificar si se envia
//var order = parameters['order[0][column]']; //verificar si se envia
var column_order = new Array('id', 'email', 'first_name', 'last_name', 'employee_id', 'status');
var order_by = new Array(0,'desc');
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("draft");
//var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
//var nextRow = sheet.getLastRow()+1;
//var row = [];
//var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
var order;
var order_2;
if( typeof(parameters['order[0][column]']) != "undefined" ){
order = parameters['order[0][column]'];
order_2 = parameters['order[0][dir]'];
}else{
order = '0';
order_2 = 'desc';
}
sheet.getRange('B1').setValue(order);
sheet.getRange('C1').setValue(order_2);
sheet.getRange('B2').setValue(start);
sheet.getRange('B3').setValue(length);
sheet.getRange('B4').setValue(draw);
//sheet.getRange().setValues([row]);
/*
rows es para la consulta a la BD para el select, se podria crear para ponerle los index de las columnas
*/
var keys = [
['email', parameters.email, 'radioEmail', parameters.radioEmail, 'radioEmail2', parameters.radioEmail2],
['first_name', parameters.first_name, 'radioFirstname', parameters.radioFirstname, 'radioFirstname2', parameters.radioFirstname2],
['last_name', parameters.last_name, 'radioLastname', parameters.radioLastname, 'radioLastname2', parameters.radioLastname2],
['employee_id', parameters.employee_id, 'radioEmployee_id', parameters.radioEmployee_id, 'radioEmployee_id2', parameters.radioEmployee_id2],
['status', parameters.status, 'radioStatus', parameters.radioStatus, 'radioStatus2', parameters.radioStatus2]
];
var selector;
selector = 1;
var list_1 = server_side(selector, keys, length, start, order, column_order, result, order_2);//server_side(selector, keys, length, start, order, column_order, order_by, data);
selector = 2;
var list_2 = server_side(selector, keys, length, start, order, column_order, result, order_2);
var abc = [{list_1: list_1, list_2: list_2}];
return abc;
}
function server_side(selector, keys, length, start, order, column_order, data, order_2){
for (var i=0; i<keys.length; i++) { //0-4
var k = i+1;
if(keys[i][1] != ""){ //verifica si el input esta vacio
if(keys[i][3] === "C"){
if(keys[i][5] === "A"){
data = search_contains_matchCase(k, keys[i][1], data);
}else{
data = search_contains(k, keys[i][1], data);
}
}else if(keys[i][3] === "S"){
if(keys[i][5] === "A"){
data = search_start_matchCase(k, keys[i][1], data);
}else{
data = search_contains(k, keys[i][1], data);
}
}else if(keys[i][3] === "F"){
if(keys[i][5] === "A"){
data = search_finish_matchCase(k, keys[i][1], data);
}else{
data = search_finish(k, keys[i][1], data);
}
}else if(keys[i][3] === "W"){
if(keys[i][5] === "A"){
data = search_wholeword_matchCase(k, keys[i][1], data);
}else{
data = search_wholeword(k, keys[i][1], data);
}
}
}else{
data = data;
}
}
var data_reorder = reorder_and_length(data, column_order, order, selector, start, length, order_2);
return data_reorder;
}
function reorder_and_length(data, column_order, order, selector, start, length, order_2){
var data1;
var column;
var dir;
//var start = (draw-1)*length;
var finish = parseInt(start)+parseInt(length); //0+5 5+5 10+5 0-5 5-10 10-15
/*
primero codigo para orderar por columna
se utiliza .toString() para evitar el error de cuando se compara un numero con un caracter
*/
if(order != 0){
column = order; //columna a ordenar, int
dir = order_2;//'desc'; // asc o desc
if(dir === "desc"){
data.sort(function (a, b) {
if (a[column].toString() === b[column].toString()) {
return 0;
}
else {
return (a[column].toString() < b[column].toString()) ? 1 : -1;
}
});
}else{
data.sort(function (a, b) {
if (a[column].toString() === b[column].toString()) {
return 0;
}
else {
return (a[column].toString() < b[column].toString()) ? -1 : 1;
}
});
}
}else{
column = order; //columna a ordenar, int
dir = order_2; // asc o desc
data.sort(function (a, b) {
if (a[column] === b[column]) {
return 0;
}
else {
return (a[column] < b[column]) ? 1 : -1;
}
});
}
/*
segundo el codigo para limitarlo como un switch del selector
*/
switch(selector){
case 1:
data1 = data;
return data1;
break;
case 2:
if(length>0){
//aplica el limite
data1 = data.slice(start,finish);
return data1;
}else{
//devuelve toda la data
data1 = data;
return data1;
}
break;
default:
if(length>0){
//aplica el limite
data1 = data.slice(start,finish);
return data1;
}else{
//devuelve toda la data
data1 = data;
return data1;
}
break;
}
return data;
}
function count_total(){
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
var lastRow = sheet.getLastRow();
var count = lastRow - 1;
return count;
}
function search_start(column, filter, data) {
var array = [];
//Logger.log(filter.toLowerCase());
for(var i=0; i<data.length; i++){
Logger.log(data[i][column].toString().toLowerCase().substring(0, filter.length));
if(data[i][column].toString().toLowerCase().substring(0, filter.length) == filter.toLowerCase()){
array.push(data[i]);
}
}
return array;
Logger.log(array);
}
function search_start_matchCase(column, filter, data) {
var array = [];
//Logger.log(filter);
for(var i=0; i<data.length; i++){
Logger.log(data[i][column].toString().substring(0, filter.length));
if(data[i][column].toString().substring(0, filter.length) == filter){
array.push(data[i]);
}
}
return array;
Logger.log(array);
}
function search_finish(column, filter, data) {
var array = [];
//Logger.log(filter.toLowerCase());
for(var i=0; i<data.length; i++){
Logger.log(data[i][column].toString().toLowerCase().substring(data[i][column].length - filter.length, data[i][column].length));
if(data[i][column].toString().toLowerCase().substring(data[i][column].length - filter.length, data[i][column].length) == filter.toLowerCase()){
array.push(data[i]);
}
}
return array;
Logger.log(array);
}
function search_finish_matchCase(column, filter, data) {
var array = [];
//Logger.log(filter.toLowerCase());
for(var i=0; i<data.length; i++){
Logger.log(data[i][column].toString().substring(data[i][column].length - filter.length, data[i][column].length));
if(data[i][column].toString().substring(data[i][column].length - filter.length, data[i][column].length) == filter){
array.push(data[i]);
}
}
return array;
Logger.log(array);
}
function search_contains(column, filter, data) {
var array = [];
//Logger.log(filter.toLowerCase());
for(var i=0; i<data.length; i++){
Logger.log(data[i][column].toString().toLowerCase().indexOf(filter.toLowerCase()));
if(data[i][column].toString().toLowerCase().indexOf(filter.toLowerCase()) >= 0){
array.push(data[i]);
}
}
return array;
Logger.log(array);
}
function search_contains_matchCase(column, filter, data) {
var array = [];
//Logger.log(filter.toLowerCase());
for(var i=0; i<data.length; i++){
Logger.log(data[i][column].toString().indexOf(filter));
if(data[i][column].toString().indexOf(filter.toLowerCase()) >= 0){
array.push(data[i]);
}
}
return array;
Logger.log(array);
}
function search_wholeword(column, filter, data) {
var array = [];
//Logger.log(filter.toLowerCase());
for(var i=0; i<data.length; i++){
//Logger.log(data[i][column].toLowerCase().indexOf(filter.toLowerCase()));
if(data[i][column].toString().toLowerCase() === filter.toLowerCase()){
array.push(data[i]);
}
}
Logger.log(array);
return array;
}
function search_wholeword_matchCase(column, filter, data) {
var array = [];
//Logger.log(filter.toLowerCase());
for(var i=0; i<data.length; i++){
//Logger.log(data[i][column].indexOf(filter));
if(data[i][column] === filter){
array.push(data[i]);
}
}
Logger.log(array);
return array;
}
Pay special attention in how we set the spreadsheet in order to acquire the POST data. We use the doPost function with parameter “e”.
For example, we can retrieve a parameter with: var start = parameters.start;
function doPost(e) {
var result = validation(e);
var total = count_total();
var filtered = (result[0].list_1).length; // deberia ser el conteo de los filtrados por longitud de mostrados
return ContentService
.createTextOutput(JSON.stringify({"draw":null, "recordsTotal":total,"recordsFiltered":filtered, "data":result[0].list_2}))
.setMimeType(ContentService.MimeType.JSON);
}
You need to publish/deploy the code as a web app:
- Execute the app as “me”
- Who has access to the App: Anyone, even anonymous
PART 2: Web App Script (Standalone)
FILE: index.html (with DataTables plugin)
<html>
<head>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/js/bootstrap.min.js"></script>
<script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css">
<style>
.accordion-toggle:after {
/* symbol for "opening" panels */
font-family: 'Glyphicons Halflings'; /* essential for enabling glyphicon */
content: "\e114"; /* adjust as needed, taken from bootstrap.css */
float: right; /* adjust as needed */
color: grey; /* adjust as needed */
}
.accordion-toggle.collapsed:after {
/* symbol for "collapsed" panels */
content: "\e080"; /* adjust as needed, taken from bootstrap.css */
}
</style>
</head>
<body>
<div class="container" id="">
<br>
<div class=" pull-right">
<a id="B100_list_filter" href="javascript:void(0)" class="btn btn-default" value="off">Filter Off</a>
</div>
<br><br>
<div class="panel panel-default" id="advance_filter" style="display:none;" >
<div class="panel-heading">
<h3 class="panel-title accordion-toggle" data-toggle="collapse" href="#collapse1">Advanced Filter : </h3>
</div>
<div class="panel-body panel-collapse collapse in" id="collapse1">
<form id="form-filter" class="form-horizontal">
<div class="form-group">
<div class="col-md-8 col-sm-8 col-xs-12">
<label for="email" class="col-sm-3 col-xs-3 control-label">Email</label>
<div class="col-sm-9 col-xs-9">
<input type="text" class="form-control" id="user_E800_email">
</div>
</div>
<div class="col-md-4 col-sm-4 col-xs-12">
<div class="input-group radioBtns">
<div id="radioBtn" class="btn-group">
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E801_radioEmail" title="Start with.." data-title="S">S</a>
<a class="btn btn-primary btn-sm active" data-toggle="user_E801_radioEmail" title="Contains.." data-title="C">C</a>
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E801_radioEmail" title="Finish with.." data-title="F">F</a>
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E801_radioEmail" title="Whole word" data-title="W">W</a>
</div>
<input type="hidden" name="user_E801_radioEmail" id="user_E801_radioEmail" value="C">
<div id="radioBtn2" class="btn-group">
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E802_radioEmail2" title="Match case" data-title="A" value="a">Aa</a>
</div>
<input type="hidden" name="user_E802_radioEmail2" id="user_E802_radioEmail2" value="a">
</div>
</div>
</div>
<div class="form-group">
<div class="col-md-8 col-sm-8 col-xs-12">
<label for="first_name" class="col-sm-3 col-xs-3 control-label">First name</label>
<div class="col-sm-9 col-xs-9">
<input type="text" class="form-control" id="user_E803_first_name">
</div>
</div>
<div class="col-md-4 col-sm-4 col-xs-12">
<div class="input-group radioBtns">
<div id="radioBtn" class="btn-group">
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E804_radioFirstname" title="Start with.." data-title="S">S</a>
<a class="btn btn-primary btn-sm active" data-toggle="user_E804_radioFirstname" title="Contains.." data-title="C">C</a>
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E804_radioFirstname" title="Finish with.." data-title="F">F</a>
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E804_radioFirstname" title="Whole word" data-title="W">W</a>
</div>
<input type="hidden" name="user_E804_radioFirstname" id="user_E804_radioFirstname" value="C">
<div id="radioBtn2" class="btn-group">
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E805_radioFirstname2" title="Match case" data-title="A" value="A">Aa</a>
</div>
<input type="hidden" name="user_E805_radioFirstname2" id="user_E805_radioFirstname2" value="a">
</div>
</div>
</div>
<div class="form-group">
<div class="col-md-8 col-sm-8 col-xs-12">
<label for="last_name" class="col-sm-3 col-xs-3 control-label">Last name</label>
<div class="col-sm-9 col-xs-9">
<input type="text" class="form-control" id="user_E806_last_name">
</div>
</div>
<div class="col-md-4 col-sm-4 col-xs-12">
<div class="input-group radioBtns">
<div id="radioBtn" class="btn-group">
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E807_radioLastname" title="Start with.." data-title="S">S</a>
<a class="btn btn-primary btn-sm active" data-toggle="user_E807_radioLastname" title="Contains.." data-title="C">C</a>
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E807_radioLastname" title="Finish with.." data-title="F">F</a>
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E807_radioLastname" title="Whole word" data-title="W">W</a>
</div>
<input type="hidden" name="user_E807_radioLastname" id="user_E807_radioLastname" value="C">
<div id="radioBtn2" class="btn-group">
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E808_radioLastname2" title="Match case" data-title="A" value="A">Aa</a>
</div>
<input type="hidden" name="user_E808_radioLastname2" id="user_E808_radioLastname2" value="a">
</div>
</div>
</div>
<div class="form-group">
<div class="col-md-8 col-sm-8 col-xs-12">
<label for="employee_id" class="col-sm-3 col-xs-3 control-label">Employee ID</label>
<div class="col-sm-9 col-xs-9">
<input type="text" class="form-control" id="user_E809_employee_id">
</div>
</div>
<div class="col-md-4 col-sm-4 col-xs-12">
<div class="input-group radioBtns">
<div id="radioBtn" class="btn-group">
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E810_radioEmployee_id" title="Start with.." data-title="S">S</a>
<a class="btn btn-primary btn-sm active" data-toggle="user_E810_radioEmployee_id" title="Contains.." data-title="C">C</a>
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E810_radioEmployee_id" title="Finish with.." data-title="F">F</a>
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E810_radioEmployee_id" title="Whole word" data-title="W">W</a>
</div>
<input type="hidden" name="user_E810_radioEmployee_id" id="user_E810_radioEmployee_id" value="C">
<div id="radioBtn2" class="btn-group">
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E811_radioEmployee_id2" title="Match case" data-title="A" value="A">Aa</a>
</div>
<input type="hidden" name="user_E811_radioEmployee_id2" id="user_E811_radioEmployee_id2" value="a">
</div>
</div>
</div>
<div class="form-group">
<div class="col-md-8 col-sm-8 col-xs-12">
<label for="status" class="col-sm-3 col-xs-3 control-label">Status</label>
<div class="col-sm-9 col-xs-9">
<input type="text" class="form-control" id="user_E812_status">
</div>
</div>
<div class="col-md-4 col-sm-4 col-xs-12">
<div class="input-group radioBtns">
<div id="radioBtn" class="btn-group">
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E813_radioStatus" title="Start with.." data-title="S">S</a>
<a class="btn btn-primary btn-sm active" data-toggle="user_E813_radioStatus" title="Contains.." data-title="C">C</a>
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E813_radioStatus" title="Finish with.." data-title="F">F</a>
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E813_radioStatus" title="Whole word" data-title="W">W</a>
</div>
<input type="hidden" name="user_E813_radioStatus" id="user_E813_radioStatus" value="C">
<div id="radioBtn2" class="btn-group">
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E814_radioStatus2" title="Match case" data-title="A" value="A">Aa</a>
</div>
<input type="hidden" name="user_E814_radioStatus2" id="user_E814_radioStatus2" value="a">
</div>
</div>
</div>
<div class="form-group">
<label for="" class="col-sm-2 control-label"></label>
<div class="col-sm-12" style="text-align:center;">
<button type="button" id="B114_btn_filter" class="btn btn-primary btn-sm">Filter</button>
<button type="button" id="B115_btn_reset" class="btn btn-default btn-sm">Reset</button>
</div>
</div>
</form>
</div>
</div>
<form id="F110_gen_form" name="myForm" method="post" action="" onSubmit="" enctype = text/plain >
<div class="table-responsive">
<table
id="table1"
data-order="[]"
data-columnDefs='[{"targets":[0],"orderable":false}]'
data-columnFilter='{"aoColumns": [ null, {"type": "text"}, {"type": "text"} ]}'
data-columnFilter-select2="true"
class="table table-striped table-sorted table-hover table-bordered table-columnfilter datatable">
<thead class="table-head-alt">
<tr>
<th id="filterCheck">
<label class="control1 control-checkbox1">
<input id="C111_gen_master_checkbox" type="checkbox" name="C111_gen_master_checkbox" class="checkbox"/>
<div class="control_indicator1"></div>
</label>
</th>
<th class="col-lg-2 col-md-2 col-sm-1 col-xs-1">Email</th>
<th>First Name</th>
<th>Last Name</th>
<th>Employee ID</th>
<th>Status</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</form>
</div>
</body>
<script>
$(document).ready(function() {
var oTable;
//if ($('.table-sorted').length) {
/* DATATABLE-------------------------------------------*/
//$('#table1').DataTable();
oTable = $('#table1').DataTable({
// "bAutoWidth": false,
// "dom": '<Bpl><rt><ip>',
/* To change the appearance of the NAV BUTTONs */
/*"pagingType": "full_numbers",*/
/*"pagingType": "full",*/
// "pagingType": "input",
"bFilter": false,
"processing": true, //Feature control the processing indicator.
"serverSide": true, //Feature control DataTables' server-side processing mode.
/* To order the list taking into account the first column*/
"order": [[ 1, "desc" ]],
/* To disable the initial sortering (the table is not sorted initially) */
//"order": [],
/*Another way: "aaSorting": [ ],*/
// Load data for the table's content from an Ajax source
"ajax": {
"url": "https://script.google.com/macros/s/AKfycbxGxm61aU60eM2xuWy403wE4yq7ZFl3n5Rg0ZQKHK7Zqzfm-cQ/exec",
"type": "POST",
"data": function ( data ) {
data.email = $('#user_E800_email').val();
data.first_name = $('#user_E803_first_name').val();
data.last_name = $('#user_E806_last_name').val();
data.employee_id = $('#user_E809_employee_id').val();
data.status = $('#user_E812_status').val();
data.radioEmail = $('#user_E801_radioEmail').val();
data.radioFirstname = $('#user_E804_radioFirstname').val();
data.radioLastname = $('#user_E807_radioLastname').val();
data.radioEmployee_id = $('#user_E810_radioEmployee_id').val();
data.radioStatus = $('#user_E813_radioStatus').val();
data.radioEmail2 = $('#user_E802_radioEmail2').val();
data.radioFirstname2 = $('#user_E805_radioFirstname2').val();
data.radioLastname2 = $('#user_E808_radioLastname2').val();
data.radioEmployee_id2 = $('#user_E811_radioEmployee_id2').val();
data.radioStatus2 = $('#user_E814_radioStatus2').val();
}
},
/* Disable column SORT (No sort icon on the affected column)*/
"aoColumnDefs": [
{
'bSortable': false,
'aTargets': [ 0 ],
"render": function ( data, type, row, meta ) {
return '<label class="control control-checkbox"> <input id="'+data+'" type="checkbox" name="C112_gen_checkbox" value="'+data+'" class="checkbox"/><div class="control_indicator"></div></label>';
}
}
],
/* To set the initial value to 50 */
"iDisplayLength": 5,
/* To change the values displayed in the DIAPLAYLENGHT*/
"oLanguage": {
"sLengthMenu": 'Display <select>'+
'<option value="5">5</option>'+
'<option value="10">10</option>'+
'<option value="25">25</option>'+
'<option value="50">50</option>'+
'<option value="100">100</option>'+
'<option value="500">500</option>'+
'<option value="1000">1000</option>'+
'<option value="-1">All</option>'+
'</select> records'
},
// buttons: []
/*
fixedHeader: {
header: true,
footer: true
}
*/
});
//------------------------------------------------
$("#B100_list_filter").click(function () {
if ($("#B100_list_filter").attr("value") === "off") {
$("#B100_list_filter").attr("value", "on");
$("#B100_list_filter").html("Filter On");
$('#B100_list_filter').removeClass('btn btn-default');
$('#B100_list_filter').addClass('btn btn-primary');
$("#B100_list_filter").blur();
$("#advance_filter").show();
} else {
//$('.table-sorted input[type="text"]').val('');
//oTable.columns().search("").draw();
$("#B100_list_filter").attr("value", "off");
$("#B100_list_filter").html("Filter Off");
$('#B100_list_filter').removeClass('btn btn-primary');
$('#B100_list_filter').addClass('btn btn-default');
$("#B100_list_filter").blur();
$("#advance_filter").hide();
$('#form-filter')[0].reset();
oTable.ajax.reload();
}
});
$('#B114_btn_filter').click(function(){ //button filter event click
$('#B114_btn_filter').blur();
oTable.ajax.reload(); //just reload table
});
$('#B115_btn_reset').click(function(){ //button reset event click
$('#B115_btn_reset').blur();
$('#form-filter')[0].reset();
oTable.ajax.reload(); //just reload table
});
});
$('#radioBtn a').tooltip();
$('#radioBtn a').on('click', function(){
var sel = $(this).data('title');
console.log(sel);
var tog = $(this).data('toggle');
console.log(tog);
$('#'+tog).prop('value', sel);
$('a[data-toggle="'+tog+'"]').not('[data-title="'+sel+'"]').removeClass('active').addClass('notActive');
$('a[data-toggle="'+tog+'"][data-title="'+sel+'"]').removeClass('notActive').addClass('active');
});
$('#radioBtn2 a').tooltip();
$('#radioBtn2 a').on('click', function(){
var sel = $(this).data('title');
var tog = $(this).data('toggle');
if($('#'+tog).attr("value")==="a"){
$('#'+tog).attr("value", "A");
$('a[data-toggle="'+tog+'"][data-title="'+sel+'"]').removeClass('notActive').addClass('active');
}else{
$('#'+tog).attr("value", "a");
$('a[data-toggle="'+tog+'"][data-title="'+sel+'"]').removeClass('active').addClass('notActive');
}
})
</script>
</html>
The filter options for each input are: 
“S”= Starts with …
“C”= Contains …
“F”= Finish wih …
“W”=Whole word …
“Aa”= Match case …
FILE: code.gs
function doGet(e) {
Logger.log(Utilities.jsonStringify(e));
if (!e.parameter.page) {
// When no specific page requested, return "home page"
return HtmlService.createTemplateFromFile('index').evaluate();
}
// else, use page parameter to pick an html file from the script
return HtmlService.createTemplateFromFile(e.parameter['page']).evaluate();
}
You need to publish/deploy the code as a web app:
- Execute the app as “user accessing the web app”
- Who has access to the App: Anyone
by: Juan Pablo Donayre
I go to link of you
“https://script.google.com/macros/s/AKfycbxIAiVZiZQmbsjXCG1KYD4DFcpgN0lwaebbqs66J_0rY3eTbKCc/exec”
but it no data ?? I want to study it
Hi,
Try it again. Now its working.