Làm thế nào kéo dữ liệu từ Google Sheets sang HTML

Phần này các bạn xem video hướng dẫn, các link bài cần thiết mình đều để bên dưới bài viết.

File Code.gs

/*
# CREATED BY: LUUHOANGPHONG
# URL: https://luuhoangphong.com/
*/

function doGet() {
return HtmlService.createTemplateFromFile('Index').evaluate();
}

//GET DATA FROM GOOGLE SHEET AND RETURN AS AN ARRAY
function getData() {
var spreadSheetId = "15NSe89LAt-2L9SqJhdMiV13cmHZy3t7In4vMuPIPZ6E"; //CHANGE
var dataRange = "Data!A2:F"; //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

index.html

<!DOCTYPE html>
<html>

<head>
<base target="_top">
<!--INCLUDE REQUIRED EXTERNAL JAVASCRIPT AND CSS LIBRARIES-->
<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" data-wp-preserve="%3Cscript%20src%3D%22https%3A%2F%2Fcode.jquery.com%2Fjquery-3.5.1.js%22%3E%3C%2Fscript%3E" data-mce-resize="false" data-mce-placeholder="1" class="mce-object" width="20" height="20" alt="&lt;script&gt;" title="&lt;script&gt;" />
<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" data-wp-preserve="%3Cscript%20src%3D%22https%3A%2F%2Fcdn.datatables.net%2F1.10.23%2Fjs%2Fjquery.dataTables.min.js%22%3E%3C%2Fscript%3E" data-mce-resize="false" data-mce-placeholder="1" class="mce-object" width="20" height="20" alt="&lt;script&gt;" title="&lt;script&gt;" />
<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" data-wp-preserve="%3Cscript%20src%3D%22https%3A%2F%2Fcdn.datatables.net%2F1.10.23%2Fjs%2FdataTables.bootstrap4.min.js%22%3E%3C%2Fscript%3E" data-mce-resize="false" data-mce-placeholder="1" class="mce-object" width="20" height="20" alt="&lt;script&gt;" title="&lt;script&gt;" />
<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">
<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 -->
</table>
</div>
</div>
</body>

</html>

<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":"Rating"},
{"title":"Reviews"},
{"title":"Book title"},
{"title":"Number of Pages"},
{"title":"Type"},
{"title":"Price"}
]
});
});
}
</script>

Video hướng dẫn

 

  • Liên kết tới Google Trang tính: https://docs.google.com/spreadsheets/d/15NSe89LAt-2L9SqJhdMiV13cmHZy3t7In4vMuPIPZ6E/edit#gid=0
  • Liên kết tới Apps Script: https://script.google.com/home/projects/1oatsrgLuthHrOg6vLXua75M0Rm1K6zX3IjcdHamKe-lM6e6hTrHhERwF/edit
  • Kết quả: https://script.google.com/macros/s/AKfycbz08XD88vE-S0MEClkwlXQPASfmHBoOtmpqq_qTiJdgYSBXIOXv8o8YwDBPMImMDO2k/exec