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

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

Post Views: 2,012

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

收到一位網友的詢問

嘗試處理問題1.

先說結論：測試的結果確實會快不少

原來的版本大約需要6~8秒

修改版只需要2~3秒

我的思路是

1.複製資料到特定的工作簿

因為原本資料是來自不同表單的工作簿，現在需要整合在同一個工作簿內，達成跨工作表的查詢

可以手工一個一個複製貼上，但是用程式來處理總是比較理想

所以，我新建一個工作簿，透過程式抓取不同表單的資料

然後以它們代表的課程cID命名工作表，這樣在第2階段就可以拿作比對

 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
 
function myFunction() { 
  
  //來源網址
  var arrID =["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 arrName =["c0101","c0102","c02","ch01","ch02","en01","en02","ma01","ma02"];

  for (var i =0; i<arr1.length ; i++){
    
    var source = SpreadsheetApp.openById(arrID[i]).getSheets()[0];
    
    //var source = SpreadsheetApp.openById(arrID[i]);

    //var sheet = source.getSheets()[0];

    var destination = SpreadsheetApp.openById("1DyT0547XXrNbt46eAH3qu625lBqiQQ9X5i5k0vTR1Eo");

    var sName = arrName[i];

    //sheet.copyTo(destination).setName(sName);

    source.copyTo(destination).setName(sName);

  }

 

2.修改後端程式比對資料的方式

修改的地方是在第43到第47行，而程式中的陣列arr2就用不到了

var r =arr1.indexOf(arryC[cc]); 
var sName = arr1[r]; 
var ss = SpreadsheetApp.openById("1DyT0547XXrNbt46eAH3qu625lBqiQQ9X5i5k0vTR1Eo"); 
var Sheet = ss.getSheetByName(sName);

想法是，知道前端傳來的是哪一個cID，等於知道新工作簿的哪一個工作表

說明一下：

arryC[]是分割前端傳來的cID

arr1[]是所有cID資料

利用indexOf就可以知道前端傳的cID，對應在arr1[]的索引值

知道索引值就可以取出對應的cID

備註：後來發現有點繞，其實arryC[cc]就是cID…….

var sName = arryC[cc];  
var ss = SpreadsheetApp.openById("1DyT0547XXrNbt46eAH3qu625lBqiQQ9X5i5k0vTR1Eo"); 
var Sheet = ss.getSheetByName(sName);
  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
 
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 sName = arr1[r];
    //console.log(sId);
      ///*
    var ss = SpreadsheetApp.openById("1DyT0547XXrNbt46eAH3qu625lBqiQQ9X5i5k0vTR1Eo");
    var Sheet = ss.getSheetByName(sName);
    
    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);
}