2023/08/31

Word / 使用VBA分割word內的表格 番外2 使用書籤

在 Word / 使用VBA分割word內的表格 番外 
使用word的復原動作來回覆檔案編輯 

2023/08/29

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

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

 
 
處理問題2. 
 密碼：abc123 
 查詢資料：alice5659@gmail.com

    

 我的思路

1.在工作簿新增一個紀錄密碼的工作表

這其實可以視需求來設定 
 我在彙整所有表單資料的工作簿新增一個名稱為user的工作表
 A欄名稱  password，用來儲存設定的密碼
   

2.新增登入頁面需要的程式碼

前端HTML

主要是多了第2行到第6行，登入的頁面 div login 原來的頁面，包在<div id="searchTable">內 
 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
 
<!--登入 密碼 12345678-->
    <div id="login">      
        <label><input id="pd" type="text" placeholder="請輸入密碼"/></label><br />
        <input name="chack" id="chack" onclick="chack();" type="button" value="送出" />
        <input name="clean2" id="cleaner2" onclick="clean2();" type="button" value="清除資料" /><br /><br />
    </div>
    <!--測試資料: mailID:"alice5659@gmail.com", cID:"c0102" -->
    <div id="searchTable">
      <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>
    </div>
 

前端JavaScript

增加了這些function
function chack()是取得input資料
避免資料型態的問題，將資料都轉換成字串型態 接著執行 google.script.run.withSuccessHandler(showTable).searchPD(data[0]); 將資料傳到後端的function .searchPD()進行資料比對，然後用function showTable()處理回傳值
備註：在測試function chack()的時候發生很詭異的事情
因為一直抓不到值 後來才發現我根本沒有選到，程式碼少了選擇器 但是我是直接抄前面的function sender()裡面的程式 
var email = mailID.value;
完整的程式碼是這樣 
var email = $('#mailID').val();
或者 
var email = document.getElementById("mailID").value;
完全搞不懂是如何抓到資料的...
function showTable(e)
根據後端function searchPD()回傳值判斷是否顯示div searchTable 以及 隱藏 div login
function clean2()
清除 input 
 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
 
function showTable(e) {
  console.log("showTable");
  console.log(e);

  if(e === "good"){
    $('#login').css("display", "none");
    $('#searchTable').css("display", "block");

  }else{
    console.log(e);
    clean2();
    $('#login input[type=button]').css("background-color", "transparent");
  }

}

function chack() {   
  var pd = $('#pd').val();
  console.log(pd);
  var data=[];
  data.push({pd:pd.toString()});
  console.log(data[0]);

  google.script.run.withSuccessHandler(showTable).searchPD(data[0]);//後端執行searchPD  回傳值到showTable
}

function clean2() {
  $("#pd").val("");

}
 

前端CSS

對應DOM物件增加相對應的設定 div searchTable 預設為不顯示 
 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
 
 #searchTable {
    display: none;
}

 #login {
    position: absolute;
    top: 50 % ;
    left: 50 % ;
    transform: translate(-50 % , -50 % );
    text - align: center;
}

 #login input {
    /*display: block;*/
    background - color: transparent;
    color:  # e1341e;
    font - size: 36px;
    border: 1px solid # e1341e;
    border - radius: 50px;
    padding: 20px 45px;
    margin - bottom: 30px;
    outline: none;
    transition: all 0.15s ease - in - out;
}

 #login input: focus {
    background - color:  # e1341e;
    color: white;
}

 #login input: focus::placeholder {
    color: white;
}

 #login input[type = button]{
    cursor: pointer;
    color:  # 1ecbe1;
    font - size: 20px;
    /*text-align: center;*/
    border - radius: 30px;
    border: 1px solid # 1ecbe1;
    padding: 10px 10px;
    outline: none;
}

 #login input[type = button]: active {
    background - color:  # ddd;
}
 

後端Gas

處理login傳來的資料 有相對應的資料，回傳 good 沒有符合的資料，回傳 bad 
 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
 
function searchPD(e) {
    Logger.log(e);
    var params = e;
    var pd = params.pd; //
    console.log(typeof(pd));
    //
    var ss = SpreadsheetApp.openById("1DyT0547XXrNbt46eAH3qu625lBqiQQ9X5i5k0vTR1Eo");
    var Sheet = ss.getSheetByName("user");

    var LastRow = Sheet.getLastRow();
    var LastColumn = Sheet.getLastColumn();

    var data = []; //用來存資料

    // 取得全部資料
    var listAll = Sheet.getSheetValues(1, 1, LastRow, LastColumn);

    //console.log(listAll[1].toString());

    for (var i = 1; i < listAll.length; i++) {
        if (listAll[i].toString() === pd) {
            data.push(listAll[i].toString());
        }
    }
    console.log(data.length);

    if (data.length > 0) {
        return "good";
    } else {
        return "bad";
    }
}
 

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

之前的 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);
}