Google Apps Scriptで工数入力自動化〜 とあるエンジニアの魔王討伐 〜

こんにちは。エンジニアの土屋です。

前職は非エンジニアだったのですが、Excelを使った業務改善をしていく内にプログラミングが楽しくなり、気づいたらエンジニアになっていたという少し変わったバックグラウンドを持つエンジニアです。

方眼をこよなく愛しており、今でも何か考えるときは自然とスプレッドシートを開いてしまいます。

そんな方眼好き、もとい業務改善好きの自分にとっての大敵。

それは、「ルーチンワーク」です。

この記事は、社員を苦しめている魔王（月次工数入力業務）を退治するためにエクスカリバー（Google Apps Script）を手に立ち上がったとある方眼エンジニアの奮闘記です。

要件定義

フォーマットは以下のようなスプレッドシートのテンプレートを使うものとします。

【要件】
・携わっているプロジェクト単位（C列）で日毎の工数を記載
・どのプロジェクトにも属さない会議については独立したプロジェクトとして記載
・日毎の工数は0.5時間単位で四捨五入した形で記載
・合計（35行目）は勤怠情報から反映されるので今回は考慮不要
・その他差分（34行目）は日毎の合計と入力された工数の差を表しており、この値が0.5未満であること

今回のスコープとしては、どのプロジェクトにも属さない「会議・1on1」について、自分のGoogleカレンダーから取得して工数を記載するようなスクリプトを作成していきます。
プロジェクトの開発業務等はカレンダー情報がないことが多いですが、その日の合計から上記で求めたプロジェクト外会議の差を計算することで半自動的に求めることができます。

成果物（スクリプト）

まずは結論から。
「会議・1on1」のプロジェクトの工数を１クリックで集計してくれるようになりました。

作成したスクリプトは以下です。

// main.gs

function myFunction() {
 var result = Browser.msgBox("カレンダーから予定を取得しますか？\\n\\n※ 24行目の入力欄が上書きされます", Browser.Buttons.OK_CANCEL);
 if (result == "ok"){
   var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('工数確認表');
   var year = sheet.getRange(2, 2).getValue().getFullYear();
   var month = sheet.getRange(2, 2).getValue().getMonth();
   var startDate = new Date(year, month, 1); //取得開始日
   var endDate = new Date(year, month + 1, 0);
   endDate.setHours(23,59,59); //取得終了日:月末の23:59まで
   
   var events = getEventFromCalendar(sheet, startDate, endDate);
   setHoursToRange(events, sheet, month, startDate, endDate);
 }
}

// 指定月のカレンダーからイベントを取得する 
function getEventFromCalendar(sheet, startDate, endDate) {
 var myCal = CalendarApp.getDefaultCalendar(); //自分のカレンダーを取得
 var myEvents = myCal.getEvents(startDate,endDate); //カレンダーのイベントを取得
 return myEvents;
}

// スプレッドシートのセルにイベントの時間を設定
function setHoursToRange(events, sheet, month, startDate, endDate) {
 var dayIndex = events[0].getStartTime().getDate();
 var dayEvents = [];
 var regExp = new RegExp("PJ-999");
 
 initRange(sheet, startDate, endDate);
 // 1日ごとの予定を配列に格納
 for each(var event in events) {
   if(event.getStartTime().getMonth() === month) {
     if(event.getDescription().match(regExp)){
       if(event.getStartTime().getDate() === dayIndex) {
         dayEvents.push(event);
       } else {
         if(dayEvents.length !== 0) {
           calcDayWorkingHours(dayEvents, sheet)
         }
         dayEvents = [];
         dayIndex = event.getStartTime().getDate();
         dayEvents.push(event);
       }
     }
   }
 }
 // 最終日の予定が処理されていないのでループ外で処理
 if(dayEvents.length !== 0) {
   calcDayWorkingHours(dayEvents, sheet)
 }
}

// 1日の予定の合計時間を計算して対象のセルに値を設定する
function calcDayWorkingHours(dayEvents, sheet) {
 var dayTotalHours = dayEvents.reduce(function(total, event) {
   return total + (event.getEndTime() - event.getStartTime())/3600000;
 }, 0);

 var date = dayEvents[0].getStartTime().getDate();
 var col = date + 3;
 var range = sheet.getRange(24, col);
 if(range.isBlank()) {
   range.setValue(halfRound(dayTotalHours));
 }
}

// 0.5単位で四捨五入
function halfRound(num) {
 return Math.round(num * 2) / 2;
}

// セルの値を初期化（クリア）する
function initRange(sheet, startDate, endDate) {
 // 当月の日数を算出
 var thisMonthDays = (endDate - startDate) / 86400000 + 1;
 sheet.getRange(24,4,1,thisMonthDays).clearContent();
}

GAS開発手順

基本的にはブラウザ上に表示されるスクリプトエディタからコードを書いていきます。

エディタにコードを書いていき、関数選択して実行することができます。
また、ログ出力メソッドを使用することでデバッグしながら開発していくことができます。

なお、今回は割愛しますが、「clasp」というCLIツールを使用することで普段使用しているエディタで書いたコードをスクリプトエディタにpushすることができます。

スクリプトの解説

全体的な処理の流れとしては以下の通りです。
1. メッセージボックス表示
2. 指定の月のカレンダーから予定を取得
3. 日毎のセルに予定の所要時間の合計（0.5h単位で四捨五入）を設定

カレンダーから予定を取得
対象カレンダー、対象シートのオブジェクトを取得することで任意の操作を行うことができるようになります。
今回は当月（A2セル記載）分のカレンダーから予定（event）を取得します。

function myFunction() {
 var result = Browser.msgBox("カレンダーから予定を取得しますか？\\n\\n※ 24行目の入力欄が上書きされます", Browser.Buttons.OK_CANCEL);
 if (result == "ok"){
   var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('工数確認表');
   var year = sheet.getRange(2, 2).getValue().getFullYear();
   var month = sheet.getRange(2, 2).getValue().getMonth();
   var startDate = new Date(year, month, 1); //取得開始日
   var endDate = new Date(year, month + 1, 0);
   endDate.setHours(23,59,59); //取得終了日:月末の23:59まで
   
   var events = getEventFromCalendar(sheet, startDate, endDate);
   setHoursToRange(events, sheet, month, startDate, endDate);
 }
}

// 指定月のカレンダーからイベントを取得する 
function getEventFromCalendar(sheet, startDate, endDate) {
 var myCal = CalendarApp.getDefaultCalendar(); //自分のカレンダーを取得
 var myEvents = myCal.getEvents(startDate,endDate); //カレンダーのイベントを取得
 return myEvents;
}

日毎のセルに所要時間合計を設定
【前提】
集計対象とする予定を判定するために所定の文言をカレンダーの予定の説明欄に記載しておきます。（例：「PJ-999」）
定期的な予定であればデフォルト設定されるので便利です。

上記のタグを元に集計対象の予定を抽出、日毎に集計し、0.5単位で四捨五入した値をセルに設定しています。

// スプレッドシートのセルにイベントの時間を設定
function setHoursToRange(events, sheet, month, startDate, endDate) {
 var dayIndex = events[0].getStartTime().getDate();
 var dayEvents = [];
 var regExp = new RegExp("PJ-999");
 
 initRange(sheet, startDate, endDate);
 // 1日ごとの予定を配列に格納
 for each(var event in events) {
   if(event.getStartTime().getMonth() === month) {
     if(event.getDescription().match(regExp)){
       if(event.getStartTime().getDate() === dayIndex) {
         dayEvents.push(event);
       } else {
         if(dayEvents.length !== 0) {
           calcDayWorkingHours(dayEvents, sheet)
         }
         dayEvents = [];
         dayIndex = event.getStartTime().getDate();
         dayEvents.push(event);
       }
     }
   }
 }
 // 最終日の予定がmonthEventsにpushされないのでループ外で追加
 if(dayEvents.length !== 0) {
   calcDayWorkingHours(dayEvents, sheet)
 }
}

// 1日の予定の合計時間を計算して対象のセルに値を設定する
function calcDayWorkingHours(dayEvents, sheet) {
 var dayTotalHours = dayEvents.reduce(function(total, event) {
   return total + (event.getEndTime() - event.getStartTime())/3600000;
 }, 0);

 var date = dayEvents[0].getStartTime().getDate();
 var col = date + 3;
 var range = sheet.getRange(24, col);
 if(range.isBlank()) {
   range.setValue(halfRound(dayTotalHours));
 }
}

// 0.5単位で四捨五入
function halfRound(num) {
 return Math.round(num * 2) / 2;
}

// セルの値を初期化（クリア）する
function initRange(sheet, startDate, endDate) {
 // 当月の日数を算出
 var thisMonthDays = (endDate - startDate) / 86400000 + 1;
 sheet.getRange(24,4,1,thisMonthDays).clearContent();
}

実行ボタン作成

スクリプトが完成したら、実行のトリガーとなるボタンを設置します。
これはとても簡単で任意のボタンに対して「スクリプトを割り当て」から関数名を設定するだけです。（今回の場合は、「myFunction」）

まとめ

魔王に「会心の一撃」を与えることができました。
しかし、戦いの中で魔王が繰り出した奥の手「勤怠管理アプリからエクスポートしたCSVをスプレッドシートに手動インポートする作業」に阻まれた（新たなルーチンワークに気づいてしまった）ため、あと一歩のところで討伐することはできませんでした。

魔王との戦いはまだしばらく続きそうです。

〜　閑話休題　〜

初めてGASを書いてみた所感としては以下の印象を受けました。
・ほぼJava Scriptの文法で書ける
・Googleアプリとの連携がとても簡単に実装できる（標準ライブラリが充実）
・ネットに日本語の情報がたくさんある
・ブラウザのスクリプトエディタでのコーディングはDX良くない（claspを使用することでほぼ解消）

総評として、Googleのサービスを使った業務の自動化をする上ではとても使いやすい、書いていて楽しいと感じました。
特に認証周りを自前で実装する必要がないのがとても嬉しい点でした。

最後に

弊社ではエンジニアを大募集中です。

昨年末に上場を果たし、今後さらなる拡大を図るためにはまだまだたくさんの仲間の力が必要です。

世の中の新しい当たり前を作っていきたい、ものづくりが好き、チームワークを大切にして成果を上げていきたいという想いがある方のご応募をお待ちしております。

株式会社スペースマーケットの募集 採用・求人情報 - Wantedly