Jamyy's Weblog

Google Apps Script: 早班提醒

by Jamyy on 六月.30, 2015, under Coding

於 Google 試算表撰寫 Script 每日晚上發送電子郵件提醒隔天輪值早班人員
輪值表欄位結構示意圖:

Google 試算表指令碼

function myFunction() {

  //取得當前工作表
  var sheet = SpreadsheetApp.getActiveSheet();
  //輪值表人員名稱與對應的 email 資訊
  var aryStaff={'Peter':'peter@mycompany.com','Wendy':'wendy@mycompany.com'};
  //取得明天的月份
  var T_MONTH = new Date(new Date().getTime() + 24 * 60 * 60 * 1000).getMonth() + 1;
  //明天日期, 格式 yyyy-MM-dd
  var TOMORROW = Utilities.formatDate(new Date(new Date().getTime() + 24 * 60 * 60 * 1000), "GMT+8", "yyyy-MM-dd");

  //從第一欄開始處理, 每次間隔五欄, 讀取日期欄位
  for (var COL=1; COL<56; COL=COL+5) {
    //將目前處理欄位轉成英文代號, 如: 第一欄即轉為 "A" (ASCII CODE 65)
    if (COL > 26) {
      var strCOL = "A" + String.fromCharCode(COL-26+64);
    } else {
      var strCOL = String.fromCharCode(COL+64);
    }
    //取得整欄日期資料, 如: 第一欄日期範圍 A2:A32
    var aryRange = sheet.getRange(strCOL+"2:"+strCOL+"32").getValues();
    //從第一列開始比對日期
    for (var ROW=0; ROW<=aryRange.length; ROW++) {
      //若有日期資料則進行處理
      if (aryRange[ROW] > '') {
        //取得資料月份
        var MONTH = new Date(aryRange[ROW]).getMonth() + 1;
        //若資料月份等於明天月份的處理
        if (MONTH == T_MONTH) {
          //將日期資料轉成 yyyy-MM-dd 格式的字串
          var DATADATE = Utilities.formatDate(new Date(aryRange[ROW]), "GMT+8", "yyyy-MM-dd");
          //若日期為明天的處理
          if (DATADATE == TOMORROW) {
            //將明天早班人員名字帶入 aryStaff 陣列取出電子郵件帳號
            var EMAIL = aryStaff[sheet.getRange(ROW+2,COL+2).getValue()];
            //若有 EMAIL 資料則進行郵件寄送處理
            if (EMAIL > '') {
              //取得星期字串帶入郵件內容
              var WEEKDAY = sheet.getRange(ROW+2,COL+1).getValue();
              //寄出提醒郵件
              MailApp.sendEmail(EMAIL, "明天早班", "明天 " + DATADATE + " (" + WEEKDAY + ") 早班");
              //Logger.log(EMAIL);
            }
            //已達目標日期, 處理完畢
            return;
          }
        } else {
          //非目標月份, 離開迴圈
          break;
        }
      }
    }
  }

}



以下是在 Google 試算表建立輪值表並設置每日定時運行 script 程式進行 email 通知的完整步驟

Step 1. 建立空白 Google 試算表

Step 2. 編輯輪值表內容

Step 3. 點擊 [工具] → [指令碼編輯器]

Step 4. 點擊 [空白專案] 或直接按 [關閉] 略過此畫面

Step 5. 貼入 Script 內容

Step 6. 點擊 [資源] → [現有專案的啟動程序] (或一朵雲右邊那個時鐘)

Step 7. 點擊 [尚未建立觸發程序,按一下這裡來新增觸發程序。]

Step 8. 將 "活動" 設定為: [時間導向]、[天數計時器]、[晚上七點到晚上八點]



:,