Googleカレンダー 工数管理表を完全自動化してみた

この記事では前回の引き続きGoogleカレンダーから値をスプレッドシートに取り出し、 完全に工数管理表を自動化していこうと思います。

必要な項目の洗い出し

まずどんな情報を取得する必要があるのかです。 自分の場合は工数表のテンプレートが既にあるのでこれを元に洗い出します。

それぞれの項目はGoogleカレンダーのどの値として利用するのか決めておきます。

• No >> 行数
• 担当者名 >> 自分の名前
• 事業部・サービス名 >> 場所
• 分類 >> 色
• 業務 >> タイトル
• 内容 >> 詳細
• 所要時間合計（時間）>> 開始時間と終了時間から算出
• 所要時間合計（人日）>> 所要時間合計時間から算出

この場合、「行数の数 = 業務の数」なので、同じ業務名のものはまとめる様にスクリプトを書きました。

スクリプト全体

スクリプトファイルは以下の様に編集します。 内容の簡単な解説については以前の記事で書いたので知りたい方は下のリンクから

Googleカレンダー スプレッドシートに出力する方法

// SETTING
var CALENDAR_ID = "カレンダーID";
var PERSON_IN_CHARGE = "自分の名前";

var TYPE_NEW = 2;
var TYPE_MAINTENANCE = 4;
var TYPE_OTHER = 5;

//color code ↓
// 1:  lavender
// 2:  sage
// 3:  grape
// 4:  flamingo
// 5:  banana
// 6:  orange
// 7:  peacock
// 8:  graphite
// 9:  blueberry
// 10: basil
// 11: tomato
// -----------------------------------------

function getEvents() {
  var spreadSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var calendar = CalendarApp.getCalendarById(CALENDAR_ID);

  var firstDate = (function() {
      var d = new Date();
      d.setMonth(d.getMonth());
      d.setDate(1);
      return d;
  })();

  var lastDate = (function() {
    var d = new Date();
    d.setMonth(d.getMonth()+1);
    d.setDate(0);
    return d;
  })();

  var events = calendar.getEvents(firstDate, lastDate);
  var titles = [];
  var rows = [];

  for(var i=0; i<events.length; i++) {
    var no = i+1;
    var pic = PERSON_IN_CHARGE;
    var location = events[i].getLocation();
    var color = (events[i].getColor() != "") ? events[i].getColor() : "7";
    var title = events[i].getTitle();
    var description = events[i].getDescription();

    var start = events[i].getStartTime();
    var end = events[i].getEndTime();
    var workTime = (end.getTime() - start.getTime()) / (1000*60*60);

    var index = titles.indexOf(title+","+color);
    if(index < 0) {
      titles.push(title+","+color);
      var row = new Row(pic, location, title, description, color);
      row.setWorkTime(color, workTime);
      rows.push(row);
    } else {
      rows[index].setWorkTime(color, workTime);
      rows[index].addDescription(description);
    }

  }
  output(rows);
}



function output(rows) {
  var spreadSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  for(var i=0; i<rows.length; i++) {
    // spreadSheet.getRange(row, column)
    spreadSheet.getRange(3+i, 1).setValue(i+1);
    spreadSheet.getRange(3+i, 2).setValue(rows[i].pic);
    spreadSheet.getRange(3+i, 3).setValue(rows[i].service);
    var type = "";
    if(rows[i].type == TYPE_NEW) type = "新規";
    else if(rows[i].type == TYPE_MAINTENANCE) type = "保守・運用";
    else if(rows[i].type == TYPE_OTHER) type = "その他";

    //Logger.log(type);
    spreadSheet.getRange(3+i, 4).setValue(type);
    spreadSheet.getRange(3+i, 5).setValue(rows[i].title);
    spreadSheet.getRange(3+i, 6).setValue(rows[i].description);

    // color code
    spreadSheet.getRange(3+i, 7).setValue(rows[i].getWorkTime(TYPE_NEW));
    spreadSheet.getRange(3+i, 8).setValue(rows[i].getWorkTime(TYPE_MAINTENANCE));
    spreadSheet.getRange(3+i, 9).setValue(rows[i].getWorkTime(TYPE_OTHER));

    var allWorkTime = rows[i].getWorkTime(TYPE_NEW) + rows[i].getWorkTime(TYPE_MAINTENANCE) + rows[i].getWorkTime(TYPE_OTHER);
    spreadSheet.getRange(3+i, 10).setValue(allWorkTime);
    spreadSheet.getRange(3+i, 11).setValue(allWorkTime/8);
  }
}


var Row = function(pic, service, title, description, type){
  this.pic = pic;
  this.service = service;
  this.title = title;
  this.description = description;
  this.type = type;
  this.workTimes = [0,0,0,0,0,0,0,0,0,0,0];

  this.setWorkTime = function(type, hour) {
    this.workTimes[type-1] += hour;
  }

  this.getWorkTime = function(type) {
    return this.workTimes[type-1];
  }

  this.addDescription = function(newDescription) {
    this.description = this.description + ", " + newDescription;
  }
}

トリガーの設定

カレンダーを編集する度に工数表が更新する様にしたいので、更新した際にスクリプトが発火する様に設定をしていきます。

「編集 > 現在のスクリプトトリガー」

すると以下の様な画面が表示されます。

あとは「トリガーの追加」から、呼ぶスクリプトと発火するタイミングの設定をすれば完了です。

結果

カレンダーを更新すると自動的にスプレッドシートが更新されていました。

今時間をかけて今作って、後で掛かる時間を0にする。 自動化素晴らしいです。