雲端工具入門
程式碼.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>