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

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

Post Views: 46

延續「Gg / 以Google sheets作為資料庫，透過Google Apps Script製作查詢頁面 修改版4 part1」

將取得更新工作表的程式碼加入流程之中

這個流程是修改自「JS / 以Google sheets作為資料庫，透過Google Apps Script製作查詢頁面 修改版1」

但是移除建立查詢頁面的程式碼，將相關程式碼移到CodePen

一方面可以測試，另一方面程式碼之後可以直接崁入其他網站，或者Google Site

整合的方式有2種

第1種簡單暴力， 先執行 getSheetInfo()，更新工作簿資料， 再執行searchData()搜尋並回傳資料

1
2
3
4
5
6
7
 
function doPost(e) {
  //Logger.log(e);
  //更新資料表
  getSheetInfo();
  //執行搜尋並回傳資料
  return searchData(e);
}

 

僅searchData()修改#19-20，取得工作簿資料

 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
 
function searchData(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 dataAll = [];//用來存資料

  for (var cc = 0; cc < arryC.length; cc++) {
    var sName = arryC[cc];
    // 從彙整所有表單資料的工作簿取得資料
    var ss = SpreadsheetApp.openById("*****************************************");
    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); //物件
}

 

第2種先拆解搜尋頁面傳來的參數，再分別傳入getSheetInfo2()、searchData()

相較於第1種方式，第2種方式只會更新傳來的課程代碼的表單資料，不會更新所有表單資料

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
 
function doPost(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);

  //更新資料表
  getSheetInfo2(cID);

  //執行搜尋並回傳資料
  return searchData(mailID, cID);
}

 

getSheetInfo2(cID)

接收搜尋頁面傳來的課程代碼

#3

分割傳來的課程代碼

#5-10

取得對應的文件ID

利用createTextFinder()建立工作表的文字搜尋

搜尋課程代碼所在的列數，知道列數，就可以知道在B攔的文件ID

之後的程式碼沒有改變

 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
 
function getSheetInfo2(e) {
  Logger.log(e);
  var arryC = e.split(",");
  for (var cc = 0; cc < arryC.length; cc++) {
    var ss = SpreadsheetApp.openById("**************************************");
    var sName = arryC[cc];
    var sh = ss.getSheets()[1];
    var tf = sh.createTextFinder(sName);
    var i = tf.findNext().getRowIndex();
    var sFileId = sh.getRange(i, 2).getValue();
    Logger.log(sFileId);

    //取得最近一次的修改紀錄
    var lastModi = listFileRevisions(sFileId);
    //console.log(lastModi);
    if (lastModi !== sh.getRange(i, 3).getValue()) {
      sh.getRange(i, 7).setValue(sh.getRange(i, 3).getValue());
      sh.getRange(i, 3).setValue(lastModi);
      //delTable(sName);
      //getTable(sFileId,sName);
      sh.getRange(i, 10).setValue("lastModi !== sh.getRange(i, 3).getValue()");
    } else {
      sh.getRange(i, 10).setValue("lastModi == sh.getRange(i, 3).getValue()");
    }

    //取得最新的表單回應
    var lastR = getSheetLastRow(sFileId) - 1; //取得表單回應
    //console.log("lastR");
    //console.log(lastR);
    //console.log("sh.getRange(i,4).getValue()");
    //console.log(sh.getRange(i, 4).getValue()); //目前的紀錄

    if (lastR != sh.getRange(i, 4).getValue()) {
      sh.getRange(i, 7).setValue(sh.getRange(i, 3).getValue());
      sh.getRange(i, 8).setValue(sh.getRange(i, 4).getValue());
      sh.getRange(i, 9).setValue(sh.getRange(i, 5).getValue());

      sh.getRange(i, 4).setValue(lastR);
      sh.getRange(i, 5).setValue(Utilities.formatDate(new Date(), "GMT+8", "yyyy/MM/dd-hh:mm:ss"));
      sh.getRange(i, 6).setValue("");
      delTable(sName);
      getTable(sFileId, sName);
      moveSheet(sName);
    } else {
      sh.getRange(i, 9).setValue(sh.getRange(i, 5).getValue());
      sh.getRange(i, 5).setValue(Utilities.formatDate(new Date(), "GMT+8", "yyyy/MM/dd-hh:mm:ss"));
      sh.getRange(i, 6).setValue("相同");
    }
  }
}

 

searchData(mailID, cID)

接收搜尋頁面傳來的電子郵件信箱跟課程代碼 ，其餘的程式碼沒有改變

 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
 
function searchData(e1, e2) {
  //取得參數
  var mailID = e1
  var arryC = e2.split(",");
  //console.log(arryC[0]);

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

  for (var cc = 0; cc < arryC.length; cc++) {
    var sName = arryC[cc];
    //
    var ss = SpreadsheetApp.openById("**************************************");
    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); //物件
}

 

Tags: