Created
September 16, 2021 10:23
-
-
Save Niddeaw/cbc87657412bd6546c509b6e3eb17f63 to your computer and use it in GitHub Desktop.
DataTable + Google Sheet
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** DataTable + Google Sheet | |
* โดย นายจิรศักดิ์ จิรสาโรช : 080-6393969 | |
*/ | |
function doGet(e) { | |
if (!e.parameter.page) { | |
var htmlOutput = HtmlService.createTemplateFromFile("index"); | |
return htmlOutput.evaluate().setTitle("WebApp-DataTable").setSandboxMode(HtmlService.SandboxMode.IFRAME).addMetaTag("viewport", "width=device-width, initial-scale=1"); | |
} | |
return HtmlService.createTemplateFromFile(e.parameter["page"]).evaluate(); | |
} | |
/** เรียก URL */ | |
function getUrl() { | |
var url = ScriptApp.getService().getUrl(); | |
return url; | |
} | |
/** ดึงไฟล์ */ | |
function include(filename) { | |
return HtmlService.createHtmlOutputFromFile(filename).getContent(); | |
} | |
/** ดึงข้อมูลให้ไปแสดง DataTable */ | |
function getData() { | |
var sheetID = "1pwbN4I-9PdhPaRGsIdgMy9HWjdu3Kf3ZPgV4O1crkZI"; // ID ชีต | |
// var dataRange = "ชีต1!A2:Z"; // ชื่อชีตและช่วงของข้อมูล | |
// var range = Sheets.Spreadsheets.Values.get(sheetID, dataRange); | |
// var values = range.values; | |
// values = values.map(function (row) { | |
// return row.concat([0]); | |
// }); | |
// Logger.log(values); | |
// return values; | |
var ss = SpreadsheetApp.openById(sheetID) | |
var sheet = ss.getSheets()[0] | |
var range = sheet.getDataRange() | |
var values = range.getDisplayValues() | |
Logger.log(values) | |
return values | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<!-- CSS DataTable --> | |
<link href="https://cdn.datatables.net/1.10.25/css/jquery.dataTables.min.css" rel="stylesheet" type="text/css"> | |
<link href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/5.0.1/css/bootstrap.min.css" rel="stylesheet" type="text/css"> | |
<link href="https://cdn.datatables.net/1.10.25/css/dataTables.bootstrap5.min.css" rel="stylesheet" type="text/css"> | |
<link href="https://cdn.datatables.net/responsive/2.2.9/css/responsive.bootstrap5.min.css" rel="stylesheet" type="text/css"> | |
<link href="https://cdn.datatables.net/searchbuilder/1.1.0/css/searchBuilder.dataTables.min.css" rel="stylesheet" type="text/css"> | |
<link href="https://cdn.datatables.net/datetime/1.1.0/css/dataTables.dateTime.min.css" rel="stylesheet" type="text/css"> | |
<!-- CSS Style --> | |
<style> | |
.sarabun { | |
font-family: 'Sarabun', sans-serif; | |
} | |
@font-face { | |
font-family: 'Sarabun'; | |
} | |
body { font-family: 'Sarabun' !important; } | |
</style> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<!DOCTYPE html> | |
<html> | |
<head> | |
<base target="_top"> | |
<title>WebApp-DataTable</title> | |
<?!= include('css'); ?> | |
</head> | |
<body> | |
<div class="container"> | |
<table id="test" class="table table-striped nowrap" style="width:100%"> | |
<!-- กำหนด table id ให้ตรงกับ js --> | |
</table> | |
</div> | |
<?!= include('js'); ?> | |
</body> | |
</html> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<!-- JavaScript DataTable --> | |
<script src="https://code.jquery.com/jquery-3.5.1.js"></script> | |
<script src="https://cdn.datatables.net/1.10.25/js/jquery.dataTables.min.js"></script> | |
<script src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/5.0.1/js/bootstrap.bundle.min.js"></script> | |
<script src="https://cdn.datatables.net/1.10.25/js/dataTables.bootstrap5.min.js"></script> | |
<!-- DataTable Responsive --> | |
<script src="https://cdn.datatables.net/responsive/2.2.9/js/dataTables.responsive.min.js"></script> | |
<script src="https://cdn.datatables.net/responsive/2.2.9/js/responsive.bootstrap5.min.js"></script> | |
<!-- DataTable Button --> | |
<script src="https://cdn.datatables.net/buttons/1.7.1/js/dataTables.buttons.min.js"></script> | |
<script src="https://cdn.datatables.net/buttons/1.7.1/js/buttons.bootstrap5.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/1.7.1/js/buttons.html5.min.js"></script> | |
<script src="https://cdn.datatables.net/buttons/1.7.1/js/buttons.print.min.js"></script> | |
<script src="https://cdn.datatables.net/buttons/1.7.1/js/buttons.colVis.min.js"></script> | |
<script> | |
google.script.run.withSuccessHandler(showData).getData(); | |
function showData(dataArray){ | |
$(document).ready(function(){ | |
$('#test').DataTable({ // กำหนดไอดีให้ตรงกับ table ใน html | |
// ข้อมูล | |
data: dataArray, | |
// หัวคอลัมภ์ | |
columns: [ | |
{"title":"ชั้น"}, | |
{"title":"ห้อง"}, | |
{"title":"เลขประจำตัว"}, | |
{"title":"คำนำหน้า"}, | |
{"title":"ชื่อ"}, | |
{"title":"นามสกุล"}, | |
//{"title":"URL Sharing"}, | |
{ | |
title: "URL Sharing", | |
render: function (data, type, row, meta) { | |
if (type === "display") { | |
data = '<a href="' + data + '" target="_blank">ลิงค์ภาพต้นฉบับ</a>'; // ลิงค์จากข้อความ | |
} | |
return data; | |
}, | |
}, | |
{"title":"ID Image"}, | |
//{"title":"URL UC"}, | |
{ | |
title: "URL UC", | |
render: function (data, type, row, meta) { | |
return '<img src="' + data + '" class="avatar" width="50" height="50"/>'; | |
}, | |
}, | |
//{"title":"URL UC + Link"}, | |
{ | |
title: "URL UC + Link", | |
render: function (data, type, row, meta) { | |
return '<a href="' + data + '" target="_blank"><img src="' + data + '" class="avatar" width="50" height="50"/></a>'; | |
}, | |
}, | |
], | |
// ภาษาไทย | |
language: { url: "//cdn.datatables.net/plug-ins/1.10.24/i18n/Thai.json" }, | |
// แสดงเลขหน้าด้านท้ายตารางทั้งหมด แสดงปุ่มหน้าแรก | |
sPaginationType: "full_numbers", | |
// กำหนดเมนูแสดงจำนวนแถว | |
lengthMenu: [ | |
[ 5, 10, 25, 50, -1 ], | |
[ '5 แถว', '10 แถว', '25 แถว', '50 แถว', 'ทั้งหมด' ] | |
], | |
// กำหนดลักษณะพิเศษเฉพาะสำหรับคอลัมน์ตารางที่ต้องการ | |
/* | |
columnDefs: [ | |
{ | |
targets: [7,8], // Index ที่ | |
visible: false, //ซ่อนคอลัมภ์ที่ต้องการ | |
searchable: false, | |
}, | |
], | |
*/ | |
// การจัดเรียงลำดับของข้อมูลในคอลัมภ์ asc = น้อยไปหามาก desc = มากไปหาน้อย | |
order: [[1, "asc"], [2, "asc"], ], | |
responsive: true, | |
// แสดงบนหัวตาราง | |
dom: 'Bfrtip', // แสดงปุ่มบนหัวตาราง เพิ่ม Buttons และตั้งชื่อปุ่มเอง | |
buttons: [ | |
{extend: 'copy',text: 'คัดลอก',}, | |
{extend: 'csv',text: 'CSV',charset: 'utf-8',filename: 'Search Report',bom: 'true',}, // เปิดจาก Excel ให้นำเข้าข้อมูล csv แบบ UTF-8 | |
{extend: 'excel',text: 'Excel'}, | |
{extend: 'pdf',text: 'PDF',exportOptions: {columns: ':visible'}, // PDF ฟอนต์ยังไม่สนับสนุนภาษาไทย | |
customize: function (doc) { | |
doc.defaultStyle.fontSize = 10;}}, | |
{extend: 'print',text: 'พิมพ์'}, | |
], | |
// การส่งออกของ button จะสร้างไฟล์ตามชื่อไตเติ้ลของเว็บในไฟล์ index.html แท็ก <title>WebApp-DataTable</title> | |
}); | |
}); | |
} | |
</script> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment