Gg / 透過Google Forms 新增行事曆活動

Gg / 透過Google Forms 新增行事曆活動

Post Views: 1,113

Google 行事曆  (Google Calendar) 可以用匯入的方式批次增加活動

我之前曾經將工作的大事記整理成Google Calendar可以匯入的格式

可以參考一下這個連結的Google Document的說明

基本上就是需要以下幾項資料

Subject (活動名稱)、Start Date(開始日期)、 Start Time(開始時間)、End Date(結束日期)、End Time(結束時間)、

All Day Event(是否為整天活動)、Location(地點)、Description (活動內容)

這次想嘗試利用Google 表單(Forms)新增行事曆活動

整體流程是：1.建立表單、2.設定行事曆需要的表單內容、3.表單回應連結試算表、4.試算表新增Sctipt、5.將試算表的資料傳到行事曆、6.開啟回應試算表的警告視窗

其中4、5的部分是參考Google Sheets – Add (Import) Events in Bulk to Google Calendar Using Apps Script Tutorial – Part 11

1.建立表單

2.設定行事曆需要的表單內容

新增表單，建立行事曆所需要的資料

主要是：活動名稱、開始日期時間、結束日期時間、地點、活動內容

其中日期，不知道可以在這個項目下加入時間，所以最剛開始的版本是分別填寫日期跟時間

這會影響之後Script處理Date格式的方式

後來就直接用一個題目來處理日期跟時間

3.表單回應連結試算表

4.試算表新增Sctipt

需要注意的是：

#12~#13：選取資料範圍，因為第一列是標題、A欄是自動加上的表單提交時間，所以就從B2開始

又一開始是將日期跟時間分別兩個題目，這樣全部會有7欄，再用getLastRow()取得有資料的最後一列的列數lr，所以範圍會是”B2:H”+lr

#17~#18：因為只要登錄最新一筆的資料，而length的計數是從1開始，陣列的計數是從0開始

所以最近一筆資料的陣列序號是length-1

#20~#54：如果沒資料就不處理

#21~#31：處理試算表的日期時間格式，先把資料格式化成日期字串，再轉成Date格式

#33~#54：將資料登錄到行事曆，並且增加了一些錯誤情況的處理

#33~#42：在最後一欄寫入錯誤訊息

#44~#53：登錄行事曆

 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
 
function add2() {
  //取得目前的資料表
  var ss= SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); 
  
  //取得最後一筆資料的列數
  var lr = ss.getLastRow();
  //Logger.log(lr);

  //取得行事曆
  var cal = CalendarApp.getCalendarById("********************");

  //取得資料表的資料，因為A欄是表單的填寫時間，所以從B欄開始抓取資料 第一列是標題 所以從第二列開始抓
  var data = ss.getRange("B2:H"+lr).getValues();  

  //Logger.log(data.length)
  
  //取得最近一筆資料
  var nt= data.length-1;
  
  if(data[nt][0] !=""){     //有資料才處理
    //處理日期跟時間格式
    //先把格式轉成字串
    var sd =Utilities.formatDate(data[nt][1],"GMT+8","yyyy/MM/dd HH:mm:ss z");      //開始日期
    //Logger.log(data[i][1]);
      
    var sdt = new Date(sd);   //再轉成Date格式      
    //Logger.log(sdt);

    var ed =Utilities.formatDate(data[nt][3],"GMT+8","yyyy/MM/dd HH:mm:ss z");      //結束日期
    
    var edt = new Date(ed);   //再轉成Date格式    
    
    var addError=ss.getLastColumn(); //取得有資料的最後一欄的欄數
    var columnError =ss.getRange(1,addError).getValue(); //取得最後一欄的欄位名稱

    //判斷最後一欄的欄位名稱是不是"登錄訊息" 是否沒有欄位名稱
    if(columnError !="登錄訊息" && columnError !="" ){
        addError = addError+1;                      //如果不是空白，並且不是"登錄訊息"，表示表單可能增加欄位，因此再新增一欄
        ss.getRange(1,addError).setValue("登錄訊息"); 
    }else{
        ss.getRange(1,addError).setValue("登錄訊息");
    }
   
    //避免結束日期時間早於開始日期時間，所產生的錯誤
    try{ 
        //存到行事曆
        cal.createEvent( data[nt][0], sdt, edt, {location:data[nt][5],description:data[nt][6]} );
        var errors = data[nt][0]+"：已登錄!!";
        ss.getRange(nt+2,addError).setValue(errors);   
      } catch (err) {
        var errors = data[nt][0]+"：請注意結束時間可能有誤";
        ss.getRange(nt+2,addError).setValue(errors); 
      }
  }  
}

5.將試算表的資料傳到行事曆

設定程式的觸發條件為提交表單

6.開啟回應試算表的警告視窗

因為表單沒辦法即時驗證結束日期時間是否早於開始日期時間

只能在提交資料後在試算表中處理

前面提交表單之後執行的程式，會將錯誤的資料訊息標註在最後一欄

這邊再設定開啟回應內容的試算表時，出現警告視窗來提醒

這部分的程式要另外寫，因為不能用提交表單當作觸發條件

其實這樣也很合理，因為如果提交表單就觸發，可能連試算表都根本沒開啟

#2~#36跟前面的程式很像，其實就是要重新抓資料跟判斷

差別是在#16~#36是用迴圈，把錯誤的資料全都抓出來

然後再透過#37~#38的警告視窗來提示有錯誤產生

 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
 
function alertFunction() {
  //取得目前的資料表
  var ss= SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); 
  
  //取得最後一筆資料的列數
  var lr = ss.getLastRow();
  //Logger.log(lr);

  //取得行事曆
  var cal = CalendarApp.getCalendarById("********************");

  //取得資料表的資料，因為A欄是表單的填寫時間，所以從B欄開始抓取資料 第一列是標題 所以從第二列開始抓
  var data = ss.getRange("B2:H"+lr).getValues();
  //Logger.log(data.length)
  
  var errors="";
  //
  for(var nt=0 ; nt < data.length ; nt++){  
    if(data[nt][0] !=""){
      //處理日期跟時間格式
      //先把格式轉成字串
      var sd =Utilities.formatDate(data[nt][1],"GMT+8","yyyy/MM/dd HH:mm:ss z");      //開始日期
      //Logger.log(data[i][1]);
        
      var sdt = new Date(sd);   //再轉成Date格式      
      //Logger.log(sdt);

      var ed =Utilities.formatDate(data[nt][3],"GMT+8","yyyy/MM/dd HH:mm:ss z");      //結束日期
      
      var edt = new Date(ed);   //再轉成Date格式    
      
      if (edt < sdt ){
        errors += "時間可能有誤，請確認-->"+data[nt][0]+"\n";
      }
    } 
  }
  var ui = SpreadsheetApp.getUi();
  ui.alert("注意!!",errors,  ui.ButtonSet.OK);
}

 

最後備註這次新用到的程式語法

CalendarApp.getCalendarById(“ID”) ID是字串格式

createEvent(title, startTime, endTime, options)

SpreadsheetApp.getUi( )

alert(title, prompt, buttons)

Tags: , , ,