MENU

【Googleカレンダー】Googleカレンダーで作業時間集計してスプレッドシートで円グラフ化する

Googleカレンダーで毎日予定を管理していて、時間集計を簡単にできないものかと考えました。
今回は、簡単に時間集計と全体の割合を円グラフにしてくれるスクリプトをメモします。

目次

スプレッドシートを新しく作成する

ドライブから、「スプレッドシート」を選択して開きます。

作成ができたら、わかりやすいファイル名をつけてあげましょう。

Apps Scriptでスクリプトを作成する

次に、「拡張機能」内の「Apps Script」を選択します。

別タブで画面が開き、以下のような画面が表示されます。

この画面中央の部分にカレンダーを集計するコードを書いて実行すると、スプレッドシートに出力できるようになります。
実際にやっていきましょう。

以下のコードを全てコピーし、貼り付けます。

/*
以下の値を設定することで集計する期間を設定可能。(形式: YYYY/MM/DD, YYYY/MM/DD HH:mm:ss)
start: 開始日時
end: 終了日時
*/
var start = "2021/12/01 00:00:00";
var end = "2022/01/01 00:00:00";

function CalendarToSpreadSheet() {
  // 所要時間を整形するクラス
  var DurationToStr = function(duration) {
    this.duration = duration;
    this.culc = function() {
      var dur_h = Math.floor(this.duration / 60);  // 時
      var dur_m = this.duration - (dur_h * 60);    // 分
      // スプレッドシートの表示形式で経過時数01、経過分数01にすると桁がそろって見栄えが良い
      var dur_h_str = ('00' + dur_h).slice(-2);
      var dur_m_str = ('00' + dur_m).slice(-2);
      return dur_h_str + ':' + dur_m_str;
    }
  }

  // カレンダーから取得したイベントを整形して保存しておくクラス
  var SimpleEvent = function(title, duration) {
    this.title = title;
    this.duration = duration;
    this.getDurationStr = function() {
      return new DurationToStr(this.duration).culc();
    };
  };

  // ログインしているユーザのカレンダーを取得
  var calendar = CalendarApp.getCalendarById(Session.getActiveUser().getEmail());

  // 現在の週を取得
  var today = new Date();
  var thisYear = today.getFullYear();
  var thisMonth = today.getMonth();
  var thisDate = today.getDate();
  var thisDay = today.getDay();
  var thisSunday = thisDate - thisDay;
  var nextSunday = thisSunday + 7;
  // 日曜日の年月日
  if (start === "") {
    var startDate = new Date(thisYear, thisMonth, thisSunday);
  } else {
    var startDate = new Date(start);
  }
  // 次の日曜日の年月日
  if (end === "") {
    var endDate = new Date(thisYear, thisMonth, nextSunday);
  } else {
    var endDate = new Date(end);
    endDate.setDate(endDate.getDate() + 1);
  }
  // シート名を作成
  var startDateStr = startDate.getFullYear() + "/" + (startDate.getMonth() + 1) + "/" + startDate.getDate();
  var endDateStr = endDate.getFullYear() + "/" + (endDate.getMonth() + 1) + "/" + (endDate.getDate() - 1);
  var week = startDateStr + " - " + endDateStr;

  // アクティブなスプレッドシートを取得
  var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  // 今週のシートが既にあれば消す
  var oldSheet = spreadSheet.getSheetByName(week);
  if (oldSheet) {
    spreadSheet.deleteSheet(oldSheet);
  }
  // 新しいシートを作成
  spreadSheet.insertSheet(week);  
  // シートを取得
  var sheet = SpreadsheetApp.getActiveSheet();

  // イベントを取得
  var events = calendar.getEvents(startDate, endDate);
  // 読み出したイベントを登録しておく配列
  var readEvent = [];
  // イベントの読み出し
  for (var i = 0; i < events.length; i++) {
    var title = events[i].getTitle();         // 予定のタイトル
    var startTime = events[i].getStartTime(); // 開始時刻
    var endTime = events[i].getEndTime();     // 終了時刻
    // 所要時間の計算
    var duration = (endTime - startTime) / (1000 * 60);
    // SimpleEvent クラスを生成
    var simpleEvent = new SimpleEvent(title, duration);

    // 配列に追加済みか判定するフラグ
    var added = false;
    // 所要時間を足し上げ
    for (var j = 0; j < readEvent.length; j++) {
      if (title == readEvent[j].title) {
        readEvent[j].duration = readEvent[j].duration + duration;
        added = true;
        break;
      }
    }
    if (!added) {
      readEvent.push(simpleEvent);
    }
  }

  // 各行のタイトルを指定
  sheet.getRange('A'+(1)).setValue('予定名');
  sheet.getRange('B'+(1)).setValue('所要時間');
  // シートに出力
  for (var i = 0; i < readEvent.length; i++) {
    sheet.getRange('A'+(i+2)).setValue(readEvent[i].title);
    sheet.getRange('B'+(i+2)).setValue(readEvent[i].getDurationStr());
  }

  // 合計を出力
  var sumDuration = 0;
  for (var i = 0; i < readEvent.length; i++) {
    sumDuration = sumDuration + readEvent[i].duration;
  }
  sheet.getRange('A'+(readEvent.length+2)).setValue("合計");
  sheet.getRange('B'+(readEvent.length+2)).setValue(new DurationToStr(sumDuration).culc());

  // 円グラフを作成
  var range=sheet.getRange("A1:B" + String(readEvent.length+1));
  var chart=sheet.newChart()
    .addRange(range)
    .setChartType(Charts.ChartType.PIE)
    .setOption("pieSliceText", "value")
    .setPosition(1,4,0,0)
    .build()
  sheet.insertChart(chart);
}

貼り付けが終わると、以下の画像のようになっていると思います。
スクリプト名をテキトーにつけて、(ここでは「Calendar_total」ってつけています。)「プロジェクトを保存」をクリックします。

「プロジェクトの保存」が完了すると、隣の「実行」ボタンがクリックできるようになっているので、「実行」を押してみます。

指定した期間のカレンダー情報がスプレッドシート上に出力されます。
(予定名の部分はスケジュールのタイトルが表示されるようになります。)

よかったらシェアしてね!

この記事を書いた人

目次
閉じる