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

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

Post Views: 7,159

時間過得好快

距離上一版「JS / 以Google sheets作為資料庫，透過Google Apps Script製作查詢頁面 修改版3」已經有1年之久了

這次的修改是要處理”資料還在增加的階段”，而且”要把所有資料都集中在同一個工作簿”

因為如果是分散在個別的工作簿，第一版就是這樣的設計

在後端程式中分解前端傳遞過來的課程代碼，再分別搜尋指定的工作簿取得對應的資料

經過彙整之後，再傳遞到前端網頁

前端網頁接收之後，經過處理、呈現在網頁上

如果要先把不同表單的資料集中在同一個工作簿，而且資料隨時會增加

這樣就必須先判斷是否有新的資料，如果有就更新原有的工作表

之後再搜尋資料表內是否有對應的資料

我的思路

1.1取得個別工作簿的版本紀錄，判斷是否更新

但是這個無法判斷是因為什麼原因產生新的版本紀錄

可能是修改其他欄位資料，不見得是新增資料筆數

或

1.2 取得個別工作簿的最後一筆資料所在列數，判斷是否增加

感覺這個方式比較準確，也比較直觀

2.更新有增加資料筆數的工作表

這邊用比較暴力的方式，直接刪除舊有工作表，複製新的工作表

為了資料的可讀性，新的工作表要放在舊有工作表原來的位置

現在說明各部分的內容

資料的介面，”資料紀錄”工作表

工作表名稱-課程代碼

fileID-表單的工作表ID

C、D、E欄紀錄當下執行所取得的修改時間、資料筆數，以及執行檢查的時間

F欄是紀錄前一次執行時所取得的修改時間、資料筆數是否跟當下執行所取得的資料，兩者比對的結果

G、H、I欄如果有新增資料就把原來的 C、D、E欄的資料移到這邊，C、D、E欄再寫入新的資料

 

主程式 getSheetInfo()

#2-4

取得”資料紀錄”工作表的最後一筆資料的列數，作為迴圈的依據

#7-47

有多少工作表就跑幾次迴圈

#8

取得A欄資料，工作表名稱

#11

取得B欄資料，工作簿的文件ID

#15-23

透過自訂函數listFileRevisions()，傳入文件ID，取得最近一次的修改紀錄時間

如果當前的修改紀錄時間不等於上一次檢查所取得的時間

將C欄資料移到G欄之後，C欄再寫入新的修改紀錄時間

在J欄寫入”lastModi !== sh.getRange(i, 3).getValue()”，目的是為了檢測程式碼是否確實執行

否則，在J欄寫入”lastModi == sh.getRange(i, 3).getValue()”，目的是為了檢測程式碼是否確實執行

 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
 
function getSheetInfo() {
  var ss = SpreadsheetApp.openById("*****************************************");
  var sh = ss.getSheets()[1];
  var lastRow = sh.getLastRow();
  //console.log(lastRow);

  for (var i = 2; i < lastRow + 1; i++) {
    var sName = sh.getRange(i, 1).getValue();
    //console.log(sName);

    var sFileId = sh.getRange(i, 2).getValue();
    //console.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);
      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; //取得表單回應

    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:hh: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:hh:ss"));
      
	  sh.getRange(i, 6).setValue("相同");
    }
  }
}

 

自訂函數

1.取得最後修改紀錄 listFileRevisions(fileId)

接收fileId-文件ID(B欄)為參數

必須引用drive api，再使用Drive.Revisions.list(fileId)取得修訂紀錄

修訂紀錄為物件(object)的資料型態，所以要用物件的方式來取得資料

只需要取得最新一筆紀錄，其中modifiedDate是修改時間

使用內建函數Utilities.formatDate()調整時區與呈現格式之後，將資料傳回

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
function listFileRevisions(fileId) {
  var response = Drive.Revisions.list(fileId);

  var lastRec = response.items.length - 1;

  var lastModiDate = response.items[lastRec].modifiedDate;

  var fmDate = Utilities.formatDate(new Date(lastModiDate), "GMT+8", "yyyy/MM/dd-hh:hh:ss");

  return fmDate;
}

 

drive api的引用方式

新增”服務”

選擇  drive api

選擇V2版本

2.取得工作表的最後一筆位置 getSheetLastRow( fileId)

接收 fileId-文件ID(B欄)為參數

取得第一個工作表的最後一筆資料所在的列數，傳回

1
2
3
4
5
6
 
function getSheetLastRow(fileId) {
  var sId = fileId;
  var ss = SpreadsheetApp.openById(sId);
  var daTaNum = ss.getSheets()[0].getLastRow();
  return daTaNum;
}

 

3.刪除資料工作表 delTable(fileId)

接收 fileId-文件ID為參數

使用內建函數deleteSheet()來刪除工作表

這邊要留意deleteSheet()接收的參數是工作表物件

1
2
3
4
5
6
 
function delTable(fileId) {
  console.log(fileId);
  var ss = SpreadsheetApp.openById("*****************************************");
  var sOne = ss.getSheetByName(fileId);
  ss.deleteSheet(sOne);
}

 

4.取得工作表 getTable(fileId, fileName)

接收 fileId-文件ID(B欄)、fileName-工作表名稱-課程代碼(A欄)為參數

s1是”資料紀錄”所在的工作簿

s2是要取得資料的來源工作簿

s2One是來源工作簿的第一個工作表

將s2One複製到s1，並用課程代碼來命名

1
2
3
4
5
6
7
8
9
 
function getTable(fileId, fileName) {
  var sId = fileId;
  var sName = fileName;
  var s1 = SpreadsheetApp.openById("*****************************************");
  var s2 = SpreadsheetApp.openById(sId);

  var s2One = s2.getSheets()[0];
  s2One.copyTo(s1).setName(sName);
}

 

5.移動工作表 moveSheet(fileName)

接收 fileName-工作表名稱-課程代碼(A欄)為參數

取得當前工作表在指定順序(arr2)的序號，再移動位置

適用只移動一個工作表

#8-9

內建的工作簿函數moveActiveSheet()接收的參數

是要移動到的工作表位數(從1開始)

工作表必須先activate()，才能使用工作簿函數moveActiveSheet()

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
function moveSheet(fileName) {
  var sName = fileName;
  var ss = SpreadsheetApp.openById("1KCNDlSA_ZgOrCJHfjJehc-REA6Dbquvs98dI0smQufg");
  var sOne = ss.getSheetByName(sName);
  var arr2 = ["c0101", "c0102", "c02", "ch01", "ch02", "en01", "en02", "ma01", "ma02"];
  var r = arr2.indexOf(sName);

  sOne.activate();
  ss.moveActiveSheet(r + 3);
}

 

接下來就是整合到原有的流程之中了

Tags: