Googleカレンダーから時間合計を種類別にスプレッドシートに書き出すGoogleAppScript(GAS)

Googleカレンダーから時間合計を種類別にスプレッドシートに書き出すGoogleAppScriptをchatGPTにも助けてもらいながら、サクッと書いてみました。
サクッと書いて個人使用用なので、命名やコードの綺麗さはご勘弁を。

このスクリプトの仕様と注意

• 終日の予定は時間計算から除外
• 単位は、分ではなく時間
• 複数のカレンダーに対応
• 既に同じ日付のデータがシートにあれば、数値だけ更新するように。なければ最終行に追記。
• 昨日のデータは、結果として、今日のデータは計画の数値としてみる前提で実行関数を用意
• 一応一週間くらいまとめて、取得する関数も用意してますが、一気にデータ取得するように最適化されたAPIリクエストの書き方はしていないので、一気にデータを取る使い方にカスタマイズする場合は要注意。

使い方

• 1.スプレッドシートのメニュー拡張機能 > App Script を選択しエディタ画面を開く
• 2.次のスクリプトを貼り付け
• 3.コード内のcalendersの変数に自分のカレンダーIDなどに書き換え
• 4.メニューから、実行する関数をtodayTotal、yesterdayTotal、weekTotal好きなものを選択し、実行。
• 5.最初だけ、実行していいかの認証画面がでてくるので、案内に沿って許可する
• 6.addMenuの関数も実行しておくと、スプレッドシート側にもスクリプト実行メニューが追加される。
• 7.エディタの左メニューからトリガーを選択。自動で、関数を実行する日時を指定しておくと、毎日自動で、記録していくことができる。

GoogleAppScript例

function weekTotal() {
  const now = new Date();
  const date = new Date();
  [0,1,2,3,4,5,6].forEach(function(i){
    date.setDate(now.getDate() - i);
    selectCalendersTogal(date);
  });
}

function yesterdayTotal() {
  const date = new Date();
  date.setDate(date.getDate() - 1);
  selectCalendersTogal(date);
}

function todayTotal() {
  selectCalendersTogal(new Date());
}

function dailyUpdate(){
  yesterdayTotal();
  todayTotal();
}

function selectCalendersTogal(date){
  const calenders = [
    {
      id: "Googleカレンダーの設定画面にあるカレンダーID",
      name: "カレンダー名",
      category: "好きな分類名",
    },
    {
      id: "Googleカレンダーの設定画面にあるカレンダーID",
      name: "カレンダー名",
      category: "好きな分類名",
    },
  ];
  calenders.forEach(function(cal) {
    calculateDailyTotal(cal, date);
  });
}

function calculateDailyTotal(cal, date) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Logs');

  const startDate = new Date(date.getFullYear(), date.getMonth(), date.getDate(), 0, 0, 0);
  const endDate = new Date(date.getFullYear(), date.getMonth(), date.getDate(), 23, 59, 59);

  const events = Calendar.Events.list(cal.id, {
    timeMin: startDate.toISOString(),
    timeMax: endDate.toISOString(),
    singleEvents: true,
    orderBy: 'startTime'
  });

  let totalDuration = 0;

  events.items.forEach(function(event_item){
    const start = new Date(event_item.start.dateTime || event_item.start.date);
    const end = new Date(event_item.end.dateTime || event_item.end.date);

    // 終日は除外
    let isAllDay = start.length === end.length;
    var oneDay = 24 * 60 * 60 * 1000;
    isAllDay = Math.round((end - start) / oneDay) >= 1; // 連日のイベントも終日扱い
    if (isAllDay){
      return;
    }

    const duration = (end.getTime() - start.getTime()) / (1000 * 60); // duration in minutes
    totalDuration += duration;
  });

  const totalDurationHour = Math.round((totalDuration / 60) * 100) / 100;
  const lastRow = sheet.getLastRow();
  const formattedDate = Utilities.formatDate(startDate, Session.getScriptTimeZone(), "yyyy/MM/dd");
  const id = formattedDate + '_' + cal.name;
  let idFound = false;

  // update
  for (let i = 1; i <= lastRow; i++) {
    const currentId = sheet.getRange(i, 1).getValue();
    if (currentId == id) {
      sheet.getRange(i, 5).setValue(totalDurationHour);
      idFound = true;
      break;
    }
  }

  // create
  if (!idFound) {
    const newRow = lastRow + 1;
    sheet.getRange(newRow, 1).setValue(id); //id (重複を防ぐための日+カレンダー名)
    sheet.getRange(newRow, 2).setValue(formattedDate); //日
    sheet.getRange(newRow, 3).setValue('日月火水木金土'[startDate.getDay()]); //曜日
    sheet.getRange(newRow, 4).setValue(cal.name); //カレンダー
    sheet.getRange(newRow, 5).setValue(totalDurationHour); //時間合計
    sheet.getRange(newRow, 6).setValue(cal.category); //カテゴリ
    sheet.getRange(newRow, 7).setValue(new Date()); //記録日時
  }
}

// スプレッドシートのメニューに関数を実行するメニューを追加
function addMenu() {
  var spreadSheet = SpreadsheetApp.getActive();
  var items = [
    {name: 'todayTotal', functionName: 'todayTotal'},
    {name: 'yesterdayTotal', functionName: 'yesterdayTotal'},
    {name: 'weekTotal', functionName: 'weekTotal'},
  ];
  spreadSheet.addMenu('ScriptAction', items);
}

// スプレッドシートを開いたときに実行
function onOpen(){
  addMenu();
}