LoginSignup
3
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@okame_qiita

GASでGoogleカレンダーの予定を自動集計Spreadsheet→GDPでビジュアライズ 前編

Last updated at Posted at 2020-07-31

いきなり成果発表！

GAS書きました。共有します。
https://github.com/kk0917/gcal-activity-spreadsheet-using-gas

コードファイルはこんな感じ。

**詳細はココをクリック**
src/gcalTotaling.js
// (function() {
  // getTodaySchedules()

// how to bulk fetching to specify a period.
// function getSchedulesTargetRange() {
//   let from = new Date(YYYY, (M - 1), 1); // M = from 0 to 11
//   let to   = new Date(YYYY, (M - 1), 1);
  
//   for(var d = from; d < to; d.setDate(d.getDate()+1)) { // end with yesterday of to Date value
//     let YEAR_STR  = d.getFullYear().toString();
//     let MONTH_STR = d.getMonth().toString().length == 2 ? (d.getMonth() + 1).toString() : '0' + (d.getMonth() + 1).toString();
//     let DATE_STR  = d.getDate().toString().length == 2 ? d.getDate().toString() : '0' + d.getDate().toString();
    
//     let SSHEET_NAME      = 'gcal-daily-activity-spreadsheet-' + YEAR_STR + MONTH_STR;
//     let ROOT_FOLDER_ID   = '***';

//     getTodaySchedules();
//     // include all function here using getTodaySchedules function and the reference function.
//   }
// }

const PRIVATE_EVENTS_ID        = '***@gmail.com';                 // Private Account
const PRIVATEWORKS_ID          = '***@group.calendar.google.com';
const TRAININGS_ID             = '***@group.calendar.google.com';
const CHORES_ID                = '***@group.calendar.google.com';  // 雑務
const ARCHITECT_AND_DEVELOP_ID = '***@group.calendar.google.com';  // 設計／開発
const RESEARCH_AND_VERIFY_ID   = '***@group.calendar.google.com';  // 調査／検証
const PRIVATE_THINGS_TODO_ID   = '***@group.calendar.google.com';  // 用事／移動@Private
const EVENTS_BY_CONNPASS_ID    = '***@import.calendar.google.com';
const DAC_EVENTS_ID            = '***@dac.co.jp';                  // DAC Account
const DAC_THINGS_TODO_ID       = '***@group.calendar.google.com';  // Mtg／移動@dac
const ZERO_DAC_EVENTS_ID       = '***@group.calendar.google.com';  // 第零@dac

const calendarsId = [
  PRIVATE_EVENTS_ID,
  PRIVATEWORKS_ID,
  TRAININGS_ID,
  CHORES_ID,
  ARCHITECT_AND_DEVELOP_ID,
  RESEARCH_AND_VERIFY_ID,
  PRIVATE_THINGS_TODO_ID,
  EVENTS_BY_CONNPASS_ID,
  DAC_EVENTS_ID,
  DAC_THINGS_TODO_ID, 
  ZERO_DAC_EVENTS_ID
];

const today = {
  // TODO: update read-only
  day: new Date(), // Month number needs to minus 1 when you insert date string as arguments. Date.getMonth() starts 0.
  getDay: function() {
    return this.day
  }
};

const YEAR_STR  = today.getDay().getFullYear().toString();
const MONTH_STR = today.getDay().getMonth().toString().length == 2 ? (today.getDay().getMonth() + 1).toString() : '0' + (today.getDay().getMonth() + 1).toString();
const DATE_STR  = today.getDay().getDate().toString().length == 2 ? today.getDay().getDate().toString() : '0' + today.getDay().getDate().toString();

const SSHEET_NAME      = 'gcal-daily-activity-spreadsheet-' + YEAR_STR + MONTH_STR;
const ROOT_FOLDER_ID   = '***';

function getTodaySchedules() {
  try {
    let folder    = getTargetFolder();
    let file       = getTargetFile(folder);
    let calendars = getCalendars(calendarsId);
    let events    = getEventsExceptAllDay(calendars)

    writeSpreadSheet(SpreadsheetApp.open(file), events); //the file needs to convert FileApp class to SpreadshetApp class
  } catch (error) {
    Logger.log(error);
  }
}

function getTargetFolder() {
  let rootFolder = DriveApp.getFolderById(ROOT_FOLDER_ID);
  let yearFolder = rootFolder.getFoldersByName(YEAR_STR);

  if (yearFolder.hasNext()) {
    return yearFolder.next();

  } else {
    let newFolder = DriveApp.createFolder(YEAR_STR);
    newFolder.moveTo(rootFolder);

    return newFolder;
  }
}

function getTargetFile(folder) {
  let file = folder.getFilesByName(SSHEET_NAME);
  if (file.hasNext()) {
    return file.next();

  } else {
    let sSheet = SpreadsheetApp.create(SSHEET_NAME);
    let file    = DriveApp.getFileById(sSheet.getId());

    file.moveTo(folder);

    return file;
  }
}

function getCalendars(calendarsId) {
  var calendars = [];

  calendarsId.map(function(id) {
    calendars.push(CalendarApp.getCalendarById(id));
  });

  return calendars;
}

function getEventsExceptAllDay(calendars) {
  var events = [];

  calendars.map(function(cal) {
    let _today      = today.getDay();
    let todayEvents = cal.getEventsForDay(_today);

    todayEvents.map(function(event) {
      var bool = exceptAllDayEvents(event);

      if (bool) events.push(event);
    });
  });

  return events;
}

function exceptAllDayEvents(event) {
  let startTime     = event.getStartTime().toTimeString().slice(0, 8);
  let endTime       = event.getEndTime().toTimeString().slice(0, 8);
  let isNotAllEvent = (startTime != '00:00:00' && endTime != '00:00:00') ? true : false;

  return isNotAllEvent;
}

function writeSpreadSheet(sSheet, events) {
  let sheet = insertSheetForToday(sSheet);

  if (sheet != null) {
    writeEventInfoToSheet(sheet, events);
  }
};

function insertSheetForToday(sSheet) {
  if (sSheet.getSheetByName(DATE_STR) == null) {
    return sSheet.insertSheet(DATE_STR, sSheet.getNumSheets());
  }
}

function writeEventInfoToSheet(sheet, events) {
  events.map(function(event, i) {
    var calendar  = CalendarApp.getCalendarById(event.getOriginalCalendarId());
    var calName   = calendar != null ? calendar.getName() : 'event@DAC';
    var eventName = event.getTitle() != '' ? event.getTitle() : '予定あり';
    var startTime = event.getStartTime().toTimeString().slice(0, 8);
    var endTime   = event.getEndTime().toTimeString().slice(0, 8);
    var totalTime = getActivityTime(event, events);

    sheet.getRange(i + 1, 1).setValue(calName);
    sheet.getRange(i + 1, 2).setValue(eventName);
    sheet.getRange(i + 1, 3).setValue(startTime);
    sheet.getRange(i + 1, 4).setValue(endTime);
    sheet.getRange(i + 1, 5).setValue(totalTime);
  });
}

function getActivityTime(event, events) {
  var hours        = event.getEndTime().getHours() - event.getStartTime().getHours();
  var minutes      = event.getEndTime().getMinutes() - event.getStartTime().getMinutes();
  [hours, minutes] = calcTotalTime(hours, minutes);

  events.map(function(_event) {
    if (event.getId() != _event.getId() && event.getStartTime() <= _event.getStartTime() && event.getEndTime() >= _event.getEndTime()) {
      var _hours         = _event.getEndTime().getHours() - _event.getStartTime().getHours();
      var _minutes       = _event.getEndTime().getMinutes() - _event.getStartTime().getMinutes();
      [_hours, _minutes] = calcTotalTime(_hours, _minutes);

      hours   -= _hours;
      minutes -= _minutes;

      [hours, minutes] = calcTotalTime(hours, minutes);
    }
  });

  return hours + ':' + minutes + ':00';

  function calcTotalTime(hours, minutes) {
    if (minutes < 0) {
      --hours;
      minutes = 60 + minutes;
    }

    return [hours, minutes];
  }
}

// TODO: make mv column function if there isn't the existing same like function
function moveColumnToRight() {
  
}
// })();

集計結果。日別でシートを分けて集計。シート1で関数組んでおいて1月分を集計（テンプレ用意）。

68efd06b-2c54-d75f-3091-9b5c17fe90b1.png

まぁ項目とかは後で増やせるんで現時点では最低限の集計しかしていません。
毎日、予定が全て確定・終了しているであろう22時台（分指定不可）にスクリプトが実行されるようをトリガーを設定しています。一月分をSS1ファイルにして日別でシートを挿入するようにしています。

背景: もやもやしてたこと

  • タスクや作業工数をGoogleカレンダーで管理しているが特に活かせていない。
  • 日々の業務でどれだけ開発に時間を費やせているか？を簡単に数値化、可視化したい。
    • 少ないとヘコむ→ヤバいもっとコード書かなきゃ！とモチベーション担保する
  • ついでにDaily/Monthlyでタスク毎、業務種別毎に時間管理でもすっぺか。
  • どうせやるならプログラマーらしくコード書いて自動化したいぜ

...まぁググって探せば同じようなの出てくると思うのでちょっと車輪の再発明っぽい匂いがプンプンですが💦

現状: Gカレの使い方

皆さんどんな感じでGカレ使ってます？Mtgや予定のみが入っている感じですか？
なんらかタスクの作業時間も入力してますか？

わたしゃこんな感じです。

ある週のカレンダー

0fe43aaf-acf4-24e2-c3c3-372481c71109.png

これは個人アカウントのカレンダーです。業務時間以外のプライベートな予定とかも入ってます。
左側のカレンダーでカテゴライズして管理しています。会社の予定は会社アカウントのカレンダーIDで引っ張ってきています（詳細は見れず「予定あり」としか表示されませんが）。

これをGASで集計してスプレッドシートに出力→GDPで取り込みビジュアライズします。

環境

  • Googleアカウント
    • 会社の予定、稼働時間のみを確認したいかたは会社アカウントで。
  • Google Apps Script
    • スプレッドシートから直接 ツールメニュー > スクリプトエディタ を開くかGASのダッシュボード的なのから新規でプロジェクト作るかどちらでも良いかと。
  • 言語：JavaScript
  • clasp
    • ローカル環境でVS Codeで書いたコードをGASにpushするやつ。

ディレクトリ構成

ルート直下にYearフォルダがあってその１階層下に月次ファイルが格納されてく感じです。

root
|----2020
|---- gcal-daily-activity-spreadsheet-202007
|---- gcal-daily-activity-spreadsheet-202006
|---- gcal-daily-activity-spreadsheet-202005
|---- ...
|----2019
|---- ...

96340fb6-3324-370a-0a18-5808e7302f01.png

実行手順

事前準備

  • Google Drive上にスプレッドシートを格納するフォルダを用意しておく
    • ブラウザで対象フォルダを開いている状態でURL欄のhttps://drive.google.com/drive/folders/abcdef***ココがフォルダID***1234をひかえておく
  • 集計したいカレンダーのカレンダーIDを控えておく

GAS実行手順

  1. GASダッシュボードで設定したトリガーでスクリプト実行
  2. 事前にひかえておいたルートフォルダIDでSSファイル格納フォルダにアクセス
  3. 今年のフォルダが無ければ作る。あれば作らない
  4. 今年のフォルダにアクセスし今月分のSS集計ファイルが無ければ作る。あれば作らない
  5. 今月分のSS集計ファイルにアクセスし当日分の集計シートが無ければ作る。あれば（何らかの事情でスクリプト手動実行したとか）作らない。
  6. 当日分の予定・作業・活動をカレンダー別・イベント別に集計。カレンダー名やタイトル名、開始時間や終了時間、イベント毎の作業時間など自分が集計したい項目を当日シートに出力

以上です。

イベント毎の作業時間集計時のポイント

終日イベントは集計に含めない

時間の概念が無いので不要です。コードはこんな感じです。

L99-111
function getEventsExceptAllDay(calendars) {
  var events = [];

  calendars.map(function(cal) {
    let _today      = today.getDay();
    let todayEvents = cal.getEventsForDay(_today);

    todayEvents.map(function(event) {
      var bool = exceptAllDayEvents(event);

      if (bool) events.push(event);
    });
  });

  return events;
}

function exceptAllDayEvents(event) {
  let startTime     = event.getStartTime().toTimeString().slice(0, 8);
  let endTime       = event.getEndTime().toTimeString().slice(0, 8);
  let isNotAllEvent = (startTime != '00:00:00' && endTime != '00:00:00') ? true : false;

  return isNotAllEvent;
}

開始時間と終了時間が共に00:00:00になっているイベントを排除します。
1日だけではなく数日間とか日を跨ぐ終日イベントもありますが、どちらにしても開始時間と終了時間は必ず00:00:00になるのでここでは時間しか見ていません。

重複時間を差し引く
先ほどのGカレだと、

30716568-e1d3-57d5-06e3-f46481e59f6f.png

こんな感じで作業の合間合間で差し込み作業が入ってマルチタスクってるところがあります。
この日の予定でいうと「調査／検証」の合計時間から「hogehoge」と「fugafuga」の合計時間をマイナスしてあげないといけないですね。
なのでイベント毎に「開始時間と終了時間の間に他のイベント入ってたら作業時間マイナスする」みたいなことをします。まぁ簡単に言えばただループ回すだけですけどね💦

L155-183
function getActivityTime(event, events) {
  var hours        = event.getEndTime().getHours() - event.getStartTime().getHours();
  var minutes      = event.getEndTime().getMinutes() - event.getStartTime().getMinutes();
  [hours, minutes] = calcTotalTime(hours, minutes);

  events.map(function(_event) {
    if (event.getId() != _event.getId() && event.getStartTime() <= _event.getStartTime() && event.getEndTime() >= _event.getEndTime()) {
      var _hours         = _event.getEndTime().getHours() - _event.getStartTime().getHours();
      var _minutes       = _event.getEndTime().getMinutes() - _event.getStartTime().getMinutes();
      [_hours, _minutes] = calcTotalTime(_hours, _minutes);

      hours   -= _hours;
      minutes -= _minutes;

      [hours, minutes] = calcTotalTime(hours, minutes);
    }
  });

  return hours + ':' + minutes + ':00';

  function calcTotalTime(hours, minutes) {
    if (minutes < 0) {
      --hours;
      minutes = 60 + minutes;
    }

    return [hours, minutes];
  }
}

うーん、リファクタしがいのあるコードですねw レビュー求む！ :pray:

前編は以上！

ざっとこんな感じです。集計項目増やしたり出力の仕方考えたりとか若干のチューニングと汚コードのリファクタｗ はあるかと思いますがだいたいこんなので伝わりましたでしょうか？

後編ではGoogle Data Portalへの取り込みビジュアライズを紹介します。
ではまた！

3
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?