DataTable using AppsScript and Google Sheet
In today's digital age, web development has become an integral part of every business. Web developers use different tools and technologies to develop websites and web applications that meet the needs of the users. One of the most popular tools used for web development is DataTables.
DataTables is a powerful and flexible jQuery plugin that enhances the functionality of HTML tables. With DataTables, you can easily add sorting, searching, pagination, and other advanced features to your tables. In this blog post, we will discuss how to use DataTables with Google Apps Script to create a dynamic table.
The code snippet provided above is a basic HTML file that includes the necessary resources for using DataTables. The first section of the code defines the document type, followed by the HTML tag. The "title" tag specifies the title of the page, while the "meta" tag sets the width and initial scale of the page. The "link" tags define the style sheets used by the page, including the W3.CSS and Font Awesome CSS. The "script" tags include the jQuery and DataTables libraries.
Next, we define the table structure using the "table" tag, along with the table headers and the table body. The table headers specify the column names, while the table body is currently empty.
In the "script" tag, we call the "google.script.run.withSuccessHandler(showData).getData();" function to retrieve data from a server-side Google Apps Script. The "showData" function is then called with the retrieved data as its parameter. Within the "showData" function, we use jQuery to define a DataTable and populate it with the data retrieved from the server.
Finally, we use jQuery to add a keyup event listener to the input element. This event listener filters the table rows based on the user input.
The code snippet provided above contains two functions, namely doGet() and getData(). Let's discuss each of these functions in detail.
The doGet() function is a special function in Google Apps Script that gets called when a user visits a web app URL. This function returns an HTML template file using HtmlService.createTemplateFromFile('index'). The evaluate() method is then used to evaluate the template and return an HTML output. Additionally, this function sets the title of the page to "title" using .setTitle('title') and adds a meta tag to the page using .addMetaTag('viewport', 'width=device-width , initial-scale=1'). Finally, the setXFrameOptionsMode() method is used to allow embedding the web app in an iframe using HtmlService.XFrameOptionsMode.ALLOWALL.
The getData() function retrieves data from a Google Sheets spreadsheet named "Data". The getActiveSpreadsheet() method is used to get the active spreadsheet, and the getSheetByName() method is used to get the sheet with the name "Data". The getDataRange() method is then used to get the range of data in the sheet, and the getDisplayValues() method is used to get the displayed values in the range. Finally, the function returns the values as an array.
In summary, these functions work together to create a web app that displays data from a Google Sheets spreadsheet. The doGet() function returns an HTML template that includes a table, while the getData() function retrieves the data from the spreadsheet. This data is then displayed in the table using DataTables, as shown in the previous code snippet. By combining these functions with DataTables, you can create a dynamic and interactive web app that allows users to view and search through data in a Google Sheets spreadsheet.
index.html
<html>
<title>W3.CSS</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.13.4/css/jquery.dataTables.css" />
<script src="https://cdn.datatables.net/1.13.4/js/jquery.dataTables.js"></script>
<body>
<table id="myTable" class="display"width="90%">
<thead>
<tr>
<th>TimeStamp</th>
<th>Receipt No</th>
<th>Student Id</th>
<th>Student name</th>
<th>Class</th>
<th>Book name</th>
<th>Quenity</th>
<th>Price</th>
</tr>
</thead>
<tbody>
<tr>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
</tbody>
</table>
<script>
google.script.run.withSuccessHandler(showData).getData();
function showData(data){
$(document).ready(function () {
// DataTable
var table = $('#myTable').DataTable({
data, });
$("input").on("keyup", function() {
var value = $(this).val().toLowerCase();
$("#myTable tr").filter(function() {
$(this).toggle($(this).text().toLowerCase().indexOf(value) > -1)
});
});
});}
</script>
</body>
</html>
Code.gs
var h = HtmlService.createTemplateFromFile('index');
return h.evaluate()
.setTitle('title')
.addMetaTag('viewport', 'width=device-width , initial-scale=1')
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)
}
function getData(){
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
var values = ss.getDataRange().getDisplayValues();
return values;
}