Google Apps Scriptで任意の月のカレンダーデータをスプレッドシートに書き出す方法

photo credit: Charos Pix Measurement via photopin (license)

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

GASでGoogleカレンダーの記録をスプレッドシートに書き出して活用する方法についてお伝えしています。

前回の記事はこちら。

Google Apps Scriptで時刻計算と文字列抽出の数式を入力する方法

Googleカレンダーの記録をスプレッドシートに書き出して活用する方法についてお伝えしています。今回は、Google Apps Scriptで時刻計算と文字列抽出の数式を入力する方法をお伝えします。

GASで集計用の数式の内容とそれをシートに書き込む方法をお伝えしました。

さて、今回は、スプレッドシートの入力内容をつかって、イベントを出力する月を指定できるようにしていきます。

ということで、Google Apps Scriptで任意の月のカレンダーデータをスプレッドシートに書き出す方法をお伝えします。

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

前回のおさらい
スプレッドシートで指定した月のイベントを取得する
1. セルの内容から開始日時と終了日時を求める
2. 実行結果
実行前にセル範囲をクリアする
まとめ
連載目次：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);
  
  const values = [];
  for(const event of events){
    const record = [
      event.getTitle(),
      event.getStartTime(),
      event.getEndTime(),
      event.getLocation(),
      event.getDescription(),
      "=INDIRECT("RC[-3]",FALSE)-INDIRECT("RC[-4]",FALSE)",
      "=IFERROR(SUBSTITUTE(LEFT(INDIRECT("RC[-6]",FALSE),FIND("】",INDIRECT("RC[-6]",FALSE))-1),"【",""),"")"
    ];
    values.push(record);
  }
  
  SpreadsheetApp.getActiveSheet().getRange(2, 1, values.length, values[0].length).setValues(values);
  
}

実行すると、以下のようにイベント情報を出力することができます。

しかし、6,7行目をご覧ください。

イベントを出力する期間の開始日時と終了日時はスクリプト内にベタ打ちなんですね。

今回は、それをスプレッドシートの内容をもとに期間を決められるようにしていきます。

スプレッドシートで指定した月のイベントを取得する

まず、準備としてシートのI1,I2セルを以下のようにしました。

つまり、取得対象とする月を、「I2」セルに入力するようにしたのです。

それで、スクリプトのほうはこの情報を読み取って、その月のイベント情報を取得するように変更しました。

こちらです。

const CALENDAR_ID = '**********'; //カレンダーID

function getCalendarEvents() {

  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(2, 1, sheet.getLastRow(), 6).clearContent();
  
  const startTime = new Date(sheet.getRange('I2').getValue());
  const endTime = new Date(startTime);
  endTime.setMonth(endTime.getMonth() + 1);
  
  const calendar = CalendarApp.getCalendarById(CALENDAR_ID);    
  const events = calendar.getEvents(startTime, endTime);
  
  const values = [];
  for(const event of events){
    const record = [
      event.getTitle(),
      event.getStartTime(),
      event.getEndTime(),
      event.getLocation(),
      event.getDescription(),
      "=INDIRECT("RC[-3]",FALSE)-INDIRECT("RC[-4]",FALSE)",
      "=IFERROR(SUBSTITUTE(LEFT(INDIRECT("RC[-6]",FALSE),FIND("】",INDIRECT("RC[-6]",FALSE))-1),"【",""),"")"
    ];
    values.push(record);
  }
  
  SpreadsheetApp.getActiveSheet().getRange(2, 1, values.length, values[0].length).setValues(values);
  
}

ではこのスクリプトの変更点について解説していきましょう。

セルの内容から開始日時と終了日時を求める

8~10行目で、I2セルの内容を取得し、getEventsメソッドにわたす開始日時と終了日時を求める部分です。

  const startTime = new Date(sheet.getRange('I2').getValue());
  const endTime = new Date(startTime);
  endTime.setMonth(endTime.getMonth() + 1);

まず、I2セルの値からDateオブジェクトを生成し、それを開始日時startTimeとします。

次に、終了日時を求めるのですが、少し工夫が必要です。

まず、開始日時を表すDateオブジェクトから、新たにDateオブジェクトを生成し、それをいったん終了日時endTimeとします。

Dateオブジェクトは、他のDateオブジェクトを引数に渡して、同じ日時のDateオブジェクトを生成できるのです。

new Date(Dateオブジェクト)

そのあとに、「月」の値だけを1加えて、「翌月」の日時とするのです。

ちょっとまどろっこしいですが、いったん「月」の値を取得して、プラス1をしたものを、「月」の値に設定し直すということをしています。

Dateオブジェクトの月の値を取得するのがgetMonthメソッド、月の値を設定するのがsetMonthメソッドです。

Dateオブジェクト.getMonth()
Dateオブジェクト.setMonth(値)

実行結果

では、I2セルに開始日時を「2020/3/1」として、スクリプトを実行してみましょう。

実行結果がこちらです。

実行前にセル範囲をクリアする

今回、別のちょっとした便利処理も追加しているので初回しておきます。

6行目です。

sheet.getRange(2, 1, sheet.getLastRow(), 6).clearContent();

対象の範囲となるRangeオブジェクトに、clearConentメソッドを使って、セルの内容をクリアしました。

これで、毎回のスクリプト実行前にイベントを削除する必要はなくなりますね。

まとめ

以上、Google Apps Scriptで任意の月のカレンダーデータをスプレッドシートに書き出す方法についてお伝えしました。

Dateオブジェクトの要素の加算や減算などは、今回お伝えした方法で行う必要がありますので、覚えてくださいね。

次回は、プロジェクトごとに工数を集計する方法をお伝えします。

どうぞお楽しみに！

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

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