【カレンダー→スプレッドシート】をGoogle Apps Scriptで自動化

2023年6月18日 12:06

毎日どの業務にどのくらいに時間を費やしているのかを可視化するために、Googleカレンダーをスプレッドシートに書き出す処理をGASで自動化し、効率化を図ろうと考えました。

以前、スプレッドシートに記載した情報をカレンダーに書き出すやり方を試したことがあるので、その逆も出来るだろうと思って、WebやYouTubeなどの情報を頼りに、お手本となるやり方を探しました。

情報はすぐに見つかり、イベントの開始時間・終了時間など、カレンダー情報の取得は以下のようなメソッドを使用することで無事にできましたが、一つ課題が見つかりました。

var calendar = CalendarApp.getCalendarById(ID);

※var の使用は現在公式リファレンスによると推奨されていませんが、一先ずそのまま使っています。

開始時間と終了時間から、経過時間を自動的に計算し、その業務にかかった時間を算出したかったのですが、スプレッドシートに書き出された値は何やら桁の多い数字で、期待していた値ではありませんでした。

ですが、値が取れているのは確かなので、経過時間の形式に変換できれば良いのではないかと考え、ChatGPTなどを活用し、解決策を考えました。

いろいろ悩んで調べた結果、値がミリ秒で渡されているのではないかという推測にたどり着きました。
そして、ミリ秒を15:00のような形式に変換するために、最終的にはBingチャットに相談してサンプルコードを書いてもらいながら、手直しをして作成中のプログラムに組み込みました。

このように、導き出されたものが何なのか、ある程度理解できる基本が出来ていることはもちろん必要ですが、あやふやな部分のアタリをつける際に、AIを使うメリットがあると感じました。

続いてカレンダーにつけたラベルも取得できると、ジャンルごとに分類できて便利だと考え、その取得を考えました。同じようにGAS公式リファレンスやBingチャットに質問しながら、値を取得するメソッドを探しました。

公式リファレンスによると、eventColorメソッドでラベルの色を取得できるようなので、先ずはconsole.logで値を確認。

最終的には数値ではなく項目名で表示したいので、取得した値を辞書のリストを使って、文字列に変換して対応しました。（この部分の処理を考えるのに手こずりました！)
こうして、無事にラベルの値をスプレッドシートに反映させることに成功しました！！

以下が今回試したコードです。

function sendToSpreadsheet() {
  var ID = "Googleアカウントのメールアドレス";
  var calendar = CalendarApp.getCalendarById(ID);

  var startTime = new Date();
  startTime.setHours(0);
  startTime.setMinutes(0);
  startTime.setSeconds(0);

  var endTime = new Date();
  endTime.setDate(endTime.getDate());
  endTime.setHours(23);
  endTime.setMinutes(59);
  endTime.setSeconds(59);

  var events = calendar.getEvents(startTime,endTime);
    var values = [];

  events.forEach(function(event){
    var title = [];
    var title = event.getTitle();
    var start = event.getStartTime();
    var end = event.getEndTime();

    var allday = event.isAllDayEvent();
    if(allday == true){
      allday = "○";
      end = "-";
    }else {
      allday = "-";
    }

    var description = event.getDescription();
    var location = event.getLocation();
    var guests = event.getGuestList();

    var eventColor = event.getColor();
    console.log(eventColor);

    //空かどうかの判定
    if(eventColor == ""){
    //ここに空白だった場合の処理
      eventColor = 4;
    }
    console.log(eventColor);

    var colorDictionary = {
      1: "業務ミーティング",
      2: "ちいさなデジタル推進室",
      3: "業務作業",
      4: "打合せ（リアル）",
      5: "打合せ（オンライン）",
      6: "イベント企画・運営",
      7: "Default",
      8: "その他",
      9: "個人の予定",
      10: "共創コンソーシアム",
      11: "取材・視察",
    };

    var colorName = colorDictionary[eventColor];
console.log(colorName);

    var guests_email = [];
    guests.forEach(function(guest){
      var email = guest.getEmail();
      guests_email.push(email);
    });
 
    var elapsed = end-start;
    var hours = Math.floor(elapsed / 3600000);
    var minutes = Math.floor((elapsed - (hours * 3600000)) / 60000);
    var seconds = ((elapsed - (hours * 3600000) - (minutes * 60000)) / 1000).toFixed(0);
      
    var time = hours + ':' + minutes+ ':' + seconds;
    guests_email = guests_email.join(",");
    values.push([start,title,time,location,description,colorName,guests_email]);
  });

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("2023.6");
    var lastRow = sheet.getLastRow()+1;
    sheet.getRange(lastRow,1,values.length,values[0].length).setValues(values);
}

先ずやってみて、いき詰まったら試行錯誤してがんばる。とにかく粘り強く繰り返すことだと考えています。
エラーが出るのは当たり前、コツコツ修正を繰り返し、悩んで考えて再チャレンジ。
その分、思った通りに動いた時のよと喜びは大きいです！！！

引き続きチャレンジを続けます。

次のチャレンジ【Slackに通知を出す】
https://note.com/little_room/n/neb88042a7c6b?sub_rt=share_b

この記事が気に入ったらサポートをしてみませんか？