Google Apps ScriptでGoogleカレンダーの記録をスプレッドシートに出力する

みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。

引き続きGoogleカレンダーの記録を活用して過去の仕事の生産性を測定する方法を進めていきます。

前回はこちらの記事でした。

429 Too Many Requests

tonari-it.com

Googleカレンダーの特定の月のイベントのイベント名をログ出力することができました。

今回は、GASでカレンダーのイベント情報をスプレッドシートに出力する方法をお伝えしていきます。

では、行ってみましょう！

前回のおさらい

前回のスクリプトはこちらでした。

const CALENDAR_ID = '**********'; //カレンダーID
function getCalendarEvents() {
  const calendar = CalendarApp.getCalendarById(CALENDAR_ID);  
  const startTime = new Date('2020/04/01 00:00:00');
  const endTime = new Date('2020/05/01 00:00:00');
   
  const events = calendar.getEvents(startTime, endTime);
 
  for(const event of events){
    console.log(event.getTitle());
  }
 
}

スクリプトの流れとしては以下のとおりでした。

1. カレンダーIDを指定してカレンダーを取得
2. 取得する期間の開始日と終了日を生成
3. カレンダーのイベントを配列として取得
4. 配列をループして取得したイベントのイベント名をログ出力

今回のお題：イベント情報をスプレッドシートに出力

今回は、イベントの情報の出力先をスプレッドシートにしたいと思います。

その前準備として、バインドしているスプレッドシートのシートに以下のように見出しを付与しました。

• A列: タイトル
• B列: 開始日時
• C列: 終了日時

前回はイベントタイトルを取得しましたが、それに加えてイベントの開始日時と終了日時も書き出したいと思います。

カレンダーの特定の月のイベントをスプレッドシートに出力する

前回のスクリプトを修正して以下のようにしました。

const CALENDAR_ID = '**********'; //カレンダーID
function getCalendarEvents() {
  const calendar = CalendarApp.getCalendarById(CALENDAR_ID);  
  const startTime = new Date('2020/04/01 00:00:00');
  const endTime = new Date('2020/05/01 00:00:00');
   
  const events = calendar.getEvents(startTime, endTime);
 
  const values = [];
  for(const event of events){
    const record = [
      event.getTitle(),
      event.getStartTime(),
      event.getEndTime()
    ];
    values.push(record);
  }
 
  SpreadsheetApp.getActiveSheet().getRange(2, 1, values.length, values[0].length).setValues(values);
 
}

7行目までは、前回と同様です。

おおまかに分けると9~17行目が、スプレッドシートに貼り付けるためのデータとなる二次元配列を作成している部分です。

19行目が、スプレッドシートのセル範囲にその二次元配列のデータを貼り付ける処理です。

以降で詳しく解説をしていきますね。

シートに貼り付ける二次元配列を生成する

スプレッドシートにデータを貼り付ける場合、「行×列」の領域に貼り付けることになりますので、配列の配列、つまり二次元配列を作ると便利です。

9行目は、valuesという空の配列を用意している部分です。

10~17行目はEventオブジェクトの配列のループをしている部分ですが、ここで以下のような一次元配列recordを作って、pushメソッドでvaluesに追加しています。

つまりこの一次元配列recordが、一行分のデータとなるわけです。

pushメソッドはArrayオブジェクトつまり配列の末尾に要素を追加するメソッドです。

二次元配列については、以下の記事もご参考ください。

【初心者向けGAS】スプレッドシートのセル範囲の値を二次元配列として取得して取り扱う方法

Google Apps Script初心者向けのお題として、名言Botの作り方をお伝えしています。今回は、スプレッドシートのセル範囲の値を二次元配列としてgetValuesで取得して取り扱う方法です。

tonari-it.com

2018.05.23

イベントの開始日時と終了日時を取得する

さて、その一次元配列recordに含まれる、開始日時と終了日時ですが、これらはそれぞgetStartTimeメソッド、getEndTimeメソッドによるものです。

以下の書式で、それぞれイベントの開始日時と終了日時をDateオブジェクトとして取得します。

セル範囲に二次元配列を貼り付ける

19行目について説明をします。

まず、以下の部分までで、アクティブなシートをSheetオブジェクトとして取得します。

SpreadsheetApp.getActiveSheet()

続いて、getRangeメソッドで、貼り付ける先のセル範囲をRangeオブジェクトとして取得します。

今回は、以下のようにしていますね。

SpreadsheetApp.getActiveSheet().getRange(2, 1, values.length, values[0].length)

lengthプロパティは配列の要素数を取得します。

これにより、開始セルが2行1列、つまりA2セルで、valuesの要素数の行数、values[0]（1つ目の一次元配列）の要素数の列数のセル範囲を指定できることになります。

続いて、setValuesメソッドで対象のセル範囲に二次元配列のデータを書き出します。

ここで、対象のセル範囲のサイズと、二次元配列のサイズは一致していないとエラーになっちゃいますので注意です。

ですから、先ほどのlengthプロパティの使い方が生きてくるわけです。

スプレッドシートへの出力結果

では、スクリプトを実行してみましょう。

以下のように書き出されるはずです。

うまくいきました！

スプレッドシートで日時の表示形式に変更する

ですが、B列とC列を見ると、時刻部分が表示されていないようです。

日時の表示形式に変更しておきましょう。

B,C列を選択した状態でスプレッドシートのメニューから「表示形式」→「数字」→「日時」とたどります。

結果としてこのような表示になります。

これで、時刻も確認できるようになりましたね。

まとめ

以上、Google Apps ScriptでGoogleカレンダーの記録をスプレッドシートに出力する方法をお伝えしました。

特に二次元配列を生成して、それをスプレッドシートのセル範囲に書き出す部分は、かなりよく使うテクニックですので、ぜひマスターくださいね。

次回は、イベントの他の情報も取得して書き出していきます。

Google Apps ScriptでGoogleカレンダーの場所・説明を取得する方法

Googleカレンダーの記録をスプレッドシートに書き出して活用する方法についてお伝えしています。今回はGoogle Apps ScriptでGoogleカレンダーのイベントの場所と説明を取得する方法です。

tonari-it.com

2016.12.23

どうぞお楽しみに！

連載目次：GASとカレンダーの記録を活用して過去の仕事を振り返る

企業ではスプレッドシートを勤怠管理やプロジェクト進捗管理などに使っている場合も多いと思います。このシリーズでは、カレンダーとスプレッドシート、そしてGoogle Apps Scriptを使って仕事の生産性を測定する方法についてお伝えしています。

1. GASで仕事の生産性を測定して振り返るためのGoogleカレンダーの使い方
2. Google Apps ScriptでGoogleカレンダーを操作する最初の一歩のスクリプト
3. Google Apps Scriptで特定月のカレンダーのイベント情報を取得する
4. Google Apps ScriptでGoogleカレンダーの記録をスプレッドシートに出力する
5. Google Apps ScriptでGoogleカレンダーの場所・説明を取得する方法
6. Google Apps Scriptで時刻計算と文字列抽出の数式を入力する方法
7. Google Apps Scriptで任意の月のカレンダーデータをスプレッドシートに書き出す方法
8. Googleカレンダーの記録を活用してスプレッドシートに業務別の工数計算