Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationEventAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
28
Help us understand the problem. What are the problem?

posted at

updated at

Googleカレンダーの中身を視覚化する

目的

Googleカレンダーに入力された内容を視覚化することで、働き方に関するPDCAをより効率的に回せるようにする。

使用するツール

  • Googleカレンダー
  • Googleスプレッドシート
  • Googleドライブ
  • Googleデータスタジオ
  • GAS(Google Apps Script)

手順

それでは、以下の5つのステップに分けて、手順を説明します。

① Googleカレンダーにスケジュールを入力する
② Googleスプレッドシートを用意する
③ GAS(Google Apps Script)を作成&実行する
④ GoogleスプレッドシートをGoogleデータスタジオに接続する

①Googleカレンダーにスケジュールを入力する

まずは、Googleカレンダーにスケジュールを入力します。
その際、今後のステップで可視化を楽にするために、各予定の名前は「○○○(カテゴリ名)/×××(内容)」という形に統一します。
下の画像が例です。

Calendar_sample.jpg

「○○○」の部分には、予定の大まかなカテゴリを入力します。
例えば、ミーティングであれば「mtg」、調査や調べ物の時間であれば「research」、といった具合です。
今回は、以下の表にあるカテゴリ名を使用します。

カテゴリ名 内容
mtg 会議、ミーティング
study 勉強
research 調査、調べもの
work 上記以外のタスク

「×××」の部分には、予定の具体的な内容を入力します。
例えば、mtgであれば「経営会議」、「定例ミーティング」、といった具合です。

②Googleスプレッドシートを用意する

続いて、Googleドライブの中に、新しいフォルダを任意の名前で作成します。
そのフォルダの中に、新規スプレッドシートを任意の名前で作成してください。

③GAS(Google Apps Script)を作成&実行する

次に、①でGoogleカレンダーに入力したスケジュールの内容を、②で作成したGoogleスプレッドシートに起こしていきます。
これによって、入力したスケジュールを、Googleデータスタジオで扱えるようになります。

順序としては、
1.②で作成したスプレッドシートを開く
2.メニューバーの「ツール」から、「スクリプト エディタ」を開く
3.テキストエディタをクリアにしてから、以下にあるコードをコピー&ペーストする
4.コードの、「スプレッドシートのフォルダID」(5行目)と「メールアドレス」(55行目)の部分を、自分のものに書き換える(この際、クオート記号を消さないように注意してください)
以上の4ステップです。

3.でコピー&ペーストするコードはこちら
3.でコピー&ペーストするコード
/* 指定月のカレンダーからイベントを取得する */
function getCalendar() {

  // フォルダ、ファイル関係の定義
  var targetFolderIds = ["スプレッドシートのフォルダID"];
  var targetFolder;
  var folderName;
  var objFiles;
  var objFile;
  var fileName;

  // スプレッドシート関係の定義
  var ss;
  var key;
  var sheets;
  var sheetId;


  for (var i = 0; i < targetFolderIds.length; i++) {
    // Idから対象フォルダの取得
    targetFolder = DriveApp.getFolderById(targetFolderIds[i]);
    folderName = targetFolder.getName();

    // 対象フォルダ以下のSpreadsheetを取得
    objFiles = targetFolder.getFilesByType(MimeType.GOOGLE_SHEETS);

    while (objFiles.hasNext()) {
      objFile = objFiles.next();
      fileName = objFile.getName();

      // Spreadsheetのオープン
      ss = SpreadsheetApp.openByUrl(objFile.getUrl());
      key = ss.getId();
      sheets = ss.getSheets();
    }
  }

  /*スプレッドシートをクリア*/
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();
  var range = sheet.getRange(1,1,400,5);
  range.clear();

  /*列名を入力*/
  var range = sheet.getRange("A1").setValue("No.");
  var range = sheet.getRange("B1").setValue("カテゴリ");
  var range = sheet.getRange("C1").setValue("内容");
  var range = sheet.getRange("D1").setValue("開始時刻");
  var range = sheet.getRange("E1").setValue("終了時刻");
  var range = sheet.getRange("F1").setValue("所要時間");

  var mySheet=SpreadsheetApp.getActiveSheet(); //シートを取得
  var no=1; //No

  var myCal=CalendarApp.getCalendarById('メールアドレス'); //特定のIDのカレンダーを取得

  var startDate=new Date('2018/08/20 00:00:00'); //取得開始日
  var endDate=new Date('2018/10/01 00:00:00');  //取得終了日

  var myEvents=myCal.getEvents(startDate,endDate); //カレンダーのイベントを取得

  /* イベントの数だけ繰り返してシートに記録 */
  for each(var evt in myEvents){
    mySheet.appendRow(
      [
        no, //No
        evt.getTitle(), //イベントタイトル
        evt,
        evt.getStartTime(), //イベントの開始時刻
        evt.getEndTime(), //イベントの終了時刻
        "=INDIRECT(\"RC[-1]\",FALSE)-INDIRECT(\"RC[-2]\",FALSE)" //所要時間を計算
      ]
    );
    no++;
  }

  /* 列分解 */
    var lastRow = sheet.getLastRow();
  for(i=2;i <= lastRow;i++){
    var x = sheet.getRange(i,2);
    var y = sheet.getRange(i,11);  // 使っていないセルを取得
    var z = sheet.getRange(2,3,lastRow-1,1);

    z.clearContent();
    y.setValue(x.getValue());
    x.clearContent();  
    strformula = "=split(K" + i + ",\"/\")";
    x.setFormula(strformula);
  }  

  /* 所要時間の[ss]表示 */
  var secondTime = '[ss]';
  for(i=2;i <= lastRow;i++){
       var numberRange=sheet.getRange(i,6,lastRow);
       numberRange.setNumberFormat( secondTime );
  }

  /* 週番号の追加 */
  var x=sheet.getRange(1,7);
  var y=sheet.getRange(1,8);
  var z=sheet.getRange(1,9);
  var w=sheet.getRange(1,10);
  x.setValue('週番号');
  y.setValue('日にち');
  z.setValue('所要時間(hh:mm)');
  w.setValue('曜日');
  var NumFormats = '0'; 
  for(i=2;i <= lastRow;i++){
    var x=sheet.getRange(i,7);
    var y=sheet.getRange(i,8);
    var z=sheet.getRange(i,9);
    var w=sheet.getRange(i,10);
    weeknum = "=WEEKNUM(D" + i + ")";
    x.setFormula(weeknum);
    x.setNumberFormat( NumFormats );
    daynum = "=day(D" + i + ")";
    y.setFormula(daynum);
    hhmm = "=TEXT(F" + i + ",\"hh:mm\")";
    z.setFormula(hhmm);
    week = "=TEXT(D" + i + ",\"ddd\")";
    w.setFormula(week); 
  }

  /* シートを追加し、値とフォーマットのペースト */
  var sheet2 = spreadsheet.insertSheet("data");
  sheet.getRange("A:I").copyTo(sheet2.getRange("A:I"), {formatOnly:true});
  //sheet.getRange("A:I").copyTo(sheet2.getRange("A:I"), {contentsOnly:true});
}

※ 取得するスケジュールの範囲を変更したい場合は、「取得開始日」、「取得終了日」のコードを任意で書き換えて下さい。
※「メールアドレス」は、今回使用するGoogleカレンダーを持っているアカウントのGmailアドレスです。
※「スプレッドシートのフォルダID」が分からない場合は、こちらの記事から確認できます。

ここまで完了したら、コードを保存してから、実行ボタンを押してください。
下の画像の様に、Googleカレンダーのスケジュールが自動でスプレッドシートに書き起こされます。

Sheet_sample.jpg

書き起こされたスプレッドシートを確認すると、「data」というワークシートが増えています。
このあと使用するGoogleデータスタジオの参照エラーを回避するために、書き起こされたデータを全て「data」に特殊貼り付け(値のみ貼り付け)します。
手順は以下の通り。
Weekly_Report_-_Google_スプレッドシート.jpg
スクリーンショット_2018-09-14_14_51_37_png.jpg

これで、ワークシート「data」に、Googleカレンダーの内容が入力された状態になります。
「カテゴリ」や「内容」のカラムがうまく表示されない場合は、スケジュールが、①で紹介したルールに則って入力されていない可能性があるので、Googleカレンダーをチェックしてみて下さい。

④GoogleスプレッドシートをGoogleデータスタジオに接続する

最後のステップです。
ここでは、③で作成したワークシート「data」をリソースとして、Googleデータスタジオを使用します。
これによって、スケジュールをタスクのカテゴリごとに見たり、どういったタスクに時間が割かれているのか、といったことを視覚化し、分析可能にします。

まずは、視覚化の例として、筆者が作成したレイアウトを紹介します。
DataStudio_sample.jpg

ここからは、このレイアウトでご自身のスケジュールを視覚化する手順を紹介します。
具体的なステップは以下の通り。

1.https://datastudio.google.com/u/1/reporting/1F3XVz9621IqCXfrM3RU8YlaYlnNhAP5d/page/qQwZ
にアクセスする

2.メニューバーから「ファイル」を選択し、「コピーを作成」をクリック
タイムリソースの利用状況.jpg

3.「新しいデータソースを作成」から、Googleスプレッドシートを選択し、③で作成したシートを「接続」し、レポートに追加する
タイムリソースの利用状況_のコピー-2.jpg
タイムリソースの利用状況_のコピー.jpg
タイムリソースの利用状況_のコピー.jpg

タイムリソースの利用状況_のコピー-4.jpg

※この際、「所要時間」のデータタイプを、「テキスト」から「数値(持続時間(秒))」に変更してください。

ここまでできたら、あとは「設定未完了」になっている各グラフに、データソースを設定するのみです。
まず、グラフ部分をクリックし、画面右端のメニューにある「データソースの選択」から、先ほど接続したシートを選択します。
次に、同じく「データソース」の項目から、「期間のディメンジョン」を選択し、「開始時刻」をクリックします。

この作業を、4つのグラフ部分全てに対して行えば、レポートの完成です。

参考URL

アイウェイズコンサルティング株式会社「【BI×働き方改革】Tableauでリソースを可視化する」
https://tableau-i-ways.com/column/googlecalendar
(2018/09/10)

ANAGRAMS「Google データスタジオの基本的な使い方:伝わるレポートでビジネスの意思決定を加速させる」
https://anagrams.jp/blog/basic-of-google-data-studio/
(2018/09/10)

科学とGAS「スプレッドシート・フォルダのID確認方法」
http://amehal.blogspot.com/2015/10/id.html
(2018/09/10)

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
28
Help us understand the problem. What are the problem?