11-4
顯示工作表資料
程式碼.gs
/*=====================================
Get
=====================================*/
function doGet(e) {
console.log(e);
let file = "index";
let title = "訂便當";
let argsObject = {
title: title
};
return render(file, argsObject, title);
}
/*======================================
渲染網頁
file：樣版檔案
argsObject：物件參數
title：有值為主樣版，無值為子樣版
======================================*/
function render(file, argsObject, title = '') {
let tmp = HtmlService.createTemplateFromFile(file);
for (let i in argsObject) {
tmp[i] = argsObject[i];
}
if (title) {//主樣板
return tmp.evaluate().setTitle(title).setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL).addMetaTag('viewport', 'width=device-width, initial-scale=1');
} else {//子樣板
return tmp.evaluate().getContent();
}
}
/*======================================
請按照工作表欄位順序 設定
======================================*/
function get_stru() {
let stru = [
{
title: '時間戳記', //欄名
visible: true, //是否顯示 true|false
align: '', //對齊 left|center|right
sortable: true, //是否排序 true|false
width: 200 //寬度(px)
},
{
title: '姓名',
visible: true,
align: '',
sortable: true,
width: ''
},
{
title: '餐點',
visible: true,
align: '',
sortable: true,
width: ''
},
{
title: '單價',
visible: true,
align: 'right',
sortable: true,
width: ''
},
{
title: '套餐 +25元',
visible: true,
align: 'center',
sortable: true,
width: ''
},
{
title: '糖',
visible: true,
align: 'center',
sortable: true,
width: ''
},
{
title: '冰',
visible: true,
align: 'center',
sortable: true,
width: ''
},
{
title: '小計',
visible: true,
align: 'right',
sortable: true,
width: '80'
}
]
return stru;
}
/*======================================
請自行更換 工作表 名稱
======================================*/
function getData() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
// 請自行更換 工作表 名稱
let ws = ss.getSheetByName("表單回應 1");
let dataArray = {};
let stru = get_stru();
let data = ws.getRange(2, 1, ws.getLastRow() - 1, ws.getLastColumn()).getValues();
// 日期格式化 + 個資顯示
data.forEach((item, index) => {
data[index][0] = Utilities.formatDate(item[0], "GMT+08:00", "yyyy/MM/dd HH:mm:ss");//格式化日期 2020/06/13/ 23:06
data[index][1] = deIdentification(item[1]); //個資顯示
});
// 撈出當日的資料
let currentDate = Utilities.formatDate(new Date(), "GMT+08:00", "yyyy/MM/dd");//格式化日期 今天的日期
let todayData = data.filter(item => {
let orderDate = item[0].split(' ')[0]; // 取得訂單的日期部分
return orderDate === currentDate;
});
return { stru: stru, data: todayData };
}
/*============================================
去識別化(個資顯示)
============================================*/
function deIdentification(str) {
str = str.toString();
const showLen = Math.round(str.length / 2); // 顯示幾個
const markLen = str.length - showLen; // 要隱藏幾個
const showStart = Math.round((str.length - showLen) / 2); // 從哪開始隱
return str.replace(str.substr(showStart, markLen), '*'.repeat(markLen));
}
新增 index.html (副檔名不用輸入)
<!doctype html>
<html lang="en">
<head>
<base target="_top">
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<!-- jquery https://developers.google.com/speed/libraries#jquery -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.3/jquery.min.js"></script>
<!-- bootstrap https://getbootstrap.com/docs/5.3/getting-started/introduction/ -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-GLhlTQ8iRABdZLl6O3oVMWSktQOp6b7In1Zl3/Jr59b6EGGoI1aFkw7cmDA6j6gD" crossorigin="anonymous">
<!-- bootstrap icon https://icons.getbootstrap.com/ -->
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.10.3/font/bootstrap-icons.css">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha1/dist/js/bootstrap.bundle.min.js" integrity="sha384-w76AqPfDkMBDXo30jS1Sgez6pr3x5MlQ1ZAGC+nuZB+EYdgRZgiwxhTBTkF7CXvN" crossorigin="anonymous"></script>
<!-- bootstrap-table -->
<link rel="stylesheet" href="https://unpkg.com/bootstrap-table@1.22.2/dist/bootstrap-table.min.css">
<script src="https://unpkg.com/bootstrap-table@1.22.2/dist/bootstrap-table.min.js"></script>
<script src="https://unpkg.com/bootstrap-table@1.22.2/dist/locale/bootstrap-table-zh-TW.js"></script>
</head>
<body>
<div class="container mt-3">
<h1 class="text-center"><?= title ?></h1>
<div id="toolbar"></div>
<table id="table"></table>
</div>
<script>
function sheetData() {
google.script.run.withSuccessHandler(function(rows){
let columns = [];
rows.stru.forEach((item, index) => {
columns.push({
field: `${index}`,
title: item['title'],
visible: item['visible'],
align: item['align'],
sortable: item['sortable'],
width: item['width']
})
})
$("#table").bootstrapTable({
classes: 'table table-sm table-bordered table-hover', //表格樣式 table table-sm table-striped table-bordered table-hover
toolbar: '#toolbar', //工具按鈕用哪個容器
cache: false, //是否使用快取，預設為 true，所以一般情況下需要設定一下這個屬性（*）
sidePagination: "client", //分頁方式：client 使用者端分頁，server 伺服器端分頁（*）
theadClasses: 'table-dark', //標題列樣式
rememberOrder: true, //使用rememberOrder選項來記住每列的順序。
sortName: '0', //排序欄名
sortOrder: 'desc', //
//可於ToolBar上顯示的按鈕
buttonsClass: "primary", //
buttonsAlign: "right", //left right
// showColumns : true, //顯示/隱藏哪些欄位
// showToggle : true, //名片式/table式切換
showRefresh : false, //重新整理
showPaginationSwitch : true, //分頁/不分頁切換
showFullscreen: true, //顯示全螢幕
search : true, //查詢
// buttons : '', //返回甄角按鈕
// cardView: true, //是否顯示詳細檢視
// detailView: true, //是否顯示父子表
pagination : true, //使否要分頁
pageNumber:1, //初始化載入第一頁，預設第一頁
onPageChange:function(currentPage, pageSize) {
//console.log("目前頁數: "+currentPage+" ,一頁顯示: "+pageSize+" 筆");
},
pageSize : 10, //一頁顯示幾筆
pageList : "[ 10, 20, 100, all]", //一頁顯示幾筆的選項
formatRecordsPerPage:function(pageSize) {
return ' 每頁顯示 ' + pageSize + ' 筆';
},
formatShowingRows:function(fromIndex, toIndex, totalSize) {
//目前第幾頁
var currentPage = Math.ceil(fromIndex / this.pageSize);
//總共幾頁
var totalPageCount = Math.ceil(totalSize / this.pageSize);
return currentPage+' / '+totalPageCount;
},
columns: columns,
data: rows.data
});
}).getData();
}
$(function(){
sheetData();
})
</script>
</body>
</html>
11-3 試算表增加欄位