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

文章索引

Post Views: 445

這次練習的程式流程跟之前的一樣

都是以Google sheets作為資料庫，在Google Apps Script製作回傳資料的API，然後建立查詢頁面

查詢頁面可以嵌入網站、Google Site，也可以寫在Google Apps Script裡

第3種方式會用到Google Apps Script本身的函數來溝通前後端的資料傳遞

回到到主題

這次練習跟之前的差異是增加了核取方塊(Checkbox) 來選擇查詢的內容

核取方塊(Checkbok)和選項按鈕(Radio)的差別：

前者是可以複選、被選取的方塊如果再重複點選就會取消
後者是單選，一旦被選取，如果要取消的話，不是全部取消checked，就是要選其他的按鈕

因此，這次使用核取方塊(Checkbok)才能夠達成想要的效果

測試用電子信箱：whittaker8907@gmail.com

裡面的資料：姓名、電子信箱、跟網址都是假的

See the Pen
Demo Post-學習扶助-講師培訓-課前表單 V5-4 by 莊幸諺 (@trico109748007)
on CodePen.0

後端程式碼

前端程式碼會傳來兩個參數，1個是電子信箱、1個是資料表

因為多了一個資料表的參數，我想把個別的資料表資料存成獨立的1筆資料

所以程式碼會多了一層迴圈，程式碼#22-81

第1層迴圈，比對出對應的google sheets ID

比對接收的資料表參數的方式，我比較土炮，應該會有更好的方式

arr1[]存放的是資料表參數

arr2[]存放的是依據arr1[]資料表參數所對應的googlesheets ID

利用indexOf()取得傳來的參數在arr1[]的序號，而這個序號對應arr2[]就可以得到googlesheets的ID

再來是第2層迴圈，從google sheets中比對是否有對應的電子信箱

如果有的話就取出資料

最後傳出的資料型態就會變成2層資料結構

[{course:{daraload:{}, daraload:{},}},{course:{daraload:{}, daraload:{},}}…]

由於部分資料表會有google drive的連結

因此在#53多了一個 if 來判斷 search(/drive.google.com/i) 是否 >0 ( i 表示不分大小寫)

如果>0，表示字串內帶有drive.google.com，也就是超連結，因此加上<a>~~</a>標籤

為了避免如果有2個連結接續再一起，</a>後面多了1 個空格

function doPost(e) {

  Logger.log(e);
  
  //取得參數
  var params = e.parameter;
  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)); //字串
  //*/
}

前端程式碼

Html

checkbox依據課程種類設定對應的value值，這樣後續的程式才能夠知道是選了哪個選項

送出按鈕執行sender()，處理要傳到searchData()的參數

清除資料按鈕執行cleanAll()

<dialog>如果沒有填寫電子信箱或勾選項目就會出現提醒視窗，這個檢查的程式在sender()裡

這邊要留意的是<dialog>在JavaScript的執行方式比較簡單

但是這個練習抓取DOM的方式都是使用jQuery，而這樣得到的是jQuery物件

所以要將jQuery轉成JavaScript物件

var infoModal = $("#infoModal").get(0);

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

Css

比較需要留意的是checbox或者radio都會有預設樣式

如果要改為自訂樣式的話

必須加上 appearance: none;

JavaScript

Function searchData()的修改

主要是在#108-155也是多了一層迴圈來處理後端回傳的資料

其實就是對應後端程式組合資料的方式，再拆解重新組合

#196-255，因為之前用radio來設計選項

而為了達成重複點選就取消的功能，所使用的程式碼

值得留意的是如果要綁定DOM事件的話，程式碼要在window.load之後再綁定

否則就會因為抓取不到對應的DOM而產生錯誤

以及radio在點取的當下，其狀態仍是checked false

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

function searchData(mailID, cID) {
  console.log("searchData-", mailID, cID);
  var arryC = cID.split(",");
  console.log(arryC[0]);
  var arr1 = [
    "c0101",
    "c0102",
    "c02",
    "ch01",
    "ch02",
    "en01",
    "en02",
    "ma01",
    "ma02"
  ];
  var arr2 = [
    "「共同課程一：低成就學生心理特質、學習動機及教學經營實務案例」",
    "「共同課程一：數位資源融入學習扶助教學知能」",
    "「共同課程二：科技化評量測驗結果應用」",
    "「分科課程：國中國語文」",
    "「分科課程：國小國語文」",
    "「分科課程：國中英語文」",
    "「分科課程：國小英語文」",
    "「分科課程：國中數學」",
    "「分科課程：國小數學」"
  ];
  var cName = [];
  for (var cc = 0; cc < arryC.length; cc++) {
    var r = arr1.indexOf(arryC[cc]);
    //console.log(r);
    cName[cc] = arr2[r];
    console.log(cName[cc]);
  }
  var sender = $("#sender");
  var cleaner = $("#cleaner");
  sender.attr("disabled", true);
  sender.val("搜尋中");
  cleaner.attr("disabled", true);
  ///*
  $.ajax({
    url:
      "https://script.google.com/macros/s/AKfycbxSf8TUnF-g62I7foCiNOjNvHFL1TK9Kw2PDf9IF5JQLfGEQa13yHyPaVC4hSk6ZFW1/exec", //Demo Post-學習扶助-講師培訓-課前表單 V5
    method: "POST",
    data: {
      mailID: mailID,
      cID: cID
    },
    success: function (e) {
      sender.attr("disabled", false);
      sender.val("送出");
      cleaner.attr("disabled", false);
      var result = e;
      //console.log(typeof result);  //object
      var obj = JSON.parse(JSON.stringify(result, dateReviver)); //解析json字串為json物件形式,dateReviver將日期字串轉為日期資料型態
      //console.log(typeof 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("");
    searchData(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"));
      });
      //
    });*/

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