|
發表於 2021-2-4 11:15:46
|
顯示全部樓層
板主您好,
昨日看完您分享的影片後,
我研究一下內容做了一個小修正,新增套入一個模糊搜尋的功能,但會出現查無資料,寫法如下,需要在跟您請益精進學習一下,想瞭解是什麼原因造成無法完成模糊搜尋這個問題。
資料庫連結:https://docs.google.com/spreadsheets/d/1hJkcK6TfR49QnqWm0aRpeRzGNyE3puaFFj9YZfhZB94/edit?usp=sharing
HTML前端代碼:
- <html>
- <head>
- <meta charset="UTF-8" />
- <script type="text/javascript">
- Date.prototype.format = function(fmt) {
- var o = {
- "M+": this.getMonth() + 1, //月份
- "d+": this.getDate(), //日
- "h+": this.getHours() % 12 == 0 ? 12 : this.getHours() % 12, //小時
- "H+": this.getHours(), //小時
- "m+": this.getMinutes(), //分
- "s+": this.getSeconds(), //秒
- "q+": Math.floor((this.getMonth() + 3) / 3), //季度
- "S": this.getMilliseconds() //毫秒
-
- };
- if (/(y+)/.test(fmt)) fmt = fmt.replace(RegExp.$1, (this.getFullYear() + "").substr(4 - RegExp.$1.length));
- for (var k in o)
- if (new RegExp("(" + k + ")").test(fmt)) fmt = fmt.replace(RegExp.$1, (RegExp.$1.length == 1) ? (o[k]) : (("00" + o[k]).substr(("" + o[k]).length)));
- return fmt;
- }
- var dateReviver = function(key, value) {
- var a;
- if (typeof value === 'string') {
- a = /^(\d{4})-(\d{2})-(\d{2})T(\d{2}):(\d{2}):(\d{2}(?:\.\d*)?)Z$/.exec(value);
- if (a) {
- return new Date(Date.UTC(+a[1], +a[2] - 1, +a[3], +a[4], +a[5], +a[6])).format("yyyy-MM-dd HH:mm:ss");
- }
- }
- return value;
- };
- function inq() {
- var xmlhttp;
- if (window.XMLHttpRequest) { // code for IE7+, Firefox, Chrome, Opera, Safari
- xmlhttp = new XMLHttpRequest();
- } else { // code for IE6, IE5
- xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
- }
- xmlhttp.onreadystatechange = function() {
- if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
- var result = xmlhttp.responseText;
- var obj = JSON.parse(result, dateReviver); //解析json字串為json物件形式
- var html = '<table border=1 width=100%>'; //
- for (var i = 0; i < obj.length; i++) { //
- html += '<tr>'; //
- if (i == 0)
- html += "<td>編號</td>";
- else
- html += "<td>" + '<input type=checkbox id="OrderID" value="' + obj[i].data[0] + '">' + i + "</td>";
- for (j = 0; j < obj[i].data.length; j++) {
- html += '<td>' + obj[i].data[j] + '</td>';
- }
- html += '</tr>';
- }
- html += "</table>";
- document.getElementById("order_status").innerHTML = html;
- if (obj.length == 1) //只有一筆代表查不到資料
- alert('查無資料');
- }
- }
- var url = "https://script.google.com/macros/s/AKfycby6DNN-JoY1VY-2xcOucvbyTfBAE771bWyn3Ckwy8r3T8Uo1JiENVuxcg/exec";
- var OrderID_list = "";
- if (typeof(OrderID) !== 'undefined') {
- for (var i = 0; i < OrderID.length; i++) {
- if (OrderID[i].checked) {
- OrderID_list += OrderID[i].value + ",";
- }
- }
- if (OrderID_list.length > 0)
- OrderID_list = OrderID_list.substr(0, OrderID_list.length - 1);
- }
- url += "?BeginDate=" + BeginDate.value + "&EndDate=" + EndDate.value + "&OrderID=" + OrderID_list + "&CName=" + CName.value + "&OrderStatus=" + OrderStatus.value;
- console.log(url);
- xmlhttp.open("get", url, true);
- xmlhttp.send();
- }
- </script>
- </head>
- <body>
- 輸入開始訂購日期:<input type="text" id="BeginDate" value="2020/02/01" /><br/> 輸入結束訂購日期:
- <input type="text" id="EndDate" value="2020/02/10" /><br/> 姓名:
- <input type="text" id="CName" value="" /><br/> 訂單狀況:
- <input type="text" id="OrderStatus" value="" /><br/>
- <input type="button" name="inq" value="查詢/更新" onclick="inq();"></br>
- <span id="order_status"></span>
- </body>
- </html>
複製代碼
Google script後端代碼:
- function doGet(e) {
- //https://youtu.be/MQV3AlAOtCU 影音說明
- var params = e.parameter;
- var BeginDate = Date.parse(params.BeginDate);
- var EndDate = Date.parse(params.EndDate);
- var OrderID = params.OrderID;
- var CName = params.CName;//姓名
- //var OrderName=params.OrderName;
- var OrderStatus = params.OrderStatus; //匯款後3碼
- var OrderList = OrderID.split(",");
- var Sheet = SpreadsheetApp.getActiveSheet();
- var LastRow = Sheet.getLastRow();
- var LastColumn = Sheet.getLastColumn();
- var data = [];
- // 取得全部資料
- var listAll = Sheet.getSheetValues(1, 1, LastRow, LastColumn);
- //存標題
- data.push({ data: listAll[0] })
- var ListDate; //儲存日期格式
- var ListOrderID;
- // 找尋資料是否有存在
- for (var i = 1; i < listAll.length; i++) {
- ListDate = listAll[i][1];
- ListDate = Date.parse(ListDate);
- ListOrderID = listAll[i][0];
- //用訂單狀態做判斷,然後用switch
- switch(OrderStatus=="")
- {
- case(OrderStatus==""):
- if (ListDate >= BeginDate && ListDate <= EndDate) {
- if (OrderStatus != "" && OrderList.indexOf(ListOrderID) != -1) {
- Sheet.getRange("D" + (i + 1)).setValue(OrderStatus); //寫回狀態
- Sheet.getRange("G" + (i + 1)).setValue(new Date()); //寫回狀態
- listAll[i] = Sheet.getSheetValues(i + 1, 1, 1, LastColumn)[0]; //重新讀取
- }
- data.push({ data: listAll[i] })
- }
- break;
- case(OrderStatus!=""&& listAll[i][2].indexOf(CName)!=-1):
- if (BeginDate =='' && EndDate=='' && listAll[i][2].indexOf(CName)!=-1) {
- if (OrderStatus != "" && OrderList.indexOf(ListOrderID) != -1) {
- Sheet.getRange("D" + (i + 1)).setValue(OrderStatus); //寫回狀態
- Sheet.getRange("G" + (i + 1)).setValue(new Date()); //寫回狀態
- listAll[i] = Sheet.getSheetValues(i + 1, 1, 1, LastColumn)[0]; //重新讀取
- }
- data.push({ data: listAll[i] })
- }
- break;
- }
- }
- //顯示資料
- Logger.log(data);
- // 回傳json資料
- return ContentService.createTextOutput(JSON.stringify(data))
- .setMimeType(ContentService.MimeType.JSON);
- }
複製代碼 |
-
|