JS / 以Google sheets作為資料庫，透過Google Apps Script製作查詢頁面

JS / 以Google sheets作為資料庫，透過Google Apps Script製作查詢頁面

Post Views: 1,768

這次全部都在google apps script完成前端與後端程式

可以用iframe嵌入網站

測試用的電子信箱：whittaker8907@gmail.com、「分科課程：國中數學」，裡面的資料同樣都是假的

架構

後端程式碼是.gs：程式碼.gs

前端程式碼是.html：index.html、style.css.html、sctipt.js.html

debug.gs是用來測試的，執行之後會觸發searchData()，並且將參數傳入

這樣就可以模擬接收前端網頁之後的執行情況

function test6() {
  searchData({ mailID:"whittaker8907@gmail.com",cID:"c0101,c0102,c02,ma01,ma02"}
  );
}

後端程式

#1-7 function doGet()

有點類似程式進入點

當發布成網頁程式之後，連結進來就會觸發doGet()，執行HtmlService，將index.html載入執行

其中 setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)的設定

可以讓網頁程式能夠崁入其他網站

#9-14 function include(filename)

createHtmlOutputFromFile(filename)，顧名思義就是從檔案建立Html輸出

可以讓前端的頁面載入Html、JavaScript、Css等前端資料

#24 var params = e;

由於是在google apps script內的傳遞

所以前端傳來的物件就直接用物件的方式來取值

var params = e; 
var mailID = params.mailID;
var cID= params.cID;

#17-144 function searchData(e)

接受前端傳來的參數

整體而言，程式碼基本上跟之前的API練習一樣

最大的差別是在最後的 return

這也是我卡住很久的地方

後來在網路論壇上看到有人建議強制轉型再傳出

我自己的測試之後可以有2種方式

方法1，先將內容是json格式的陣列轉成json字串，然後再轉成物件

這樣對應前端的接收是物件型態

方法2.只將內容是json格式的陣列轉成json字串

前端收到的是字串型態

//return dataAll; //無法傳遞 

//#1 return JSON.parse(JSON.stringify(dataAll)); 

//#2 //return JSON.stringify(dataAll);

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
 
function doGet() {

  var output = HtmlService.createTemplateFromFile("index").evaluate().setTitle("查詢系統");
  output.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);//可以將發布成html介面的程式  崁入其他網站
  return output;

}

//讓前端可以載入 css js
function include(filename) {

  return HtmlService.createHtmlOutputFromFile(filename).getContent();

}

//----------------------------------------------------------搜尋前端傳來的值
function searchData(e) {

  Logger.log(e);
  
  //取得參數
  //var params = e.parameter;
  //console.log(e);
  var params = e;
  var mailID = params.mailID;//
  //console.log(mailID);

  var cID= params.cID;
  //console.log(cID);

  var arryC = cID.split(",");
  //console.log(arryC[0]);

  var arr1 =["c0101","c0102","c02","ch01","ch02","en01","en02","ma01","ma02"];

  var arr2 =["1fuCeX0Frf_N1avskAaCZ8CZ_QPjpVmzt-MyLfmQ-tCU","1Or0-pD7GBI9icsXpGB5275o0hxuyZiBBGJ__0kXii6g","1GVha6hSEvntglGnTM3iO2tZqli70T3Q0zeg3cpI1aHE","1id-dB13xyx8rpBBNPZ6mEUvz25Y60p6AfWpW2E7oBrQ","1_2zCPSrxUyKzeVwi60DTFDM_8Zw_HRfSbzxQxMARmKE","12IZpoyma-SpbLWjUqIWsdivTMPGDJFBbu-rGsr0gG9U","1h4BO_5oiyE8uKgL84-iwEcP-cElkM7QxyLs73gfOSYU","1cYIy0Ojytx-F5yFhQnoc-ggmEiFdbsAlz5kr7J22-Ws","1oi8TLFDbsX_Q7YPakbheE7VAPI272SEyQkwpsWUm0kw"];
  
  var dataAll = [];//用來存資料

  for(var cc= 0; cc < arryC.length ; cc++){
    var r =arr1.indexOf(arryC[cc]);
    //console.log(r);
    var sId = arr2[r];
    //console.log(sId);
      ///*
    var Sheet = SpreadsheetApp.openById(sId).getSheets()[0];

    var LastRow = Sheet.getLastRow();
    var LastColumn = Sheet.getLastColumn();
    
    var data = [];//用來存資料

    // 取得全部資料
    var listAll = Sheet.getSheetValues(1, 1, LastRow, LastColumn);
    //console.log( listAll[0]);
    //存標題
    var listA =[];
    for(var j=0; j < listAll[0].length ; j++){
      listA.push(listAll[0][j]);
    }
    //console.log(listA);
    //[listAll[0][0],listAll[0][1],listAll[0][2],listAll[0][3],listAll[0][4]];
    data.push({dataload: listA })  //第1列是標題,序號由0開始，設定JSON格式{dataload: listAll[0]}
    //console.log(data);

    for(var i = 1; i < listAll.length; i++){ //第2列才是資料,序號由0開始
        if(listAll[i][1] == mailID){ //電子信箱在第2欄,序號由0開始
          var listOne =[];
          for(var k=0; k < listAll[0].length ; k++){
            
            if( listAll[i][k].toString().search(/drive.google.com/i) > 0){
              var rr= listAll[i][k].split(",");
              //console.log(rr[0]);
              //console.log(rr[1]);
              if(rr.length >0){
                var nHref ="";
                for(var m =0 ; m < rr.length ; m++){
                    nHref += "<a href='" + rr[m].trim() +"' target='_blank'>" + rr[m].trim() + "</a> ";
                }
                //console.log("nHref");
                //console.log(nHref);
                listAll[i][k] =nHref;
              }else{
                listAll[i][k] = "<a href='" + listAll[i][k]+"' target='_blank'>"+ listAll[i][k] +"</a>";
              }
              
              //console.log(listAll[i][k] );
              listOne.push(listAll[i][k]);  
            }else{
              listOne.push(listAll[i][k]);  
            }
            
          }
          // [listAll[i][0], listAll[i][1], listAll[i][2], listAll[i][3],listAll[i][4]]
          data.push({dataload: listOne}); //寫入資料
        }       
    }
    dataAll.push({course:data});
  }
        
  //顯示資料
   // Logger.log(data);
   Logger.log(dataAll);
  //將資料存成JSON格式並回傳  //結果不同
  //return ContentService.createTextOutput(JSON.stringify(dataAll)).setMimeType(ContentService.MimeType.JSON); //物件
  //return ContentService.createTextOutput(JSON.stringify(dataAll)); //字串
  //*/
  //參考 https://stackoverflow.com/questions/56480228/google-script-run-withsuccesshandler-does-not-return-value
  //return dataAll;  //無法傳遞
  //#1
  return JSON.parse(JSON.stringify(dataAll));
  //#2
  //return JSON.stringify(dataAll);
}

 

前端程式

Html

#6 載入jQuery

#7 載入自訂的Css檔案

#8 <base target=”_top”> 最上層顯示

#36 載入自訂的JavaScript檔案

在</body>後載入以確保前面的DOM都已經輸出

這邊用到google apps script的強制列印指令碼

<?!= ... ?>

可以將包起來的程式碼執行的結果輸出到網頁

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
 
<!DOCTYPE html>
<html>
  <head> <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <script src="https://code.jquery.com/jquery-3.3.1.js" integrity="sha256-2Kok7MbOyxpgUVvAk/HJ2jigOSYS2auK4Pfzbm7uH60=" crossorigin="anonymous"></script>
    <?!= include("style.css"); ?>
    <base target="_top">
  </head>
  <body>
    <!--測試資料: mailID:"alice5659@gmail.com", cID:"c0102" -->
    <div id="d1">
      <label>電子信箱：<input id="mailID" type="text" /></label><br /><br />
      <label>查詢填寫紀錄</label><br />
      <label><input type="checkbox" name="courses1" value="c0101" />共同課程一 ：低成就學生心理特質、學習動機及教學經營實務案例</label>
      <label><input type="checkbox" name="courses1" value="c0102" />共同課程一 ：數位資源融入學習扶助教學知能</label><br />
      <label><input type="checkbox" name="courses2" value="c02" />共同課程二 ：科技化評量測驗結果應用</label><br />
      <label><input type="checkbox" name="courses3" value="ch01" />分科課程 ：國中國語文</label>
      <label><input type="checkbox" name="courses3" value="ch02" />分科課程 ：國小國語文</label><br />
      <label><input type="checkbox" name="courses3" value="en01" />分科課程 ：國中英語文</label>
      <label><input type="checkbox" name="courses3" value="en02" />分科課程 ：國小英語文</label><br />
      <label><input type="checkbox" name="courses3" value="ma01" />分科課程 ：國中數學&ensp;&ensp;</label>
      <label><input type="checkbox" name="courses3" value="ma02" />分科課程 ：國小數學</label>
    </div>
    <div id="d2">
      <input name="search" id="sender" onclick="sender();" type="button" value="送出" />
      <input name="clean" id="cleaner" onclick="cleanAll();" type="button" value="清除資料" />
    </div>
    <span id="search_status"></span>
    <dialog id="infoModal">
      <p>請輸入查詢資訊!!</p>
      <button id="close">關閉視窗</button>
    </dialog>

  </body>
  <?!= include("script.js"); ?>   <!-- showData -->
</html>

 

Css

跟之前的練習一樣

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
 
<style>
    .table,
    .table * {
      margin: 10 auto;
      padding: 0;
      font-size: 10px;
      font-family: "Noto Sans CJK TC", "Microsoft JhengHei", PingFang, STHeiti, sans-serif, serif;
    }

    .table {
      display: table;
      width: 100%;
      border-collapse: collapse;
    }

    .table-tr {
      display: table-row;
      height: 30px;
    }

    .table-th {
      display: table-cell;
      font-weight: bold;
      height: 100%;
      border: 1px solid gray;
      text-align: start;
      vertical-align: middle;
      background-color: #E5E5E5;
    }

    .table-td {
      display: table-cell;
      height: 100%;
      border: 1px solid gray;
      text-align: start;
      vertical-align: middle;
      padding-left: 4px;
    }

    #d1 input[type=checkbox] {
      padding: 0;
      margin-top: 10px;
      width: 20px;
      height: 20px;
      border: 2px solid black;
      border-radius: 50%;
      cursor: pointer;
      transition: 0.5s;
      appearance: none;
      /*取消預設樣式 重要!!*/
    }

    #d1 input[type=checkbox]:checked {
      background-color: #DFFF47;
      border-color: black;
      width: 25px;
      height: 25px;
    }

    /*
    #d1 input[type=radio].focus,
    #d1 input[type=radio]:hover {
      width: 25px;
      height: 25px;         
    }
    */

    #d1 label {
      margin-top: 10px;
      margin-bottom: 10px;
      padding-top: 20px;
      padding-bottom: 20px;
    }

    #d2 {
      margin-top: 20px;
      margin-bottom: 20px;
    }

    /*
    input[type=button] {
      background-color: transparent;
      padding: 8px;
      color: black;
      cursor: pointer;
    }
    
    input[type=button]:hover {
      background-color: rgba(240, 240, 240, 0.8);
    }
    */

</style>

 

JavaScript

#54-164 有修改的部分

因為是在google apps script內進行前端與後端的溝通，程式碼有一些調整

將原本在sendData()內執行的ajax程式碼，在獲取後端資料的處理程式獨立成getAll()

在sendData()的最後增加google.script.run.withSuccessHandler(getAll).searchData(data[0])，則是類似ajax溝通後端的程序

意思是將data[0]傳入在後端的searchData()，執行之後回傳值到前端的getAll()

sendData()跟getAll()共用的變數也需要拉出來宣告為廣域變數

data也因為是直接傳到searchDat()之中，所以還是陣列(內容是物件型態)，也只有1筆資料，所以是data[0]

在#109-113配合後端傳回值的型態有2種處理方式

#1 前端傳來 object 
var obj = JSON.parse(JSON.stringify(result, dateReviver));
#2 前端傳來 json string 
var obj = JSON.parse(result, dateReviver);

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
 
<script>
//
var dateReviver = function (key, value) {
  //將日期字串轉為日期資料型態
  //console.log("key", key);
  //console.log("value", 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
    ); //比對是否符合日期型態
    //console.log(a);
    if (a) {
      var dataString = new Date(
        Date.UTC(+a[1], +a[2] - 1, +a[3], +a[4], +a[5], +a[6])
      );
      //console.log("dataString", dataString);
      var weekday = new Array(6);
      weekday[0] = "日";
      weekday[1] = "一";
      weekday[2] = "二";
      weekday[3] = "三";
      weekday[4] = "四";
      weekday[5] = "五";
      weekday[6] = "六";
      var hour = dataString.getHours();
      var min = dataString.getMinutes();
      var sec = dataString.getSeconds();
      // 轉成字串，如果低於10，前面加上'0'
      var hourString = hour < 10 ? "0" + hour : "" + hour;
      var minString = min < 10 ? "0" + min : "" + min;
      var secString = sec < 10 ? "0" + sec : "" + sec;
      var formatted_date =
        dataString.getFullYear() +
        "/" +
        (dataString.getMonth() + 1) +
        "/" +
        dataString.getDate() +
        "(" +
        weekday[dataString.getDay()] +
        ")" +
        " " +
        hourString +
        ":" +
        minString +
        ":" +
        secString;
      return formatted_date;
    }
  }
  return value;
}
//
var arryC =[];
var cName = [];

function sendData(mailID, cID) {
  console.log("searchData-", mailID, cID);
  console.log("cID");
  console.log(cID);
  arryC =[];
  cName = [];
  arryC = cID.split(",");
  console.log("arryC");
  console.log(arryC);
  var arr1 = ["c0101","c0102","c02","ch01","ch02","en01","en02","ma01","ma02"];
  var arr2 = ["「共同課程一：低成就學生心理特質、學習動機及教學經營實務案例」","「共同課程一：數位資源融入學習扶助教學知能」","「共同課程二：科技化評量測驗結果應用」","「分科課程：國中國語文」","「分科課程：國小國語文」","「分科課程：國中英語文」","「分科課程：國小英語文」","「分科課程：國中數學」","「分科課程：國小數學」"];
  
  for (var cc = 0; cc < arryC.length; cc++) {
    var r = arr1.indexOf(arryC[cc]);
    console.log("r");
    console.log(r);
    cName[cc] = arr2[r];
    console.log("arr2[r]");
    console.log(arr2[r]);
    console.log("cName[cc]");
    console.log(cName[cc]);
  }
  console.log("cName");
  console.log(cName);
  var sender = $("#sender");
  var cleaner = $("#cleaner");
  sender.attr("disabled", true);
  sender.val("搜尋中");
  cleaner.attr("disabled", true);
  
  var data=[];
  data.push({mailID:mailID,cID: cID});
  console.log(data);
  
  console.log(data[0]);

  google.script.run.withSuccessHandler(getAll).searchData(data[0]);//後端執行searchData  回傳值到success
}
///*

function getAll(e) {
      console.log("e");
      console.log(e);
      var sender = $("#sender");
      var cleaner = $("#cleaner");
      sender.attr("disabled", false);
      sender.val("送出");
      cleaner.attr("disabled", false);
      var result = e;
      console.log("result");
      console.log(typeof result);
      
      //#1 後端傳來 object
      var obj = JSON.parse(JSON.stringify(result, dateReviver)); //JSON.stringify解析成json字串,dateReviver將日期字串轉為日期資料型態  JSON.parse解析json字串為json物件形式
      //#2 後端傳來 json string
      //var obj = JSON.parse(result, dateReviver); //
      console.log(obj);
      
      var html = "";
      for (var m = 0; m < obj.length; m++) {
        console.log(obj[m]);
        console.log(cName[m]);
        if (obj[m].course.length == 1) {
          //只有1筆(標題)代表查不到資料
          html += "<h3>" + (m + 1) + "-" + cName[m] + "：查無資料<h3>";
          $("#search_status").html(html); //寫入Html
          //alert('查無資料');
          $("#mailID").val("");
          $('input[type="checkbox"]').prop("checked", false);
        } else {
          //
          //
          html += "<h3>" + (m + 1) + "-" + cName[m] + "</h3>";
          html += '<div class="table"><div class="table-tr">';
          //取出標題
          for (var k = 0; k < obj[m].course[0].dataload.length; k++) {
            html +=
              '<div class="table-th">' +
              obj[m].course[0].dataload[k] +
              "</div>";
          }
          html += "</div>";
          //<div class="table-tr"><div class="table-th">填寫日期</div><div class="table-th">電子信箱</div><div class="table-th">姓名</div><div class="table-th">服務單位</div><div class="table-th">職稱</div></div>';
          //將資料寫出在Html表格，配合2次迴圈讀取每1筆資料內的所有資料
          //
          for (var i = 1; i < obj[m].course.length; i++) {
            //所有筆數的資料跑迴圈
            html += '<div class="table-tr">'; //
            for (
              var j = 0;
              j < obj[m].course[i].dataload.length;
              j++ //每1筆資料內的所有資料，dataload對應回傳的JSON格式
            ) {
              html +=
                '<div class="table-td">' +
                obj[m].course[i].dataload[j] +
                "</div>";
            }
            html += "</div>";
          }
          html += "</div>";
          //
          $("#search_status").html(html); //寫入Html
          $("#mailID").val("");
          $('input[type="checkbox"]').prop("checked", false);
        }
      }
    }
//*/
//
//
function sender() {
  var courses = [];
  $('input[type="checkbox"]:checked').each(function (i) {
    courses.push($(this).val());
    //console.log($(this).val());
    //console.log(i);
  });
  //console.log("sender");
  console.log(courses.toString());
  var email = $('#mailID').val();
  if (courses.toString() == "" || email == "") {
    console.log("err");
    //window.alert("請確認查詢資訊");
    //var infoModal = document.getElementById("infoModal");
    var infoModal = $("#infoModal").get(0); //重要 https://www.fooish.com/jquery/selectors.html
    var closeD = $("#close");
    infoModal.show();
    closeD.on("click", function () {
      console.log("close");
      infoModal.close();
    });
  } else {
    $("#search_status").html("");
    sendData(email, courses.toString());
  }
}
//
function cleanAll() {
  $("#search_status").html("");
  $("#mailID").val("");
  $('input[type="checkbox"]').prop("checked", false);
}
//
/*
    $(document).ready(function() { //重要
      $('input[type="radio"]').on("click", function() { //绑定click 事件
        //var courses1 = $("input[type=radio]:checked").val();
        //console.log("courses1");
        //console.log($("input[type=radio]:checked").data("checked"));
        var $radio = $(this);
        //console.log($radio.data("checked"));
        if ($radio.data("checked") == true) {
          $radio.prop("checked", false);
          $radio.data("checked", false);
          //console.log("then");
          //console.log($radio.data("checked"));
        } else {
          $radio.prop("checked", true);
          $radio.data("checked", true);
          //console.log("else");
          //console.log($radio.data("checked"));
        }
        //console.log($radio.data("checked"));
        //var courses2 = $("input[type=radio]:checked").val();
        //console.log("courses2");
        //console.log($("input[type=radio]:checked").data("checked"));
      });
      //
    });
*/

</script>

 

Tags: