この記事は UG Advent Calendar 2020 21日目の記事です。
10月末くらいだったかに来年の稼働計画を立てる時期が来た。 今年の実績から概算で入力すればOKだから昨年作った自分用計算シートに値を入れてコピペかなぁと思っていたら、なんと今年は金額ベースの計画入力だけでなく案件ごとの時間数まで入力するフォーマットに変わっていた...。
時間数となると2021のカレンダーに合わせて祝日や曜日のズレも考慮して計算しないといけない。なんてめんどくさいんだ。 入力期限明日って言われたしちまちまやってたら間に合わない(余裕持って確認しなかったのが悪いんですけどね)。
Googleカレンダーには全部入力してあるんだしそこから自動生成してくれたらいいのに。
と思ったので作りました。ちまちまデータ入力するより楽しかったです。
概要
この記事でできるようになること
- GASでGoogleカレンダーに入っている予定を取得してスプレッドシートに出力
- 複数のカレンダーも同時取得
- 日付範囲を指定して取得
- 特定の件名の予定は取得対象から除外する(正規表現)
- 終日の予定を除外する
目標物確認
こんなかんじのカレンダーがあります。
ここから移動、MTG、終日の予定を除外してプロジェクトごとの稼働の時間数だけを取得して、以下のような月別・プロジェクト別の時間数を集計します。
動作イメージ
「設定」シートに取得対象カレンダーIDやその他条件を入力後、上部メニューの GAS > カレンダー取得 をクリックすると「予定」シートに出力されます。
また、「集計」シートではピボットテーブルを件名と月で時間数を集計しています。
設定内容
休憩時間付与のしきい値(h) は指定の時間数を超える場合は休憩時間1Hを減算する設定です。
取得対象カレンダーIDはGoogleカレンダーの設定画面内にあるカレンダーの統合以下に記載されています。
完成品
細かい事はいいから完成品くれ って人用にスプレッドシートごと公開してますのでコピーして使ってください。
以下コード全文
// シート取得 const spreadSheet = SpreadsheetApp.getActiveSpreadsheet(); const sheetSchedules = spreadSheet.getSheetByName("予定"); const sheetSettings = spreadSheet.getSheetByName("設定"); // 設定値取得 const calendarIds = sheetSettings.getRange(1, 2).getValue().split(","); // カレンダーID配列 const reExclusion = new RegExp(sheetSettings.getRange(2, 2).getValue()); // 除外ワード const allDayExclusion = sheetSettings.getRange(3, 2).getValue(); // 終日の予定除外 const startDate = new Date(sheetSettings.getRange(4, 2).getValue()); // 取得開始日 const endDate = new Date(sheetSettings.getRange(5, 2).getValue()); // 取得終了日 const breakTimeThreshold = sheetSettings.getRange(6, 2).getValue(); // 休憩時間付与のしきい値 /** * メニュー追加 */ function onOpen() { const ui = SpreadsheetApp.getUi(); // UIクラス取得 const menu = ui.createMenu("GAS"); // メニュー名セット menu.addItem("Googleカレンダー取得", "getGoogleCalendar"); // 関数セット menu.addToUi(); // スプレッドシートに反映 } /** * 実行 */ function getGoogleCalendar() { // シート2行名以下をクリア let lastRow = sheetSchedules.getLastRow(); let lastColumn = sheetSchedules.getLastColumn(); sheetSchedules.getRange(2, 1, lastRow, lastColumn).clearContent(); // 配列初期化 let table = new Array(); // カレンダー数分ループ処理 for (let i = 0; i < calendarIds.length; i++) { // 取得結果の配列を追記 table = table.concat( fetchSchedules(calendarIds[i],reExclusion,allDayExclusion,startDate,endDate) ); } if (table.length) { sheetSchedules.getRange(2, 1, table.length, table[0].length).setValues(table); // シートに出力 Logger.log(`${table.length}件の予定を取得しました。`); spreadSheet.toast(`${table.length}件の予定を取得しました。`, 'Googleカレンダー取得完了', 5); // 完了メッセージ表示 } else { Logger.log(`${table.length}件の予定を取得しました。`); spreadSheet.toast('取得結果が0件です。', 'Googleカレンダー取得完了', 5); // エラーメッセージ表示 } } /** * GoogleカレンダーからgetEvents * @param {Array} calendarIds 取得対象カレンダーIDの配列 * @return {Array} 取得結果の二次元配列 */ function fetchSchedules(calendarId) { const schedules = new Array(); // 配列初期化 const calendar = CalendarApp.getCalendarById(calendarId); // カレンダー const calendarName = calendar.getName(); // カレンダー名 const events = calendar.getEvents(startDate, endDate); // 範囲内の予定を取得 // 各予定のデータを配列に追加 for (let i = 0; i < events.length; i++) { // 除外対象の場合は処理をスキップ if (isExclusion(events[i], reExclusion, allDayExclusion)) continue; let start = events[i].getStartTime(); let end = events[i].getEndTime(); let event = new Array(); event.push(calendarName); // カレンダー名 event.push(events[i].getTitle()); // 件名 event.push(start); // 開始日時 event.push(end); // 終了日時 event.push(start.getMonth() + 1); // 月 event.push(getOperatingTime(start, end)); // 時間数 event.push(events[i].getDescription()); // 詳細 schedules.push(event); // 配列に追加 } return schedules; } /** * 取得対象の切り分け * @param {CalendarEvent} schedule 個別のCalendarEventクラス * @return {boolean} 真偽値 */ function isExclusion(event) { // 終日イベントはスキップ if (allDayExclusion && event.isAllDayEvent()) return true; // 除外ワードを含む場合はスキップ if (reExclusion.test(event.getTitle())) return true; return false; } /** * 経過時間数の計算 * @param {Date} start 予定の開始日時 * @param {Date} end 予定の終了日時 * @return {number} 経過時間数 */ function getOperatingTime(start, end) { // 時間数算出 const time = (end - start) / 1000 / 60 / 60; // 休憩時間の減算 const operatingTime = time >= breakTimeThreshold ? time - 1 : time; return operatingTime; }
解説
それぞれの処理ごとにある程度コメントを書いてあるので不要かもしれませんが一応上から順に解説します。
設定値取得
まずはスクリプトのグローバル変数としてシートと設定値を取得。処理対象シートが複数ある場合は可読性も考慮して getSheetByName
で取得します。
シートの並びの変更は影響ないですが、シート名を変更すると取得できなくなります。 シートの保護で警告を設定しておくといいかも?
// シート取得 const spreadSheet = SpreadsheetApp.getActiveSpreadsheet(); const sheetSchedules = spreadSheet.getSheetByName("予定"); const sheetSettings = spreadSheet.getSheetByName("設定");
カレンダーIDは取得結果を split
で配列に変換。
除外ワードは取得と同時に正規表現オブジェクトを生成。
日付は取得と同時にDateオブジェクトを生成。
// 設定値取得 const calendarIds = sheetSettings.getRange(1, 2).getValue().split(","); // カレンダーID配列 const reExclusion = new RegExp(sheetSettings.getRange(2, 2).getValue()); // 除外ワード const allDayExclusion = sheetSettings.getRange(3, 2).getValue(); // 終日の予定除外 const startDate = new Date(sheetSettings.getRange(4, 2).getValue()); // 取得開始日 const endDate = new Date(sheetSettings.getRange(5, 2).getValue()); // 取得終了日 const breakTimeThreshold = sheetSettings.getRange(6, 2).getValue(); // 休憩時間付与のしきい値
メニュー追加
GASでは関数名 onOpen
とすることでスプレッドシートが開かれたと同時に呼び出される関数を利用できます。Excelで言うところの Workbook_Open
みたいなかんじ。
createMenu
でスプレッドシートに独自メニューを追加し、 addItem
で関数を実行するアイテムを追加しています。
function onOpen() { const ui = SpreadsheetApp.getUi(); // UIクラス取得 const menu = ui.createMenu("GAS"); // メニュー名セット menu.addItem("Googleカレンダー取得", "getGoogleCalendar"); // 関数セット menu.addToUi(); // スプレッドシートに反映 }
シートの初期化と結果のセット
getGoogleCalendar は「予定」シートの初期化、複数カレンダーを取得する設定の場合にカレンダーリストのループ処理を行い、 fetchSchedules
関数にカレンダーIDやその他の条件を渡すことで予定の配列を取得します。
最終的に全てのカレンダーの予定の配列を結合した上でシートに値をセットします。
concat
メソッドは初めて使いましたがイミュータブル(元のオブジェクトを変更しない)なので table = table.concat(<fetchSchedulesの結果>)
とすることで「配列に配列を追加」というのを実現しています。
function getGoogleCalendar() { // シート2行名以下をクリア let lastRow = sheetSchedules.getLastRow(); let lastColumn = sheetSchedules.getLastColumn(); sheetSchedules.getRange(2, 1, lastRow, lastColumn).clearContent(); // 配列初期化 let table = new Array(); // カレンダー数分ループ処理 for (let i = 0; i < calendarIds.length; i++) { // 取得結果の配列を追記 table = table.concat( fetchSchedules(calendarIds[i],reExclusion,allDayExclusion,startDate,endDate) ); } if (table.length) { sheetSchedules.getRange(2, 1, table.length, table[0].length).setValues(table); // シートに出力 Logger.log(`${table.length}件の予定を取得しました。`); spreadSheet.toast(`${table.length}件の予定を取得しました。`, 'Googleカレンダー取得完了', 5); // 完了メッセージ表示 } else { Logger.log(`${table.length}件の予定を取得しました。`); spreadSheet.toast('取得結果が0件です。', 'Googleカレンダー取得完了', 5); // エラーメッセージ表示 } }
カレンダーから予定を取得して結果を配列に代入
fetchSchedules では第一引数で受け取った calendarId
のカレンダーから指定日付範囲内の予定を一括で取得、取得結果をループ処理しながら除外条件にマッチする予定を除いて schedules
配列に格納して返します。
/** * GoogleカレンダーからgetEvents * @param {Array} calendarIds 取得対象カレンダーIDの配列 * @return {Array} 取得結果の二次元配列 */ function fetchSchedules(calendarId) { const schedules = new Array(); // 配列初期化 const calendar = CalendarApp.getCalendarById(calendarId); // カレンダー const calendarName = calendar.getName(); // カレンダー名 const events = calendar.getEvents(startDate, endDate); // 範囲内の予定を取得 // 各予定のデータを配列に追加 for (let i = 0; i < events.length; i++) { // 除外対象の場合は処理をスキップ if (isExclusion(events[i], reExclusion, allDayExclusion)) continue; let start = events[i].getStartTime(); let end = events[i].getEndTime(); let event = new Array(); event.push(calendarName); // カレンダー名 event.push(events[i].getTitle()); // 件名 event.push(start); // 開始日時 event.push(end); // 終了日時 event.push(start.getMonth() + 1); // 月 event.push(getOperatingTime(start, end)); // 時間数 event.push(events[i].getDescription()); // 詳細 schedules.push(event); // 配列に追加 } return schedules; }
この schedules
配列はシートにそのままセット出来るよう、以下のような二次元配列になっています。
[ ['稼働スケジュール','プロジェクトA','2021/01/04 09:00','2021/01/04 12:00',1,3,'プロジェクトAの詳細'], ['稼働スケジュール','プロジェクトB','2021/01/04 14:00','2021/01/04 18:00',1,4,'プロジェクトBの詳細'] ]
除外対象チェック
isExclusion では終日の予定を除外の設定にチェックが入っている かつ 予定が終日だった場合
または 除外ワードの正規表現にマッチする場合 に true
を返し、fetchSchedules関数での配列生成のループ処理で continue
することでスキップさせています。
getTitle
をfetchSchedulesとisExclusion で2回実行しちゃってるので呼び出し元で変数に入れておいても良かったかも。
/** * 取得対象の切り分け * @param {CalendarEvent} schedule 個別のCalendarEventクラス * @return {boolean} 真偽値 */ function isExclusion(event) { // 終日イベントはスキップ if (allDayExclusion && event.isAllDayEvent()) return true; // 除外ワードを含む場合はスキップ if (reExclusion.test(event.getTitle())) return true; return false; }
経過時間数の計算
getOperatingTime では開始日時と終了日時を受け取って範囲内の時間数を算出。
休憩時間の閾値を超える場合は1時間を減算して返します。
/** * 経過時間数の計算 * @param {Date} start 予定の開始日時 * @param {Date} end 予定の終了日時 * @return {number} 経過時間数 */ function getOperatingTime(start, end) { // 時間数算出 const time = (end - start) / 1000 / 60 / 60; // 休憩時間の減算 const operatingTime = time >= breakTimeThreshold ? time - 1 : time; return operatingTime; }
ピボットテーブルでの集計
「集計」シートのピボットテーブルは「予定」シートの A:G列を参照。
行に「件名」、列に「月」、値に「時間数」(集計: SUM)として 月別・プロジェクト別の工数表が出来上がりました。
空白行もデータソースになっているので 条件フィルタで「空白ではないセル」としておけばスッキリ。
あとがき
実は稼働スケジュールの取得のためにのみ作った段階ではメニューや設定シートなどは作っていませんでしたが、この記事を書くにあたってある程度使い勝手の良いものにしてから公開したいなと思って付け加えました。
年間の稼働スケジュールを集計する以外にも週報を自動生成したり、一定の時間数を超えそうになったらチャットにアラートを飛ばすなど色々カスタマイズの余地がありそうですね。
特定の件名を除外 とは逆に特定の文字列を含む予定だけ取得したい というニーズもあるでしょうか getEvents
の第3引数 options
を追加するのもいいかな。
参考: https://google-apps-script.net/instructions/?p=489#2
さくっと書こうと思ったらいろいろ欲が出て2時間くらいかかってしまいましたとさ。