MENU
お問い合わせ
  1. ホーム
  2. 業務改善
  3. 【業務改善】Googleカレンダーの予定をスプレッドシートに転記するGoogle Apps Scriptの作り方【ChatGPT】

【業務改善】Googleカレンダーの予定をスプレッドシートに転記するGoogle Apps Scriptの作り方【ChatGPT】

業務改善

はじめに

みなさまこんにちは！
ChatGPT使われていますか？弊社ではあまり浸透していませんが、私はガンガン使っております😆
と、言っても個人情報は入れないようにしてるので、直接的な業務には使えていませんが。。。
しかし、個人情報のないメールテンプレの作成や、難解な説明書の要約などには大活躍しています。
本日は弊社で取り組んでいる業務改善の一部をChatGPTと協力しておこなった様子をお届けするとともに、そのまま実行できるコードもお伝えします。
プログラムコードを書けない私がChatGPTに書かせた内容なので、細かな間違いはご容赦くださいm(_ _)m

最終的に出来上がるコード

function exportEventsToSpreadsheet() {
  // スプレッドシートの設定。アクティブなシートを取得します。
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheets()[0];

  // 使用するカレンダーのID
  var calendarIds = ['your_calendarId','your_calendarId'];

   // 5日前の日付（月曜日）を取得
  var fiveDaysAgo = new Date();
  fiveDaysAgo.setDate(fiveDaysAgo.getDate() - 5);

  // 現在の日付（金曜日）を取得　トリガーは毎週金曜日の実行を予定
  var today = new Date();

  // イベント色ラベル名を取得する関数
  var colorLabelMap = getColorLabelMap();

  // 各カレンダーからイベントを取得し、処理
  calendarIds.forEach(function(calendarId) {
    var calendar = CalendarApp.getCalendarById(calendarId);
    var events = calendar.getEvents(fiveDaysAgo,today );

    events.forEach(function(event) {
      // 終日イベントは除外
      if (event.isAllDayEvent()) {
        return;
      }

      var attendees = event.getGuestList().map(function(guest) {
        return guest.getEmail();
      }).join(", ");
      
      // 使用時間を計算（小数点形式）
      var duration = calculateDuration(event.getStartTime(), event.getEndTime());

      // イベントの色ラベル名を取得
      var eventColorLabel = colorLabelMap[event.getColor()];

      // イベント情報をスプレッドシートに書き込み
      sheet.appendRow([
        event.getId(), 
        calendarId,
        event.getTitle(), 
        event.getStartTime(), 
        event.getEndTime(), 
        event.getLocation(), 
        event.getDescription(),
        attendees, 
        event.getCreators().join(", "),
        "時間指定",
        duration,
        eventColorLabel
      ]);
    });
  });
}

// イベント色ラベル名をマッピングする関数
// Googleカレンダーの予定を右クリックすると設定できます。


//デフォルトセッティング
//"1": "淡い青色",
//"2": "淡い緑色",
//"3": "Mauve",
//"4": "淡い赤",
//"5": "黄色",
//"6": "オレンジ",
//"7": "シアン",
//"8": "グレー",
//"9": "青",
//"10": "緑",
//"11": "赤"
//以下の色をカレンダーに登録したラベルと対応するように書き換えてください

function getColorLabelMap() {
  return {
    "1": "淡い青色",
    "2": "淡い緑色",
    "3": "Mauve",
    "4": "淡い赤",
    "5": "黄色",
    "6": "オレンジ",
    "7": "シアン",
    "8": "グレー",
    "9": "青",
    "10": "緑",
    "11": "赤"
    
  };
}

// 開始時刻と終了時刻から所要時間を小数点で計算する関数
//30分=0.5として表示する
function calculateDuration(startTime, endTime) {
  var duration = (endTime.getTime() - startTime.getTime()) / (1000 * 60 * 60); // 時間単位で計算
  return Math.round(duration * 100) / 100; // 小数点第二位までに丸める
}

業務改善の経緯

そもそもなぜタイトルの業務改善が発生したかと言いますと、他社さんで業務量の見える化をLookerStudioで実施されてたんですよね。
弊社では定期的に他社を見学(社内ではベンチマーキングと呼んでます)しに行く文化がありまして、それをみた後輩が早速自社に持ち帰って実践してくれてました！(見てから自分のもにするまで超早かった)

その後社内の業務改善共有会で発表し、社内投票で2位に選ばれていたので真似してみようと思った次第です。
せっかく真似するならもっと簡単に手間なくできたほうが良かろうってんでChatGPTに聞いて自動化までしてみました

全体像

さっそく今回実装する内容をざっくり考えてみました。使用するのは以下の4つ+ChatGPTです

  • Googleカレンダー
  • Google AppsScript(GAS)
  • Googleスプレッドシート
  • LookerStudio

Googleカレンダーの予定をGoogle AppsScriptを使ってGoogleスプレッドシートに転記、それをLookerStudioに表示ってな感じで実行していきます。
これのいいところはGoogle AppsScriptはスケジュール実行ができるので、一度設定してしまえば毎日、毎週、毎月でも自動実行してくれるところ！
ビバ自動化＼(^o^)／

Googleカレンダーの内容をスプレッドシートに転記するまで(エラーが出たときのコードも書いてます)

GASでとりあえずカレンダー情報をスプレッドシートに書き込むまで

さっそく始めてみますが、なんせコードが書けないので最初からChatGPTに聞いて勧めます。
※私は極度の飽き性かつめんどくさがり屋なので、タイピングではなく音声入力しています。右側の吹き出しに誤字脱字が多発するかもしれませんがChatGPTは意図を汲み取って回答してくれます。

使用している音声入力の拡張機能

GoogleAppsScriptのコードを書いてきます これはカレンダーの1週間の予定をGoogleスプレッドシートに転記するコードを書こうと思っています Googleカレンダーの予定から取得できるパラメーターの全てを書き出してください

基本的にAPIとかプログラミングコードがわからないし、調べるつもりもない私はChatGPTの知っているすべてのことを最初に書いてもらうようにしてます。これをせずに何かをやってもらおうとすると、失敗する確率が高い気がします(個人の感想です。ChatGPTもアップデートされ続けているので、今はそんなことないのかもしれません)

Googleカレンダーの予定から取得可能な主なパラメーターは以下の通りです：
イベントID (eventId): 各イベントを一意に識別するID。
タイトル (summary): イベントのタイトル。
開始日時 (start.dateTime): イベントの開始日時。終日イベントの場合は start.date。
終了日時 (end.dateTime): イベントの終了日時。終日イベントの場合は end.date。
場所 (location): イベントが行われる場所。
説明 (description): イベントに関する詳細な説明。
出席者 (attendees): イベントに招待されている人々のリスト。
作成者 (creator): イベントの作成者情報。
オーガナイザー (organizer): イベントの主催者情報。
ステータス (status): イベントのステータス（”confirmed”、”tentative”、”cancelled” など）。
透明度 (transparency): イベントが時間を占有するかどうか（”transparent”、”opaque”）。
繰り返し (recurrence): 繰り返しイベントのルール。
リマインダー (reminders): リマインダーの設定。
これらのパラメーターを利用して、Google Apps Scriptを用いてGoogleカレンダーの予定をスプレッドシートに転記することができます。

とのことで一旦GoogleカレンダーからGASで取得できる要素がわかりました。あとはもう簡単です！書いてもらえばいいのです！
Googleスプレッドシートを新規作成→拡張機能→Google AppsScriptを開いておきます。

それでは先ほど出たパラメーターの全てを1週間分取得してGoogleスプレッドシートに転記するためのGoogleAppsScriptのコードを書いてください スプレッドシート側のカラムは以下の通りに設定してあります eventId summary start.dateTime end.dateTime location description attendees creator organizer status transparency recurrence reminders

Google Apps Scriptを使って、Googleカレンダーから1週間分のイベントデータを取得し、指定されたカラムに従ってGoogleスプレッドシートに転記するためのコードを以下に示します。
このスクリプトは、指定されたスプレッドシートの最初のシートにデータを転記します。まず、スプレッドシートの準備が必要で、カラムのタイトルは質問で挙げられた順序に従って設定してください。

function exportEventsToSpreadsheet() {
  // スプレッドシートの設定
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheets()[0];

  // 現在の日付と1週間後の日付を取得
  var today = new Date();
  var oneWeekLater = new Date();
  oneWeekLater.setDate(today.getDate() + 7);

  // カレンダーからイベントを取得
  var calendar = CalendarApp.getDefaultCalendar();
  var events = calendar.getEvents(today, oneWeekLater);

  // 各イベントのデータを処理
  for (var i = 0; i < events.length; i++) {
    var event = events[i];
    var attendees = event.getGuestList().map(function(guest) {
      return guest.getEmail();
    }).join(", ");
    
    // イベント情報をスプレッドシートに書き込み
    sheet.appendRow([
      event.getId(), 
      event.getTitle(), 
      event.getStartTime(), 
      event.getEndTime(), 
      event.getLocation(), 
      event.getDescription(),
      attendees, 
      event.getCreators().join(", "),
      event.getCreators().join(", "), // オーガナイザーは getOrganizers() が存在しないため、代わりに getCreators() を使用
      event.getStatus(),
      event.getTransparency(),
      event.getRecurrenceRule() ? event.getRecurrenceRule().getAsText() : "", 
      JSON.stringify(event.getReminders())
    ]);
  }
}

何度書いてもらってもコードが書かれていくさまを見るのは感心しますよね！
ですが、実行してみるとエラーと表示されました。。。

TypeError: event.getStatus is not a function
exportEventsToSpreadsheet @ コード.gs:33

これもChatGPTに聞いてみるとなぜエラーが起きたのかの原因を教えてくれます。

event.getStatus() がエラーを引き起こす原因は、Google Apps ScriptのCalendarEventクラスには getStatus() メソッドが存在しないためです。このメソッドは、私の誤りにより誤って提供されました。このメソッドを削除して、コードを修正しましょう。

存在しない要素があったようです。このあともいくつか存在しない要素が見つかり、最終的には以下のようなコードが生成されました。

function exportEventsToSpreadsheet() {
  // スプレッドシートの設定
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheets()[0];

  // 現在の日付と1週間後の日付を取得
  var today = new Date();
  var oneWeekLater = new Date();
  oneWeekLater.setDate(today.getDate() + 7);

  // カレンダーからイベントを取得
  var calendar = CalendarApp.getDefaultCalendar();
  var events = calendar.getEvents(today, oneWeekLater);

  // 各イベントのデータを処理
  for (var i = 0; i < events.length; i++) {
    var event = events[i];
    var attendees = event.getGuestList().map(function(guest) {
      return guest.getEmail();
    }).join(", ");
    
    // イベント情報をスプレッドシートに書き込み
    sheet.appendRow([
      event.getId(), 
      event.getTitle(), 
      event.getStartTime(), 
      event.getEndTime(), 
      event.getLocation(), 
      event.getDescription(),
      attendees, 
      event.getCreators().join(", "),
      event.getCreators().join(", "), // オーガナイザーは getOrganizers() が存在しないため、代わりに getCreators() を使用
      event.isAllDayEvent() ? "終日" : "時間指定"
    ]);
  }
}

いったんこのコードで実行を確認できました。
ここから、細かな部分を調整していきます。

運用するための修正

データが取得できるようになったので、具体的な運用方法を考えながら微調整していきます。
まずデータを取るタイミングですが、1週間で取得するようにしました。1日の終りの真夜中に実行しようとも思いましたが、カレンダーに登録するのを忘れていたりすると正しいデータが集まらないのでやめときました。一ヶ月だと変化がなくて飽きちゃいますしね笑

最終的には毎週金曜日の夜8時に、月曜から今日までの1週間分のデータを取得することにします。

ということで早速GPTに聞いてみます。

このコードを今日から数えて5日前までの区間を取得するとしたらどのように変わりますか具体的には金曜日の夕方5時にこのコードを実行するようにトリガー設定をする予定ですその時私が知りたいものは月曜日から今日である金曜日までの予定の一覧を取得したいです

金曜日の夕方5時に実行されるトリガーを設定し、その時点で月曜日から金曜日までの予定を取得するには、コードでの日付の取得方法を変更する必要があります。
現在の日付から5日前（月曜日）の日付を計算し、それを開始日とし、現在の日付（金曜日）を終了日として設定します。以下のコードは、この要件に合わせて修正されています。

function exportEventsToSpreadsheet() {
  // スプレッドシートの設定
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheets()[0];

  // 5日前の日付（月曜日）を取得
  var fiveDaysAgo = new Date();
  fiveDaysAgo.setDate(fiveDaysAgo.getDate() - 5);

  // 現在の日付（金曜日）を取得
  var today = new Date();

  // カレンダーからイベントを取得
  var calendar = CalendarApp.getDefaultCalendar();
  var events = calendar.getEvents(fiveDaysAgo, today);

  // 各イベントのデータを処理
  for (var i = 0; i < events.length; i++) {
    var event = events[i];
    var attendees = event.getGuestList().map(function(guest) {
      return guest.getEmail();
    }).join(", ");
    
    // イベント情報をスプレッドシートに書き込み
    sheet.appendRow([
      event.getId(), 
      event.getTitle(), 
      event.getStartTime(), 
      event.getEndTime(), 
      event.getLocation(), 
      event.getDescription(),
      attendees, 
      event.getCreators().join(", "),
      event.getCreators().join(", "), // オーガナイザーは getOrganizers() が存在しないため、代わりに getCreators() を使用
      event.isAllDayEvent() ? "終日" : "時間指定"
    ]);
  }
}

実行してみると今日から数えて5日前までの予定を取得できてました！やっぱりすごいぞGPT!
ただし終日設定している予定は時間を取得できないので、除外したい。。。あとちょっとカラム変えたい

ステータスが終日のものを除きたいですコードを修正してください
eventId summary start.dateTime end.dateTime location description attendees creator organizer status もう1点追加で絡む情報が上記のように変わりましたので修正をしてください

終日イベントを除外するために、スクリプトに条件を追加します。isAllDayEvent() メソッドを使用して、イベントが終日のものかどうかをチェックし、終日のイベントの場合はスプレッドシートに追加しないようにします。以下が修正されたコードです。

function exportEventsToSpreadsheet() {
  // スプレッドシートの設定
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheets()[0];

  // 5日前の日付（月曜日）を取得
  var fiveDaysAgo = new Date();
  fiveDaysAgo.setDate(fiveDaysAgo.getDate() - 5);

  // 現在の日付（金曜日）を取得
  var today = new Date();

  // カレンダーからイベントを取得
  var calendar = CalendarApp.getDefaultCalendar();
  var events = calendar.getEvents(fiveDaysAgo, today);

  // 各イベントのデータを処理
  for (var i = 0; i < events.length; i++) {
    var event = events[i];
    
    // 終日イベントは除外
    if (event.isAllDayEvent()) {
      continue;
    }

    var attendees = event.getGuestList().map(function(guest) {
      return guest.getEmail();
    }).join(", ");
    
    // イベント情報をスプレッドシートに書き込み
    sheet.appendRow([
      event.getId(), 
      event.getTitle(), 
      event.getStartTime(), 
      event.getEndTime(), 
      event.getLocation(), 
      event.getDescription(),
      attendees, 
      event.getCreators().join(", "), // 作成者
      event.getCreators().join(", "), // オーガナイザーは getOrganizers() が存在しないため、代わりに getCreators() を使用
      "時間指定" // ステータス
    ]);
  }
}

す、素晴らしすぎる。
ここまで来るともう少し欲が出てきまして、カレンダーの予定からその作業にどのくらい時間がかかったのか計算してほしいわけです。LookerStudioでも計算はできると思いますが、あまり計算させると表示スピードがゴミ(見るに耐えない遅さ)になります。
やっぱり計算が終了しているスプレッドシート is 神ですわ。GASで計算させましょう。

行動更新しました以下の内容確認してください

function exportEventsToSpreadsheet() {
  // スプレッドシートの設定
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheets()[0];

  // 5日前の日付（月曜日）を取得
  var fiveDaysAgo = new Date();
  fiveDaysAgo.setDate(fiveDaysAgo.getDate() - 5);

  // 現在の日付（金曜日）を取得
  var today = new Date();

  // カレンダーからイベントを取得
  var calendar = CalendarApp.getDefaultCalendar();
  var events = calendar.getEvents(fiveDaysAgo, today);

  // 各イベントのデータを処理
  for (var i = 0; i < events.length; i++) {
    var event = events[i];
    
    // 終日イベントは除外
    if (event.isAllDayEvent()) {
      continue;
    }

    var attendees = event.getGuestList().map(function(guest) {
      return guest.getEmail();
    }).join(", ");
    
    // イベント情報をスプレッドシートに書き込み
    sheet.appendRow([
      event.getId(), 
      event.getTitle(), 
      event.getStartTime(), 
      event.getEndTime(), 
      event.getLocation(), 
      event.getDescription(),
      attendees, 
      event.getCreators().join(", "), // 作成者
      "時間指定" // ステータス
    ]);
  }
}

この状態でスプレッドシートのカラムを追加しますカラム名は使用時間ですこのカラムにはスタートデイトとエンドデイトからそこにかかっている時間を計算して入力してくださいここの表記方法はhh:mmでお願いします

指定された要件に基づいて、イベントの開始時刻と終了時刻から所要時間を計算し、その結果を hh:mm 形式で新しいカラム「使用時間」に入力するようにスクリプトを修正します。以下が修正されたコードです。

function exportEventsToSpreadsheet() {
  // スプレッドシートの設定
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheets()[0];

  // 5日前の日付（月曜日）を取得
  var fiveDaysAgo = new Date();
  fiveDaysAgo.setDate(fiveDaysAgo.getDate() - 5);

  // 現在の日付（金曜日）を取得
  var today = new Date();

  // カレンダーからイベントを取得
  var calendar = CalendarApp.getDefaultCalendar();
  var events = calendar.getEvents(fiveDaysAgo, today);

  // 各イベントのデータを処理
  for (var i = 0; i < events.length; i++) {
    var event = events[i];
    
    // 終日イベントは除外
    if (event.isAllDayEvent()) {
      continue;
    }

    var attendees = event.getGuestList().map(function(guest) {
      return guest.getEmail();
    }).join(", ");
    
    // 使用時間を計算（hh:mm形式）
    var duration = calculateDuration(event.getStartTime(), event.getEndTime());

    // イベント情報をスプレッドシートに書き込み
    sheet.appendRow([
      event.getId(), 
      event.getTitle(), 
      event.getStartTime(), 
      event.getEndTime(), 
      event.getLocation(), 
      event.getDescription(),
      attendees, 
      event.getCreators().join(", "), // 作成者
      "時間指定", // ステータス
      duration // 使用時間
    ]);
  }
}

// 開始時刻と終了時刻から所要時間を計算する関数
function calculateDuration(startTime, endTime) {
  var duration = new Date(endTime - startTime);
  var hours = duration.getUTCHours();
  var minutes = duration.getUTCMinutes();
  return ('0' + hours).slice(-2) + ':' + ('0' + minutes).slice(-2);
}

この修正により、イベントの開始時刻と終了時刻から所要時間を計算し、それを hh:mm 形式で「使用時間」カラムに入力します。スプレッドシートの対応するカラムにこのデータが追加されます。

キタ＼(^o^)／！これで完成や！…しかし矢木に電流走る

「せっかく作ったなら他の人にも共有したろ」というおせっかい魂。さっそくGPTに相談してみると

ちなみに私はいくつかのカレンダーを所有しているんですがいくつかのカレンダーに対してこのコードを実行する事って可能ですか？

はい、複数のカレンダーに対してこのスクリプトを実行することは可能です。Google Apps Scriptを使用して、特定のカレンダーを指定する方法はいくつかありますが、一般的なアプローチとしては、カレンダーのIDを使用して特定のカレンダーを取得する方法があります。
以下のスクリプトは、指定された複数のカレンダーIDに基づいてイベントを取得し、スプレッドシートに記録する方法を示しています。この例では、各カレンダーのIDを配列に格納し、それらのカレンダーからイベントを取得します。

function exportEventsToSpreadsheet() {
  // スプレッドシートの設定
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheets()[0];

  // 使用するカレンダーのID（複数指定可能）
  var calendarIds = ['カレンダーID1', 'カレンダーID2', 'カレンダーID3']; // 実際のカレンダーIDに置き換えてください

  // 5日前の日付（月曜日）を取得
  var fiveDaysAgo = new Date();
  fiveDaysAgo.setDate(fiveDaysAgo.getDate() - 5);

  // 現在の日付（金曜日）を取得
  var today = new Date();

  // 各カレンダーからイベントを取得し、処理
  calendarIds.forEach(function(calendarId) {
    var calendar = CalendarApp.getCalendarById(calendarId);
    var events = calendar.getEvents(fiveDaysAgo, today);

    events.forEach(function(event) {
      // 終日イベントは除外
      if (event.isAllDayEvent()) {
        return;
      }

      var attendees = event.getGuestList().map(function(guest) {
        return guest.getEmail();
      }).join(", ");
      
      // 使用時間を計算（hh:mm形式）
      var duration = calculateDuration(event.getStartTime(), event.getEndTime());

      // イベント情報をスプレッドシートに書き込み
      sheet.appendRow([
        event.getId(), 
        event.getTitle(), 
        event.getStartTime(), 
        event.getEndTime(), 
        event.getLocation(), 
        event.getDescription(),
        attendees, 
        event.getCreators().join(", "), // 作成者
        "時間指定", // ステータス
        duration // 使用時間
      ]);
    });
  });
}

// 開始時刻と終了時刻から所要時間を計算する関数
function calculateDuration(startTime, endTime) {
  var duration = new Date(endTime - startTime);
  var hours = duration.getUTCHours();
  var minutes = duration.getUTCMinutes();
  return ('0' + hours).slice(-2) + ':' + ('0' + minutes).slice(-2);
}

このスクリプトでは、calendarIds 配列に指定した各カレンダーIDに対して、指定された日付範囲内のイベントを取得し、スプレッドシートに記録します。各カレンダーIDは、Googleカレンダーの設定で確認することができます。

いける！ただこのままだと誰の予定かわからなくなって分析ができない。。。GPTに聞く

カラムにカレンダーIDを追加して記入することはできますか

はい、スプレッドシートのカラムにカレンダーIDを追加して記録することは可能です。これを実現するには、スクリプトにカレンダーIDを含めるように変更する必要があります。カレンダーIDを各イベント行の一部として追加し、スプレッドシートに記録します。
以下は、カレンダーIDを含むように修正されたスクリプトです。このスクリプトでは、各イベント情報の先頭にカレンダーIDを追加しています。

function exportEventsToSpreadsheet() {
  // スプレッドシートの設定
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheets()[0];

  // 使用するカレンダーのID（複数指定可能）
  var calendarIds = ['カレンダーID1', 'カレンダーID2', 'カレンダーID3']; // 実際のカレンダーIDに置き換えてください

  // 5日前の日付（月曜日）を取得
  var fiveDaysAgo = new Date();
  fiveDaysAgo.setDate(fiveDaysAgo.getDate() - 5);

  // 現在の日付（金曜日）を取得
  var today = new Date();

  // 各カレンダーからイベントを取得し、処理
  calendarIds.forEach(function(calendarId) {
    var calendar = CalendarApp.getCalendarById(calendarId);
    var events = calendar.getEvents(fiveDaysAgo, today);

    events.forEach(function(event) {
      // 終日イベントは除外
      if (event.isAllDayEvent()) {
        return;
      }

      var attendees = event.getGuestList().map(function(guest) {
        return guest.getEmail();
      }).join(", ");
      
      // 使用時間を計算（hh:mm形式）
      var duration = calculateDuration(event.getStartTime(), event.getEndTime());

      // イベント情報をスプレッドシートに書き込み
      sheet.appendRow([
        calendarId, // カレンダーIDを追加
        event.getId(), 
        event.getTitle(), 
        event.getStartTime(), 
        event.getEndTime(), 
        event.getLocation(), 
        event.getDescription(),
        attendees, 
        event.getCreators().join(", "), // 作成者
        "時間指定", // ステータス
        duration // 使用時間
      ]);
    });
  });
}

// 開始時刻と終了時刻から所要時間を計算する関数
function calculateDuration(startTime, endTime) {
  var duration = new Date(endTime - startTime);
  var hours = duration.getUTCHours();
  var minutes = duration.getUTCMinutes();
  return ('0' + hours).slice(-2) + ':' + ('0' + minutes).slice(-2);
}

これで誰の予定かもわかるし完璧！のように思えますがまだ足りぬ。このままだと、どんな仕事にどれくらいの時間がかかっているか見るのが面倒くさすぎる。
どうやって分析するか考えた結果、Googleカレンダーのラベル付けを活用できる気がしました。(この時点ではできるかどうか分からなかった＼(^o^)／根拠のない自信大切)
早速聞いてみる

Googleカレンダーの色ラベルを取得することは可能ですか？

Google Apps Scriptの現在の機能では、Googleカレンダーのイベントに設定された色ラベル（色コード）を直接取得することはできません。Calendar APIには、イベントの色を直接取得するための組み込みメソッドがありません。

まさかのできない言われました笑
でもGoogleカレンダーに存在する以上何かしらの方法で取得できる気がしてなりません。

ここでようやく公式リファレンスを読み漁ってみるとイベントカラーの記述がありまして、早速GPTに公式リファレンスの内容をそのまま伝えます。そのまま伝えるのは結構大切な気がします。自分で理解して自分の言葉で話すと、GPTの理解が間違えて、最終的に間違えた出力しがち。
言われたことを言われたとおりに伝えるの大切。

公式リファレンスによると色はこのように表示というか対応取得されるようにセットされてるようです参照してみてください

”’
Enum EventColor

bookmark_border
カレンダー サービスで使用可能な名前付きの予定の色を表す列挙型。

列挙型を呼び出すには、親クラス、名前、プロパティを呼び出します。たとえば、 CalendarApp.EventColor.PALE_BLUE のようにします。
プロパティ
プロパティ 種類 説明
PALE_BLUE Enum 淡い青色（”1″）。
PALE_GREEN Enum 淡い緑色（”2″）。
MAUVE Enum Mauve（”3″）。
PALE_RED Enum 淡い赤（”4″）。
YELLOW Enum 黄（”5″）。
ORANGE Enum オレンジ（”6″）。
CYAN Enum シアン（”7″）。
GRAY Enum グレー（”8″）。
BLUE Enum 青（”9″）。
GREEN Enum 緑（”10″）。
RED Enum 赤（”11″）。
”’

理解できますか？

はい、理解しました。Google Apps ScriptのCalendarAppで提供されている EventColor 列挙型は、Googleカレンダーの予定に設定できる色を表しています。これらの色は、予定の色を設定したり、特定の色が設定されているイベントを識別する際に使用することができます。

なにかを理解したようなのでとりあえずゴールに向かって対話を進めてみます。

ここまでの情報をもとにカレンダーにセットされている色を取得しその色に当てられてるラベルをスプレッドシートに転記したいです先ほど書いてもらった以下のコードに修正を加えてください

function exportEventsToSpreadsheet() {
  // スプレッドシートの設定
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheets()[0];

  // 使用するカレンダーのID（複数指定可能）
  var calendarIds = []; // 実際のカレンダーIDに置き換えてください

   // 5日前の日付（月曜日）を取得
  var fiveDaysAgo = new Date();
  fiveDaysAgo.setDate(fiveDaysAgo.getDate() - 5);

  // 現在の日付（金曜日）を取得
  var today = new Date();

  // 各カレンダーからイベントを取得し、処理
  calendarIds.forEach(function(calendarId) {
    var calendar = CalendarApp.getCalendarById(calendarId);
    var events = calendar.getEvents(fiveDaysAgo, today);

    events.forEach(function(event) {
      // 終日イベントは除外
      if (event.isAllDayEvent()) {
        return;
      }

      var attendees = event.getGuestList().map(function(guest) {
        return guest.getEmail();
      }).join(", ");
      
      // 使用時間を計算（hh:mm形式）
      var duration = calculateDuration(event.getStartTime(), event.getEndTime());

      // イベント情報をスプレッドシートに書き込み
      sheet.appendRow([
        event.getId(), 
        calendarId, // カレンダーIDを追加
        event.getTitle(), 
        event.getStartTime(), 
        event.getEndTime(), 
        event.getLocation(), 
        event.getDescription(),
        attendees, 
        event.getCreators().join(", "), // 作成者
        "時間指定", // ステータス
        duration // 使用時間
      ]);
    });
  });
}

// 開始時刻と終了時刻から所要時間を計算する関数
function calculateDuration(startTime, endTime) {
  var duration = new Date(endTime - startTime);
  var hours = duration.getUTCHours();
  var minutes = duration.getUTCMinutes();
  return ('0' + hours).slice(-2) + ':' + ('0' + minutes).slice(-2);
}

指定された要件に従って、イベントの色ラベルを取得し、スプレッドシートに転記する機能を追加します。イベントの色ラベルは、EventColor 列挙型を使用して取得し、それに対応するラベル名をスプレッドシートに記録します。
以下は修正されたスクリプトです。新しいカラム「イベント色」を追加し、それに対応する色ラベル名を転記します。

function exportEventsToSpreadsheet() {
  // スプレッドシートの設定
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheets()[0];

  // 使用するカレンダーのID（複数指定可能）
  var calendarIds = []; // 実際のカレンダーIDに置き換えてください

  // 5日前の日付（月曜日）を取得
  var fiveDaysAgo = new Date();
  fiveDaysAgo.setDate(fiveDaysAgo.getDate() - 5);

  // 現在の日付（金曜日）を取得
  var today = new Date();

  // イベント色ラベル名を取得する関数
  var colorLabelMap = getColorLabelMap();

  // 各カレンダーからイベントを取得し、処理
  calendarIds.forEach(function(calendarId) {
    var calendar = CalendarApp.getCalendarById(calendarId);
    var events = calendar.getEvents(fiveDaysAgo, today);

    events.forEach(function(event) {
      // 終日イベントは除外
      if (event.isAllDayEvent()) {
        return;
      }

      var attendees = event.getGuestList().map(function(guest) {
        return guest.getEmail();
      }).join(", ");
      
      // 使用時間を計算（hh:mm形式）
      var duration = calculateDuration(event.getStartTime(), event.getEndTime());

      // イベントの色ラベル名を取得
      var eventColorLabel = colorLabelMap[event.getColor()];

      // イベント情報をスプレッドシートに書き込み
      sheet.appendRow([
        event.getId(), 
        calendarId, // カレンダーIDを追加
        event.getTitle(), 
        event.getStartTime(), 
        event.getEndTime(), 
        event.getLocation(), 
        event.getDescription(),
        attendees, 
        event.getCreators().join(", "), // 作成者
        "時間指定", // ステータス
        duration, // 使用時間
        eventColorLabel // イベント色
      ]);
    });
  });
}

// イベント色ラベル名をマッピングする関数
function getColorLabelMap() {
  return {
    "1": "淡い青色",
    "2": "淡い緑色",
    "3": "Mauve",
    "4": "淡い赤",
    "5": "黄色",
    "6": "オレンジ",
    "7": "シアン",
    "8": "グレー",
    "9": "青",
    "10": "緑",
    "11": "赤"
    // その他の色があれば、ここに追加
  };
}

// 開始時刻と終了時刻から所要時間を計算する関数
function calculateDuration(startTime, endTime) {
  var duration = new Date(endTime - startTime);
  var hours = duration.getUTCHours();
  var minutes = duration.getUTCMinutes();
  return ('0' + hours).slice(-2) + ':' + ('0' + minutes).slice(-2);
}

ついに完成しました！！！
これでカレンダーのラベルさえセットすればLookerStudioでの分析が可能になる！！

まとめ

最終的にはLookerStudioで分析する際に’使用時間’をhh:mm形式で入力すると集計しづらいため修正しています。修正後は30分を0.5として数えます。

今回はいままでの投稿と少し方向性を変えて社内の業務改善の取り組みについてお伝えしました。
しばらくは業務改善の取り組みについても投稿していきますので、ご興味のある方は弊社サイトを定期的に見に来ていただけると嬉しいです😆

業務改善
ChatGPT Google 業務改善
よかったらシェアしてね！
  • URLをコピーしました！
  • URLをコピーしました！

この記事を書いた人

yasuyukiのアバター yasuyuki

関連記事

コメント

コメントする

コメントは日本語で入力してください。(スパム対策)

目次