🐘

GAS:Googleカレンダーに登録している予定をスプレッドシートに出力して工数管理をする

2022/01/15に公開

概要

Googleカレンダーの特定のカレンダーから、スプレッドシートで指定した期間の以下3つの情報を取得し、出力させることができる。

  • タイトル
  • 開始時間
  • 終了時間

作業手順

  1. スプレッドシートを画像の通りに準備する
    • 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)
  2. スクリプトを追加する
    • 下に掲載しているスクリプトの19行目「*****」の部分をカレンダーID[1]に置き換える
  3. onOpenを実行する
  4. メニューバーに「GAS」>「作業ログ取得」というアイテムが現れる

使い方

  1. C2とC3に、取得したい期間を入力する
    • ダブルクリックするとカレンダーが表示されるので、選択すると良い
  2. メニューバーの「GAS」>「作業ログ取得」をクリックする

おすすめの使い方

工数管理専用のカレンダーを作成して、予定を取得する。

Googleカレンダー作成手順

  1. PCでGoogleカレンダーを開く
  2. 左サイドバーのにある他のカレンダーの「+」をクリックする
  3. 「新しいカレンダーを作成」をクリックする
  4. 名前や説明を入力し、「カレンダーを作成」をクリックする

スクリプト

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)
      ]
    );
  }
}
脚注
  1. PCでGoogleカレンダーを開き、予定を取得したいカレンダーのオーバーフローメニューで「設定と共有」をクリックする。カレンダーの統合の部分に「カレンダーID」が記載されている。 ↩︎

Discussion