初試 App Script

緣起:

    我現在的工作，發薪日是 10 號，9/11 號時有問老闆，她說需要我回報工時，格式如下。我就趁著工作時的空檔寫一寫，有些時間忘了，還能翻翻日記來看那天到底是幾點做到幾點。

    最近想到，每次做這個報表時都要這樣搞的話也太麻煩，直接開個 google spreadsheet 紀錄這些，感覺會方便許多，我印象中它還能寫 script 來處理資料，所以就花了點時間弄了一個小專案。

    這篇文章記錄開發時的想法與遇到的問題。

日期與時間:

    每列的資料很簡單，當天日期、當天的工作開始跟結束時間

    再來設定日期那欄，全選欄，點擊右鍵，選 "資料驗證"。

    設定範圍，A2 開始，到 A50，條件設定為 "是有效的日期" 即可。

    雙擊那一欄的儲存格，就能直接選擇日期了。

    接著是時間，比較麻煩，因為 google spreadsheet 的資料規則，沒有 "時間" 的條件，我後來是參考這影片，新增一個工作表，然後在 A1 填入這個公式

=ARRAYFORMULA(SEQUENCE(48,1,0, 30)* 1/(60*24))

    時間是每半小時一項，所以 SEQUENCE 公式代入的值是 48, 1, 0, 30

    然後再全選欄，將格式設為時間

    最後回到主表，設定開始時間與結束時間欄的資料驗證，條件選擇 "下拉式選單(來自某範圍)，然後選取資料表的範圍

    時間的下拉選單就完成了

App Script:

    上方工具列，選擇擴充功能裡的 App Script，它會為你建立一個對應此試算表的 App Script，一個 gs 檔。整個專案弄下來，就只會動到那個 gs 檔，還有執行 gs 檔裡頭定義的 function，它寫起來基本上跟 JavaScript 差不多。

    又是 gpt 大神教我寫程式的時候了，先是寫一些測試用的 function，做基本的功能，像是讀某一格資料，確認沒有問題後，再把它們並入完整的 function 裡，編輯器上方有選單讓你選擇某一 function 來執行

    第一次執行程式時，會跟你要求權限 (同意啦，怎麼可能不同意)

產生報表:

    首先是最重要的，產生報表

function showResult(){
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('main');
  let timeZone='GMT+8';
  let sResult = '';
  let totalHours = 0;
  for(let i=2;i<=sheet.getLastRow();i++){
    let rowDate = sheet.getRange('A'+i.toString()).getValue();
    let rowStartTime = sheet.getRange('B'+i.toString()).getValue();
    let rowEndTime = sheet.getRange('C'+i.toString()).getValue();
    sResult+=`${Utilities.formatDate(rowDate, timeZone, 'MM/dd')}\t${Utilities.formatDate(rowStartTime, timeZone, 'HH:mm')}~${Utilities.formatDate(rowEndTime, timeZone, 'HH:mm')}\n`;
    totalHours+=(rowEndTime-rowStartTime)/(1000*60*60);
  }
  sResult+=`總共${totalHours}小時`;
  SpreadsheetApp.getUi().alert(sResult);
}

    第 2 行 : 用工作表的名稱來取得 sheet 實體

    第 3~5 行 : timeZone 格式化時間會用到，sResult 用來組合輸出結果，totalHours 紀錄工時總和。

    第 6 行 : getLastRow 可以取得最後一列有資料的。

    第 7~9 行 : getRange 取得儲存格資料，參數是字串，ex : "A1:C3"，會回傳一個二維的陣列，但我們只需單一欄位的值，所以直接取 A{數字} B{數字} C{數字} 單一儲存格，然後再用 getValue 拿欄位裡的值。雖然開始時間跟結束時間的下拉選單只有呈現時間的值，但取值後其實還會有日期的資料，固定是 1899/12/30，不影響程式，我們只是需要求時間差而已。

    第 10 行 : 我原本以為日期類別的資料可以像 C# 一樣直接呼叫 toString 來格式化，但正確做法應該是呼叫 Utilities.formatDate。

    第 11 行 : 兩個時間資料相減，會回傳之間的毫秒，我要的單位是小時，所以除以 1000*60*60。

    

清空資料:

    每次回報完後，舊資料就用不到了，所以我還有寫個清空資料的 function

function clearDatas() {
  const ui =SpreadsheetApp.getUi();
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('main');
  const cell = sheet.getRange('A2:C30');
  let response = SpreadsheetApp.getUi().alert(
    '提醒',
    '確定清空?',
    ui.ButtonSet.OK_CANCEL
    )

  if(response == ui.Button.OK){
    cell.clearContent();
  }
  
}

    第 5~9 行 : alert 還能與使用者互動，用 response 來記錄使用者

    第 11~13 行 : 使用者點擊"確定"後，用 clearContent 清空儲存格的資料

    執行 function 後，會先跳出一個提示，用來避免不小心把整個月的資料都砍掉。

客製工具列:

    如果每次要操作，都還要跑去 app script 那邊執行 function，還是有些麻煩，所以我還有再問 gpt 有沒有辨法在工具列新增客製的選項，方法是有的

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('自訂工具')
    .addItem('顯示報表', 'showResult')
    .addItem('清空資料', 'clearDatas')
    .addToUi();
}

    呼叫 ui 的 createMenu，建立一個選單，再用 addItem 加入選項，第二個參數綁定呼叫的 function 名稱。執行一次 onOpen 的 function 後，試算表的工具列就能看到你自訂的選單

    至此，我的工時回報小工具就完成了 ~~~