🐘
GAS:Googleカレンダーに登録している予定をスプレッドシートに出力して工数管理をする
概要
Googleカレンダーの特定のカレンダーから、スプレッドシートで指定した期間の以下3つの情報を取得し、出力させることができる。
- タイトル
- 開始時間
- 終了時間
作業手順
- スプレッドシートを画像の通りに準備する
- C2とC3:メニューバーの「データ」>「データの入力規制」より、条件を「日付」にして保存する
- H3:
=SUM(H6:H)
- G5:
=QUERY(B5:E,"select B, sum(E) where E is not null group by B label sum(E) '時間数(h)'",1)
- スクリプトを追加する
- 下に掲載しているスクリプトの19行目「*****」の部分をカレンダーID[1]に置き換える
- onOpenを実行する
- メニューバーに「GAS」>「作業ログ取得」というアイテムが現れる
使い方
- C2とC3に、取得したい期間を入力する
- ダブルクリックするとカレンダーが表示されるので、選択すると良い
- ダブルクリックするとカレンダーが表示されるので、選択すると良い
- メニューバーの「GAS」>「作業ログ取得」をクリックする
おすすめの使い方
工数管理専用のカレンダーを作成して、予定を取得する。
Googleカレンダー作成手順
- PCでGoogleカレンダーを開く
- 左サイドバーのにある他のカレンダーの「+」をクリックする
- 「新しいカレンダーを作成」をクリックする
- 名前や説明を入力し、「カレンダーを作成」をクリックする
スクリプト
var ss = SpreadsheetApp.getActiveSpreadsheet();
//操作するシートを「作業ログ取得」に指定する
var sheet = ss.getSheetByName("作業ログ取得");
//メニューバーに「GAS > 作業ログ取得」というアイテムを追加する
function onOpen() {
var ui = SpreadsheetApp.getUi();
var menu = ui.createMenu("GAS");
menu.addItem("作業ログ取得","getCalendar");
menu.addToUi();
}
function getCalendar() {
//B6:Eのコンテンツをクリアする
sheet.getRange("B6:E").clearContent();
//*****の部分に取得するカレンダーのIDを入力する
var calendar = CalendarApp.getCalendarById('*****');
//getEventsメソッドにわたす開始日時(C2)と終了日時(C3)を求める
var startTime = new Date(sheet.getRange("C2").getValue());
var endTime = new Date(sheet.getRange("C3").getValue());
var events = calendar.getEvents(startTime, endTime);
for(var i = 0; i < events.length; i++) {
//最終行の下の行にデータを追加する
sheet.appendRow(
[
"", //A列は空白
events[i].getTitle(), //B列はタイトル
events[i].getStartTime(), //C列は開始時間
events[i].getEndTime(), //D列は終了時間
"=(INDIRECT(\"RC[-1]\",FALSE)-INDIRECT(\"RC[-2]\",FALSE))* 24" //E列は時間数(h)
]
);
}
}
-
PCでGoogleカレンダーを開き、予定を取得したいカレンダーのオーバーフローメニューで「設定と共有」をクリックする。カレンダーの統合の部分に「カレンダーID」が記載されている。 ↩︎
Discussion