Edited at

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


目的

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


使用するツール


  • Googleカレンダー

  • Googleスプレッドシート

  • Googleドライブ

  • Googleデータスタジオ

  • GAS(Google Apps Script)


手順

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

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

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

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

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


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

まずは、Googleカレンダーにスケジュールを入力します。

その際、今後のステップで可視化を楽にするために、各予定の名前は「○○○(カテゴリ名)/×××(内容)」という形に統一します。

下の画像が例です。

「○○○」の部分には、予定の大まかなカテゴリを入力します。

例えば、ミーティングであれば「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カレンダーのスケジュールが自動でスプレッドシートに書き起こされます。

書き起こされたスプレッドシートを確認すると、「data」というワークシートが増えています。

このあと使用するGoogleデータスタジオの参照エラーを回避するために、書き起こされたデータを全て「data」に特殊貼り付け(値のみ貼り付け)します。

手順は以下の通り。



これで、ワークシート「data」に、Googleカレンダーの内容が入力された状態になります。

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


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

最後のステップです。

ここでは、③で作成したワークシート「data」をリソースとして、Googleデータスタジオを使用します。

これによって、スケジュールをタスクのカテゴリごとに見たり、どういったタスクに時間が割かれているのか、といったことを視覚化し、分析可能にします。

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

ここからは、このレイアウトでご自身のスケジュールを視覚化する手順を紹介します。

具体的なステップは以下の通り。

1.https://datastudio.google.com/u/1/reporting/1F3XVz9621IqCXfrM3RU8YlaYlnNhAP5d/page/qQwZ

にアクセスする

2.メニューバーから「ファイル」を選択し、「コピーを作成」をクリック

3.「新しいデータソースを作成」から、Googleスプレッドシートを選択し、③で作成したシートを「接続」し、レポートに追加する





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

ここまでできたら、あとは「設定未完了」になっている各グラフに、データソースを設定するのみです。

まず、グラフ部分をクリックし、画面右端のメニューにある「データソースの選択」から、先ほど接続したシートを選択します。

次に、同じく「データソース」の項目から、「期間のディメンジョン」を選択し、「開始時刻」をクリックします。

この作業を、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)