見出し画像

GWSのReportsAPIを使ってmeetの通話時間をミーティング別で可視化してみた

tnkt

少し前に社内のミーティングの可視化をしてみよう、という案件が出てきたので、GWSのReportsAPIなるものを使ってmeetの可視化をしてみました。という作業のメモ。



Google meetのデータを取得する

meetの情報を取れるかは公式ドキュメント等を参考に進めました。


GASを書く

取りたい内容のメインは、ユーザー別のmeetの通話時間。適当なスプレッドシートを準備しGASを作ります。

次にスクリプトエディタの「サービス」メニュー右の「＋」をクリックして、表示されたダイアログで「Admin SDK API」をクリック、バージョンで「reports_v1」を選択します。IDはとりあえず「AdminReports」にしておきます。

準備ができたらGASを書きます。ドキュメントを見ると、とりあえず下記の形で書けば対応したデータが取れるようでした。

AdminReports.Activities.list(userKey, applicationName, optionalArgs)


ユーザーを自分、アプリケーションをmeetにして書いてみます。ChatGPTさんの力も借りつつ、下記の形でとりあえず実施時間と通話秒数が取れるようにしました。デフォルトだとタイムゾーンの兼ね合いで時間が9時間ずれるので注意です。

function myFunction() {
  // 対象ユーザー
  const user = 'ユーザーのメールアドレス';

  // 対象アプリケーション
  const app ='meet';

  // オプション
  const options ='';

  const result = AdminReports.Activities.list(user, app);

  // パース
  let res = JSON.stringify(result['items']);
  res = JSON.parse(res);

  
  // 配列内のeventsとid配列を取得
  for (let i = 0; i < res.length; i++) {
    let eventsArray = res[i]['events'];
    let idArray = res[i]['id'];

    // eventsとid配列内の要素をループで検索
    for (let j = 0; j < eventsArray.length; j++) {
      // parameters配列を取得
      let parametersArray = eventsArray[j]['parameters'];

      // parametersとid配列内の要素をループで検索
      for (let k = 0; k < parametersArray.length; k++) {
        // nameが"duration_seconds"の場合、intValue(通話時間秒数)とtime(実施時間)を返す
        if (parametersArray[k].name === "duration_seconds") {
          console.log(parametersArray[k]['intValue']);
          console.log(idArray['time']);
        }

      }

    }

  }

}


期間を指定せずに流してみたら、10か月くらい前までのデータがでてきました。デフォルトの件数は件数なのか期間なのか。オプションで期間指定をすればちゃんと指定範囲内で取ってくれます。

const options = {
    startTime: '2023-11-01T06:13:10.078Z',
    endTime: '2023-11-10T00:55:49.478Z'
  };

const result = AdminReports.Activities.list(user, app, options);


全員分のデータを取る

まず単体ユーザーで試してみましたが、実際はユーザー全員の通話時間のデータがほしいので、ユーザーリストを引っ張ってきてからリストに対して処理を行うように変更します。

「サービス」メニュー右の「＋」をクリックして、表示されたダイアログで「Admin SDK API」をクリック、バージョンで「directory_v1」を追加します。IDは「AdminDirectory」にしておきます。

あとは(どちらかのサイトで紹介していた)下記のコードでユーザーの情報を取得するようにします。

function listAllUsers(){
  // 初期設定
  let pageToken;
  let page;
  let result = [];
  
  do {
    page = AdminDirectory.Users.list({
      domain: '取得対象ドメイン',
      orderBy: 'EMAIL',
      maxResults: 500,
      pageToken: pageToken
    });
    let users = page.users;

    // 取得できたら名前とメールアドレスを配列に格納
    if (users) {
      for(const user of users){
        result.push(user.primaryEmail)
      }
    }

    // 一括で取得できるのは最大500件までなので、それ以上の場合はページトークンを変数に格納して引き渡し
    pageToken = page.nextPageToken;

  } while (pageToken)

  return result;

}

この処理でユーザーの配列を最初に作ったあとAdminReportsに順番に食わせていけば、全ユーザーのmeet通話時間リストの出来上がりです。


予定のタイトルを取れるようにする

ここまでの処理で、ユーザー別の通話時間データはとりあえず取れるようになりました。ただ、それぞれ何のミーティングの通話時間なのかが分かりません。実用性に欠けるので、ミーティングのタイトルも取れるようにします。


Googleカレンダーのデータと突合する

meetのデータはミーティングのタイトルは保持していないので、Googleカレンダーと突き合わせを行う必要があります。meetからはカレンダーの予定IDを取得できるので、Googleカレンダーのデータと予定IDで突き合わせればタイトルの取得ができそうです。

具体的には、meetから「calendar_event_id」の値を取った後に「AdminReports.Activities.list」でカレンダーの予定を取り出して、meetのデータから取得した予定IDをもつカレンダーを探索します。

ただ調べていくうち、カレンダーに登録されているのにmeetで予定IDを持たないイベントがあることが分かりました。見た感じ、定期開催だとそうなるように見えます。

これだと紐づけができないので困ります。もうちょっと調べてみると、Calendar APIの「Events.list」でカレンダーに登録されているmeetのURLが取れることがわかったので、このURLとmeet側で取得した会議コードを突き合わせる形にします。

GASでカレンダーAPIを追加し、実装します。下記でカレンダーのタイトルと会議コードが取得できました。

function myFunction() {
  // カレンダーIDを指定
  let calendarId = "ユーザーのメールアドレス";

  // カレンダーAPIでイベントリストを取得
  let events = Calendar.Events.list(calendarId).items;

  // カレンダーのタイトルと会議コードを取得する
  for (let i = 0; i < events.length; i++) {
    if ('conferenceData' in events[i]) {
      console.log(events[i]['summary'])
      console.log(events[i]['conferenceData']['conferenceId']);
    }
  }  
}


会議コードで突合する

meetの会議コードはカレンダーでは「3-4-3」で区切られて「aaa-bbbb-ccc」の形の小文字になるので、meet側で取得した会議コードを加工して突き合わせます。最終的に下記の形になりました。

// 書き込み先SS
const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('data');

// データ一時格納用配列
const dataValues = [];

function getMeetUsage() {

  // 現在時刻の取得
  const now = new Date();
  
  // 昨日の日付を計算
  let yesterdayStart = new Date(now);
  let yesterdayEnd = new Date(now);
  yesterdayStart.setDate(now.getDate() - 1);
  yesterdayEnd.setDate(now.getDate() - 1);
  yesterdayStart.setHours(0, 0, 0, 0);
  yesterdayEnd.setHours(23, 59, 59, 59);

  // 日本標準時（JST）に変換
  const yesterdayStartJST = Utilities.formatDate(yesterdayStart, "Asia/Tokyo", "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'");
  const yesterdayEndJST = Utilities.formatDate(yesterdayEnd, "Asia/Tokyo", "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'");

  // 変数宣言
  let durationTime;
  let japanTime;
  let meetingCode;
  let calendarTitle;
  let calendarMeetingCode;

  // GWSの全ユーザーの配列を準備
  const allUsers = listAllUsers();
  const users = allUsers[0];
  const emails = allUsers[1];

  // 対象アプリケーションを指定
  const app ='meet';

  // オプション
  const options = {
    // 取得時間を範囲指定
    startTime: yesterdayStartJST,
    endTime: yesterdayEndJST
  };

  // 全ユーザーのmeetのログから会議コードを取得し、会議コードからカレンダーの予定の名前を取得する
  for (let l = 0; l < users.length; l++) {
    console.log(users[l]);

    // データ取得
    const result = AdminReports.Activities.list(users[l], app, options);
    // itemsが存在しなければスキップ
    if (!('items' in result)) {
      continue;
    }

    // パース
    let res = JSON.stringify(result['items']);
    res = JSON.parse(res);
    
    // 配列内のeventsとid配列を取得
    for (let i = 0; i < res.length; i++) {
      let eventsArray = res[i]['events'];
      let idArray = res[i]['id'];

      // eventsとid配列内の要素を検索
      for (let j = 0; j < eventsArray.length; j++) {
        // parameters配列を取得
        let parametersArray = eventsArray[j]['parameters'];

        // parametersとid配列内の要素を検索
        for (let k = 0; k < parametersArray.length; k++) {
          // nameが"duration_seconds"の場合、intValue(通話時間秒数)とtime(退出時間)を返す
          if (parametersArray[k].name === "duration_seconds") {
            
            // 秒を分に直して四捨五入する
            durationTime = Math.round(parametersArray[k]['intValue'] / 60);

            // Dateオブジェクトを作成し、UTC時間で解釈
            let inputDate = new Date(idArray['time']);

            // タイムゾーンを日本時間に設定
            japanTime = Utilities.formatDate(inputDate, "Asia/Tokyo", "yyyy-MM-dd HH:mm");

          // nameが"meeting_code"の場合、会議コードIDを取得してカレンダーから予定タイトルを取得する
          } else if (parametersArray[k].name === "meeting_code") {
            meetingCode = parametersArray[k]['value'];

            // 会議コードを3-4-3の形でハイフンで区切って小文字にする
            calendarMeetingCode = [];
            const chunkSize = [3, 4, 3];
            let startIndex = 0;
            for (let m = 0; m < chunkSize.length; m++) {
              let chunk = meetingCode.substring(startIndex, startIndex + chunkSize[m]);
              calendarMeetingCode.push(chunk);
              startIndex += chunkSize[m];
            }
            calendarMeetingCode = calendarMeetingCode.join("-");
            calendarMeetingCode = calendarMeetingCode.toLowerCase();
            
            // 会議コードを引き渡してカレンダーからタイトルを取得
            calendarTitle = getCalendar(users[l], calendarMeetingCode, yesterdayStartJST, yesterdayEndJST);

          }

        }
        // 書き込み用に配列にデータを入れておく
        if (!(durationTime === "")) {
          let values = [users[l], emails[l], durationTime, japanTime, calendarTitle, calendarMeetingCode];
          dataValues.push(values);
          // 値を空にしておく
          durationTime = "";
          calendarTitle = "";
          calendarMeetingCode = "";
        }
      }

    }
  }

  // 出力が済んだら配列のデータを書き込む
  let lastRow = ss.getLastRow();
  ss.getRange(lastRow + 1, 1, dataValues.length, dataValues[0].length).setValues(dataValues);
}



// カレンダーAPIからデータを取得する
function getCalendar(calendarId, meetingCode, yesterdayStartJST, yesterdayEndJST) {

  let title;

  // オプション
  const options = {
    timeMin: yesterdayStartJST,
    timeMax: yesterdayEndJST,
    maxResults: 2500
  };

  // カレンダーAPIでイベントリストを取得
  let events = Calendar.Events.list(calendarId, options).items;

  // 会議コードからカレンダーのタイトルを取得する
  for (let i = 0; i < events.length; i++) {
    if ('conferenceData' in events[i]) {
      if (events[i]['conferenceData']['conferenceId'] === meetingCode ) {
        title = events[i]['summary'];
      }
    }
  }

  return title;
}



function listAllUsers(){
  // 初期設定
  let pageToken;
  let page;
  let email = [];
  let fullName = [];
  
  do {
    page = AdminDirectory.Users.list({
      domain: '取得対象ドメイン',
      orderBy: 'EMAIL',
      maxResults: 500,
      pageToken: pageToken
    });
    let users = page.users;

    // 取得できたら名前とメールアドレスを配列に格納
    if (users) {
      for(const user of users){
        fullName.push(user.name['fullName']);
        email.push(user.primaryEmail);
      }
    }

    // 一括で取得できるのは最大500件までなので、それ以上の場合はページトークンを変数に格納して引き渡し
    pageToken = page.nextPageToken;

  } while (pageToken)

  return [email, fullName];

}


ちなみに、カレンダーに予定がないものはタイトルがそもそも存在しないので、予定の名前が取得できないものが多少でてきます。

また、各ユーザーごとに見ていくので、取得期間によっては動作が重くなります。自分の場合は1日1回取得できればよいので、夜中に昨日1日分を取得するようにしました。ユーザー別にmeetのログを取るのではなく、一括で取ってから突き合わせたほうが早いかもしれません。


可視化と共有について

ここまでの処理で、ユーザー別かつミーティング別のmeetの通話時間が取得できるようになりました。


ついでにLookerで可視化する

せっかくなのでLookerに食わせて、ミーティングのタイトルで検索できるようにしてみました。

自分の場合は水曜に1on1しがちでした。


なお公開はしなかった

はじめはこれを社内で公開することを検討していましたが、誰でも誰かのミーティング時間を見れるのは良いことだけではないかなと思ったので、結局公開はしませんでした。

今回はAPIでmeetとカレンダーのデータを取得してみましたが、他にも色々取得できそうなのでReportsAPIにはまだまだ活用方法がありそうです。今後も機会があれば触ってみたいと思えるものでした。

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