GASを使ってGoogleカレンダーの予定をスプレッドシートに取り込む★★☆

便利なGASレシピ集！に戻る場合は以下から

便利なGASレシピ集！ – [2019年最新版]

2019年5月22日

Contents

はじめに
1.GASコード
2.コード説明

はじめに

今回のビジボットでは、タイトル通り『GASを使ってGoogleカレンダーの予定をスプレッドシートに取り込む』方法を紹介していきます。よく、カレンダーの中にある情報をExcelにDLできるサイトを見かけますが、GASで記述するので、例えばタイトルを統一した文言で『【導入】』ってしていた場合、それだけをスプレッドシートに取り込んだりかなり自由に出力することが可能です。
※今回はフィルター条件を入れたりはしていません

[Chatwork版]GASでGoogleフォームの結果を通知したい！-2019年版

2019年4月16日

GASで今日のGoogleカレンダーをSlackに通知する！ – 2019年版

2019年4月10日

GASを使って複数メンバーのGoogleカレンダーをスプレッドシートに取り込む★★☆ – ビジボット

2019年2月21日

それでは早速始めていきます！

1.GASコード

POINT

まずは完成コードを記載していきます。概要を簡単に説明すると、どのGoogleカレンダーから情報を取得するかGoogleカレンダーIDを取得し、そのIDに対して指定した期間の情報を出力してスプレッドシートに反映する。というシンプルな作りです。
詳細については後ほど説明していきます！

GASコード

function getCal() {
  
  //カレンダーIDを指定して、カレンダーを取得
  var calendar = CalendarApp.getCalendarById('********************');

  //対象の日付を範囲指定
  var date = '2019/01/01'; 
  //この時はまだfrom,end両方に変数dateで指定した日付が入っている
  var from= new Date(date);　
  var end = new Date(date);
  //月を指定するときは必ず大文字のMMを指定します（半角mmだとminutesを指してしまう）
  Logger.log('from:' + Utilities.formatDate( from, 'Asia/Tokyo', 'yyyy-MM-dd'));
  Logger.log('end:' + Utilities.formatDate( end, 'Asia/Tokyo', 'yyyy-MM-dd'));


  //endのMonthｍ（月）に+1をして翌月を指定
  end.setMonth(end.getMonth()+1);
  Logger.log('== setMonth、getMonthでendに+1ヶ月後 =====');
  Logger.log('from:' + Utilities.formatDate( from, 'Asia/Tokyo', 'yyyy-MM-dd'));
  Logger.log('end:' + Utilities.formatDate( end, 'Asia/Tokyo', 'yyyy-MM-dd'));
  
  //getEventsで取得したいカレンダーの始まりと終わりを指定して引数にセット
  var events = calendar.getEvents(from, end);


  //アクティブなシートを取得
  var sheet = SpreadsheetApp.getActiveSheet();

  //ヘッダ行に入る値を指定（誤って削除しても都度指定）
  sheet.getRange('A'+(1)).setValue('タイトル');
  sheet.getRange('B'+(1)).setValue('開始時刻');
  sheet.getRange('C'+(1)).setValue('終了時刻');
  sheet.getRange('D'+(1)).setValue('所要時間');
  sheet.getRange('E'+(1)).setValue('詳細');
  sheet.getRange('F'+(1)).setValue('住所');

  //eventsに入っている要素の数だけ実行
  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)/(60);
    var detail = events[i].getDescription();
    var location = events[i].getLocation();

    //取得した値をスプレッドシートに反映
    sheet.getRange('A'+(i+2)).setValue(title);
    sheet.getRange('B'+(i+2)).setValue(startTime).setNumberFormat('yyyy/mm/dd hh:mm');
    sheet.getRange('C'+(i+2)).setValue(endTime).setNumberFormat('yyyy/mm/dd hh:mm');
    sheet.getRange('D'+(i+2)).setValue(duration);
    //sheet.getRange('E'+(i+2)).setValue(detail);
    sheet.getRange('F'+(i+2)).setValue(location);
  }
}

function getCal() {

//カレンダーIDを指定して、カレンダーを取得

var calendar = CalendarApp.getCalendarById('********************');

//対象の日付を範囲指定

var date = '2019/01/01';

//この時はまだfrom,end両方に変数dateで指定した日付が入っている

var from= new Date(date);　

var end = new Date(date);

//月を指定するときは必ず大文字のMMを指定します（半角mmだとminutesを指してしまう）

Logger.log('from:' + Utilities.formatDate( from, 'Asia/Tokyo', 'yyyy-MM-dd'));

Logger.log('end:' + Utilities.formatDate( end, 'Asia/Tokyo', 'yyyy-MM-dd'));

//endのMonthｍ（月）に+1をして翌月を指定

end.setMonth(end.getMonth()+1);

Logger.log('== setMonth、getMonthでendに+1ヶ月後 =====');

Logger.log('from:' + Utilities.formatDate( from, 'Asia/Tokyo', 'yyyy-MM-dd'));

Logger.log('end:' + Utilities.formatDate( end, 'Asia/Tokyo', 'yyyy-MM-dd'));

//getEventsで取得したいカレンダーの始まりと終わりを指定して引数にセット

var events = calendar.getEvents(from, end);

//アクティブなシートを取得

var sheet = SpreadsheetApp.getActiveSheet();

//ヘッダ行に入る値を指定（誤って削除しても都度指定）

sheet.getRange('A'+(1)).setValue('タイトル');

sheet.getRange('B'+(1)).setValue('開始時刻');

sheet.getRange('C'+(1)).setValue('終了時刻');

sheet.getRange('D'+(1)).setValue('所要時間');

sheet.getRange('E'+(1)).setValue('詳細');

sheet.getRange('F'+(1)).setValue('住所');

//eventsに入っている要素の数だけ実行

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)/(60);

var detail = events[i].getDescription();

var location = events[i].getLocation();

//取得した値をスプレッドシートに反映

sheet.getRange('A'+(i+2)).setValue(title);

sheet.getRange('B'+(i+2)).setValue(startTime).setNumberFormat('yyyy/mm/dd hh:mm');

sheet.getRange('C'+(i+2)).setValue(endTime).setNumberFormat('yyyy/mm/dd hh:mm');

sheet.getRange('D'+(i+2)).setValue(duration);

//sheet.getRange('E'+(i+2)).setValue(detail);

sheet.getRange('F'+(i+2)).setValue(location);

}

詳細説明は後ほどしていきます。
以下はLogger.log()で出力されたログ結果です。

GASログ

[19-02-20 00:52:47:567 JST] from:2018-11-01
[19-02-20 00:52:47:568 JST] end:2018-11-01
[19-02-20 00:52:47:569 JST] == setMonth、getMonthでendに+1ヶ月後 =====
[19-02-20 00:52:47:569 JST] from:2018-11-01
[19-02-20 00:52:47:570 JST] end:2018-12-01

[19-02-20 00:52:47:567 JST] from:2018-11-01

[19-02-20 00:52:47:568 JST] end:2018-11-01

[19-02-20 00:52:47:569 JST] == setMonth、getMonthでendに+1ヶ月後 =====

[19-02-20 00:52:47:569 JST] from:2018-11-01

[19-02-20 00:52:47:570 JST] end:2018-12-01

2.コード説明

POINT

まず、取得したいカレンダーIDを取得します。「******************」に入る部分を以下方法で取得します。
//カレンダーIDを指定して、カレンダーを取得
var calendar = CalendarApp.getCalendarById(”);

カレンダーの左側に表示されるメニューをクリックし、『設定と共有』をクリックします。

カレンダーID確認方法 - ビジボット — カレンダーID確認方法 – ビジボット

画面を下までスクロールすると『カレンダーの統合』という項目があります。
この中にある『カレンダーID』が「******************」に入る部分です。

日付を取得 – from

次に、取得したいデータ範囲を指定します。
変数 date に取得したい日付を指定して、それらを変数 from と変数 end に入れます。
※Logger.log()では素のままだと見にくいのでタイムゾーンと表示形式を整形してます

GASコード

//対象の日付を範囲指定
  var date = '2019/01/01'; 
  //この時はまだfrom,end両方に変数dateで指定した日付が入っている
  var from= new Date(date);　
  var end = new Date(date);
  //月を指定するときは必ず大文字のMMを指定します（半角mmだとminutesを指してしまう）
  Logger.log('from:' + Utilities.formatDate( from, 'Asia/Tokyo', 'yyyy-MM-dd'));
  Logger.log('end:' + Utilities.formatDate( end, 'Asia/Tokyo', 'yyyy-MM-dd'));

//対象の日付を範囲指定

var date = '2019/01/01';

//この時はまだfrom,end両方に変数dateで指定した日付が入っている

var from= new Date(date);　

var end = new Date(date);

//月を指定するときは必ず大文字のMMを指定します（半角mmだとminutesを指してしまう）

Logger.log('from:' + Utilities.formatDate( from, 'Asia/Tokyo', 'yyyy-MM-dd'));

Logger.log('end:' + Utilities.formatDate( end, 'Asia/Tokyo', 'yyyy-MM-dd'));

ログ結果はこの時点ではfromとendの時間は共通になってます。

GASログ

[19-02-20 00:52:47:567 JST] from:2018-11-01
[19-02-20 00:52:47:568 JST] end:2018-11-01

1 2	[19-02-20 00:52:47:567 JST] from:2018-11-01 [19-02-20 00:52:47:568 JST] end:2018-11-01

日付を取得 – end

次に、取得した時間に対して終わりの時間を指定します。
fromとendの時間は共通なので、endの時間にsetMonthを使って、getMonth() + 1します。
すると、endの時間だけ1ヶ月分ずれた状態でセットされるのでそれをカレンダーの取得条件として引数に渡します。

GASコード

//endのMonthｍ（月）に+1をして翌月を指定
  end.setMonth(end.getMonth()+1);
  Logger.log('== setMonth、getMonthでendに+1ヶ月後 =====');
  Logger.log('from:' + Utilities.formatDate( from, 'Asia/Tokyo', 'yyyy-MM-dd'));
  Logger.log('end:' + Utilities.formatDate( end, 'Asia/Tokyo', 'yyyy-MM-dd'));
  
  //getEventsで取得したいカレンダーの始まりと終わりを指定して引数にセット
  var events = calendar.getEvents(from, end);

//endのMonthｍ（月）に+1をして翌月を指定

end.setMonth(end.getMonth()+1);

Logger.log('== setMonth、getMonthでendに+1ヶ月後 =====');

Logger.log('from:' + Utilities.formatDate( from, 'Asia/Tokyo', 'yyyy-MM-dd'));

Logger.log('end:' + Utilities.formatDate( end, 'Asia/Tokyo', 'yyyy-MM-dd'));

//getEventsで取得したいカレンダーの始まりと終わりを指定して引数にセット

var events = calendar.getEvents(from, end);

ログ結果はこの時点でfromとendで1ヶ月の差が出来ます。
※この場合2018-12-01 00:00:00 より小さい データを指しています

GASログ

[19-02-20 00:52:47:569 JST] == setMonth、getMonthでendに+1ヶ月後 =====
[19-02-20 00:52:47:569 JST] from:2018-11-01
[19-02-20 00:52:47:570 JST] end:2018-12-01

[19-02-20 00:52:47:569 JST] == setMonth、getMonthでendに+1ヶ月後 =====

[19-02-20 00:52:47:569 JST] from:2018-11-01

[19-02-20 00:52:47:570 JST] end:2018-12-01

スプレッドシートに反映

最後にスプレッドシートに取得した情報をセットする部分を見ていきます。
まず、操作しているスプレッドシートのヘッダ行に入る値をプログラム側でセットしておきます。
※直接スプレッドシートに記載して、コードは無しでもいいですが誤って消した時に便利です

次に、カレンダーから取得した値を取得した要素分for文で回し、取得した結果をスプレッドシートに反映します。

※var duration = (endTime – startTime) /(1000*60)/(60);は1時間を1として見た時の所要時間が表示されてます

GASコード

  //アクティブなシートを取得
  var sheet = SpreadsheetApp.getActiveSheet();

  //ヘッダ行に入る値を指定（誤って削除しても都度指定）
  sheet.getRange('A'+(1)).setValue('タイトル');
  sheet.getRange('B'+(1)).setValue('開始時刻');
  sheet.getRange('C'+(1)).setValue('終了時刻');
  sheet.getRange('D'+(1)).setValue('所要時間');
  sheet.getRange('E'+(1)).setValue('詳細');
  sheet.getRange('F'+(1)).setValue('住所');

  //eventsに入っている要素の数だけ実行
  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)/(60);
    var detail = events[i].getDescription();
    var location = events[i].getLocation();

    //取得した値をスプレッドシートに反映
    sheet.getRange('A'+(i+2)).setValue(title);
    sheet.getRange('B'+(i+2)).setValue(startTime).setNumberFormat('yyyy/mm/dd hh:mm');
    sheet.getRange('C'+(i+2)).setValue(endTime).setNumberFormat('yyyy/mm/dd hh:mm');
    sheet.getRange('D'+(i+2)).setValue(duration);
    //sheet.getRange('E'+(i+2)).setValue(detail);
    sheet.getRange('F'+(i+2)).setValue(location);
  }

//アクティブなシートを取得

var sheet = SpreadsheetApp.getActiveSheet();

//ヘッダ行に入る値を指定（誤って削除しても都度指定）

sheet.getRange('A'+(1)).setValue('タイトル');

sheet.getRange('B'+(1)).setValue('開始時刻');

sheet.getRange('C'+(1)).setValue('終了時刻');

sheet.getRange('D'+(1)).setValue('所要時間');

sheet.getRange('E'+(1)).setValue('詳細');

sheet.getRange('F'+(1)).setValue('住所');

//eventsに入っている要素の数だけ実行

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)/(60);

var detail = events[i].getDescription();

var location = events[i].getLocation();

//取得した値をスプレッドシートに反映

sheet.getRange('A'+(i+2)).setValue(title);

sheet.getRange('B'+(i+2)).setValue(startTime).setNumberFormat('yyyy/mm/dd hh:mm');

sheet.getRange('C'+(i+2)).setValue(endTime).setNumberFormat('yyyy/mm/dd hh:mm');

sheet.getRange('D'+(i+2)).setValue(duration);

//sheet.getRange('E'+(i+2)).setValue(detail);

sheet.getRange('F'+(i+2)).setValue(location);

}

実行後に完了すると、指定した列タイトルが1行目に入ってて、その下にカレンダーから取得した情報が反映されてます！
所要時間は1時間あたりを1として見た時に30分の作業であれば0.5といったように表示されてます！
※今回詳細は取得しないようにコメントアウトしてますが、出力したい場合はコメントアウトを外してください