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

文章索引

Post Views: 469

這次全部都在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);

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的強制列印指令碼

<?!= ... ?>

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

<!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

跟之前的練習一樣

<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);

<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>

2024 年 5 月
一	二	三	四	五	六	日
		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

架構