目的
Googleカレンダーに入力された内容を視覚化することで、働き方に関するPDCAをより効率的に回せるようにする。
使用するツール
- Googleカレンダー
- Googleスプレッドシート
- Googleドライブ
- Googleデータスタジオ
- GAS（Google Apps Script）
手順
それでは、以下の５つのステップに分けて、手順を説明します。
① 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行目）の部分を、自分のものに書き換える(この際、クオート記号を消さないように注意してください)
以上の４ステップです。
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スプレッドシートを選択し、③で作成したシートを「接続」し、レポートに追加する
※この際、「所要時間」のデータタイプを、「テキスト」から「数値（持続時間（秒））」に変更してください。
ここまでできたら、あとは「設定未完了」になっている各グラフに、データソースを設定するのみです。
まず、グラフ部分をクリックし、画面右端のメニューにある「データソースの選択」から、先ほど接続したシートを選択します。
次に、同じく「データソース」の項目から、「期間のディメンジョン」を選択し、「開始時刻」をクリックします。
この作業を、４つのグラフ部分全てに対して行えば、レポートの完成です。
参考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)